MySQL索引之我见 mysqlsql

MySQL索引之我见之前在电梯里听见有个帅哥说：“Sql看着很简单，但是想写好却要花点心思”。当时沉迷于他的侧颜，对这句话却不以为然。大二的时候，在一门公选课的作业里，第一次接触Sql，之后便没有再碰过。因此，对Sql的掌握程度一直停留在表面的语法上。最近抽空重拾Sql的时候，发现自己在写Sql时，会有点迷茫，比如一个查询可以有很多种写法，为什么这样写查询效率会比较高；每次创建数据表的时候都会设置索引，知道索引可以提高查询效率，但是对于它底层的工作机制却一无所知。这些疑问都会导致我在优化Sql语句的时候，有点无从下手。这里也想感慨一下自己的思辨思维的变化，以前做事情都只会停留在表面，觉得自己对这件事有个大概的了解就行了。慢慢地，我发现如果对事情的起因以及背后的机制不了解清楚，那么我就很难处理好这件事情。虽然这个世界不允许我花太多的时间去挖掘每个物体背后的本质，但我也要尽力地去消除物体背后的“视觉盲区”。
因此，我花了一些时间去了解Sql索引背后的机制。在这里感谢所有提供这方面知识的创作者，本文的内容也是在他们的创作内容的基础上进行总结的。我用的RDBMS是MySQL，因此本章会对MySQL的InnoDB存储引擎的一些知识点的梳理。在第一部分，本文阐述了索引存储结构的重要性；在第二部分，本文在简要介绍了数据库在磁盘中存储的基本单位；在第三部分，本文在理论上论述了InnoDB不采取二叉树作为索引存储结构的原因；在第四部分，本文介绍了InnoDB如何使用B+树存储索引；在第五部分，本文对二级索引和联合索引的存储结构进行了介绍；在第六部分，本文罗列一些使用索引的Tips；最后，本文在第七部分进行了总结。本文可能会有一些理解上或者表达上的错误，望指出，望海涵。
1. 背景
我们先来讲一讲InnoDB存储引擎的那些事儿。在InnoDB存储引擎中，数据和索引是位于磁盘上的。这意味着，我们每次查询数据的时候，都需要把磁盘上的数据读进内存中。相对于内存的存取来说，磁盘 I/O 需要消耗的时间比较多。当我们使用索引查询数据行时，如果发生的磁盘 I/O 次数越多，则消耗的时间就会越多。因此，如何让每次查询的磁盘 I/O 次数尽可能少，是数据库查询的关键问题之一。在InnoDB存储引擎中，索引结构是以B+树的数据结构存在于磁盘上的。在接下来的内容中，我们会详细地解释为什么InnoDB会选择B+树来存储索引。
2. 数据库存储的基本单位
【MySQL索引之我见】数据库在磁盘中存储的基本单位是页，数据库磁盘I/O 执行的最小单位也是页。其实很好理解，如果每次磁盘 I/O 只读取一行数据，这种方法的执行效率会很低。查看MySQL的InnoDB存储引擎的页大小的命令如下：

show variables like '%innodb_page_size%';

在InnoDB中，默认页大小是16k。常见的页类型有数据页（保存B+树节点）、系统页、Undo页和事务数据页等。我们了解一下最常用的数据页的结构。数据页的结构如图2-1所示。

图2-1 数据页结构
文章图片

其中，关于数据页结构中的每一部分的作用如下：
（1）文件头和文件尾可以主要是起到校验的作用，比如我们在传输一个数据页的时候由于某些原因导致传输断开，这时候通过对比文件头和文件尾的校验和，如果不相等则说明传输有问题并进行重传，反之则说明传输过程没有问题；
（2）用户记录用于记录数据，而空闲空间用于给新的用户记录腾空间；
（3）用户记录在页中是以单链表的数据结构存在的，最小记录和最大记录是InnoDB为每个页添加的虚拟记录，其中最小记录为单链表的头节点，最大记录为单链表的尾节点；
（4）单链表存储数据的好处在于插入和删除数据行很方便，但是检索的效率却不高，极端情况下可能要遍历链表上的所有节点才能完成一次检索。因此，InnoDB提供了二分查找的方式，用来提高记录的检索效率。二分查找的示意图见图2-2，可见该查找过程用到了数据页的页目录结构，页目录结构实际上是部分记录的索引。具体地，页目录的创建过程如下：

将用户记录和最小最大记录进行分组，其中第一个分组只有最小记录这一条记录，最后一个分组包含了最大记录且记录条数的范围在1-8之间，剩下的分组的记录条数在4-8之间；
每个分组的最后一条记录的头信息会存储改组的记录条数（图2-2中的粉红色字段）
每组最后一条记录的地址偏移量称之为每个分组的槽（Slot），每个分组的Slot相当于该分组的索引，这些Slot存储在页目录中。

利用页目录，可以对用户记录进行二分查找。比如，在图2-2中，我们要查找主键为11的用户记录，整个查找过程如下：

首先，计算中间槽位\( (0+4)/2=2 \)，2号槽对应的分组的主键最大值为8，11比8大，因此11在2号分组之后；
计算下一个目标槽位\( (2+4)/2=3 \)，2号槽对应的分组的主键最大值为12，11比12小，因此键值11的记录就在3号分组里；
对3号分组中的链表，从头遍历（即从键值为9的记录开始遍历），直到找到键值11的用户记录。值得一提的是，上述提到每个分组中的记录条数不会超过8，因此在这里对分组中的链表进行遍历不会影响检索效率。

此外，当用户记录的条数比较多时，一个数据页存放不下这么多数据，于是剩下的数据就存放在另外的数据页上，且每个数据页存放的数据彼此之间有顺序关系，比如数据页1存放了键值1-16的用户记录，数据页2存放了键值17-32的用户记录等。在磁盘上，页与页之间使用一个双向链表进行链接，这意味着页的分布在逻辑上是连续的，在物理上不一定是连续的。在数据页结构中的文件头有两个字段，分别是 FIL_PAGE_PREV 和FIL_PAGE_NEXT，它们的作用相当于指针，分别指向上一个数据页和下一个数据页。

图2-2 InnoDB数据页二分查找示意图
文章图片

3. 为什么不使用二叉树作为索引结构
树是一种数据结构。其中，二叉查找树（Binary Search Tree，简称BST）是一种特殊的二叉树。对于BST上的每个节点\( node \)，如果存在左子节点\( node_{left}\)，则\( node_{left}\)上的值必定小于\( node \)的值；如果存在右子节点\( node_{right}\)，则\( node_{right}\)上的值必定大于\( node \)的值。假设我们有一个100万行的数据表，其中一个唯一索引是\( id \)。如果我们使用遍历的方式去查找\( id=n \)，\( n=1,...,1e6 \)，的一行数据，则复杂度为\( O(N) \)。而如果我们使用BST来存储索引\( id \)，则查询数据的复杂度为\( O(log_2n) \)，BST的查找过程如图3-1所示。既然如此，InnoDB为何不用BST来存储索引呢？这是因为我们没有办法保证BST是一棵平衡二叉树。如图3-2所示，图中的树结构仍然属于BST结构，但是这时候的BST已经退化为一个链表，此时的查询复杂度为\( O(N) \)。

图3-1 BST查找过程	图3-2 BST退化为链表
文章图片	文章图片

为了解决BST会退化成链表的问题，俄罗斯数学家 G. M. Andel'son-Vel-skii 和 E. M. Landis 提出了平衡二叉查找树（AVL树）。AVL树在BST的基础上，增加了一个约束：每个节点的左子树的高度与右子树的高度之差不能大于1。实际上，图3-1中的树结构就是一棵AVL树。那既然问题都解决了，为什么InnoDB不用AVL树存储索引呢？主要是因为数据库在磁盘中的存储的基本单位是页，如果使用AVL树作为索引存储结构，则每一页（对应AVL树上的每个节点）中只有一行数据。当索引取值范围比较广的时候，存储索引的AVL树的高度会非常高。由于只有索引的根节点是在内存中，因此磁盘 I/O 次数会比较多。因此，使用AVL树也不能解决查询时磁盘 I/O 次数过多的问题。
4. 为什么使用B+树作为索引结构
为了更好地阐述B+树索引结构，我们以表4-1中的数据作为例子。从该表中可以发现，叫Alice的人应该都是社交达人（狗头）。切回正题，在InnoDB中，索引结构可以分为聚集索引和非聚集索引（二级索引）。其中，聚集索引可以理解为是数据表的主键（比如表4-1中的Id字段）在磁盘上的存储结构，聚集索引是会存储真正的数据行的。而二级索引是存储主键之外的其它索引在磁盘中的存储结构，比如当我们将下表中的“朋友数量”字段设为索引时，它就会以二级索引的结构存在于磁盘中，且二级索引不包含真正的数据行（详见下一节）。

表4-1 数据样例
文章图片

在这一章中，我们先来聊一聊聚集索引的存储结构。我们假设每个磁盘中每个数据页的目录页只有两个槽（实际情况肯定远大于两个槽，我们这里仅仅是为了画图方便），则数据表4-1的聚集索引的存储结构如图4-1所示。在二叉树中，每个数据页只存储了一行记录。而在B+树中，每个数据页都存储了若干条记录。并且，在B+树的非叶子节点中，某几个键值将数据划分为若干个部分，且这些键值所在节点包含了一个指向对应部分所在数据页的指针。举例而言，在图4-1中，数据页1用\( id=1 \)和\( id=7 \)将所有id值划分为两个部分（即1-6和7-12），且\( id=1 \)所在的记录存储了指向1-6所在数据页的指针，\( id=7 \)所在的记录存储了指向7-12所在数据页的指针。
可以看出，这些非叶子节点都没有存储真正的数据，数据表4-1的每一行数据只存在于叶子节点中。这么设计的原因，是为了让非叶子节点存储更多的键值节点，让整个B+树的高度变矮，从而有效地减少磁盘 I/O次数。可以这样理解，B+树的根节点存在于内存中，其他节点都在磁盘里。假设B+树每个节点都分裂为1000个节点（也称为1000阶）且树的高度为3，则意味着共有10亿条记录，当我们要检索某一条记录时，只需要2次磁盘 I/O！这是很令人惊喜的结果，也很好地说明了在创建数据表时索引的重要性！
我们接着来看看，假设我们的查询语句是

select * from Table4_1 where id = 3;

则整个查询过程如下（见图4-1中的红色虚线框）：
（1）首先，根节点在内存中，3大于1且3小于7，所以下一个要访问的数据页是页2；
（2）在磁盘中将数据页2读进内存，3大于1且3小于4，所以下一个要访问的数据页是页4；
（3）在磁盘中将数据页4读进内存，根据第二章提到的目录页去查找\( id=3 \)所在的记录。
此外，如果我们要查询的语句是

select * from Table4_1 where id between 3 and 6;

则整个查询过程如下：
（1）执行图4-1中的红色虚线框，找到数据页4中的键值为3的记录；
（2）执行图4-1中的红色箭头，找到键值id为3-6的所有记录。
这里需要提一嘴的是，在实际上，InnoDB每次从磁盘读数据时，不是一个页一个页读取的，而是顺序读取若干个数据页（具体数量和磁盘吞吐有关）。因此，在图4-1中，实际上可能一次I/O就可以了。我们这里这样描述，仅仅是为了方便说明索引的查询过程。InnoDB顺序读取是和它的缓存设计有关的，如果有时间，这部分知识我也会进行整理。

图4-1 聚集索引示意图
文章图片

5. 二级索引和联合索引的存储结构
5.1 二级索引在第四章中，我们介绍了聚集索引在磁盘中的存储结构以及聚集索引的查询过程。接下来，我们来看看二级索引是怎么存储的。我们以表4-1中的“朋友数量”字段为例。我们将“朋友数量”设为索引，则其存储结构如图5-1所示。显然，二级索引和聚集索引不同的地方在于，二级索引的叶子节点不存储真正的的数据行，而是存储对应的主键值。这样做的目的是为了节省存储空间，尽可能让二级索引的B+树的高度变矮。如果我们执行以下命令：

select * from Table4_1 where number_of_friends = 19;

则，当我们在二级索引的B+树中找到“朋友数量”为19的主键值3后，还要去图4-1中的聚集索引的B+树去寻找真正的记录行，这个过程称为回表。具体地，整个指向过程为先执行图5-1中的黄色虚线框，再执行图4-1中的红色虚线框。值得一提的是，在MySQL5.6之后，叶子节点中除了存储主键值还存储了二级索引对应的值，例如我们执行如下命令：

select id,number_of_friends from Table4_1 where number_of_friends = 19;

则这种情况是不需要回表的，这称为索引覆盖。我们用实际例子来演示一下，如图5-2所示，当我们select后面的字段是id和索引number_of_friends时，执行计划的Extra的值为using index，这意味着此时为索引覆盖，不需要回表。

图5-1 二级索引示意图
文章图片

图5-2 索引覆盖实例
文章图片

5.2 联合索引我们再来看看联合索引的存储结构。我们使用如下命令把表4-1中的（姓名，朋友数量）设置为一个联合索引：

alter table Table4_1 add unique index (name,number_of_friends);

则该联合索引的存储结构如图5-3所示。当我们执行以下命令

select * from Table4_1 where name = 'Alice' and number_of_friends = 12;

则查询过程如下（见图5-2中的红色虚线框）：
（1）首先，根据联合索引中的第一个索引name='Alice'，下一个要读取的数据页为页2；
（2）将磁盘中的数据页2读进内存。由于在数据页2中，联合索引的第一个索引name的值相同，于是开始比较第二个索引（即number_of_friends）的值。我们要查找number_of_friends=12，而12大于8且12小于18，所以下一个读取的数据页为页5；
（3）将磁盘中的数据页5读进内存。使用数据页5的目录页找到number_of_friends=12的记录，主键id的值为2。
（4）根据主键id的值，在图4-1中的聚集索引进行回表查询。

图5-3 联合索引示意图
文章图片

所以，联合索引和单个二级索引是很相似的，根据联合索引定义的索引顺序进行查询即可。但是，有两个需要注意的点：
（1）在等值查询中，查询的索引顺序是没有关系的，比如我们使用以下的查询语句

select * from Table4_1 where number_of_friends = 12 and name = 'Alice';

MySQL优化器会自动调整索引顺序的，因此该语句仍然可以利用到上述定义的联合索引。
（2）在不等值查询时，会存在联合索引失效的情况，比如我们使用以下的查询语句

select * from Table4_1 where name >= 'Bob' and number_of_friends =9;

执行该语句时，图5-2中的数据页1有两个节点都是满足name >= 'Bob'的，那么第二个索引要往页3走还是往页4走呢？这就很矛盾。因此，这时对于字段number_of_friends就没办法走索引了。
（3）在上述中，我们发现联合索引都是按照联合索引的索引顺序进行查询的。对于联合索引（姓名，朋友数量），如果只提供朋友数量作为索引，则没有办法利用联合索引。因此，当我们要使用联合索引时，左边的索引必须要有，这也称为联合所以的最左匹配原则。我们用实际例子进行观测，如图5-4。如果只使用字段“朋友数量”进行查询时，图5-4的执行计划中的key=Null意味着没有利用到索引，type=ALL意味着此语句使用的是全表扫描。

图5-4 最左匹配原则
文章图片

6. 使用索引的一些Tips
本章罗列一些不适合使用索引以及索引可能会失效的情况。
6.1 不适合使用索引的情况（1）当某个字段的取值重复率高达百分之10%以上时，是没必要使用索引的。比如，数据表中的性别字段，一般情况下（挠头.jpg），只有男和女两种情况。如果我们将该字段设置为索引，则查询所有性别为女的记录时还要对几十万条的数据进行回表。这种时候，使用索引的执行效率还不如直接全表扫描。我们用实际例子来证明一下。我在Mysql中的一个数据表，使用以下语句查询10000条性别为女的记录，

select * from user_gender where user_gender=0 limit 10000;

首先直接对user_gender字段进行全表扫描，执行时间为0.01s。接着，我们使用以下语句将user_gender字段设置索引，

alter table user_gender add index (user_gender);

再执行查询10000条数据的语句，执行时间为0.03s。可见，对于这种重复值过多的字段，当将其设置为索引时，回表时间会严重影响执行效率。
（2）当数据表的记录数很小时，没必要采用索引。比如对于以下表格，

文章图片

只有13行数据，age字段不是索引，id字段是主键，我们分别使用age和id进行查询，如下图，发现执行效率没什么任何区别。

文章图片

（3）当字段是字符型的，且长度可能比较大（比如url），此时考虑到数据页的大小有限，这些字段不适合作为索引。
6.2 索引失效的情况（1）如果对索引进行了表达式计算，则会失效。
比如对于表4-1，我们使用主键id进行查询时，对其进行了计算，执行计划如下图，

文章图片

发现，此时为全表扫描（type=all），因此索引失效。
（2）如果对索引使用函数，则会造成失效。
比如对于表4-1，我们将“姓名”字段设置为索引，当我们使用length()函数时，执行计划如下图，

文章图片

发现，此时为全表扫描（type=all），因此索引失效。
（3）在where子句中，关键字or前面的字段是索引，而后面的字段不是索引，则会失效
比如对于表4-1，我们将“姓名”和“朋友数量”字段设置为索引，而新增加的“年龄”字段不是索引，则执行计划如下图，

文章图片

发现，如果or后面的字段是“年龄”，则为全表扫描（type=all），因此索引失效。值得一提的是，在第一个sql语句中，执行计划的type为index_merge，这里的意思是将两个索引的查询结果进行合并。
（4）使用关键字like时，前面不是%
比如对于表4-1，我们将“姓名”字段设置为索引，执行计划如下图，

文章图片

发现，如果使用like关键字，%符号在后面仍然可以使用索引，而在前面时，则索引失效。
（5）索引列尽量设置为NOT NULL约束
当我们查找对索引查找not null值时，往往是全表扫描，如下图。

文章图片

因此，我们在创建数据表时，要养成给空值数据添加默认值的良好习惯。
（6）使用联合索引时，没有按照最左匹配原则
7. 总结
本文在网路上许多优秀创作者的创作内容上，对MySQL中的InnoDB存储引擎的索引机制做了一个简单的总结，以便自己以后回顾这些知识点。这是鄙人的第一篇博文，行文粗糙，有许多不足之处，望海涵。
Reference List
[1] https://bbs.huaweicloud.com/b...
[2] https://time.geekbang.org/col...