大家好，我是君哥。

使用 MySQL 時，我們經(jīng)常會聽到“回表”、“索引下推”這樣的概念，今天就來聊一聊什么是回表，什么是索引下推。

一、回表

1.1 概念

我們看下面這個 SQL：

CREATE TABLE`test_temp` (
`id`INT(11) NOTNULLDEFAULT'0',
`a`VARCHAR(20) DEFAULTNULL,
`b`VARCHAR(10) DEFAULTNULL,
  PRIMARY KEY (`id`),
KEY(`b`)
) ENGINE=INNODBDEFAULTCHARSET=utf8

我們創(chuàng)建一個 test_temp 表，主鍵是 id，給字段 b 加了一個索引。插入 4 條數(shù)據(jù)，SQL 如下：

INSERT INTO test_temp(100, 10, 50);
INSERT INTO test_temp(200, 20, 40);
INSERT INTO test_temp(300, 30, 30);
INSERT INTO test_temp(400, 40, 10);

test_temp 表會構建 2 個索引，一個是主鍵索引，一個是字段 b 的普通索引。

一般主鍵索引被稱為聚集索引，普通索引被稱為非聚集索引。

我們執(zhí)行下面查詢 SQL：

select * from test_temp where b in(10, 20, 30 ,40);

這個 SQL 語句的查詢過程如下圖：

圖片

1.從索引 b 上查詢 10，查到主鍵 id 的值是 400，再用 400 這個 id 去主鍵索引上取出 row4；

2.從索引 b 上查詢 20，沒有查到記錄，繼續(xù)下一條；

3.從索引 b 上查詢 30，查到主鍵 id 的值是 300，再用 300 這個 id 去主鍵索引上取出 row3；

4.從索引 b 上查詢 40，查到主鍵 id 的值是 200，再用 200 這個 id 去主鍵索引上取出 row2；

5.給客戶端返回結果集。

上面 1、3、5 回到主鍵索引搜索數(shù)據(jù)的過程，就叫回表。上面查詢回表 3 次。

1.2 缺點

回表有什么問題嗎？回表次數(shù)多了，可能會嚴重影響查詢效率。

1.導致磁盤 I/O 增加：每次回表讀取數(shù)據(jù)行，這些數(shù)據(jù)分散在磁盤各個地方，導致大量的磁盤 I/O。

2.導致緩存失效：回表的數(shù)據(jù)如果不在緩存行中，就需要從磁盤加載，新的數(shù)據(jù)可能會覆蓋已有的緩存，影響其他查詢。

1.3 措施

那有什么方法可以避免回表嗎？下面兩個方法可以避免：

1.覆蓋索引

上面的查詢中，如果 SQL 改成：

select b, id from test_temp where b in(10, 20, 30 ,40);

這樣就不用回表查詢了。如果需要查詢 b、a 兩個字段，可以創(chuàng)建 b、a 的覆蓋索引，這樣就可以從 b、a 這個覆蓋索引上查詢出結果。

2.只查詢必要字段

修改查詢范圍，不用的字段不查詢。如果查詢的字段不多，可以把查詢語句改成只查聯(lián)合索引包含的字段。如果查詢頻率高，又沒有覆蓋索引，可以加一個包含查詢字段的聯(lián)合索引。

二、索引下推

首先回顧一下 MySQL 的邏輯架構：

圖片

Server 層是 MySQL 的核心服務層，這一次包括查詢解析、分析、優(yōu)化、緩存、以及所有內(nèi)置函數(shù)（例如，日期、時間、數(shù)學和加密函數(shù)），所有跨存儲引擎的功能都在這一層實現(xiàn)，包括：存儲過程、觸發(fā)器、視圖等。

存儲引擎層負責 MySQL 中數(shù)據(jù)的存儲和提取。

首先，我們創(chuàng)建一張表：

CREATE TABLE`test_temp` (
`id`INT(11) NOTNULLDEFAULT'0',
`a`VARCHAR(20) DEFAULTNULL,
`b`VARCHAR(10) DEFAULTNULL,
`c`VARCHAR(10) DEFAULTNULL,
`d`VARCHAR(10) DEFAULTNULL,
  PRIMARY KEY (`id`),
KEY`a_b`(`a`,`b`)
) ENGINE=INNODBDEFAULTCHARSET=utf8

插入一批數(shù)據(jù)：

INSERT INTO test_temp VALUES(100, 10, 20, 2, 1);
INSERT INTO test_temp VALUES(200, 10, 40, 4, 2);
INSERT INTO test_temp VALUES(300, 10, 30, 3, 3);
INSERT INTO test_temp VALUES(400, 40, 10, 1, 4);

這時我們看一下下面這條 SQL 的執(zhí)行計劃：

EXPLAIN SELECT * FROM test_temp WHERE a > '10' AND b < '50';

我們看一下執(zhí)行計劃：

圖片

上圖中的 Using index condition 就是使用了索引下推。

如果不使用索引下推，比如只對 a 這個字段加了索引，那就會對 a 這個字段篩選出來的 id，依次做回表查詢，查到結果后再對 b 字段進行過濾。

而使用了索引下推，SQL 執(zhí)行過程如下：

1.Server 層向存儲引擎查詢數(shù)據(jù)；

2.存儲引擎根據(jù) a_b 聯(lián)合索引首先找到所有 a > '10' 的數(shù)據(jù)，根據(jù)聯(lián)合索引中已經(jīng)存在的 b 字段對數(shù)據(jù)做過濾，找出符合條件 b < '50' 的數(shù)據(jù)；

3.存儲引擎根據(jù) a_b 聯(lián)合索引找到所有符合條件的數(shù)據(jù)后，回表查詢，給 Server 層返回結果集。

可以看到，索引下推最大的優(yōu)勢就是在存儲引擎層，利用聯(lián)合索引的優(yōu)勢對查詢條件進行了過濾，這樣可以減少回表查詢次數(shù)，從而大大減少 I/O 次數(shù)，提升查詢性能。

索引下推是在 MySQL 5.6 版本中才引入的，MySQL 5.6 以前版本沒有這個功能。

當然使用索引下推也有一定限制：

1.索引下推主要適用于 eq_ref、range、ref、ref_or_null 這幾個場景；

2.InnoDB 和 MyISAM 存儲引擎都支持索引下推，MySQL 分區(qū)表也支持；

3.對 InnoDB 存儲引擎來說，索引下推只適用于二級索引，主鍵索引（聚集索引）不支持，因為主鍵索引存儲了數(shù)據(jù)，不存在回表這一說;

4.語句中子查詢的條件不支持索引下推；

5.使用了存儲函數(shù)的 SQL，存儲函數(shù)中的條件不支持索引下推，因為存儲引擎無法調(diào)用存儲函數(shù)。

我們再看下面這個查詢語句（把條件 b 改成條件 c）：

EXPLAIN SELECT * FROM test_temp WHERE a > '10' AND c < '50';

這個語句其實并不能使用聯(lián)合索引第二個字段在存儲引擎層做過濾，還是需要對每一條索引 a_b 上查詢到的 id 做回表查詢，但是執(zhí)行計劃里面卻有索引下推，這也是需要注意的一點。

圖片

總結

本文介紹了 MySQL 的回表和索引下推，這兩個概念在 MySQL 中非常重要，希望對你的學習和面試有所幫助。