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

派大星：是的，有了解。

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

派大星：可以。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

這個(gè)具體可以參考官方文檔：

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

如圖：

圖片

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

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

所以在聯(lián)合索引中，由于某個(gè)前導(dǎo)列因?yàn)樗饕ФM(jìn)行掃表并回表時(shí)，就可以進(jìn)行索引下推優(yōu)化了。

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

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

因?yàn)樯鲜鯯QL的字段類型不匹配導(dǎo)致索引失效，但是通過索引下推優(yōu)化其實(shí)是可以減少回表的次數(shù)的。

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

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

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

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

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

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

派大星：謝謝。