数据库学习之索引

最新推荐文章于 2022-08-21 18:20:47 发布

sakeww

最新推荐文章于 2022-08-21 18:20:47 发布

阅读量708

点赞数 20

分类专栏：数据库学习文章标签：数据库学习 mysql

本文链接：https://blog.csdn.net/sakeww/article/details/126322243

版权

数据库学习专栏收录该内容

13 篇文章 0 订阅

订阅专栏

引入-没有索引，会有什么问题

索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。
常见索引分为：
主键索引(primary key)
唯一索引(unique)
普通索引(index)
全文索引(fulltext)–解决中子文索引问题

准备工作

创建一个数据库，内含大量随机数据，
在这里插入图片描述

测试–查询员工编号为XXXXX的员工

在这里插入图片描述

修改方法：增加索引
在这里插入图片描述

认识磁盘

MySQL对数据做的CRUD操作，根据冯诺依曼体系，MySQL不可能直接去访问磁盘
实际上数据库中对数据做的所有的访问，全部都是在内存中进行的
定期的将数据刷新到磁盘中

MySQL的工作过程：
MySQL在启动时，会在内存中malloc一大块空间buffer pool[]，将磁盘中的数据缓存在buffer pool[],然后你的所有操作都是在修改这块内存的数据
MySQL会定期的将buffer pool[]中的数据刷新到内核缓冲区中，（通过write）
然后通过系统调用，将内核缓冲区中的数据刷新到磁盘中（系统调用接口）

MySQL与磁盘交互基本单位–基本数据单元

在这里插入图片描述

预备知识

MySQL 中的数据文件，是以page为单位保存在磁盘当中的。
MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。
而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。
所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位就是Page。
为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 BufferPool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。
为何更高的效率，一定要尽可能的减少系统和磁盘IO的次数

索引理解

建立测试表

插入多条记录

为何IO交互要是 Page
为何MySQL和磁盘进行IO交互的时候，要采用Page的方案进行交互呢?用多少，加载多少不香吗?
如上面的5条记录，如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那么就需要2次IO。如果要找id=5，那么就需要5次IO。
但，**如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录),**那么第一次IO查找id=2的时候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。但是往后如果在查找id=1,3,4,5等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，大大减少了IO的次数。
你怎么保证，用户一定下次找的数据，就在这个Page里面？我们不能严格保证，但是有很大概率，因为有局部性原理。往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数

单个page
包含 left，right，目录，数据内容
多个page根据单个page的left和right相连

page内部：查询是根据目录寻找数据内容
数据内容中的数据是按照链表的形式存储的

多个page按照主键的最小值排序组成一个新的目录，
新的目录然后又根据这个方案形成新新目录
。。。
最后是一张表用来记录所有数据
B+树

什么是主键索引
1.所有的数据最终都可以在磁盘中，也可以在mysql的buffer pool内存中
2.所有的数据都必须以page为单位进行IO，以page为单位组织

在mysql内部，将热点数据，以B+树的形式将所有的page页，进行组织，形成的数据结构与其配套的查找算法，叫做索引！！！

因为查找方便，所以需要加在的page表大大的减少了，也就是IO的次数减少，从而增加了效率

B+树在哪里？
在磁盘上有完整的B+和数据
在内存中有局部被访问的B+的核心page
MySQL查找一定会伴生着MySQL进行根据B+进行page的换入换出！

聚簇索引vs非聚簇索引

非聚簇索引

聚簇索引

辅助索引
对于 MyISAM ,建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复

InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的key值
通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这种过程，就叫做回表查询

mysiam：因为存储的是数据地址，所以更加适合做查找的工作

索引操作

主键索引
创建：
primary key：在需要的属性后面添加
primary key(属性)：在创建表的末尾，加上
alter table 表名 add primary key(属性);
查看索引
desc 表名
show index from 表名（主键，唯一键，普通索引都可以用这个查看）
show keys from 表名
删除索引
alter table 表名 drop primary key;
特点：
一个表中，最多有一个主键索引，当然可以使符合主键
主键索引的效率高（主键不可重复）
创建主键索引的列，它的值不能为null，且不能重复
主键索引的列基本上是int

唯一索引—unique—唯一键
创建同主键索引，primary key->unique
添加：
alter table 表名 add unique (属性)
特点：
一个表中，可以有多个唯一索引
查询效率高
如果在某一列建立唯一索引，必须保证这列不能有重复数据
如果一个唯一索引上指定not null，等价于主键索引

普通索引：
创建：
index(属性）在创建表的末尾，加上
alter table 表名 add index(属性);
create index 新名 on 表名(属性);
删除：
alter table 表名 drop index 索引名；
drop index 索引名 on 表名（普通索引和唯一键索引都是这种删除方法）
特点：
一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引