作者：劉鄧忠
Mysql 是大家最常用的資料庫，下面為大家帶來 mysql 索引下推知識點的分享，以便鞏固 mysql 基礎知識，如有錯誤，還請各位大佬們指正。
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 總結
回表操作：當所要查詢的欄位不在非主鍵索引樹上時，需要透過葉子節點的主鍵值去主鍵索引上獲取對應的行資料，這個過程稱為回表操作。
索引下推：索引下推主要是減少了不必要的回表操作。對於查詢出來的資料，先過濾掉不符合條件的，其餘的再去主鍵索引樹上查詢。