MYSQL ICP 索引下推 為什麼他行，你不行？

• 2019 年 11 月 27 日
• 筆記

MYSQL 的ICP 估計大家也都知道，Index condition pushdown,但這個東西怎麼用，有什麼用，什麼時候用，估計能答得上來的人就不多了。

其實這篇文字寫的有點費勁，我大約一天的時間，沒有得到答案，到底什麼時候能走ICP。所以下面是我通過大約一天的測試後得到的結果。

索引條件下推(ICP)是MySQL使用索引從表中檢索行的一種優化。如果沒有ICP，存儲引擎將遍歷索引來定位基表中的行，並將它們返回給MySQL伺服器，MySQL伺服器將計算這些行的WHERE條件。啟用了ICP，如果只使用來自索引的列就可以評估WHERE條件的一部分，那麼MySQL伺服器將這部分WHERE條件下推到存儲引擎。然後，存儲引擎通過使用索引項來評估推入的索引條件。並且能用到的查詢類型 range ref eq_ref 等類型。

上面是比較官方的說法，如果用大白話來說明，一句話，減少在使用二級索引查詢中因為二級索引中不包含某些欄位，而造成的部分不再INNODB 引擎層處理的數據上行到 SERVER 層，造成的I/O消耗。

1 我們使用下面的表來做一個實驗，下面的兩張圖說明的表結構，數據量，以及查詢到底有么有走ICP，顯然是走了ICP

顯然看上去走ICP 是一件很簡單的事情，實際上我們看下邊的表

上圖明顯的符合最上面的走ICP的條件，為什麼上面的查詢沒有走ICP ，而僅僅是走了索引掃描。

為啥，人家的查詢走了ICP ，你的沒有走ICP ，

我們翻過來看官方文檔，關於在什麼時候走ICP 的條件

translation

1 首先你的查詢type 的是range ,ref,eq_ref, 要是const 抱歉走不了，也沒有必要走 (但上邊的圖上的查詢類型符合ref,為啥不走)

2 資料庫引擎要INNODB OR MYISAM， （資料庫引擎是 INNODB，為啥還不走）

3 ICP 僅僅服務於二級索引，主鍵查詢時走不了ICP的，（問題是我查詢時差的非主鍵，使用的也是二級索引，為啥還不走）

4 ICP 不支援在虛擬列上創建的二級索引 （我是實體列，為啥不走）

5 條件是子查詢的走不了 （我不是子查詢，為啥不走）

6 條件是函數，也不能走 （我不是函數，我給了具體的值，為啥還不走）

7 觸發條件的不能走 （我不是，為啥還不走）

上面的符合5.7 上列出來不能走ICP的條件，上圖中的查詢也符合走ICP的條件，為什麼不走呢。

我們稍微變化一下查詢，我們可以看到，即使查詢中沒有結果，還是走了ICP

那麼問題來了，到底怎麼才能走ICP ， 大家稍微可以注意，凡是走ICP 的時候，大部分情況下都是通過 INDEX 獲取的數據的範圍，與索引之外的其他條件相比，不能快速界定要查找的數據。（估計這樣說，我也看不明白我說什麼，畫一張圖），通過圖可以看到，一般走ICP的時候，大部分情景都是非索引的條件，比走索引更能定位要查詢最終的結果，同時走索引還是可以排除一大部分數據的情況下，否則就走全表掃描了（注意：是大部分情景）

在這樣的情況下，去走ICP ，所以在都符合官7條的那些數據的情況下，為什麼不走ICP ，大部分原因是通過非索引包含的條件並不比單純走索引定位的數據量少。

註：查看是否走ICP 的詳情，通過 SELECT NAME, COUNT_RESET FROM information_schema.INNODB_METRICS WHERE NAME LIKE 'icp%';

部分欄位含義

icp_:評估ICP的行數 icp_no_match:與推入位置條件不完全匹配的行數 icp_out_of_range:所檢查的不在有效掃描範圍內的行數 icp_match:完全匹配推入位置條件的行數

如果都為0 則說明沒走ICP ,EXPLAIN 中沒有 index condition 也是沒有走ICP。

其實本篇文字，還有一點想說的是，有的時候索引的建立應該是找更能界定查找數據範圍的欄位，如果走了ICP ，其實是不是也可以考慮，你目前的索引建立的與你當前的查詢不大匹配的可能性。