MySQL :: MySQL 8.4 參考手冊 :: 10.2.1.2 範圍最佳化

版本 8.4

9.0
8.0
5.7

8.0 日文

MySQL 8.4 參考手冊 / ... / 範圍最佳化

10.2.1.2 範圍最佳化

range 存取方法使用單一索引來擷取包含在一個或多個索引值區間內的資料表列子集。它可以針對單部分或多部分索引使用。以下各節說明最佳化工具使用範圍存取的情況。

單部分索引的範圍存取方法

對於單部分索引，索引值區間可以使用 WHERE 子句中的對應條件來方便地表示，以範圍條件來表示，而不是「區間」。

單部分索引的範圍條件定義如下

對於 BTREE 和 HASH 索引，當使用 =、<=>、IN()、IS NULL 或 IS NOT NULL 運算子時，將金鑰部分與常數值進行比較即為範圍條件。
此外，對於 BTREE 索引，當使用 >、<、>=、<=、BETWEEN、!= 或 <> 運算子，或者如果 LIKE 的引數是不以萬用字元開頭的常數字串，則將金鑰部分與常數值進行比較即為範圍條件。
對於所有索引類型，與 OR 或 AND 組合的多個範圍條件會形成範圍條件。

前述說明中的「常數值」表示下列其中之一

查詢字串中的常數
來自相同聯結的 const 或 system 資料表的欄位
不相關子查詢的結果
完全由前述類型的子表達式組成的任何表達式

以下是一些在 WHERE 子句中使用範圍條件的查詢範例

SELECT * FROM t1
  WHERE key_col > 1
  AND key_col < 10;

SELECT * FROM t1
  WHERE key_col = 1
  OR key_col IN (15,18,20);

SELECT * FROM t1
  WHERE key_col LIKE 'ab%'
  OR key_col BETWEEN 'bar' AND 'foo';

某些非常數值可能會在最佳化工具常數傳播階段轉換為常數。

MySQL 嘗試從每個可能索引的 WHERE 子句中提取範圍條件。在提取過程中，無法用於建構範圍條件的條件會被捨棄，產生重疊範圍的條件會被合併，而產生空範圍的條件則會被移除。

考量以下陳述式，其中 key1 是已索引的欄位，而 nonkey 則未被索引

SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z');

索引鍵 key1 的提取過程如下：

從原始的 WHERE 子句開始

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')

移除 nonkey = 4 和 key1 LIKE '%b'，因為它們不能用於範圍掃描。移除它們的正確方法是將它們替換為 TRUE，這樣我們在執行範圍掃描時就不會遺漏任何符合的列。將它們替換為 TRUE 後會得到：
```
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')
```
摺疊永遠為真或為假的條件
- (key1 LIKE 'abcde%' OR TRUE) 永遠為真
- (key1 < 'uux' AND key1 > 'z') 永遠為假
將這些條件替換為常數後會得到：
```
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
```
移除不必要的 TRUE 和 FALSE 常數後會得到：
```
(key1 < 'abc') OR (key1 < 'bar')
```
將重疊的間隔合併為一個，得到最終用於範圍掃描的條件：
```
(key1 < 'bar')
```

一般而言（如前面的例子所示），用於範圍掃描的條件比 WHERE 子句的限制較少。MySQL 會執行額外的檢查，以過濾掉符合範圍條件但不符合完整 WHERE 子句的列。

範圍條件提取演算法可以處理任意深度的巢狀 AND/OR 結構，並且其輸出不取決於條件在 WHERE 子句中出現的順序。

MySQL 不支援合併空間索引的 range 存取方法的多個範圍。為了繞過此限制，您可以使用具有相同 SELECT 陳述式的 UNION，但將每個空間述詞放入不同的 SELECT 中。

多部分索引的範圍存取方法

多部分索引上的範圍條件是單部分索引範圍條件的延伸。多部分索引上的範圍條件將索引列限制在一個或多個索引鍵值組間隔內。索引鍵值組間隔是使用索引的排序在索引鍵值組的集合上定義的。

例如，考量定義為 key1(key_part1, key_part2, key_part3) 的多部分索引，以及以下依索引順序排列的索引鍵值組集合

key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'

條件 key_part1 = 1 定義此間隔

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

該間隔涵蓋先前資料集中的第 4、5 和 6 個索引鍵值組，並且可由範圍存取方法使用。

相反地，條件 key_part3 = 'abc' 不定義單一間隔，因此無法由範圍存取方法使用。

以下描述更詳細地說明範圍條件如何用於多部分索引。

對於 HASH 索引，可以使用每個包含相同值的間隔。這表示只有在以下形式的條件下才能產生間隔
```
    key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;
```
在這裡，const1、const2、… 是常數，cmp 是 =、<=> 或 IS NULL 比較運算子之一，並且這些條件涵蓋所有索引部分。（也就是說，有 N 個條件，每個 N 部分索引的每個部分一個條件。）例如，以下是三部分 HASH 索引的範圍條件
```
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
```
有關何謂常數的定義，請參閱單部分索引的範圍存取方法。
對於 BTREE 索引，如果每個條件都使用 =、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN 或 LIKE 'pattern'（其中 'pattern' 不以萬用字元開頭）將索引鍵部分與常數值進行比較，則可以將間隔用於與 AND 合併的條件。只要能夠確定包含所有符合條件之列的單一索引鍵值組（如果使用 <> 或 !=，則為兩個間隔），就可以使用間隔。
只要比較運算子是 =、<=> 或 IS NULL，最佳化工具就會嘗試使用其他索引鍵部分來判斷間隔。如果運算子是 >、<、>=、<=、!=、<>、BETWEEN 或 LIKE，最佳化工具會使用它，但不再考慮其他索引鍵部分。對於以下運算式，最佳化工具會使用來自第一個比較的 =。它也會使用來自第二個比較的 >=，但不再考慮其他索引鍵部分，並且不會使用第三個比較來建構間隔
```
key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
```
單一間隔為
```
('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)
```
建立的間隔可能包含比初始條件更多的列。例如，前面的間隔包含值 ('foo', 11, 0)，這不滿足原始條件。
如果將涵蓋間隔內所含列集合的條件與 OR 合併，則會形成一個涵蓋其間隔聯集內所含列集合的條件。如果將條件與 AND 合併，則會形成一個涵蓋其間隔交集內所含列集合的條件。例如，針對雙部分索引的這個條件
```
(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
```
間隔為
```
(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)
```
在此範例中，第一行的間隔使用一個索引鍵部分做為左邊界，並使用兩個索引鍵部分做為右邊界。第二行的間隔只使用一個索引鍵部分。EXPLAIN 輸出的 key_len 欄位會指出所使用索引鍵前置碼的最大長度。
在某些情況下，key_len 可能會指出使用了索引鍵部分，但這可能不是您所期望的。假設 key_part1 和 key_part2 可以是 NULL。然後，key_len 欄位會顯示以下條件的兩個索引鍵部分長度
```
key_part1 >= 1 AND key_part2 < 2
```
但事實上，條件會轉換成這樣
```
key_part1 >= 1 AND key_part2 IS NOT NULL
```

如需有關如何最佳化來合併或消除單部分索引範圍條件的間隔的說明，請參閱單部分索引的範圍存取方法。對於多部分索引上的範圍條件，也會執行類似的步驟。

多值比較的等值範圍最佳化

考量以下運算式，其中 col_name 是已索引的欄位

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

如果 col_name 等於數個值中的任何一個，則每個運算式都為真。這些比較是等值範圍比較（其中「範圍」是單一值）。最佳化工具會依以下方式估計等值範圍比較之符合條件的列的讀取成本

如果 col_name 上有唯一索引，則每個範圍的列估計值為 1，因為最多只有一列可以具有給定的值。
否則，col_name 上的任何索引都是非唯一的，並且最佳化工具可以使用深入索引或索引統計資料來估計每個範圍的列計數。

透過深入索引，最佳化工具會在範圍的每一端進行深入搜尋，並使用範圍內的列數作為估計值。例如，運算式 col_name IN (10, 20, 30) 有三個等值範圍，並且最佳化工具會對每個範圍進行兩次深入搜尋以產生列估計值。每對深入搜尋都會產生具有給定值的列數估計值。

深入索引提供精確的列估計值，但是隨著運算式中比較值的數量增加，最佳化工具會花更長的時間來產生列估計值。使用索引統計資料的準確性不如深入索引，但允許對大型值清單進行更快的列估計。

您可以使用 eq_range_index_dive_limit 系統變數來設定最佳化工具在不同的資料列估算策略之間切換的數值。若要允許對最多 N 個等值範圍的比較使用索引潛入 (index dives)，請將 eq_range_index_dive_limit 設定為 N + 1。若要停用統計資料的使用，並始終使用索引潛入，無論 N 為何，請將 eq_range_index_dive_limit 設定為 0。

若要更新資料表索引統計資料以獲得最佳估算值，請使用 ANALYZE TABLE。

在 MySQL 8.4 之前，除了使用 eq_range_index_dive_limit 系統變數外，沒有其他方法可以略過使用索引潛入來估算索引的實用性。在 MySQL 8.4 中，如果查詢滿足所有這些條件，則可以跳過索引潛入：

查詢是針對單一資料表，而不是多個資料表的聯結。
存在單一索引 FORCE INDEX 索引提示。其概念是，如果強制使用索引，則從執行索引潛入的額外開銷中沒有任何獲益。
索引是非唯一索引，且不是 FULLTEXT 索引。
不存在子查詢。
不存在 DISTINCT、GROUP BY 或 ORDER BY 子句。

對於 EXPLAIN FOR CONNECTION，如果跳過索引潛入，輸出會如下變更：

對於傳統輸出，rows 和 filtered 的值為 NULL。
對於 JSON 輸出，不會出現 rows_examined_per_scan 和 rows_produced_per_join，skip_index_dive_due_to_force 為 true，且成本計算不準確。

如果沒有 FOR CONNECTION，當跳過索引潛入時，EXPLAIN 輸出不會變更。

在執行跳過索引潛入的查詢後，資訊綱要 OPTIMIZER_TRACE 資料表中對應的資料列會包含 index_dives_for_range_access 的值 skipped_due_to_force_index。

跳過掃描範圍存取方法

請考慮以下情境：

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

為了執行此查詢，MySQL 可以選擇索引掃描來擷取所有資料列 (索引包括所有要選取的資料行)，然後套用 WHERE 子句中的 f2 > 40 條件來產生最終的結果集。

範圍掃描比完整索引掃描更有效率，但在這種情況下無法使用，因為在第一個索引資料行 f1 上沒有任何條件。最佳化工具可以使用一種稱為「跳過掃描」的方法來執行多個範圍掃描，每個 f1 的值執行一個掃描，此方法類似於「寬鬆索引掃描」(Loose Index Scan) (請參閱第 10.2.1.17 節，「GROUP BY 最佳化」)

在第一個索引部分 f1 (索引前置詞) 的不同值之間跳過。
針對剩餘索引部分上 f2 > 40 條件的每個不同前置詞值執行子範圍掃描。

對於先前顯示的資料集，此演算法的運作方式如下：

取得第一個金鑰部分 (f1 = 1) 的第一個不同值。
根據第一個和第二個金鑰部分 (f1 = 1 AND f2 > 40) 建構範圍。
執行範圍掃描。
取得第一個金鑰部分 (f1 = 2) 的下一個不同值。
根據第一個和第二個金鑰部分 (f1 = 2 AND f2 > 40) 建構範圍。
執行範圍掃描。

使用此策略可減少存取的資料列數，因為 MySQL 會跳過不符合每個建構範圍條件的資料列。此跳過掃描存取方法適用於以下情況：

資料表 T 至少有一個複合索引，其金鑰部分的形式為 ([A_1, ..., A_k,] B_1, ..., B_m, C [, D_1, ..., D_n])。金鑰部分 A 和 D 可以是空的，但 B 和 C 必須是非空的。
查詢僅參考一個資料表。
查詢未使用 GROUP BY 或 DISTINCT。
查詢僅參考索引中的資料行。
A_1, ..., A_k 上的述詞必須是等式述詞，而且它們必須是常數。這包括 IN() 運算子。
查詢必須是合取查詢；也就是說，AND 的 OR 條件：(cond1(key_part1) OR cond2(key_part1)) AND (cond1(key_part2) OR ...) AND ...
在 C 上必須有範圍條件。
允許 D 資料行上的條件。D 上的條件必須與 C 上的範圍條件結合使用。

在 EXPLAIN 輸出中，跳過掃描的使用方式如下：

Extra 資料行中的 Using index for skip scan 表示使用寬鬆索引跳過掃描存取方法。
如果索引可以用於跳過掃描，則索引應在 possible_keys 資料行中可見。

在最佳化工具追蹤輸出中，跳過掃描的使用方式由這種形式的 "skip scan" 元素指示：

"skip_scan_range": {
  "type": "skip_scan",
  "index": index_used_for_skip_scan,
  "key_parts_used_for_access": [key_parts_used_for_access],
  "range": [range]
}

您也可能會看到 "best_skip_scan_summary" 元素。如果選擇跳過掃描作為最佳範圍存取變體，則會寫入 "chosen_range_access_summary"。如果選擇跳過掃描作為整體最佳存取方法，則會出現 "best_access_path" 元素。

跳過掃描的使用方式受 optimizer_switch 系統變數的 skip_scan 旗標的值限制。請參閱第 10.9.2 節，「可切換的最佳化」。預設情況下，此旗標為 on。若要停用它，請將 skip_scan 設定為 off。

除了使用 optimizer_switch 系統變數來控制整個工作階段的最佳化工具跳過掃描使用之外，MySQL 還支援最佳化工具提示，以根據每個陳述式來影響最佳化工具。請參閱第 10.9.3 節，「最佳化工具提示」。

資料列建構子表達式的範圍最佳化

最佳化工具能夠將範圍掃描存取方法套用至此形式的查詢：

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

先前，若要使用範圍掃描，則必須將查詢寫成：

SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );

若要讓最佳化工具使用範圍掃描，查詢必須滿足這些條件：

只使用 IN() 述詞，而不使用 NOT IN()。
在 IN() 述詞的左側，資料列建構子只包含資料行參考。
在 IN() 述詞的右側，資料列建構子只包含執行階段常數，這些常數是文字或在執行期間繫結至常數的本機資料行參考。
在 IN() 述詞的右側，有多個資料列建構子。

如需有關最佳化工具和資料列建構子的詳細資訊，請參閱第 10.2.1.22 節，「資料列建構子表達式最佳化」

限制範圍最佳化的記憶體使用

若要控制範圍最佳化工具可用的記憶體，請使用 range_optimizer_max_mem_size 系統變數

值 0 表示「沒有限制。」
當值大於 0 時，最佳化工具會在考慮範圍存取方法時追蹤所耗用的記憶體。如果即將超過指定的限制，則會捨棄範圍存取方法，並改為考慮其他方法，包括完整資料表掃描。這可能較不理想。如果發生這種情況，則會出現以下警告 (其中 N 是目前的 range_optimizer_max_mem_size 值)：
```
Warning    3170    Memory capacity of N bytes for
                   'range_optimizer_max_mem_size' exceeded. Range
                   optimization was not done for this query.
```
對於 UPDATE 和 DELETE 陳述式，如果最佳化工具回復為完整資料表掃描，且啟用了 sql_safe_updates 系統變數，則會發生錯誤，而不是警告，因為實際上，沒有使用任何金鑰來判斷要修改哪些資料列。如需詳細資訊，請參閱使用安全更新模式 (--safe-updates)。

對於超過可用範圍最佳化記憶體且最佳化工具回復為較不理想計畫的個別查詢，增加 range_optimizer_max_mem_size 值可能會改善效能。

若要估計處理範圍表達式所需的記憶體量，請使用這些準則：

對於以下簡單的查詢 (其中有一個用於範圍存取方法的候選金鑰)，每個與 OR 結合的述詞約使用 230 個位元組：
```
SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
```
類似地，對於以下查詢，每個與 AND 結合的述詞約使用 125 個位元組：
```
SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;
```
對於具有 IN() 述詞的查詢：
```
SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
```
IN() 清單中的每個文字值都算作與 OR 結合的述詞。如果有兩個 IN() 清單，則與 OR 結合的述詞數量是每個清單中文字值的乘積。因此，在上述情況下，與 OR 結合的述詞數量為 M × N。