Photo by hippopx.com

Photo by hippopx.com

《MySQL實戰(zhàn)45講》筆記蔓钟。

1. redo log——只是一塊粉板

孔乙己又來酒館喝酒滥沫，兜里沒錢手機也沒電了，只能向掌柜的賒賬世分。掌柜有一塊粉板缀辩，當客人要賒賬的時候就往上寫一筆臀玄，等客人少的時候或者粉板寫滿了就記到賬本里去。還好有這塊粉板荣恐，不然每次客人要賒賬累贤，掌柜都要翻看賬本，在密密麻麻的賬本里找到賒賬客人的名字絕對不是一件容易的事硼被，有了粉板讶请，掌柜只要往粉板上記一筆：“孔乙己 賒 兩文”夺溢，空閑的時候再更新到賬本里去，簡單多了嘉汰。

同樣的状勤，MySQL也有一塊“粉板”—— redo log双泪。更新的時候焙矛，先寫到 redo log 和內(nèi)存里残腌，這次更新就算是結束了抛猫。等到合適的時機再寫到磁盤里，大大減小了寫磁盤的次數(shù)逾滥。

redo log 是固定大小败匹、“循環(huán)寫”的，就像粉板一樣毅待，頂多也就記個十幾二十條归榕，多了就記不下了刹泄，這時會把粉板上的帳都寫到賬本里，再擦掉粉板盅蝗，從頭開始記姆蘸。假設 redo log 配置了4組文件逞敷，每個文件 1G ，一共可記錄 4G 的操作裂问，寫滿了就會擦掉一部分記錄。

redo log 是物理日志痊乾，記錄的是“在某個數(shù)據(jù)頁上做了什么修改”椭更。

有了 redo log虑瀑，InnoDB 就可以保證即使數(shù)據(jù)庫發(fā)生了異常重啟，之前提交的記錄都不會丟失，這個能力稱為 crash-safe描馅。

2. binlog

binlog 是 MySQL 的 Server 層實現(xiàn)的铭污，所有引擎都可以使用。

binlog 是邏輯日志岂膳，記錄的是這個語句的原始邏輯谈截，比如”給 ID=2 這一行的 c 字段加1“涧偷。

binlog 是“追加寫”的，一個文件寫完了會切換到下一個喻鳄，不會覆蓋以前的日志确封。

為什么有了 redo log 還需要 binlog?

其實 redo log 才是那個新來的仔爪喘。MySQL 自帶了 binlog 日志用于歸檔，沒有 crash-safe 的能力泛啸。InnoDB 引擎以插件的形式引入 MySQL 時，為了能夠實現(xiàn) crash-safe 的能力吕粹，引入了 redo log 匹耕。

一般我們用 binlog 做主從復制荠雕，數(shù)據(jù)恢復等操作。

binlog 是如何做數(shù)據(jù)恢復的既鞠？

一般我們做數(shù)據(jù)庫備份是一周一備嘱蛋，一天一備五续，也可能一月一備。

假設今天中午12點凶伙，我們發(fā)現(xiàn)部分數(shù)據(jù)被誤刪了函荣。需要恢復到昨天晚上8點這個時間段扳肛。但是數(shù)據(jù)庫是每天凌晨3點的時候備份敞峭，離我們最近的一份備份數(shù)據(jù)已經(jīng)缺失，只能恢復到昨天凌晨3點殖蚕。這個時候我們就可以拿出昨天凌晨3點到晚上8點這個時間段的 binlog沉迹，重放到數(shù)據(jù)缺失前的那個時刻鞭呕。在把這份數(shù)據(jù)恢復到線上數(shù)據(jù)庫去。

3. 更新操作的執(zhí)行流程

了解了 redo log 和 binlog 這兩個日志的概念瓦糕，我們再來看看執(zhí)行器和 InnoDB 引擎在執(zhí)行這個簡單的 update 語句時的內(nèi)部流程咕娄。

1. 執(zhí)行器先找引擎取 ID=2 這一行。如果數(shù)據(jù)在內(nèi)存就直接返回费变，如果不在內(nèi)存就先從磁盤讀入內(nèi)存圣贸，再返回吁峻。
2. 執(zhí)行器拿到數(shù)據(jù)，給這行的 c 值加 1。
3. 引擎將這行數(shù)據(jù)的改動更新到內(nèi)存中耕餐，同時將這個更新操作記錄到 redo log 里面辟狈，此時 redo log 處于prepare 狀態(tài)哼转。然后告知執(zhí)行器執(zhí)行完成了，隨時可以提交事務趟妥。
4. 執(zhí)行器生成這個操作的 binlog佣蓉，并把 binlog 寫入磁盤勇凭。
5. 執(zhí)行器調(diào)用引擎的提交事務接口，引擎把剛剛寫入的 redo log 改成 commit 狀態(tài)寓盗，更新完成。

下圖出自《MySQL實戰(zhàn)45講》傀蚌，淺色框表示是在 InnoDB 內(nèi)部執(zhí)行的喳张，深色框表示實在執(zhí)行器中執(zhí)行的。

image

4. redo log 和 binlog 的兩階段提交

為什么需要兩階段提交摸航？

我們先假設沒有兩階段提交時酱虎，可能會有以下兩種情況：

1. redo log 提交成功了读串，這時候數(shù)據(jù)庫掛掉導致 binlog 沒有成功寫入撒妈。數(shù)據(jù)庫重啟之后通過 redo log 把數(shù)據(jù)恢復回來，但是 binlog 沒有成功寫入杰捂，導致我們在做主從復制或者數(shù)據(jù)恢復的時候嫁佳，數(shù)據(jù)不一致谷暮。
2. binlog 提交成功了湿弦，這時候數(shù)據(jù)庫掛掉導致 redo log 沒有成功寫入。數(shù)據(jù)庫重啟之后赌蔑，無法恢復崩潰之前提交的那個事務娃惯，這部分數(shù)據(jù)更改在主庫缺失肥败。但是 binlog 已經(jīng)成功寫入了，從庫反而有了該事務的改動皿哨，導致數(shù)據(jù)不一致证膨。

綜上我們知道央勒，redo log 和 binlog 必須同時成功或同時失敗，才能保證數(shù)據(jù)一致性稳吮。

兩階段提交是如何保證 redo log 和 binlog 同時成功或同時失敗的井濒？

圖片來自《MySQL實戰(zhàn)45講》

假設已經(jīng)有了兩階段提交瑞你，分析一下以下兩種情況：

1. 假設在上圖的時刻A者甲，redo log 處于 prepare 之后，寫 binlog 之前，數(shù)據(jù)庫掛掉了寇钉。由于此時 binlog 還沒有寫扫倡，redo log 也還沒有提交竟纳，所以崩潰恢復后锥累，這個事務會回滾。這時候 binlog 還沒寫语淘，所以也不會傳到備庫惶翻。
2. 假設在上圖的時刻B，寫 binlog 之后纺荧，redo log 還沒有 commit 前發(fā)生 crash颅筋。那么崩潰恢復時垃沦，MySQL 會做以下判斷：
   1. 如果 redo log 里面的事務是完整的肢簿，也就是已經(jīng)有了 commit 標識桩引，則直接提交收夸；
   2. 如果 redo log 里面的事務只有完整的 prepare卧惜，則判斷對應的事務 binlog 是否存在并完整：
      a. 如果是咽瓷，則提交事務茅姜；
      b. 否則，回滾事務奋姿。

那么 MySQL 是怎么知道 binlog 是否完整的称诗？

一個事務的 binlog 是有完整的格式的：

• statement 格式的 binlog头遭，最后會有 COMMIT;
• row 格式的 binlog，最后會有一個 XID event狡刘。

5. change buffer

什么是 change buffer ?

當需要更新一個數(shù)據(jù)時嗅蔬，如果數(shù)據(jù)頁在內(nèi)存里就直接更新了疾就，如果數(shù)據(jù)頁不在內(nèi)存里猬腰，InnoDB 會將這些更新操作緩存在 change buffer 中，這樣就不需要讀磁盤了盒延。在下次查詢需要訪問到這個數(shù)據(jù)頁的時候添寺，將數(shù)據(jù)頁讀入內(nèi)存计露，然后執(zhí)行 change buffer 中與這個頁有關的操作憎乙。

如果能夠將更新操作先記錄在 change buffer泞边， 減少讀磁盤，更新操作變快沈善。而且數(shù)據(jù)讀入內(nèi)存是需要占用 buffer pool 的，所以這種方式還能夠避免占用內(nèi)存绳矩，提高內(nèi)存利用率翼馆。

change buffer 是可以持久化的數(shù)據(jù)，change buffer 在內(nèi)存中有拷貝严沥，也會被寫入到磁盤中消玄。

將 change buffer 中的操作應用到原數(shù)據(jù)頁丢胚，得到最新結果的過程稱為 merge携龟。以下情況會觸發(fā) merge:

1. 訪問數(shù)據(jù)頁
2. 系統(tǒng)有后臺線程定期 merge
3. 數(shù)據(jù)庫正常關閉也會觸發(fā) merge

為什么普通索引比唯一索引效率高峡蟋？

1. 查詢時：
   1. 普通索引查出數(shù)據(jù)頁层亿，數(shù)據(jù)頁讀入內(nèi)存，判斷是否有相等的數(shù)據(jù)方灾，返回數(shù)據(jù)裕偿。
   2. 唯一索引查出數(shù)據(jù)頁痛单，數(shù)據(jù)頁讀入內(nèi)存旭绒，直接返回數(shù)據(jù)挥吵。
   3. 雖然普通索引多了一步判斷忽匈，但是數(shù)據(jù)是以頁為單位讀入內(nèi)存的丹允，判斷大概率是內(nèi)存操作，消耗很小折柠，可以忽略宾娜。
2. 更新時：
   1. 普通索引直接更新內(nèi)存或者緩存到 change buffer 中，結束扇售。
   2. 唯一索引更新時需要判斷是否有數(shù)據(jù)沖突碳默，所以無法利用 change buffer，當數(shù)據(jù)頁不在內(nèi)存時缘眶，必須讀磁盤寫入內(nèi)存再做判斷嘱根，效率低于普通索引。

什么情況下不適合使用 change buffer?

如果某個業(yè)務更新后馬上做查詢巷懈，即使我們把更新先記錄在 change buffer，讀取操作也會馬上把數(shù)據(jù)讀入內(nèi)存顶燕，而且立即觸發(fā) merge 操作凑保。這種情況下，隨機訪問磁盤的次數(shù)沒有減少涌攻，反而增加了 change buffer 的維護代價欧引。所以對于這種業(yè)務，change buffer 反而起到了反作用恳谎。

6. change buffer 和 redo log

插入時

image

1. 插入的數(shù)據(jù)頁剛好在內(nèi)存中芝此，直接更新內(nèi)存中的數(shù)據(jù)頁（上圖1）。
2. 數(shù)據(jù)頁不在內(nèi)存中因痛，在 change buffer 里記錄下對該數(shù)據(jù)頁的改動（上圖2）婚苹。
3. 將上述兩個動作記入 redo log 中（上圖3，4）鸵膏。

我們可以看到膊升，執(zhí)行這條語句的成本很低，寫了兩處內(nèi)存（內(nèi)存和change buffer）谭企，寫了一處磁盤（redo log廓译，兩次操作合在一起寫磁盤），而且還是順序寫（直接寫日志文件）债查。

同時非区，圖中兩個虛線箭頭，是后臺操作（異步操作攀操，空閑時間就刷的那種）院仿，不影響該語句的響應時間秸抚。

查詢時

image

1. 數(shù)據(jù)在內(nèi)存時速和，直接讀取歹垫。
2. 數(shù)據(jù)不在內(nèi)存時，從磁盤讀入內(nèi)存颠放，然后應用 change buffer 里的操作日志排惨，在內(nèi)存生成一個最新的數(shù)據(jù)。

比較

從上面兩個案例我們可以看出：

1. redo log 主要節(jié)省的是隨機寫磁盤的 IO 消耗（把更新時的隨機寫磁盤轉成順序寫）碰凶。
2. change buffer 主要節(jié)省的是隨機讀磁盤的 IO 消耗（減少更新時讀磁盤的次數(shù)）暮芭。

7. binlog 和 redo log 的持久化

binlog 的寫入機制

binlog 的寫入邏輯：事務執(zhí)行過程中，先把日志寫到 binlog cache欲低，事務提交的時候辕宏，再把 binlog cache 寫到 binlog 文件中。

一個事務的 binlog 是不能被拆開寫的砾莱，因此不論這個事務多大瑞筐，也要確保一次性寫入。

系統(tǒng)給 binlog cache 分配了一片內(nèi)存腊瑟，每個線程一個聚假，參數(shù) binlog_cache_size 用于控制單個線程內(nèi) binlog cache 所占內(nèi)存的大小。如果超過了這個參數(shù)規(guī)定的大小闰非，就要暫存的磁盤中膘格。

事務提交時，執(zhí)行器把 binlog cache 里的完整事務寫到 binlog file 和 磁盤中财松，并清空 binlog cache瘪贱。狀態(tài)如下圖所示：

image

1. 圖中的 write，指的是日志寫入到文件系統(tǒng)的 page cache辆毡，并沒有把數(shù)據(jù)持久化到磁盤政敢，速度比較快。
2. 圖中的 fsync胚迫，指的是日志最終持久化到磁盤喷户，速度慢。
3. write 和 fsync 的時機访锻，由參數(shù) sync_binlog 控制：
   1. sync_binlog=0 時脑题，表示每次提交事務都只 write，不 fsync学赛；
   2. sync_binlog=1 時财岔，表示每次提交事務都會執(zhí)行 fsync；
   3. sync_binlog=N(N>1) 時龟虎，表示每次提交事務都 write璃谨，但累積 N 個事務后才 fsync。
4. 將 sync_binlog 設置為 N，對應的風險是：如果主機發(fā)生異常重啟佳吞，會丟失最近 N 個事務的 binlog 日志（沒有持久化到磁盤拱雏，主機掛了就丟失了）。

redo log 的寫入機制

image

1. 事務在執(zhí)行過程中底扳，生成的 redo log 會先寫到 redo log buffer 中铸抑。
2. 寫入到 page cache 的速度也很快，寫入到磁盤的速度慢衷模。
3. innodb_flush_log_at_trx_commit 參數(shù)用來控制 redo log 的寫入策略：
   1. 設為 0 時鹊汛，表示每次事務提交時都只是把 redo log 留在 redo log buffer 中；
   2. 設為 1 時刁憋，表示每次事務提交時都將 redo log 直接持久化到磁盤；
   3. 設為 2 時木蹬，表示每次事務提交時都只是把 redo log 寫到 page cache职祷。
4. InnoDB 有一個后臺線程，每隔 1 秒届囚，就會把 redo log buffer 中的日志有梆，調(diào)用 write 寫到文件系統(tǒng)的 page cache，然后調(diào)用 fsync 持久化到磁盤意系。
5. 事務執(zhí)行過程中寫入 redo log buffer 的記錄泥耀，也會隨著其他事務的提交或者定時寫入過程持久化到磁盤中。也就是說有些還未提交的事務的 redo log 也會被持久化蛔添。
6. redo log buffer 占用的空間即將達到 innodb_log_buffer_size 一半的時候痰催，也會觸發(fā)持久化操作。

分組提交

為了降低寫磁盤的次數(shù)迎瞧，redo log 把 write 和 fsync 拆成兩個步驟夸溶，當有并發(fā)時，事務A寫完 page cahce凶硅，事務B也寫完了 page cache缝裁，事務A觸發(fā) fsync 的時候，會把兩個事務的 redo log 并在一組足绅，一起寫磁盤捷绑。

并且為了能讓更多的事務加入同一個組，InnoDB 讓 redo log 和 binlog 的 write 和 fsync 交替執(zhí)行氢妈，分組提交的優(yōu)化粹污，redo log 和 binlog 都有。

image

WAL 機制是減少磁盤寫首量，但每次提交事務都要寫 redo log 和 binlog壮吩，寫磁盤的次數(shù)好像沒有減少进苍？

1. redo log 和 binlog 都是順序寫，磁盤的順序寫比隨機寫速度快鸭叙；（日志寫磁盤都是順序寫的觉啊，事務提交后直接把數(shù)據(jù)寫磁盤就是隨機訪問）；
2. 組提交機制可以大幅降低磁盤的 IOPS 消耗递雀。

MySQL 是如何保證 crash-safe 的。

redo log 是如何保證 crash-safe 的蚀浆。
寫到 redo log buffer 不能保證 crash-safe缀程，寫到 fs cache 也不能保證 crash-safe，只有 redo log 寫入磁盤之后市俊，數(shù)據(jù)庫異常重啟杨凑，從磁盤中的 redo log 拿出未執(zhí)行的日志進行恢復，才算是 crash-safe摆昧。
這也說明了多個事務提交之后才寫磁盤撩满，還是會有事務丟失。只有每個事務提交后都進行寫磁盤才能保證數(shù)據(jù)完全不丟失绅你。

binlog 為什么無法保證 crash-safe?

1. 如果 binlog 寫入成功了伺帘，數(shù)據(jù)還沒寫入磁盤，數(shù)據(jù)庫異常崩潰忌锯，重啟后主庫沒有這部分數(shù)據(jù)伪嫁，而通過 binlog 同步的從庫卻有了這部分配置，導致主從數(shù)據(jù)不一致偶垮。
2. 如果 數(shù)據(jù)寫入磁盤张咳，binlog 寫入失敗了，數(shù)據(jù)庫異常崩潰似舵，重啟后主庫有這部分數(shù)據(jù)脚猾，而通過 binlog 同步的從庫沒有這部分數(shù)據(jù)，導致主從數(shù)據(jù)不一致砚哗。

能否只使用 binlog 或 redo log 單個日志保證 crash-safe?

我的理解是：并不是單個 log 無法保證 crash-safe龙助，而是 binlog 本身無法保證 crash-safe，因為 InnoDB 無法重新設計 binlog蛛芥，所以引入了 redo log泌参。并且花了很大力氣來保證 redo log 和 binlog 的一致性。
如果重新設計 MySQL常空，可以使用 redo log 實現(xiàn) binlog 的功能沽一，也可以把 binlog 設計成 crash-safe 的，這樣就只需要一種 log 了漓糙。

參考

02 | 日志系統(tǒng)：一條SQL更新語句是如何執(zhí)行的铣缠？-極客時間

09 | 普通索引和唯一索引，應該怎么選擇？-極客時間

12 | 為什么我的MySQL會"抖"一下蝗蛙？-極客時間

15 | 答疑文章（一）：日志和索引相關問題-極客時間

23 | MySQL是怎么保證數(shù)據(jù)不丟的蝇庭？-極客時間