你的 SQL 還在回表查詢嗎?快給它安排覆蓋索引

飛天小牛肉 發表於 2021-09-07
SQL

什麼是回表查詢

小夥伴們可以先看這篇文章瞭解下什麼是聚集索引和輔助索引:Are You OK?主鍵、聚集索引、輔助索引,簡單回顧下,聚集索引的葉子節點包含完整的行資料,而非聚集索引的葉子節點儲存的是每行資料的輔助索引鍵 + 該行資料對應的聚集索引鍵(主鍵值)。

假設有張 user 表,包含 id(主鍵),name,age(普通索引)三列,有如下資料:

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

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

  • 聚集索引:

    你的 SQL 還在回表查詢嗎?快給它安排覆蓋索引

  • 輔助索引(age):

    你的 SQL 還在回表查詢嗎?快給它安排覆蓋索引

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

select * from user where id = 7;

查詢過程如圖中綠色所示:

你的 SQL 還在回表查詢嗎?快給它安排覆蓋索引

如果查詢條件為普通索引(輔助索引) age,則需要先查一遍輔助索引 B+ 樹,根據輔助索引鍵得到對應的聚集索引鍵,然後再去聚集索引 B+ 樹中查詢到對應的行記錄。舉個例子:

select * from user where age = 28;

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

如圖所示,第一步,查 age 輔助索引:

你的 SQL 還在回表查詢嗎?快給它安排覆蓋索引

第二步,查聚集索引:

你的 SQL 還在回表查詢嗎?快給它安排覆蓋索引

這就是所謂的回表查詢,因為需要掃描兩次索引 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+ 樹就行了

你的 SQL 還在回表查詢嗎?快給它安排覆蓋索引

這樣,結合這個例子,不知道各位有沒有受到啟發,如何實現覆蓋索引拒絕回表查詢呢?

答:聯合索引

我們把 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 時,就表示觸發索引覆蓋:

你的 SQL 還在回表查詢嗎?快給它安排覆蓋索引

顯然現在是沒有觸發覆蓋索引的,我們來優化下:將 age 列設定為索引 create index idx_age on user(age),這樣只需要查一遍 age 索引的 B+ 樹即可得到結果:

你的 SQL 還在回表查詢嗎?快給它安排覆蓋索引

3)分頁查詢

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

對於這條 SQL,因為 name 欄位不是索引,所以在分頁查詢需要進行回表查詢。

你的 SQL 還在回表查詢嗎?快給它安排覆蓋索引

Using filesort 表示沒有使用索引的排序,或者說表示在索引之外,需要額外進行外部的排序動作。看到這個欄位就應該意識到你需要對這條 SQL 進行優化了。

使用索引覆蓋優化:將 (age, name) 設定為聯合索引,這樣只需要查一遍 (age, name) 聯合索引的 B+ 樹即可得到結果。

你的 SQL 還在回表查詢嗎?快給它安排覆蓋索引

我是小牛肉,長風破浪會有時,小夥伴們下篇文章再見 👋

🎉 關注公眾號 | 飛天小牛肉,即時獲取更新

  • 博主東南大學碩士在讀,攜程 Java 後臺開發暑期實習生,利用課餘時間運營一個公眾號『 飛天小牛肉 』,2020/12/29 日開通,專注分享計算機基礎(資料結構 + 演算法 + 計算機網路 + 資料庫 + 作業系統 + Linux)、Java 技術棧等相關原創技術好文。本公眾號的目的就是讓大家可以快速掌握重點知識,有的放矢。關注公眾號第一時間獲取文章更新,成長的路上我們一起進步

  • 並推薦個人維護的開源教程類專案: CS-Wiki(Gitee 推薦專案,現已累計 1.8k+ star), 致力打造完善的後端知識體系,在技術的路上少走彎路,歡迎各位小夥伴前來交流學習 ~ 😊

  • 如果各位小夥伴春招秋招沒有拿得出手的專案的話,可以參考我寫的一個專案「開源社群系統 Echo」Gitee 官方推薦專案,目前已累計 900+ star,基於 SpringBoot + MyBatis + MySQL + Redis + Kafka + Elasticsearch + Spring Security + ... 並提供詳細的開發文件和配套教程。公眾號後臺回覆 Echo 可以獲取配套教程,目前尚在更新中。