InnoDB数据页什么时候合并
source link: http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ%3D%3D&%3Bmid=2653934124&%3Bidx=1&%3Bsn=7dfb10b8569eaec8a66b907d0d220b77
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
1. 为什么要合并数据页 2. 什么时候合并数据页 2.1 准备测试环境 2.2 找到两个相邻页 2.3 试探性逐步删除数据，接近阈值 2.4 再次只删除一条记录，验证是否合并 3. 其他补充说明 3.1 除了表级可以设置外，单个索引也可以设置合并阈值 3.2 页合并状态监控 3.3 如何适当调整阈值设置 延伸阅读
当低于设定的阈值时就进行合并
在本文开始前，可以先复习这几篇文章
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中的数据：
[[email protected]]# innodb_space -s ibdata1 -T test/t_sk -p 7 page-records > recs-no7.txt
[[email protected]]# 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的结果：
[[email protected]]# 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条数据，只差一条数据就达到临界点，看看后续会不会发生合并。
[[email protected]]> delete from t_sk where id>=252 and id <= 318;
Query OK, 67 rows affected (0.01 sec)
[[email protected]]> delete from t_sk where id>=352 and id <= 418;
Query OK, 67 rows affected (0.00 sec)
先用 innblock
工具快速扫描：
[[email protected]]# 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
[[email protected]]# 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
[[email protected]]# 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再各自删除一条记录，使得填充率低于临界点：
[[email protected]]> delete from t_sk where id in (319, 419);
Query OK, 2 rows affected (0.01 sec)
还是用 innblock
工具扫描，不过这次先扫描第7号page，看看是不是把8号page合并过来了。
[[email protected]]# 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，此时已经从索引树里被摘掉了：
[[email protected]]# 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来说，其实整个表都是索引页，无非是聚集索引页还是辅助索引页而已。
因此，页合并阈值既可以用于聚集索引页，也可以用于辅助索引页。
只需要在创建索引时指定即可：
[[email protected]]> ALTER TABLE t_sk ADD INDEX k1(c1) COMMENT 'MERGE_THRESHOLD=20';
当然了，这个只能在创建索引时一次性指定，不能中途修改。
然而，表级别的合并阈值则可以在运行时修改：
[[email protected]]> 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
[[email protected]]> 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语法
[[email protected]]> 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
[[email protected]]> set global innodb_monitor_enable="module_index";
Query OK, 0 rows affected (0.00 sec)
# 一顿删除操作猛如虎出发页合并之后查询
[[email protected]]> 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设计的好，每条记录的长度基本上一致的话，并且没有频繁的变长更新或删除，那页合并的次数应该不会太高才对。
延伸阅读
-
Configuring the Merge Threshold for Index Pages
-
jcole.us：The physical structure of InnoD B index pages
-
jcole.us：B+Tree index structures in InnoDB
Enjoy MySQL ：）
最后多啰嗦一句，MySQL官方手册真是个宝藏，有事没事多翻翻吧。
全文完。
叶老师的「MySQL核心优化」大课已升级到MySQL 8.0，扫码开启MySQL 8.0修行之旅吧
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK