【MySQL】深入理解B+树索引

「爱情、让人受尽委屈。」 2024-03-30 14:15 20阅读 0赞

#### 文章目录 ####

*  1. 前言
 *  2. 索引方案
 *  3. InnoDB的索引方案
 *  4. 索引的分类
 *   *  4.1 聚簇索引
     *  4.2 二级索引
     *  4.3 联合索引
 *  5. InnoDB中的B+树索引的注意事项
 *   *  5.1 内节点中目录项记录的唯一主
     *  5.2 一个页至少容纳2条记录
 *  6. MyISAM中的索引⽅案简单介绍

## 1. 前言 ##

**索引，是MySQL快速查询的秘籍。**

如果没有索引，是怎么查询记录的呢？

> **首先，假如表中记录比较少，所有记录都可以存放在一个页中。**

那么可以根据搜索条件，分为两种情况：

*  **以主键为搜索条件**，这种就可以通过页目录使用二分法快速定位到相应的槽，接着遍历槽中的记录，就可以快速找到指定记录了。
 *  **以其他列作为搜索条件**，因为页中没有为非主键建立所谓的页目录，所以无法通过二分法定位槽的方式查找。只能从Infimum记录开始依次遍历单项链表的每条记录，然后对比是否符合搜索条件。（效率最低）

> **如果表中记录很多，就需要用比较多的页存储这些记录**

那么，就需要两个步骤：

1.  定位记录所在页
2.  从所在页内查找相应记录。

由于没有索引，无论是根据主键列还是其他列，都不能快速定位记录所在页，需要从第一页开始往下找，在每一页中使用二分法查找指定记录。很显然，这种方式非常消耗时间。

--------------------

## 2. 索引方案 ##

就以这个`t_index_demo`这个表为例

CREATE TABLE t_index_demo(
    	c1 INT,
        c2 INT,
        c3 CHAR(1),
        PRIMAY KEY(c1)
    )ROW_FORMAT = COMPACT;

该表指定了使用`COMPACT`为行格式，并且列`c1`为主键。

行格式示意图简化如下：

![image-20230103154414952][]

以及记录存放在页中的示意图如下：

![image-20230103154449496][]

先往表中插入数据

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

这些记录就会按照主键值的大小形成一个单向链表

![image-20230103154615764][]

这时候，新插入一条数据。

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

**这里假设每个页最多只能存放3条记录**，再插入一条记录，就**需要重新分配一个页了。**

![image-20230103155100593][]

理论应该像上图所示，但是如果是这样的话，页10的最大主键值为5，页25的一条记录的主键值为4，5>4。

不符合`下一条数据页中用户记录的主键必须大于上一页中用户记录的主键值`，因此需要将主键值为4的记录进行一次记录移动。

***怎么移动呢？***

*  先将主键值为5的记录移动到页28
 *  再将主键值为4的记录插入到页10中

![image-20230103155456823][]

这个过程，可以称为**页分裂**。

**在每次进行表记录的增删改操作的时候，都必须通过记录移动操作来保证这个规则一直成立。**

这样插入数据的操作就完成了。

> 那么，**怎么快速定位查找的记录在哪些数据页呢？**

其实和查找用户记录类似，查找用户记录的时候，**MySQL为了根据根据主键值快速定位一条记录而设立了页目录**。所以也可以**为快速定位记录所在的数据⻚⽽建⽴⼀个别的⽬录**，但是建这个⽬录必须完成下边这些事⼉：

*  **下⼀个数据⻚中⽤户记录的主键值必须⼤于上⼀个⻚中⽤户记录的主键值**。
 *  **给所有的页创建一个目录项**。

这个目录项，由两部分组成

*  ⻚的⽤户记录中最⼩的主键值，我们⽤`key`来表示。
 *  ⻚号，我们⽤`page_no`表示。

![image-20230103160348584][]

**如上图所示，我们将几个目录项在物理内存中连续存储，比如放在一个数组中，就能实现根据主键值快速查找某条记录的功能了。**

比如需要查找主键值为20的数据。

*  现在目录项目根据二分查找确定主键值为20的记录在目录项3中。而目录项3中对应的就是页9
 *  接着在根据二分法在页9中就可以快速确认主键值为20的数据的具体记录了。

**这种目录，就是我们常用的索引！！！！**

--------------------

## 3. InnoDB的索引方案 ##

**上述的是一种简单的索引方案，并不是真正的InnoDB索引方案。**

在InnoDB中，由于这些目录项和用户记录长得很像，**所以在InnoDB中复用了之前存储用户记录的页来存储目录项。**

同时，InnoDB为了区分是目录项还是普通的用户记录，将目录项的记录头信息的`record_type`属性设置为1。

在这里，就可以知道`record_type`的所有含义了

*  `0`：普通用户记录
 *  `1`：⽬录项记录
 *  `2`：最⼩记录
 *  `3`：最⼤记录

![image-20230103161342140][]

***这里总结一下⽬录项记录和普通的⽤户记录的不同点：***

1.  ⽬录项记录的`record_type`值是1，⽽普通⽤户记录的`record_type`值是0。
2.  **⽬录项记录只有主键值和⻚的编号两个列**，⽽**普通的⽤户记录的列是⽤户⾃⼰定义的**，可能包含很多列，另外**还有InnoDB⾃⼰添加的隐藏列**。
3.  **只有在存储⽬录项记录的⻚中的主键值最⼩的⽬录项记录**的`min_rec_mask`值为1， 其他别的记录的`min_rec_mask`值都是0。

并且，需要注意的是，由于目录项使用的是和存储用户记录的页是一样的数据结构。

**因此目录项实际是一个双向链表！！！！！而不是上面提到的目录项是连续空间！！！！**

还有就是，InnoDB中一个页只有16kb大小，当表的数据太多，一个数据页已经不足以存放所有目录项的话，就需要多整一个存储目录项的页了。（这里假设了一个页最多存放4条记录）

![image-20230103162014990][]

因此，在InnoDB中，**如果需要查询一条用户记录的话**，就需要三个步骤

1.  **确定⽬录项记录⻚**
2.  **通过⽬录项记录⻚确定⽤户记录真实所在的⻚。**
3.  **在真实存储⽤户记录的⻚中定位到具体的记录。**

> ***但是问题又来了，当一个表的数据非常多的时候，这也会产生非常多的目录项，那么怎么根据主键值快速定位一个目录项呢？***

很简单，为这些存储⽬录项记录的⻚再⽣成⼀个更⾼级的⽬录，就像是⼀个多级⽬录⼀样，⼤⽬录⾥嵌套⼩⽬录，⼩⽬录⾥才是实际的数据。

![image-20230103162322859][]

**而这种结构，就是大名鼎鼎的B+树了**

**不论是存放⽤户记录的数据⻚，还是存放⽬录项记录的数据⻚，我们都把它们存放到B+树这个数据结构中了**

**我们也称这些数据页为B+树的节点，并且我们真正的用户记录其实存放在B+树最底层的节点上。**

**这些节点也被称为叶⼦节点或叶节点，其余⽤来存放⽬录项的节点称为⾮叶⼦节点或者内节点，其中 B+树最上边的那个节点也称为根节点。**

![image-20230103162615048][]

MySQL规定最下边的那层，**也就是存放我们⽤户记录的那层为第0层**，之 后依次往上加。

⼀般情况下，**我们⽤到的B+树都不会超过4层**，那我们通过主键值去查找某条记录最多只需要做4个⻚⾯内的查找 （查找3个⽬录项⻚和⼀个⽤户记录⻚）。

之前有提到的`Page Header`部分的一个`PAGE_LEVEL`属性，它**记录的就是这个数据页作为节点在B+树所在的层级。**

--------------------

## 4. 索引的分类 ##

**这里探讨的索引主要有**

*  聚簇索引
 *  二级索引
 *  联合索引

--------------------

### 4.1 聚簇索引 ###

B+树本身是一个目录，或者说本身是一个索引。具有以下两个特点：

1.  **使⽤记录主键值的⼤⼩进⾏记录和⻚的排序**
    
    1.  ⻚内的记录是按照主键的⼤⼩顺序排成⼀个单向链表。
    2.  **各个存放⽤户记录的⻚**也是根据⻚中⽤户记录的主键⼤⼩顺序排成⼀个双向链表。
    3.  存放**⽬录项记录的⻚**分为不同的层次，在同⼀层次中的⻚也是根据⻚中⽬录项记录的主键⼤⼩顺序排成⼀个双向链表。
2.  **B+树的叶⼦节点存储的是完整的⽤户记录。**
    
    1.  所谓完整的⽤户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

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

这种聚簇索引并不需要我们在MySQL语句中显式的使⽤INDEX 语句去创建，InnoDB存储引擎会⾃动的为我们创建聚簇索引。

另外有趣的⼀点是，在InnoDB存储引擎中，聚簇索引就是数据的存储 ⽅式（所有的⽤户记录都存储在了叶⼦节点），也就是所谓的索引即数据，数据即索引。

--------------------

### 4.2 二级索引 ###

二级索引可以说就是另外一个B+树，这个B+树不再以主键的值进行排序。而是索引中指定的某个列作为大小作为数据页、页中记录的排序规则等等。

![image-20230103211236326][]

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

1.  使⽤记录**c2列**的⼤⼩进⾏记录和⻚的排序
    
    1.  ⻚内的记录是按照**c2列**的⼤⼩顺序排成⼀个单向链表
    2.  各个存放⽤户记录的⻚也是根据⻚中记录的c2列⼤⼩顺序排成⼀个双向链表。
    3.  存放⽬录项记录的⻚分为不同的层次，在同⼀层次中的⻚也是根据⻚中⽬录项记录的**c2列**⼤⼩顺序排成⼀个双向链表。
2.  B+树的叶⼦节点存储的并不是完整的⽤户记录，⽽只是c2列+主键这两个列的值。
3.  目录项记录中不再是主键+⻚号的搭配，⽽变成了c2列+⻚号的搭配。

比方说需要查找c2=4的列，这样的记录会有很多条，**但是我们只需要在该 树的叶子节点处定位到第一条满足搜索条件 c2斗的那条记录，然后沿着自记录组成的单向链表一直向后扫描即可。**

另外，各个叶子节点 组成了双向链表，搜索完了本页面的记录后可以很顺利地跳到下一个页面中的第一条记录，然 后继续沿着记录组成的单向链表向后扫描，查找过程如下。

1.  确定第一条符合 c2=4条件的目录项记录所在的页
2.  通过第一条符合 c2=4条件的目录项记录所在的页面确定第一条符合 c2=4条件的用 户记录所在的页
3.  在真正存储第一条符合 c2=4条件的用户记录的页中定位到具体的记录。
4.  但是这个B+树的叶⼦节点中的记录只存储了c2和c1（也就是主键）两个列，**所以我们必须再根据主键值去聚簇索引中再查找⼀遍完整的⽤户记录**。**这个通过携带主键信息到聚簇索引中重新定位完整的用户记录的过程也称为回表。**

> **为什么还需要一次回表操作呢?直接把完整的用户记录放到时子节点不就好了么?**

确实可以这样，但是太占内存，相当于每建一个B+树都需要将所有用户记录复制一遍，这样太浪费内存了。

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

由于我们使⽤的是c2列的⼤⼩作为B+树的排序规则，**所以我们也称这个B+树为为c2列建⽴的索引。**

> 我们把上面聚簇索引或者二级索引的叶子节点中的记录称为**用户记录**。
> 
> 为了区分，也把聚簇索引叶子节点中的记录称为**完整的用户记录**。
> 
> 把二级索引叶子节点中的记录称为**不完整的用户记录**。

--------------------

### 4.3 联合索引 ###

我们也可以同时以多个列的⼤⼩作为排序规则，也就是**同时为多个列建⽴索引，这种就叫联合索引**。

⽐⽅说我们想让B+树按照c2和c3列的⼤⼩进⾏排序，这个包含两层含义：

1.  先把各个记录和⻚按照c2列进⾏排序。
2.  在记录的c2列相同的情况下，采⽤c3列进⾏排序

![image-20230103212403565][]

但是注意的是**以c2和c3列的⼤⼩为排序规则建⽴的B+树称为联合索引**，本质上也是⼀个⼆级索引。

它的意思与**分别为c2和c3列分别建⽴索引的表述是不同的**。

1.  **建⽴联合索引**只会建⽴如上图⼀样的1棵B+树。
2.  **为c2和c3列分别建⽴索引**会分别以c2和c3列的⼤⼩为排序规则建⽴2棵B+树。

--------------------

## 5. InnoDB中的B+树索引的注意事项 ##

**B+树索引不是先把存储⽤户记录的叶⼦节点都画出来，然后接着画存储⽬录项记录的内节点**，实际上B+树的形成过程 是这样的：

1.  每当为某个表创建⼀个B+树索引（聚簇索引不是⼈为创建的，默认就有）的时候，**都会为这个索引创建⼀个根节点⻚⾯**。最开始表中没有数据的时候，**每个 B+树索引对应的根节点中既没有⽤户记录，也没有⽬录项记录。**
2.  随后向表中插⼊⽤户记录时，先把⽤户记录存储到这个根节点中。
3.  当根节点中的可⽤空间⽤完时继续插⼊记录，此时会将根节点中的所有记录复制到⼀个新分配的⻚，⽐如⻚a中，然后**对这个新⻚进⾏⻚分裂的操作**，得到另⼀ 个新⻚，⽐如⻚b。这时新插⼊的记录根据键值（也就是聚簇索引中的主键值，⼆级索引中对应的索引列的值）的⼤⼩就会被分配到⻚a或者⻚b中，⽽根节点便 升级为存储⽬录项记录的⻚。

> 需要注意的是，**⼀个B+树索引的根节点⾃诞⽣之⽇起，便不会再移动**

这样只要我们对某个表建⽴⼀个索引，那么它的根节点的⻚号便会被记录到某个地⽅，然后**凡是InnoDB存储引擎需要⽤到这个索引的时候**，都会**从那个固定的地⽅取出根节点的⻚号，从⽽来访问这个索引**。

--------------------

### 5.1 内节点中目录项记录的唯一主 ###

就以为`t_index_demo`这个表的数据来说

如果⼆级索引中⽬录项记录的内容只是**索引列 + ⻚号**的搭配的话，那么为c2列建⽴索引后的B+树应该⻓这样：

![image-20230103215110757][]

如果此时插入一个c1为9，c2为1，c3为’c’的记录，那么由于原来每个页的记录的c2列均为1，那么新插入的数据就会不知道应该放到页4还是页5。

为了让新插⼊记录能找到⾃⼰在那个⻚⾥，**我们需要保证在B+树的同⼀层内节点的⽬录项记录除⻚号这个字段以外是唯⼀的**。所以对于⼆级索引的内节点的⽬录 项记录的内容实际上是由三个部分构成的：

1.  索引列的值
2.  主键值
3.  页号

![image-20230103215411989][]

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

> 对于二级索引的记录来说，是先**按照二级索引列的值进行排序，如果该值相同，再按照主键值进行排序的**。
> 
> 所以，为c2列建立索引相当于为(c2, c1)列**建立了一个联合索引**。
> 
> 而对于唯一二级索引（某列声明为UNIQUE）来说，也可能出现相同值的情况（为NULL），**唯一二级索引的内节点的目录项也需要包括记录的主键值。**

--------------------

### 5.2 一个页至少容纳2条记录 ###

一颗B+树只需要很少的层级就可以轻松存储数亿条记录。

虽然说一个大的目录存放一个子目录看起来也是可以的，但是这样的话层级关系就会很多。因此InnoDB规定，**一个数据页至少存放两条记录。**

--------------------

## 6. MyISAM中的索引⽅案简单介绍 ##

在InnoDB中，索引即数据。**也就是聚簇索引的那棵B+树的叶⼦节点中已经把所有完整的⽤户记录都包含了**。

但是在MyISAM就不一定了。MyISAM的索引⽅案虽然也 使⽤树形结构，**但是却将索引和数据分开存储**。

*  **将表中的记录按照记录的插⼊顺序单独存储在⼀个⽂件中**，称之为数据⽂件。这个⽂件并不划分为若⼲个数据⻚，有多少记录就往这个⽂件中塞多少记录就成 了。**我们可以通过⾏号⽽快速访问到⼀条记录**。

![image-20230103220448376][]

由于插入数据的时候没有按照主键大小排序，因此不能使用二分法查找。

*  **使⽤MyISAM存储引擎的表会把索引信息另外存储到⼀个称为索引⽂件的另⼀个⽂件中**。MyISAM会单独为表的主键创建⼀个索引，**只不过在索引的叶⼦节点中存储的不是完整的⽤户记录**，⽽是**主键值 + ⾏号**的组合。也就是先通过索引找到对应的⾏号，再通过⾏号去找对应的记录！

而InnoDB是**只需要根据主键值对聚簇索引进⾏⼀次查找就能找到对应的记录**，⽽在MyISAM中却需要进⾏⼀次 回表操作，意味着**MyISAM中建⽴的索引相当于全部都是⼆级索引**！

*  如果有需要的话，我们也可以对其它的列分别**建⽴索引或者建⽴联合索引**，原理和InnoDB中的索引差不多，不过在叶⼦节点处**存储的是相应的列 + ⾏号**。这些索引也全部都是⼆级索引。

> **MyISAM会直接在索引的叶子节点处存储该条记录在数据文件中的地址偏移量。**
> 
> **而InnoDB是获取主键之后再去聚簇索引中找记录。**
> 
> 所以，MyISAM的回表速度会比InnoDB快。

--------------------

> 参考：《MySQL是怎样运行的：从根儿上理解 MySQL》

--------------------

[image-20230103154414952]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/fe80079bba29426c8215d348a8b2680b.png
[image-20230103154449496]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/90d50f1793d145b39ac4f61cdfb56582.png
[image-20230103154615764]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/fd9bc2f651424735bd0604a32d8efa17.png
[image-20230103155100593]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/5cd08d47be014aadb1c827fbaf81236e.png
[image-20230103155456823]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/cf1fdbb097a34d7f8d48c93800ecb491.png
[image-20230103160348584]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/9d56dc99caa44f54a55db37254ba3d26.png
[image-20230103161342140]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/c20bc06c9f5f448dab4405e0c83a8741.png
[image-20230103162014990]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/af860430a747430ca284295305638301.png
[image-20230103162322859]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/b5fc56fcaa17496f9410c44d3ae4febf.png
[image-20230103162615048]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/7fb8097a78cc45958335e7fcf23b6d61.png
[image-20230103211236326]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/bcbdfcfeb35b45a8b51d98c4cd6d0c2e.png
[image-20230103212403565]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/2e2fdb461fb343248611d203fa3b5a58.png
[image-20230103215110757]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/7736d5a955c44abe9cc6508d7c737fbf.png
[image-20230103215411989]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/856e1cca574244f9851f50c3dc62c04a.png
[image-20230103220448376]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/30/17a398244bee4aa5b0adef84d622c095.png