你的 SQL 還在回表查詢嗎？快給它安排上覆蓋索引
本文轉載自微信公眾號「飛天小牛肉」，作者小牛肉。轉載本文請聯系飛天小牛肉公眾號。
什么是回表查詢
小伙伴們可以先看這篇文章了解下什么是聚集索引和輔助索引：Are You OK?主鍵、聚集索引、輔助索引，簡單回顧下，聚集索引的葉子節點包含完整的行數據，而非聚集索引的葉子節點存儲的是每行數據的輔助索引鍵 + 該行數據對應的聚集索引鍵(主鍵值)。
假設有張 user 表，包含 id(主鍵)，name，age(普通索引)三列，有如下數據：
- 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+ 樹，根據輔助索引鍵得到對應的聚集索引鍵，然后再去聚集索引 B+ 樹中查找到對應的行記錄。舉個例子：
- select * from user where age = 28;
上述 select * 等同于 select id, age, name 對吧，id 是主鍵索引，age 是普通索引，而 name 并不存在于 age 索引的 B+ 樹上，所以通過 age 索引查詢到 id 和 age 的值之后，還需要去聚集索引上才能查到 name 的值。
如圖所示，第一步，查 age 輔助索引：
第二步，查聚集索引：
這就是所謂的回表查詢，因為需要掃描兩次索引 B+ 樹，所以很顯然它的性能較掃一遍索引樹更低。
什么是覆蓋索引
覆蓋索引的目的就是避免發生回表查詢，也就是說，通過覆蓋索引，只需要掃描一次 B+ 樹即可獲得所需的行記錄。
如何實現覆蓋索引
上文解釋過，下面這個 SQL 語句需要查詢兩次 B+ 樹：
- select * from user where age = 28;
我們將其稍作修改，使其只需要查詢一次 B+ 樹：
- select id, age from user where age = 28;
之前我們的返回結果是整個行記錄，現在我們的返回結果只需要 id 和 age。
id 是什么?主鍵索引(聚集索引)，age 是什么?普通索引(輔助索引)，age 索引的 B+ 樹的葉子節點存儲的是什么?輔助索引鍵 + 對應的聚集索引鍵
所以這條 SQL 語句只需要掃描一次 age 索引的 B+ 樹就行了
這樣，結合這個例子，不知道各位有沒有受到啟發，如何實現覆蓋索引拒絕回表查詢呢?
答：聯合索引。
我們把 age,name 設置為聯合索引：
- create index idx_age_name on user(`age`,`name`);
此時 age 和 name 作為輔助索引鍵都在同一棵輔助索引的 B+ 樹上，所以只需掃描一次這個組合索引的 B+ 樹即可獲取到 id、age 和 name，這就是實現了索引覆蓋
覆蓋索引的常見使用場景
在下面三個場景中，可以使用覆蓋索引來進行優化 SQL 語句：
1)列查詢回表優化(如上面講的例子，將單列索引 age 升級為聯合索引(age, name))
2)全表 count 查詢
舉個例子，假設 user 表中現在只有一個索引即主鍵 id：
- select count(age) from user;
可以用 explain 分析下這條語句，如果 Extra 字段為 Using index 時，就表示觸發索引覆蓋：
顯然現在是沒有觸發覆蓋索引的，我們來優化下：將 age 列設置為索引 create index idx_age on user(age)，這樣只需要查一遍 age 索引的 B+ 樹即可得到結果：
3)分頁查詢
- select id, age, name from user order by username limit 500, 100;
對于這條 SQL，因為 name 字段不是索引，所以在分頁查詢需要進行回表查詢。
Using filesort 表示沒有使用索引的排序，或者說表示在索引之外，需要額外進行外部的排序動作。看到這個字段就應該意識到你需要對這條 SQL 進行優化了。
使用索引覆蓋優化：將 (age, name) 設置為聯合索引，這樣只需要查一遍 (age, name) 聯合索引的 B+ 樹即可得到結果。
