![d18b2a9f17ff1bf6aebc0924611b3a3d.png](https://i-blog.csdnimg.cn/blog_migrate/c7d5d594201a96add54af3cf2805a441.png)
场景二:
页 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%。
1. 表的 MERGE_THRESHOLD
对整张表设置 MERGE_THRESHOLD,需要把这个值放入表的 comment 中。INNODB 并不是第一个在表注释里定义对表数据控制选项的, MySQL 之前的列式引擎 brighthouse 早这么干过,把对表的一些利于优化的 HINT 放入到 comment 里。具体的语法为:mysql> create table sample1(id int primary key,r1 int, r2 varchar(1000)) comment 'MERGE_THRESHOLD=40';Query OK, 0 rows affected (0.08 sec)
或者是针对之前的表更改 MERGE_THRESHOLD 值
mysql> alter table t1 comment 'MERGE_THRESHOLD=40';Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
2. 索引的 MERGE_THRESHOLD
也可以针对单个索引列设置 MERGE_THRESHOLD 值,单个列的 MERGE_THRESHOLD 优先级比表高,也就是会覆盖掉表的设置。mysql> create table t1(id int, key idx_id(id) comment 'MERGE_THRESHOLD=40');Query OK, 0 rows affected (0.08 sec)
或者先删除索引,再建立新的。
mysql> alter table t1 drop key idx_id, add key idx_id(id) comment 'MERGE_THRESHOLD=40';Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0
或者,
mysql> create index idx_id on t1(id) comment 'MERGE_THRESHOLD=40';Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0
3. 查看 MERGE_THRESHOLD
可以通过查看表定义信息,mysql> show create table sample1\G...) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='MERGE_THRESHOLD=40'1 row in set (0.00 sec)
可以通过单独查看索引定义信息,
mysql> show index from t1\G... Comment:Index_comment: MERGE_THRESHOLD=401 row in set (0.00 sec)
或者可以通过
数据字典表 information_schema.innodb_indexes 来获取表里所有对象的 MERGE_THRESHOLD 值设定。
mysql> select a.name as tablename,b.name as index_name, b.MERGE_THRESHOLD from innodb_tables as a,innodb_indexes as b where a.table_id = b.table_id and a.name like 'ytt%';+-------------+-----------------+-----------------+| tablename | index_name | MERGE_THRESHOLD |+-------------+-----------------+-----------------+| ytt/sample1 | PRIMARY | 40 || ytt/t1 | GEN_CLUST_INDEX | 50 || ytt/t1 | idx_id | 40 |+-------------+-----------------+-----------------+3 rows in set (0.00 sec)
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, key idx_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_count FROM 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_count FROM 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_count FROM 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 索引页合并临界值的概念以及如何在实际环境中评估这个值对索引页合并以及拆分的影响,有问题或者相关建议欢迎指正回复。
文章推荐:
第11期:压缩表第12期:压缩表性能监测第13期:表统计信息的计算
关于 MySQL 的技术内容,你们还有什么想知道的吗?赶紧 留言告诉小编吧!