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回表查詢與索引覆蓋的區別的文章就介紹到這了,更多相關MySQL回表查詢與索引覆蓋內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！