MySQL是如何保證數(shù)據(jù)的完整性

數(shù)據(jù)的一致性和完整性對于在線業(yè)務(wù)的重要性不言而喻，如何保證數(shù)據(jù)不丟呢？今天我們就探討下關(guān)于數(shù)據(jù)的完整性和強一致性，MySQL做了哪些改進。

一. MySQL的二階段提交

    在Oracle和MySQL這種關(guān)系型數(shù)據(jù)庫中，講究日志先行策略（Write-Ahead Logging）,只要日志持久化到磁盤，就能保證MySQL異常重啟后，數(shù)據(jù)不丟失。在MySQL中，提到日志不得不提的就是redo log和binlog。

1. redo log

redo log又稱重做日志文件，詳細的記錄了對每一個數(shù)據(jù)頁里面的數(shù)據(jù)行的修改，記錄的是數(shù)據(jù)修改之后的值。Redo log是用來做數(shù)據(jù)庫crash recovery的，是保證數(shù)據(jù)安全的非常重要的功能之一。

redo log的寫入的方式是順序?qū)?、循環(huán)寫，通過innodb_log_file_size和innodb_log_files_in_group兩個參數(shù)控制redo log的文件大小和個數(shù)。redo log在寫入磁盤前會先寫redo log buffer中，大小由innodb_log_buffer_size控制。日志在寫入redo log buffer后是如何持久化到磁盤的呢？為了控制redo log的寫入策略，Innodb根據(jù)innodb_flush_log_at_trx_commit參數(shù)不同的取值采用不同的策略，它有三種不同的取值：

• 1. 設(shè)置為 0 的時候：事務(wù)提交時由MySQL的后臺Master線程每隔1秒將緩存區(qū)的文件刷新到日志文件中。
• 2. 設(shè)置為 1 的時候，表示每次事務(wù)提交時都將 redo log 直接持久化到磁盤，保證了事務(wù)日志不丟失，但會對數(shù)據(jù)庫性能稍有影響。
• 3. 設(shè)置為 2 的時候，表示每次事務(wù)提交時都只是把 redo log 寫到 日志文件中，但不會刷盤，由文件系統(tǒng)自行刷磁盤。

三種模式下，0的性能最好，但是不安全，MySQL進程一旦崩潰會導致丟失一秒的數(shù)據(jù)。1的安全性最高，但是對性能影響最大，2的話主要由操作系統(tǒng)自行控制刷磁盤的時間，如果僅僅是MySQL宕機，對數(shù)據(jù)不會產(chǎn)生影響，如果是主機異常宕機了，同樣會丟失數(shù)據(jù)。

2. binlog

binlog又稱二進制日志，記錄了對MySQL數(shù)據(jù)庫執(zhí)行更改的所有操作，不包含select和show操作，主要起到了恢復、復制、審計等功能。Binlog的格式主要有statement、row、mixed三種。

Statement：基于操作的SQL語句記錄到binlog中，不建議使用。

Row：基于行的變更情況記錄，會記錄行更改前后的內(nèi)容，row模式也是數(shù)據(jù)庫不丟數(shù)據(jù)的重要保證，推薦使用。

Mixed：混合前兩個模式，不建議使用。

Binlog的寫入邏輯也比較簡單：事務(wù)執(zhí)行過程中，先寫入binlog cache,事務(wù)提交時再寫入binlog文件。binlog cache由binlog_cache_size和max_binlog_size參數(shù)控制，每個線程分配一個binlog cache,但是共用binlog文件。

Binlog的寫入日志文件的機制由sync_binlog控制：

• 1. sync_binlog=0 的時候，表示每次提交事務(wù)都只 write，不 fsync；
• 2. sync_binlog=1 的時候，表示每次提交事務(wù)都會執(zhí)行 fsync，將數(shù)據(jù)刷盤；
• 3. sync_binlog=N(N>1) 的時候，表示n次事務(wù)提交之后，MySQL才進行一次fsync動作，將binlog cache中的數(shù)據(jù)刷入磁盤。

innodb_flush_log_at_trx_commit和sync_binlog都設(shè)置為1是MySQL數(shù)據(jù)中經(jīng)典的雙一模式，是數(shù)據(jù)庫不丟數(shù)據(jù)的保障。

MySQL數(shù)據(jù)采取WAL機制就是為了減少每次臟數(shù)據(jù)刷盤帶來的性能影響，如果設(shè)置”雙一”策略會不會影響數(shù)據(jù)庫的性能呢？其實這主要得益于redo log和binlog都是順序?qū)懀疟P的順序?qū)懕入S機寫的速度要快的多，加上MySQL內(nèi)部的組提交機制，已經(jīng)大幅降低了對磁盤的IOPS消耗了。

3. 兩階段提交

MySQL引入二階段提交（two phase commit or 2pc），MySQL內(nèi)部會將普通事務(wù)當做一個XA事務(wù)（內(nèi)部分布式事務(wù)）來處理,會自動為每個事務(wù)分配一個唯一的ID（XID），COMMIT會被動的分成Prepare和Commit兩個階段。

第一階段：Transaction Prepare Phase

此時SQL已經(jīng)成功執(zhí)行，并生成xid信息及redo和undo的內(nèi)存日志。然后調(diào)用prepare方法完成第一階段，將事務(wù)狀態(tài)設(shè)為TRX_PREPARED，并將redo log刷盤。

第二階段：Commit Phase

如果事務(wù)第一階段進入prepare階段，則將產(chǎn)生的binlog寫入文件并刷盤，此時事務(wù)已經(jīng)鐵定要提交了。

具體異常場景分析：

1. 當事務(wù)在prepare階段crash，數(shù)據(jù)庫recovery的時候該事務(wù)未寫⼊Binary log并且存儲引擎未提交，則該事務(wù)rollback。

2. 當事務(wù)在binlog階段crash，此時⽇志還沒有成功寫⼊到磁盤中，啟動時會rollback此事務(wù)。3. 當事務(wù)在binlog⽇志已經(jīng)fsync()到磁盤后crash，但是InnoDB沒有來得及commit，此時MySQL數(shù)據(jù)庫recovery的時候?qū)x出⼆進制⽇志的Xid_log_event，然后告訴InnoDB提交這些XID的事務(wù)，InnoDB提交完這些事務(wù)后會回滾其它的事務(wù)，使存儲引擎和⼆進制⽇志始終保持⼀致。

MySQL的二階段提交就保證了數(shù)據(jù)庫在異常宕機重啟后的數(shù)據(jù)不丟失。

二. Double Write

    前面我們說了，redo log、binlog以及二階段提交保證了數(shù)據(jù)在MySQL異常重啟后能夠通過前滾和回滾恢復數(shù)據(jù)。MySQL在recovery時通過redo log進行恢復，redo log記錄的是頁上的物理操作，但是這里有個問題，如果頁本身就是錯的，比如發(fā)生頁的部分寫問題(頁大小是 16K，假設(shè)在把內(nèi)存中的臟頁寫到數(shù)據(jù)庫的時候，寫了4K 突然掉電。也就是前兩 4K 是新的，后 12K 是舊的，那么這個數(shù)據(jù)頁就是不完整的，是一個壞掉的數(shù)據(jù)頁), 這時redo恢復的時候會去校驗數(shù)據(jù)頁的完整性，此時數(shù)據(jù)頁已經(jīng)損壞了，故無法使用 redo log 進行恢復，這個數(shù)據(jù)就丟失了。

Double Write原理:

1、當刷新緩沖池臟頁時，并不直接寫到數(shù)據(jù)文件中，而是先拷貝至double write buffer。

2、然后從double write buffer分兩次寫入磁盤共享表空間中，每次寫入 1MB。

3、最后再從double write buffer寫入數(shù)據(jù)文件。雖然數(shù)據(jù)總是寫入兩次，但是由于double write 寫入的時候是順序?qū)懀瑢嶋H上也就犧牲了系統(tǒng)性能的 10%左右。

這樣就可以解決上文提到的部分寫失效的問題，因為在磁盤共享表空間中已有數(shù)據(jù)頁副本拷貝，如果數(shù)據(jù)庫在頁寫入數(shù)據(jù)文件的過程中宕機，在實例恢復時，可以從共享表空間中找到該頁副本，將其拷貝覆蓋原有的數(shù)據(jù)頁，再應用重做日志即可。

3. 小結(jié)

    今天我們聊了MySQL的二階段提交和double write機制，分別解決了在MySQL宕機重啟以及發(fā)生頁的部分寫的場景下，MySQL是如何做到不丟失數(shù)據(jù)。那如果我們的操作系統(tǒng)宕機無法啟動了，又該怎么辦呢？MySQL在集群架構(gòu)中又做了哪些優(yōu)化來保證數(shù)據(jù)不丟失呢？我們下一章再來和大家分享MySQL在集群架構(gòu)中的優(yōu)化改進。