哈嘍！大家好屹堰，我是小奇肛冶，一位不靠譜的程序員
小奇打算以輕松幽默的對話方式來分享一些技術，如果你覺得通過小奇的文章學到了東西扯键，那就給小奇一個贊吧
文章持續(xù)更新睦袖，可以微信搜索【小奇JAVA面試】第一時間閱讀，回復【資料】更有我為大家準備的福利喲荣刑！
文章目錄
MySQL索引底層數(shù)據(jù)結(jié)構(gòu)
作為一名Java程序員馅笙，MySQL底層的一些原理是我們不必學會就可以搬磚工作的一種技能點伦乔，但是小奇為什么還要講一下呢？難道就是為了浪費大家1分鐘的寶貴時間董习，一個人1分鐘烈和，50萬人就是1年，5000萬人就是100年皿淋，賺了招刹，小奇以一己之力成功搞掛一個人（血賺）。
當然不是窝趣，并且小奇的文章也沒有那么多人看疯暑，最多也就浪費個腎吧。
學習MySQL底層原理是因為面試官要問啊哑舒！妇拯，所以我們就要學，什么洗鸵？不實用的你不學越锈？那鄰居小奇可要使勁學啦，到時候面試官只要小奇不要你膘滨。
至于你問為什么面試官要問MySQL底層原理呢甘凭，這個。火邓。对蒲。我把這次機會留給你，下次你面試的時候面試官問：“講一下MySQL底層原理”贡翘。你：“面試官你好，請問為什么你要問MySQL底層原理呢砰逻，你給我臺電腦鸣驱，我五分鐘給你搭建好圖書管理系統(tǒng)他不香嗎，咱們鍵盤上見真章”蝠咆。這時面試官就會告訴你答案踊东，你就可以把答案打在評論區(qū)，讓小奇以及眾多小伙伴一起知道一下到底為什么要問刚操？
在一個晴朗的周日闸翅，我來到了一個陌生的園區(qū)（別問為什么是周日，問就是997菊霜，不過為了填飽肚子的打工人坚冀，只能明知山有虎、偏向虎山行）鉴逞，坐在陌生的會議室记某，等待HR小姐姐去叫面試官司训，此時我的心情和各位小伙伴一樣五味雜陳，擔心面試官問的會不會很難液南？問到我的知識盲區(qū)我該怎么辦壳猜？一會自我介紹的時候要不要吹一下我和小奇的關系？
一位英俊瀟灑滑凉，眼神犀利的面試官走了進來统扳，看到他那犀利、仿佛能看穿一切的眼神 畅姊，我在想要不然一會就不要20k了咒钟，要8k得了，這個面試官一看就不好糊弄啊涡匀，但是我想起來我來之前剛看了小奇的趣學編程系列盯腌，我已經(jīng)完全學會了小奇的精髓，我頓時就來了底氣陨瘩，決定一會要30k腕够，不給就學小奇賴著不走（哈哈）
面試官：小奇是吧，帶簡歷了嗎舌劳？
我：沒帶帚湘，現(xiàn)在彩印兩塊一張，我簡歷五張甚淡，每次面試都要花費十塊大诸，我朋友說了還沒工作就先讓你掏錢的工作不要去。
面試官：贯卦。资柔。。那你靠什么來征服我撵割，讓我錄用你
我：氣質(zhì)贿堰？
（此時面試官并沒有叫保安，而是從門后拿出了恭候我多時的棍子啡彬，我瞬間慫了）
（我只好從我的雙肩包中拿出了我從上午沒有面試通過的其他公司面試官手中要回的簡歷羹与，上午的情形是這樣的，上午的面試官：今天的面試就到這吧庶灿，回去等通知吧纵搁！我：面試官你好，如果貴公司不打算錄取我的話往踢，能不能把我的紙質(zhì)簡歷還給我腾誉，我下午還有一家面試。上午的面試官：我說你的簡歷怎么皺皺巴巴，原來你一直在循環(huán)利用巴纭惑灵！這個癥狀出現(xiàn)多久了？我：半拉月了眼耀。英支。。）
（當我把皺皺巴巴的簡歷交給面試官后哮伟，這場面試才得以繼續(xù)進行干花。。楞黄。）
MySQL索引底層數(shù)據(jù)結(jié)構(gòu)
面試官：我看你簡歷上寫的精通MySQL池凄？（哼，面試官輕蔑的一笑）
（看著面試官輕蔑的笑容鬼廓，我忍不住拿出了我的MySQL入門書籍推給了他）
我：這本書我倒背如流肿仑，你隨便提問，答不上來算我輸碎税，答上來你就要為你的輕蔑向我道歉尤慰。
（我的笑容逐漸自信。雷蹂。伟端。）
（此時面試官笑的更大聲了，完全不在意我就坐在對面）
面試官：哈哈哈匪煌、你這本書都寫了MySQL入門了责蝠，你還敢說你精通MySQL，我隨便問你一個問題就把你問住了萎庭，因為我問的問題都是你這本書上沒有的
我：那你問吧霜医，是騾子是馬咱拉出來溜溜。
面試官：好驳规，小伙子還挺硬氣支子，那你說說MySQL索引的底層數(shù)據(jù)結(jié)構(gòu)吧
我：MySQL索引的底層數(shù)據(jù)結(jié)構(gòu)是B+樹數(shù)據(jù)結(jié)構(gòu)（這有何難。达舒。。）
面試官：完了叹侄？詳細介紹一下B+樹的數(shù)據(jù)結(jié)構(gòu)是什么樣子的巩搏，不然我怎么知道你真懂假懂
我：B+樹有三個特性
1、B+樹是一個平衡多叉樹趾代，與平衡二叉樹的每一個節(jié)點下面最多有兩個子節(jié)點相比B+樹每一個節(jié)點下面有多個子節(jié)點贯底。
2、B+樹葉子節(jié)點（也就是最下面一層的沒有子節(jié)點的節(jié)點）有一個雙向鏈表，左右是為了方便范圍查找（假如我找前100條數(shù)據(jù)禽捆，那么我找到第一條葉子節(jié)點的數(shù)據(jù)就可以從葉子節(jié)點直接向后取100個數(shù)據(jù)即可笙什，不用再從根節(jié)點向下尋找）
3、B+樹的葉子節(jié)點有data數(shù)據(jù)（就是數(shù)據(jù)庫中這一條所有的字段數(shù)據(jù)）胚想，非葉子節(jié)點只有索引數(shù)據(jù)琐凭。
面試官：嗯，那你說一下B樹和B+樹的區(qū)別浊服，為什么MySQL底層使用B+樹而不使用B樹呢
我：（很明顯巴城！B+比B多一個+啊牙躺，年底了能拿A+的誰愛拿A呢愁憔，這一題過。孽拷。吨掌。）
我們先來看一下B樹的一個數(shù)據(jù)結(jié)構(gòu)
很明顯B樹與B+樹有兩個地方不同，一個是葉子節(jié)點的雙向鏈表脓恕，一個是B樹不是只有葉子節(jié)點有data數(shù)據(jù)膜宋，而是所有的節(jié)點都有data數(shù)據(jù)。
面試官：嗯进肯。那為什么不用二叉樹作為索引的底層結(jié)構(gòu)而用B+樹呢
我：因為二叉樹的特性造成根節(jié)點距離葉子節(jié)點的路徑太長激蹲，假如一個7個節(jié)點的數(shù)據(jù)二叉樹從根節(jié)點到葉子節(jié)點的距離為三。
如果用B+樹則距離為1就可以搞定（當然B+樹一層不止7個節(jié)點江掩，節(jié)點數(shù)量取決于一頁數(shù)據(jù)能存放多少個節(jié)點）
面試官：嗯学辱，每一個節(jié)點都有data數(shù)據(jù)不是更好嗎，不需要到達葉子節(jié)點就可以獲取數(shù)據(jù)返回了环形，為什么B+樹還要把其他節(jié)點的data數(shù)據(jù)去掉策泣，只留葉子節(jié)點的data數(shù)據(jù)呢
我：因為這里涉及到計算機中的IO操作，計算機IO一次只能拿一數(shù)據(jù)頁的數(shù)據(jù)（姑且認為大小為64KB吧）抬吟，如果每一個節(jié)點都有data數(shù)據(jù)萨咕，那么計算機IO一次可能只夠拿一個節(jié)點出來，這樣火本，可能IO一百次才能找到結(jié)果危队，如果其他節(jié)點不存儲data數(shù)據(jù)，那么這個索引占用空間就少钙畔，IO一個可以拿出多個節(jié)點來茫陆，這樣IO的次數(shù)就大大降低了，IO一次是比較耗費性能的擎析，所以使用B+樹就提高了性能簿盅。
面試官：可以啊小伙子，有點東西，平時都怎么學習呀桨醋，回答的這么全面
我：平時都是看看小奇的《趣學編程》系列文章棚瘟，文章簡答又有趣，利用閑暇時間就慢慢得到了升華（此時真想給小奇的文章點個贊喜最，拒絕白嫖哦偎蘸，不點贊就很壞~~）
面試官：嗯。那你簡單說一下聚集索引和非聚集索引是什么意思
我：那我就由淺入深的簡單說一下吧返顺。
聚集索引：首先所謂聚集的含義是索引與data數(shù)據(jù)是否相鄰禀苦，就是我找到索引以后在它附近就可以找到想要的data數(shù)據(jù)這就是聚集索引。
非聚集索引：非聚集索引就是我找到索引后遂鹊，在它的附近找不到data數(shù)據(jù)振乏。
這里我們根據(jù)之前的圖想一下，之前的圖葉子節(jié)點下面緊挨著就是data數(shù)據(jù)秉扑，這里肯定是聚集索引啊慧邮，那么什么情況下是非聚集索引呢。
在索引的字段是非主鍵的時候就是非聚集索引舟陆。
這里我來舉個例子误澳，如果一個student表中有主鍵id，姓名name秦躯，年齡age忆谓，住址add。這個時候我們給name字段建立一個索引踱承，給add字段建立了一個索引倡缠，那么這個時候是不是有兩個B+樹的索引結(jié)構(gòu)，那么意味著這兩個索引結(jié)構(gòu)的葉子節(jié)點都需要有data數(shù)據(jù)茎活，那豈不是需要將name索引中的data數(shù)據(jù)復制一份出來給add索引昙沦。
那假如有100個字段都建立了索引，豈不是data要復制100遍载荔。這個時候我就想了一個辦法（這個辦法不是我想的盾饮。。懒熙。我快要想出來了丘损，被別人提前答出來了）能不能只讓一個索引的子節(jié)點有data數(shù)據(jù)，其他索引的子節(jié)點沒有data數(shù)據(jù)而是放有data數(shù)據(jù)的索引的地址呢工扎。
這個時候就讓哪個索引作為唯一擁有data數(shù)據(jù)的索引呢号俐，這里很明顯可以用主鍵嘛，因為主鍵正好是唯一的定庵，其他字段都可以為多個，所以主鍵所建立的索引就是擁有data數(shù)據(jù)的聚集索引，而其他非主鍵字段建立的索引就是非聚集索引蔬浙。
面試官：非常棒猪落，看來小奇的文章真不錯呀，接下來講一講MySQL語句怎么寫可以提高性能呢
我：當然是建立索引啦畴博，建立了索引猶如給書加上了目錄笨忌，如魚得水、如虎添翼俱病、如官疲。。亮隙。
面試官：那索引是不是建立的越多越好呢
我：當然不是途凫。。溢吻。
面試官：為什么维费？
我：（因為一般面試官用這種口氣問問題就是給你下套呢，答肯定不是就對了促王。犀盟。。機智如我）
因為每一個索引就是給索引字段建立一個索引結(jié)構(gòu)蝇狼，假如現(xiàn)在插入一條數(shù)據(jù)阅畴，那么這條數(shù)據(jù)也需要將字段建立到索引結(jié)構(gòu)當中旨枯，就需要調(diào)整索引結(jié)構(gòu)了扇商，如果建立了100個索引号醉，那么插入1條數(shù)據(jù)需要調(diào)整100個索引結(jié)構(gòu)（數(shù)據(jù)庫：我去你趁尼。的辈末，瞎雞巧涧。搞）泼疑，那么性能就可想而知了赘淮。
但是不建立索引又不行血公，必須還得建立昵仅，那么應該怎么建立呢，就是把經(jīng)常要用到的查詢條件的字段建立一個聯(lián)合索引累魔，這樣用一個索引樹可以將多個字段建立了索引摔笤。
面試官：嗯。說到了聯(lián)合索引垦写，如果我建立的聯(lián)合索引是A吕世、B、C這三個字段梯投，那么我查詢的時候條件是A命辖、B那么這個索引還有效果嗎况毅？
我：有效果。
面試官：為什么呢尔艇？
我：因為最左前綴原則尔许，假如當我們給姓名、年齡终娃、性別三個字段建立了索引味廊，那么從左邊先開始的字段才可以索引有效果。
面試官：那如果我查A和C呢棠耕？
我：那么只有A有索引效果余佛，查詢的字段從索引的最左邊開始向右查找，如果中間斷了窍荧，那么后面的索引字段就失去效果了辉巡。
面試官：嗯。講一下MySQL的索引覆蓋是怎么回事吧
我：所謂索引覆蓋就是用索引字段來覆蓋要查詢的字段搅荞。
假如我們要查詢兩個字段红氯，name和age，我們的sql語句為
select name,age from student where name=‘張三’ and age=20
假如我們這個時候只有name建立了索引咕痛，這個時候我們需要在索引中找到name等于張三的這些數(shù)據(jù)痢甘，并回表（就是從普通索引中找不全所要查詢的所有字段，那么需要回表再去主鍵聚簇索引中尋找茉贡，因為聚簇索引中有全量的data數(shù)據(jù)）塞栅。
這個時候我們可以看到我們需要查詢的字段只有name，age兩個字段腔丧，這個時候我們可以將name放椰，age這兩個字段做一個聯(lián)合索引，這個時候我們直接通過聯(lián)合索引就可以找到所要查詢出的字段了愉粤。
請注意如果是 select name,age,add from student where name=‘張三’ and age=20砾医；這個時候由于剛剛的聯(lián)合索引只有name，age兩個字段衣厘，沒有add字段如蚜，所以這種情況又要回表查詢，這種情況就沒有索引覆蓋了影暴。
所以我們sql語句要盡可能的查詢出少量的字段错邦，就是用哪個字段就查詢哪個字段，更要避免select * 的這種情況型宙。
面試官：嗯撬呢。非常不錯，那你再說一下什么是索引下推吧
我：（我特么妆兑。魂拦。毛仪。精通MySQL就這么被問啊，早知道不寫精通MySQL了芯勘，累死我了。借尿。茄靠。可以來個贊給我續(xù)續(xù)命嗎家人們）
首先索引下推是MySQL5.6版本引入的一種優(yōu)化手段，說白了就是優(yōu)化了一下，具體優(yōu)化后有了哪些效果呢，切聽帥氣的小奇給你娓娓道來疯搅。
關鍵點：
1秋泄、第一個字段為非等值字段。
2厉碟、查詢的字段建立了聯(lián)合索引款咖。
前提：
name和age建立了聯(lián)合索引
例如 select name,age from student where name like ‘%李*%’ and age=20跨新；
這個時候如果在5.6之前，我們會在聯(lián)合索引中先找到所有name為李開頭的數(shù)據(jù)id（主鍵），然后再去主鍵索引（聚集索引）中找age為20的數(shù)據(jù)的id拿回來，然后將最后合并的數(shù)據(jù)根據(jù)id再去聚集索引中找，這樣其實是兩次回表查詢。
而在5.6之后劲蜻，我們在聯(lián)合索引中就直接將name為李開頭的和age等于20的數(shù)據(jù)id篩選出來了，然后再去聚集索引中查詢身冬，這樣就只進行了一次回表查詢嘿歌。
總結(jié)：5.6之前如果查詢字段為非等值字段，那么后面的查詢條件就回去聚集索引中進行判斷呼盆，5.6之后非等值字段后面的查詢條件在當前非聚集索引中也可以進行判斷腿时。
面試官：嗯格了。非常不錯，那你能說一下索引在什么情況下會失效嗎徽鼎？
我：在特么沒有建立索引的情況下會失效盛末。。否淤。
面試官：嗯悄但。你等我找一下棍子
我：額，在如下這幾種情況下會失效石抡。
1檐嚣、在使用不等于！= 或者<> 這樣的會失效汁雷。
2净嘀、在使用不包含 not in 报咳， 不存在 not exists 這樣的會失效。
3挖藏、在使用空 is null暑刃，不為空 is not null 這樣的會失效。
4膜眠、在使用小于 <岩臣、大于 >、<=宵膨、 >= 這些的時候架谎，mysql優(yōu)化器會根據(jù)索引比例、表的數(shù)據(jù)量大小等因素來決定走不走索引辟躏。
面試官：嗯谷扣。那我寫了一條sql，我怎么知道這條sql有沒有走索引呢
我：使用explain解釋器來查看捎琐，在sql語句前面加上explain就可以來查看
explain中有多列会涎，我們直接來看type這一列，這一列表示訪問類型瑞凑，即MySQL決定以哪種形式來查找表中的行末秃，是根據(jù)索引還是全表掃描，表示查找數(shù)據(jù)行記錄的大概范圍籽御。
type中的數(shù)據(jù)類型從優(yōu)到差依次為：
system > const > eq_ref > ref > range > index >ALL
當我們寫了一條sql語句發(fā)現(xiàn)他的type是ALL的時候我們就要考慮一下怎么優(yōu)化一下了练慕，因為ALL是最差的，我們就需要琢磨一下怎么優(yōu)化技掏，當然優(yōu)化到system是最好的铃将，但是一般不會優(yōu)化到這種程度，你只要前進一小步對于整個系統(tǒng)來說就是文明一大步哑梳。麸塞。。
面試官：可以可以涧衙，回答的不錯哪工，不過你是精通MySQL，那我必須得全面的問你MySQL的相關知識弧哎，你再堅持堅持雁比，和我再大戰(zhàn)個三百回合
我：（我特么謝謝您。撤嫩。偎捎。）
面試官：數(shù)據(jù)庫有哪些事務隔離級別，MySQL使用了哪種級別呢？
我：數(shù)據(jù)庫有四種事務隔離級別
讀未提交
讀已提交
可重復讀
可串行化
MySQL默認是可重復讀事務隔離級別
面試官：嗯茴她。那MySQL有哪些鎖呢寻拂？
我：從顆粒度來分，MySQL有表鎖和行鎖丈牢。
表鎖：每次操作鎖住整張表祭钉，開銷小，加鎖快己沛；不會出現(xiàn)死鎖慌核；鎖定力度大，發(fā)生鎖沖突的概率最低申尼。
行鎖：每次操作鎖住一行數(shù)據(jù)垮卓。開銷大，加鎖慢师幕；會出現(xiàn)死鎖粟按；鎖定粒度最小，發(fā)生鎖沖突的概率最低霹粥，并發(fā)度最高钾怔。
面試官：嗯。那你知道MVCC機制嗎蒙挑，他的原理是什么？
我：（這特么好難講明白的愚臀。忆蚀。。）那我就簡單的說一下姑裂。
MVCC（Multi-Version Concurrency Control）是多版本并發(fā)控制馋袜，是在多個事務情況下可以保證每個事物之間相互隔離，MVCC機制適用于讀已提交和可重復讀這兩個事務隔離級別舶斧。
MVCC機制中有最重要的兩部分：
undo日志版本鏈：在一行數(shù)據(jù)被多個事務依次修改過后欣鳖，每次的修改記錄都會保存到undo日志版本鏈中，用于回滾操作茴厉。
一致性視圖：read-view：每一個事務開啟后泽台，執(zhí)行任何查詢sql時就會生成當前事務的一致性視圖。這個視圖是由查詢的時候所有未提交的事務id數(shù)組和已創(chuàng)建的最大事務id組成矾缓。
總結(jié)：MySQL通過是可重復讀的怀酷，所以當一個事務執(zhí)行第一個查詢語句的時候就生成了一個一致性視圖，當?shù)诙€事務修改了這條數(shù)據(jù)夠嗜闻，第一個事務查詢的還是原來的數(shù)據(jù)蜕依，因為第一個事務當時查詢的時候生成了一致性視圖，這個一致性視圖對于第一個事務來說沒有變化，所以查詢出來的數(shù)據(jù)也沒有變化样眠。
面試官：小伙子真厲害啊友瘤，我這邊沒有什么要問的了，你還有什么問題要問（面試官兩眼放光）
我：額檐束。辫秧。。面試官這個我的紙質(zhì)簡歷可以給我嗎厢塘，可以不往我的簡歷上寫寫畫畫嗎茶没，我明天的面試還要用。
面試官：還面啥別的公司啊晚碾，就來我這吧抓半，條件隨便開
我：那就100k吧（此時面試官又拿起了他準備好的棍子）
面試官：你要是不來就給我推薦一下，讓別人來我這面試一下
我：你先好好學習一下MySQL吧格嘁，今天幸虧只是我來了笛求，如果是小奇的忠實讀者來了，你將會被虐的很慘的糕簿。（我轉(zhuǎn)身留下了帥氣的背影探入，而面試官落寞無神的呆呆的坐在那里，仿佛一個億離他而去懂诗。蜂嗽。。）
MySQL是及基礎又重要的一項技術殃恒，所以大家要收藏后認真反復的去學習植旧，如果覺得我的文章還不錯的話就點個贊吧，另外可以微信搜索【小奇JAVA面試】閱讀更多的好文章离唐，獲取我為大家準備的資料病附。
