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

版本 9.0

8.4 目前
8.0
5.7

8.0 日文

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

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 存取方法的 multiple ranges。若要解決此限制，您可以使用 UNION，其中包含相同的 SELECT 陳述式，但將每個空間謂詞放在不同的 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 索引，間隔可以用於與 AND 合併的條件，其中每個條件使用 =、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN 或 LIKE 'pattern' （其中 'pattern' 不以萬用字元開頭）來比較索引鍵部分與常數值。只要可以判斷出包含所有符合條件的資料列的單一索引鍵元組（如果使用 <> 或 != 則為兩個間隔），就可以使用間隔。
只要比較運算子為 =、<=> 或 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 個相等範圍的比較使用索引深入查詢，請將 eq_range_index_dive_limit 設定為 N + 1。若要停用統計資料的使用，並始終使用索引深入查詢，而無論 N 為何，請將 eq_range_index_dive_limit 設定為 0。

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

在 MySQL 9.0 之前的版本，除了使用 eq_range_index_dive_limit 系統變數之外，沒有其他方法可以略過索引潛探 (index dives) 來估計索引的有效性。在 MySQL 9.0 中，對於符合以下所有條件的查詢，可以跳過索引潛探：

查詢針對單一資料表，而非多個資料表的聯結。
存在單一索引的 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 的值執行一次，使用稱為「跳躍掃描」的方法，該方法類似於寬鬆索引掃描（請參閱第 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" 元素。

跳躍掃描的使用受 skip_scan 旗標的值影響，該旗標屬於 optimizer_switch 系統變數。請參閱第 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。

上一個首頁上一層下一個