作者：劉鄧忠
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/
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/540913.html
標籤：其他