Innodb B+树索引的分裂优化

一、B+树插入逻辑

1,如果结点不存在,则新生成一个结点,作为B+树的根结点,结束。
2,如果结点存在,则查找当前数值应该插入的位置,定位到需要插入到叶子结点,然后插入到叶子结点。
3,插入的结点如果未达到最大数量,结束。如果达到最大数量,则把当前叶子结点对半分裂:[m/2]个放入左结点,剩余放入右结点。
4,将分裂后到右结点的第一个值提升到父结点中。若父结点元素个数未达到最大,结束。若父结点元素个数达到最大,分裂父结点:[m/2]个元素分裂为左结点,m-[m/2]-1个分裂为右结点,第[m/2]+1个结点提升为父结点。

下面以实际操作讲解。为了演示,我们以3阶B+树为例。

1)插入结点5:B+树根结点不存在,生成根结点

2)插入结点8:根结点存在,查找存放结点为根结点,插入

3)插入结点10 :同2)

  结点元素个数达到最大3,分裂结点。([3/2]=)1个元素放左边,剩余2个元素放右边。并将右子结点的第一个值提升到父结点。

 4)插入结点15 :找到插入到叶子结点,并插入,然后调整。步骤同3)

 5)插入结点20 :步骤同3)

  插入完成后,父结点元素个数达到最大,继续分裂父结点。([3/2]=)1个结点分裂为左结点;3-[3/2]-1=1个结点,分裂为右结点;第([3/2]+1=)2个结点提升为父结点。

二、mysql索引的B+树插入优化

  以上就是B+树的插入逻辑。你应该已经发现,虽然B+树为3阶树,但是分裂后的叶子结点都只有1~2个。如果这个算法应用到数据库索引,假设一个磁盘分页可以存放2千条数据,但是每次分裂后,都只存储1000条数据在磁盘分页中,那么必然会造成磁盘浪费。而且时接近50%的浪费。B+树的这个设计,是因为插入的结点不是有序的,每次的插入,定位到每个叶子结点的可能性都是有的,所以采用对半分,防止叶子结点频繁分裂造成性能问题。但是索引值一般是自增数值,所以已经分裂过的叶子结点,后面是不会再有结点插入的。所以这部分的浪费是不可接受的。

  出于以上考虑,mysql做了一版优化,即叶子结点在分裂时,不再按照对半分,而是保持原有的叶子结点不变,将超出的结点插入新的叶子结点,并把这个结点值,提升到父结点。父结点的分裂逻辑(待考证)。

  但是,这样会引发新的问题。假如索引值是严格按照顺序插入的,那么没有问题,如果不是,就会引发更严重的空间浪费。
  例如有下面一颗5阶B+树。

  现在插入结点19,定位到左边叶子结点;插入后,该叶子结点达到最大值,然后分裂出新结点结点。

  同理,继续插入结点18,同样会分裂出18的叶子结点。

  由于,18,19,20之间已经没有其他整型数值,所以18,19这两个结点永远都只有一个值,无疑带来了更严重的存储空间浪费。要知道,磁盘分页存储的可不是十几二十条记录。

  然而,mysql团队很快也发现了这个问题,并在一次补丁中修复了该漏洞。详情可参见官方说明。

  接下来,我们就用官方说明里面的方法,来验证现行的mysql索引的插入逻辑是怎么样的。

三、mysql的B+树插入逻辑

3.1 mysql版本

 1 mysql> show variables like 'ver%';
 2 +-------------------------+------------------------------+
 3 | Variable_name           | Value                        |
 4 +-------------------------+------------------------------+
 5 | version                 | 8.0.17                       |
 6 | version_comment         | MySQL Community Server - GPL |
 7 | version_compile_machine | x86_64                       |
 8 | version_compile_os      | macos10.14                   |
 9 | version_compile_zlib    | 1.2.11                       |
10 +-------------------------+------------------------------+
11 5 行于数据集 (0.03 秒)
12 
13 mysql> 

3.2 新建测试表

1 CREATE TABLE test.page_split_test
2 (
3   id BIGINT UNSIGNED NOT NULL,
4   payload1 CHAR(255) NOT NULL,
5   payload2 CHAR(255) NOT NULL,
6   payload3 CHAR(255) NOT NULL,
7   payload4 CHAR(255) NOT NULL,
8   PRIMARY KEY (`id`)
9 ) ENGINE=INNODB;

3.3 填充B+树根结点

填充根结点至分裂前的最大值

 1 # Fill up the root page, but don't split it.
 2 
 3 INSERT INTO test.page_split_test (id, payload1, payload2, payload3, payload4) 
 4 VALUES 
 5 (1, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
 6 (2, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
 7 (3, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
 8 (4, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
 9 (5, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
10 (6, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
11 (7, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
12 (8, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
13 (9, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
14 (10, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
15 (11, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
16 (12, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
17 (13, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
18 (14, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255));

  查看B+树结点情况

1 SELECT page_number, page_type, number_records, data_size
2 FROM information_schema.innodb_buffer_page
3 WHERE table_name like "%page_split_test%" AND index_name = "PRIMARY";

  结果如下,当前只有一个根结点,含14个数据行

3.4 首次分裂

  继续插入一个数据,B+树达到最大,首次进行分裂。

1 INSERT INTO test.page_split_test (id, payload1, payload2, payload3, payload4) 
2 VALUES (15, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255));

  查看B+树结点情况如下

  根结点对半分裂成两个结点,左结点(第5页)和右结点(第6页),原来的根结点(第4页)升级为父结点,父结点中包含两个元素,分别为指向两个子结点的引用。

3.5 继续填充右叶子结点

  继续插入数据,填充右叶子结点至临近饱和状态。

1 INSERT INTO test.page_split_test (id, payload1, payload2, payload3, payload4) 
2 VALUES 
3 (16, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
4 (17, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
5 (18, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
6 (19, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
7 (20, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255)),
8 (21, REPEAT("A", 255), REPEAT("B", 255), REPEAT("C", 255), REPEAT("D", 255));

  查看B+树状态

  在这里,mysql并没有像之前的bug一样,生成6个结点,而是将后面的6个结点合并成了一个。这就是mysql做的再一次优化,即叶子结点满了之后,如果该叶子结点后面有还有叶子结

点,则会将新的数据插入到后续的叶子结点中。

  然而这样的方案,虽然避免了空间浪费,但是增加了索引插入时的性能。极端假设,新增的数据定位到了第一个叶子结点,插入后,叶子结点达到最大数量,然后分裂出最后的一个元素插到后一个叶子结点。假设后面的叶子结点都是已经满额的状态,那么这个插入会导致所有的叶子结点都发生一次分裂,且所有父结点的数据都要重新调整。这样的效率简直是灾难性的。

  然而,这个问题,可以通过人为的避免,那就是使用自增索引。自增索引,可以保证每次插入的主键都是递增的,永远都只会修改和新增最右的叶子结点,而不用修改原有叶子结点。所以,使用递增索引,可以大大提升数据的插入效率。

四、mysql的B+树插入逻辑小结

  InnoDB的索引分裂策略,在特定的情况下,索引页面的分裂存在问题,导致每个分裂出来的页面,仅仅存储一条记录,页面的空间利用率极低。

4.1 B+树的分裂

传统B+树页面分裂操作分析:

  按照原页面中50%的数据量进行分裂,针对当前这个分裂操作,3,4记录保留在原有页面,5,6记录,移动到新的页面。最后将新纪录7插入到新的页面中;

50%分裂策略的优势:

  分裂之后,两个页面的空间利用率是一样的;如果新的插入是随机在两个页面中挑选进行,那么下一次分裂的操作就会更晚触发

50%分裂策略的劣势:

  空间利用率不高按照传统50%的页面分裂策略,索引页面的空间利用率在50%左右

  分裂频率较大:针对如上所示的递增插入(递减插入),每新插入两条记录,就会导致最右的叶页面再次发生分裂;

4.2 B+树分裂操作的优化

  新的分裂策略,在插入7时,不移动原有页面的任何记录,只是将新插入的记录7写到新页面之中;原有页面的利用率,仍旧是100%;

优化分裂策略的优势:

  索引分裂的代价小:不需要移动记录;

  索引分裂的概率降低:如果接下来的插入,仍旧是递增插入,那么需要插入4条记录,才能再次引起页面的分裂。相对于50%分裂策略,分裂的概率降低了一半;

  索引页面的空间利用率提高:新的分裂策略,能够保证分裂前的页面,仍旧保持100%的利用率,提高了索引的空间利用率;

优化分裂策略的劣势:

  如果新的插入,不再满足递增插入的条件,而是插入到原有页面,那么就会导致原有页面再次分裂,增加了分裂的概率

  因此,此优化分裂策略,仅仅是针对递增递减插入有效,针对随机插入,就失去了优化的意义,反而带来了更高的分裂概率

  在InnoDB的实现中,为每个索引页面维护了一个上次插入的位置,以及上次的插入是递增/递减的标识。根据这些信息,InnoDB能够判断出新插入到页面中的记录,是否仍旧满足递增/递减的约束,若满足约束,则采用优化后的分裂策略;若不满足约束,则退回到50%的分裂策略

五、参考文章

 https://blog.csdn.net/baidu_29258265/article/details/82150728?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-4.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromMachineLearnPai2%7Edefault-4.control

https://blog.csdn.net/qq_42389764/article/details/108152624

posted @ 2021-08-21 15:31  Mr-xxx  阅读(1728)  评论(0编辑  收藏  举报