面試官:什么是回表,什么是索引下推?
大家好,我是君哥。
使用 MySQL 時(shí),我們經(jīng)常會(huì)聽到“回表”、“索引下推”這樣的概念,今天就來聊一聊什么是回表,什么是索引下推。
一、回表
1.1 概念
我們看下面這個(gè) 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)建一個(gè) test_temp 表,主鍵是 id,給字段 b 加了一個(gè)索引。插入 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 表會(huì)構(gòu)建 2 個(gè)索引,一個(gè)是主鍵索引,一個(gè)是字段 b 的普通索引。
一般主鍵索引被稱為聚集索引,普通索引被稱為非聚集索引。
我們執(zhí)行下面查詢 SQL:
select * from test_temp where b in(10, 20, 30 ,40);
這個(gè) SQL 語句的查詢過程如下圖:
圖片
1.從索引 b 上查詢 10,查到主鍵 id 的值是 400,再用 400 這個(gè) id 去主鍵索引上取出 row4;
2.從索引 b 上查詢 20,沒有查到記錄,繼續(xù)下一條;
3.從索引 b 上查詢 30,查到主鍵 id 的值是 300,再用 300 這個(gè) id 去主鍵索引上取出 row3;
4.從索引 b 上查詢 40,查到主鍵 id 的值是 200,再用 200 這個(gè) id 去主鍵索引上取出 row2;
5.給客戶端返回結(jié)果集。
上面 1、3、5 回到主鍵索引搜索數(shù)據(jù)的過程,就叫回表。上面查詢回表 3 次。
1.2 缺點(diǎn)
回表有什么問題嗎?回表次數(shù)多了,可能會(huì)嚴(yán)重影響查詢效率。
1.導(dǎo)致磁盤 I/O 增加:每次回表讀取數(shù)據(jù)行,這些數(shù)據(jù)分散在磁盤各個(gè)地方,導(dǎo)致大量的磁盤 I/O。
2.導(dǎo)致緩存失效:回表的數(shù)據(jù)如果不在緩存行中,就需要從磁盤加載,新的數(shù)據(jù)可能會(huì)覆蓋已有的緩存,影響其他查詢。
1.3 措施
那有什么方法可以避免回表嗎?下面兩個(gè)方法可以避免:
1.覆蓋索引
上面的查詢中,如果 SQL 改成:
select b, id from test_temp where b in(10, 20, 30 ,40);
這樣就不用回表查詢了。如果需要查詢 b、a 兩個(gè)字段,可以創(chuàng)建 b、a 的覆蓋索引,這樣就可以從 b、a 這個(gè)覆蓋索引上查詢出結(jié)果。
2.只查詢必要字段
修改查詢范圍,不用的字段不查詢。如果查詢的字段不多,可以把查詢語句改成只查聯(lián)合索引包含的字段。如果查詢頻率高,又沒有覆蓋索引,可以加一個(gè)包含查詢字段的聯(lián)合索引。
二、索引下推
首先回顧一下 MySQL 的邏輯架構(gòu):
圖片
Server 層是 MySQL 的核心服務(wù)層,這一次包括查詢解析、分析、優(yōu)化、緩存、以及所有內(nèi)置函數(shù)(例如,日期、時(shí)間、數(shù)學(xué)和加密函數(shù)),所有跨存儲(chǔ)引擎的功能都在這一層實(shí)現(xiàn),包括:存儲(chǔ)過程、觸發(fā)器、視圖等。
存儲(chǔ)引擎層負(fù)責(zé) MySQL 中數(shù)據(jù)的存儲(chǔ)和提取。
首先,我們創(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);
這時(shí)我們看一下下面這條 SQL 的執(zhí)行計(jì)劃:
EXPLAIN SELECT * FROM test_temp WHERE a > '10' AND b < '50';
我們看一下執(zhí)行計(jì)劃:
圖片
上圖中的 Using index condition 就是使用了索引下推。
如果不使用索引下推,比如只對(duì) a 這個(gè)字段加了索引,那就會(huì)對(duì) a 這個(gè)字段篩選出來的 id,依次做回表查詢,查到結(jié)果后再對(duì) b 字段進(jìn)行過濾。
而使用了索引下推,SQL 執(zhí)行過程如下:
1.Server 層向存儲(chǔ)引擎查詢數(shù)據(jù);
2.存儲(chǔ)引擎根據(jù) a_b 聯(lián)合索引首先找到所有 a > '10' 的數(shù)據(jù),根據(jù)聯(lián)合索引中已經(jīng)存在的 b 字段對(duì)數(shù)據(jù)做過濾,找出符合條件 b < '50' 的數(shù)據(jù);
3.存儲(chǔ)引擎根據(jù) a_b 聯(lián)合索引找到所有符合條件的數(shù)據(jù)后,回表查詢,給 Server 層返回結(jié)果集。
可以看到,索引下推最大的優(yōu)勢(shì)就是在存儲(chǔ)引擎層,利用聯(lián)合索引的優(yōu)勢(shì)對(duì)查詢條件進(jìn)行了過濾,這樣可以減少回表查詢次數(shù),從而大大減少 I/O 次數(shù),提升查詢性能。
索引下推是在 MySQL 5.6 版本中才引入的,MySQL 5.6 以前版本沒有這個(gè)功能。
當(dāng)然使用索引下推也有一定限制:
1.索引下推主要適用于 eq_ref、range、ref、ref_or_null 這幾個(gè)場(chǎng)景;
2.InnoDB 和 MyISAM 存儲(chǔ)引擎都支持索引下推,MySQL 分區(qū)表也支持;
3.對(duì) InnoDB 存儲(chǔ)引擎來說,索引下推只適用于二級(jí)索引,主鍵索引(聚集索引)不支持,因?yàn)橹麈I索引存儲(chǔ)了數(shù)據(jù),不存在回表這一說;
4.語句中子查詢的條件不支持索引下推;
5.使用了存儲(chǔ)函數(shù)的 SQL,存儲(chǔ)函數(shù)中的條件不支持索引下推,因?yàn)榇鎯?chǔ)引擎無法調(diào)用存儲(chǔ)函數(shù)。
我們?cè)倏聪旅孢@個(gè)查詢語句(把條件 b 改成條件 c):
EXPLAIN SELECT * FROM test_temp WHERE a > '10' AND c < '50';
這個(gè)語句其實(shí)并不能使用聯(lián)合索引第二個(gè)字段在存儲(chǔ)引擎層做過濾,還是需要對(duì)每一條索引 a_b 上查詢到的 id 做回表查詢,但是執(zhí)行計(jì)劃里面卻有索引下推,這也是需要注意的一點(diǎn)。
圖片
總結(jié)
本文介紹了 MySQL 的回表和索引下推,這兩個(gè)概念在 MySQL 中非常重要,希望對(duì)你的學(xué)習(xí)和面試有所幫助。