Mysql InnoDB: Page创建、Page Merging和Page Splitting

Page创建

Mysql的InnoDB中，库windmills中有一张wmills表，这张表在磁盘中（路径通常是/var/lib/mysql/）由两个文件构成（5.6版本后innodb_file_per_table默认是1），如下

data/
  windmills/
      wmills.ibd
      wmills.frm

如果wmills表中有N个索引

• wmills.ibd文件中有N个Segment
• Segment 每个Segment包含多个Extent
• Extent 每个Extent默认1MB，最多包含64个Page（每个Page默认16KB）
• Page 每个Page存储2到k行数据（因此每行数据最大8192B）
  
  需要注意的是，InnoDB操作的基本单位是Page而不是Row。

Page Merging

Page有一个参数MERGE_THRESHOLD，默认值是Page大小的50%，当Page中保存的数据量少于MERGE_THRESHOLD时，InnoDB将尝试merge page。

Merge的过程

• 图1 Page5中当前有8条记录
  

• 图2 Page6中当前有3条记录

• 图3 删除Page5中主键ID是5 6 7 8的记录。注意，这里是标记删除，没有物理删除，这里只是标记空间是可回收的。
  

• 图4 这里Page5的使用率低于50%，InnoDB尝试merge Page5 pre指针和next指针指向的page，看看能不能merge一下，从而提高空间利用率。这里Page6的使用率少于50%，所以会把Page5和Page6 merge下。merge后Page5如下图所示。
  

• 图6 Page6如下图所示。

Page Merging的结论

• 进行删除或者更新操作（更新行记录可能会使行变小）时，当一个Page小于MERGE_THRESHOLD，这个Page可能会进行PageMerge操作。如果merge成功，则INFORMATION_SCHEMA.INNODB_METRICS 中的index_page_merge_successful指标会增加。

Page Splits

Split的过程

• 图1 假设我们现在有一个ID是27的行，但是现在Page10剩余的空间容纳不下这条记录，因此我们想把27放在Page11里。
  
• 图2 但是不幸的是，Page11也容纳不下27这条记录。由于27是自增主键，我们也不能乱序的插入Page。那么问题来了，这时候该怎么办？
  
• 图3 解决方法如下

1. 创建一个新的Page
2. 看一下原始的Page在哪里可以分开
3. 移动记录
4. 重新设置pre和next指针

对于上述情况，具体做法如下
1. 新建Page12
2. InnoDB决定在ID为24的记录上进行Split
3. 把ID为24 25 26的行移动到新创建的Page12上
4. 将Page10的Next指针指向Page12；Page12的pre指针指向Page10，next指针指向Page11；Page11的Pre指针指向Page12，next指针指向Page13

结论

• 当Page split之后，尽管逻辑上B+树还是连续的，但是在物理存储上，Page已经不是连续的了，通常在不同的Extent中。这就意味着在操作ID连续的行时已经是随机读和随机写磁盘了。
• 执行插入和更新操作可能引起PageSplit。
• Page Splits的数据会被记录到INFORMATION_SCHEMA.INNODB_METRICS表的index_page_splits、index_page_reorg_attempts/successful 指标中。

总结

• PageMerge和PageSplits会获取B+树上的X-Latch，当Mysql繁忙时，竞争X-Latch可能会造成性能问题。
• 好的主键设计（例如自增的bigint）不仅对读数据有影响，对于Extent中数据的分布也有影响。

参考

https://www.percona.com/blog/2017/04/10/innodb-page-merging-and-page-splitting/

本文地址：https://blog.csdn.net/sodawoods/article/details/111937712