系列文章

• 原來一條select語句在MySQL是這樣執(zhí)行的《死磕MySQL系列 一》
• 一生摯友redo log、binlog《死磕MySQL系列 二》

前言

咔咔閑談

上期根據(jù)一條查詢語句查詢流程分析MySQL的整體架構。同樣搞坝，本期也使用一條查詢SQL語句來做引子】啵可以肯定的是桩撮，查詢語句執(zhí)行的流程更新語句同樣也會執(zhí)行。

因此本期的著重點就不在MySQL架構圖上峰弹，文章標題也給出了大家重點店量，就是要了解redo log、binlog鞠呈。

一融师、redo log

第一步，創(chuàng)建一個表 user蚁吝，主鍵是 id诬滩，下面是創(chuàng)建語句。

CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) NOT NULL,
 `age` tinyint(4) NOT NULL,
 `time` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

插入一條數(shù)據(jù)

insert into user (`name`,`age`,`time`) values ("咔咔","25",unix_timestamp(now()))

若要將插入的這條數(shù)據(jù)的age改為26灭将，則需要執(zhí)行語句

update user set age = 26 where id = 1;

第一期文章中提到一條查詢語句的執(zhí)行流程疼鸟，該流程與更新語句相同。這里將那幅圖拿過來在熟悉一下庙曙。

image

每個模塊的功能可以回到第一期文章去查看空镜。

在MySQL8.0中redo log、binlog日志文件都位于/var/lib/mysql此目錄下捌朴，如圖

image

文件名為ib_logfile的是重做日志吴攒，undo開頭的就是回滾日志，對于回滾日志后期進行詳細的討論砂蔽。

redo log（重做日志）是實現(xiàn)事務持久性必備要素洼怔，當一個事務提交后，并非直接修改數(shù)據(jù)庫的數(shù)據(jù)左驾，而是首先保證在 redo log中記錄相關的操作镣隶。

Innodb存儲引擎中的redo log大小是固的，上圖顯示配置了一組兩個文件诡右，每個文件大小默認為48M安岂，使用innodb_log_file_size參數(shù)來控制單個文件大小，在MySQL5.6.8以及之后版本都默認為48M帆吻。

image

然后redo log可以記錄48M的操作域那，redo log是一個閉環(huán)的循環(huán)寫。所設定的文件個數(shù)和文件大小不再增加猜煮。

image

write pos將記錄當前位置次员，同時向后移動败许，在ib-log-file-3文件末尾后，然后返回ib-logfilg-0文件開始寫淑蔚。

check point記錄的是當前擦除的位置市殷，要使文件循環(huán)寫入，必須一邊擦除束倍。清楚數(shù)據(jù)的前提是要將記錄更新到數(shù)據(jù)文件被丧。

上面的綠色部分就是可寫的部分盟戏，假設如果 writepos追上了 checkpoint绪妹，那該怎么辦？

你必須理解write pos的推進是因為在執(zhí)行更新操作柿究，這樣就不能再執(zhí)行更新操作邮旷，直到記錄更新到數(shù)據(jù)文件，然后check point進行擦除后才可以繼續(xù)執(zhí)行更新操作蝇摸。

對于innodb_log_file_size的設置也是有一些計算規(guī)則的婶肩，下面將為你介紹。

若innodb_log_file_size設置太小貌夕，將導致redo log文件頻繁切換律歼，頻繁的觸發(fā)數(shù)據(jù)庫的檢查點（check point），導致記錄更新到數(shù)據(jù)文件的次數(shù)增加啡专，從而影響IO性能险毁。

同樣，如果有一個大的事務们童，并且所有 redo log日志都已寫滿畔况，但是還沒有完成，將導致日志無法切換慧库，從而導致 MySQL直接堵死跷跪。

innodb_log_file_size設置太大，雖然極大地提高了 IO性能齐板，但是在 MySQL重啟或宕機時吵瞻，恢復時間會因為 redo log文件過大而延長。而這種恢復時間通常是無法控制的甘磨。

在設置合理的redo log大小和數(shù)量后听皿，Innodb能夠保證，即使數(shù)據(jù)庫發(fā)生異常重啟宽档，以前提交的記錄也不會丟失尉姨，這一點也稱為crash-safe。

在這里吗冤，對crash-safe的理解先不提及它是什么又厉，后面的文章會讓你明白九府。

二、如何根據(jù)項目情況設置innodb_log_file_size

對于參數(shù)innodb_log_files_in_group設置3~4個就夠用了覆致，不用進行優(yōu)化侄旬。

著重討論innodb_log_file_size的大小設置或優(yōu)化設置。

在 MySQL8.0之前煌妈，通常是計算在一段時間內(nèi)生成的事務日志(redo log)大小儡羔，而 MySQL日志文件最小應承載一小時的業(yè)務日志量。

此處的一段時間必須視自己的業(yè)務情況而定璧诵，外界有用1分鐘的日志量也有1小時的日志量來計算汰蜘。

首先看一下 MySQL客戶端的一個命令 pager，在 MySQL日常操作中之宿，通過設置 pager的顯示方式族操，可以大大提高工作效率。

目前比被，要查看 sequence在一分鐘之內(nèi)的值色难，您就可以執(zhí)行 pager grep sequence，它對mysql> show engine innodb status\ G select sleep (60); show engine innodbstatus\ G;返回的結(jié)果等缀。

禁止 pager設置執(zhí)行 nopager，如果不執(zhí)行該命令尺迂，則只有等到下一次重新啟動該命令才會失效笤妙。

image

此處咔咔是在虛擬機上做的操作危喉，可以看到一分鐘內(nèi)是沒有任何操作，所以值前后相同州疾，你可以在測試服務器做測試。

這樣計算出來的select (后邊數(shù)據(jù)-前面的數(shù)據(jù))/1024/1024*60 asMB_per_hour;值是一個小時后 redo log的大小

但是用這種方法計算一定是不合適的严蓖，在一分鐘內(nèi)業(yè)務繁忙或者業(yè)務空閑時間計算出的值都會產(chǎn)生較大誤差。

合適的方法是在一天中確定幾個時間點颗胡，用一個腳本定時執(zhí)行毫深，然后記錄相應的值毒姨，再取平均值哑蔫，計算出的誤差將減至最小。

什么是 sequece?
當每個 binlog生成時，該值從1開始闸迷，然后遞增嵌纲，每增加一個事務， sequenumber就加上1腥沽。

二逮走、binlog

您可以從總體上了解到 MySQL架構分為兩層，一個是 server層今阳，另一個是存儲引擎層师溅。

server層當然是負責功能方面的，而存儲引擎層則負責處理與存儲相關的操作盾舌。

而且上面提到的redo log是Innodb存儲引擎層特有的墓臭，其它存儲引擎是不具備的，而server層也有自己的日志記錄矿筝，就是將要聊到的binlog起便。

redo log和binlog的區(qū)別

redo log是Innodb引擎特有的棚贾，而binlog是MySQLserver層特有的窖维，所有引擎都可以使用。

redo log是物理日志妙痹，它記錄的是一條更新操作所做的修改铸史，binlog是邏輯日志，記錄的是一條更新語句執(zhí)行邏輯

redo log是循環(huán)寫的怯伊，并且空間是固定的琳轿，比如上面配置4個1GB的redo log文件，binlog是追加寫的耿芹，這個文件寫完了崭篡，換下一個文件，不會覆蓋以前的日志吧秕。這也就是你經(jīng)沉鹕粒看到只要你有完整的binlog文件就可以給你恢復到你想要的數(shù)據(jù)。

MySQL為什么會有倆份日志呢砸彬？

在沒有Innodb存儲引擎之前颠毙，MySQL默認存儲引擎是MyIsam，但MyIsam是沒有重啟恢復能力的砂碉，binlog日志也僅用于歸檔蛀蜜。

Innodb是另一家公司以插件的形式引入到Mysql增蹭，既然binlog沒有重啟恢復的能力，那么我就使用redo log來實現(xiàn)重啟恢復的功能霎奢。

這就導致了當你使用Innodb存儲引擎時會寫倆份日志。

三椰憋、什么是兩階段提交

對redo log、binlog有了一定的認識后再來看看一條更新語句的執(zhí)行流程橙依。

update user set age = age + 1 where id = 1;

• 執(zhí)行器先到引擎層找到id = 1這一行，由于ID是主鍵女责，所以會在主鍵索引樹找到這一行。如果ID=2這一行所在的數(shù)據(jù)頁本來就在內(nèi)存中抵知，就直接返回給執(zhí)行器。否則刷喜，需要先從磁盤中讀入內(nèi)存立砸，然后再返回。

• 執(zhí)行器拿到存儲引擎返回id = 2結(jié)果后颗祝，給age加上1，原來是25螺戳，現(xiàn)在就是26,在調(diào)用引擎接口寫入這行新數(shù)據(jù)。

• 引擎將這行數(shù)據(jù)先更新到內(nèi)存中倔幼，同時將這個更新操作記錄到redo log中，此時redo log處于prepare狀態(tài)奸忽。然后告知執(zhí)行器執(zhí)行完成了，隨時可以提交事務栗菜。

• 接著執(zhí)行器生成這個操作的binlog，并把binlog寫入磁盤疙筹。

• 執(zhí)行器調(diào)用引擎的提交事務接口，引擎把剛剛寫入的redo log改成提交commit狀態(tài)而咆，更新完成。

到這里你應該就清晰了暴备，一條更新SQL會先寫redo log再寫binlog，這也就是標題為什么叫一生摯友redo log涯捻、binlog。

image

四凌外、為什么需要兩階段提交

是為了讓redo log跟binlog兩份日志之間的邏輯一致，看下面?zhèn)z種情況康辑。

先寫redo log后寫binlog

• 更新語句為age = age +1
• 將數(shù)據(jù)寫入redo log轿亮，MySQL進程異常重啟
• 此時binlog還沒有開始寫
• 系統(tǒng)重啟后進行數(shù)據(jù)恢復此時的值為26
• 需要搭建從庫時需要拿binlog進行恢復數(shù)據(jù)，但此時age = age +1 這行的操作是沒有記錄到binlog的
• 那么此時的從庫就會少這一次的更新哀托，恢復出來的age依然是25劳秋，造成于主庫數(shù)據(jù)不一致。

先寫binlog后寫redo log

• 更新語句為age = age +1
• 將數(shù)據(jù)寫入binlog玻淑，MySQL異常重啟
• 此時redo log 還沒寫
• MySQL系統(tǒng)重啟，這個更新操作是對于redo log是不存在的补履，所以重啟后的值依然是25
• 但binlog 中的值已將是26了
• 需要搭建從庫時，從庫的值是26箫锤，主庫的值是25，造成主從數(shù)據(jù)不一致

所以說谚攒，如果不使用兩階段提交，那么原庫和用它的binlog日志恢復出來的庫數(shù)據(jù)是不一致的野蝇。

五、《孔乙己》讓你明白redo log是什么

來看一個初中九年級語文課文中《孔乙己》這篇文章绕沈，就算不記得內(nèi)容，標題總記得哈乍狐！

這個案例也是看丁老師文章中提到的，為什么丁老可以靈活的使用這個案例來講redo log而我們想不到呢浅蚪？

其本質(zhì)原因是對知識點沒有理解透徹，使用生活案例來解釋技術是讓人最容易理解并不難遺忘的耘戚。

《孔乙己》中的主人公就叫他酒店掌柜，掌柜的有倆件法寶讓比其他老板工作效率高很多收津。一個是小黑板另一個是賬本。

試想一下如果有客人要賒賬撞秋，是直接寫到黑板效率高嚣鄙，還是翻密密麻麻的賬本來的快呢？

掌柜肯定會選擇先記錄到黑板上哑子，等人少或者不忙時再把黑板的記錄寫到賬本中。

反之老板沒有黑板的話卧蜓，只能在密密麻麻的賬本中先找到賒賬人的名字，如果之前有賒賬記錄追加弥奸，找了一遍發(fā)現(xiàn)沒有才進行新增。

這個過程不僅繁瑣而且效率低的讓人難以接受盛霎，如果酒店客人多老板是記錄不過來的。

同樣愤炸，在MySQL中也會存在這個問題，每次執(zhí)行更新語句都需要先找到那條記錄摇幻，然后再更新挥萌，整個過程IO成本枉侧、查找成本都很高。所以MySQL也利用了酒店掌柜的智慧使用黑板來提升執(zhí)行效率榨馁。

畫一幅圖讓大家能更好的理解掌柜、黑板屑柔、在MySQL中的對應關系。

酒店掌柜于MySQL對應的關系

六掸宛、redo log參數(shù)詳解

事務的持久性就是通過重做日志來實現(xiàn)的招拙。

當提交事務之后，并不是直接修改數(shù)據(jù)庫的數(shù)據(jù)的别凤，而是先保證將相關的操作記錄到redo日志中。

數(shù)據(jù)庫會根據(jù)相應的機制將內(nèi)存的中的臟頁數(shù)據(jù)刷新到磁盤中规哪。

重做日志寫入流程

上圖是一個簡單的重做日志寫入流程。

在上圖中提到倆個陌生概念蝠嘉，Buffer pool、redo log buffer是晨，這個倆個都是Innodb存儲引擎的內(nèi)存區(qū)域的一部分舔箭。

而redo log file是位于磁盤位置蚊逢。

也就說當有DML（insert、update烙荷、delete）操作時，數(shù)據(jù)會先寫入Buffer pool终抽，然后在寫到重做日志緩沖區(qū)桶至。

重做日志緩沖區(qū)會根據(jù)刷盤機制來進行寫入重做日志中匾旭。

這個機制的設置參數(shù)為innodb_flush_log_at_trx_commit，參數(shù)分別為0,1价涝，2

刷盤策略

上圖即為重做日志的寫入策略。

• 當這個參數(shù)的值為0的時伪窖，提交事務之后，會把數(shù)據(jù)存放到redo log buffer中覆山，然后每秒將數(shù)據(jù)寫進磁盤文件
• 當這個參數(shù)的值為1的時泥栖，提交事務之后，就必須把redo log buffer從內(nèi)存刷入到磁盤文件里去聊倔，只要事務提交成功，那么redo log就必然在磁盤里了耙蔑。
• 當這個參數(shù)的值為2的情況，提交事務之后甸陌，把redo log buffer日志寫入磁盤文件對應的os cache緩存里去，而不是直接進入磁盤文件钱豁，1秒后才會把os cache里的數(shù)據(jù)寫入到磁盤文件里去。

服務器異常停止對事務如何應對（事務寫入過程）

• 當參數(shù)為0時牲尺，前一秒的日志都保存在日志緩沖區(qū)，也就是內(nèi)存上溃卡，如果機器宕掉，可能丟失1秒的事務數(shù)據(jù)瘸羡。
• 當參數(shù)為1時，數(shù)據(jù)庫對IO的要求就非常高了犹赖，如果底層的硬件提供的IOPS比較差队他，那么MySQL數(shù)據(jù)庫的并發(fā)很快就會由于硬件IO的問題而無法提升麸折。
• 當參數(shù)為2時，數(shù)據(jù)是直接寫進了os cache緩存磕谅，這部分屬于操作系統(tǒng)部分，如果操作系統(tǒng)部分損壞或者斷電的情況會丟失1秒內(nèi)的事務數(shù)據(jù)膊夹，這種策略相對于第一種就安全了很多，并且對IO要求也沒有那么高放刨。

小結(jié)

關于性能：0>2>1

關于安全：1>2>0

根據(jù)以上結(jié)論尸饺，所以說在MySQL數(shù)據(jù)庫中，刷盤策略默認值為1浪听，保證事務提交之后，數(shù)據(jù)絕對不會丟失迹栓。

堅持學習、堅持寫作克伊、堅持分享是咔咔從業(yè)以來所秉持的信念。愿文章在偌大的互聯(lián)網(wǎng)上能給你帶來一點幫助愿吹，我是咔咔，下期見犁跪。