索引下推(index condition pushdown )簡稱ICP,在Mysql5.6的版本上推出,用于優(yōu)化查詢。
在不使用ICP的情況下,在使用非主鍵索引(又叫普通索引或者二級索引)進行查詢時,存儲引擎通過索引檢索到數(shù)據(jù),然后返回給MySQL服務器,服務器然后判斷數(shù)據(jù)是否符合條件 。
在使用ICP的情況下,如果存在某些被索引的列的判斷條件時,MySQL服務器將這一部分判斷條件傳遞給存儲引擎,然后由存儲引擎通過判斷索引是否符合MySQL服務器傳遞的條件,只有當索引符合條件時才會將數(shù)據(jù)檢索出來返回給MySQL服務器 。
索引條件下推優(yōu)化可以減少存儲引擎查詢基礎表的次數(shù),也可以減少MySQL服務器從存儲引擎接收數(shù)據(jù)的次數(shù)。
開擼
在開始之前先先準備一張用戶表(user),其中主要幾個字段有:id、name、age、address。建立聯(lián)合索引(name,age)。
假設有一個需求,要求匹配姓名第一個為陳的所有用戶,sql語句如下:
SELECT * from user where name like '陳%'
根據(jù) "最佳左前綴" 的原則,這里使用了聯(lián)合索引(name,age)進行了查詢,性能要比全表掃描肯定要高。
問題來了,如果有其他的條件呢?假設又有一個需求,要求匹配姓名第一個字為陳,年齡為20歲的用戶,此時的sql語句如下:
SELECT * from user where name like '陳%' and age=20
這條sql語句應該如何執(zhí)行呢?下面對Mysql5.6之前版本和之后版本進行分析。
Mysql5.6之前的版本
5.6之前的版本是沒有索引下推這個優(yōu)化的,因此執(zhí)行的過程如下圖:
會忽略age這個字段,直接通過name進行查詢,在(name,age)這課樹上查找到了兩個結果,id分別為2,1,然后拿著取到的id值一次次的回表查詢,因此這個過程需要回表兩次。
Mysql5.6及之后版本
5.6版本添加了索引下推這個優(yōu)化,執(zhí)行的過程如下圖:
InnoDB并沒有忽略age這個字段,而是在索引內部就判斷了age是否等于20,對于不等于20的記錄直接跳過,因此在(name,age)這棵索引樹中只匹配到了一個記錄,此時拿著這個id去主鍵索引樹中回表查詢全部數(shù)據(jù),這個過程只需要回表一次。
實踐
當然上述的分析只是原理上的,我們可以實戰(zhàn)分析一下,因此陳某裝了Mysql5.6版本的Mysql,解析了上述的語句,如下圖:
根據(jù)explain解析結果可以看出Extra的值為Using index condition,表示已經(jīng)使用了索引下推。
總結
索引下推在非主鍵索引上的優(yōu)化,可以有效減少回表的次數(shù),大大提升了查詢的效率。
關閉索引下推可以使用如下命令,配置文件的修改不再講述了,畢竟這么優(yōu)秀的功能干嘛關閉呢:
set optimizer_switch='index_condition_pushdown=off';
到此這篇關于Mysql性能優(yōu)化之索引下推的文章就介紹到這了,更多相關Mysql 索引下推內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
您可能感興趣的文章:- 一篇文章讀懂什么是MySQL索引下推(ICP)
- MySQL索引下推(ICP)的簡單理解與示例
- 五分鐘讓你快速弄懂MySQL索引下推
- 五分鐘帶你搞懂MySQL索引下推