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