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

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

一. MySQL的二階段提交

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

1. redo log

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

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

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

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

2. binlog

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

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

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

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

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

Binlog的寫入日志文件的機(jī)制由sync_binlog控制：

• 1. sync_binlog=0 的時(shí)候，表示每次提交事務(wù)都只 write，不 fsync；
• 2. sync_binlog=1 的時(shí)候，表示每次提交事務(wù)都會(huì)執(zhí)行 fsync，將數(shù)據(jù)刷盤；
• 3. sync_binlog=N(N>1) 的時(shí)候，表示n次事務(wù)提交之后，MySQL才進(jìn)行一次fsync動(dòng)作，將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機(jī)制就是為了減少每次臟數(shù)據(jù)刷盤帶來的性能影響，如果設(shè)置”雙一”策略會(huì)不會(huì)影響數(shù)據(jù)庫的性能呢？其實(shí)這主要得益于redo log和binlog都是順序?qū)?，磁盤的順序?qū)懕入S機(jī)寫的速度要快的多，加上MySQL內(nèi)部的組提交機(jī)制，已經(jīng)大幅降低了對磁盤的IOPS消耗了。

3. 兩階段提交

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

第一階段：Transaction Prepare Phase

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

第二階段：Commit Phase

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

具體異常場景分析：

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

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

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

二. Double Write

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

Double Write原理:

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

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

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

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

3. 小結(jié)

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