MySQL事務(wù)已提交，數(shù)據(jù)卻丟了，趕緊檢查下這個(gè)配置！！！

作者：58沈劍 2022-04-11 00:21:32

既然redo log能保證事務(wù)的ACID特性，那為什么還會(huì)出現(xiàn)，水友提問中出現(xiàn)的“數(shù)據(jù)庫崩潰，丟數(shù)據(jù)”的問題呢？一起看下redo log的實(shí)現(xiàn)細(xì)節(jié)。

有個(gè)水友提問：

沈老師，我們有一次MySQL崩潰，重啟后發(fā)現(xiàn)有些已經(jīng)提交的事務(wù)對(duì)數(shù)據(jù)的修改丟失了，不是說事務(wù)能保證ACID特性么，想問下什么情況下可能導(dǎo)致“事務(wù)已經(jīng)提交，數(shù)據(jù)卻丟失”呢？

這個(gè)問題有點(diǎn)復(fù)雜，得先從redo log說起。

為什么要有redo log？

事務(wù)提交后，必須將事務(wù)對(duì)數(shù)據(jù)頁的修改刷(fsync)到磁盤上，才能保證事務(wù)的ACID特性。

這個(gè)刷盤，是一個(gè)隨機(jī)寫，隨機(jī)寫性能較低，如果每次事務(wù)提交都刷盤，會(huì)極大影響數(shù)據(jù)庫的性能。

隨機(jī)寫性能差，有什么優(yōu)化方法呢？

架構(gòu)設(shè)計(jì)中有兩個(gè)常見的優(yōu)化方法：

先寫日志(write log first)，將隨機(jī)寫優(yōu)化為順序?qū)懀?/li>
將每次寫優(yōu)化為批量寫；

這兩個(gè)優(yōu)化，數(shù)據(jù)庫都用上了。

先說第一個(gè)優(yōu)化，將對(duì)數(shù)據(jù)的修改先順序?qū)懙饺罩纠铮@個(gè)日志就是redo log。

假如某一時(shí)刻，數(shù)據(jù)庫崩潰，還沒來得及將數(shù)據(jù)頁刷盤，數(shù)據(jù)庫重啟時(shí)，會(huì)重做redo log里的內(nèi)容，以保證已提交事務(wù)對(duì)數(shù)據(jù)的影響被刷到磁盤上。

一句話，redo log是為了保證已提交事務(wù)的ACID特性，同時(shí)能夠提高數(shù)據(jù)庫性能的技術(shù)。

redo log的三層架構(gòu)？

畫了一個(gè)丑圖，簡單說明下redo log的三層架構(gòu)：

粉色，是InnoDB的一項(xiàng)很重要的內(nèi)存結(jié)構(gòu)(In-Memory Structure)，日志緩沖區(qū)(Log Buffer)，這一層，是MySQL應(yīng)用程序用戶態(tài)；
屎黃色，是操作系統(tǒng)的緩沖區(qū)(OS cache)，這一層，是OS內(nèi)核態(tài)；
藍(lán)色，是落盤的日志文件。

redo log最終落盤的步驟如何？

首先，事務(wù)提交的時(shí)候，會(huì)寫入Log Buffer，這里調(diào)用的是MySQL自己的函數(shù)WriteRedoLog；

接著，只有當(dāng)MySQL發(fā)起系統(tǒng)調(diào)用寫文件write時(shí)，Log Buffer里的數(shù)據(jù)，才會(huì)寫到OS cache。注意，MySQL系統(tǒng)調(diào)用完write之后，就認(rèn)為文件已經(jīng)寫完，如果不flush，什么時(shí)候落盤，是操作系統(tǒng)決定的；

畫外音：有時(shí)候打日志，明明printf了，tail -f卻看不到，就是這個(gè)原因，操作系統(tǒng)還沒有刷盤。

最后，由操作系統(tǒng)(當(dāng)然，MySQL也可以主動(dòng)flush)將OS cache里的數(shù)據(jù)，最終fsync到磁盤上。

操作系統(tǒng)為什么要緩沖數(shù)據(jù)到OS cache里，而不直接刷盤呢？

這里就是將“每次寫”優(yōu)化為“批量寫”，以提高操作系統(tǒng)性能。

數(shù)據(jù)庫為什么要緩沖數(shù)據(jù)到Log Buffer里，而不是直接write呢？

這也是“每次寫”優(yōu)化為“批量寫”思路的體現(xiàn)，以提高數(shù)據(jù)庫性能。

畫外音：這個(gè)優(yōu)化思路，非常常見，高并發(fā)的MQ落盤，高并發(fā)的業(yè)務(wù)數(shù)據(jù)落盤，都可以使用。 redo log的三層架構(gòu)，MySQL做了一次批量寫優(yōu)化，OS做了一次批量寫優(yōu)化，確實(shí)能極大提升性能，但有什么副作用嗎？

畫外音：有優(yōu)點(diǎn)，必有缺點(diǎn)。

這個(gè)副作用，就是可能丟失數(shù)據(jù)：

事務(wù)提交時(shí)，將redo log寫入Log Buffer，就會(huì)認(rèn)為事務(wù)提交成功；
如果寫入Log Buffer的數(shù)據(jù)，write入OS cache之前，數(shù)據(jù)庫崩潰，就會(huì)出現(xiàn)數(shù)據(jù)丟失；
如果寫入OS cache的數(shù)據(jù)，fsync入磁盤之前，操作系統(tǒng)崩潰，也可能出現(xiàn)數(shù)據(jù)丟失；

畫外音：如上文所說，應(yīng)用程序系統(tǒng)調(diào)用完write之后(不可能每次write后都立刻flush，這樣寫日志很蠢)，就認(rèn)為寫成功了，操作系統(tǒng)何時(shí)fsync，應(yīng)用程序并不知道，如果操作系統(tǒng)崩潰，數(shù)據(jù)可能丟失。

任何脫離業(yè)務(wù)的技術(shù)方案都是耍流氓：

有些業(yè)務(wù)允許低效，但不允許一丁點(diǎn)數(shù)據(jù)丟失；
有些業(yè)務(wù)必須高性能高吞吐，能夠容忍少量數(shù)據(jù)丟失。

MySQL是如何折衷的呢？

MySQL有一個(gè)參數(shù)：

innodb_flush_log_at_trx_commit

能夠控制事務(wù)提交時(shí)，刷redo log的策略。

目前有三種策略：

策略一：最佳性能(innodb_flush_log_at_trx_commit=0)

每隔一秒，才將Log Buffer中的數(shù)據(jù)批量write入OS cache，同時(shí)MySQL主動(dòng)fsync。
這種策略，如果數(shù)據(jù)庫崩潰，有一秒的數(shù)據(jù)丟失。

策略二：強(qiáng)一致(innodb_flush_log_at_trx_commit=1)

每次事務(wù)提交，都將Log Buffer中的數(shù)據(jù)write入OS cache，同時(shí)MySQL主動(dòng)fsync。
這種策略，是InnoDB的默認(rèn)配置，為的是保證事務(wù)ACID特性。

策略三：折衷(innodb_flush_log_at_trx_commit=2)

每次事務(wù)提交，都將Log Buffer中的數(shù)據(jù)write入OS cache；
每隔一秒，MySQL主動(dòng)將OS cache中的數(shù)據(jù)批量fsync。

畫外音：磁盤IO次數(shù)不確定，因?yàn)椴僮飨到y(tǒng)的fsync頻率并不是MySQL能控制的。

這種策略，如果操作系統(tǒng)崩潰，最多有一秒的數(shù)據(jù)丟失。

畫外音：因?yàn)镺S也會(huì)fsync，MySQL主動(dòng)fsync的周期是一秒，所以最多丟一秒數(shù)據(jù)。

講了這么多，回到水友的提問上來，數(shù)據(jù)庫崩潰，重啟后丟失了數(shù)據(jù)，有很大的可能，是將innodb_flush_log_at_trx_commit參數(shù)設(shè)置為0了，這位水友最好和DBA一起檢查一下InnoDB的配置。

可能有水友要問，高并發(fā)的業(yè)務(wù)，InnoDB運(yùn)用哪種刷盤策略最合適？

高并發(fā)業(yè)務(wù)，行業(yè)最佳實(shí)踐，是使用第三種折衷配置(=2)，這是因?yàn)椋?/p>

配置為2和配置為0，性能差異并不大，因?yàn)閷?shù)據(jù)從Log Buffer拷貝到OS cache，雖然跨越用戶態(tài)與內(nèi)核態(tài)，但畢竟只是內(nèi)存的數(shù)據(jù)拷貝，速度很快；
配置為2和配置為0，安全性差異巨大，操作系統(tǒng)崩潰的概率相比MySQL應(yīng)用程序崩潰的概率，小很多，設(shè)置為2，只要操作系統(tǒng)不崩潰，也絕對(duì)不會(huì)丟數(shù)據(jù)。