Mysql索引面試題的小結

本文來自一位不愿意透露姓名的粉絲投稿,由Hollis整理并"還原"了面試現(xiàn)場。
相信很多人對于MySQL的索引都不陌生,索引(Index)是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結構。
因為索引是MySQL中比較重點的知識,相信很多人都有一定的了解,尤其是在面試中出現(xiàn)的頻率特別高。樓主自認為自己對MySQL的索引相關知識有很多了解,而且因為最近在找工作面試,所以單獨復習了很多關于索引的知識。
但是,我還是圖樣圖森破,直到我被阿里的面試官虐過之后我才知道,自己在索引方面的知識,只是個小學生水平。
以下,是我總結的一次阿里面試中關于索引有關的問題以及知識點。
1、索引概念、索引模型
我們是怎么聊到索引的呢,是因為我提到我們的業(yè)務量比較大,每天大概有幾百萬的新數(shù)據(jù)生成,于是有了以下對話:
Q:你們每天這么大的數(shù)據(jù)量,都是保存在關系型數(shù)據(jù)庫中嗎?
A:是的,我們線上使用的是MySQL數(shù)據(jù)庫
Q:每天幾百萬數(shù)據(jù),一個月就是幾千萬了,那你們有沒有對于查詢做一些優(yōu)化呢?
A:我們在數(shù)據(jù)庫中創(chuàng)建了一些索引(我現(xiàn)在非常后悔我當時說了這句話)
這里可以看到,阿里的面試官并不會像有一些公司一樣拿著題庫一道一道的問,而是會根據(jù)面試者做過的事情以及面試過程中的一些內容進行展開。
Q:那你能說說什么是索引嗎?
A:(這道題肯定難不住我啊)索引其實是一種數(shù)據(jù)結構,能夠幫助我們快速的檢索數(shù)據(jù)庫中的數(shù)據(jù)
Q:那么索引具體采用的哪種數(shù)據(jù)結構呢?
A:(這道題我也背過)常見的MySQL主要有兩種結構:Hash索引和B+ Tree索引,我們使用的是InnoDB引擎,默認的是B+樹
這里我耍了一個小心機,特意說了一下索引和存儲引擎有關。希望面試官可以問我一些關于存儲引擎的問題。然而面試官并沒有被我?guī)?..
Q:既然你提到InnoDB使用的B+ 樹的索引模型,那么你知道為什么采用B+ 樹嗎?這和Hash索引比較起來有什么優(yōu)缺點嗎?
A:(突然覺得這道題有點難,但是我還是憑借著自己的知識儲備簡單的回答上一些)因為Hash索引底層是哈希表,哈希表是一種以key-value存儲數(shù)據(jù)的結構,所以多個數(shù)據(jù)在存儲關系上是完全沒有任何順序關系的,所以,對于區(qū)間查詢是無法直接通過索引查詢的,就需要全表掃描。所以,哈希索引只適用于等值查詢的場景。而B+ 樹是一種多路平衡查詢樹,所以他的節(jié)點是天然有序的(左子節(jié)點小于父節(jié)點、父節(jié)點小于右子節(jié)點),所以對于范圍查詢的時候不需要做全表掃描
Q:除了上面這個范圍查詢的,你還能說出其他的一些區(qū)別嗎?
A:(這個題我回答的不好,事后百度了一下)
B+ Tree索引和Hash索引區(qū)別?
哈希索引適合等值查詢,但是無法進行范圍查詢
哈希索引沒辦法利用索引完成排序
哈希索引不支持多列聯(lián)合索引的最左匹配規(guī)則
如果有大量重復鍵值的情況下,哈希索引的效率會很低,因為存在哈希碰撞問題
2、聚簇索引、覆蓋索引
Q:剛剛我們聊到B+ Tree ,那你知道B+ Tree的葉子節(jié)點都可以存哪些東西嗎?
A:InnoDB的B+ Tree可能存儲的是整行數(shù)據(jù),也有可能是主鍵的值
Q:那這兩者有什么區(qū)別嗎?
A:(當他問我葉子節(jié)點的時候,其實我就猜到他可能要問我聚簇索引和非聚簇索引了)在 InnoDB 里,索引B+ Tree的葉子節(jié)點存儲了整行數(shù)據(jù)的是主鍵索引,也被稱之為聚簇索引。而索引B+ Tree的葉子節(jié)點存儲了主鍵的值的是非主鍵索引,也被稱之為非聚簇索引
Q:那么,聚簇索引和非聚簇索引,在查詢數(shù)據(jù)的時候有區(qū)別嗎?
A:聚簇索引查詢會更快?
Q:為什么呢?
A:因為主鍵索引樹的葉子節(jié)點直接就是我們要查詢的整行數(shù)據(jù)了。而非主鍵索引的葉子節(jié)點是主鍵的值,查到主鍵的值以后,還需要再通過主鍵的值再進行一次查詢
Q:剛剛你提到主鍵索引查詢只會查一次,而非主鍵索引需要回表查詢多次。(后來我才知道,原來這個過程叫做回表)是所有情況都是這樣的嗎?非主鍵索引一定會查詢多次嗎?
A:(額、這個問題我回答的不好,后來我自己查資料才知道,通過覆蓋索引也可以只查詢一次)
覆蓋索引?
覆蓋索引(covering index)指一個查詢語句的執(zhí)行只用從索引中就能夠取得,不必從數(shù)據(jù)表中讀取。也可以稱之為實現(xiàn)了索引覆蓋。
當一條查詢語句符合覆蓋索引條件時,MySQL只需要通過索引就可以返回查詢所需要的數(shù)據(jù),這樣避免了查到索引后再返回表操作,減少I/O提高效率。
如,表covering_index_sample中有一個普通索引 idx_key1_key2(key1,key2)。
當我們通過SQL語句:select key2 from covering_index_sample where key1 = 'keytest';的時候,就可以通過覆蓋索引查詢,無需回表。
3、聯(lián)合索引、最左前綴匹配
Q:不知道的話沒關系,想問一下,你們在創(chuàng)建索引的時候都會考慮哪些因素呢?
A:我們一般對于查詢概率比較高,經(jīng)常作為where條件的字段設置索引
Q: 那你們有用過聯(lián)合索引嗎?
A:用過呀,我們有對一些表中創(chuàng)建過聯(lián)合索引
Q:那你們在創(chuàng)建聯(lián)合索引的時候,需要做聯(lián)合索引多個字段之間順序你們是如何選擇的呢?
A:我們把識別度最高的字段放到最前面
Q:為什么這么做呢?
A:(這個問題有點把我問蒙了,稍微有些慌亂)這樣的話可能命中率會高一點吧。。。
Q: 那你知道最左前綴匹配嗎?
A:(我突然想起來原來面試官是想問這個,怪自己剛剛為什么就沒想到這個呢。)哦哦哦。您剛剛問的是這個意思啊,在創(chuàng)建多列索引時,我們根據(jù)業(yè)務需求,where子句中使用最頻繁的一列放在最左邊,因為MySQL索引查詢會遵循最左前綴匹配的原則,即最左優(yōu)先,在檢索數(shù)據(jù)時從聯(lián)合索引的最左邊開始匹配。所以當我們創(chuàng)建一個聯(lián)合索引的時候,如(key1,key2,key3),相當于創(chuàng)建了(key1)、(key1,key2)和(key1,key2,key3)三個索引,這就是最左匹配原則
雖然我一開始有點懵,沒有聯(lián)想到最左前綴匹配,但是面試官還是引導了我。很友善。
4、索引下推、查詢優(yōu)化
Q:你們線上用的MySQL是哪個版本啊呢?
A:我們MySQL是5.7
Q:那你知道在MySQL 5.6中,對索引做了哪些優(yōu)化嗎?
A:不好意思,這個我沒有去了解過。(事后我查了一下,有一個比較重要的 :Index Condition Pushdown Optimization)
Index Condition Pushdown(索引下推)
MySQL 5.6引入了索引下推優(yōu)化,默認開啟,使用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%';
如果沒有使用索引下推技術,則MySQL會通過zipcode='95054'從存儲引擎中查詢對應的數(shù)據(jù),返回到MySQL服務端,然后MySQL服務端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'來判斷數(shù)據(jù)是否符合條件。
如果使用了索引下推技術,則MYSQL首先會返回符合zipcode='95054'的索引,然后根據(jù)lastname LIKE '%etrunia%'和address LIKE '%Main Street%'來判斷索引是否符合條件。如果符合條件,則根據(jù)該索引來定位對應的數(shù)據(jù),如果不符合,則直接reject掉。有了索引下推優(yōu)化,可以在有l(wèi)ike條件查詢的情況下,減少回表次數(shù)。
Q:你們創(chuàng)建的那么多索引,到底有沒有生效,或者說你們的SQL語句有沒有使用索引查詢你們有統(tǒng)計過嗎?
A:這個還沒有統(tǒng)計過,除非遇到慢SQL的時候我們才會去排查
Q:那排查的時候,有什么手段可以知道有沒有走索引查詢呢?
A:可以通過explain查看sql語句的執(zhí)行計劃,通過執(zhí)行計劃來分析索引使用情況
Q:那什么情況下會發(fā)生明明創(chuàng)建了索引,但是執(zhí)行的時候并沒有通過索引呢?
A:(大概記得和優(yōu)化器有關,但是這個問題并沒有回答好)
查詢優(yōu)化器?
一條SQL語句的查詢,可以有不同的執(zhí)行方案,至于最終選擇哪種方案,需要通過優(yōu)化器進行選擇,選擇執(zhí)行成本最低的方案。
在一條單表查詢語句真正執(zhí)行之前,MySQL的查詢優(yōu)化器會找出執(zhí)行該語句所有可能使用的方案,對比之后找出成本最低的方案。
這個成本最低的方案就是所謂的執(zhí)行計劃。優(yōu)化過程大致如下:
1、根據(jù)搜索條件,找出所有可能使用的索引
2、計算全表掃描的代價
3、計算使用不同索引執(zhí)行查詢的代價
4、對比各種執(zhí)行方案的代價,找出成本最低的那一個
Q:哦,索引有關的知識我們暫時就問這么多吧。你們線上數(shù)據(jù)的事務隔離級別是什么呀?
A:(后面關于事務隔離級別的問題了,就不展開了)
感覺是因為我回答的不夠好,如果這幾個索引問題我都會的話,他還會追問更多,恐怕會被虐的更慘
5、總結&感悟
以上,就是一次面試中關于索引部分知識的問題以及我整理的答案。感覺這次面試過程中關于索引的知識,自己大概能夠回答的內容占70%左右,但是自信完全答對的內容只占50%左右,看來自己索引有關的知識了解的還是不夠多。
通過這次面試,發(fā)現(xiàn)像阿里這種大廠對于底層知識還是比較看重的,我以前以為關于索引最多也就問一下Hash和B+有什么區(qū)別,沒想到最后都能問到查詢優(yōu)化器上面。
最后,不管本次面試能不能通過,都非常感謝有這樣一次機會,可以讓自己看到自己的不足。通過這次面試,我也收獲了很多東西。加油!
參考資料:
極客時間 -《MySQL實戰(zhàn)45講》
掘金小冊 -《MySQL 是怎樣運行的:從根兒上理解 MySQL》
博文視點 -《高性能MySQL》
到此這篇關于Mysql索引面試題的小結的文章就介紹到這了,更多相關Mysql索引面試題內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章,希望大家以后多多支持腳本之家!
相關文章
- 這篇文章主要介紹了mysql常見筆試題小結,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧2020-08-19
- 這篇文章主要介紹了MySQL常見面試題與答案整理,小編覺得挺不錯的,現(xiàn)在分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2020-04-17
- 這篇文章主要介紹了Mysql版sql語句練習50題,小編覺得挺不錯的,現(xiàn)在分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2020-04-01
- 這篇文章主要介紹了MySQL常見面試題(小結),小編覺得挺不錯的,現(xiàn)在分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2020-02-17
- 這篇文章主要介紹了去BAT面試完的Mysql面試題總結,小編覺得挺不錯的,現(xiàn)在分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2019-12-19
- 這篇文章主要介紹了100道MySQL常見面試題總結,本文主要受眾為開發(fā)人員,所以不涉及到MySQL的服務部署等操作,且內容較多,感興趣的可以了解一下2019-08-22
- 這篇文章主要介紹了20個MySQL經(jīng)典面試題的相關資料,并在下面整理好了答案,方便使用,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習2019-04-10
- 這篇文章主要介紹了MySQL數(shù)據(jù)庫選擇題小結,小編覺得挺不錯的,現(xiàn)在分享給大家,也給大家做個參考。一起跟隨小編過來看看吧2021-02-07