作者 | 京東云開發者-劉鄧忠

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 總結

回表操作：當所要查找的字段不在非主鍵索引樹上時，需要通過葉子節點的主鍵值去主鍵索引上獲取對應的行數據，這個過程稱為回表操作。

索引下推：索引下推主要是減少了不必要的回表操作。對于查找出來的數據，先過濾掉不符合條件的，其余的再去主鍵索引樹上查找。

審核編輯：湯梓紅