InnoDB数据页什么时候合并

淩亂°似流年 2023-02-25 08:59 7阅读 0赞

1. 为什么要合并数据页2. 什么时候合并数据页2.1 准备测试环境2.2 找到两个相邻页2.3 试探性逐步删除数据，接近阈值2.4 再次只删除一条记录，验证是否合并3. 其他补充说明3.1 除了表级可以设置外，单个索引也可以设置合并阈值3.2 页合并状态监控3.3 如何适当调整阈值设置延伸阅读

> 当低于设定的阈值时就进行合并

在本文开始前，可以先复习这几篇文章

*  [**InnoDB表聚集索引层高什么时候发生变化**][InnoDB]
 *  [**浅析InnoDB索引结构**][InnoDB 1]
 *  [**Innodb页合并和页分裂**][Innodb]

### 1. 为什么要合并数据页 ###

我们知道，当从InnoDB表删除数据时，相应的数据是先打上删除标签（deleted mark），而后再由purge线程执行清理工作。

清理工作结束后，如果两个相邻的数据页存储填充率低于一定程度，就会尝试合并页，以降低碎片率，提高存储效率。

或者经过多次长度变小的UPDATE操作后（将varchar列长度更新变短），数据页填充率低于一定程度也会尝试合并。

合并完毕之后，空出来的页就会被标记为空闲页，等待再分配。

这个工作是InnoDB后台线程自动完成的，无需人为干预、控制。

### 2. 什么时候合并数据页 ###

MySQL官方手册 **The InnoDB Storage Engine / InnoDB Configuration / Configuring the Merge Threshold for Index Pages** 中其实已经详细说明了什么时候会进行合并。

通过调整参数 `MERGE_THRESHOLD` 的值，当InnoDB数据页填充率低于该阈值时，就会尝试进行合并页操作。

该参数默认值是 **50**，最小值是 **1**，在5.6版本之后允许自行指定设置，在5.6之前的版本中则是被硬编码的，无法修改。

When the “page-full” percentage for an index page falls below 50%, which is the default MERGE_THRESHOLD setting, InnoDB attempts to merge the index page with a neighboring page. If both pages are close to 50% full, a page split can occur soon after the pages are merged.

简言之，就是当发现两个相邻页的填充率都低于50%时，就会尝试进行合并。

#### 2.1 准备测试环境 ####

我们拿一个实际案例进行测试，观察InnoDB的页合并是怎么做的。

测试表DDL

CREATE TABLE `t_sk` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `c1` int(10) unsigned NOT NULL DEFAULT '0',
      `c2` int(10) unsigned NOT NULL,
      `c3` int(10) unsigned NOT NULL,
      `c4` int(10) unsigned NOT NULL,
      `c5` datetime NOT NULL,
      `c6` char(20) NOT NULL,
      `c7` varchar(30) NOT NULL,
      `c8` varchar(30) NOT NULL,
      `c9` varchar(30) NOT NULL,
      PRIMARY KEY (`id`),
      KEY `k1` (`c1`)
    ) ENGINE=InnoDB COMMENT='MERGE_THRESHOLD=30';

特地设置了 `MERGE_THRESHOLD=30`，看看page的填充率在高于30%时会不会发生合并。

用 `mysql_random_data_load` 灌入一些测试数据。

本文使用的MySQL版本是Percona-Server-5.7.22，下载源码后自编译的。

Server version:        5.7.22-22-log Source distribution

#### 2.2 找到两个相邻页 ####

随机找到其中的两个相邻的页，pageno分别是7和8。

用 `innodb_ruby` 工具扫描这两个page中的数据：

[root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -p 7 page-records > recs-no7.txt
    
    [root@yejr.run]# cat recs-no7.txt
    Record 128: (id=252)...
    ...
    Record 15143: (id=351)...

非常巧，这个page中共有100条记录，其ID值从 252 ~ 351。

同样地，扫描8号page，也正好有100条记录，其ID值从 352 ~ 451。

#### 2.3 试探性逐步删除数据，接近阈值 ####

先利用 `innodb_ruby` 工具查看page-dump的结果：

[root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -p 7 page-dump > dump-no7.txt
    ...
    sizes:
      header           120
      trailer            8
      directory         52
      free            1054
      used           15330
      record         15150
      per record     151.00
    ...

从上面信息可以看到，每条记录平均长度是151字节，共100条记录。

如果想让填充率低于30%，那么需要删除的数据量大概是：

ceil((15150 - 16384 * 0.3) / 151) = 68

关于上述公式简要说明下：

*  当前的数据所占字节数是：15150
 *  只剩30%填充率的字节数是：16384 \* 0.3
 *  每条记录平均长度字节数是：151  
    这样应该能看懂吧。  
    也就是两个page分别都需要删除68条记录才会触发合并操作。

好了，针对上述两个ID值区间，先各自分别删除67条数据，只差一条数据就达到临界点，看看后续会不会发生合并。

[root@yejr.run]> delete from t_sk where id>=252 and id <= 318;
    Query OK, 67 rows affected (0.01 sec)
    
    [root@yejr.run]> delete from t_sk where id>=352 and id <= 418;
    Query OK, 67 rows affected (0.00 sec)

先用 `innblock` 工具快速扫描：

[root@yejr.run]# innblock test/t_sk.ibd 7 16 | grep n_rows; innblock test/t_sk.ibd 8 16 | grep n_rows
    slot_nums:9         heaps_rows:102        n_rows:33
    slot_nums:9         heaps_rows:102        n_rows:33

确认两个page还没合并，各自都只剩33条记录。  
再看两个page的填充率情况：

#扫描pageno=7
    [root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -p 7 page-dump
    ...
    sizes:
      header           120
      trailer            8
      directory         18
      free           11198
      used            5186
      record          5040
      per record     152.00
    ...
    
    #扫描pageno=8
    [root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -p 8 page-dump
    ...
    sizes:
      header           120
      trailer            8
      directory         18
      free           11200
      used            5184
      record          5038
      per record     152.00
    ...

此时两个page的填充率分别是：

5040/16384 = 0.3076171875
    5038/16384 = 0.3074951171

都超过了 `MERGE_THRESHOLD=30` 阈值，所以还没有进行合并。

#### 2.4 再次只删除一条记录，验证是否合并 ####

接下来，我们分别对两个page再各自删除一条记录，使得填充率低于临界点：

[root@yejr.run]> delete from t_sk where id in (319, 419);
    Query OK, 2 rows affected (0.01 sec)

还是用 `innblock` 工具扫描，不过这次先扫描第7号page，看看是不是把8号page合并过来了。

[root@yejr.run]# innblock test/t_sk.ibd 7 16 | grep n_rows; innblock test/t_sk.ibd 8 16 | grep n_rows
    slot_nums:16        heaps_rows:67         n_rows:64
    slot_nums:9         heaps_rows:102        n_rows:32
    ...

果真如此，发现第7号page当前有64条记录，这是两个page合并之后的结果。

而第8号page因为已经被合并了，被标记为空闲page，此时已经从索引树里被摘掉了：

[root@yejr.run]# innodb_space -s ibdata1 -T test/t_sk -I PRIMARY -l 0 index-level-summary
    page    index   level   data    free    records min_key
    4       54      0       7536    8692    50      id=1
    5       54      0       15118   1086    100     id=52
    6       54      0       15103   1101    100     id=152
    7       54      0       9768    6456    64      id=320
    9       54      0       15159   1045    100     id=452
    10      54      0       15056   1150    99      id=552

很明显，第8号page已经不在列表中了。只不过page被合并后，里面的物理记录还存着，并没有立即被抹掉，等以后被重用时直接覆盖就好了。

至此，page合并试验结束。

### 3. 其他补充说明 ###

#### 3.1 除了表级可以设置外，单个索引也可以设置合并阈值 ####

对InnoDB来说，其实整个表都是索引页，无非是聚集索引页还是辅助索引页而已。

因此，页合并阈值既可以用于聚集索引页，也可以用于辅助索引页。  
只需要在创建索引时指定即可：

[root@yejr.run]> ALTER TABLE t_sk ADD INDEX k1(c1) COMMENT 'MERGE_THRESHOLD=20';

当然了，这个只能在创建索引时一次性指定，不能中途修改。

然而，表级别的合并阈值则可以在运行时修改：

[root@yejr.run]> ALTER TABLE t_sk COMMENT 'MERGE_THRESHOLD=40';
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0

还可以通过查看元数据确认各个索引的合并阈值设置：

# 直接根据 TABLE_ID 条件查询聚集索引和辅助索引
    # 如果辅助索引创建时没设置阈值，则其阈值设置直接从表级设置中继承
    # 提醒：8.0中 INNODB_SYS_INDEXES 表名变成了 INNODB_INDEXES
    [root@yejr.run]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE select * from information_schema.innodb_sys_Indexes where TABLE_ID = 66\G
    *************************** 1. row ***************************
           INDEX_ID: 54
               NAME: PRIMARY  --聚集索引
           TABLE_ID: 66
               TYPE: 3
           N_FIELDS: 1
            PAGE_NO: 3
              SPACE: 31
    MERGE_THRESHOLD: 30
    *************************** 2. row ***************************
           INDEX_ID: 65
               NAME: k1  --聚集索引
           TABLE_ID: 66
               TYPE: 0
           N_FIELDS: 1
            PAGE_NO: 8
              SPACE: 31
    MERGE_THRESHOLD: 20  --自行设定阈值为20%
    
    # 或者执行SHOW语法
    [root@yejr.run]> SHOW INDEX FROM t_sk\G
    *************************** 1. row ***************************
            Table: t_sk
       Non_unique: 0
         Key_name: PRIMARY
     Seq_in_index: 1
      Column_name: id
        Collation: A
      Cardinality: 494750
         Sub_part: NULL
           Packed: NULL
             Null:
       Index_type: BTREE
          Comment:
    Index_comment:
    *************************** 2. row ***************************
            Table: t_sk
       Non_unique: 1
         Key_name: k1
     Seq_in_index: 1
      Column_name: c1
        Collation: A
      Cardinality: 451839
         Sub_part: NULL
           Packed: NULL
             Null:
       Index_type: BTREE
          Comment:
    Index_comment: MERGE_THRESHOLD=20

#### 3.2 页合并状态监控 ####

页合并的统计情况，可以通过查询 `INNODB_METRICS` 表获取到。

# 先启用该metric
    [root@yejr.run]> set global innodb_monitor_enable="module_index";
    Query OK, 0 rows affected (0.00 sec)
    
    # 一顿删除操作猛如虎出发页合并之后查询
    [root@yejr.run]> SELECT NAME,COUNT,STATUS,COMMENT from INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE 'index_page%merge%';
    +-----------------------------+-------+---------+----------------------------------------+
    | NAME                        | COUNT | STATUS  | COMMENT                                |
    +-----------------------------+-------+---------+----------------------------------------+
    | index_page_merge_attempts   |    13 | enabled | Number of index page merge attempts    |
    | index_page_merge_successful |     1 | enabled | Number of successful index page merges |
    +-----------------------------+-------+---------+----------------------------------------+

通过监控这个metric，如果发现页合并非常频繁的话，可以考虑把 `MERGE_THRESHOLD` 阈值调低。但是设置太低也有风险，因为合并频率降低了，结果会导致更高的数据页碎片率。

#### 3.3 如何适当调整阈值设置 ####

有个不便的地方是，阈值 `MERGE_THRESHOLD` 无法全局设定（`innodb_merge_threshold_set_all_debug`参数只能用于debug版本，正常版本不可设置），而且通过 `INNODB_METRICS` 也无法监控到具体是哪些表上的合并操作最多。因此当发现有很高合并频率时，可能需要扫描所有表，找到那些碎片率较高的表，其产生合并的"嫌疑"应该也较高。

页分裂、合并是个平衡的艺术，如果表DDL设计的好，每条记录的长度基本上一致的话，并且没有频繁的变长更新或删除，那页合并的次数应该不会太高才对。

### 延伸阅读 ###

*  [**InnoDB表聚集索引层高什么时候发生变化**][InnoDB]
 *  [**浅析InnoDB索引结构**][InnoDB 1]
 *  [**Innodb页合并和页分裂**][Innodb]
 *  [**innblock | InnoDB page观察利器**][innblock _ InnoDB page]
 *  **Configuring the Merge Threshold for Index Pages**  
    
 *  **jcole.us：The physical structure of InnoDB index pages**
 *  **jcole.us：B+Tree index structures in InnoDB**

Enjoy MySQL ：）

最后多啰嗦一句，MySQL官方手册真是个宝藏，有事没事多翻翻吧。

全文完。

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

叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧

![format_png][]

[InnoDB]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b574d8a4cde5b83f6fdfb47523ef1ce0b40a6eb89756a8e10be49de5109573d20a7560e74&idx=1&mid=2653932839&scene=21&sn=37200bacf8594cd3fcf4f16592b90c6e#wechat_redirect
[InnoDB 1]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b57278a4cde310e66c2d6b6f86010a65eff78f926bdaab3a9285310ea2a61d55a562a0e41&idx=1&mid=2653932877&scene=21&sn=74fcd85f775b98acefc08d523b312131#wechat_redirect
[Innodb]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b4b9b8a4cc28d4783386f4c07ee4978ea0dbb3a6a5a44daf1588ff045aff0204b3fc8d4cc&idx=1&mid=2653934065&scene=21&sn=b679c440140829ed86f8e10aa7b9ec6a#wechat_redirect
[innblock _ InnoDB page]: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&chksm=bd3b5f168a4cd600ce0c5d01365c762364c38995b0c7764818bd43b95df5517750fb9bb5aa25&idx=1&mid=2653930876&scene=21&sn=000298084f385654e587244d030df064#wechat_redirect
[format_png]: /images/20230209/121b608b51344c00b3a809da29a30d6b.png