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設定由於大量的空頁面空間導致大量的資料檔案。