您可以为索引页配置MERGE_THRESHOLD值。如果在删除行或通过UPDATE操作缩短行时，索引页的“page-full”百分比低于MERGE_THRESHOLD值，InnoDB将尝试将索引页与相邻索引页合并。默认的MERGE_THRESHOLD值为 50，这是之前硬编码的值。最小MERGE_THRESHOLD值为 1，最大值为 50。You can configure the MERGE_THRESHOLD value for index pages. If the “page-full” percentage for an index page falls below the MERGE_THRESHOLD value when a row is deleted or when a row is shortened by an UPDATE operation, InnoDB attempts to merge the index page with a neighboring index page. The default MERGE_THRESHOLD value is 50, which is the previously hardcoded value. The minimum MERGE_THRESHOLD value is 1 and the maximum value is 50.

当索引页的“页面满度”百分比低于 50%时，这是默认的MERGE_THRESHOLD设置，InnoDB会尝试将该索引页与邻近页合并。如果两页都接近 50%满度，则在页合并之后不久可能会发生页拆分。如果这种合并-拆分行为频繁发生，会对性能产生不利影响。为了避免频繁的合并-拆分，可以降低MERGE_THRESHOLD值，使InnoDB在更低的“页面满度”百分比时尝试合并页面。在较低页面满度百分比下合并页面，索引页会有更多空间，从而有助于减少合并-拆分行为。When the “page-full” percentage for an index page falls below 50%, which is the default MERGE_THRESHOLD setting, InnoDB attempts to merge the index page with a neighboring page. If both pages are close to 50% full, a page split can occur soon after the pages are merged. If this merge-split behavior occurs frequently, it can have an adverse affect on performance. To avoid frequent merge-splits, you can lower the MERGE_THRESHOLD value so that InnoDB attempts page merges at a lower “page-full” percentage. Merging pages at a lower page-full percentage leaves more room in index pages and helps reduce merge-split behavior.

索引页面的 MERGE_THRESHOLD 可以为整张表或单独的索引定义。为单个索引定义的 MERGE_THRESHOLD 值优先于为表定义的 MERGE_THRESHOLD 值。如果未定义，MERGE_THRESHOLD 值默认为 50。The MERGE_THRESHOLD for index pages can be defined for a table or for individual indexes. A MERGE_THRESHOLD value defined for an individual index takes priority over a MERGE_THRESHOLD value defined for the table. If undefined, the MERGE_THRESHOLD value defaults to 50.

为表设置 MERGE_THRESHOLDSetting MERGE_THRESHOLD for a Table

您可以使用 CREATE TABLE 语句的 table_option COMMENT 子句设置表的 MERGE_THRESHOLD 值。例如：You can set the MERGE_THRESHOLD value for a table using the table_option COMMENT clause of the CREATE TABLE statement. For example:

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

您还可以使用 ALTER TABLE 的 table_option COMMENT 子句为现有表设置 MERGE_THRESHOLD 值：You can also set the MERGE_THRESHOLD value for an existing table using the table_option COMMENT clause with ALTER TABLE:

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

ALTER TABLE t1 COMMENT='MERGE_THRESHOLD=40';

为单个索引设置 MERGE_THRESHOLDSetting MERGE_THRESHOLD for Individual Indexes

要为单个索引设置 MERGE_THRESHOLD 值，可以在 CREATE TABLE、ALTER TABLE 或 CREATE INDEX 时使用 index_option COMMENT 子句，如以下示例所示：To set the MERGE_THRESHOLD value for an individual index, you can use the index_option COMMENT clause with CREATE TABLE, ALTER TABLE, or CREATE INDEX, as shown in the following examples:

查询索引的 MERGE_THRESHOLD 值Querying the MERGE_THRESHOLD Value for an Index

可以通过查询 INNODB_INDEXES 表来获取索引的当前 MERGE_THRESHOLD 值。例如：The current MERGE_THRESHOLD value for an index can be obtained by querying the INNODB_INDEXES table. For example:

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 to view the MERGE_THRESHOLD value for a table, if explicitly defined using the table_option COMMENT clause:You can use SHOW CREATE TABLE to view the MERGE_THRESHOLD value for a table, if explicitly defined using the table_option COMMENT clause:

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

同样，如果显式使用 index_option COMMENT 子句定义了索引的 MERGE_THRESHOLD 值，你可以使用 SHOW INDEX 来查看:Likewise, you can use SHOW INDEX to view the MERGE_THRESHOLD value for an index, if explicitly defined using the index_option COMMENT clause:

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 设置效果的测量Measuring the Effect of MERGE_THRESHOLD Settings

INNODB_METRICS 表提供了两个计数器，可用于衡量 MERGE_THRESHOLD 设置对索引页合并的影响。The INNODB_METRICS table provides two counters that can be used to measure the effect of a MERGE_THRESHOLD setting on index page merges.

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 值时，目标是：When lowering the MERGE_THRESHOLD value, the objectives are:

MERGE_THRESHOLD 设置过小可能会导致由于过多空页空间而产生大的数据文件。A MERGE_THRESHOLD setting that is too small could result in large data files due to an excessive amount of empty page space.

有关使用 INNODB_METRICS 计数器的信息，请参阅 第 17.15.6 节，“InnoDB INFORMATION_SCHEMA Metrics 表”。For information about using INNODB_METRICS counters, see Section 17.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.