[[422248]]

本文轉(zhuǎn)載自微信公眾號(hào)「飛天小牛肉」，作者小牛肉。轉(zhuǎn)載本文請(qǐng)聯(lián)系飛天小牛肉公眾號(hào)。

什么是回表查詢

小伙伴們可以先看這篇文章了解下什么是聚集索引和輔助索引：Are You OK?主鍵、聚集索引、輔助索引，簡(jiǎn)單回顧下，聚集索引的葉子節(jié)點(diǎn)包含完整的行數(shù)據(jù)，而非聚集索引的葉子節(jié)點(diǎn)存儲(chǔ)的是每行數(shù)據(jù)的輔助索引鍵 + 該行數(shù)據(jù)對(duì)應(yīng)的聚集索引鍵(主鍵值)。

假設(shè)有張 user 表，包含 id(主鍵)，name，age(普通索引)三列，有如下數(shù)據(jù)：

id name age 
1 Jack     18 
7 Alice     28 
10 Bob      38 
20 Carry     48 

畫一個(gè)比較簡(jiǎn)單比較容易懂的圖來(lái)看下聚集索引和輔助索引：

• 聚集索引：

• 輔助索引(age)：

如果查詢條件為主鍵，則只需掃描一次聚集索引的 B+ 樹(shù)即可定位到要查找的行記錄。舉個(gè)例子：

select * from user where id = 7; 

查找過(guò)程如圖中綠色所示：

如果查詢條件為普通索引(輔助索引) age，則需要先查一遍輔助索引 B+ 樹(shù)，根據(jù)輔助索引鍵得到對(duì)應(yīng)的聚集索引鍵，然后再去聚集索引 B+ 樹(shù)中查找到對(duì)應(yīng)的行記錄。舉個(gè)例子：

select * from user where age = 28; 

上述 select * 等同于 select id, age, name 對(duì)吧，id 是主鍵索引，age 是普通索引，而 name 并不存在于 age 索引的 B+ 樹(shù)上，所以通過(guò) age 索引查詢到 id 和 age 的值之后，還需要去聚集索引上才能查到 name 的值。

如圖所示，第一步，查 age 輔助索引：

第二步，查聚集索引：

這就是所謂的回表查詢，因?yàn)樾枰獟呙鑳纱嗡饕?B+ 樹(shù)，所以很顯然它的性能較掃一遍索引樹(shù)更低。

什么是覆蓋索引

覆蓋索引的目的就是避免發(fā)生回表查詢，也就是說(shuō)，通過(guò)覆蓋索引，只需要掃描一次 B+ 樹(shù)即可獲得所需的行記錄。

如何實(shí)現(xiàn)覆蓋索引

上文解釋過(guò)，下面這個(gè) SQL 語(yǔ)句需要查詢兩次 B+ 樹(shù)：

select * from user where age = 28; 

我們將其稍作修改，使其只需要查詢一次 B+ 樹(shù)：

select id, age from user where age = 28; 

之前我們的返回結(jié)果是整個(gè)行記錄，現(xiàn)在我們的返回結(jié)果只需要 id 和 age。

id 是什么?主鍵索引(聚集索引)，age 是什么?普通索引(輔助索引)，age 索引的 B+ 樹(shù)的葉子節(jié)點(diǎn)存儲(chǔ)的是什么?輔助索引鍵 + 對(duì)應(yīng)的聚集索引鍵

所以這條 SQL 語(yǔ)句只需要掃描一次 age 索引的 B+ 樹(shù)就行了

這樣，結(jié)合這個(gè)例子，不知道各位有沒(méi)有受到啟發(fā)，如何實(shí)現(xiàn)覆蓋索引拒絕回表查詢呢?

答：聯(lián)合索引。

我們把 age,name 設(shè)置為聯(lián)合索引：

create index idx_age_name on user(`age`,`name`); 

此時(shí) age 和 name 作為輔助索引鍵都在同一棵輔助索引的 B+ 樹(shù)上，所以只需掃描一次這個(gè)組合索引的 B+ 樹(shù)即可獲取到 id、age 和 name，這就是實(shí)現(xiàn)了索引覆蓋

覆蓋索引的常見(jiàn)使用場(chǎng)景

在下面三個(gè)場(chǎng)景中，可以使用覆蓋索引來(lái)進(jìn)行優(yōu)化 SQL 語(yǔ)句：

1)列查詢回表優(yōu)化(如上面講的例子，將單列索引 age 升級(jí)為聯(lián)合索引(age, name))

2)全表 count 查詢

舉個(gè)例子，假設(shè) user 表中現(xiàn)在只有一個(gè)索引即主鍵 id：

select count(age) from user; 

可以用 explain 分析下這條語(yǔ)句，如果 Extra 字段為 Using index 時(shí)，就表示觸發(fā)索引覆蓋：

顯然現(xiàn)在是沒(méi)有觸發(fā)覆蓋索引的，我們來(lái)優(yōu)化下：將 age 列設(shè)置為索引 create index idx_age on user(age)，這樣只需要查一遍 age 索引的 B+ 樹(shù)即可得到結(jié)果：

3)分頁(yè)查詢

select id, age, name from user order by username limit 500, 100; 

對(duì)于這條 SQL，因?yàn)?name 字段不是索引，所以在分頁(yè)查詢需要進(jìn)行回表查詢。

Using filesort 表示沒(méi)有使用索引的排序，或者說(shuō)表示在索引之外，需要額外進(jìn)行外部的排序動(dòng)作?？吹竭@個(gè)字段就應(yīng)該意識(shí)到你需要對(duì)這條 SQL 進(jìn)行優(yōu)化了。

使用索引覆蓋優(yōu)化：將 (age, name) 設(shè)置為聯(lián)合索引，這樣只需要查一遍 (age, name) 聯(lián)合索引的 B+ 樹(shù)即可得到結(jié)果。