14.8.12 配置索引页的合并阈值

您可以为索引页配置MERGE_THRESHOLD值。如果删除行或通过UPDATE操作缩短行时索引页的“页面已满”百分比低于MERGE_THRESHOLD值，则InnoDB会尝试将索引页与相邻的索引页合并。 MERGE_THRESHOLD的默认值为 50，这是以前的硬编码值。 MERGE_THRESHOLD的最小值为 1，最大值为 50.

当索引页面的“页面已满”百分比低于默认值MERGE_THRESHOLD的 50％以下时，InnoDB尝试将索引页面与相邻页面合并。如果两个页面都接近 50％充满，则页面合并后可能会很快发生页面拆分。如果此合并拆分行为频繁发生，则可能会对性能产生不利影响。为了避免频繁的合并拆分，可以降低MERGE_THRESHOLD的值，以便InnoDB尝试以较低的“页面已满”百分比进行页面合并。以较低的页面占满百分比合并页面会在索引页面中留出更多空间，并有助于减少合并分割行为。

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

为 table 格设置 MERGE_THRESHOLD

您可以使用CREATE TABLE语句的* table_option * COMMENT子句为 table 设置MERGE_THRESHOLD值。例如：

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

您还可以使用带有ALTER TABLE的* table_option * COMMENT子句为现有 table 设置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';

查询索引的 MERGE_THRESHOLD 值

可以通过查询INNODB_SYS_INDEXEStable 获得索引的当前MERGE_THRESHOLD值。例如：

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_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

如果使用* table_option * COMMENT子句明确定义，则可以使用显示创建 table查看 table 的MERGE_THRESHOLD值：

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=latin1

同样，如果使用* index_option * COMMENT子句明确定义，则可以使用SHOW INDEX查看索引的MERGE_THRESHOLD值：

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_METRICStable 提供了两个计数器，这些计数器可用于衡量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计数器的信息，请参见第 14.16.6 节“ InnoDB INFORMATION_SCHEMAMetricstable”。