[[422248]]

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

什么是回表查詢

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

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

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

畫一個比較簡單比較容易懂的圖來看下聚集索引和輔助索引：

• 聚集索引：

• 輔助索引(age)：

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

select * from user where id = 7; 

查找過程如圖中綠色所示：

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

select * from user where age = 28; 

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

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

第二步，查聚集索引：

這就是所謂的回表查詢，因為需要掃描兩次索引 B+ 樹，所以很顯然它的性能較掃一遍索引樹更低。

什么是覆蓋索引

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

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

上文解釋過，下面這個 SQL 語句需要查詢兩次 B+ 樹：

select * from user where age = 28; 

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

select id, age from user where age = 28; 

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

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

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

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

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

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

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

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

覆蓋索引的常見使用場景

在下面三個場景中，可以使用覆蓋索引來進(jìn)行優(yōu)化 SQL 語句：

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

2)全表 count 查詢

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

select count(age) from user; 

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

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

3)分頁查詢

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

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

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

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