?我當(dāng)面試官，面你 MySQL 原理
時間：2021-11-08 17:01:46
手機看文章
掃描二維碼
隨時隨地手機看文章
[導(dǎo)讀]言歸正傳，之前MySQL基礎(chǔ)篇的知識點，小伙伴們有熟練掌握嗎？沒有的話趕緊來復(fù)習(xí)一下～我當(dāng)面試官，面你MySQL基礎(chǔ)覺得已經(jīng)沒有問題，我們就一起來進行第二部分的學(xué)習(xí)吧——MySQL原理篇?；A(chǔ)篇主要是側(cè)重基礎(chǔ)知識，原理篇是有一定基礎(chǔ)后的遞進，通過學(xué)習(xí)本篇，不僅可以進一步了解MyS...
言歸正傳，之前MySQL基礎(chǔ)篇的知識點，小伙伴們有熟練掌握嗎？沒有的話趕緊來復(fù)習(xí)一下～
- 我當(dāng)面試官，面你 MySQL 基礎(chǔ)
基礎(chǔ)篇主要是側(cè)重基礎(chǔ)知識，原理篇是有一定基礎(chǔ)后的遞進，通過學(xué)習(xí)本篇，不僅可以進一步了解MySQL的各項特性，還能為接下來的容災(zāi)調(diào)優(yōu)打下堅實的基礎(chǔ)。
現(xiàn)在，就讓我們繼續(xù)跟隨阿柴進行這場沉浸式面試吧。
ACID與隔離級別
那你先來說說MySQL的四種隔離級別吧。SQL標(biāo)準(zhǔn)定義了4類隔離級別，包括一些具體規(guī)則，用來限定事務(wù)之間的隔離性。
這四種級別分別是讀未提交、讀已提交、可重復(fù)讀、串型化。
讀未提交，顧名思義，就是可以讀到還沒有提交的數(shù)據(jù)；讀已提交會讀到其它事務(wù)已經(jīng)提交的數(shù)據(jù)；可重復(fù)讀確保了同一事務(wù)中，讀取同一條數(shù)據(jù)時，會看到同樣的數(shù)據(jù)行；串型化通過強制事務(wù)排序，使其不可能相互沖突。
重點介紹下Repeatable Read吧。Repeatable Read就是可重復(fù)讀。它確保了在同一事務(wù)中，讀取同一條數(shù)據(jù)時，會看到同樣的數(shù)據(jù)行。
它也是MyQL的默認(rèn)事務(wù)隔離級別，這種級別事務(wù)之間影響很小，通常已經(jīng)能夠滿足日常需要了。
說出四種隔離級別只是最低要求，能每一項具體去闡述特性就算過關(guān)。如果還能指出存在的問題、依賴的技術(shù)，那么就是妥妥的加分了！
下面我們來聊聊InnoDB中ACID的實現(xiàn)吧，先說一下原子性是怎么實現(xiàn)的。事務(wù)要么失敗，要么成功，不能做一半。聰明的InnoDB，在干活兒之前，先將要做的事情記錄到一個叫undo log的日志文件中，如果失敗了或者主動rollback，就可以通過undo log的內(nèi)容，將事務(wù)回滾。
那undo log里面具體記錄了什么信息呢？undo log屬于邏輯日志，它記錄的是sql執(zhí)行相關(guān)的信息。當(dāng)發(fā)生回滾時，InnoDB會根據(jù)undo log的內(nèi)容做與之前相反的工作，使數(shù)據(jù)回到之前的狀態(tài)。。。
那持久性又是怎么實現(xiàn)的？持久性是用來保證一旦給客戶返回成功，數(shù)據(jù)就不會消失，持久存在。最簡單的做法，是每次寫完磁盤落地之后，再給客戶返回成功。但如果每次讀寫數(shù)據(jù)都需要磁盤IO，效率就會很低。
為此，追求極致的InnoDB提供了緩沖。當(dāng)向數(shù)據(jù)庫寫入數(shù)據(jù)時，會首先寫入緩沖池，緩沖池中修改的數(shù)據(jù)會定期刷新到磁盤中，這一過程稱為刷臟。
如果MySQL宕機，那此時Buffer Pool中修改的數(shù)據(jù)不是丟失了嗎？Innodb引入了redo log來解決這個問題。當(dāng)數(shù)據(jù)修改時，會先在redo log記錄這次操作，然后再修改緩沖池中的數(shù)據(jù)，當(dāng)事務(wù)提交時，會調(diào)用fsync接口對redo log進行刷盤。
如果MySQL宕機，重啟時可以讀取redo log中的數(shù)據(jù)，對數(shù)據(jù)庫進行恢復(fù)。由于redo log是WAL日志，也就是預(yù)寫式日志，所有修改先寫入日志，所以保證了數(shù)據(jù)不會因MySQL宕機而丟失，從而滿足了持久性要求。
按你所說，redo log 也需要寫磁盤，為什么不直接將數(shù)據(jù)寫磁盤呢？嗯。。。主要是有以下兩方面的原因：
1.對Buffer Pool進行刷臟是隨機IO，因為每次修改的數(shù)據(jù)位置隨機，但寫redo log是追加操作，屬于順序IO；
2.刷臟是以數(shù)據(jù)頁為單位，MySQL默認(rèn)頁大小是16KB，一個Page上一個小修改都要整頁寫入，所以積累一些數(shù)據(jù)一并寫入會大大提升性能；而redo log中只包含真正需要寫入的部分，無效IO比較少。
redo log是持久性的核心，WAL的思路也是持久化的常見解決方式，只有先落地了，才能應(yīng)對后續(xù)的各種異常。
那隔離性怎么實現(xiàn)呢？MySQL能支持Repeatable Read這種高隔離級別，主要是鎖和MVCC一起努力的結(jié)果。
我先說鎖吧。事務(wù)在讀取某數(shù)據(jù)的瞬間，必須先對其加行級共享鎖，直到事務(wù)結(jié)束才釋放；事務(wù)在更新某數(shù)據(jù)的瞬間，必須先對其加行級排他鎖，直到事務(wù)結(jié)束才釋放；
為了防止幻讀，還會有間隙鎖進行區(qū)間排它鎖定。
然后是MVCC，多版本并發(fā)控制，主要是為了實現(xiàn)可重復(fù)讀，雖然鎖也可以，但是為了更高性能考慮，使用了這種多版本快照的方式。
因為是快照，所以一個事務(wù)針對同一條Sql查詢語句的結(jié)果，不會受其它事務(wù)影響。
索引原理
索引的底層實現(xiàn)是什么？用的B 樹，它是一個N叉排序樹，每個節(jié)點通常有多個子節(jié)點。節(jié)點種類有普通節(jié)點和葉子節(jié)點。根節(jié)點可能是一個葉子節(jié)點， 也可能是個普通節(jié)點。
B 樹
那MySQL為什么用樹做索引？一般而言，能做索引的，要么Hash，要么樹，要么就是比較特殊的跳表。Hash不支持范圍查詢，跳表不適合這種磁盤場景，而樹支持范圍查詢，且多種多樣，很多樹適合磁盤存儲。所以MySQL選擇了樹來做索引。
那你能說說為什么是B 樹，而不是平衡二叉樹、紅黑樹或者B-樹嗎？平衡二叉樹追求絕對平衡，條件比較苛刻，實現(xiàn)起來比較麻煩，每次插入新節(jié)點之后需要旋轉(zhuǎn)的次數(shù)不能預(yù)知。
同時，B 樹優(yōu)勢在于每個節(jié)點能存儲多個信息，這樣深度比平衡二叉樹會淺很多，減少數(shù)據(jù)查找的次數(shù)。
平衡二叉樹
紅黑樹放棄了追求完全平衡，只追求大致平衡，在與平衡二叉樹的時間復(fù)雜度相差不大的情況下，保證每次插入最多只需要三次旋轉(zhuǎn)就能達到平衡，實現(xiàn)起來也更為簡單。
但是紅黑樹多用于內(nèi)部排序，即全放在內(nèi)存中，而B 樹多用于外存上時，B 也被稱為一個磁盤友好的數(shù)據(jù)結(jié)構(gòu)。
同時，紅黑樹和平衡二叉樹有相同缺點，即每個節(jié)點存儲一個關(guān)鍵詞，數(shù)據(jù)量大時，導(dǎo)致它們的深度很深，MySQL每次讀取時都會消耗大量IO。
紅黑樹
那B 樹相比B-樹有什么優(yōu)點呢？哈哈，我覺得這就屬于同門師兄較勁兒了。B 樹非葉子節(jié)點只存儲key值，而B-樹存儲key值和data值，這樣B 樹的層級更少，查詢效率更高；
MySQL進行區(qū)間訪問時，由于B 樹葉子節(jié)點之間用指針相連，只需要遍歷所有的葉子節(jié)點即可，而B-樹則需要中序遍歷一遍。
B-樹
這類選型問題其實很深，要深刻理解為什么要用B 樹、B 樹有哪些競爭對手。換句話說，也就是要了解，哪些數(shù)據(jù)結(jié)構(gòu)能做索引。如果能答出哈希表、樹、跳表這三大類，就說明確實有自己的深入思考，這部分知識點學(xué)透了，也是加分項。
接下來講講聚簇索引和二級索引吧。聚簇索引是主鍵上的索引，二級索引是非主鍵字段的索引。這兩者相同點是都是基于B 樹實現(xiàn)。
區(qū)別在于，二級索引的葉子結(jié)點只存儲索引本身內(nèi)容，以及主鍵ID，聚簇索引的葉子結(jié)點，會存儲完整的行數(shù)據(jù)。在一定程度上，可以說二級索引就是主鍵索引的索引。
一般來說，面試官讓介紹兩個名詞或者概念，潛臺詞就是要我們說清楚兩者的相同點、不同點，說清楚了就過關(guān)。如果有些自己的總結(jié)性思考，比如在上面的對話中，阿柴回答出二級索引是主鍵索引的索引，這樣就會讓面試官眼前一亮。
鎖
下面講講MySQL鎖的分類吧。MySQL從鎖粒度粒度上講，有表級鎖、行級鎖。從強度上講，又分為意向共享鎖、共享鎖、意向排它鎖和排它鎖。
鎖模式的兼容情況
那select操作會加鎖嗎？對于普通select語句，InnoDB 不會加任何鎖。但是select語句，也可以顯示指定加鎖。有兩種模式，一種是LOCK IN SHARE MODE是加共享鎖，還有Select ... for updates是加排它鎖。
什么情況下會發(fā)生死鎖？嗯。。。比如事務(wù)A鎖住了資源1，然后去申請資源2，但事務(wù)B已經(jīng)占據(jù)了資源2，需要資源1，誰都不退讓，就死鎖了。對于MySQL，最常見的情況，就是資源1、資源2分別對應(yīng)一個排它鎖。
那間隙鎖你有了解么？間隙鎖就是對索引行進行加鎖操作，不僅鎖住其本身，還會鎖住周圍鄰近的范圍區(qū)間。間隙鎖的目的是為了解決幻影讀，但也因此帶來了更大的死鎖隱患。
比如，一個任務(wù)表里面有個狀態(tài)字段，是一個非唯一索引，有一個任務(wù)id，是唯一索引。
一個sql將狀態(tài)處于執(zhí)行中的任務(wù)設(shè)置為等待中，另一個sql正好通過任務(wù)id更新在范圍內(nèi)的一條任務(wù)信息。那么因為是在不同索引加鎖的，所以都能成功。但是最后去更新主鍵數(shù)據(jù)的時候，就會死鎖。
介于篇幅，其中的一些知識點，比如MVCC，并未擴展出來深度闡述，建議大家可以看看我往期的數(shù)據(jù)庫文章。
- 索引為什么能提高查詢效率
- 事務(wù)、事務(wù)隔離級別和MVCC
- MySQL 全局鎖、表級鎖、行級鎖
- MySQL 到底是怎么加行鎖的？
- 林哥，幻讀是怎么被解決的？
- 完蛋，公司被一條 update 語句干趴了！
下載文檔
