MySQL 索引页合并MERGE_THRESHOLD阈值

索引页MERGE介绍

在MySQL中Innodb引擎表中删除一行或通过UPDATE操作缩短一行时，如果索引页的“页满”百分比低于MERGE_THRESHOLD值，InnoDB会尝试将索引页与相邻的索引页合并。默认的MERGE_THRESHOLD值为50，这是以前硬编码的值。MERGE_THRESHOLD的最小值为1，最大值为50。可以为表或单个索引定义索引页的MERGE_THRESHOLD。为单个索引定义的MERGE_THRESHOLD值优先于为表定义的MERGE_THRESOLD值。

索引页MERGE值设置

可以通过查询INNODB_INDEXES表来获得索引字段MERGE_THRESHOLD值：

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='idx_lname' G;
*************************** 1. row ***************************
INDEX_ID: 603
NAME: idx_lname
TABLE_ID: 1369
TYPE: 0
N_FIELDS: 2
PAGE_NO: 5
SPACE: 307
MERGE_THRESHOLD: 50
1 row in set (0.01 sec)


MERGE_THRESHOLD没有单独的设置语句，是以COMMENT方式体现在SQL中。跟注释明显冲突。

mysql> ALTER TABLE employees DROP INDEX idx_lname,ADD INDEX idx_lname(last_name) COMMENT 'MERGE_THRESHOLD=30';

mysql> SHOW CREATE TABLE employees;
+-----------+-------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
`emp_no` int NOT NULL,
。。。
PRIMARY KEY (`emp_no`),
KEY `idx_lname` (`last_name`) COMMENT 'MERGE_THRESHOLD=30'
) ENGINE=InnoDB
+-----------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


还可以使用带有ALTER TABLE的table_option COMMENT子句为表级别全局设置MERGE_THRESHOLD值：

mysql> ALTER TABLE employees COMMENT='MERGE_THRESHOLD=40';


如果两个页面都接近50%满，则页面合并后不久可能会发生页面拆分。如果这种合并-拆分行为频繁发生，可能会对性能产生不利影响。为了避免频繁的合并拆分，可以降低MERGE_THRESHOLD值，以便InnoDB尝试以较低的“页面已满”百分比进行页面合并。以较低的页面满百分比合并页面会在索引页面中留下更多空间，并有助于减少合并拆分行为。当设置高于阈值50值时，会提供Warning警告提示信息，但底层的表结构还是被强制更改。

mysql> ALTER TABLE employees COMMENT='MERGE_THRESHOLD=80';
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 1

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------------------------------+
| Warning | 1478 | InnoDB: Invalid value for MERGE_THRESHOLD in the CREATE TABLE statement. The
value is ignored. |
+---------+------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table employees;
+-----------+-------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+-------------------------------------------------------------------------------------+
| employees | CREATE TABLE `employees` (
`id` int NOT NULL,
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
`manager_id` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='MERGE_THRESHOLD=80' |
+-----------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


INNODB_METRICS表提供了两个计数器，可用于测量MERGE_THRESHOLD设置对索引页合并的影响。

mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME like '%index_page_merge%';
+-----------------------------+-----------+----------+
| name | subsystem | status |
+-----------------------------+-----------+----------+
| index_page_merge_attempts | index | disabled |
| index_page_merge_successful | index | disabled |
+-----------------------------+-----------+----------+
2 rows in set (0.00 sec)

#开启统计
mysql> SET GLOBAL innodb_monitor_enable = index_page_merge_attempts;
mysql> SET GLOBAL innodb_monitor_enable = index_page_merge_successful;

mysql> SELECT name, subsystem, status FROM INFORMATION_SCHEMA.INNODB_METRICS
WHERE NAME like '%index_page_merge%';
+-----------------------------+-----------+---------+
| name | subsystem | status |
+-----------------------------+-----------+---------+
| index_page_merge_attempts | index | enabled |
| index_page_merge_successful | index | enabled |
+-----------------------------+-----------+---------+


索引页MERGE效果

MERGE_THRESHOLD设置的合并效果。先采取默认值

mysql> CREATE TABLE `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`tno` varchar(10),
`name` varchar(10) ,
`addr` varchar(10) ,
PRIMARY KEY (`id`),
KEY `idx_no` (`tno`)
) ENGINE=InnoDB ;

#使用mysqlslap插入1000条记录：
shell> mysqlslap -uroot -p123456 -S /opt/data8.0/data/mysql.sock --concurrency=10 --create-schema='demo' --query="insert into test(tno,name,addr) select 'A','BB','CCC';" --number-of-queries=1000

# 删除数据500条记录：
mysql> delete from test where id SELECT name,subsystem,count,TIME_ENABLED FROM INFORMATION_SCHEMA.INNODB_METRICS t
WHERE t.NAME in('index_page_merge_attempts','index_page_merge_successful');
+-----------------------------+-----------+-------+---------------------+
| name | subsystem | count | TIME_ENABLED |
+-----------------------------+-----------+-------+---------------------+
| index_page_merge_attempts | index | 299 | 2024-01-15 10:51:46 |
| index_page_merge_successful | index | 3 | 2024-01-15 10:51:46 |
+-----------------------------+-----------+-------+---------------------+
2 rows in set (0.00 sec)


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

当MERGE_THRESHOLD改成1
把所有数据删除时，查看计数器结果，尝试合并 5次，合并成功 5次。基本上合并非常少。

mysql> ALTER TABLE employees COMMENT='MERGE_THRESHOLD=1';
mysql> delete from test where id SELECT name,subsystem,count,TIME_ENABLED FROM INFORMATION_SCHEMA.INNODB_METRICS t
WHERE t.NAME in('index_page_merge_attempts','index_page_merge_successful');
+-----------------------------+-----------+-------+---------------------+
| name | subsystem | count | TIME_ENABLED |
+-----------------------------+-----------+-------+---------------------+
| index_page_merge_attempts | index | 5 | 2024-01-15 11:06:27 |
| index_page_merge_successful | index | 5 | 2024-01-15 11:06:28 |
+-----------------------------+-----------+-------+---------------------+
2 rows in set (0.00 sec)


总结

索引合并值（update 和delete时），按照规律，阈值接近于50%时，就会出现合并的同时会进行分裂操作。所以降低MERGE_THRESHOLD，可以避免分裂，但有可能会导致页不满的情况。最好的情况是：

• 两值相似：页面合并尝试次数=成功的页面合并次
• 减少：页面合并尝试次数和成功的页面合并次数
  MERGE_THRESHOLD设置过小可能会由于过多的空页面空间而导致数据文件过大。所以想调整也应该在45~50范围内。

除了合并，当然也有索引的分裂和重组（insert，update，delete）。INFORMATION_SCHEMA.INNODB_METRICS里提供参考指标index_page_splits，index_page_reorg_attempts和index_page_reorg_successful。目前没有阈值可以控制这部分。按照innodb_page_size大小和二叉树构造，底层硬码控制。