范圍優(yōu)化
??范圍訪問方法使用單個索引檢索包含在一個或多個索引值區(qū)間內(nèi)的表行子集。它可以用于Single-Part索引或Multiple-Part索引。以下部分描述優(yōu)化器使用范圍訪問的條件：
- Single-Part索引的范圍訪問方法
- Multiple-Part索引的范圍訪問方法
- 多值比較的等式范圍優(yōu)化
- 行構(gòu)造函數(shù)表達(dá)式的范圍優(yōu)化
- 用于范圍優(yōu)化的有限內(nèi)存使用
??我將通過五篇文章來分別介紹這幾種范圍優(yōu)化的方法。本文將以介紹Single-Part索引的范圍訪問方法為主。
Single-Part索引的范圍訪問方法
??對于單部分索引，索引值區(qū)間可以方便地用WHERE子句中的對應(yīng)條件表示，表示為范圍條件，而不是“區(qū)間”。
??Single-Part索引的范圍條件定義如下:
- 對于BTREE索引和HASH索引，在()中使用=、<=>時的條件為空，或?yàn)榉强詹僮鞣臅r候鍵部分與常量值的比較都是一個范圍條件。
- 此外，對于BTREE索引，當(dāng)使用>、<、>=、<=、BETWEEN、!=、<>操作符或LIKE的參數(shù)內(nèi)容是一個常量字符串，且不以通配符開頭時，關(guān)鍵部分與常量值的比較是一個范圍條件。
- 對于所有索引類型，由多個范圍條件通過OR或AND組成一個范圍條件。
??在前面的描述中，“常量值”是指以下內(nèi)容之一：
- 一個常量形式的查詢字符串。
- 來自同一連接的常量列或系統(tǒng)表的列
- 不相關(guān)子查詢的結(jié)果
- 完全由前面類型的子表達(dá)式組成的任何表達(dá)式
??以下是在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';
??在優(yōu)化器常量的傳遞階段，可以將一些非常量值轉(zhuǎn)換為常量。
??MySQL試圖為每個可能的索引從WHERE子句中提取范圍條件。在提取過程中，刪除無法構(gòu)造范圍條件的條件，合并產(chǎn)生重疊范圍的條件，刪除產(chǎn)生空范圍的條件。
??考慮下面的語句，其中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');
??關(guān)鍵值key1的提取過程如下:
- 1、以原有WHERE語句開頭：
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')
- 2、刪除nonkey = 4和key1 LIKE '%b'，因?yàn)樗鼈儾荒苡糜诜秶鷴呙?。刪除它們的正確方法是用TRUE替換它們，這樣在進(jìn)行范圍掃描時就不會遺漏任何匹配的行。用TRUE代替以后得出以下SQL:
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')
- 3、 去除恒定真或恒定假的條件:
- (key1 LIKE 'abcde%' OR TRUE)為恒真的條件
- (key1 < 'uux' AND key1 > 'z')為恒假的條件
用常數(shù)替換這些條件得到:
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
去掉不必要的真、假常數(shù)得到:
(key1 < 'abc') OR (key1 < 'bar')
- 4、將重疊區(qū)間合并為一個區(qū)間，得到用于范圍掃描的最終條件:
(key1 < 'bar')
??一般來說(如上例所示)，用于范圍掃描的條件和WHERE子句擁有更少的限制。MySQL執(zhí)行額外的檢查，過濾出滿足范圍條件但不滿足完整WHERE子句的行。
??范圍條件提取算法可以處理任意深度的嵌套AND/OR構(gòu)造，其輸出不依賴于WHERE子句中條件出現(xiàn)的順序。
??MySQL不支持為空間索引的范圍訪問方法合并多個范圍。為了解決這個限制，您可以使用UNOIN來關(guān)聯(lián)具有相同的SELECT語句，來代替將每個空間謂詞放在不同的SELECT中。
