對于單元素索引,可以用WHERE子句中的相應條件很方便地表示索引值區間,因此我們稱為范圍條件而不是“區間”。
單元素索引范圍條件的定義如下:
· 對于BTREE和HASH索引,當使用=、<=>、IN、IS NULL或者IS NOT NULL操作符時,關鍵元素與常量值的比較關系對應一個范圍條件。
· 對于BTREE索引,當使用>、<、>=、<=、BETWEEN、!=或者<>,或者LIKE 'pattern'(其中 'pattern'不以通配符開始)操作符時,關鍵元素與常量值的比較關系對應一個范圍條件。
· 對于所有類型的索引,多個范圍條件結合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的提取過程如下:
1. 用原始WHERE子句開始:
2. (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
3. (key1 < 'bar' AND nonkey = 4) OR
4. (key1 < 'uux' AND key1 > 'z')
5. 刪除nonkey = 4和key1 LIKE '%b',因為它們不能用于范圍掃描。刪除它們的正確途徑是用TRUE替換它們,以便進行范圍掃描時不會丟失匹配的記錄。用TRUE替換它們后,可以得到:
6. (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
7. (key1 < 'bar' AND TRUE) OR
8. (key1 < 'uux' AND key1 > 'z')
9. 取消總是為true或false的條件:
· (key1 LIKE 'abcde%' OR TRUE)總是true
· (key1 < 'uux' AND key1 > 'z')總是false
用常量替換這些條件,我們得到:
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
刪除不必要的TRUE和FALSE常量,我們得到
(key1 < 'abc') OR (key1 < 'bar')
10.將重疊區間組合成一個產生用于范圍掃描的最終條件:
11. (key1 < 'bar')
總的來說(如前面的例子所述),用于范圍掃描的條件比WHERE子句限制少。MySQL再執行檢查以過濾掉滿足范圍條件但不完全滿足WHERE子句的行。
范圍條件提取算法可以處理嵌套的任意深度的AND/OR結構,并且其輸出不依賴條件在WHERE子句中出現的順序。