MySQL索引下推知識(shí)分享

作者 | 京東云開(kāi)發(fā)者-劉鄧忠

Mysql 是大家最常用的數(shù)據(jù)庫(kù)，下面為大家?guī)?lái) mysql 索引下推知識(shí)點(diǎn)的分享，以便鞏固 mysql 基礎(chǔ)知識(shí)，如有錯(cuò)誤，還請(qǐng)各位大佬們指正。

1 什么是索引下推

索引下推 (Index Condition Pushdown，索引條件下推，簡(jiǎn)稱 ICP)，是 MySQL5.6 版本的新特性，它可以在對(duì)聯(lián)合索引遍歷過(guò)程中，對(duì)索引中包含的所有字段先做判斷，過(guò)濾掉不符合條件的記錄之后再回表，能有效的減少回表次數(shù)(目前我們使用的 mysql 版本較高，一般大家可能感覺(jué)這是正常的，但是 mysql5.6 之前都不是這樣實(shí)現(xiàn)的，下面會(huì)細(xì)細(xì)道來(lái))。

1.1 適用條件

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

只支持 select。

當(dāng)需要訪問(wèn)全表時(shí)，ICP 用于 range，ref，eq_ref 和 ref_or_null 訪問(wèn)類型。

ICP 可用于 InnoDB 和 MyISAM 表，包括分區(qū)的 InnoDB 和 MyISAM 表。(5.6 版本不適用分區(qū)表查詢，5.7 版本后可以用于分區(qū)表查詢)。

對(duì)于 InnDB 引擎只適用于二級(jí)索引(也叫輔助索引)，因?yàn)?InnDB 的聚簇索引會(huì)將整行數(shù)據(jù)讀到 InnDB 的緩沖區(qū)，這樣一來(lái)索引條件下推的主要目的減少 IO 次數(shù)就失去了意義。因?yàn)閿?shù)據(jù)已經(jīng)在內(nèi)存中了，不再需要去讀取了。

在虛擬生成列上創(chuàng)建的輔助索引不支持 ICP(注：InnoDB 支持虛擬生成列的輔助索引)。

使用了子查詢的條件無(wú)法下推。

使用存儲(chǔ)過(guò)程或函數(shù)的條件無(wú)法下推(因?yàn)橐驗(yàn)榇鎯?chǔ)引擎沒(méi)有調(diào)用存儲(chǔ)過(guò)程或函數(shù)的能力)。

觸發(fā)條件無(wú)法下推。(有關(guān)觸發(fā)條件的信息，請(qǐng)參閱官方資料：Section 8.2.2.3, “Optimizing Subqueries with the EXISTS Strategy”.。)

1.2 原理介紹

首先，我們大致回顧下 mysql 的基本架構(gòu)：

MySQL 基本的架構(gòu)示例圖 MySQL 服務(wù)層主要負(fù)責(zé) SQL 語(yǔ)法解析、生成執(zhí)行計(jì)劃等，并調(diào)用存儲(chǔ)引擎層去執(zhí)行數(shù)據(jù)的存儲(chǔ)和查詢。

索引下推的下推其含義就是指將部分上層(服務(wù)層)負(fù)責(zé)的事情，交給了下層(引擎層)去處理。

在 MySql 5.6 版本之前沒(méi)有索引下推這個(gè)功能，從 5.6 版本后才加上了這個(gè)優(yōu)化項(xiàng)。我們先簡(jiǎn)單對(duì)比一下使用和未使用 ICP 兩種情況下，MySql 的查詢過(guò)程吧。

1) 未使用 ICP 的情況下：

- 存儲(chǔ)引擎讀取索引記錄;

- 根據(jù)索引中的主鍵值，定位并讀取完整的行記錄;

- 存儲(chǔ)引擎把記錄交給 Server 層去檢測(cè)該記錄是否滿足 WHERE 條件。

2) 使用 ICP 的情況下：

- 存儲(chǔ)引擎讀取索引記錄(不是完整的行記錄);

- 判斷 WHERE 條件部分能否用索引中的列來(lái)做檢查，條件不滿足，則處理下一行索引記錄;

- 條件滿足，使用索引中的主鍵去定位并讀取完整的行記錄(就是所謂的回表);

- 存儲(chǔ)引擎把記錄交給 Server 層，Server 層檢測(cè)該記錄是否滿足 WHERE 條件的其余部分。

2 具體示例

上面介紹了基本原理，下面使用示例，帶大家更直觀的進(jìn)行理解(注：以下示例基于 InnoDB 存儲(chǔ)引擎。)

首先，我們新建一張用戶表(jxc_user)，設(shè)置 id 為主鍵索引，并創(chuàng)建聯(lián)合索引(name, age)。

我們先看一下該表主鍵索引的大致結(jié)構(gòu)示例：

主鍵索引結(jié)構(gòu)示例圖 然后我們?cè)倏匆幌略摫砺?lián)合索引的大致結(jié)構(gòu)示例：

聯(lián)合索引結(jié)構(gòu)示例圖 如果現(xiàn)在有一個(gè)需求，要求檢索出表中名字第一個(gè)字是張，而且年齡等于 10 歲的所有用戶。示例 SQL 語(yǔ)句如下：

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

根據(jù)索引最左匹配原則，上面這個(gè) sql 語(yǔ)句在查索引樹(shù)的時(shí)候，只能用 “張”，查到第一個(gè)滿足條件的記錄：id 為 1。

那接下來(lái)我們具體看一下 使用與未使用 ICP 的情況。

2.1 未使用 ICP 的情況

在 MySQL 5.6 之前，存儲(chǔ)引擎根據(jù)聯(lián)合索引先找到 name like ‘張 %’ 的主鍵 id(1、4)，再逐一進(jìn)行回表掃描，去聚簇索引找到完整的行記錄，返回 server 層，server 層拿到數(shù)據(jù)后，再根據(jù)條件 age=10 對(duì)拿到的數(shù)據(jù)進(jìn)行篩選。大致的示意圖如下：

從上圖，可以看到需要回表兩次，存儲(chǔ)引擎并不會(huì)去按照 age=10 進(jìn)行過(guò)濾，相當(dāng)于聯(lián)合索引的另一個(gè)字段 age 在存儲(chǔ)引擎層沒(méi)有發(fā)揮作用，比較浪費(fèi)。

2.2 使用 ICP 的情況

而 MySQL 5.6 以后， 存儲(chǔ)引擎會(huì)根據(jù)(name，age)聯(lián)合索引，找到 name like ‘張 %’，由于聯(lián)合索引中包含 age 列，所以存儲(chǔ)引擎直接再聯(lián)合索引里按照條件 age=10 進(jìn)行過(guò)濾，然后根據(jù)過(guò)濾后的數(shù)據(jù)再依次進(jìn)行回表掃描。大致的示意圖如下：

從上圖，可以看到只是 id=1 的數(shù)據(jù)，回表了一次。 除此之外我們還可以看一下執(zhí)行計(jì)劃，看到 Extra 一列里 Using index condition，就是用到了索引下推。

3 控制參數(shù)

Mysql 索引下推功能默認(rèn)是開(kāi)啟的，可以用系統(tǒng)參數(shù) optimizer_switch 來(lái)控制是否開(kāi)啟。

查看狀態(tài)命令：

select @@optimizer_switch;

關(guān)閉命令：set optimizer_switch=”index_condition_pushdown=off”;

開(kāi)啟命令：set optimizer_switch=”index_condition_pushdown=on”;

4 總結(jié)

回表操作：當(dāng)所要查找的字段不在非主鍵索引樹(shù)上時(shí)，需要通過(guò)葉子節(jié)點(diǎn)的主鍵值去主鍵索引上獲取對(duì)應(yīng)的行數(shù)據(jù)，這個(gè)過(guò)程稱為回表操作。

索引下推：索引下推主要是減少了不必要的回表操作。對(duì)于查找出來(lái)的數(shù)據(jù)，先過(guò)濾掉不符合條件的，其余的再去主鍵索引樹(shù)上查找。

審核編輯：湯梓紅