本文來自一位不愿意透露姓名的粉絲投稿
相信很多人對(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í)烹吵。
但是，我還是圖樣圖森破桨武，直到我被阿里的面試官虐過之后我才知道肋拔，自己在索引方面的知識(shí)，只是個(gè)小學(xué)生水平呀酸。
以下凉蜂，是我總結(jié)的一次阿里面試中關(guān)于索引有關(guān)的問題以及知識(shí)點(diǎn)。
索引概念性誉、索引模型
我們是怎么聊到索引的呢窿吩，是因?yàn)槲姨岬轿覀兊臉I(yè)務(wù)量比較大，每天大概有幾百萬的新數(shù)據(jù)生成艾栋，于是有了以下對(duì)話：
面試官：你們每天這么大的數(shù)據(jù)量爆存，都是保存在關(guān)系型數(shù)據(jù)庫(kù)中嗎？
我：是的蝗砾，我們線上使用的是MySQL數(shù)據(jù)庫(kù)
面試官：每天幾百萬數(shù)據(jù)先较，一個(gè)月就是幾千萬了携冤，那你們有沒有對(duì)于查詢做一些優(yōu)化呢？
我：我們?cè)跀?shù)據(jù)庫(kù)中創(chuàng)建了一些索引（我現(xiàn)在非常后悔我當(dāng)時(shí)說了這句話）闲勺。
這里可以看到曾棕，阿里的面試官并不會(huì)像有一些公司一樣拿著題庫(kù)一道一道的問，而是會(huì)根據(jù)面試者做過的事情以及面試過程中的一些內(nèi)容進(jìn)行展開菜循。
面試官：那你能說說什么是索引嗎翘地？
我：（這道題肯定難不住我啊）索引其實(shí)是一種數(shù)據(jù)結(jié)構(gòu)癌幕，能夠幫助我們快速的檢索數(shù)據(jù)庫(kù)中的數(shù)據(jù)衙耕。
面試官：那么索引具體采用的哪種數(shù)據(jù)結(jié)構(gòu)呢？
我：（這道題我也背過）常見的MySQL主要有兩種結(jié)構(gòu)：Hash索引和B+ Tree索引勺远，我們使用的是InnoDB引擎橙喘，默認(rèn)的是B+樹。
這里我耍了一個(gè)小心機(jī)胶逢，特意說了一下索引和存儲(chǔ)引擎有關(guān)厅瞎。希望面試官可以問我一些關(guān)于存儲(chǔ)引擎的問題。
面試官：既然你提到InnoDB使用的B+ Tree的索引模型初坠，那么你知道為什么采用B+ 樹嗎和簸？這和Hash索引比較起來有什么優(yōu)缺點(diǎn)嗎？
我：（突然覺得這道題有點(diǎn)難碟刺，但是我還是憑借著自己的知識(shí)儲(chǔ)備簡(jiǎn)單的回答上一些）因?yàn)镠ash索引底層是哈希表锁保，哈希表是一種以key-value存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)，所以多個(gè)數(shù)據(jù)在存儲(chǔ)關(guān)系上是完全沒有任何順序關(guān)系的南誊，所以身诺，對(duì)于區(qū)間查詢是無法直接通過索引查詢的，就需要全表掃描抄囚。所以霉赡，哈希索引只適用于等值查詢的場(chǎng)景。而B+ Tree是一種多路平衡查詢樹幔托，所以他的節(jié)點(diǎn)是天然有序的（左子節(jié)點(diǎn)小于父節(jié)點(diǎn)穴亏、父節(jié)點(diǎn)小于右子節(jié)點(diǎn)），所以對(duì)于范圍查詢的時(shí)候不需要做全表掃描重挑。
面試官：除了上面這個(gè)范圍查詢的嗓化，你還能說出其他的一些區(qū)別嗎？
我：（這個(gè)題我回答的不好谬哀，事后百度了一下）
科普時(shí)間：B+ Tree索引和Hash索引區(qū)別 哈希索引適合等值查詢刺覆，但是不無法進(jìn)行范圍查詢 哈希索引沒辦法利用索引完成排序 哈希索引不支持多列聯(lián)合索引的最左匹配規(guī)則 如果有大量重復(fù)鍵值得情況下，哈希索引的效率會(huì)很低史煎，因?yàn)榇嬖诠Ｅ鲎矄栴}
聚簇索引谦屑、覆蓋索引
面試官：剛剛我們聊到B+ Tree 驳糯，那你知道B+ Tree的葉子節(jié)點(diǎn)都可以存哪些東西嗎？
我：InnoDB的B+ Tree可能存儲(chǔ)的是整行數(shù)據(jù)氢橙，也有可能是主鍵的值酝枢。
面試官：那這兩者有什么區(qū)別嗎？ 我：（當(dāng)他問我葉子節(jié)點(diǎn)的時(shí)候悍手，其實(shí)我就猜到他可能要問我聚簇索引和非聚簇索引了）在 InnoDB 里帘睦，索引B+ Tree的葉子節(jié)點(diǎn)存儲(chǔ)了整行數(shù)據(jù)的是主鍵索引，也被稱之為聚簇索引坦康。而索引B+ Tree的葉子節(jié)點(diǎn)存儲(chǔ)了主鍵的值的是非主鍵索引竣付，也被稱之為非聚簇索引。
面試官：那么滞欠，聚簇索引和非聚簇索引卑笨，在查詢數(shù)據(jù)的時(shí)候有區(qū)別嗎？
我：聚簇索引查詢會(huì)更快仑撞？
面試官：為什么呢？
我：因?yàn)橹麈I索引樹的葉子節(jié)點(diǎn)直接就是我們要查詢的整行數(shù)據(jù)了妖滔。而非主鍵索引的葉子節(jié)點(diǎn)是主鍵的值隧哮，查到主鍵的值以后，還需要再通過主鍵的值再進(jìn)行一次查詢座舍。
面試官：剛剛你提到主鍵索引查詢只會(huì)查一次沮翔，而非主鍵索引需要回表查詢多次。（后來我才知道曲秉，原來這個(gè)過程叫做回表）是所有情況都是這樣的嗎采蚀？非主鍵索引一定會(huì)查詢多次嗎？
我：（額承二、這個(gè)問題我回答的不好榆鼠，后來我自己查資料才知道，通過覆蓋索引也可以只查詢一次）
科普時(shí)間——覆蓋索引 覆蓋索引（covering index）指一個(gè)查詢語句的執(zhí)行只用從索引中就能夠取得亥鸠，不必從數(shù)據(jù)表中讀取妆够。也可以稱之為實(shí)現(xiàn)了索引覆蓋。 當(dāng)一條查詢語句符合覆蓋索引條件時(shí)负蚊，MySQL只需要通過索引就可以返回查詢所需要的數(shù)據(jù)神妹，這樣避免了查到索引后再返回表操作，減少I/O提高效率家妆。 如鸵荠，表covering_index_sample中有一個(gè)普通索引 idx_key1_key2(key1,key2)。當(dāng)我們通過SQL語句：select key2 from covering_index_sample where key1 = ‘keytest’;的時(shí)候伤极，就可以通過覆蓋索引查詢蛹找，無需回表姨伤。
聯(lián)合索引、最左前綴匹配
面試官：不知道的話沒關(guān)系熄赡，想問一下姜挺，你們?cè)趧?chuàng)建索引的時(shí)候都會(huì)考慮哪些因素呢？
我：我們一般對(duì)于查詢概率比較高彼硫，經(jīng)常作為where條件的字段設(shè)置索引
面試官：那你們有用過聯(lián)合索引嗎炊豪？
我：用過呀，我們有對(duì)一些表中創(chuàng)建過聯(lián)合索引拧篮。
面試官：那你們?cè)趧?chuàng)建聯(lián)合索引的時(shí)候词渤，需要做聯(lián)合索引多個(gè)字段之間順序你們是如何選擇的呢？
我：我們把識(shí)別度最高的字段放到最前面串绩。
面試官：為什么這么做呢缺虐？
我：（這個(gè)問題有點(diǎn)把我問蒙了，稍微有些慌亂）這樣的話可能命中率會(huì)高一點(diǎn)吧礁凡。高氮。。
面試官：那你知道最左前綴匹配嗎顷牌？
我：（我突然想起來原來面試官是想問這個(gè)剪芍，怪自己剛剛為什么就沒想到這個(gè)呢。）哦哦哦窟蓝。您剛剛問的是這個(gè)意思啊罪裹，在創(chuàng)建多列索引時(shí)，我們根據(jù)業(yè)務(wù)需求运挫，where子句中使用最頻繁的一列放在最左邊状共，因?yàn)镸ySQL索引查詢會(huì)遵循最左前綴匹配的原則，即最左優(yōu)先谁帕，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開始匹配峡继。所以當(dāng)我們創(chuàng)建一個(gè)聯(lián)合索引的時(shí)候，如(key1,key2,key3)匈挖，相當(dāng)于創(chuàng)建了（key1）鬓椭、(key1,key2)和(key1,key2,key3)三個(gè)索引，這就是最左匹配原則关划。
雖然我一開始有點(diǎn)懵小染，沒有聯(lián)想到最左前綴匹配，但是面試官還是引導(dǎo)了我贮折。很友善裤翩。
索引下推、查詢優(yōu)化
面試官：你們線上用的MySQL是哪個(gè)版本啊呢？
我：我們MySQL是5.7
面試官：那你知道在MySQL 5.6中踊赠，對(duì)索引做了哪些優(yōu)化嗎呵扛？
我：不好意思，這個(gè)我沒有去了解過筐带。（事后我查了一下今穿，有一個(gè)比較重要的 ：Index Condition Pushdown Optimization）
科普時(shí)間—— Index Condition Pushdown（索引下推） MySQL 5.6引入了索引下推優(yōu)化，默認(rèn)開啟伦籍，使用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%’;
如果沒有使用索引下推技術(shù)芝薇，則MySQL會(huì)通過zipcode='95054’從存儲(chǔ)引擎中查詢對(duì)應(yīng)的數(shù)據(jù)，返回到MySQL服務(wù)端作儿，然后MySQL服務(wù)端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'來判斷數(shù)據(jù)是否符合條件洛二。 如果使用了索引下推技術(shù)，則MYSQL首先會(huì)返回符合zipcode='95054’的索引攻锰，然后根據(jù)lastname LIKE '%etrunia%'和address LIKE '%Main Street%'來判斷索引是否符合條件晾嘶。如果符合條件，則根據(jù)該索引來定位對(duì)應(yīng)的數(shù)據(jù)娶吞，如果不符合变擒，則直接reject掉。 有了索引下推優(yōu)化寝志，可以在有l(wèi)ike條件查詢的情況下，減少回表次數(shù)策添。
面試官：你們創(chuàng)建的那么多索引材部，到底有沒有生效，或者說你們的SQL語句有沒有使用索引查詢你們有統(tǒng)計(jì)過嗎唯竹？
我：這個(gè)還沒有統(tǒng)計(jì)過乐导，除非遇到慢SQL的時(shí)候我們才會(huì)去排查
面試官：那排查的時(shí)候，有什么手段可以知道有沒有走索引查詢呢浸颓？
我：可以通過explain查看sql語句的執(zhí)行計(jì)劃物臂，通過執(zhí)行計(jì)劃來分析索引使用情況
面試官：那什么情況下會(huì)發(fā)生明明創(chuàng)建了索引，但是執(zhí)行的時(shí)候并沒有通過索引呢产上？
我：（依稀記得和優(yōu)化器有關(guān)棵磷，但是這個(gè)問題并沒有回答好）
科普時(shí)間——查詢優(yōu)化器 一條SQL語句的查詢，可以有不同的執(zhí)行方案晋涣，至于最終選擇哪種方案仪媒，需要通過優(yōu)化器進(jìn)行選擇，選擇執(zhí)行成本最低的方案谢鹊。 在一條單表查詢語句真正執(zhí)行之前算吩，MySQL的查詢優(yōu)化器會(huì)找出執(zhí)行該語句所有可能使用的方案留凭，對(duì)比之后找出成本最低的方案。這個(gè)成本最低的方案就是所謂的執(zhí)行計(jì)劃偎巢。 優(yōu)化過程大致如下： 1蔼夜、根據(jù)搜索條件，找出所有可能使用的索引 2压昼、計(jì)算全表掃描的代價(jià) 3求冷、計(jì)算使用不同索引執(zhí)行查詢的代價(jià) 4、對(duì)比各種執(zhí)行方案的代價(jià)巢音，找出成本最低的那一個(gè)
面試官：哦遵倦，索引有關(guān)的知識(shí)我們暫時(shí)就問這么多吧。你們線上數(shù)據(jù)的事務(wù)隔離級(jí)別是什么呀官撼？
我：(后面關(guān)于事務(wù)隔離級(jí)別的問題了梧躺，就不展開了)
感覺是因?yàn)槲一卮鸬牟粔蚝茫绻@幾個(gè)索引問題我都會(huì)的話傲绣，他還會(huì)追問更多掠哥，恐怕會(huì)被虐的更慘
總結(jié)&感悟
以上，就是一次面試中關(guān)于索引部分知識(shí)的問題以及我整理的答案秃诵。感覺這次面試過程中關(guān)于索引的知識(shí)续搀，自己大概能夠回答的內(nèi)容占70%左右，但是自信完全答對(duì)的內(nèi)容只占50%左右菠净，看來自己索引有關(guān)的知識(shí)了解的還是不夠多禁舷。
通過這次面試，發(fā)現(xiàn)像阿里這種大廠對(duì)于底層知識(shí)還是比較看重的毅往，我以前以為關(guān)于索引最多也就問一下Hash和B+有什么區(qū)別牵咙，沒想到最后都能問到查詢優(yōu)化器上面。
最后攀唯，不管本次面試能不能通過洁桌，都非常感謝有這樣一次機(jī)會(huì)，可以讓自己看到自己的不足侯嘀。通過這次面試另凌，我也收獲了很多東西。加油戒幔！
原文：https://blog.csdn.net/hollis_chuang/article/details/95167242
