Mysql之B+树索引详解（4）——聚簇索引的生长、凋零

前言

数据页结构和聚簇索引 两篇文章已经介绍了数据页的结构和数据页的组织方式。Mysql利用B+树的形式将所有数据页组织起来，达到了高效索引数据的目的，并且设定了页内数据根据主键有序排列，页间也同样根据主键有序的规则。数据是源源不断产生的，聚簇索引必须不断调整、生长，才能满足有序、B+树的要求，本节通过追踪一批数据的插入过程，来讲解聚簇索引生长过程中，示例数据、图片来自B站视频 mysql索引：b+树的生成过程，本篇文章在此视频基础上补充相关细节。
阅读完本篇文章，你就能明白，为何推荐使用自增id作为主键，为何推荐逻辑删除而不是物理删除，为何推荐批量插入而不是单条循环插入等规范。

插入数据

以数字型主键为例，插入一批数据，假设每个数据页只能存3条数据，方便演示页分裂过程，插入id顺序如下：

当表创建时，只有一个空的根页存在，假设页号为1，插入id = 9,17两条数据后，如下：

继续插入id =3的数据，显然这里需要移动id= 9,17的两条数据，使得id=3的数据插入到最前面，形成页内有序的结构：

至此，根页数据已满，继续插入id = 7的数据，将会触发【页分裂】操作，但此时的数据页是根页，因此在页分裂之前还有一步操作，就是建立根页索引

建立索引

具体来说，首先申请一个新数据页（设page_no = 2），把页1中的数据全部复制到页2中，并在根页中插入一条目录项记录，关联页2，如下图：

页分裂

根页建立起索引后，就是页分裂过程。同样新申请一个数据页3，把页2中的一半数据复制到新页3中，在根页中新建一个条目录项将页3建立索引，再把目标数据id =7插入到页2中（伴随着一次记录移动）：

由此完成了一次完整的索引建立、页分裂过程。

索引页的索引

按照前面的描述顺序，id = 35的记录插入后，索引树变成了下图所示：

根页中已经填满目录项，现在考虑插入id = 8的数据。id = 8的数据应该插入页2中，但由于页2已经没有多余空间，所有会触发一次【页分裂】操作，新申请一个页面6，将页2中的一半（向下取整）数据复制，并将id= 8的数据插入页2中：

新的页6也需要被索引，但由于根页已经填满，此时就需要再进行一次【索引建立】+【根页分裂】操作。具体说，就是新申请一个数据页7，把根页的数据完全复制过来，再把页7索引到根页中。然后再对新的数据页进行页分裂，以腾出空间供页6的索引插入：

之后的数据插入过程大同小异，就是不断地页分页、建立索引过程，而且变动过程是从下往上的。就是说，插入一条数据，先定位到最底层的数据页的插入位置，如果需要页分裂，就申请新的页 ，再把新的页加入到上层索引，如果上层索引需要再分裂，就一直重复，直到根页。

删除数据

更新数据只会修改数据页中某一条记录，并不会触发索引的修改，这里值讨论值删除的情况。当删除某一条数据的时候，Mysql会先定位到数据所在，然后发生一下事情：
1、修改记录头中的deleted_flag = 1，代表该记录已经删除。
2、修改该记录头中的next_record = 0，代表该记录没有下一条记录。
3、修改该记录所在分组的最大主键那条记录的记录头中的n_owned 减1。
这里补充下第3步，在数据页结构中介绍Page Directory时有说过，User records中的记录会进行分组，每组最大主键的那条记录的偏移量会记录在Page Directory中，称为Slot。实际上该记录头中还有一个属性n_owned，用来记录该分组有多少条记录的。
Mysql并不会真正删除一条记录（擦除硬盘数据），而是标记该条记录不可见，并用一个链表将所有删除的记录的heap_no记录起来，用于下次插入新数据时的可用空间。这么看好像删除数据也不会触发索引的变化？Mysql中还有另外一个机制叫【页合并】。

页合并

我们知道数据页中的User records部分是用来存储实际数据的，并且被删除的记录实际还存在表中，但被标记为不可用。如果一张表被频繁删除，又没有新的数据填充到被删除位置，岂不是会造成很大浪费？为了解决这个问题，Mysql设计了一个【页合并】机制，设定一个存储百分比阈值，例如50%，当某页的实际存储小于50%时，mysql会尝试合并两个数据页，如果恰好两个页的已用存储都小于50%，并且已经符合有序的规则，这两个页就可以进行一次合并。
页的合并必然伴随一次索引页的目录项删除，这部分的原理和记录删除是一致的，而且索引页也可以进行页合并。

Mysql规范

• 为什么推荐用自增ID做主键？
  不论什么数据做主键，不断插入数据必然伴随着页分裂，但是自增主键天然有序且稠密，意味着不会出现需要移动数据才能插入新数据的情况，顺序读取、写入的IO效率是最高的。为了适应分布式要求，可以再使用一个分布式ID作为业务ID，满足全局唯一性。
• 为什么不推荐物理删除，而使用逻辑删除？
  除了考虑数据留存归档的需求外，逻辑删除只是update某个字段，而物理删除Mysql需要进行更多操作，还有页合并等处理，这都增加了Mysql的处理工作量。