MySQL配置索引页的合并阈值

最新推荐文章于 2025-09-27 18:56:24 发布

转载最新推荐文章于 2025-09-27 18:56:24 发布 · 152 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/wanbin/p/9899615.html

文章标签：

#数据库

本文介绍如何在MySQL中配置索引页的合并阈值(MERGE_THRESHOLD)，以优化索引页的空间利用率并减少合并拆分行为。文章详细说明了为表或单个索引设置该阈值的方法，并提供了查询及调整阈值效果的具体步骤。

MySQL配置索引页的合并阈值

如果删除行或者通过UPDATE操作缩短行
可以为索引页面配置MERGE_THRESHOLD值。当delete与update缩短了行长度时，索引页的“page-full”百分比低于MERGE_THRESHOLD值，InnoDB会尝试将索引页与相邻索引页合并。默认的MERGE_THRESHOLD值为50。 MERGE_THRESHOLD的最小值为1，最大值为50。

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

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

1. 为表设置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';

2. 为单个索引设置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';

3. 查询索引的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子句显式定义，则可以使用SHOW CREATE 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

4. 查看MERGE_THRESHOLD设置的效果

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

SELECT t.NAME,
       t.SUBSYSTEM,
       t.STATUS,
       t.COMMENT
FROM INFORMATION_SCHEMA.INNODB_METRICS t
WHERE t.NAME LIKE '%index_page_merge%';

+-----------------------------+-----------+----------+----------------------------------------+
| NAME                        | SUBSYSTEM | STATUS   | COMMENT                                |
+-----------------------------+-----------+----------+----------------------------------------+
| index_page_merge_attempts   | index     | disabled | Number of index page merge attempts    |
| index_page_merge_successful | index     | disabled | Number of successful index page merges |
+-----------------------------+-----------+----------+----------------------------------------+
2 rows in set (0.00 sec)