1 什麼是索引下推

索引下推 (Index Condition Pushdown，索引條件下推，簡稱 ICP)，是 MySQL5.6 版本的新特性，它可以在對聯合索引遍歷過程中，對索引中包含的所有欄位先做判斷，過濾掉不符合條件的記錄之後再回表，能有效的減少回表次數（目前我們使用的 mysql 版本較高，一般大家可能感覺這是正常的，但是 mysql5.6 之前都不是這樣實現的，下面會細細道來）。

1.1 適用條件

我們先來了解一下索引下推的使用條件及限制：

只支援 select。
當需要存取全表時，ICP 用於 range，ref，eq_ref 和 ref_or_null 存取型別。
ICP 可用於 InnoDB 和 MyISAM 表，包括分割區的 InnoDB 和 MyISAM 表。（5.6 版本不適用分割區表查詢，5.7 版本後可以用於分割區表查詢）。
對於 InnDB 引擎只適用於二級索引（也叫輔助索引），因為 InnDB 的聚簇索引會將整行資料讀到 InnDB 的緩衝區，這樣一來索引條件下推的主要目的減少 IO 次數就失去了意義。因為資料已經在記憶體中了，不再需要去讀取了。
在虛擬生成列上建立的輔助索引不支援 ICP（注：InnoDB 支援虛擬生成列的輔助索引）。
使用了子查詢的條件無法下推。
使用儲存過程或函數的條件無法下推（因為因為儲存引擎沒有呼叫儲存過程或函數的能力）。
觸發條件無法下推。（有關觸發條件的資訊，請參閱官方資料：Section 8.2.2.3, 「Optimizing Subqueries with the EXISTS Strategy」.。）

1.2 原理介紹

首先，我們大致回顧下 mysql 的基本架構：

MySQL 基本的架構範例圖

MySQL 服務層主要負責 SQL 語法解析、生成執行計劃等，並呼叫儲存引擎層去執行資料的儲存和查詢。
索引下推的下推其含義就是指將部分上層（服務層）負責的事情，交給了下層（引擎層）去處理。
在 MySql 5.6 版本之前沒有索引下推這個功能，從 5.6 版本後才加上了這個優化項。我們先簡單對比一下使用和未使用 ICP 兩種情況下，MySql 的查詢過程吧。

1) 未使用 ICP 的情況下：

儲存引擎讀取索引記錄；
根據索引中的主鍵值，定位並讀取完整的行記錄；
儲存引擎把記錄交給 Server 層去檢測該記錄是否滿足 WHERE 條件。

2) 使用 ICP 的情況下：

儲存引擎讀取索引記錄（不是完整的行記錄）；
判斷 WHERE 條件部分能否用索引中的列來做檢查，條件不滿足，則處理下一行索引記錄；
條件滿足，使用索引中的主鍵去定位並讀取完整的行記錄（就是所謂的回表）；
儲存引擎把記錄交給 Server 層，Server 層檢測該記錄是否滿足 WHERE 條件的其餘部分。

2 具體範例

上面介紹了基本原理，下面使用範例，帶大家更直觀的進行理解（注：以下範例基於 InnoDB 儲存引擎。）
首先，我們新建一張使用者表（jxc_user），設定 id 為主鍵索引，並建立聯合索引（name, age）。

我們先看一下該表主鍵索引的大致結構範例：

主鍵索引結構範例圖

然後我們再看一下該表聯合索引的大致結構範例：

聯合索引結構範例圖

如果現在有一個需求，要求檢索出表中名字第一個字是張，而且年齡等於 10 歲的所有使用者。範例 SQL 語句如下：

select id,name,age,tel,addr from jxc_user where name like '張%' and age=10;

根據索引最左匹配原則，上面這個 sql 語句在查索引樹的時候，只能用「張」，查到第一個滿足條件的記錄：id 為 1。
那接下來我們具體看一下使用與未使用 ICP 的情況。

2.1 未使用 ICP 的情況

在 MySQL 5.6 之前，儲存引擎根據聯合索引先找到 name like ‘張 %’ 的主鍵 id（1、4），再逐一進行回表掃描，去聚簇索引找到完整的行記錄，返回 server 層，server 層拿到資料後，再根據條件 age=10 對拿到的資料進行篩選。大致的示意圖如下：

從上圖，可以看到需要回表兩次，儲存引擎並不會去按照 age=10 進行過濾，相當於聯合索引的另一個欄位 age 在儲存引擎層沒有發揮作用，比較浪費。

2.2 使用 ICP 的情況

而 MySQL 5.6 以後，儲存引擎會根據（name，age）聯合索引，找到 name like ‘張 %’，由於聯合索引中包含 age 列，所以儲存引擎直接再聯合索引裡按照條件 age=10 進行過濾，然後根據過濾後的資料再依次進行回表掃描。大致的示意圖如下：

從上圖，可以看到只是 id=1 的資料，回表了一次。

除此之外我們還可以看一下執行計劃，看到 Extra 一列裡 Using index condition，就是用到了索引下推。

3 控制引數

Mysql 索引下推功能預設是開啟的，可以用系統引數 optimizer_switch 來控制是否開啟。
檢視狀態命令：
select @@optimizer_switch;

關閉命令：set optimizer_switch=」index_condition_pushdown=off」;
開啟命令：set optimizer_switch=」index_condition_pushdown=on」;

4 總結

回表操作：當所要查詢的欄位不在非主鍵索引樹上時，需要通過葉子節點的主鍵值去主鍵索引上獲取對應的行資料，這個過程稱為回表操作。
索引下推：索引下推主要是減少了不必要的回表操作。對於查詢出來的資料，先過濾掉不符合條件的，其餘的再去主鍵索引樹上查詢。

MySql索引下推知識分享