什么是回表查詢
小伙伴們可以先看這篇文章了解下什么是聚集索引和輔助索引：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+ 樹即可得到結果，
我是小牛肉，長風破浪會有時，小伙伴們下篇文章再見 ??
?? 關注公眾號 | 飛天小牛肉，即時獲取更新
-
博主東南大學碩士在讀，攜程 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 可以獲取配套教程，目前尚在更新中，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/298215.html
標籤：MySQL
上一篇：MySQL實戰45講之基礎篇