索引下推，這個點你肯定不知道！
索引下推(Index Condition Pushdown) ICP 是Mysql5.6之后新增的功能，主要的核心點就在于把數(shù)據(jù)篩選的過程放在了存儲引擎層去處理，而不是像之前一樣放到Server層去做過濾。
雖然這是一個比較簡單的概念，但是可能很多不細(xì)心的同學(xué)對于索引下推會存在一個小小的誤區(qū)，至于是什么，請看下文。
什么是索引下推
首先，我們創(chuàng)建一張user表，同時建立age_name的聯(lián)合索引，同時插入3條測試數(shù)據(jù)。
然后，我們執(zhí)行查詢explain SELECT * from user where age >10 and name = 'a'，如下圖所示，就會看見Extra中顯示了Using index condition，你可能就知道了，這表示出現(xiàn)了索引下推了。
沒錯，針對這個查詢場景就是索引下推，那到底什么是索引下推呢?
按照我們上述的場景，實際上就存在兩個索引樹，一個是主鍵索引，存儲了具體的數(shù)據(jù)的信息，另外則是age_name的聯(lián)合索引，保存了主鍵的ID。
在沒有ICP索引下推的時候，這個查詢的流程應(yīng)該是這樣(略過無關(guān)的細(xì)節(jié))：
- Mysql Server層調(diào)用API查詢存儲引擎數(shù)據(jù)
- 存儲引擎根據(jù)聯(lián)合索引首先通過條件找到所有age>10的數(shù)據(jù)
- 找到的每一條數(shù)據(jù)都根據(jù)主鍵索引進(jìn)行回表查詢，直到找到不符合條件的結(jié)果
- 返回數(shù)據(jù)給Server層，Server根據(jù)條件對結(jié)果進(jìn)行過濾，流程結(jié)束
而有了ICP之后的流程則是這樣：
- Mysql Server層調(diào)用API查詢存儲引擎數(shù)據(jù)
- 存儲引擎根據(jù)聯(lián)合索引首先通過條件找到所有age>10的數(shù)據(jù)，根據(jù)聯(lián)合索引中已經(jīng)存在的name數(shù)據(jù)進(jìn)行過濾，找到符合條件的數(shù)據(jù)
- 根據(jù)找到符合條件的數(shù)據(jù)，回表查詢
- 返回數(shù)據(jù)給Server層，流程結(jié)束
對比這兩個流程就會很明顯的發(fā)現(xiàn)，使用ICP之后我們就是簡單的通過聯(lián)合索引中本來就有的數(shù)據(jù)直接過濾了，不需要再查到一堆無用的數(shù)據(jù)去Server層進(jìn)行過濾，這樣的話減少了回表的次數(shù)和返回的數(shù)據(jù)，IO次數(shù)減少了，對性能有很好的提升。
按照官方文檔所說，ICP其實也存在一定的使用限制場景，只說關(guān)鍵的，亂七八糟的不說。
- 首先，ICP適用于range、ref、eq_ref和ref_or_null的場景下
- InnoDB和MyISAM都支持ICP，Mysql partition分表的話也可以使用
- 對于InndoDB而言，ICP只支持二級索引，因為主鍵索引它用不上不是嗎?
- 子查詢不支持
現(xiàn)在我們基本都使用的5.6以上的版本了，默認(rèn)就是開啟ICP的，想關(guān)閉的話可以通過命令SET optimizer_switch = 'index_condition_pushdown=off';。
一個小小的誤區(qū)
一般來說，正常情況下Mysql一次查詢都只能走一個索引，我們來修改上述的表結(jié)構(gòu)，把聯(lián)合索引改為兩個單獨的索引，數(shù)據(jù)保持不變
然后我們執(zhí)行查詢explain SELECT * from user where age >10 and name like 'a%'，結(jié)果如下圖。
你會發(fā)現(xiàn)，我靠，怎么還有索引下推?這不科學(xué)對不對，好像無法解釋嘛，難道這一次索引下推還能先查出age再下推到name索引嗎，這完全不合理啊。
其實不然，真實的情況是，Using index condition并不代表一定是使用了索引下推，只是代表可以使用，但是不一定用了。。。
這個就有點坑爹，可能會對我們判斷的時候造成誤解啊。
如果你去網(wǎng)上搜很多人舉例子這樣建索引，然后告訴你這就是索引下推的時候，你可以盡情的噴他了，我們說索引下推一定是在聯(lián)合索引的情況下，根據(jù)聯(lián)合索引本身就有的數(shù)據(jù)直接做一次過濾，而不用再進(jìn)行多次無用的回表再到Server層進(jìn)行過濾，這一點你要很明確才行。
好了，今天的話題就到這里結(jié)束，我是艾小仙，我們下期見。
(本來我想多畫兩張圖的，不過好像覺得這個概念實在太簡單了，畫的花里胡哨的反而沒有意義，就像你說覆蓋索引、回表還畫好幾張圖給你解釋嗎，沒有必要對不對，肯定不是因為我懶。。。)
