面試官：MySQL索引底層數據結構原理與性能調優，你能回答多少？

哈嘍！大家好，我是小奇，一位不靠譜的程序員

小奇打算以輕松幽默的對話方式來分享一些技術，如果你覺得通過小奇的文章學到了東西，那就給小奇一個贊吧

文章持續更新，可以微信搜索【小奇JAVA面試】第一時間閱讀，回復【資料】更有我為大家準備的福利喲！

前言

作為一名Java程序員，Mysql底層的一些原理是我們不必學會就可以搬磚工作的一種技能點，但是小奇為什么還要講一下呢？難道就是為了浪費大家1分鐘的寶貴時間，一個人1分鐘，50萬人就是1年，5000萬人就是100年，賺了，小奇以一己之力成功搞掛一個人（血賺）。

當然不是，并且小奇的文章也沒有那么多人看，最多也就浪費個腎吧。

學習MySQL底層原理是因為面試官要問啊！，所以我們就要學，什么？不實用的你不學？那鄰居小奇可要使勁學啦，到時候面試官只要小奇不要你。

至于你問為什么面試官要問MySQL底層原理呢，這個。。。我把這次機會留給你，下次你面試的時候面試官問：“講一下MySQL底層原理”。你：“面試官你好，請問為什么你要問MySQL底層原理呢，你給我臺電腦，我五分鐘給你搭建好圖書管理系統他不香嗎，咱們鍵盤上見真章”。這時面試官就會告訴你答案，你就可以把答案打在評論區，讓小奇以及眾多小伙伴一起知道一下到底為什么要問？

面試

在一個晴朗的周日，我來到了一個陌生的園區（別問為什么是周日，問就是997，不過為了填飽肚子的打工人，只能明知山有虎、偏向虎山行），坐在陌生的會議室，等待HR小姐姐去叫面試官，此時我的心情和各位小伙伴一樣五味雜陳，擔心面試官問的會不會很難？問到我的知識盲區我該怎么辦？一會自我介紹的時候要不要吹一下我和小奇的關系？

一位英俊瀟灑，眼神犀利的面試官走了進來，看到他那犀利、仿佛能看穿一切的眼神 ，我在想要不然一會就不要20k了，要8k得了，這個面試官一看就不好糊弄啊，但是我想起來我來之前剛看了小奇的趣學編程系列，我已經完全學會了小奇的精髓，我頓時就來了底氣，決定一會要30k，不給就學小奇賴著不走（哈哈）

面試官：小奇是吧，帶簡歷了嗎？

我：沒帶，現在彩印兩塊一張，我簡歷五張，每次面試都要花費十塊，我朋友說了還沒工作就先讓你掏錢的工作不要去。

面試官：。。。那你靠什么來征服我，讓我錄用你

我：氣質？

（此時面試官并沒有叫保安，而是從門后拿出了恭候我多時的棍子，我瞬間慫了）

（我只好從我的雙肩包中拿出了我從上午沒有面試通過的其他公司面試官手中要回的簡歷，上午的情形是這樣的，上午的面試官：今天的面試就到這吧，回去等通知吧！我：面試官你好，如果貴公司不打算錄取我的話，能不能把我的紙質簡歷還給我，我下午還有一家面試。上午的面試官：我說你的簡歷怎么皺皺巴巴，原來你一直在循環利用啊！這個癥狀出現多久了？我：半拉月了。。。）

（當我把皺皺巴巴的簡歷交給面試官后，這場面試才得以繼續進行。。。）

MySQL索引底層數據結構

面試官：我看你簡歷上寫的精通MySQL？（哼，面試官輕蔑的一笑）

（看著面試官輕蔑的笑容，我忍不住拿出了我的MySQL入門書籍推給了他）

我：這本書我倒背如流，你隨便提問，答不上來算我輸，答上來你就要為你的輕蔑向我道歉。

（我的笑容逐漸自信。。。）

（此時面試官笑的更大聲了，完全不在意我就坐在對面）

面試官：哈哈哈、你這本書都寫了MySQL入門了，你還敢說你精通MySQL，我隨便問你一個問題就把你問住了，因為我問的問題都是你這本書上沒有的

我：那你問吧，是騾子是馬咱拉出來溜溜。

面試官：好，小伙子還挺硬氣，那你說說MySQL索引的底層數據結構吧

我：MySQL索引的底層數據結構是B+樹數據結構（這有何難。。。）

面試官：完了？詳細介紹一下B+樹的數據結構是什么樣子的，不然我怎么知道你真懂假懂

我：B+樹有三個特性

1、B+樹是一個平衡多叉樹，與平衡二叉樹的每一個節點下面最多有兩個子節點相比B+樹每一個節點下面有多個子節點。

2、B+樹葉子節點（也就是最下面一層的沒有子節點的節點）有一個雙向鏈表，左右是為了方便范圍查找（假如我找前100條數據，那么我找到第一條葉子節點的數據就可以從葉子節點直接向后取100個數據即可，不用再從根節點向下尋找）

3、B+樹的葉子節點有data數據（就是數據庫中這一條所有的字段數據），非葉子節點只有索引數據。

面試官：嗯，那你說一下B樹和B+樹的區別，為什么MySQL底層使用B+樹而不使用B樹呢

我：（很明顯啊！B+比B多一個+啊，年底了能拿A+的誰愛拿A呢，這一題過。。。）

我們先來看一下B樹的一個數據結構

很明顯B樹與B+樹有兩個地方不同，一個是葉子節點的雙向鏈表，一個是B樹不是只有葉子節點有data數據，而是所有的節點都有data數據。

面試官：嗯。那為什么不用二叉樹作為索引的底層結構而用B+樹呢

我：因為二叉樹的特性造成根節點距離葉子節點的路徑太長，假如一個7個節點的數據二叉樹從根節點到葉子節點的距離為三。

如果用B+樹則距離為1就可以搞定（當然B+樹一層不止7個節點，節點數量取決于一頁數據能存放多少個節點）

面試官：嗯，每一個節點都有data數據不是更好嗎，不需要到達葉子節點就可以獲取數據返回了，為什么B+樹還要把其他節點的data數據去掉，只留葉子節點的data數據呢

我：因為這里涉及到計算機中的IO操作，計算機IO一次只能拿一數據頁的數據（姑且認為大小為64KB吧），如果每一個節點都有data數據，那么計算機IO一次可能只夠拿一個節點出來，這樣，可能IO一百次才能找到結果，如果其他節點不存儲data數據，那么這個索引占用空間就少，IO一個可以拿出多個節點來，這樣IO的次數就大大降低了，IO一次是比較耗費性能的，所以使用B+樹就提高了性能。

面試官：可以啊小伙子，有點東西，平時都怎么學習呀，回答的這么全面

我：平時都是看看小奇的《趣學編程》系列文章，文章簡答又有趣，利用閑暇時間就慢慢得到了升華（此時真想給小奇的文章點個贊，拒絕白嫖哦，不就很壞~~）

面試官：嗯。那你簡單說一下聚集索引和非聚集索引是什么意思

我：那我就由淺入深的簡單說一下吧。

聚集索引：首先所謂聚集的含義是索引與data數據是否相鄰，就是我找到索引以后在它附近就可以找到想要的data數據這就是聚集索引。

非聚集索引：非聚集索引就是我找到索引后，在它的附近找不到data數據。

這里我們根據之前的圖想一下，之前的圖葉子節點下面緊挨著就是data數據，這里肯定是聚集索引啊，那么什么情況下是非聚集索引呢。

在索引的字段是非主鍵的時候就是非聚集索引。

這里我來舉個例子，如果一個student表中有主鍵id，姓名name，年齡age，住址add。這個時候我們給name字段建立一個索引，給add字段建立了一個索引，那么這個時候是不是有兩個B+樹的索引結構，那么意味著這兩個索引結構的葉子節點都需要有data數據，那豈不是需要將name索引中的data數據復制一份出來給add索引。

那假如有100個字段都建立了索引，豈不是data要復制100遍。這個時候我就想了一個辦法（這個辦法不是我想的。。。我快要想出來了，被別人提前答出來了）能不能只讓一個索引的子節點有data數據，其他索引的子節點沒有data數據而是放有data數據的索引的地址呢。

這個時候就讓哪個索引作為唯一擁有data數據的索引呢，這里很明顯可以用主鍵嘛，因為主鍵正好是唯一的，其他字段都可以為多個，所以主鍵所建立的索引就是擁有data數據的聚集索引，而其他非主鍵字段建立的索引就是非聚集索引。

MySQL索引優化

面試官：非常棒，看來小奇的文章真不錯呀，接下來講一講MySQL語句怎么寫可以提高性能呢

我：當然是建立索引啦，建立了索引猶如給書加上了目錄，如魚得水、如虎添翼、如。。。

面試官：那索引是不是建立的越多越好呢

我：當然不是。。。

面試官：為什么？

我：（因為一般面試官用這種口氣問問題就是給你下套呢，答肯定不是就對了。。。機智如我）

因為每一個索引就是給索引字段建立一個索引結構，假如現在插入一條數據，那么這條數據也需要將字段建立到索引結構當中，就需要調整索引結構了，如果建立了100個索引，那么插入1條數據需要調整100個索引結構（數據庫：我去你。的，瞎雞。搞），那么性能就可想而知了。

但是不建立索引又不行，必須還得建立，那么應該怎么建立呢，就是把經常要用到的查詢條件的字段建立一個聯合索引，這樣用一個索引樹可以將多個字段建立了索引。

面試官：嗯。說到了聯合索引，如果我建立的聯合索引是A、B、C這三個字段，那么我查詢的時候條件是A、B那么這個索引還有效果嗎？

我：有效果。

面試官：為什么呢？

我：因為最左前綴原則，假如當我們給姓名、年齡、性別三個字段建立了索引，那么從左邊先開始的字段才可以索引有效果。

面試官：那如果我查A和C呢？

我：那么只有A有索引效果，查詢的字段從索引的最左邊開始向右查找，如果中間斷了，那么后面的索引字段就失去效果了。

MySQL索引覆蓋

面試官：嗯。講一下MySQL的索引覆蓋是怎么回事吧

我：所謂索引覆蓋就是用索引字段來覆蓋要查詢的字段。

假如我們要查詢兩個字段，name和age，我們的sql語句為

select name,age from student where name=‘張三’ and age=20

假如我們這個時候只有name建立了索引，這個時候我們需要在索引中找到name等于張三的這些數據，并回表（就是從普通索引中找不全所要查詢的所有字段，那么需要回表再去主鍵聚簇索引中尋找，因為聚簇索引中有全量的data數據）。

這個時候我們可以看到我們需要查詢的字段只有name，age兩個字段，這個時候我們可以將name，age這兩個字段做一個聯合索引，這個時候我們直接通過聯合索引就可以找到所要查詢出的字段了。

請注意如果是 select name,age,add from student where name=‘張三’ and age=20；這個時候由于剛剛的聯合索引只有name，age兩個字段，沒有add字段，所以這種情況又要回表查詢，這種情況就沒有索引覆蓋了。

所以我們sql語句要盡可能的查詢出少量的字段，就是用哪個字段就查詢哪個字段，更要避免select * 的這種情況。

MySQL索引下推

面試官：嗯。非常不錯，那你再說一下什么是索引下推吧

我：（我特么。。。精通MySQL就這么被問啊，早知道不寫精通MySQL了，累死我了。。。可以來個贊給我續續命嗎家人們）

首先索引下推是MySQL5.6版本引入的一種優化手段，說白了就是優化了一下，具體優化后有了哪些效果呢，切聽帥氣的小奇給你娓娓道來。

關鍵點：

1、第一個字段為非等值字段。

2、查詢的字段建立了聯合索引。

前提：

name和age建立了聯合索引

例如 select name,age from student where name like ‘%李*%’ and age=20；

這個時候如果在5.6之前，我們會在聯合索引中先找到所有name為李開頭的數據id（主鍵），然后再去主鍵索引（聚集索引）中找age為20的數據的id拿回來，然后將最后合并的數據根據id再去聚集索引中找，這樣其實是兩次回表查詢。

而在5.6之后，我們在聯合索引中就直接將name為李開頭的和age等于20的數據id篩選出來了，然后再去聚集索引中查詢，這樣就只進行了一次回表查詢。

總結：5.6之前如果查詢字段為非等值字段，那么后面的查詢條件就回去聚集索引中進行判斷，5.6之后非等值字段后面的查詢條件在當前非聚集索引中也可以進行判斷。

MySQL索引失效

面試官：嗯。非常不錯，那你能說一下索引在什么情況下會失效嗎？

我：在特么沒有建立索引的情況下會失效。。。

面試官：嗯。你等我找一下棍子

我：額，在如下這幾種情況下會失效。

1、在使用不等于！= 或者<> 這樣的會失效。

2、在使用不包含 not in ， 不存在 not exists 這樣的會失效。

3、在使用空 is null，不為空 is not null 這樣的會失效。

4、在使用小于 <、大于 >、<=、 >= 這些的時候，mysql優化器會根據索引比例、表的數據量大小等因素來決定走不走索引。

EXPLAIN

面試官：嗯。那我寫了一條sql，我怎么知道這條sql有沒有走索引呢

我：使用explain解釋器來查看，在sql語句前面加上explain就可以來查看

explain中有多列，我們直接來看type這一列，這一列表示訪問類型，即MySQL決定以哪種形式來查找表中的行，是根據索引還是全表掃描，表示查找數據行記錄的大概范圍。

type中的數據類型從優到差依次為：

system > const > eq_ref > ref > range > index >ALL

當我們寫了一條sql語句發現他的type是ALL的時候我們就要考慮一下怎么優化一下了，因為ALL是最差的，我們就需要琢磨一下怎么優化，當然優化到system是最好的，但是一般不會優化到這種程度，你只要前進一小步對于整個系統來說就是文明一大步。。。

MySQL事務隔離級別

面試官：可以可以，回答的不錯，不過你是精通MySQL，那我必須得全面的問你MySQL的相關知識，你再堅持堅持，和我再大戰個三百回合

我：（我特么謝謝您。。。）

面試官：數據庫有哪些事務隔離級別，MySQL使用了哪種級別呢？

我：數據庫有四種事務隔離級別

讀未提交

讀已提交

可重復讀

可串行化

MySQL默認是可重復讀事務隔離級別

面試官：嗯。那MySQL有哪些鎖呢？

我：從顆粒度來分，MySQL有表鎖和行鎖。

表鎖：每次操作鎖住整張表，開銷小，加鎖快；不會出現死鎖；鎖定力度大，發生鎖沖突的概率最低。

行鎖：每次操作鎖住一行數據。開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度最高。

MVCC機制

面試官：嗯。那你知道MVCC機制嗎，他的原理是什么？

我：（這特么好難講明白的。。。）那我就簡單的說一下。

MVCC（Multi-Version Concurrency Control）是多版本并發控制，是在多個事務情況下可以保證每個事物之間相互隔離，MVCC機制適用于讀已提交和可重復讀這兩個事務隔離級別。

MVCC機制中有最重要的兩部分：

undo日志版本鏈：在一行數據被多個事務依次修改過后，每次的修改記錄都會保存到undo日志版本鏈中，用于回滾操作。

一致性視圖：read-view：每一個事務開啟后，執行任何查詢sql時就會生成當前事務的一致性視圖。這個視圖是由查詢的時候所有未提交的事務id數組和已創建的最大事務id組成。

總結：MySQL通過是可重復讀的，所以當一個事務執行第一個查詢語句的時候就生成了一個一致性視圖，當第二個事務修改了這條數據夠，第一個事務查詢的還是原來的數據，因為第一個事務當時查詢的時候生成了一致性視圖，這個一致性視圖對于第一個事務來說沒有變化，所以查詢出來的數據也沒有變化。

面試官：小伙子真厲害啊，我這邊沒有什么要問的了，你還有什么問題要問（面試官兩眼放光）

我：額。。。面試官這個我的紙質簡歷可以給我嗎，可以不往我的簡歷上寫寫畫畫嗎，我明天的面試還要用。

面試官：還面啥別的公司啊，就來我這吧，條件隨便開

我：那就100k吧（此時面試官又拿起了他準備好的棍子）

面試官：你要是不來就給我推薦一下，讓別人來我這面試一下

我：你先好好學習一下MySQL吧，今天幸虧只是我來了，如果是小奇的忠實讀者來了，你將會被虐的很慘的。（我轉身留下了帥氣的背影，而面試官落寞無神的呆呆的坐在那里，仿佛一個億離他而去。。。）

總結

MySQL是及基礎又重要的一項技術，所以大家要后認真反復的去學習，如果覺得我的文章還不錯的話就點個贊吧，另外可以微信搜索【小奇JAVA面試】閱讀更多的好文章，獲取我為大家準備的資料。

MySQL 應用性能調優 數據結構

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。