MySQL :: MySQL 8.4 参考手册 :: 17.8.11 配置索引页的合并阈值

版本 8.4

8.0 日语

MySQL 8.4 参考手册 / ... / 配置索引页的合并阈值

17.8.11 配置索引页的合并阈值

您可以为索引页配置 MERGE_THRESHOLD 值。如果在删除行或通过 UPDATE 操作缩短行时，索引页的“页面已满”百分比低于 MERGE_THRESHOLD 值，则 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 值，您可以将 index_option COMMENT 子句与 CREATE TABLE、ALTER TABLE 或 CREATE INDEX 一起使用，如下例所示

使用 CREATE TABLE 为单个索引设置 MERGE_THRESHOLD

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

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

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';

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

CREATE TABLE t1 (id INT);
CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';

注意

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

查询索引的 MERGE_THRESHOLD 值

可以通过查询 INNODB_INDEXES 表来获取索引的当前 MERGE_THRESHOLD 值。例如

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_INDEXES WHERE NAME='id_index' \G
*************************** 1. row ***************************
       INDEX_ID: 91
           NAME: id_index
       TABLE_ID: 68
           TYPE: 0
       N_FIELDS: 1
        PAGE_NO: 4
          SPACE: 57
MERGE_THRESHOLD: 40

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

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

注意

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

同样，您可以使用 SHOW INDEX 查看索引的 MERGE_THRESHOLD 值（如果使用 index_option COMMENT 子句显式定义）。

mysql> SHOW INDEX FROM t2 \G
*************************** 1. row ***************************
        Table: t2
   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

测量 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 |
+-----------------------------+----------------------------------------+

降低 MERGE_THRESHOLD 值的目标是：

减少页面合并尝试次数和成功的页面合并次数。
页面合并尝试次数和成功的页面合并次数相似。

过小的 MERGE_THRESHOLD 设置可能会导致数据文件过大，因为存在过多的空页空间。

有关使用 INNODB_METRICS 计数器的信息，请参阅第 17.15.6 节“InnoDB INFORMATION_SCHEMA 指标表”。