Mysql索引面試題

轉(zhuǎn)載:https://mp.weixin.qq.com/s/_bk2JVOm2SkXfdcvki6-0w

本文來自一位不愿意透露姓名的粉絲投稿高诺,由Hollis整理并"還原"了面試現(xiàn)場。

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

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

但是码倦,我還是圖樣圖森破,直到我被阿里的面試官虐過之后我才知道锭碳,自己在索引方面的知識叹洲,只是個(gè)小學(xué)生水平。

以下工禾,是我總結(jié)的一次阿里面試中關(guān)于索引有關(guān)的問題以及知識點(diǎn)运提。

1

索引概念、索引模型

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

Q:你們每天這么大的數(shù)據(jù)量槽畔,都是保存在關(guān)系型數(shù)據(jù)庫中嗎栈妆?

A:是的,我們線上使用的是MySQL數(shù)據(jù)庫

Q:每天幾百萬數(shù)據(jù)厢钧,一個(gè)月就是幾千萬了鳞尔,那你們有沒有對于查詢做一些優(yōu)化呢?

A:我們在數(shù)據(jù)庫中創(chuàng)建了一些索引(我現(xiàn)在非常后悔我當(dāng)時(shí)說了這句話)


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

?

Q:那你能說說什么是索引嗎霞扬?

A:(這道題肯定難不住我案馊汀)索引其實(shí)是一種數(shù)據(jù)結(jié)構(gòu)枫振,能夠幫助我們快速的檢索數(shù)據(jù)庫中的數(shù)據(jù)

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

A:(這道題我也背過)常見的MySQL主要有兩種結(jié)構(gòu):Hash索引和B+ Tree索引萤彩,我們使用的是InnoDB引擎粪滤,默認(rèn)的是B+樹


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


Q:既然你提到InnoDB使用的B+ 樹的索引模型,那么你知道為什么采用B+ 樹嗎愚墓?這和Hash索引比較起來有什么優(yōu)缺點(diǎn)嗎予权?

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

Q:除了上面這個(gè)范圍查詢的渠抹,你還能說出其他的一些區(qū)別嗎蝙昙??

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


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

哈希索引適合等值查詢奇颠,但是無法進(jìn)行范圍查詢?

哈希索引沒辦法利用索引完成排序?

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

如果有大量重復(fù)鍵值的情況下,哈希索引的效率會很低放航,因?yàn)榇嬖诠E鲎矄栴}


2

聚簇索引烈拒、覆蓋索引


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

A:InnoDB的B+ Tree可能存儲的是整行數(shù)據(jù)荆几,也有可能是主鍵的值

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

A:(當(dāng)他問我葉子節(jié)點(diǎn)的時(shí)候赊时,其實(shí)我就猜到他可能要問我聚簇索引和非聚簇索引了)在 InnoDB 里吨铸,索引B+ Tree的葉子節(jié)點(diǎn)存儲了整行數(shù)據(jù)的是主鍵索引,也被稱之為聚簇索引祖秒。而索引B+ Tree的葉子節(jié)點(diǎn)存儲了主鍵的值的是非主鍵索引焊傅,也被稱之為非聚簇索引

Q:那么剂陡,聚簇索引和非聚簇索引,在查詢數(shù)據(jù)的時(shí)候有區(qū)別嗎狐胎?

A:聚簇索引查詢會更快鸭栖?

Q:為什么呢??

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

Q:剛剛你提到主鍵索引查詢只會查一次暴浦,而非主鍵索引需要回表查詢多次溅话。(后來我才知道,原來這個(gè)過程叫做回表)是所有情況都是這樣的嗎歌焦?非主鍵索引一定會查詢多次嗎飞几?

A:(額、這個(gè)問題我回答的不好独撇,后來我自己查資料才知道屑墨,通過覆蓋索引也可以只查詢一次)


覆蓋索引?

覆蓋索引(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í)候颠通,就可以通過覆蓋索引查詢址晕,無需回表。


3

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


Q:不知道的話沒關(guān)系斩箫,想問一下,你們在創(chuàng)建索引的時(shí)候都會考慮哪些因素呢撵儿?

A:我們一般對于查詢概率比較高乘客,經(jīng)常作為where條件的字段設(shè)置索引

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

A:用過呀淀歇,我們有對一些表中創(chuàng)建過聯(lián)合索引

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

A:我們把識別度最高的字段放到最前面

Q:為什么這么做呢浪默?

A:(這個(gè)問題有點(diǎn)把我問蒙了牡直,稍微有些慌亂)這樣的話可能命中率會高一點(diǎn)吧缀匕。。碰逸。

Q:?那你知道最左前綴匹配嗎乡小?

A:(我突然想起來原來面試官是想問這個(gè),怪自己剛剛為什么就沒想到這個(gè)呢饵史。)哦哦哦满钟。您剛剛問的是這個(gè)意思啊,在創(chuàng)建多列索引時(shí)胳喷,我們根據(jù)業(yè)務(wù)需求湃番,where子句中使用最頻繁的一列放在最左邊,因?yàn)镸ySQL索引查詢會遵循最左前綴匹配的原則吭露,即最左優(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)了我投剥。很友善。


4

索引下推担孔、查詢優(yōu)化


Q:你們線上用的MySQL是哪個(gè)版本啊呢江锨??

A:我們MySQL是5.7

Q:那你知道在MySQL 5.6中,對索引做了哪些優(yōu)化嗎糕篇??

A:不好意思啄育,這個(gè)我沒有去了解過。(事后我查了一下拌消,有一個(gè)比較重要的 :Index Condition Pushdown Optimization)


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會通過zipcode='95054'從存儲引擎中查詢對應(yīng)的數(shù)據(jù)铝阐,返回到MySQL服務(wù)端,然后MySQL服務(wù)端基于lastname

LIKE '%etrunia%'和address LIKE '%Main Street%'來判斷數(shù)據(jù)是否符合條件铐拐。

如果使用了索引下推技術(shù)徘键,則MYSQL首先會返回符合zipcode='95054'的索引练对,然后根據(jù)lastname LIKE

'%etrunia%'和address LIKE '%Main

Street%'來判斷索引是否符合條件。如果符合條件吹害,則根據(jù)該索引來定位對應(yīng)的數(shù)據(jù)螟凭,如果不符合,則直接reject掉它呀。有了索引下推優(yōu)化赂摆,可以在有l(wèi)ike條件查詢的情況下,減少回表次數(shù)钟些。

Q:你們創(chuàng)建的那么多索引烟号,到底有沒有生效,或者說你們的SQL語句有沒有使用索引查詢你們有統(tǒng)計(jì)過嗎政恍?

A:這個(gè)還沒有統(tǒng)計(jì)過汪拥,除非遇到慢SQL的時(shí)候我們才會去排查

Q:那排查的時(shí)候,有什么手段可以知道有沒有走索引查詢呢篙耗?

A:可以通過explain查看sql語句的執(zhí)行計(jì)劃迫筑,通過執(zhí)行計(jì)劃來分析索引使用情況

Q:那什么情況下會發(fā)生明明創(chuàng)建了索引,但是執(zhí)行的時(shí)候并沒有通過索引呢宗弯??

A:(大概記得和優(yōu)化器有關(guān)脯燃,但是這個(gè)問題并沒有回答好)


查詢優(yōu)化器?

一條SQL語句的查詢蒙保,可以有不同的執(zhí)行方案辕棚,至于最終選擇哪種方案,需要通過優(yōu)化器進(jìn)行選擇邓厕,選擇執(zhí)行成本最低的方案逝嚎。

在一條單表查詢語句真正執(zhí)行之前,MySQL的查詢優(yōu)化器會找出執(zhí)行該語句所有可能使用的方案详恼,對比之后找出成本最低的方案补君。

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

1昧互、根據(jù)搜索條件挽铁,找出所有可能使用的索引?

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

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

4叽掘、對比各種執(zhí)行方案的代價(jià),找出成本最低的那一個(gè)

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

A:(后面關(guān)于事務(wù)隔離級別的問題了茄菊,就不展開了)


感覺是因?yàn)槲一卮鸬牟粔蚝梅杼叮绻@幾個(gè)索引問題我都會的話赊堪,他還會追問更多,恐怕會被虐的更慘


5

總結(jié)&感悟

以上竖哩,就是一次面試中關(guān)于索引部分知識的問題以及我整理的答案哭廉。感覺這次面試過程中關(guān)于索引的知識,自己大概能夠回答的內(nèi)容占70%左右相叁,但是自信完全答對的內(nèi)容只占50%左右遵绰,看來自己索引有關(guān)的知識了解的還是不夠多。

通過這次面試增淹,發(fā)現(xiàn)像阿里這種大廠對于底層知識還是比較看重的椿访,我以前以為關(guān)于索引最多也就問一下Hash和B+有什么區(qū)別,沒想到最后都能問到查詢優(yōu)化器上面虑润。

最后成玫,不管本次面試能不能通過,都非常感謝有這樣一次機(jī)會拳喻,可以讓自己看到自己的不足哭当。通過這次面試,我也收獲了很多東西冗澈。加油钦勘!

參考資料:

極客時(shí)間 -《MySQL實(shí)戰(zhàn)45講》?

掘金小冊 -《MySQL 是怎樣運(yùn)行的:從根兒上理解 MySQL》?

博文視點(diǎn) -《高性能MySQL》

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市亚亲,隨后出現(xiàn)的幾起案子彻采,更是在濱河造成了極大的恐慌,老刑警劉巖朵栖,帶你破解...
    沈念sama閱讀 223,002評論 6 519
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件颊亮,死亡現(xiàn)場離奇詭異柴梆,居然都是意外死亡陨溅,警方通過查閱死者的電腦和手機(jī),發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 95,357評論 3 400
  • 文/潘曉璐 我一進(jìn)店門绍在,熙熙樓的掌柜王于貴愁眉苦臉地迎上來门扇,“玉大人,你說我怎么就攤上這事偿渡【始模” “怎么了?”我有些...
    開封第一講書人閱讀 169,787評論 0 365
  • 文/不壞的土叔 我叫張陵溜宽,是天一觀的道長吉拳。 經(jīng)常有香客問我,道長适揉,這世上最難降的妖魔是什么留攒? 我笑而不...
    開封第一講書人閱讀 60,237評論 1 300
  • 正文 為了忘掉前任煤惩,我火速辦了婚禮,結(jié)果婚禮上炼邀,老公的妹妹穿的比我還像新娘魄揉。我一直安慰自己,他們只是感情好拭宁,可當(dāng)我...
    茶點(diǎn)故事閱讀 69,237評論 6 398
  • 文/花漫 我一把揭開白布洛退。 她就那樣靜靜地躺著,像睡著了一般杰标。 火紅的嫁衣襯著肌膚如雪兵怯。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 52,821評論 1 314
  • 那天腔剂,我揣著相機(jī)與錄音摇零,去河邊找鬼。 笑死桶蝎,一個(gè)胖子當(dāng)著我的面吹牛驻仅,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播登渣,決...
    沈念sama閱讀 41,236評論 3 424
  • 文/蒼蘭香墨 我猛地睜開眼噪服,長吁一口氣:“原來是場噩夢啊……” “哼!你這毒婦竟也來了胜茧?” 一聲冷哼從身側(cè)響起粘优,我...
    開封第一講書人閱讀 40,196評論 0 277
  • 序言:老撾萬榮一對情侶失蹤,失蹤者是張志新(化名)和其女友劉穎呻顽,沒想到半個(gè)月后雹顺,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 46,716評論 1 320
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡廊遍,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 38,794評論 3 343
  • 正文 我和宋清朗相戀三年嬉愧,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片喉前。...
    茶點(diǎn)故事閱讀 40,928評論 1 353
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡没酣,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出卵迂,到底是詐尸還是另有隱情裕便,我是刑警寧澤,帶...
    沈念sama閱讀 36,583評論 5 351
  • 正文 年R本政府宣布见咒,位于F島的核電站偿衰,受9級特大地震影響,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜下翎,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 42,264評論 3 336
  • 文/蒙蒙 一囱嫩、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧漏设,春花似錦墨闲、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 32,755評論 0 25
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至犬性,卻和暖如春瞻离,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背乒裆。 一陣腳步聲響...
    開封第一講書人閱讀 33,869評論 1 274
  • 我被黑心中介騙來泰國打工套利, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人鹤耍。 一個(gè)月前我還...
    沈念sama閱讀 49,378評論 3 379
  • 正文 我出身青樓肉迫,卻偏偏與公主長得像,于是被迫代替她去往敵國和親稿黄。 傳聞我的和親對象是個(gè)殘疾皇子喊衫,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 45,937評論 2 361

推薦閱讀更多精彩內(nèi)容