作者：劉鄧忠 Mysql 是大家最常用的資料庫，下麵為大家帶來 mysql 索引下推知識點的分享，以便鞏固 mysql 基礎知識，如有錯誤，還請各位大佬們指正。 1 什麼是索引下推 索引下推 (Index Condition Pushdown，索引條件下推，簡稱 ICP)，是 MySQL5.6 版本 ...
作者：劉鄧忠
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 總結
回表操作：當所要查找的欄位不在非主鍵索引樹上時，需要通過葉子節點的主鍵值去主鍵索引上獲取對應的行數據，這個過程稱為回表操作。
索引下推：索引下推主要是減少了不必要的回表操作。對於查找出來的數據，先過濾掉不符合條件的，其餘的再去主鍵索引樹上查找。
5 參考文獻
- https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html
- http://mysql.taobao.org/monthly/2015/12/08/
