相信很多人對(duì)于MySQL的索引都不陌生，索引(Index)是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。

因?yàn)樗饕荕ySQL中比較重點(diǎn)的知識(shí)，相信很多人都有一定的了解，尤其是在面試中出現(xiàn)的頻率特別高。樓主自認(rèn)為自己對(duì)MySQL的索引相關(guān)知識(shí)有很多了解，而且因?yàn)樽罱谡夜ぷ髅嬖?，所以單?dú)復(fù)習(xí)了很多關(guān)于索引的知識(shí)。

但是，我還是圖樣圖森破，直到我被阿里的面試官虐過(guò)之后我才知道，自己在索引方面的知識(shí)，只是個(gè)小學(xué)生水平。

以下，是我總結(jié)的一次阿里面試中關(guān)于索引有關(guān)的問(wèn)題以及知識(shí)點(diǎn)。

1 .索引概念、索引模型

我們是怎么聊到索引的呢，是因?yàn)槲姨岬轿覀兊臉I(yè)務(wù)量比較大，每天大概有幾百萬(wàn)的新數(shù)據(jù)生成，于是有了以下對(duì)話(huà)：

Q：你們每天這么大的數(shù)據(jù)量，都是保存在關(guān)系型數(shù)據(jù)庫(kù)中嗎?

A：是的，我們線(xiàn)上使用的是MySQL數(shù)據(jù)庫(kù)。

Q：每天幾百萬(wàn)數(shù)據(jù)，一個(gè)月就是幾千萬(wàn)了，那你們有沒(méi)有對(duì)于查詢(xún)做一些優(yōu)化呢?

A：我們?cè)跀?shù)據(jù)庫(kù)中創(chuàng)建了一些索引(我現(xiàn)在非常后悔我當(dāng)時(shí)說(shuō)了這句話(huà))

這里可以看到，阿里的面試官并不會(huì)像有一些公司一樣拿著題庫(kù)一道一道的問(wèn)，而是會(huì)根據(jù)面試者做過(guò)的事情以及面試過(guò)程中的一些內(nèi)容進(jìn)行展開(kāi)。

Q：那你能說(shuō)說(shuō)什么是索引嗎?

A：(這道題肯定難不住我啊)索引其實(shí)是一種數(shù)據(jù)結(jié)構(gòu)，能夠幫助我們快速的檢索數(shù)據(jù)庫(kù)中的數(shù)據(jù)。

Q：那么索引具體采用的哪種數(shù)據(jù)結(jié)構(gòu)呢?

A：(這道題我也背過(guò))常見(jiàn)的MySQL主要有兩種結(jié)構(gòu)：Hash索引和B+ Tree索引，我們使用的是InnoDB引擎，默認(rèn)的是B+樹(shù)。

這里我耍了一個(gè)小心機(jī)，特意說(shuō)了一下索引和存儲(chǔ)引擎有關(guān)。希望面試官可以問(wèn)我一些關(guān)于存儲(chǔ)引擎的問(wèn)題。然而面試官并沒(méi)有被我?guī)?..

Q：既然你提到InnoDB使用的B+ 樹(shù)的索引模型，那么你知道為什么采用B+ 樹(shù)嗎?這和Hash索引比較起來(lái)有什么優(yōu)缺點(diǎn)嗎?

A：(突然覺(jué)得這道題有點(diǎn)難，但是我還是憑借著自己的知識(shí)儲(chǔ)備簡(jiǎn)單的回答上一些)因?yàn)镠ash索引底層是哈希表，哈希表是一種以key-value存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)，所以多個(gè)數(shù)據(jù)在存儲(chǔ)關(guān)系上是完全沒(méi)有任何順序關(guān)系的，所以，對(duì)于區(qū)間查詢(xún)是無(wú)法直接通過(guò)索引查詢(xún)的，就需要全表掃描。所以，哈希索引只適用于等值查詢(xún)的場(chǎng)景。而B(niǎo)+ 樹(shù)是一種多路平衡查詢(xún)樹(shù)，所以他的節(jié)點(diǎn)是天然有序的(左子節(jié)點(diǎn)小于父節(jié)點(diǎn)、父節(jié)點(diǎn)小于右子節(jié)點(diǎn))，所以對(duì)于范圍查詢(xún)的時(shí)候不需要做全表掃描。

Q：除了上面這個(gè)范圍查詢(xún)的，你還能說(shuō)出其他的一些區(qū)別嗎?

A：(這個(gè)題我回答的不好，事后百度了一下)

B+ Tree索引和Hash索引區(qū)別?

哈希索引適合等值查詢(xún)，但是無(wú)法進(jìn)行范圍查詢(xún)

哈希索引沒(méi)辦法利用索引完成排序

哈希索引不支持多列聯(lián)合索引的最左匹配規(guī)則

如果有大量重復(fù)鍵值的情況下，哈希索引的效率會(huì)很低，因?yàn)榇嬖诠Ｅ鲎矄?wèn)題

2 .聚簇索引、覆蓋索引

Q：剛剛我們聊到B+ Tree ，那你知道B+ Tree的葉子節(jié)點(diǎn)都可以存哪些東西嗎?

A：InnoDB的B+ Tree可能存儲(chǔ)的是整行數(shù)據(jù)，也有可能是主鍵的值。

Q：那這兩者有什么區(qū)別嗎?

A：(當(dāng)他問(wèn)我葉子節(jié)點(diǎn)的時(shí)候，其實(shí)我就猜到他可能要問(wèn)我聚簇索引和非聚簇索引了)在 InnoDB 里，索引B+ Tree的葉子節(jié)點(diǎn)存儲(chǔ)了整行數(shù)據(jù)的是主鍵索引，也被稱(chēng)之為聚簇索引。而索引B+ Tree的葉子節(jié)點(diǎn)存儲(chǔ)了主鍵的值的是非主鍵索引，也被稱(chēng)之為非聚簇索引。

Q：那么，聚簇索引和非聚簇索引，在查詢(xún)數(shù)據(jù)的時(shí)候有區(qū)別嗎?

A：聚簇索引查詢(xún)會(huì)更快?

Q：為什么呢?

A：因?yàn)橹麈I索引樹(shù)的葉子節(jié)點(diǎn)直接就是我們要查詢(xún)的整行數(shù)據(jù)了。而非主鍵索引的葉子節(jié)點(diǎn)是主鍵的值，查到主鍵的值以后，還需要再通過(guò)主鍵的值再進(jìn)行一次查詢(xún)。

Q：剛剛你提到主鍵索引查詢(xún)只會(huì)查一次，而非主鍵索引需要回表查詢(xún)多次。(后來(lái)我才知道，原來(lái)這個(gè)過(guò)程叫做回表)是所有情況都是這樣的嗎?非主鍵索引一定會(huì)查詢(xún)多次嗎?

A：(額、這個(gè)問(wèn)題我回答的不好，后來(lái)我自己查資料才知道，通過(guò)覆蓋索引也可以只查詢(xún)一次)

覆蓋索引?

覆蓋索引(covering index)指一個(gè)查詢(xún)語(yǔ)句的執(zhí)行只用從索引中就能夠取得，不必從數(shù)據(jù)表中讀取。也可以稱(chēng)之為實(shí)現(xiàn)了索引覆蓋。

當(dāng)一條查詢(xún)語(yǔ)句符合覆蓋索引條件時(shí)，MySQL只需要通過(guò)索引就可以返回查詢(xún)所需要的數(shù)據(jù)，這樣避免了查到索引后再返回表操作，減少I(mǎi)/O提高效率。

如，表covering_index_sample中有一個(gè)普通索引 idx_key1_key2(key1,key2)。

當(dāng)我們通過(guò)SQL語(yǔ)句：select key2 from covering_index_sample where key1 = 'keytest';的時(shí)候，就可以通過(guò)覆蓋索引查詢(xún)，無(wú)需回表。

3 .聯(lián)合索引、最左前綴匹配

Q：不知道的話(huà)沒(méi)關(guān)系，想問(wèn)一下，你們?cè)趧?chuàng)建索引的時(shí)候都會(huì)考慮哪些因素呢?

A：我們一般對(duì)于查詢(xún)概率比較高，經(jīng)常作為where條件的字段設(shè)置索引。

Q： 那你們有用過(guò)聯(lián)合索引嗎?

A：用過(guò)呀，我們有對(duì)一些表中創(chuàng)建過(guò)聯(lián)合索引。

Q：那你們?cè)趧?chuàng)建聯(lián)合索引的時(shí)候，需要做聯(lián)合索引多個(gè)字段之間順序你們是如何選擇的呢?

A：我們把識(shí)別度***的字段放到最前面。

Q：為什么這么做呢?

A：(這個(gè)問(wèn)題有點(diǎn)把我問(wèn)蒙了，稍微有些慌亂)這樣的話(huà)可能***率會(huì)高一點(diǎn)吧。。。

Q： 那你知道最左前綴匹配嗎?

A：(我突然想起來(lái)原來(lái)面試官是想問(wèn)這個(gè)，怪自己剛剛為什么就沒(méi)想到這個(gè)呢。)哦哦哦。您剛剛問(wèn)的是這個(gè)意思啊，在創(chuàng)建多列索引時(shí)，我們根據(jù)業(yè)務(wù)需求，where子句中使用最頻繁的一列放在最左邊，因?yàn)镸ySQL索引查詢(xún)會(huì)遵循最左前綴匹配的原則，即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開(kāi)始匹配。所以當(dāng)我們創(chuàng)建一個(gè)聯(lián)合索引的時(shí)候，如(key1,key2,key3)，相當(dāng)于創(chuàng)建了(key1)、(key1,key2)和(key1,key2,key3)三個(gè)索引，這就是最左匹配原則。

雖然我一開(kāi)始有點(diǎn)懵，沒(méi)有聯(lián)想到最左前綴匹配，但是面試官還是引導(dǎo)了我。很友善。

4 .索引下推、查詢(xún)優(yōu)化

Q：你們線(xiàn)上用的MySQL是哪個(gè)版本啊呢?

A：我們MySQL是5.7

Q：那你知道在MySQL 5.6中，對(duì)索引做了哪些優(yōu)化嗎?

A：不好意思，這個(gè)我沒(méi)有去了解過(guò)。(事后我查了一下，有一個(gè)比較重要的 ：Index Condition Pushdown Optimization)

Index Condition Pushdown(索引下推)

MySQL 5.6引入了索引下推優(yōu)化，默認(rèn)開(kāi)啟，使用SET optimizer_switch = 'index_condition_pushdown=off';可以將其關(guān)閉。官方文檔中給的例子和解釋如下：

people表中(zipcode，lastname，firstname)構(gòu)成一個(gè)索引

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

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

如果使用了索引下推技術(shù)，則MYSQL首先會(huì)返回符合zipcode='95054'的索引，然后根據(jù)lastname LIKE '%etrunia%'和address LIKE '%Main Street%'來(lái)判斷索引是否符合條件。如果符合條件，則根據(jù)該索引來(lái)定位對(duì)應(yīng)的數(shù)據(jù)，如果不符合，則直接reject掉。有了索引下推優(yōu)化，可以在有l(wèi)ike條件查詢(xún)的情況下，減少回表次數(shù)。

Q：你們創(chuàng)建的那么多索引，到底有沒(méi)有生效，或者說(shuō)你們的SQL語(yǔ)句有沒(méi)有使用索引查詢(xún)你們有統(tǒng)計(jì)過(guò)嗎?

A：這個(gè)還沒(méi)有統(tǒng)計(jì)過(guò)，除非遇到慢SQL的時(shí)候我們才會(huì)去排查。

Q：那排查的時(shí)候，有什么手段可以知道有沒(méi)有走索引查詢(xún)呢?

A：可以通過(guò)explain查看sql語(yǔ)句的執(zhí)行計(jì)劃，通過(guò)執(zhí)行計(jì)劃來(lái)分析索引使用情況。

Q：那什么情況下會(huì)發(fā)生明明創(chuàng)建了索引，但是執(zhí)行的時(shí)候并沒(méi)有通過(guò)索引呢?

A：(大概記得和優(yōu)化器有關(guān)，但是這個(gè)問(wèn)題并沒(méi)有回答好)

查詢(xún)優(yōu)化器?

一條SQL語(yǔ)句的查詢(xún)，可以有不同的執(zhí)行方案，至于最終選擇哪種方案，需要通過(guò)優(yōu)化器進(jìn)行選擇，選擇執(zhí)行成本***的方案。

在一條單表查詢(xún)語(yǔ)句真正執(zhí)行之前，MySQL的查詢(xún)優(yōu)化器會(huì)找出執(zhí)行該語(yǔ)句所有可能使用的方案，對(duì)比之后找出成本***的方案。

這個(gè)成本***的方案就是所謂的執(zhí)行計(jì)劃。優(yōu)化過(guò)程大致如下：

1、根據(jù)搜索條件，找出所有可能使用的索引

2、計(jì)算全表掃描的代價(jià)

3、計(jì)算使用不同索引執(zhí)行查詢(xún)的代價(jià)

4、對(duì)比各種執(zhí)行方案的代價(jià)，找出成本***的那一個(gè)

Q：哦，索引有關(guān)的知識(shí)我們暫時(shí)就問(wèn)這么多吧。你們線(xiàn)上數(shù)據(jù)的事務(wù)隔離級(jí)別是什么呀?

A：(后面關(guān)于事務(wù)隔離級(jí)別的問(wèn)題了，就不展開(kāi)了)

感覺(jué)是因?yàn)槲一卮鸬牟粔蚝茫绻@幾個(gè)索引問(wèn)題我都會(huì)的話(huà)，他還會(huì)追問(wèn)更多，恐怕會(huì)被虐的更慘。

5 .總結(jié)&感悟

以上，就是一次面試中關(guān)于索引部分知識(shí)的問(wèn)題以及我整理的答案。感覺(jué)這次面試過(guò)程中關(guān)于索引的知識(shí)，自己大概能夠回答的內(nèi)容占70%左右，但是自信完全答對(duì)的內(nèi)容只占50%左右，看來(lái)自己索引有關(guān)的知識(shí)了解的還是不夠多。

通過(guò)這次面試，發(fā)現(xiàn)像阿里這種大廠對(duì)于底層知識(shí)還是比較看重的，我以前以為關(guān)于索引最多也就問(wèn)一下Hash和B+有什么區(qū)別，沒(méi)想到***都能問(wèn)到查詢(xún)優(yōu)化器上面。

***，不管本次面試能不能通過(guò)，都非常感謝有這樣一次機(jī)會(huì)，可以讓自己看到自己的不足。通過(guò)這次面試，我也收獲了很多東西。加油!