MySQL :: MySQL 9.0 參考手冊 :: 10.2.1.3 索引合併最佳化

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 索引合併最佳化

10.2.1.3 索引合併最佳化

索引合併存取方法會使用多個 range 掃描來擷取資料列，並將它們的結果合併成一個。此存取方法只會合併單一資料表的索引掃描，而不會合併多個資料表的掃描。合併可以產生其基礎掃描的聯集、交集或聯集交集。

可能使用索引合併的範例查詢

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

注意

索引合併最佳化演算法有下列已知限制

如果您的查詢具有複雜的 WHERE 子句，其中包含深層的 AND/OR 巢狀結構，而 MySQL 未選擇最佳計畫，請嘗試使用下列恆等轉換來分配詞語
```
(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)
```
索引合併不適用於全文索引。

在 EXPLAIN 輸出中，索引合併方法會在 type 資料行中顯示為 index_merge。在這種情況下，key 資料行包含所使用索引的清單，而 key_len 包含這些索引最長金鑰部分的清單。

索引合併存取方法具有數個演算法，這些演算法會顯示在 EXPLAIN 輸出的 Extra 欄位中

使用 intersect(...)
使用 union(...)
使用 sort_union(...)

以下章節會更詳細地說明這些演算法。最佳化工具會根據各種可用選項的成本估計，在不同可能的索引合併演算法和其他存取方法之間進行選擇。

索引合併交集存取演算法

當 WHERE 子句被轉換為以 AND 結合的不同鍵上的多個範圍條件，且每個條件符合以下其中一項時，適用此存取演算法：

此形式的 N 部分的表達式，其中索引正好有 N 個部分（即涵蓋所有索引部分）。
```
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
```
在 InnoDB 表的主鍵上的任何範圍條件。

範例

SELECT * FROM innodb_table
  WHERE primary_key < 10 AND key_col1 = 20;

SELECT * FROM tbl_name
  WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

索引合併交集演算法對所有使用的索引執行同步掃描，並產生從合併的索引掃描接收到的列序列的交集。

如果查詢中使用的所有欄位都包含在使用索引中，則不會檢索完整的資料列（EXPLAIN 輸出在這種情況下會在 Extra 欄位中包含 Using index）。以下是一個此類查詢的範例：

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;

如果使用的索引沒有涵蓋查詢中使用的所有欄位，只有在所有使用鍵的範圍條件都滿足時，才會檢索完整資料列。

如果合併的條件之一是針對 InnoDB 表的主鍵的條件，則它不用於資料列檢索，而是用於篩選使用其他條件檢索的資料列。

索引合併聯集存取演算法

此演算法的標準與索引合併交集演算法的標準相似。當表格的 WHERE 子句被轉換為以 OR 結合的不同鍵上的多個範圍條件，且每個條件符合以下其中一項時，適用此演算法：

此形式的 N 部分的表達式，其中索引正好有 N 個部分（即涵蓋所有索引部分）。
```
key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN
```
在 InnoDB 表的主鍵上的任何範圍條件。
適用於索引合併交集演算法的條件。

範例

SELECT * FROM t1
  WHERE key1 = 1 OR key2 = 2 OR key3 = 3;

SELECT * FROM innodb_table
  WHERE (key1 = 1 AND key2 = 2)
     OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;

索引合併排序聯集存取演算法

當 WHERE 子句被轉換為以 OR 結合的多個範圍條件，但索引合併聯集演算法不適用時，則適用此存取演算法。

範例

SELECT * FROM tbl_name
  WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;

排序聯集演算法與聯集演算法之間的區別在於，排序聯集演算法必須先獲取所有資料列的列 ID 並進行排序，然後才能傳回任何資料列。

影響索引合併最佳化

索引合併的使用取決於 index_merge、index_merge_intersection、index_merge_union 和 index_merge_sort_union 旗標的值，這些旗標屬於 optimizer_switch 系統變數。請參閱第 10.9.2 節，「可切換的最佳化」。預設情況下，所有這些旗標都設為 on。若要僅啟用某些演算法，請將 index_merge 設為 off，並且僅啟用應該允許的其他演算法。

除了使用 optimizer_switch 系統變數來控制最佳化器在整個會話期間使用索引合併演算法之外，MySQL 還支援最佳化器提示，以在每個語句的基礎上影響最佳化器。請參閱第 10.9.3 節，「最佳化器提示」。

上一步首頁上一層下一步