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

版本 8.4

8.0 日文

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

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 欄位中

Using intersect(...)
Using union(...)
Using 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 節，「最佳化工具提示」。