MySQL 配置索引页的合并阈值

配置索引页的合并阈值
您可以为索引页配置MERGE_THRESHOLD值。如果一个索引页的“page-full”百分比低于MERGE_THRESHOLD值，当一个行被删除或当一个行被UPDATE 操作缩短时，InnoDB会尝试合并这个索引页和相邻的索引页。默认的MERGE_THRESHOLD值是50，这是以前的硬编码值。MERGE_THRESHOLD最小值为1，最大值为50。

当索引页的页满百分比低于50%(默认的MERGE_THRESHOLD设置)时，InnoDB会尝试将索引页与相邻页合并。如果两个页面都接近50%的满，那么在页面合并后不久就会发生页面分割。如果频繁发生这种合并-分割行为，则会对性能产生不利影响。为了避免频繁的合并分割，你可以降低MERGE_THRESHOLD值，这样InnoDB尝试页面合并的百分比就会降低。以较低的页满百分比合并页面会在索引页中留下更多的空间，并有助于减少合并-分割行为。

可以为一个表或单个索引定义索引页的MERGE_THRESHOLD。为单个索引定义的MERGE_THRESHOLD值优先于为表定义的MERGE_THRESHOLD值。如果未定义，MERGE_THRESHOLD值默认为50。

设置表的MERGE_THRESHOLD
可以使用CREATE TABLE语句的table_option COMMENT子句为一个表设置MERGE_THRESHOLD值。例如:

CREATE TABLE t1 (
id INT,
KEY id_index (id)
) COMMENT='MERGE_THRESHOLD=45';

还可以使用ALTER TABLE的table_option COMMENT子句为现有表设置MERGE_THRESHOLD值

CREATE TABLE t1 (
id INT,
KEY id_index (id)
);

ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';

为单个索引设置MERGE_THRESHOLD
要为单个索引设置MERGE_THRESHOLD值，可以使用带有CREATE TABLE、ALTER TABLE或CREATE INDEX的index_option COMMENT子句，如下面的示例所示:
.使用CREATE TABLE为单个索引设置MERGE_THRESHOLD:

mysql> CREATE TABLE t1 (
    -> id INT,
    -> KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40'
    -> );
Query OK, 0 rows affected (0.11 sec)

.使用ALTER TABLE为单个索引设置MERGE_THRESHOLD:

mysql> CREATE TABLE t1 (
    -> id INT,
    -> KEY id_index (id)
    -> );
ALTER TABLE t1 DROP KEY id_index;
ALTER TABLE t1 ADD KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40';Query OK, 0 rows affected (0.17 sec)

mysql> ALTER TABLE t1 DROP KEY id_index;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD KEY id_index (id) COMMENT 'MERGE_THRESHOLD=40';
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

使用CREATE INDEX为单个索引设置MERGE_THRESHOLD:

mysql> CREATE TABLE t1 (id INT);
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

不能在索引级别修改GEN_CLUST_INDEX的MERGE_THRESHOLD值，GEN_CLUST_INDEX是InnoDB在创建InnoDB表时，在没有主键或唯一键索引的情况下创建的聚集索引。只能通过设置表的MERGE_THRESHOLD来修改GEN_CLUST_INDEX的MERGE_THRESHOLD值

查询索引的MERGE_THRESHOLD值
当前索引的MERGE_THRESHOLD值可以通过查询INNODB_SYS_INDEXES表获得。例如:

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE NAME='id_index' \G
*************************** 1. row ***************************
       INDEX_ID: 265
           NAME: id_index
       TABLE_ID: 267
           TYPE: 0
       N_FIELDS: 1
        PAGE_NO: 4
          SPACE: 269
MERGE_THRESHOLD: 40
1 row in set (0.00 sec)

如果使用table_option COMMENT子句显式定义，可以使用SHOW CREATE TABLE查看表的MERGE_THRESHOLD值

mysql> SHOW CREATE TABLE t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  KEY `id_index` (`id`) COMMENT 'MERGE_THRESHOLD=40'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

在索引级别定义的MERGE_THRESHOLD值优先于为表定义的MERGE_THRESHOLD值。如果未定义，MERGE_THRESHOLD默认为50% (MERGE_THRESHOLD=50，这是以前的硬编码值。

同样，如果使用index_option COMMENT子句显式地定义，也可以使用SHOW INDEX查看索引的MERGE_THRESHOLD值:

mysql> show index from t1 \G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: id_index
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment: MERGE_THRESHOLD=40
1 row in set (0.00 sec)

测量MERGE_THRESHOLD设置的效果:

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

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)

当降低MERGE_THRESHOLD值时，目标是:
.页面合并尝试和成功合并的次数更少
.尝试合并和成功合并页面的次数相同

太小的MERGE_THRESHOLD设置由于大量的空页面空间导致大量的数据文件。