【MySQL进阶】深入理解B+树索引底层原理

布满荆棘的人生 2023-09-23 18:52 177阅读 0赞

### 一、前言——没有索引的查找 ###

在正式介绍 索引 之前，我们需要了解一下没有索引的时候是怎么查找记录的。我们下边先只唠叨搜索条件为对某个列精确匹配的情况，所谓精确匹配，就是搜索条件中用等于 = 连接起的表达式，比如这样：

SELECT [列名列表] FROM 表名 WHERE 列名 = xxx;
    复制代码

#### 1、在一个页中的查找 ####

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

*  以主键为搜索条件
    
    这个查找过程我们已经很熟悉了，可以在 页目录 中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
 *  以其他列作为搜索条件
    
    对非主键列的查找的过程可就不这么幸运了，因为在数据页中并没有对非主键列建立所谓的 页目录 ，所以我们无法通过二分法快速定位相应的 槽 。这种情况下只能从 最小记录 开始依次遍历单链表中的每条记录，然后对比每条记录是不是符合搜索条件。很显然，这种查找的效率是非常低的。

#### 2、在很多页中查找 ####

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

*  定位到记录所在的页
 *  从所在的页内中查找相应的记录

#### 3、总结 ####

在没有索引的情况下，不论是根据主键列或者其他列的值进行查找，由于我们并不能快速的定位到记录所在的页，所以只能从第一个页沿着双向链表一直往下找，在每一个页中根据我们刚刚唠叨过的查找方式去查找指定的记录。因为要遍历所有的数据页，所以这种方式显然是超级耗时的，如果一个表有一亿条记录，使用这种方式去查找记录那要等到猴年马月才能等到查找结果。

### 二、索引 ###

我们先建一个表：

mysql> CREATE TABLE index_demo(
    -> c1 INT,
    -> c2 INT,
    -> c3 CHAR(1),
    -> PRIMARY KEY(c1)
    -> ) ROW_FORMAT = Compact;
    Query OK, 0 rows affected (0.03 sec)
    复制代码

为了我们理解上的方便，我们简化了一下 index\_demo 表的行格式示意图：

![format_png][]

把一些记录放到页里边的示意图就是：

![format_png 1][]

#### 1、一个简单的索引方案 ####

回到正题，我们在根据某个搜索条件查找一些记录时为什么要遍历所有的数据页呢？因为各个页中的记录并没有规律，我们并不知道我们的搜索条件匹配哪些页中的记录，所以 不得不 依次遍历所有的数据页。所以如果我们想快速的定位到需要查找的记录在哪些数据页中该咋办？还记得我们为根据主键值快速定位一条记录在页中的位置而设立的页目录么？我们也可以想办法为快速定位记录所在的数据页而建立一个别的目录，建这个目录必须完成下边这些事儿：

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

为了故事的顺利发展，我们这里需要做一个假设：假设我们的每个数据页最多能存放3条记录（实际上一个数据页非常大，可以存放下好多记录）。有了这个假设之后我们向 index\_demo 表插入3条记录：

mysql> INSERT INTO index_demo VALUES(1, 4, 'u'), (3, 9, 'd'), (5, 3, 'y');
    Query OK, 3 rows affected (0.01 sec
    复制代码

那么这些记录已经按照主键值的大小串联成一个单向链表了，如图所示：

![format_png 2][]

从图中可以看出来， index\_demo 表中的3条记录都被插入到了编号为 10 的数据页中了。此时我们再来插入一条记录：

mysql> INSERT INTO index_demo VALUES(4, 4, 'a');
    Query OK, 1 row affected (0.00 sec)
    复制代码

因为 页10 最多只能放3条记录，所以我们不得不再分配一个新页：

![format_png 3][]

页10 中用户记录最大的主键值是 5 ，而 页28 中有一条记录的主键值是 4 ，因为 5> 4 ，所以这就不符合下一个数据页中用户记录的主键值必须大于上一个页中用户记录的主键值的要求，所以在插入主键值为 4 的记录的时候需要伴随着一次记录移动，也就是把主键值为 5 的记录移动到 页28 中，然后再把主键值为 4 的记录插入到 页10 中，这个过程的示意图如下：

![format_png 4][]

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

*  **给所有的页建立一个目录项**

由于数据页的编号可能并不是连续的，所以在向 index\_demo 表中插入许多条记录后，可能是这样的效果：

![format_png 5][]

**目录项：**

因为这些 16KB 的页在物理存储上可能并不挨着，所以如果想从这么多页中根据主键值快速定位某些记录所在的页，我们需要给它们做个目录，每个页对应一个**目录项**，每个目录项包括下边两个部分：

*  页的用户记录中最小的主键值，我们用 key 来表示
 *  页号，我们用 page\_no 表示

![format_png 6][]

**至此，针对数据页做的简易目录就搞定了。不过忘了说了，这个 目录 有一个别名，称为 索引 。**

#### 2、InnoDB中的索引方案 ####

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

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

**所以，设计 InnoDB 的大叔们设计了一种可以灵活管理所有 目录项 的方式。**

目录项其实长得跟我们的用户记录差不多，只不过 目录项 中的两个列是 主键 和 页号 而已。为了和用户记录做一下区分，我们把这些用来表示目录项的记录称为 目录项记录 。那 InnoDB 怎么区分一条记录是普通的 用户记录 还是 目录项记录 呢？别忘了记录头信息里的record\_type 属性，它的各个取值代表的意思如下：

*  0 ：普通的用户记录
 *  1 ：目录项记录
 *  2 ：最小记录
 *  3 ：最大记录

我们把前边使用到的目录项放到数据页中的样子就是这样：

![format_png 7][]

从图中可以看出来，我们新分配了一个编号为 30 的页来专门存储 目录项记录 。这里再次强调一遍 目录项记录和普通的 用户记录 的不同点：

*  目录项记录 的 record\_type 值是1，而普通用户记录的 record\_type 值是0。
 *  目录项记录 只有主键值和页的编号两个列，而普通的用户记录的列是用户自己定义的，可能包含很多列，另外还有 InnoDB 自己添加的隐藏列。

现在以查找主键为 20 的记录为例，根据某个主键值去查找记录的步骤就可以大致拆分成下边两步：

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

那如果表中的数据太多，以至于一个数据页不足以存放所有的 目录项记录 ，该咋办呢？

*  当然是再多整一个存储 目录项记录 的页喽

我们假设一个存储 目录项记录 的页最多只能存放4条 目录项记录，所以如果此时我们再向上图中插入一条主键值为 320 的用户记录的话，那就需要分配一个新的存储 目录项记录的页：

![format_png 8][]

现在因为存储 目录项记录 的页不止一个，所以如果我们想根据主键值查找一条用户记录大致需要3个步骤，以查找主键值为 20 的记录为例：

*  确定 目录项记录 页
    
    我们现在的存储 目录项记录 的页有两个，即 页30 和 页32 ，又因为 页30 表示的目录项的主键值的范围是\[1, 320) ， 页32 表示的目录项的主键值不小于 320 ，所以主键值为 20 的记录对应的目录项记录在 页30中。
 *  通过 目录项记录 页确定用户记录真实所在的页
 *  在真实存储用户记录的页中定位到具体的记录。

那么问题来了，在这个查询步骤的第1步中我们需要定位存储 目录项记录 的页，但是这些页在存储空间中也可能不挨着，如果我们表中的数据非常多则会产生很多存储 目录项记录 的页，那我们怎么根据主键值快速定位一个存储 目录项记录 的页呢？其实也简单，为这些存储 目录项记录 的页再生成一个更高级的目录，就像是一个多级目录一样，大目录里嵌套小目录，小目录里才是实际的数据，所以现在各个页的示意图就是这样子：

![format_png 9][]

**这玩意儿像不像一个倒过来的 树 呀，上头是树根，下头是树叶！其实这是一种组织数据的形式，或者说是一种数据结构，它的名称是 B+ 树。**

![format_png 10][]

#### 3、B+ 树 ####

MySQL 中最常用的索引的数据结构是 B+ 树，他有以下特点：

1.  在 B+ 树中，所有数据记录节点都是按照键值的大小存放在同一层的叶子节点上，而非叶子结点只存储key的信息，这样可以大大减少每个节点的存储的key的数量，降低B+ 树的高度
2.  B+ 树叶子节点的关键字从小到大有序排列，左边结尾数据都会保存右边节点开始数据的指针。
3.  B+ 树的层级更少：相较于 B 树 B+ 每个非叶子节点存储的关键字数更多，树的层级更少所以查询数据更快
4.  B+ 树查询速度更稳定：B+ 所有关键字数据地址都存在叶子节点上，所以每次查找的次数都相同所以查询速度要比B树更稳定;
5.  B+ 树天然具备排序功能：B+ 树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，数据紧密性很高，缓存的命中率也会比B树高。
6.  B+ 树全节点遍历更快：B+ 树遍历整棵树只需要遍历所有的叶子节点即可，，而不需要像 B 树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

![format_png 11][]

假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，那么：

*  如果 B+ 树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放 100 条记录
 *  如果 B+ 树有2层，最多能存放 1000×100=100000 条记录
 *  如果 B+ 树有3层，最多能存放 1000×1000×100=100000000 条记录
 *  如果 B+ 树有4层，最多能存放 1000×1000×1000×100=100000000000 条记录

你的表里能存放 100000000000 条记录么？所以一般情况下，我们用到的 B+ 树都不会超过4层，那我们通过主键值去查找某条记录最多只需要做4个页面内的查找（查找3个目录项页和一个用户记录页），又因为在每个页面内有所谓的 Page Directory （页目录），所以在页面内也可以通过二分法实现快速定位记录，这不是很牛么，哈哈！

#### 4、聚簇索引 ####

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

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

**我们把具有这两种特性的 B+ 树称为 聚簇索引 ，所有完整的用户记录都存放在这个 聚簇索引 的叶子节点处。**

在 InnoDB 存储引擎中， 聚簇索引 就是数据的存储方式（所有的用户记录都存储在了 叶子节点 ），也就是所谓的索引即数据，数据即索引。

#### 5、二级索引 ####

上边介绍的 聚簇索引 只能在搜索条件是主键值时才能发挥作用，因为 B+ 树中的数据都是按照主键进行排序的。那如果我们想以别的列作为搜索条件该咋办呢？难道只能从头到尾沿着链表依次遍历记录么？

不，我们可以多建几棵 B+ 树，不同的 B+ 树中的数据采用不同的排序规则。比方说我们用 c2 列的大小作为数据页、页中记录的排序规则，再建一棵 B+ 树，效果如下图所示：

![format_png 12][]

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

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

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

*  确定 目录项记录 页
    
    根据 根页面 ，也就是 页44 ，可以快速定位到 目录项记录 所在的页为 页42 （因为 2 < 4 < 9 ）。
 *  通过 目录项记录 页确定用户记录真实所在的页
    
    在 页42 中可以快速定位到实际存储用户记录的页，但是由于 c2 列并没有唯一性约束，所以 c2 列值为 4 的记录可能分布在多个数据页中，又因为 2 < 4 ≤ 4 ，所以确定实际存储用户记录的页在 页34 和 页35 中。
 *  在真实存储用户记录的页中定位到具体的记录
    
    到 页34 和 页35 中定位到具体的记录。
 *  但是这个 B+ 树的叶子节点中的记录只存储了 c2 和 c1 （也就是 主键 ）两个列，所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。

#### 6、回表 ####

我们根据这个以 c2 列大小排序的 B+ 树只能确定我们要查找记录的主键值，所以如果我们想根据 c2 列的值查找到完整的用户记录的话，仍然需要到 聚簇索引 中再查一遍，这个过程也被称为 回表 。也就是根据 c2 列的值查询一条完整的用户记录需要使用到 2 棵 B+ 树！！！

那现在假设查询的 SQL 是这样子的（我们假设 student 中还有除了name，age，id 其他的字段 ）

SELECT * FROM student WHERE name='wx'
    复制代码

这种情况下，MySQL 就需要进行**回表**查询了。此时 MySQL 就会根据定位到的某条记录中的 id 再次进行聚簇索引查找，也就是说会根据 id 去维护 id 的那么 B+ 树中查找。因为聚簇索引中数据页记录的是一条记录的完整的记录，这个过程就叫**回表**。

再强调下回表的含义：`根据非主键索引查询到的结果并没有查找的字段值，此时就需要再次根据主键从聚簇索引的根节点开始查找，这样再次查找到的记录才是完成的`。

因为这种按照 非主键列 建立的 B+ 树需要一次 回表 操作才可以定位到完整的用户记录，所以这种 B+ 树也被称为 **二级索引** （英文名 secondary index ），或者 **辅助索引** 。

由于我们使用的是 c2 列的大小作为 B+ 树的排序规则，所以我们也称这个 B+ 树为为c2列建立的索引。

#### 7、联合索引 ####

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

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

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

![format_png 13][]

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

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

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

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

### 三、InnoDB的B+树索引的注意事项 ###

#### 1、根页面万年不动窝 ####

我们前边介绍 B+ 树索引的时候，为了大家理解上的方便，先把存储用户记录的叶子节点都画出来，然后接着画存储目录项记录的内节点，实际上 B+ 树的形成过程是这样的：

*  每当为某个表创建一个 B+ 树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个 根节点 页面。最开始表中没有数据的时候，每个 B+ 树索引对应的 根节点 中既没有用户记录，也没有目录项记录。
 *  随后向表中插入用户记录时，先把用户记录存储到这个 根节点 中。
 *  当 根节点 中的可用空间用完时继续插入记录，此时会将 根节点 中的所有记录复制到一个新分配的页，比如 页a 中，然后对这个新页进行 页分裂 的操作，得到另一个新页，比如 页b 。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到 页a 或者 页b 中，而根节点 便升级为存储目录项记录的页。

这个过程需要大家特别注意的是：**一个B+树索引的根节点自诞生之日起，便不会再移动。** 这样只要我们对某个表建立一个索引，那么它的 根节点 的页号便会被记录到某个地方，然后凡是 InnoDB 存储引擎需要用到这个索引的时候，都会从那个固定的地方取出 根节点 的页号，从而来访问这个索引。

#### 2、内节点中目录项记录的唯一性 ####

我们知道 B+ 树索引的内节点中目录项记录的内容是 索引列 + 页号 的搭配，但是这个搭配对于二级索引来说有点儿不严谨。还拿 index\_demo 表为例，假设这个表中的数据是这样的：

![format_png 14][]

如果二级索引中目录项记录的内容只是 索引列 + 页号 的搭配的话，那么为 c2 列建立索引后的 B+ 树应该长这样：

![format_png 15][]

如果我们想新插入一行记录，其中 c1 、 c2 、 c3 的值分别是： 9 、 1 、 'c' ，那么在修改这个为 c2 列建立的二级索引对应的 B+ 树时便碰到了个大问题：

我们新插入的这条记录的 c2 列的值也是 1 ，那我们这条新插入的记录到底应该放到 页4 中，还是应该放到 页5 中啊？

为了让新插入记录能找到自己在那个页里，**我们需要保证在B+树的同一层内节点的目录项记录除 页号 这个字段以外是唯一的。** 所以对于二级索引的内节点的目录项记录的内容实际上是由三个部分构成的：

*  索引列的值
 *  主键值
 *  页号

也就是我们把 主键值 也添加到二级索引内节点中的目录项记录了，这样就能保证 B+ 树每一层节点中各条目录项记录除 页号 这个字段外是唯一的，所以我们为 c2 列建立二级索引后的示意图实际上应该是这样子的：

![format_png 16][]

这样我们再插入记录 (9, 1, 'c') 时，由于 页3 中存储的目录项记录是由 c2列 + 主键 + 页号 的值构成的，可以先把新记录的 c2 列的值和 页3 中各目录项记录的 c2 列的值作比较，如果 c2 列的值相同的话，可以接着比较主键值，因为 B+ 树同一层中不同目录项记录的 c2列 + 主键 的值肯定是不一样的，所以最后肯定能定位唯一的一条目录项记录，在本例中最后确定新记录应该被插入到 页5 中。

### 四、总结 ###

#### 1、B+ Tree 原理 ####

![format_png 17][]

#### 2、具体查找流程 ####

![format_png 18][]

![format_png 19][]

[format_png]: https://img-blog.csdnimg.cn/img_convert/cee5519293d3834168f223974d6f5755.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/11d592decc7fbf506f11cb3aa4c4d073.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/d18937726ff44e8e1199f49fb2fcf5fd.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/e91d3d2869ca2f58fc42bc46434051fa.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/f0c8da37505a63100498a6031305c815.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/b765bed4ed658a675d521dcd5408cc1e.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/100d82a37228cd947fddc616d5fe3d2f.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/65109f345f009482cb19cb63875d96c4.webp?x-oss-process=image/format,png
[format_png 8]: https://img-blog.csdnimg.cn/img_convert/684467aa0d06c88b0bc75fdd98033e33.webp?x-oss-process=image/format,png
[format_png 9]: https://img-blog.csdnimg.cn/img_convert/2c50527bac3aca1c92597605fba9b568.webp?x-oss-process=image/format,png
[format_png 10]: https://img-blog.csdnimg.cn/img_convert/18c851da2163ab294660054843bf6510.webp?x-oss-process=image/format,png
[format_png 11]: https://img-blog.csdnimg.cn/img_convert/68d53955dc375e56237a7d6a2991e10e.webp?x-oss-process=image/format,png
[format_png 12]: https://img-blog.csdnimg.cn/img_convert/2f098dfefdf755cc03dd0597c8fc4af9.webp?x-oss-process=image/format,png
[format_png 13]: https://img-blog.csdnimg.cn/img_convert/cc2f91a64ac0edb7f6725a95a08f9c93.webp?x-oss-process=image/format,png
[format_png 14]: https://img-blog.csdnimg.cn/img_convert/249cd80298929ab63915243a38cfaefd.webp?x-oss-process=image/format,png
[format_png 15]: https://img-blog.csdnimg.cn/img_convert/5e38a55fa421d3f4d345dfde8ba13e53.webp?x-oss-process=image/format,png
[format_png 16]: https://img-blog.csdnimg.cn/img_convert/dd61309d0f29c0e2f6629e51868fdbcd.webp?x-oss-process=image/format,png
[format_png 17]: https://img-blog.csdnimg.cn/img_convert/2ff08e2bfd09ffd496021f064eda084c.webp?x-oss-process=image/format,png
[format_png 18]: https://img-blog.csdnimg.cn/img_convert/b9122df201dc877707b724440f7dd37b.webp?x-oss-process=image/format,png
[format_png 19]: https://img-blog.csdnimg.cn/img_convert/822fe71debd417716e72e89619913296.webp?x-oss-process=image/format,png