汗顏！工作10年去面試，被“MySQL怎么保證事物一致性”難倒了

阿牛去一家中意的公司面試，本以為憑借以往豐富的經(jīng)驗(yàn)，肯定手到擒來(lái)，結(jié)果第一個(gè)問(wèn)題，我就“出門右拐”了。

成都創(chuàng)新互聯(lián)公司成立與2013年，是專業(yè)互聯(lián)網(wǎng)技術(shù)服務(wù)公司，擁有項(xiàng)目成都網(wǎng)站制作、成都做網(wǎng)站網(wǎng)站策劃，項(xiàng)目實(shí)施與項(xiàng)目整合能力。我們以讓每一個(gè)夢(mèng)想脫穎而出為使命，1280元赤城做網(wǎng)站,已為上家服務(wù),為赤城各地企業(yè)和個(gè)人服務(wù),聯(lián)系電話:13518219792

問(wèn)題就是：MySQL是怎么保證事務(wù)一致性的？

回到家阿牛翻閱資料，終于搞懂了，在這里分享給大家。

定義

在搞清楚問(wèn)題答案之前，先搞清楚以下幾個(gè)名詞以及大致的用處

redo log：

通常是物理日志，記錄的是數(shù)據(jù)頁(yè)的物理修改，而不是某一行或某幾行修改成怎樣怎樣，它用來(lái)恢復(fù)提交后的物理數(shù)據(jù)頁(yè)(恢復(fù)數(shù)據(jù)頁(yè)，且只能恢復(fù)到最后一次提交的位置)、Innodb特有的，他在存儲(chǔ)引擎層。循環(huán)寫的，空間固定會(huì)用完。作用是crash-safe能力

binlog：

是邏輯日志，記錄的是這個(gè)語(yǔ)句的原始邏輯，比如“給 ID=2 這一行的 c 字段加 1 ” 是 MySQL 的 Server 層實(shí)現(xiàn)的，所有引擎都可以使用。是可以追加寫入的,“追加寫”是指 binlog 文件寫到一定大小后會(huì)切換到下一個(gè)，并不會(huì)覆蓋以前的日志。作用是數(shù)據(jù)歸檔

undo log：

有兩個(gè)作用：提供回滾和多個(gè)行版本控制(MVCC)。

在數(shù)據(jù)修改的時(shí)候，不僅記錄了redo，還記錄了相對(duì)應(yīng)的undo，如果因?yàn)槟承┰驅(qū)е率聞?wù)失敗或回滾了，可以借助該undo進(jìn)行回滾。

SQL執(zhí)行的過(guò)程

了解了以上名詞之后，讓我們看一下“一條更新SQL語(yǔ)句執(zhí)行的過(guò)程是什么？”

如圖1有幾個(gè)關(guān)鍵步驟：

1、先查找記錄所在的Innodb頁(yè)在不在內(nèi)存里；如果不在內(nèi)存里則將記錄所在的頁(yè)加載在內(nèi)存里；根據(jù)SQL語(yǔ)句在內(nèi)存中將記錄更新

2、將更新前的記錄寫入undolog

3、根據(jù)記錄的更新值將變更寫入redolog（buffer）中，并將狀態(tài)變更為prepare

4、將變更記錄到邏輯日志

5、redolog日志中的狀態(tài)修改為commit，返回結(jié)束

至此：一條更新語(yǔ)句的過(guò)程結(jié)束

上面的步驟中有些同學(xué)可能會(huì)有一些疑問(wèn)：為什么更新一條記錄要把一整頁(yè)數(shù)據(jù)加載到內(nèi)存里答：因?yàn)镮nnodb引擎中，最小的存儲(chǔ)單位是頁(yè)為什么一定要加載到內(nèi)存里？答：因?yàn)樗械挠?jì)算操作都是在內(nèi)存里，操作完成后最終才寫回磁盤為什么要寫入redolog，直接寫入磁盤，然后寫入binlog就好了??？答：這將在下面會(huì)提到，請(qǐng)往后看

為了加深理解，準(zhǔn)備了下面2張圖輔助理解

以圖3為例，讓我們看看在每個(gè)步驟出現(xiàn)異常的時(shí)候，到底怎么保證事物一致性的吧！1、步驟123，所有的操作最多還只是內(nèi)存里，如果出現(xiàn)宕機(jī)、斷電等異常，? 記錄不會(huì)有任何變動(dòng)，事物是一致的2、步驟4剛執(zhí)行完，斷電了，因?yàn)閞edolog還處在prepare狀態(tài)，???這時(shí)候事物也是一致的3、步驟5記錄binlog的過(guò)程中斷電了，這時(shí)候要保證主從一致性，? 事物也是不生效的，最終也是一致的4、步驟6、7如果中間任何一個(gè)時(shí)刻斷電了，這時(shí)候情況就不一樣了，事物是生效的，因?yàn)閞edolog、binlog的數(shù)據(jù)都是完整的，服務(wù)器重啟后可以按照xid來(lái)去查看binlog、redolog中是否都存在，? 都存在該事物就是生效的。上面就是怎么保證事務(wù)一致性的根本原因

為什么要使用redolog？

回答這個(gè)問(wèn)題之前，我們先看看redolog用圖形表示的

圖4是redolog的形象一點(diǎn)的表現(xiàn)，并不是說(shuō)redolog 長(zhǎng)這個(gè)樣子，只是為了更形象；一般情況下redolog一組4個(gè)文件，每個(gè)文件1個(gè)G，其中write pos是指redolog當(dāng)前寫到什么位置了，check point是指上次刷臟結(jié)束的位置，當(dāng)write log和check point重合時(shí)，所有的進(jìn)程停止，開(kāi)始新一輪的刷臟操作。刷完后redolog清空開(kāi)始下一輪的寫入，往返重復(fù)。

可能這樣表示有點(diǎn)抽象，讓我們看下圖5

從上圖中可以看的更形象一點(diǎn)，在sql執(zhí)行的時(shí)候，會(huì)有磁盤IO將數(shù)據(jù)頁(yè)加載到內(nèi)存，然后在內(nèi)存中將數(shù)據(jù)修改，修改后的數(shù)據(jù)頁(yè)在內(nèi)存中叫做臟頁(yè)（叫臟頁(yè)因?yàn)楹痛疟P中的數(shù)據(jù)不一致?。?，又因?yàn)樵趦?nèi)存中容易丟失，所以將數(shù)據(jù)頁(yè)的變更記錄如redolog中，隨著記錄插入、更新等操作的增多，redolog空間慢慢的滿了，這時(shí)候就開(kāi)始刷臟操作了，page cleaner thread線程會(huì)將所有的臟頁(yè)數(shù)據(jù)刷新到磁盤，使得變更最終被持久化到磁盤。

講到這里一定還會(huì)有人不太理解，刷臟之前斷電了咋辦？

這就是redolog的另一個(gè)重要的作用，crash-safe能力，實(shí)現(xiàn)的邏輯是這樣的，斷電后內(nèi)存的數(shù)據(jù)都沒(méi)了，重啟后讀取redolog文件，因?yàn)閞edolog文件記錄的是在Innodb頁(yè)x的m處做了y的修改，所以根據(jù)redolog將涉及到的Innodb頁(yè)重新加載到內(nèi)存，根據(jù)redolog的記錄將內(nèi)存中的數(shù)據(jù)重新修改，這樣就能恢復(fù)斷電前的數(shù)據(jù)了。

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?完

下期預(yù)告：還是MySQL，敬請(qǐng)期待

本文首發(fā)自： 程序員阿牛

怎么避免mysql從庫(kù)同步 怎么保證數(shù)據(jù)一致性

用 pt-table-checksum 時(shí)，會(huì)不會(huì)影響業(yè)務(wù)性能？

實(shí)驗(yàn)

實(shí)驗(yàn)開(kāi)始前，給大家分享一個(gè)小經(jīng)驗(yàn)：任何性能評(píng)估，不要相信別人的評(píng)測(cè)結(jié)果，要在自己的環(huán)境上測(cè)試，并（大概）知曉原理。

我們先建一對(duì)主從：

然后用 mysqlslap跑一個(gè)持續(xù)的壓力：

開(kāi)另外一個(gè)會(huì)話，將 master 上的 general log 打開(kāi)：

然后通過(guò) pt-table-checksum 進(jìn)行一次比較：

查看 master 的 general log，由于 mysqlslap 的影響，general log 中有很多內(nèi)容，我們找到與 pt-table-checksum 相關(guān)的線程：

將該線程的操作單獨(dú)列出來(lái)：

操作比較多，我們一點(diǎn)一點(diǎn)來(lái)說(shuō)明：

這里工具調(diào)小了 innodb 鎖等待時(shí)間。使得之后的操作，只要在 innodb 上稍微有鎖等待，就會(huì)馬上放棄操作，對(duì)業(yè)務(wù)影響很小。

另外工具調(diào)小了 wait_timeout 時(shí)間，倒是沒(méi)有特別的作用。

工具將隔離級(jí)別調(diào)整為了 RR 級(jí)別，事務(wù)的維護(hù)代價(jià)會(huì)比 RC 要高，不過(guò)后面我們會(huì)看到工具使用的每個(gè)事務(wù)都很小，加上之前提到 innodb 鎖等待時(shí)間調(diào)到很小，對(duì)線上業(yè)務(wù)產(chǎn)生的成本比較小。

RR 級(jí)別是數(shù)據(jù)對(duì)比的基本要求。

工具通過(guò)一系列操作，了解表的概況。工具是一個(gè)數(shù)據(jù)塊一個(gè)數(shù)據(jù)塊進(jìn)行校驗(yàn)，這里獲取了第一個(gè)數(shù)據(jù)塊的下邊界。

接下來(lái)工具獲取了下一個(gè)數(shù)據(jù)塊的下邊界，每個(gè) SQL前都會(huì) EXPLAIN 一下，看一下執(zhí)行成本，非常小心翼翼。

之后工具獲取了一個(gè)數(shù)據(jù)塊的 checksum，這個(gè)數(shù)據(jù)塊不大，如果跟業(yè)務(wù)流量有沖突，會(huì)馬上出發(fā) innodb 的鎖超時(shí)，立刻退讓。

以上是 pt-table-checksum 的一些設(shè)計(jì)，可以看到這幾處都是精心維護(hù)了業(yè)務(wù)流量不受影響。

工具還設(shè)計(jì)了其他的一些機(jī)制保障業(yè)務(wù)流量，比如參數(shù) --max-load 和 --pause-file 等，還有精心設(shè)計(jì)的數(shù)據(jù)塊劃分方法，索引選擇方法等。大家根據(jù)自己的情況配合使用即可達(dá)到很好的效果。

總結(jié)

本期我們介紹了簡(jiǎn)單分析 pt-table-checksum 是否會(huì)影響業(yè)務(wù)流量，坊間會(huì)流傳工具的各種參數(shù)建議或者不建議使用，算命的情況比較多，大家都可以用簡(jiǎn)單的實(shí)驗(yàn)來(lái)分析其中機(jī)制。

還是那個(gè)觀點(diǎn)，性能測(cè)試不能相信道聽(tīng)途說(shuō)，得通過(guò)實(shí)驗(yàn)去分析。

mysql 如何解決數(shù)據(jù)一致性

MySQL主從復(fù)制

現(xiàn)在常用的MySQL高可用方案，十有八九是基于 MySQL的主從復(fù)制（replication）來(lái)設(shè)計(jì)的，包括常規(guī)的一主一從、雙主模式，或者半同步復(fù)制（semi-sync replication）。

我們常常把MySQL replication說(shuō)成是MySQL同步（sync），但事實(shí)上這個(gè)過(guò)程是異步（async）的。大概過(guò)程是這樣的：

在master上提交事務(wù)后，并且寫入binlog，返回事務(wù)成功標(biāo)記；

將binlog發(fā)送到slave，轉(zhuǎn)儲(chǔ)成relay log；

在slave上再將relay log讀取出來(lái)應(yīng)用。

步驟1和步驟3之間是異步進(jìn)行的，無(wú)需等待確認(rèn)各自的狀態(tài)，所以說(shuō)MySQL replication是異步的。

MySQL semi-sync replication在之前的基礎(chǔ)上做了加強(qiáng)完善，整個(gè)流程變成了下面這樣：

首先，master和至少一個(gè)slave都要啟用semi-sync replication模式；

某個(gè)slave連接到master時(shí)，會(huì)主動(dòng)告知當(dāng)前自己是否處于semi-sync模式；

在master上提交事務(wù)后，寫入binlog后，還需要通知至少一個(gè)slave收到該事務(wù)，等待寫入relay log并成功刷新到磁盤后，向master發(fā)送“slave節(jié)點(diǎn)已完成該事務(wù)”確認(rèn)通知；

master收到上述通知后，才可以真正完成該事務(wù)提交，返回事務(wù)成功標(biāo)記；

在上述步驟中，當(dāng)slave向master發(fā)送通知時(shí)間超過(guò)rpl_semi_sync_master_timeout設(shè)定值時(shí)，主從關(guān)系會(huì)從semi-sync模式自動(dòng)調(diào)整成為傳統(tǒng)的異步復(fù)制模式。

半同步復(fù)制看起來(lái)很美好有木有，但如果網(wǎng)絡(luò)質(zhì)量不高，是不是出現(xiàn)抖動(dòng)，觸發(fā)上述第5條的情況，會(huì)從半同步復(fù)制降級(jí)為普通復(fù)制；此外，采用半同步復(fù)制，會(huì)導(dǎo)致master上的tps性能下降非常嚴(yán)重，最嚴(yán)重的情況下可能會(huì)損失50%以上。

這樣來(lái)看，除非需要非常嚴(yán)格保證數(shù)據(jù)一致性等迫不得已的場(chǎng)景，就不太建議使用半同步復(fù)制了。當(dāng)然了，事實(shí)上我們也可以通過(guò)加強(qiáng)程序端的邏輯控制，來(lái)避免主從數(shù)據(jù)不一致時(shí)發(fā)生邏輯錯(cuò)誤，比如說(shuō)如果在從上讀取到的數(shù)據(jù)和主不一致的話，那么就觸發(fā)主從間的一次數(shù)據(jù)修復(fù)工作?；蛘撸覀円部梢杂?pt-table-checksum pt-table-sync 兩個(gè)工具來(lái)校驗(yàn)并修復(fù)數(shù)據(jù)，只要運(yùn)行頻率適當(dāng)，是可行的。

真想要提高多節(jié)點(diǎn)間的數(shù)據(jù)一致性，可以考慮采用PXC方案?，F(xiàn)在已知用PXC規(guī)模較大的有qunar、sohu，如果團(tuán)隊(duì)里初期沒(méi)有人能比較專注PXC的話，還是要謹(jǐn)慎些，畢竟和傳統(tǒng)的主從復(fù)制差異很大，出現(xiàn)問(wèn)題時(shí)需要花費(fèi)更多精力去排查解決。

如何保證主從復(fù)制數(shù)據(jù)一致性

上面說(shuō)完了異步復(fù)制、半同步復(fù)制、PXC，我們回到主題：在常規(guī)的主從復(fù)制場(chǎng)景里，如何能保證主從數(shù)據(jù)的一致性，不要出現(xiàn)數(shù)據(jù)丟失等問(wèn)題呢？

在MySQL中，一次事務(wù)提交后，需要寫undo、寫redo、寫binlog，寫數(shù)據(jù)文件等等。在這個(gè)過(guò)程中，可能在某個(gè)步驟發(fā)生crash，就有可能導(dǎo)致主從數(shù)據(jù)的不一致。為了避免這種情況，我們需要調(diào)整主從上面相關(guān)選項(xiàng)配置，確保即便發(fā)生crash了，也不能發(fā)生主從復(fù)制的數(shù)據(jù)丟失。

1. 在master上修改配置

innodb_flush_log_at_trx_commit = 1

sync_binlog = 1

上述兩個(gè)選項(xiàng)的作用是：保證每次事務(wù)提交后，都能實(shí)時(shí)刷新到磁盤中，尤其是確保每次事務(wù)對(duì)應(yīng)的binlog都能及時(shí)刷新到磁盤中，只要有了binlog，InnoDB就有辦法做數(shù)據(jù)恢復(fù)，不至于導(dǎo)致主從復(fù)制的數(shù)據(jù)丟失。

2. 在slave上修改配置

master_info_repository = "TABLE"

relay_log_info_repository = "TABLE"

relay_log_recovery = 1

上述前兩個(gè)選項(xiàng)的作用是：確保在slave上和復(fù)制相關(guān)的元數(shù)據(jù)表也采用InnoDB引擎，受到InnoDB事務(wù)安全的保護(hù)，而后一個(gè)選項(xiàng)的作用是開(kāi)啟relay log自動(dòng)修復(fù)機(jī)制，發(fā)生crash時(shí)，會(huì)自動(dòng)判斷哪些relay log需要重新從master上抓取回來(lái)再次應(yīng)用，以此避免部分?jǐn)?shù)據(jù)丟失的可能性。

通過(guò)上面幾個(gè)選項(xiàng)的調(diào)整，就可以確保主從復(fù)制數(shù)據(jù)不會(huì)發(fā)生丟失了。但是，這并不能保證主從數(shù)據(jù)的絕對(duì)一致性，因?yàn)?，有可能設(shè)置了ignore\do\rewrite等replication規(guī)則，或者某些SQL本身存在不確定因素，或者人為在slave上修改數(shù)據(jù)，最終導(dǎo)致主從數(shù)據(jù)不一致。這種情況下，可以采用pt-table-checksum?和?pt-table-sync?工具來(lái)進(jìn)行數(shù)據(jù)的校驗(yàn)和修復(fù)。