第14期：数据页合并( 二 ) MySQLInnoDB表数据页或者二级索引

4. MERGE_THRESHOLD 设置效果评估
innodb_metrics 表提供了两个计数器来跟踪页合并（Innodb_metrics 表非常有用，后期单独开篇详细来介绍。）
这两个计数器默认是屏蔽的，需要显式开启，
mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS-> WHERE NAME like '%index_page_merge%';+-----------------------------+----------------------------------------+| NAME| COMMENT|+-----------------------------+----------------------------------------+| index_page_merge_attempts| Number of index page merge attempts|| index_page_merge_successful | Number of successful index page merges |+-----------------------------+----------------------------------------+2 rows in set (0.00 sec)开启这两个计数器，
mysql> set global innodb_monitor_enable='index_page_merge_attempts';Query OK, 0 rows affected (0.00 sec)mysql> set global innodb_monitor_enableQuery OK, 0 rows affected (0.00 sec)建立两张表， MERGE_THRESHOLD 分别为默认值和 20 ，导入同样的 10000 条记录，看看页面合并的对比。
mysql> create table t1_max(id int primary key ， r1 int, keyidx_r1 (r1));Query OK, 0 rows affected (0.08 sec)mysql> create table t1_min(id int, primary key (id) comment 'MERGE_THRESHOLDQuery OK, 0 rows affected (0.08 sec)对比前，先清空计数器；禁止后；重置计数器。
mysql> set global innodb_monitor_disable='index_page_merge_attempts';Query OK, 0 rows affected (0.00 sec)mysql> set global innodb_monitor_disableQuery OK, 0 rows affected (0.00 sec)mysql> set global innodb_monitor_reset_allQuery OK, 0 rows affected (0.00 sec)mysql> set global innodb_monitor_enableQuery OK, 0 rows affected (0.00 sec)先往表 t1_max 里随机插入 1000 条记录。
replace into t1_max select ceil(rand()*1000),ceil(rand()*100) ；...mysql> select count(*) from t1_max+----------+| count(*) |+----------+|1000 |+----------+1 row in set (0.03 sec)再删掉 500 条记录
mysql> delete from t1_max limit 500;Query OK, 500 rows affected (0.05 sec)查看计数器结果，尝试合并 707 次，合并成功 20 次。
mysql> SELECT name,count,max_count,avg_countFROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME like '%index_page_merge%';+-----------------------------+-------+-----------+--------------------+| name| count | max_count | avg_count|+-----------------------------+-------+-----------+--------------------+|| index_page_merge_successful |20 |20 |0.072992700729927 |+-----------------------------+-------+-----------+--------------------+2 rows in set (0.00 sec)再清空计数器，对表 t1_min 执行同样的操作，
mysql> select count(*) from t1_min;+----------+| count(*) |+----------+|500 |+----------+1 row in set (0.02 sec)mysql> delete from t1_min limit 500Query OK, 500 rows affected (0.02 sec)再次查看计数器，尝试合并 30 次，实际成功次数仅仅为 2 。比默认的合并次数少了 20 多倍。所以可以看到，在一定的数据模型下，手动控制合并临界值对数据页的合并频率调节非常有效。
mysql> SELECT name,count,max_count,avg_countFROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME like '%index_page_merge%';+-----------------------------+-------+-----------+---------------------+| name| count | max_count | avg_count|+-----------------------------+-------+-----------+---------------------+|| index_page_merge_successful |2 |2 | 0.03333333333333333 |+-----------------------------+-------+-----------+---------------------+2 rows in set (0.00 sec)当然，设置成最小值 1 ，基本上不会合并了，结果应该如下。
mysql> SELECT name,count,max_count,avg_countFROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME like '%index_page_merge%';+-----------------------------+-------+-----------+-----------+| name| count | max_count | avg_count |+-----------------------------+-------+-----------+-----------+|| index_page_merge_successful |0 |NULL |0 |+-----------------------------+-------+-----------+-----------+2 rows in set (0.00 sec)总结这篇我介绍了 MySQL 索引页合并临界值的概念以及如何在实际环境中评估这个值对索引页合并以及拆分的影响，有问题或者相关建议欢迎指正回复。
关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！
文章插图