MySQL :: MySQL 9.0 參考手冊 :: 17.8.11 設定索引頁面的合併閾值

版本 9.0

8.4 目前版本
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 設定索引頁面的合併閾值

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';

注意

您無法在索引層級修改 GEN_CLUST_INDEX 的 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 度量表格」。

上一步首頁上一層下一步