MySQL資料寫入過程介紹

第一階段

資料直接寫入到磁碟。

問題： 速度慢

因為磁碟寫入速度比記憶體寫入速度慢很多。

第二階段

解決方案：

資料先寫入記憶體，後非同步重新整理到磁碟。

記憶體中髒資料什麼時間重新整理到磁碟？

1 、 InnoDB 的 redo log 寫滿了。

這時候系統會停止所有更新操作，把checkpoint 往前推進， redo log 留出空間可以繼續寫。

2 、系統記憶體不足。

當需要新的記憶體頁，而記憶體不夠用的時候，就要淘汰一些資料頁，空出記憶體給別的資料頁使用。如果淘汰的是 “ 髒頁 ” ，就要先將髒頁寫到磁碟。

3 、 MySQL 認為系統 “ 空閒 ” 的時候。

4 、 MySQL 正常關閉的情況。

這時候，MySQL 會把記憶體的髒頁都 flush 到磁碟上，這樣下次 MySQL 啟動的時候，就可以直接從磁碟上讀資料，啟動速度會很快。

問題：

非同步：資料還沒完全寫入磁碟後，記憶體或系統崩潰，資料丟失。

第三階段

解決方案：

寫入記憶體後，為了提高速度，並不馬上寫磁碟，後面會延時批次寫入磁碟，同時為了資料安全，引入 redo log ，在記憶體寫入資料時，會同時生成 redo log 資料，記錄資料修改操作，用於崩潰恢復。

Redo 記錄示例：

將第 5 號表空間中第 100 號頁面中偏移量為 150 處的值更新為 2 。

崩潰恢復：

如果系統崩潰了，記憶體的資料全部丟失，重啟後，只需要按照 redo 記錄重新更新一遍資料頁，就可以恢復丟失的資料。

為什麼 redo log buffer 寫入到 redo log file 速度比髒塊寫入到 datafile 快？

1.redo 日誌佔用空間非常小。

2.redo 日誌是順序寫入磁碟的，速度比隨機性快。

redo log buffer 寫入到 redo log file 觸發條件：

1.log buffer 空間不足時。

透過系統變數 innodb_log_buffer_size 指定 log_buffer 大小，如果 log buffer 的 redo 日誌量已經佔滿 log buffer 總空間 50% 左右時，會將日誌重新整理到磁碟。

2. 事務提交

事務提交時，可以不把修改過的 buffer pool 頁面立即重新整理到 datafile 裡，但是為了保證永續性，必須要把資料修改時所對應的 redo 日誌重新整理到磁碟 redo log file ，用於崩潰恢復。

這個過程和 innodb_flush_log_at_trx_commit 引數有關，該引數有 3 個可選值， 0 、 1 、 2 。

引數值為 0 時：

表示事物提交時，不會立即向磁碟同步 redo 日誌，這個任務交給後臺執行緒來處理。

引數值為 1 時：

表示在事物提交時需要將 redo 日誌同步到磁碟，可以保證事物的永續性，這也是預設值。

引數值為 2 時：

表示事務提交時需要將 redo 日誌寫入到作業系統緩衝區中，但並不需要保證將日誌真正的落盤。

3.buffer pool 中髒頁重新整理到磁碟 datafile

buffer pool 中髒頁重新整理到磁碟 datafile 前，業務先執行對應 redo 日誌的刷盤。

4. 每 1 秒

後臺執行緒會以每 1 秒一次的頻率將 log buffer 中 redo 日誌進行刷盤。

5. 正常關閉伺服器時

6. 做 checkpoint 時

問題：

1. 寫入過程中，還未提交，為了避免髒讀，別的會話如何讀取修改前的資料。

2. 寫入後悔了怎麼辦。

第四階段

解決方案：

修改記憶體資料前，先將舊資料寫入到 undo 中，可以透過 undo 中的舊資料進行一致性查詢和回滾等操作。

如果沒有 undo ，其他會話想要讀取另一個會話正在修改還未提交的資料時，為了避免髒讀，讀取請求會被阻塞，直到另一個會話完成提交或回滾，這在高併發大事務下效率會很低。

問題：

因為 mysql 資料頁大小 16KB ，作業系統也大小一般 4KB ，在執行一次 mysql I/O 寫入時，對應 4 次 OS  I/O, 這種情況被稱為寫失效（ partial page write ）。此時重啟後，磁碟上就是不完整的資料頁，就算使用 redo log 也是無法進行恢復的。

第五階段

解決方案：

Double Write  雙寫

在對緩衝池的髒頁進行重新整理時，並不直接寫磁碟，而是會透過 memcpy 函式將髒頁先複製到記憶體中的 Double write buffer 。透過 Double write buffer 再分兩次，每次 1MB 順序地寫入共享表空間的物理磁碟上，然後馬上呼叫 fsync 函式，同步磁碟，避免緩衝寫帶來的問題。

問題：

如果開啟了 binlog ，是先寫 binlog 還是先寫 redolog?

場景 1 ： 先寫 redo log 後寫 binlog

假設在 redo log 寫完， binlog 還沒有寫完的時候， MySQL 程式異常重啟。由於我們前面說過的， redo log 寫完之後，系統即使崩潰，仍然能夠把資料恢復回來。

但是由於 binlog 沒寫完就 crash 了，這時候 binlog 裡面就沒有記錄這個語句。因此，之後備份日誌的時候，存起來的 binlog 裡面就沒有這條語句。

如果需要用這個 binlog 來恢復臨時庫的話，由於這個語句的 binlog 丟失，這個臨時庫就會少了這一次更新，與原庫的值不同。

場景 2 ： 先寫 binlog 後寫 redo log

如果在 binlog 寫完之後 crash ，由於 redo log 還沒寫，崩潰恢復以後這個事務無效，值更新失敗。但是 binlog 裡面已經記錄了修改值的日誌。所以，在之後用 binlog 來恢復的時候就多了一個事務出來，恢復出來的這一行的值與原庫的值不同。

可以看到，無論是先寫 binlog 在寫 redo 還是先寫 redo 在寫 binlog 都存在問題。

第六階段

解決方案：

二階段提交：

兩個場景都有問題，所以引入了 “二階段提交”，將 redo log 的提交分為 prepare 和 commit 兩個階段 。

透過二階段提交，可以解決如下場景問題：

1.redo log(prepare) 執行失敗，由於 redo log 沒有 commit 標識，並且 binlog 沒有寫入，對應的事務直接回滾。

2.redo log(prepare) 執行成功， binlog 還沒開始寫入，由於 redo log 沒有 commit 標識，並且 binlog 沒有寫入，對應的事務直接回滾。

3.redo log(prepare) 執行成功， binlog 寫入了部分，系統故障，由於 redo log 沒有 commit 標識，並且 binlog 檔案不完整，對應的事務直接回滾。

4.redo log(prepare) 執行成功， binlog 寫入成功， redo log(commit) 寫入失敗，檢查 redo log(prepare) 成功，並且 binlog 是完整的，直接提交事務。

上述講解的 binlog 寫入是指寫入到記憶體中，也就是 binlog cache 中，那麼 binlog 如何刷盤呢？

事務 binlog event 寫入流程

binlog cache 和 binlog 臨時檔案都是在事務執行過程中寫入，一旦事務提交， binlog cache 和 binlog 臨時檔案都會釋放掉。而且如果事務中包含多個 DML 語句，他們共享 binlog cache 和 binlog 臨時檔案。

整個 binlog 寫入流程類似如下：

1. 事務開啟 ;

2. 執行 dml 語句，在 dml 語句第一次執行的時候會分配記憶體空間 binlog cache;

3. 執行 dml 語句期間生成的 event 不斷寫入到 binlog cache;

4. 如果 binlog cache 的空間已經滿了，則將 binlog cache 的資料寫入到 binlog 臨時檔案，同時清空 binlog cache;

如果 binlog 臨時檔案的大小大於了 max_binlog_cache_size 的設定則拋錯 ERROR 1197;

5. 事務提交，整個 binlog cache 和 binlog 臨時檔案資料全部寫入到 binlog file 中，同時釋放 binlog cache 和 binlog 臨時檔案。

這塊和 sync_binlog 引數有關：

sync_binlog=0 時：

當事務提交之後， MySQL 不做 fsync 之類的磁碟同步指令重新整理 binlog_cache 中的資訊到磁碟，而讓 Filesystem 自行決定什麼時候來做同步，或者 cache 滿了之後才同步到磁碟。

sync_binlog=n 時：

當每進行 n 次事務提交之後， MySQL 將進行一次 fsync 之類的磁碟同步指令來將 binlog_cache 中的資料強制寫入磁碟。

但是注意此時 binlog cache 的記憶體空間會被保留以供 THD 上的下一個事務使用，但是 binlog 臨時檔案被截斷為 0 ，保留檔案描述符。其實也就是 IO_CACHE( 參考後文 ) 保留，並且保留 IO_CACHE 中的分配的記憶體空間，和物理檔案描述符 ;

6.  客戶端斷開連線，這個過程會釋放 IO_CACHE 同時釋放其持有的 binlog cache 記憶體空間以及持有的 binlog 臨時檔案。

問題：

如果 mysql 伺服器或硬體故障，無法及時啟動資料庫，如何減少服務中斷和資料損失。

第七階段

解決方案：

主從複製

如果存在從庫，主庫會將新增的資料產生的 binlog 日誌透過 binlog dump 執行緒傳給從庫，從庫透過 I/O 執行緒接收傳來的日誌並寫入到 relay log 日誌中，最後 SQL 執行緒解析 relay log 日誌進行資料重放。

問題：

主從複製預設是非同步複製的，在非同步複製中，主庫並不關心從庫是否接收到完整的日誌，直接會進行後面的提交操作，如果在從庫還沒接收完主庫傳來的 binlog ，這時主庫故障，從庫切換為主庫，那麼在新主庫上讀取的資料可能會有缺失，導致資料不一致。

第八階段

解決方案：

半同步複製

主庫將新增的資料產生的的 binlog 日誌透過 binlog dump 執行緒傳給從庫，從庫透過 I/O 執行緒接收傳來的日誌並寫入到 relay log 日誌中，最後 SQL 執行緒解析 relay log 日誌進行資料重放。

其中在從庫將日誌並寫入到本地 relay log 後，會給主庫返回 ack 訊息，告知主庫也提交事務了，之後主庫才會繼續提交事務。

這在一定情況下解決了非同步複製的問題，提高了資料的安全性，但是半同步複製還是有一些缺陷。

問題 ：

1. 從庫將日誌並寫入到本地 relay log 後，主庫提交事務，這時主庫故障，從庫切換為主庫，如果 relay log 很大， SQL 執行緒還沒有重放完成，讀取新主庫的資料是滯後的，資料也不是強一致的，而是最終一致的。

2. 由於安全性和效能總是對立的，安全級別越高，效能通常最差，配置半同步時需要指定超時引數 rpl_semi_sync_master_timeout 預設 10 秒，也就是主從連線超時後，主庫會卡住 10 秒等待從庫響應， 10 秒以後半同步就會降級到非同步複製，之後如果主從連線恢復，又會自動恢復到半同步，如果主從連線一直不恢復，主從複製型別就會一直是非同步複製，同樣存在非同步複製的缺點。

總結

新資料寫入到資料庫過程：

例如執行下面語句，將 name 為 chen 行的 name 列更新為 cjc 。

update t1 set name='cjc' where name='chen';

1. 檢查待修改頁是否在記憶體 buffer 中，如果不在，將頁從磁碟讀取到記憶體中，如果已經在記憶體中，準備修改記憶體資料。

2. 修改記憶體資料之前，先將原值 name='chen' 寫入到 undo ，用於一致性讀或回滾事務，當然涉及 undo 頁修改的操作也會生成對應的 redo 資料，用來保護生成的 undo 資料。

3. 開始修改記憶體資料，將 name 為 chen 行的 name 列更新為 cjc 。

4. 生成修改資料對應的 redo log buffer ，重做日誌完全寫入到 redo log file ，更新 prepare 標識。

5. 將資料修改操作寫入到 binlog cache 中。

6.binlog 寫入完成後會傳到從庫，從庫 I/O 執行緒將接收到的日誌寫入到本地 relay log 日誌中，寫入完成後向主庫返回 ack 資訊，從庫 SQL 執行緒讀取 relay log 日誌，進行資料重放。

7. 更新 redo 日誌的 commit 標識，事務更新完成，客戶端可以正常返回。

8.buffer pool 中髒資料會根據特定觸發條件寫入到 Double write buffer 中， Double write buffer 分兩次寫，每 1MB 順序地寫入共享表空間的物理磁碟上，然後馬上呼叫 fsync 函式，同步磁碟。

###chenjuchao 20221201###