电脑怎么样合并内存条频率(第14期：数据页合并)

MySQLInnoDB表数据页或者二级索引页（简称数据页或者索引页）的合并与分裂对InnoDB表整体性能影响很大；数据页的这类操作越多，对InnoDB表数据写入的影响越大。

MySQL提供了一个数据页合并临界值（MERGE_THRESHOLD），在某些场景下，可以人为介入，减少数据页的合并与分裂。

在InnoDB表里，每个数据页默认16K大小，默认MERGE_THRESHOLD值为50，取值范围从1到50，默认值即是最大值。也就是当页面记录数占比小于50%时，MySQL会把这页和相邻的页面进行合并，保证数据页的紧凑，避免太多浪费。

触发临界值场景

场景一：

页A里本来数据占用100%，有一部分记录被删掉后，数据占用小于50%，刚好触发了临界值。

场景二：

页B里存放的记录被更新为更短的形式，比如记录值由rpad（'我爱你们所有人',10000,'添加冗余字符'）变为'我只爱你'，这时候记录对数据页占用也小于50%，刚好触发了临界值。

简述数据页的合并

页A在删除一些记录后，此页里剩余记录对页A的占用小于MERGE_THRESHOLD设定的值，此时刚好页A相邻的一个页C，数据占用也不到50%，这时候MySQL会把页C的记录并入页A，之后页C的空间就被释放，不包含任何数据，页C就可用于以后新记录的写入，避免空间的浪费。

简述数据页的分裂页D和页E，两个页面记录占用都在49%。那么页合并后，页D记录占用98%，只剩下2%。页F和页H，两个页面记录占用也都是49%，那么合并后，页F记录占用98%，也只剩下2%。

此时有新的插入请求过来，这条记录的主键刚好在页D和页F之间，可是页D和页F都只剩下2%的空间，不够插入这条记录。那怎么办？此时只能拆分页D。建立一个新的页I，完了把页D原来的记录和新插入的记录做一个排序，再按照新的顺序把页D填满，剩下的数据放到页I。所以页分裂会涉及到老页数据的迁移到新建页的建立，如果页的分裂频繁，那开销很大。

下来看看MERGE_THRESHOLD的实际用法与监测。

注意！MERGE_THRESHOLD不能小写，必须大写！小写就会被MySQL当作简单的注释。

1.表的MERGE_THRESHOLD

对整张表设置MERGE_THRESHOLD，需要把这个值放入表的comment中。INNODB并不是第一个在表注释里定义对表数据控制选项的，MySQL之前的列式引擎brighthouse早这么干过，把对表的一些利于优化的HINT放入到comment里。具体的语法为：

mysql>createtablesample1(idintprimarykey,r1int,r2varchar(1000))comment'MERGE_THRESHOLD=40';QueryOK,0rowsaffected(0.08sec)

或者是针对之前的表更改MERGE_THRESHOLD值

mysql>altertablet1comment'MERGE_THRESHOLD=40';QueryOK,0rowsaffected(0.02sec)Records:0Duplicates:0Warnings:0

2.索引的MERGE_THRESHOLD

也可以针对单个索引列设置MERGE_THRESHOLD值，单个列的MERGE_THRESHOLD优先级比表高，也就是会覆盖掉表的设置。

mysql>createtablet1(idint,keyidx_id(id)comment'MERGE_THRESHOLD=40');QueryOK,0rowsaffected(0.08sec)

或者先删除索引，再建立新的。

mysql>altertablet1dropkeyidx_id,addkeyidx_id(id)comment'MERGE_THRESHOLD=40';QueryOK,0rowsaffected(0.02sec)Records:0Duplicates:0Warnings:0

或者，

mysql>createindexidx_idont1(id)comment'MERGE_THRESHOLD=40';QueryOK,0rowsaffected(0.05sec)Records:0Duplicates:0Warnings:0

3.查看MERGE_THRESHOLD

可以通过查看表定义信息，

mysql>showcreatetablesample1\G...)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ciCOMMENT='MERGE_THRESHOLD=40'1rowinset(0.00sec)

可以通过单独查看索引定义信息，

mysql>showindexfromt1\G...Comment:Index_comment:MERGE_THRESHOLD=401rowinset(0.00sec)

或者可以通过数据字典表information_schema.innodb_indexes来获取表里所有对象的MERGE_THRESHOLD值设定。

mysql>selecta.nameastablename,b.nameasindex_name,b.MERGE_THRESHOLDfrominnodb_tablesasa,innodb_indexesasbwherea.table_id=b.table_idanda.namelike'ytt%';+-------------+-----------------+-----------------+|tablename|index_name|MERGE_THRESHOLD|+-------------+-----------------+-----------------+|ytt/sample1|PRIMARY|40||ytt/t1|GEN_CLUST_INDEX|50||ytt/t1|idx_id|40|+-------------+-----------------+-----------------+3rowsinset(0.00sec)

4.MERGE_THRESHOLD设置效果评估

innodb_metrics表提供了两个计数器来跟踪页合并（Innodb_metrics表非常有用，后期单独开篇详细来介绍。）

这两个计数器默认是屏蔽的，需要显式开启，

mysql>SELECTNAME,COMMENTFROMINFORMATION_SCHEMA.INNODB_METRICS->WHERENAMElike'%index_page_merge%';+-----------------------------+----------------------------------------+|NAME|COMMENT|+-----------------------------+----------------------------------------+|index_page_merge_attempts|Numberofindexpagemergeattempts||index_page_merge_successful|Numberofsuccessfulindexpagemerges|+-----------------------------+----------------------------------------+2rowsinset(0.00sec)

开启这两个计数器，

mysql>setglobalinnodb_monitor_enable='index_page_merge_attempts';QueryOK,0rowsaffected(0.00sec)mysql>setglobalinnodb_monitor_enableQueryOK,0rowsaffected(0.00sec)

建立两张表，MERGE_THRESHOLD分别为默认值和20，导入同样的10000条记录，看看页面合并的对比。

mysql>createtablet1_max(idintprimarykey，r1int,keyidx_r1(r1));QueryOK,0rowsaffected(0.08sec)mysql>createtablet1_min(idint,primarykey(id)comment'MERGE_THRESHOLDQueryOK,0rowsaffected(0.08sec)

对比前，先清空计数器；禁止后；重置计数器。

mysql>setglobalinnodb_monitor_disable='index_page_merge_attempts';QueryOK,0rowsaffected(0.00sec)mysql>setglobalinnodb_monitor_disableQueryOK,0rowsaffected(0.00sec)mysql>setglobalinnodb_monitor_reset_allQueryOK,0rowsaffected(0.00sec)mysql>setglobalinnodb_monitor_enableQueryOK,0rowsaffected(0.00sec)

先往表t1_max里随机插入1000条记录。

replaceintot1_maxselectceil(rand()*1000),ceil(rand()*100)；...mysql>selectcount(*)fromt1_max+----------+|count(*)|+----------+|1000|+----------+1rowinset(0.03sec)

再删掉500条记录

mysql>deletefromt1_maxlimit500;QueryOK,500rowsaffected(0.05sec)

查看计数器结果，尝试合并707次，合并成功20次。

mysql>SELECTname,count,max_count,avg_countFROMINFORMATION_SCHEMA.INNODB_METRICSWHERENAMElike'%index_page_merge%';+-----------------------------+-------+-----------+--------------------+|name|count|max_count|avg_count|+-----------------------------+-------+-----------+--------------------+||index_page_merge_successful|20|20|0.072992700729927|+-----------------------------+-------+-----------+--------------------+2rowsinset(0.00sec)

再清空计数器，对表t1_min执行同样的操作，

mysql>selectcount(*)fromt1_min;+----------+|count(*)|+----------+|500|+----------+1rowinset(0.02sec)mysql>deletefromt1_minlimit500QueryOK,500rowsaffected(0.02sec)

再次查看计数器，尝试合并30次，实际成功次数仅仅为2。比默认的合并次数少了20多倍。所以可以看到，在一定的数据模型下，手动控制合并临界值对数据页的合并频率调节非常有效。

mysql>SELECTname,count,max_count,avg_countFROMINFORMATION_SCHEMA.INNODB_METRICSWHERENAMElike'%index_page_merge%';+-----------------------------+-------+-----------+---------------------+|name|count|max_count|avg_count|+-----------------------------+-------+-----------+---------------------+||index_page_merge_successful|2|2|0.03333333333333333|+-----------------------------+-------+-----------+---------------------+2rowsinset(0.00sec)

当然，设置成最小值1，基本上不会合并了，结果应该如下。

mysql>SELECTname,count,max_count,avg_countFROMINFORMATION_SCHEMA.INNODB_METRICSWHERENAMElike'%index_page_merge%';+-----------------------------+-------+-----------+-----------+|name|count|max_count|avg_count|+-----------------------------+-------+-----------+-----------+||index_page_merge_successful|0|NULL|0|+-----------------------------+-------+-----------+-----------+2rowsinset(0.00sec)

总结

这篇我介绍了MySQL索引页合并临界值的概念以及如何在实际环境中评估这个值对索引页合并以及拆分的影响，有问题或者相关建议欢迎指正回复。

关于MySQL的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！