MySQL回表查詢與索引覆蓋的區別是什么

這篇文章主要介紹“MySQL回表查詢與索引覆蓋的區別是什么”，在日常操作中，相信很多人在MySQL回表查詢與索引覆蓋的區別是什么問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”MySQL回表查詢與索引覆蓋的區別是什么”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

回表查詢

InnoDB索引分為兩大類，一類是聚集索引(Clustered Index)，一類是非聚集索引(Secondary Index)

聚集索引（聚簇索引）：葉子節點中存的是整行數據，找到索引也就找到了數據，索引即數據，表中行的物理順序與鍵值的邏輯（索引）順序相同，一個表只能包含一個聚集索引。因為索引（目錄）只能按照一種方法進行排序。

非聚集索引（普通索引、非聚簇索引、二級索引）：非聚集索引的btree葉子節點中存儲的是當行數據的PK（主鍵）。例如MYISAM通過key_buffer把索引先緩存到內存中，當需要訪問數據時(通過索引訪問數據)，在內存中直接搜索索引，然后通過索引找到磁盤相應數據，這也就是為什么索引不在key buffer命中時，速度慢的原因。

為什么非主鍵索引結構葉子結點存儲的是主鍵值？

減少了出現行移動或者數據頁分裂時二級索引的維護工作（當數據需要更新的時候，二級索引不需要修改，只需要修改聚簇索引，一個表只能有一個聚簇索引，其他的都是二級索引，這樣只需要修改聚簇索引就可以了，不需要重新構建二級索引）

在使用非聚集索引時，為了取到具體數據，則需要通過PK回到聚集索引里去查詢數據。著就叫回表查詢。掃描了2次索引樹。所以效率相對較低。

索引覆蓋

索引覆蓋就是解決回表查詢的一種方案。見名知意，就是查詢的所有列均被所使用的索引列覆蓋(可以是單列索引也可以是聯合索引，通常是聯合索引，單列索引很難覆蓋查詢的所有列)。

因為索引中已經包含了要查詢的字段的值，因此查詢的時候直接返回索引中的字段值就可以了，不需要再到表中查詢，避免了對主鍵索引的二次查詢，也就提高了查詢的效率。

id為聚集索引，name為非聚集索引：

select name, age from t where name = 'lcc';

就需要回表查詢

索引覆蓋：

在SQL中只查詢name字段。這樣name的索引就覆蓋到了所有的查詢列。

select name  from t where name = 'lcc';

將name的索引修改為聯合索引(name, age )，之后還是執行select name, age from t where name = 'lcc'。這樣也覆蓋到了所有的查詢列。
因為覆蓋索引必須要存儲索引的列值，而哈希索引、空間索引和全文索引等都不存儲索引列值，從而只有使用B-Tree索引的數據可以做覆蓋索引。

進行索引覆蓋查詢時，在explain（執行計劃）的Extra列可以看到【Using Index】的信息。

索引覆蓋的優點

• 索引條目通常遠小于數據行的大小，因為覆蓋索引只需要讀取索引，極大地減少了數據的訪問量。

• 索引是按照列值順序存儲的，對于IO密集的范圍查找會比隨機從磁盤讀取每一行數據的IO小很多。

• 一些存儲引擎比如MyISAM在內存中只緩存索引，數據則依賴操作系統來緩存，因此要訪問數據的話需要一次系統調用，使用覆蓋索引則避免了這一點。

• 由于InnoDB的聚簇索引，覆蓋索引對InnoDB引擎下的數據庫表特別有用。因為InnoDB的二級索引在葉子節點中保存了行的主鍵值，如果二級索引能夠覆蓋查詢，就避免了對主鍵索引的二次查詢。

哪些場景適合使用索引覆蓋來優化SQL

• 當不需要查詢整行記錄時；

• 全表count查詢優化；

• Limit分頁查詢；

哪些情況下不要建索引

• 表記錄太少

• 經常增刪改的表或者字段（如用戶余額）

• Where條件里用不到的字段不創建索引

• 過濾性不好的不適合建索引（如性別）

索引下推

索引下推優化是 MySQL 5.6 引入的， 可以在索引遍歷過程中，對索引中包含的字段先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數

建立聯合索引：

KEY `username` (`name`,`age`) )

執行：

select * from user2 where name like 'j%' and age=99;

上面的查詢sql符合索引的最左前綴原則，所以將會用到 username 索引

5.5中上面這個 SQL 的執行流程是這樣的：

• 首先 MySQL 的 server 層調用存儲引擎獲取第一個以 j 開頭的 username。

• 存儲引擎找到 username=‘j’ 的第一條記錄后，在 B+Tree 的葉子結點中保存著主鍵 id，此時通過回表操作，去主鍵索引中找到該條記錄的完整數據，并返回給 server 層。

• server 層拿到數據之后，判斷該條記錄的 age 是否為 99，如果 age=99，就把該條記錄返回給客戶端，如果 age!=99，那就就丟棄該記錄。

 5.6中上面這個 SQL 的執行流程是這樣的：

• MySQL 的 server 層首先調用存儲引擎定位到第一個以 j 開頭的 username。

• 找到記錄后，存儲引擎并不急著回表，而是繼續判斷這條記錄的 age 是否等于 99，如果 age=99，再去回表，如果 age 不等于 99，就不去回表了，直接繼續讀取下一條記錄。

到此，關于“MySQL回表查詢與索引覆蓋的區別是什么”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！