8.2.1.3 Range Optimization 範圍優化
範圍通路方法使用索引來檢索表記錄的子集,有一個或者多個索引值間隔。
它可以用于一個單部分或者多部分的索引,下面的章節給出了一個詳細的描述說明如何從WHERE 子句中提取間隙:
8.2.1.3.1 單部分索引的範圍通路方法:
對于一個單部分索引, 索引值的時間間隔可以友善的代表通過相應的條件在WHERE 子句中,
是以我們講的範圍條件,而不是時間間隔。
範圍條件的定義對于一個單部分索引如下:
1.對于BTREE和HASH indexes, 比較一個key part 和一個恒定值是範圍條件當使用=,<=>,in(),is null 或者not null操作。
- 此外, 對于BTREE 索引,比較一個key part 和一個常量是一個範圍條件當使用>,<,>=，<=,
BETWEEN,!=,<>操作符,或者like 比較符 如果蠶食LIKE 是一個常量字元串,不是以通配符開始。
多餘所有的類型的索引,多個範圍條件組合成OR 或者AND 形成一個範圍條件。
在前面的描述中的常量值指下列之一:
1.查詢字元串常量
- 常量表的列或者系統表
3.一個非關聯子查詢的結果
這裡有一些查詢的例子,使用range 條件在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’);
對于key 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’)
删除nonkey = 4 和key1 LIKE ‘%b’ 因為它們不能用于一個範圍掃描,
正确的方式是删除它們:
(key1 < ‘abc’ AND (key1 LIKE ‘abcde%’ OR TRUE)) OR
(key1 < ‘bar’ AND TRUE) OR
(key1 < ‘uux’ AND key1 > ‘z’)
折疊的條件,總是真或者假:
(key1 LIKE ‘abcde%’ OR TRUE) is always true
(key1 < ‘uux’ AND key1 > ‘z’) is always false
用常量替換這些條件,我們得到:
(key1 < ‘abc’ AND TRUE) OR (key1 < ‘bar’ AND TRUE) OR (FALSE)
在一般情況下（如前面的例子所證明),用于範圍掃描的條件比在WHERE 子句中的限制的更少。
MySQL 執行一個額外的檢查來規律滿足range 條件的記錄,但不是整個where 子句。
範圍條件提取算法依靠處理 nested AND/OR 結構,其處處不依賴條件表達式出現在WHERE 的順序。
目前, MySQL 不支援merge多個range 對于rang 通路方法,
為了解決這個限制,你可以使用一個UNION 連接配接單個SELECT 語句
8.2.1.3.2 The Range Access Method for Multiple-Part Indexes 範圍通路方法 用于部分索引（組合索引)
Multiple-Part Indexes ：組合索引
範圍條件在一個多部分的索引是一個擴充的範圍條件,
一個範圍條件在一個多部分索引限制索引記錄位于一個或者多個索引。
例如,考慮一個多部分索引定位為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)
區間在第四,第5，第6 元組在前面的資料庫,可以通過範圍通路。
相比之下, 條件 key_part3 = ‘abc’ 不定義一個機關的區間 不能用于範圍掃描
下面的描述包括範圍條件如何工作對于組合索引:
對于HASH indexes, 每個間隔包含相同的值被使用,這意味着 間隔可以産生僅在下面的形式的條件:
key_part1 cmp const1
AND key_part2 cmp const2
AND …
AND key_partN cmp constN;
在這裡,const1，const2 …是常數,cmp是=,<=>或者is NULL 比較表達式中的一個,
條件覆寫所有的index 部分：
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = ‘foo’
對于B樹索引,一個區間可以是有用的對于送出結合(and）,其中每個條件比較:
一個索引 部分 是一個常量值使用=,<=>，IS NULL ,>,< >= != <>
BETWEEN,或者LIKE 模式(模式不是通配符開始).
優化器嘗試 使用額外的key 部分來确定間隔隻要比較運算符是=,<=>或者IS NULL。
如果操作符是>,<,>=,<=,!=,<> 優化器使用它,但是認為沒有更多的key 部分。
對于下面的表達式,優化器使用=從第一個表達式。
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 組合,
(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
The intervals are:
(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)
在這個例子中,第一行的間隔使用一個索引 對于左邊邊界,兩個索引用于後邊邊界。
第2行間隔使用一個索引,key_len列在EXPLAIN中的輸出表示索引字首使用的最大長度:
在某系情況下, key_len 可能表示一個索引被使用, 但是這個可能不是你期望的,