面試官：了解MySQL的索引吧？

派大星：是的，有了解。

面試官：那你能簡單聊聊是什么MySQL的覆蓋索引嗎？

派大星：可以。

覆蓋索引，也就是covering index。指的是一個查詢語句的執(zhí)行只用從索引中就能獲取到目標數(shù)據(jù)，不必從數(shù)據(jù)表中讀取。因此也可稱之為實現(xiàn)了索引覆蓋。

當我們執(zhí)行一條查詢語句符合覆蓋索引時，MySQL只需要通過索引就可以返回查詢所需要的數(shù)據(jù)，這樣避免了查到索引后再返回查表操作，減少I/O并提高了效率。

比如：我們有一張表covering_tabel，其中有一個普通索引idx_key1_key2(key1, key2)。當我們執(zhí)行SQLselect key1 from covering_table where key1 = "ketvalue"的時候，此時其實就i是通過了覆蓋索引進行查詢，無需回表。

但是在使用過程中要注意的是：有兩種情況是不滿足的：

1. sql的where條件不符合最左前綴匹配原則
2. SQL查詢的字段不屬于聯(lián)合索引

比如如果sql不符合最左前綴匹配，即使是索引覆蓋也是無法使用到索引的（會掃描索引樹），比如這個SQLselect key1 from covering_table where key2 = "keyvalue"

要是SQL中的查詢字段也沒有包含在聯(lián)合索引中，其實也是不會走索引覆蓋的。比如：select key2, key3 from covering_table where key1 = "keyvalue"

面試官：嗯，理解可以，那你知道什么是索引下推嗎？

派大星：有了解，索引下推是MySQL在5.6中引入的一種優(yōu)化技術，默認是開啟狀態(tài)的。當然也可以通過set optimizer_switch = index_condition_pushdown = off進行關閉。

官方文檔中大致解釋如下：

• 假設有一個people表中的(zipcode、lastname、firstname)構成一個索引。

SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';

如果要是上述SQL在沒有使用索引下推技術，則MySQL會通過 zipcode='95054' 從存儲引擎中查詢對應的數(shù)據(jù)，返回到MySQL服務端，然后MySQL服務端基于lastname LIKE '%etrunia%'  和  address LIKE '%Main Street%';  來判斷是否符合條件。

但是如果使用了索引下推技術的話，MySQL則會通過 zipcode='95054' 先返回符合條件的索引，然后根據(jù)lastname LIKE '%etrunia%' 來判斷索引是否符合條件。如果符合條件，就會根據(jù)該索引來定位對應的數(shù)據(jù)，如果不符合，則直接reject掉，有了索引下推的優(yōu)化，可以在like條件查詢的情況下，減少回表的次數(shù)。

需要注意的是：當一條SQL使用到了索引下推時，那么explain的執(zhí)行計劃中的extra字段對應的內(nèi)容為：Using index condition。

這個具體可以參考官方文檔：

https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

如圖：

圖片

面試官：挺好。那你覺得索引下推只是在Like的情況下嗎？官方其實是只提到了Like，這里你有什么想法嗎？

派大星：其實，我個人認為在上面的例子以及官網(wǎng)中都是只提到了like，但其實不知有l(wèi)ike。因為我認為索引下推其實是解決索引失效帶來的效率低的問題的一種手段。

所以在聯(lián)合索引中，由于某個前導列因為索引失效而要進行掃表并回表時，就可以進行索引下推優(yōu)化了。

比如：有聯(lián)合索引a，b。類型都是varchar，下面這個SQL也是可以用到索引下推的。

select d from t where a = "test" and b = 1;

因為上述SQL的字段類型不匹配導致索引失效，但是通過索引下推優(yōu)化其實是可以減少回表的次數(shù)的。

面試官：不錯那你知道什么是回表，怎么減少回表的次數(shù)嗎？

派大星：這個了解一些。在 InnoDB 里，索引B+ Tree的葉子節(jié)點存儲了整行數(shù)據(jù)的是主鍵索引，也被稱之為聚簇索引。而索引B+ Tree的葉子節(jié)點存儲了主鍵的值的是非主鍵索引，也被稱之為非聚簇索引。

在存儲的數(shù)據(jù)方面，主鍵（聚簇）索引的B+樹的葉子節(jié)點直接就是我們要查詢的整行數(shù)據(jù)了。而非主鍵（非聚簇）索引的葉子節(jié)點是主鍵的值。

那么，當我們根據(jù)非聚簇索引查詢的時候，會先通過非聚簇索引查到主鍵的值，之后，還需要再通過主鍵的值再進行一次查詢才能得到我們要查詢的數(shù)據(jù)。而這個過程就叫做回表。

所以，在InnoDB 中，使用主鍵查詢的時候，是效率更高的， 因為這個過程不需要回表。另外，依賴覆蓋索引、索引下推等技術，我們也可以通過優(yōu)化索引結(jié)構以及SQL語句減少回表的次數(shù)。

面試官：嗯，理解的十分透徹。有想法。

派大星：謝謝。