MySQL事務ACID都知道，原理是什麼？附面試題

本文内容：本文先列出2022年近期網際網路的面試真題，然後介紹了ACID及其原理，并且簡要介紹了MySQL中的三大log。

2022 MySQL 面試題

1. 事務的特性？ACID實作原理是什麼
2. MySQL索引的結構？為什麼選用B+數？每種資料結構差別優缺點。
3. 一個SQL插入在MySQL中執行過程是怎樣的？
4. 資料庫性能瓶頸？如何排查慢SQL？如何優化？
5. MVCC的實作原理是什麼？
6. 在索引上進行一次增删改查的流程，B+樹的操作，節點調整 分裂合并等瞎扯
7. 講講bin log和redo log，以及他們的差別？
8. MySQL事務隔離級别 Read Uncommit Read Commit Repeatable Read Serialize，各自特點？原理？你們使用哪種？
9. 幻讀和可重複讀有什麼差別？舉例子說明？怎麼解決？
10. InnoDb 的鎖有哪些？
11. Gap lock 原理
12. mysql主從同步如何實作？瓶頸在哪裡？你們的MySQL架構？如何解決同步時效問題？

ACID 原理（事務的特性及原理）

說到MySQL事務，首先要提他的四大特性（ACID）：

原子性（Atomicity）、一緻性（Consistent）、隔離性（Isolation）以及持久性（Durable）。正是這些特性，才保證了資料庫事務的安全性。

原子性 （Atomicity）：

定義：原子性是指一個事務是一個不可分割的工作機關，其中的操作要麼都做，要麼都不做；如果事務中一個sql語句執行失敗，則已執行的語句也必須復原，資料庫退回到事務前的狀态。

實作原理：從定義可以看出原子性具有要麼成功要麼失敗兩方面。是以實作原子性的關鍵，是當事務復原時能夠撤銷所有已經成功執行的sql語句。InnoDB實作復原，靠的是undo log：當事務對資料庫進行修改時，InnoDB會生成對應的undo log。如果事務執行失敗或調用了rollback，導緻事務需要復原，便可以利用undo log中的資訊将資料復原到修改之前的樣子。原子性的成功靠的是redolog來實作，保證這批變更的資料一定送出成功。

關于每一種log會在後文給出介紹。

持久性（Durable）

定義：持久性是指事務一旦送出，它對資料庫的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響。

實作原理：持久性依賴于redolog實作，保證對于資料庫的改變可以持久化到磁盤中。

隔離性（Isolation）

定義：與原子性、持久性側重于研究事務本身不同，隔離性研究的是不同僚務之間的互相影響。隔離性是指，事務内部的操作與其他事務是隔離的，并發執行的各個事務之間不能互相幹擾。

嚴格的隔離性，對應了事務隔離級别中的Serializable (可串行化)，但實際應用中出于性能方面的考慮很少會使用可串行化。隔離性追求的是并發情形下事務之間互不幹擾。SQL标準定義了4類隔離級别：Read Uncommitted（讀取未送出内容）、Read Committed（讀取送出内容）、Repeatable Read（可重讀）、Serializable（可串行化）。

實作原理：簡單起見，我們僅考慮最簡單的讀操作和寫操作(暫時不考慮帶鎖讀等特殊操作)，那麼隔離性的探讨，主要可以分為兩個方面：(一個事務)寫操作對(另一個事務)寫操作的影響：更新/删除/插入會用LBCC（基于鎖的并發控制）保證隔離性；(一個事務)寫操作對(另一個事務)讀操作的影響：MVCC保證隔離性（MVCC依賴于undo log）。

一緻性（Consistent）

定義：一緻性是指事務執行結束後，資料庫的完整性限制沒有被破壞，事務執行的前後都是合法的資料狀态。資料庫的完整性限制包括但不限于：實體完整性（如行的主鍵存在且唯一）、列完整性（如字段的類型、大小、長度要符合要求）、外鍵限制、使用者自定義完整性（如轉賬前後，兩個賬戶餘額的和應該不變）。

可以說，一緻性是事務追求的最終目标。

實作原理：前面提到的原子性、持久性和隔離性，都是為了保證資料庫狀态的一緻性。此外，除了資料庫層面的保障，一緻性的實作也需要應用層面進行保障。實作一緻性的措施包括：

1.保證原子性、持久性和隔離性，這三種特性共同保證了一緻性，如果這些特性無法保證，事務的一緻性也無法保證

2.資料庫本身提供保障，例如不允許向整形列插入字元串值、字元串長度不能超過列的限制等

3.應用層面進行保障，例如如果轉賬操作隻扣除轉賬者的餘額，而沒有增加接收者的餘額，無論資料庫實作的多麼完美，也無法保證狀态的一緻

三大log

這裡對于MySQL三大 log 隻做最精華的介紹，根據回報如有需要可以後期寫更詳細的文章。redo log和undo log都屬于InnoDB的事務日志，下面先聊一下redo log存在的背景。

BufferPool的産生

InnoDB作為MySQL的存儲引擎，資料是存放在磁盤中的，但如果每次讀寫資料都需要磁盤IO，效率會很低。為此，InnoDB提供了緩存(Buffer Pool)，Buffer Pool中包含了磁盤中部分資料頁的映射，作為通路資料庫的緩沖：當從資料庫讀取資料時，會首先從Buffer Pool中讀取，如果Buffer Pool中沒有，則從磁盤讀取後放入Buffer Pool；當向資料庫寫入資料時，會首先寫入Buffer Pool，Buffer Pool中修改的資料會定期重新整理到磁盤中（這一過程稱為刷髒）。

redo log

Buffer Pool的使用大大提高了讀寫資料的效率，但是也帶了新的問題：如果MySQL當機，而此時Buffer Pool中修改的資料還沒有重新整理到磁盤，就會導緻資料的丢失，事務的持久性無法保證。

于是，redo log被引入來解決這個問題：當資料修改時，除了修改Buffer Pool中的資料，還會在redo log記錄這次操作；當事務送出時，會調用fsync接口對redo log進行刷盤。如果MySQL當機，重新開機時可以讀取redo log中的資料，對資料庫進行恢複。redo log采用的是WAL（Write-ahead logging，預寫式日志），所有修改先寫入日志，再更新到Buffer Pool，保證了資料不會因MySQL當機而丢失，進而滿足了持久性要求。

既然redo log也需要在事務送出時将日志寫入磁盤，為什麼它比直接将Buffer Pool中修改的資料寫入磁盤(即刷髒)要快呢？主要有以下三方面的原因：

1. 刷髒是随機IO，因為每次修改的資料位置随機，但寫redo log是追加操作，屬于順序IO，對于磁盤來說順序讀寫的速度是非常快的。
2. 刷髒是以資料頁（Page）為機關的，MySQL預設頁大小是16KB，一個Page上一個小修改都要整頁寫入；而redo log中隻包含真正需要寫入的部分，無效IO大大減少。
3. redolog還有組送出的機制，多個事務的資料會一起送出寫入磁盤，是以會提高刷入磁盤的效率，這裡我們就不展開了。

Binlog

Binlog記錄所有MySQL資料庫表結構變更以及表資料修改的二進制日志，不會記錄select和show這類查詢操作的日志。Binlog日志是以事件形式記錄，還包含語句所執行的消耗時間。開啟Binlog日志有以下兩個最重要的使用場景。

• 主從複制：在主庫中開啟Binlog功能，這樣主庫就可以把Binlog傳遞給從庫，從庫拿到Binlog後實作資料恢複達到主從資料一緻性。
• 資料恢複：通過mysqlbinlog等工具來恢複資料

Binlog檔案記錄模式有STATEMENT、ROW和MIXED三種，通常使用 row 模式。

redo log 和 binlog 差別

我們知道，在MySQL中還存在binlog(二進制日志)也可以記錄寫操作并用于資料的恢複，但二者是有着根本的不同的：

1. 作用不同：redo log是用于crash recovery的，保證MySQL當機也不會影響持久性；binlog是用于point-in-time recovery的，保證伺服器可以基于時間點恢複資料，此外binlog還用于主從複制。
2. 層次不同：redo log是InnoDB存儲引擎實作的，而binlog是MySQL的伺服器層實作的，同時支援InnoDB和其他存儲引擎。
3. 内容不同：redo log是實體日志，内容基于磁盤的Page；binlog的内容是二進制的，根據binlog_format參數的不同，可能基于sql語句、基于資料本身或者二者的混合。
4. 寫入時機不同：binlog在事務通常送出時寫入或者N個事務送出一次，redo log的寫入時機相對多元，可能每次事務送出時，也可能被其他線程事務送出，還可能每秒鐘刷盤。（注意：redolog未送出的事務也可能刷入磁盤）

前面曾提到：當事務送出時會調用fsync對redo log進行刷盤；這是預設情況下的政策，修改innodb_flush_log_at_trx_commit參數可以改變該政策，但事務的持久性将無法保證。

innodb_flush_log_at_trx_commit參數

當設定為0，該模式速度最快，但不太安全，mysqld程序的崩潰會導緻上一秒鐘所有事務資料的丢失。

當設定為1，該模式是最安全的，但也是最慢的一種方式。在mysqld 服務崩潰或者伺服器主機crash的情況下，binary log 隻有可能丢失最多一個語句或者一個事務。

當設定為2，該模式速度較快，也比0安全，隻有在作業系統崩潰或者系統斷電的情況下，上一秒鐘所有事務資料才可能丢失。

通常這個參數設定為1

undo log

Undo Log的字面意思就是撤銷操作的日志，指的是使MySQL中的資料回到某個狀态。在MySQL資料庫中，事務開始之前，MySQL會将待修改的記錄儲存到Undo Log中，如果資料庫崩潰或者事務需要復原時，MySQL可以通過利用Undo Log日志，将資料庫中的資料復原到之前的狀态。

Undo Log是一種 邏輯日志， 記錄的是一個變化過程。比如，MySQL執行一個delete操作，Undo Log就會記錄一個insert操作；MySQL執行一個insert操作，Undo Log就會記錄一個delete操作；MySQL執行一個update操作，Undo Log就會記錄一個相反的update操作。

以update操作為例：當事務執行update時，其生成的undo log中會包含被修改行的主鍵(以便知道修改了哪些行)、修改了哪些列、這些列在修改前後的值等資訊，復原時便可以使用這些資訊将資料還原到update之前的狀态。

總結

原子性：事務一起成功或者失敗。依賴 undo log 和 redo log。

隔離性：事務之間是透明的(互相不可見)。普通 select 依賴 MVCC 實作，更新/删除/插入會用LBCC(基于鎖的并發控制)實作。

持久性：一旦事務送出，則其所做的修改就會永久儲存到資料庫中。依賴redolog實作。

一緻性：事務的執行的前後資料的完整性保持一緻，依賴原子性、隔離性、持久性一起保證。

最後，歡迎大家提問和交流。

作者：後端開發技術