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 值，您可以使用 CREATE TABLE、ALTER TABLE 或 CREATE INDEX 的 index_option COMMENT 子句，如下列範例所示

使用 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 值，此值適用於 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 Metrics 表格」。

上一步首頁上一層下一步