深入理解InnoDB（8）

1. 訪問方法

MySQL把執行查詢語句的方式稱之為訪問方法或者訪問類型。
而訪問方法大致分為兩類

全表掃描
索引
而進行細分的話可以分為以下幾類
（為了方便說明，先建一個表）

CREATE TABLE single_table (id INT NOT NULL AUTO_INCREMENT,key1 VARCHAR(100),key2 INT,key3 VARCHAR(100),key_part1 VARCHAR(100),key_part2 VARCHAR(100),key_part3 VARCHAR(100),common_field VARCHAR(100),PRIMARY KEY (id),KEY idx_key1 (key1),UNIQUE KEY idx_key2 (key2),KEY idx_key3 (key3),KEY idx_key_part(key_part1, key_part2, key_part3)
) Engine=InnoDB CHARSET=utf8;

1.1 const

SELECT * FROM single_table WHERE id = 1438;SELECT * FROM single_table WHERE key2 = 3841;

例如上面兩個sql語句，條件都是主鍵或者唯一二級索引列與常量進行比較。

第一個sql語句的執行，直接就是在主鍵的聚簇索引中，根據主鍵值進行匹配
第二個sql語句，先要在對應的唯一二級索引中，根據查詢的索引列進行一次匹配，查到主鍵id后，再回表查出所有的數據
因為主鍵和唯一二級索引，他們的索引列都是唯一的，因此查找的速度是非常快的，所以這種訪問方法叫const，意思就是常數級別的查詢速度.（注意：當索引列的查詢條件為空時，訪問方法并不能算const，因為可能有多條記錄為空）

1.2 ref

而ref的訪問方式類似于唯一二級索引的訪問方式，都是先在二級索引上進行匹配，找到主鍵id后，再回表查詢。但是，因為普通二級索引列的值并不是唯一的，因此可能查詢到的是多條記錄，再進行多次回表。

SELECT * FROM single_table WHERE key1 = 'abc';

例如上面的sql語句，因為key1是普通二級索引，可能存在多個key1='abc’的記錄，因此在查詢時，需要對多個記錄進行回表

1.3 ref_or_null

這個與ref類似，只是多了對于空值的匹配

SELECT * FROM single_table WHERE key1 = 'abc' OR key1 IS NULL;

例如上面的sql語句，是在key1這個二級索引上，查詢key1 = 'abc’和key1為空的主鍵id，然后再進行回表

1.4 range

而range訪問方法，意思就是根據范圍進行查詢，前面的查詢都是匹配單個常數值的，而range是查詢一個范圍的。

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79);

例如上面的sql語句，只是在之前查詢key2 IN (1438, 6328)的基礎上，將滿足79>=key2>=38的主鍵id也找出來，同樣進行回表

1.5 index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = 'abc';

key_part2 = 'abc’這個條件并不滿足最左匹配，但是key_part1, key_part2, key_part3 屬于一個聯合索引idx_key_part，因此這個查詢是在idx_key_part索引的葉子節點中，一個一個匹配key_part2 = ‘abc’，直接返回索引列即可，并不需要回表。

index的意思就是直接在索引中匹配查詢。

1.6 all

而all就是全表掃描

2.注意事項

2.1 一般情況下只能利用單個二級索引執行查詢

SELECT * FROM single_table WHERE key1 = 'abc' AND key2 > 1000;

查詢優化器會根據single_table表的統計數據，判斷key1 = ‘abc’ 和 key2 > 1000兩個條件，哪個索引查詢的條數少，就選擇到哪個索引的B+樹上查。

例如在上面的例子中，優化器選擇的是key1 = ‘abc’，那么查詢到滿足key1 = ‘abc’ 的主鍵后，就會回表查詢，并且以 key2 > 1000為條件進行過濾

2.2 明確range訪問方法使用的范圍區間

2.2.1 所有搜索條件都可以使用某個索引的情況

例如：

SELECT * FROM single_table WHERE key2 > 100 AND key2 > 200;

因為條件都是來自同一個索引的范圍查詢，因此只要將對應的范圍進行集合的運算即可

2.2.2 有的搜索條件無法使用索引的情況

SELECT * FROM single_table WHERE key2 > 100 AND common_field = 'abc';

因為common_field = 'abc’無法使用到索引，因此范圍搜索只需要關注key2 > 100這個范圍即可，回表的時候再根據common_field = 'abc’過濾一次，就可以保證選出來的記錄滿足 key2 > 100 AND common_field = ‘abc’。

SELECT * FROM single_table WHERE key2 > 100 OR common_field = 'abc';

但是，當條件換成or以后，就無法使用key2的索引了，因為查詢出了key2 > 100的記錄以后，沒有辦法也在key2的索引中查出common_field = 'abc’的記錄

2.2.3 復雜搜索條件下找出范圍匹配的區間

SELECT * FROM single_table WHERE (key1 > 'xyz' AND key2 = 748 ) OR(key1 < 'abc' AND key1 > 'lmn') OR(key1 LIKE '%suf' AND key1 > 'zzz' AND (key2 < 8000 OR common_field = 'abc')) ;

主要使用到key1和key2 兩個索引，因此對他們進行分別的查找的區間

對于key1

先將不屬于key1的條件替換為true

(key1 > 'xyz' AND TRUE ) OR
(key1 < 'abc' AND key1 > 'lmn') OR
(TRUE AND key1 > 'zzz' AND (TRUE OR TRUE))

替換掉永遠為TRUE或FALSE的條件
因為符合key1 < ‘abc’ AND key1 > 'lmn’永遠為FALSE，所以上邊的搜索條件可以被寫成這樣：

(key1 > 'xyz') OR (key1 > 'zzz')

合并范圍

key1 > xyz

對于key2

先將不屬于key1的條件替換為true

(TRUE AND key2 = 748 ) OR
(TRUE AND TRUE) OR
(TRUE AND TRUE AND (key2 < 8000 OR TRUE))

再次化簡，可以推出最后條件是true，也就是說需要搜索全部的key2索引

2.3 索引合并

之前第一點注意事項說過：一般情況下只能利用單個二級索引執行查詢，而在特殊情況下也可能在一個查詢中使用到多個二級索引。因此把這種使用到多個索引來完成一次查詢的執行方法稱之為：index merge，具體的索引合并算法有下邊三種。

2.3.1 Intersection合并

Intersection（交集），意味著對多個二級索引的結果找交集

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

執行過程

在key1這個二級索引上找key1 = ‘a’
在key3這個二級索引中找key3 = ‘b’
因為二級索引查出來的記錄格式是索引列加主鍵，因此直接對他們查出來的主鍵作一個交集再回表即可

為什么不直接查到二級索引key1的記錄，然后在回表的過程中，過濾掉不滿足key3的條件呢？

因為如果在key1上面查出來的記錄條數很多的話，那么就要進行很多次的回表，而這種回表屬于隨機I/O，非常耗時，而選擇兩次的二級索引查找，屬于順序I/O，有更好的性能。

使用Intersection的情況：

二級索引只能是等值匹配

SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
SELECT * FROM single_table WHERE key1 > 'a' AND key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
SELECT * FROM single_table WHERE key1 = 'a' AND key_part1 = 'a';

第一條sql語句可以正常使用
第二條使用了范圍查詢
第三條沒有使用聯合索引的所有列

主鍵列可以是范圍匹配

SELECT * FROM single_table WHERE id > 100 AND key1 = 'a';

因為二級索引下，索引列是已經排好序的了，而如果出現了索引列全部相同的記錄，那么將按照主鍵值進行排序，而使用了排好序的主鍵來求交集，就非常簡單了，因為對于兩個已經排好序的主鍵序列，求他們交集的時間復雜度是o（n）。

這也回答了第一種情況，為什么只能是等值匹配，因為只有等值匹配的情況下，索引列記錄相同的幾條記錄才能按照主鍵的順序排好。

上面的兩種情況只是使用Intersection的必要條件。優化器只有在單獨根據搜索條件從某個二級索引中獲取的記錄數太多，導致回表開銷太大，而通過Intersection索引合并后需要回表的記錄數大大減少時才會使用Intersection索引合并。

2.3.2 Union合并

Union是并集的意思，適用于使用不同索引的搜索條件之間使用OR連接起來的情況。
使用Union的情況：

二級索引只能是等值匹配(與Intersection相同)
主鍵列可以是范圍匹配(與Intersection相同)
使用Intersection索引合并的搜索條件
例如：

SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c' OR (key1 = 'a' AND key3 = 'b');

1.先按key1 = ‘a’ AND key3 = ‘b’，使用 Intersection得到一個交集

2.再使用 key_part1 = ‘a’ AND key_part2 = ‘b’ AND key_part3 = ‘c’，得到一個集合

3.再對上面求到的兩個集合求并集。然后進行回表

優化器只有在單獨根據搜索條件從某個二級索引中獲取的記錄數比較少，通過Union索引合并后進行訪問的代價比全表掃描更小時才會使用Union索引合并。

2.3.3 Sort-Union合并

Union的條件太苛刻了，只有等值匹配才能使用到。

SELECT * FROM single_table WHERE key1 < 'a' OR key3 > 'z'

而對于這樣的范圍查詢就無法使用了，而對于上面的sql就可能執行Sort-Union索引合并。

1.按照key1 < 'a’查出所有記錄，并且排好序
2.按照key3 > 'z’查出所有記錄，并且排好序
3.因為兩個集合的主鍵都是排好序的了，因此直接按Union那樣做就行了

為什么沒有Sort-Intersection？
因為Union是在單獨根據搜索條件從某個二級索引中獲取的記錄數比較少的情況下使用的，而這種情況下記錄數少，排序的性能損耗小。而Intersection的情況恰恰相反，它是針對查詢出的記錄數多，如果排序對于性能的損耗就很大了

2.3.4 聯合索引注意事項

聯合索引替代Intersection索引合并

SELECT * FROM single_table WHERE key1 = 'a' AND key3 = 'b';

像這樣的sql語句，需要使用到兩個單獨的索引，倒不如直接做成聯合索引，既可以少一顆b+樹，還不能做集合運算。

ALTER TABLE single_table drop index idx_key1, idx_key3, add index idx_key1_key3(key1, key3);