今天我們來聊聊MySQL原理

基礎篇主要是側重基礎知識，原理篇是有一定基礎後的遞進，通過學習本篇，不僅可以進一步了解MySQL的各項特性，還能為接下來的容灾調優打下堅實的基礎。

現在，就讓我們繼續跟隨阿柴進行這場沉浸式面試吧。

ACID與隔離級別

那你先來說說MySQL的四種隔離級別吧。

SQL標准定義了4類隔離級別，包括一些具體規則，用來限定事務之間的隔離性。

這四種級別分別是讀未提交、讀已提交、可重複讀、串型化。

讀未提交，顧名思義，就是可以讀到還沒有提交的數據；讀已提交會讀到其它事務已經提交的數據；可重複讀確保了同一事務中，讀取同一條數據時，會看到同樣的數據行；串型化通過强制事務排序，使其不可能相互沖突。

重點介紹下Repeatable Read吧。

Repeatable Read就是可重複讀。它確保了在同一事務中，讀取同一條數據時，會看到同樣的數據行。

它也是MyQL的默認事務隔離級別，這種級別事務之間影響很小，通常已經能够滿足日常需要了。

說出四種隔離級別只是最低要求，能每一項具體去闡述特性就算過關。如果還能指出存在的問題、依賴的技術，那麼就是妥妥的加分了！

下面我們來聊聊InnoDB中ACID的實現吧，先說一下原子性是怎麼實現的。

事務要麼失敗，要麼成功，不能做一半。聰明的InnoDB，在幹活兒之前，先將要做的事情記錄到一個叫undo log的日志文件中，如果失敗了或者主動rollback，就可以通過undo log的內容，將事務回滾。

那undo log裏面具體記錄了什麼信息呢？

undo log屬於邏輯日志，它記錄的是sql執行相關的信息。當發生回滾時，InnoDB會根據undo log的內容做與之前相反的工作，使數據回到之前的狀態。。。

那持久性又是怎麼實現的？

持久性是用來保證一旦給客戶返回成功，數據就不會消失，持久存在。最簡單的做法，是每次寫完磁盤落地之後，再給客戶返回成功。但如果每次讀寫數據都需要磁盤IO，效率就會很低。

為此，追求極致的InnoDB提供了緩沖。當向數據庫寫入數據時，會首先寫入緩沖池，緩沖池中修改的數據會定期刷新到磁盤中，這一過程稱為刷髒。

如果MySQL宕機，那此時Buffer Pool中修改的數據不是丟失了嗎？

Innodb引入了redo log來解决這個問題。當數據修改時，會先在redo log記錄這次操作，然後再修改緩沖池中的數據，當事務提交時，會調用fsync接口對redo log進行刷盤。

如果MySQL宕機，重啟時可以讀取redo log中的數據，對數據庫進行恢複。由於redo log是WAL日志，也就是預寫式日志，所有修改先寫入日志，所以保證了數據不會因MySQL宕機而丟失，從而滿足了持久性要求。

按你所說，redo log 也需要寫磁盤，為什麼不直接將數據寫磁盤呢？

嗯。。。主要是有以下兩方面的原因：

1.對Buffer Pool進行刷髒是隨機IO，因為每次修改的數據比特置隨機，但寫redo log是追加操作，屬於順序IO；

2.刷髒是以數據頁為單比特，MySQL默認頁大小是16KB，一個Page上一個小修改都要整頁寫入，所以積累一些數據一並寫入會大大提昇性能；而redo log中只包含真正需要寫入的部分，無效IO比較少。

redo log是持久性的核心，WAL的思路也是持久化的常見解决方式，只有先落地了，才能應對後續的各種异常。

那隔離性怎麼實現呢？

MySQL能支持Repeatable Read這種高隔離級別，主要是鎖和MVCC一起努力的結果。

我先說鎖吧。事務在讀取某數據的瞬間，必須先對其加行級共享鎖，直到事務結束才釋放；事務在更新某數據的瞬間，必須先對其加行級排他鎖，直到事務結束才釋放；

為了防止幻讀，還會有間隙鎖進行區間排它鎖定。

然後是MVCC，多版本並發控制，主要是為了實現可重複讀，雖然鎖也可以，但是為了更高性能考慮，使用了這種多版本快照的方式。

因為是快照，所以一個事務針對同一條Sql查詢語句的結果，不會受其它事務影響。

索引原理

索引的底層實現是什麼？

用的B+樹，它是一個N叉排序樹，每個節點通常有多個子節點。節點種類有普通節點和葉子節點。根節點可能是一個葉子節點， 也可能是個普通節點。

那MySQL為什麼用樹做索引？

一般而言，能做索引的，要麼Hash，要麼樹，要麼就是比較特殊的跳錶。Hash不支持範圍查詢，跳錶不適合這種磁盤場景，而樹支持範圍查詢，且多種多樣，很多樹適合磁盤存儲。所以MySQL選擇了樹來做索引。

那你能說說為什麼是B+樹，而不是平衡二叉樹、紅黑樹或者B-樹嗎？

平衡二叉樹追求絕對平衡，條件比較苛刻，實現起來比較麻煩，每次插入新節點之後需要旋轉的次數不能預知。

同時，B+樹優勢在於每個節點能存儲多個信息，這樣深度比平衡二叉樹會淺很多，减少數據查找的次數。

平衡二叉樹

紅黑樹放弃了追求完全平衡，只追求大致平衡，在與平衡二叉樹的時間複雜度相差不大的情况下，保證每次插入最多只需要三次旋轉就能達到平衡，實現起來也更為簡單。

但是紅黑樹多用於內部排序，即全放在內存中，而B+樹多用於外存上時，B+也被稱為一個磁盤友好的數據結構。

同時，紅黑樹和平衡二叉樹有相同缺點，即每個節點存儲一個關鍵詞，數據量大時，導致它們的深度很深，MySQL每次讀取時都會消耗大量IO。

那B+樹相比B-樹有什麼優點呢？

哈哈，我覺得這就屬於同門師兄較勁兒了。B+樹非葉子節點只存儲key值，而B-樹存儲key值和data值，這樣B+樹的層級更少，查詢效率更高；

MySQL進行區間訪問時，由於B+樹葉子節點之間用指針相連，只需要遍曆所有的葉子節點即可，而B-樹則需要中序遍曆一遍。

這類選型問題其實很深，要深刻理解為什麼要用B+樹、B+樹有哪些競爭對手。換句話說，也就是要了解，哪些數據結構能做索引。如果能答出哈希錶、樹、跳錶這三大類，就說明確實有自己的深入思考，這部分知識點學透了，也是加分項。

接下來講講聚簇索引和二級索引吧。

聚簇索引是主鍵上的索引，二級索引是非主鍵字段的索引。這兩者相同點是都是基於B+樹實現。

區別在於，二級索引的葉子結點只存儲索引本身內容，以及主鍵ID，聚簇索引的葉子結點，會存儲完整的行數據。在一定程度上，可以說二級索引就是主鍵索引的索引。

一般來說，面試官讓介紹兩個名詞或者概念，潜臺詞就是要我們說清楚兩者的相同點、不同點，說清楚了就過關。如果有些自己的總結性思考，比如在上面的對話中，阿柴回答出二級索引是主鍵索引的索引，這樣就會讓面試官眼前一亮。

下面講講MySQL鎖的分類吧。

MySQL從鎖粒度粒度上講，有錶級鎖、行級鎖。從强度上講，又分為意向共享鎖、共享鎖、意向排它鎖和排它鎖。

鎖模式的兼容情况

那select操作會加鎖嗎？

對於普通select語句，InnoDB 不會加任何鎖。但是select語句，也可以顯示指定加鎖。有兩種模式，一種是LOCK IN SHARE MODE是加共享鎖，還有Select ... for updates是加排它鎖。

什麼情况下會發生死鎖？

嗯。。。比如事務A鎖住了資源1，然後去申請資源2，但事務B已經占據了資源2，需要資源1，誰都不退讓，就死鎖了。對於MySQL，最常見的情况，就是資源1、資源2分別對應一個排它鎖。

那間隙鎖你有了解麼？

間隙鎖就是對索引行進行加鎖操作，不僅鎖住其本身，還會鎖住周圍鄰近的範圍區間。間隙鎖的目的是為了解决幻影讀，但也因此帶來了更大的死鎖隱患。

比如，一個任務錶裏面有個狀態字段，是一個非唯一索引，有一個任務id，是唯一索引。

一個sql將狀態處於執行中的任務設置為等待中，另一個sql正好通過任務id更新在範圍內的一條任務信息。那麼因為是在不同索引加鎖的，所以都能成功。但是最後去更新主鍵數據的時候，就會死鎖。

最後

介於篇幅，其中的一些知識點，比如MVCC，並未擴展出來深度闡述，建議大家下來自己深入研究一下，牛牛後面也會針對一些重點知識，進行單篇講解。

原文鏈接：
https://mp.weixin.qq.com/s/BRn4wXS8afzKjwjkkZ6ATg