吊打面试官之深入浅出 MySQL 索引底层原理

吊打面试官之深入浅出 MySQL 索引底层原理

1、前置知识

在了解 MySQL 索引的底层原理之前，我们需要知道 MySQL 中数据页的相关知识，知道各个数据页可以组成一个双向链表，而每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。页和记录的关系示意图如下：

页a、页b、页c … 页n可以不在物理结构上相连，只要通过双向链表关联即可。

2、没有索引的查找

为了简化理解，假设是对某个列精确匹配的情况，就是搜索条件中用 = 连接的表达式。

2.1 在一个页中查找

假设目前表中的记录比较少，所有的记录都可以被存放到一个页中，在查找记录的时候可以根据搜索条件的不同分为两种情况：

• 以主键为搜索条件

  在目录页中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

• 以其他列作为搜索条件

  对非主键列的索引过程就没这么幸运了，在数据页中并没有对非主键列建立所谓的页目录，所以我们无法通过二分法快速定位相应的槽。这种情况下只能从最小记录开始一次遍历单链表中的每条记录，然后对比每条记录是否符合搜索条件，这种效率是非常低的。

所谓最小记录，在每个页中，都会存在一个最小记录和最大记录，最小记录指向页中的第一条记录，最后一条记录指向最大记录。

2.2 在多页中查找

大部分情况下表中的记录时非常多的，需要好多的数据页来存储这些记录，在很多页中查找记录的话可以分为两个步骤：

1. 定位到记录所在的页
2. 从所在的页中查找相应的记录

在没有索引的情况下，无论是根据主键列或者其他列值进行查找，由于并不能快速定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下查找，在每一个页中根据我们刚说的查找方法区查找指定的记录。由于要遍历所有的数据页，所有这种方式是很耗时的，于是索引横空出世了。

3、索引

为了下文讲解，我们先创建一个表：

mysql> CREATE TABLE index_demo(
-> c1 INT,
-> c2 INT,
-> c3 CHAR(1),
-> PRIMARY KEY(c1)
-> ) ROW_FORMAT = Compact;

这个表使用的是 Compact 行格式来存储记录的，里面几个字段我们先了解一下，如果不清楚记录行格式的，可以自行了解一下：

这里我们简化了一下：

• record_type：记录头信息的一项属性，表示记录的类型：0表示普通记录，2表示最小记录，3表示最大记录，1表示目录项记录（这个稍后再说）。
• next_record：记录头信息的一项属性，表示下一条地址相对于本条记录的地址偏移量，我们使用箭头来表示。
• 各个列的值：就是我们创建的几个列。
• 其他信息：除了上述3中信息之外的所有信息，包括其他隐藏列的值以及记录的额外信息。

那么将多条记录放到一个数据页里面，就是如下这样：

3.1 一个简单的索引方案

我们在根据某个搜索条件查找一些记录的时候为什么要遍历所有的数据页呢？

因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以不得不依次遍历所有的数据页。如果想要快速定位要需要查找记录的数据页怎么办？我们为根据主键值快速定位一条记录在页中的位置设立的页目录，于是我们也可以为快速定位到记录所在的数据页而建立一个别的目录，这个目录需要满足如下要求：

• 下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值。

此时我们像 index_demo 表中插入三条记录，假设一个数据页只能插入3条记录，实际上数据页16KB，可以插入很多条记录。

mysql> INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');

那么这些记录在一个数据页中根据主键值串成一个单链表，如图：

如图可以知道，这三条记录都被插入到了编号为10的数据页中了，我们此时再插入一条记录：

mysql> INSERT INTO index_demo VALUES(4, 4, 'a');

由于一个页只能插入3条记录，所以不得不再分配一个新的页，如图所示：

图中可以看到，新分配的数据页编号可能并不是连续的，也就是说我们使用这些页在存储空间里可能并不挨着。只是通过维护上一个页和下一个页的编号建立了链表关系。

我们仔细看图就会发现，页28中的一条记录的主键是4，小于页10记录最大的主键5，这就不符合下一个数据页中用户记录的主键值必须大于上一页中用户记录的主键值的要求，所以插入主键值为4的记录时就需要伴随着一次记录移动，交换主键值为4和主键值为5记录的位置。

这个过程表明了在对页中的记录进行增删改操作的过程中，必须通过一些诸如记录移动的操作来始终保证这个转态一直成立：下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值，这个过程可以称为页分裂。

由于数据页的编号可能并不是连续的，在想表中插入多条记录后，可能是这样的效果：

但是主键值始终是连续递增的。由于这些页在物理存储上可能并不挨着，所以如果想从这么多页中根据主键值快速定位到某些记录所在的页，需要给他做个目录，每个页对应一个目录项，每个目录项包括下边两个部分：

• 页的用户记录中最小的主键值，用key表示。
• 页号，用page_no表示。

那上边的几个页就会是下面这个样子：

以页28为例，它对应目录项2，这个目录项中包含着该页的页号28以及该页中用户记录的最小主键值5。我们只需要把几个目录项在物理存储器上连续存储，比如把它们放到一个数组里，就可以实现根据主键值快速查找某条记录的功能了。比如查找主键值为20的记录：

• 先从目录页中根据二分法快速确定出主键值为20的记录在目录项3中（12 < 20 < 309），它对应的页是页9。
• 再根据前边说的在页中查找记录的方法去页9中定位具体的记录。

这个目录有一个别名，就是索引。

3.2 InnoDB 中的索引方案

上边之所以称为一个简易的索引方案，是因为我们为了在根据主键值进行查找时使用二分法快速定位具体的目录项而假设所有目录项都可以在物理存储器上连续存储，但是这样做有如下几个问题：

• InnoDB是使用页来作为管理存储空间的基本单位，也就是最多能保证16KB的连续存储空间，而随着表中记录数量的增多，需要非常大的连续的存储空间才能把所有的目录项都放下，这对记录数量非常多的表是不现实的。
• 我们经常会对表进行增删，假设把页28中的记录都删除了，页28也就没有存在的必要了，那意味着目录项2也就没有什么存在的必要了，就需要把目录项2后的目录项都向前移动一下，这种设计可不是好主意。

所以需要一种可以灵活管理所有目录项的方式，于是InnoDB复用了之前存储用户记录的数据页来存储目录项，为了和用户记录做一下区分，把这些表示目录项的记录称为目录项记录。那怎么区分一个记录是普通记录还是目录项记录呢，别忘了我们上边说过的 record_type 属性，改值为1就是代表目录项记录。

那我们把目录项放到数据页中就是下面这个样子的：

从图中可以看出，我们用编号为30的页来专门存储目录项记录。这个存储目录项的页和存储用户记录的页都是一样的数据页，也都会为主键值生成页目录，按照主键值进行二分查找。比如我们现在要查找主键值为20的记录：

• 先到存储目录项记录的页，也就是页30中通过二分法快速定位到对应的目录项，找到对应的记录所在的页是页9。
• 再到存储用户记录的页9中根据二分法快速定位到主键值为20的用户记录。

虽然说目录项记录中只存储主键值和对应的页号，比用户记录需要的存储空间小多了，但一个页大小也就是16KB，能存放的目录项记录也是有限的，如果表中的数据太多，以至于一个数据页不足以存放所有的目录项记录，怎么办呢？

当然是再多整一个目录项记录的页，其过程和上边讲的页分裂是差不多的。比如我们插入一条主键值为320的用户记录的话，就需要分配一个新的存储目录项记录的页（这里假设一个页中只能存储4条目录项记录，实际上可以存储很多条），如下图：

从图中可以看出，插入一条主键值为320的用户记录之后需要两个新的数据页：

• 为存储该用户记录而生成的页31
• 由于原先存储目录项记录的页30已满，不得不需要一个新的页32来存放页31对应的目录项。

由于目录项记录的页不止一个，现在查找一条用户记录的步骤如下：

• 确定目录项纪录的页。
• 通过目录项记录页确定用户记录真实所在的页。
• 在真实所在的页中定位到具体的记录。

在这个查询步骤的第一步中需要定位到目录项记录的页，但是这些页在存储空间中可能并不挨着，如果表中的数据非常多则会产生很多存储目录项记录的页，那我们怎么根据主键值快速定位一个存储目录项记录的页呢？

我们可以为这些储存目录项记录的页再生成一个更高级的目录，就像是一个多级目录一样，如下图：

如图，我们生成了一个更高级目录的页33，页中的两条记录分别代表页30和页32。随着表中记录的增加，这个目录的层级会继续增加，简化一下就是下面这个样子：

看图可知，这就像一个树，它的名字是 B+ 树。

在一个树中，我们称数据页为一个节点，实际用户记录其实都存储在B+树的最底层的节点上，这些节点被称为叶子节点或叶节点。其余用来存放目录项的节点称为非叶子节点或内节点，最上边的节点称为根节点。这个就是MySQL中 InnoBD 存储引擎中的所有的庐山真面目了。

3.2.1 聚簇索引

上边介绍的B+树本身就是一个目录，或者说本身就是一个索引，有两个特点：

• 使用记录主键值的大小进行记录和页的排序
  • 页内的记录是按照主键的大小顺序排成一个单项链表
  • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
  • 存放目录项记录的页分为不同的层次，在同一层次中的页根据目录项记录的主键大小顺序排成一个单向链表
• B+树的叶子节点存储的是完整的用户记录，所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处，这个聚簇索引并不需要我们在MySQL中使用INDEX语句去创建，InnoDB 存储引擎会自动的为我们创建聚簇索引。在 InnoDB中，聚簇索引就是数据存储的方式，即所谓的数据即索引，索引即数据。

3.2.2 二级索引

上面的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是主键进行排序的，那如果想以别的列作为搜索条件怎么办呢？

我们可以多建几颗 B+ 树，不同的 B+ 树采用不同的排序规则，比如我们用 c2 列的大小作为数据页、页中记录的排序规则，再建一颗二叉树，如图：

这个索引与上边介绍的聚簇索引有几处不同：

• 使用记录 c2 列的大小进行记录和页的排序
  • 页内的记录时按照 c2 列的大小顺序排成一个单向链表
  • 各个存放用户记录的页也是根据页中记录的 c2 列大小顺序排成一个双向链表
  • 存放目录项记录的页分为不同层次，在同一层次中的页也是根据页中目录项记录的 c2 列大小排序成一个单向链表。
• B+ 树的叶子节点存储的并不是完整的用户记录，而只是c2列+主键这两个列的值。
• 目录项记录中不是主键+页号了，而是c2列+页号的了。

如果我们想通过 c2 列的值查找某些记录的话可以使用我们刚建好的这个B+树了，以查找 c2 列的值为4的记录为例：

• 确定目录项记录页
• 通过目录项记录页确定用户记录真实存在的页
• 在真实存储用户记录的页中定位到具体的记录
• 但是这个B+树中的叶子节点中的记录只存储了c2和c1（主键）两个列，所我以们必须再根据主键值去聚簇索引中在查找一遍完整的用户记录。

上边第四个步骤称为回表，我们查找一个记录需要使用到2棵B+树。那为啥不把用户记录放到以c2列为索引的B+树中呢？这样就不需要回表了啊。

要是这样的话，每建立一颗B+树都要把所有用户的记录都拷贝一份，太浪费内存空间了。这种按照非主键列建立的索引称为二级索引，或者辅助索引。

3.2.3 联合索引

我们也可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想按照c2和c3列的大小进行排序，这个包含两层含义：

• 先把各个记录的页按照c2列进行排序
• 在记录的c2列相同的情况下，采用c3列进行排序

为c2和c3建立的索引的示意图如下：

如图所示，我们需要注意几点：

• 每条目录项记录都由 c2、c3、页号这三个部分组成，各条记录先按照c2列的值进行排序，如果记录的 c2 列相同，则按照c3列的值进行排序。
• B+ 树的叶子节点处的用户记录由c2、c3和主键c1列组成。

以c2和c3列的大小为排序规则建立的B+树称为联合索引，本质上也是一个二级索引。它的意思分别为c2和c3列分别建立索引的表述是不同的，不同点如下：

• 建立联合索引只会建立如图上一样的1课B+树。
• 为c2和c3列分别建立索引会分别以c2和c3列的大小为排序规则建立2棵B+树。

3.3 MyISAM中的索引方案

到此我们都是讲的 InnoDB 存储引擎的索引方案，我们现在简单介绍一下MyISAM存储引擎中的所有方案。我们知道InnoDB 中索引即数据，也就是聚簇索引的那棵B+树的叶子节点已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也是使用树形结构，但是是将索引和数据分开存储的：

• 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就行了，可以通过行号而快速访问到一条记录。由于在插入数据的时候并没有按照主键大小排序，所有并不能在这些数据上使用二分法进行查找。
• 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM 会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录。这一点 InnoDB 是不同的，在MyISAM 中每次查询都需要一次回表，意味着 MyISAM 中建立的所有相当于全是二级索引。
• 如果有需要的话，我们也可以为其他的列分别建立索引或者建立联合索引，原理和 InnoDB 差不多，不过在叶子节点处存储的是相应的列 + 行号，这些索引页全部都是二级索引。

巨人的肩膀：

https://juejin.cn/book/6844733769996304392