滿滿乾貨！深入剖析MySQL中可能會忽視的小細節，原來還能這麼玩

Java程序老師

今日分享開始啦，請大家多多指教~

mysql基礎

對於後端開發來說，打交道最多的應該是數據庫了，因為你總得把東西存起來。

或是mongodb或者redis又或是mysql。然後你發現一個問題，就是他們都有日志系統，那麼這些日志用來幹什麼的呢？

舉兩個例子，回滾和同步。

回滾，這裏的回滾是比如說一條語句增加了1，然後再減一嗎？這裏的回滾操作並不是這樣的。

比如說我要更新一條語句，update test set a=1 where b=2,這樣的語句，如果這條語句需要回滾，那麼操作就應該是在執行前，先查詢這條數據進行保存，如果執行完畢需要回滾，那麼就直接把原來那條語句寫回去。

又比如說，你的數據庫要還原到一個小時前，那麼你可以把2個小時前的備份拿出來，然後運行前兩個小時到前一個小時的日志文件，那麼這個時候就相當於回到了一個小時前了。

同步，比如說主從同步了，這樣老生常談的了，一般通過事務日志來同步。

總之，有了日志，那麼可以幫我們實現很多功能的了。

那麼mysql 在innodb 引擎下，有兩個日志非常重要，那就是redo log（重做日志）和 binlog（歸檔日志）日志。

如果沒有這兩個日志，應該沒啥人敢用mysql的了，因為這兩個日志是用了保證mysql的數據完整性的，如果一個數據庫連完整性都不能保證，那麼是非常危險的。

redo log

首先看下redo log，這個是什麼呢？ 這個是innodb存儲引擎的日志。

說一個它的功能哈，前文提及到存儲引擎就相當於我們操作系統的文件系統。

那麼問題來了，我們的文件系統是有緩存的，比方說我們寫入一個文件，當我們調用函數的時候，不會直接寫入，而是寫入緩存去，而後又文件系統自己判斷啥時候應該寫入進去。

判斷什麼時候應該寫入進去：

其中有一個標准就是這次要寫入緩存的時候，判斷緩存是否能夠裝得下，如果裝不下，那麼先寫入文件，清除緩存，然後再寫入緩存。

第二個判斷標准就是根據時間某一段時間後進行寫入。

同樣存儲引擎也要為這一段事情操心啊。如果我們更新一條語句，存儲引擎就直接給我們操作正在存儲數據的地方，那效率可想而知。所以說，存儲引擎就想到一個方法，把更新記錄記錄到redo log 中，等redo log 快寫滿，然後就操作到磁盤，或等空閑時間更新進去。

寫完redo log 之後，就會告訴執行器，執行完畢了。這個時候我們的應用程序得到更新成功的回調。

如果單純只寫入redo log是不行的，因為存儲過程不僅要寫，還要讀啊，如果寫完redo log 通知我們的應用程序更新成功，這個時候還沒寫入到數據文件，那麼我們的應用程序去讀的時候就讀到了舊的數據。

那麼這個時候，存儲引擎是這麼幹的。
反正你給我查詢的時候要先查詢內存，內存中沒有才去查詢數據文件。那麼存儲引擎，先更新到內存中，然後更新到redo log。這樣對於存儲引擎外部來說，是更新了的，畢竟對於外部來說存儲引擎是一個整體。

這就是MySQL裏經常說到的WAL技術，WAL的全稱是Write-Ahead Logging。

那麼這個redo log 是一個什麼樣的機制呢？難道就一直記錄到一個文件中，然後當要寫入數據文件的時候，全部寫入，然後刪除？如果這麼幹效率自然是低了。redo log的機制是這樣的。

redo log 是由四個文件組成，每個文件大小為1G左右，這個都是可以設置的。

有兩個參數，分別為checkpoint 和 write pos。

write pos 是當前記錄的位置。

checkpoint 是當前寫入到數據文件的位置。

比如說：

一開始的時候write pos 寫得了第二個文件的問題，如果為位置1000。

這個時候還沒有去正在寫入數據文件，那麼這個時候checkpoint 位置就是0。要往數據文件中寫入數據的部分就是checkpoint 到 write pos這一段區間，也就是0-1000位置。

那麼這個時候存儲引擎感覺可以更新了，然後開始寫入到正在的數據文件中，那麼這個時候開始checkpoints 開始往右移動，假設更新800條。

那麼就到了下面這個位置：

這個時候存儲引擎感覺比較忙了，那麼就更新800條後，繼續接執行器的任務，那麼write pos 往右繼續移動。

那麼這個時候就有一個問題出現，比如說文件大小不變，write pos 一直往右移動，這樣會超出啊。

那麼這個時候write pos 發現自己到了末尾，人家又從第一個開始寫，覆蓋寫入。

如下：

綠色橫線部分是要更新到數據文件的部分。

redo log 還有一個重要的作用，保證數據正在地寫入到數據文件中。

比如說這個時候正在寫入數據文件，然後數據庫異常重啟了，這裏理解異常重啟，簡單點理解就是內存都沒了。那麼我們知道寫入文件是有緩存的，如果寫入到一半異常了，那麼數據其實是丟了的。

有了redo log之後，只有數據文件flush了，那麼這個時候checkpoint才開始偏移，否則就如果異常了內存沒了，那麼繼續覆蓋更新，因為checkpoint 沒有變化，那麼還是從原來那個異常前的位置開始同步。

那麼問題來了，這時候就會想，數據文件是文件，redo log也是文件啊。如果寫入的時候在緩存區，然後宕機這個時候也沒了啊。

沒錯，的確有這個問題，這個時候為了數據安全，redo log直接不使用緩存區。

redo log用於保證crash-safe能力。innodb_flush_log_at_trx_commit這個參數設置成1的時候，表示每次事務的redo log都直接持久化到磁盤。

binlog

binlog 是每個mysql都有的，而不是存儲引擎的東西，屬於mysql 的server 層的東西。

對比一下binlog 和 redo log。

這兩種日志有以下三點不同。

redo log是InnoDB引擎特有的；binlog是MySQL的Server層實現的，所有引擎都可以使用。

redo log是物理日志，記錄的是“在某個數據頁上做了什麼修改”；binlog是邏輯日志，記錄的是這個語句的原始邏輯，比如“給ID=2這一行的c字段加1 ”。

redo log是循環寫的，空間固定會用完；binlog是可以追加寫入的。“追加寫”是指binlog文件寫到一定大小後會切換到下一個，並不會覆蓋以前的日志。

一條更新語句在innodb引擎下的更新過程:

1. 執行器先找引擎取ID=2這一行。ID是主鍵，引擎直接用樹搜索找到這一行。如果ID=2這一行所在的數據頁本來就在內存中，就直接返回給執行器；否則，需要先從磁盤讀入內存，然後再返回。

2. 執行器拿到引擎給的行數據，把這個值加上1，比如原來是N，現在就是N+1，得到新的一行數據，再調用引擎接口寫入這行新數據。

3. 引擎將這行新數據更新到內存中，同時將這個更新操作記錄到redo log裏面，此時redo log處於prepare狀態。然後告知執行器執行完成了，隨時可以提交事務。

4. 執行器生成這個操作的binlog，並把binlog寫入磁盤。

5. 執行器調用引擎的提交事務接口，引擎把剛剛寫入的redo log改成提交（commit）狀態，更新完成。

這個時候有人可能會問，如果第3步驟先更新到內存，這個時候要是讀取操作而之後redo log沒有寫入就宕機怎麼辦？因為是寫入是有鎖的，如果沒有提交事務，這個時候有寫鎖。

這時候就發現一個細節，那就是redo log 一條記錄有兩個狀態一個是prepare，一個是commit狀態。

那麼為什麼要兩個狀態呢？

我們知道mysql 主從，其實是通過binlog 一條一條發送給從數據庫，讓從數據庫執行binlog裏面的操作。

假設沒有這兩個狀態。

假如innodb 寫入redo log之後呢，這個時候數據庫突然宕機了，這個時候redo log 是有的記錄的，這個時候binlog 沒有記錄。那麼innodb 通過redo log 進行寫入到數據文件後，binlog 依然沒有這一條記錄。那麼從庫就少了一條操作了。

這個時候主從永遠不可能一致。

如果有了兩個狀態，數據庫重啟後，innodb存儲引擎還是會通知binlog。這時候兩個狀態就保證了binlog裏面的數據完整性。那麼這個時候又會問了，假如上面第四步執行了，第五步沒有執行怎麼辦？比如宕機了。

是啊，這個時候bin log 中有記錄但是redo log沒有記錄。那麼從庫就少了一條操作記錄了。

這個時候主從永遠不可能一致。同樣，我們如果數據庫退回到某個時間點，如果binlog 和 redolog不一致的話，同樣適用binlog進行回滾一樣的會遇到這個問題。

如果有了redo log 的prepare 狀態，那麼如果數據庫重啟的時候檢測到宕機，這個時候redo log裏面prepare 狀態的數據就會和binlog裏面的數據進行校驗，進而進行恢複。

這種有兩個狀態的提交，叫做兩階段提交。他們起到的作用是如果宕機檢測到異常，就會對比恢複。

同樣binlog也是文件，同樣存在緩存的問題，sync_binlog這個參數設置成1的時候，表示每次事務的binlog都持久化到磁盤。

mysql 事務

事務有四大特性:

1.原子性(atomicity)

一個事務必須被視為一個不可分割的最小單元。

2.一致性(consistency)

數據庫總是從一個一致性的狀態轉換到另一個一致性的狀態。很多人對事務的一致性和原子性可能會有偏差。要理解這個東西呢，首先要拋開mysql，或者我們常見的數據庫sql server，mongodb。

單純來理解數據庫的事務。

假如有兩個事務，事務a和事務b。

假設A和B的兩個賬號，a賬戶是500塊，b賬戶是300塊。裏面有一個限制就是A賬號不能大於600塊。

事務a 的邏輯是給A增加100塊。然後給B減少100塊。

事務b 的邏輯是給B減少100塊。然後給A增加100塊。

現在事務a，開始執行，做好備份做回滾(500,300),然後給A增加了100塊，A現在是600。

現在事務b，開始執行，做好備份做回滾(600,300),然後給B減少了100塊，現在B是200。

現在事務a開始執行，給B減少100，B變成了100。也急速說b事務提交的是(600,100)

現在b開始執行，但是報錯了，遇到了A不能高於600的限制，現在開始回滾，那麼回滾為(600,300)。

那麼這個時候就變成了(600,300)了。那麼請問事務a和事務b 是否符合原子性?

首先分析事務a，現在能做的，全部執行了。那麼是符合原子性的。

然後分析事務b，的確是回滾了，也是符合原子性的。

這有疑問嗎？沒有吧。

那麼事務a和事務b是否符合一致性呢?

a是否符合一致性呢? 數據庫總是從一個一致性的狀態轉換到另一個一致性的狀態。一致性的要求是B賬戶減少100塊，A賬戶多出100塊了。這是一致性的要求。

數據庫一開始是:(500,300),然後a提交的時候(600,100),這顯然不符合。b事務其實是符合一致性的，一開始是(500,300)，回滾也是（500,300），這沒錯。

那麼是否一致性就一定要原子性呢?

這樣其實也是可以一致性的。

那麼數據庫為什麼不這麼幹呢?比如說 update test set a=a-1;

這個時候回滾的時候還得給你生成一個update test set a=a+1。這是簡單的，如果是複雜的，這是要將數據庫稱為人工智能嗎？數據庫都會傻掉的。

要實現一致性，原子性的成本應該是最低的，但是單單原子性是不能實現一致性的。

3.隔離性

上面我們知道單單原子性是沒有實現一致性的。那麼隔離性就是在原子性的基礎上增加一些，一些限制條件那麼就可以實現一致性。

比如說，每個事務只能串行執行，這個時候也說符合的，這也是一種隔離級別。但是如果是串行，就不滿足並發了，所以就有其他隔離級別了，或者說其他隔離方式。

4.持久性

一旦事務提交，則其所做的修改就會永久保存到數據庫中。可能有人說這不是廢話嗎？事務做的修改不就是要保持帶數據文件中，能夠持久化嗎？

這裏面的持久性，表示得更多的是一種方案。持久化是有很多方式的，怎麼確保你的持久化方案可行呢？比如說一個事務要修改4條語句分別在四張表，那麼怎麼確保這四條語句能夠全部寫入進去呢？會不會寫到第二條的時候系統崩潰呢？

如果出現了上面的問題，該怎麼處理？這就是持久性的重要性了。前三個都是強調語句執行，最後一個強調存儲。

今日份分享已結束，請大家多多包涵和指點！