分析Mysql索引下推是什麼?是否有助優化?

藏色散人
發布: 2021-11-04 15:51:54
轉載
1685 人瀏覽過

Mysql效能最佳化:什麼是索引下推?

導讀

索引下推(index condition pushdown )簡稱ICP,在Mysql5.6的版本上推出,用於最佳化查詢。

在不使用ICP的情況下,在使用非主鍵索引(又叫普通索引或二級索引)進行查詢時,儲存引擎透過索引檢索到數據,然後傳回給MySQL伺服器,伺服器然後判斷數據是否符合條件。

在使用ICP的情況下,如果存在某些被索引的列的判斷條件時,MySQL伺服器將這一部分判斷條件傳遞給儲存引擎,然後由儲存引擎透過判斷索引是否符合MySQL伺服器傳遞的條件,只有當索引符合條件時才會將資料檢索出來回傳給MySQL伺服器。

索引條件下推優化可以減少儲存引擎查詢基礎表的次數,也可以減少MySQL伺服器從儲存引擎接收資料的次數。

【推薦學習:mysql影片教學

開擼

在開始之前先準備一張用戶表(user),其中主要幾個欄位有:id、name、age、address。建立聯合索引(name,age)。

假設有一個需求,要求匹配姓名第一個為陳的所有用戶,sql語句如下:

SELECT * from user where  name like '陈%'
登入後複製

根據"最佳左前綴" 的原則,這裡使用了聯合索引( name,age)進行了查詢,效能要比全表掃描肯定要高。

問題來了,如果有其他的條件呢?假設又有一個需求,要求匹配姓名第一個字為陳,年齡為20歲的用戶,此時的sql語句如下:

SELECT * from user where  name like '陈%' and age=20
登入後複製

這條sql語句該如何執行呢?下面對Mysql5.6之前版本和之後版本進行分析。

Mysql5.6之前的版本

5.6之前的版本是沒有索引下推這個最佳化的,因此執行的流程如下圖:

分析Mysql索引下推是什麼?是否有助優化?

會忽略age這個字段,直接透過name進行查詢,在(name,age)這課樹上查找到了兩個結果,id分別為2,1,然後拿著取到的id值一次次的回表查詢,因此這個過程需要回表兩次。

Mysql5.6及之後版本

5.6版本加入了索引下推這個最佳化,執行的流程如下圖:

分析Mysql索引下推是什麼?是否有助優化?

InnoDB並沒有忽略age這個字段,而是在索引內部就判斷了age是否等於20,對於不等於20的記錄直接跳過,因此在(name,age)這棵索引樹中只匹配到了一個記錄,此時拿著這個id去主鍵索引樹中回表查詢全部數據,這個過程只​​需要回表一次。

實作

當然上述的分析只是原理上的,我們可以實戰分析一下,因此陳某裝了Mysql5.6版的Mysql,解析了上述的語句,如下圖:

分析Mysql索引下推是什麼?是否有助優化?

根據explain解析結果可以看出Extra的值為Using index condition,表示已經使用了索引下推。

總結

索引下推在非主鍵索引上的最佳化,可以有效減少回表的次數,大大提升了查詢的效率。

關閉索引下推可以使用如下指令,設定檔的修改不再講述了,畢竟這麼優秀的功能幹嘛:

set optimizer_switch='index_condition_pushdown=off';
登入後複製

以上是分析Mysql索引下推是什麼?是否有助優化?的詳細內容。更多資訊請關注PHP中文網其他相關文章!

相關標籤:
來源:juejin.im
本網站聲明
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
熱門教學
更多>
最新下載
更多>
網站特效
網站源碼
網站素材
前端模板