MySQL索引

最新推荐文章于 2022-11-05 19:54:26 发布

哪里的船迷醉了夕阳

最新推荐文章于 2022-11-05 19:54:26 发布

阅读量901

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 database

本文链接：https://blog.csdn.net/qq_41784433/article/details/121208479

版权

MySQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

学习地址： mysql45讲、 MySQL索引

1、索引是什么？

索引是帮助MySQL高效获取数据的排好序的数据结构。
索引的出现就是为了提高数据查询的效率，可以理解为书的目录一样。
数据是放在磁盘上的，每一行记录地址不一定连续，如果不使用索引，一行一行的进行扫描，每扫描一行就会和磁盘进行一次交互。IO交互是很慢的。
用于提高读写效率的数据结构有很多，45讲介绍三种常见且简单的数据结构：1、哈希表；2、有序数组；3、搜索树。

1.1、哈希表

是key-value存储结构，输入key就可以找到对应的值value。
key是索引，value是索引所在行的磁盘文件地址。
哈希思路：值放在数组中，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的该位置。
4.哈希冲突：当有多个key经过哈希的换算，会出现同一个值的情况。解决的一种方式就是引入链表。

1.1.1、优缺点

哈希的优点：在同一个索引位置增加值的时候，速度很快。
缺点：因为加入的时候并不是有序的，所以哈希索引做区间查询的速度是很慢的。
区间查询慢的原因：定位到了索引位置，但是现在该索引位置有很多元素，在这个范围查找某个元素就必须对这个区间进行全部扫描。

1.1.2、结论

哈希表适用于只有等值查询的场景（例如：select name from T where id = 12），比如：Memcached和其他一些NoSQL引擎。

1.2、有序数组

有序数组在等值查询和范围查询场景的性能就都非常优秀。
因为是有序的，那么在一个范围上进行查找某个确定的值，假设值都是没有重复的，那么就可以使用二分法快速得到，二分的时间复杂度 $O(log_n)$ .

1.2.1、优缺点

优点：有序数组做查询的效率很快。
缺点：需要更新数据的时候很麻烦，当在有序数组这个范围中间插入一条记录的时候，就必须挪动后面所有的记录。

1.2.2、结论

有序数组只使用静态存储引擎。比如：存储的是不会再修改的数据。

1.3、二叉搜索树

二叉搜索树的特点：根结点左子树所有节点值都小于根节点的值，右子树所有节点值都大于根节点的值。
二叉搜索树的时间复杂度： $O(log_n)$ 。
同样，为了维持 $O(log_n)$ 的时间复杂度，就必须保持这颗数是平衡二叉树，更新的时间复杂度是 $O(log_n)$ 。
二叉树是搜索效率最高的，但实际上大多数的数据库存储却并不会使用二叉树，原因是，索引不止存在内存中，还要写到磁盘上。即：当二叉树过高的时候，每次查询需要访问过多的节点，访问的数据块过多，而从磁盘随机读取数据块过于耗时（IO次数过多）。

1.4、N叉树

N取决于数据块的大小。
以InnoDB的一个整数字段索引为例，N差不多是1200（mysql默认一个节点的长度是16k(show global status like 'Innodb_page_size')，bitint的字段索引长度是8字节（B），另外每个索引还跟着6字节的指向其子树的指针，那么16k/(8+4)B≈1170）
当树的高度是4的时候，那么就可以存1200的3次方个值，接近17亿，考虑到树根的数据块总是在内存中，一个10亿行的表上一个整数字段的索引，查找一个值，那么最多只需要访问3次磁盘。其实，树的第二层也有很大的概率在内存中，这样磁盘被访问的平均次数就更少了。

2、索引模型

在MySQL中，索引是在存储引擎层实现的，所以没有统一的索引标准，即：不同存储引擎的索引工作方式并不一样。
即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

2.1、InnoDB的索引模型

在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB使用了B+树索引模型，所以数据都是存储在B+树中的。
1. 在InnoDB中，每一张表其实是多个B+树，就是一个主键索引树+多个非主键索引树。
2. 执行效率：使用主键索引>使用非主键索引>不使用索引。
3. 不使用索引进行查询，那么从主索引的B+树的叶子结点依次进行遍历。
每一个索引在InnoDB里面对应一颗B+树。
举例：
```
mysql> create table T(
id int primary key,  //主键
k int not null, //字段k
name varchar(16),
index (k))engine=InnoDB;//给k建立索引，引擎是innodb
```
表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。

根据叶子结点的内容，索引类型可以分为主键索引以及非主键索引。
主键索引的叶子结点存放的是整行数据，在InnoDB中，主键索引也被称为聚簇索引。
非主键索引的叶子结点内容是主键的值，在InnoDB中，非主键索引也被称为二级索引。

2.1.1、基于主键索引和普通索引的查询区别

主键查询
```
select * from T where Id = 500
```
这样就是基于主键查询，那么只需要搜索Id这棵B+树。
普通索引查询
```
select * from T where k = 5
```
这样就是普通索引查询，那么就需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次，这个过程称为回表。
结论：基于非主键索引的查询需要多扫描一个索引树。因此，在应用中应该尽量使用主键查询。

2.1.2、索引维护

B+树为了维护索引的有序性，在插入新值的时候需要做必须的维护。
1. 插入一个新的行，ID值为700，那么此时，只需要在R5记录后面插入一个新的记录。如果插入的ID值为400，那么会麻烦一点，需要逻辑上挪动后面的数据，空出位置。
2. 如果R5所在的数据页已经满了，那么根据B+树的算法，这个时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。此时，性能自然会收到影响。
3. 页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。
4. 有分裂就有合并。当相邻两个页由于删除数据，利用率很低之后，会将数据页做合并。合并过程可以理解为分裂的逆过程。
基于维护索引过程说明，讨论一个案例：哪些时候需要使用自增主键，而哪些时候不应该使用。
1. 自增主键是指自增列上定义的主键，定义：not null primary key auto_increment。
2. 插入新的记录的时候，可以不指定ID值，系统会获取当前最大的值加1作为下一条记录的ID值。
3. 自增主键的插入数据模式正符合了前面提到的递增插入的场景。每次插入一条新纪录，都是追加操作，都不会涉及到挪动其他记录，也不会触发叶子结点的分裂。
4. 而有业务逻辑的字段做主键，往往不容易保证有序插入，这样写数据成本相对较高。
5. 从存储空间看：假设表中确实有一个唯一字段，比如字符串类型的身份证号，那么应该用身份证号作为主键，还是用自增字段做主键？
6. 由于每个非主键索引的叶子结点上都是主键的值。如果用身份证号作为主键的话，那么每个二级索引（非主键索引）的叶子结点占用18个字节，但如果用整型做主键，那么只需要4个字节，如果是bigint就是8个字节。
7. 主键长度越小，普通索引的叶子结点就越小，普通索引占用的空间也就越小。
8. 那么从性能和存储空间考虑，自增主键是更合理的选择。
9. 那什么时候使用业务字段直接做主键？
10. 比如：只有一个索引、该索引必须是唯一索引。由于没有其他索引，也就不需要考虑其他索引的叶子结点大小了。
11. 此时，要优先考虑到“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

3、小结

当有大量数据的时候，二叉树会很高，会有很多层，而每一层的数据是不太可能连续的（导致随机IO次数增加），这样导致磁盘在找每一层数据的时候会有加高的寻址代价。
B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次数。
由于InnoDB是索引组织表（表都是根据主键顺序以索引的形式存放的），一般情况下建议创建一个自增主键，这样非主键索引占用空间最小。但也考虑到只有一个索引的情况。
主键索引的叶子结点存放的是整行数据，普通索引叶子结点存储的是主键值。所以修改主键索引会影响普通索引，修改主键索引和普通索引，主要考虑的问题是减少修改主键索引对普通索引的影响。

4、回顾

建表以及插入数据

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
s varchar(16) NOT NULL DEFAULT '',
index k(k))
engine=InnoDB;
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

查询

select * from T where k between 3 and 5

这条语句，执行了多少次树的搜索操作，以及会扫描多少行？
可以得到InnoDB的索引组织结构：
查询流程：
1. 先去k索引树查找k=3的记录，得到ID=300；
2. 再到ID的索引树查到ID=300对应的R3；
3. 又去k索引树取下一个值k=5，得到ID=500；
4. 再回到ID索引树查到ID=500的R4；
5. 再在k索引树取下一个值k=6，不满足条件，循环结束。
回到主键索引树搜索的过程称为回表。在查询过程中读了k索引树的3条记录（1、3、5），回表了两次（2、4）。因为查询结果所需要的数据只在主键索引上有，所以不得不回表。可否经过索引优化，避免回表？

5、覆盖索引

如果执行的是select ID from T where k between 3 and 5，因为只查询ID的值，并且ID的值已经在k索引树上了，那么可以直接提供查询结果，不需要回表。即：在这个查询中，索引k已经覆盖了查询需求，这就是覆盖索引。
因为覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
在引擎内部使用覆盖索引在索引k上其实读了3个记录（1、3、5）。但是对于MySQL的Server层来说，就是找到引擎拿到了两条记录，因此MySQL认为扫描行数是2。

5.1、问题讨论

在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

建表

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

身份证号是市民唯一标识，即：如果有根据身份证号查询市民信息的需求，只需要在身份证号字段上建立索引就可以了，何需再建立一个（身份证号、姓名）的联合索引，是否浪费空间？

其实问题就是是否有必要根据这个高频请求而再去创建一个（身份证号、姓名）的联合索引。

若现在有一个高频请求，是要根据市民身份证号查询他的姓名，那么此时这个联合索引就有意义。可以在这个高频请求上用到覆盖索引，就不再需要回表查整行记录了，减少执行时间。

6、最左前缀原则

如果为每一个查询都设计一个索引，索引是否太多了。单独为一个不频繁的请求创建一个索引又感觉浪费空间，应该如何做？
B+树这种索引结构，可以利用索引的最左前缀来定位记录。
使用（name, age）这个联合索引来分析

索引是按照索引定义里面的出现字段顺序进行排序的。
当查询逻辑需求是查到所有名字是“张三”的人的时候，可以快速定位到ID4。
如果使用模糊查询，差的是所有名字第一个字是“张”的人，查找到的第一个符合条件的记录是ID3，然后向后遍历，直到不满足条件为止。
可以看到，不只是索引的全部定义，只要满足最左前缀，就可以利用索引加速检索。这个最左前缀可以是联合索引的最左N个字段（属性），也可以是字符串索引的最左M个字符。

6.1、问题讨论

如何安排索引内的字段顺序？
评估标准：索引的复用能力。因为可以支持最左前缀，所以当已经有了（a,b）这个联合索引的时候，一般是不需要单独在a上面建立索引了。因此，第一原则：如果通过调整顺序，可以少维护一个索引，那么这个索引顺序往往就是需要优先考虑采用的。
所以对于上述身份证问题，要为高频请求创建（身份证号，姓名）这个联合索引，并用这个索引支持“根据身份证号查询地址”的请求，这样是可以查到主键的id，然后再回表查找到确切的地址。
如果既有联合查询，又有基于a、b各自的查询？查询条件里面只有b的语句，是无法使用（a，b）这个联合索引的，此时不得不维护另外一个索引，也就是说同时需要维护（a，b）、（b）这两个索引。
第二个原则：空间。市民表中，name字段比age字段大，45讲建议创建一个（name，age）的联合索引以及一个（age）的单独字段索引。其实就是字段长的只建立一次，短的建立两次。

6.2、最左前缀失效

其实失效只能说没有发挥出联合索引应该具备的功能，并非这个什么也做不了。
当使用联合索引查询select * from tuser where name like '张%'，这样的话，假设上面的叶子结点顺序是

注：有使用到联合索引，排序的是先按照第一个索引字段进行排序，排完之后，再按照第二个索引字段进行排序，其他的字段不管。

(“张六”，30)	(“张三”，10)	(“王五”，10)	(“李四”，20)
ID3	ID4	ID2	ID1

那么此时查询，因为使用模糊查询，那么索引就直接先找到ID1，然后索引就失效了，也就是后面其他的记录就会筛选进来，然后再进行回表的操作。

所以失效的场景就是使用了like、between、比较大小等。这些就会导致联合索引的失效。此时只能快速的定位到满足未失效索引字段的第一个记录，而没有失效的话，定位到的第一个记录未必与失效定位到的记录是同一个。

7、索引下推

最左前缀可以用于在索引中定位记录，那么那么不符合最左前缀的部分会如何？
同样以联合索引（name，age）为例。现在的需求：检索出表中“名字第一个字是张，且年龄是10岁的所有男孩”
```
select * from tuser where name like '张%' and age=10 and ismale=1;
```
1. 前缀索引规则在搜索索引树的时候，只能用“张”找到第一个满足条件的记录ID3。这肯定比扫描全表好，然后呢？
2. 在MySQL5.6之前，只能从ID3开始一个个回表。到主键索引上找出数据行，然后对比字段的值。
3. MySQL5.6引入索引下推优化，可以在索引遍历的过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，这样可以减少回表的次数。
4. 无索引下推执行流程：因为是最左前缀原则进行查找，此时InnoDB不会去查看age的值，只是按顺序把第一个字是“张”的记录一条条的取出来进行回表。那么进行四次回表操作。
5. 有索引下推的执行流程：此时InnoDB在（name，age）索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。这样减少了回表的次数，下图执行流程中，只回表了2次。
  
  每一个虚线箭头表示回表一次。

8、课后思考题

建表

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

问题：既然已经单独的为字段c创建了一个索引，为什么需要创建“ca”和“cb”两个索引？
分析：主键索引的叶子结点存放的是整行数据，而普通索引的叶子结点存放的是主键值。
1. 此时的主键是使用了a，b两个字段。那么就是按照先a后b的顺序，c无序。

a	b	c	d
1	2	3	d
1	3	2	d
1	4	3	d
2	1	3	d
2	2	2	d
2	3	4	d

2. 索引ca就是先c后a排序，同时记录主键，主键只有b而不是ab，因为InnoDB会把主键字段放在索引定义字段后面，同时也会去重，所以当主键是（a，b）的时候，再定义c的索引，实际上是（a,b,c）；定义（c，a）的索引，实际上是（c，a，b）；定义（c，b）的索引，实际上是（c，b，a）。

c	a	主键（只有b）
2	1	3
2	2	2
3	1	2
3	1	4
3	2	1
4	2	3

和索引c是一样的

3. 索引cb就是先c后b排序，同时记录主键，此时主键也只有a。

c	a	主键（只有a）
2	2	2
2	3	1
3	1	2
3	2	1
3	4	1
4	3	2

结论：所以只用保留cb，可以去掉ca。