MySQL深入學習第十五篇－日志和索引相關問題

日志相關問題

我在第 2 篇文章《MySQL深入學習第二篇 － 一條SQL更新陳述句是如何執行的？》中，和你講到 binlog（歸檔日志）和 redo log（重做日志）配合崩潰恢復的時候，用的是反證法，說明了如果沒有兩階段提交，會導致 MySQL 出現主備資料不一致等問題，

在這篇文章下面，很多同學在問，在兩階段提交的不同瞬間，MySQL 如果發生例外重啟，是怎么保證資料完整性的？

現在，我們就從這個問題開始吧，

我再放一次兩階段提交的圖，方便你學習下面的內容，如下 圖1 所示階段提交示意圖：

這里，我要先和你解釋一個誤會式的問題，有同學在評論區問到，這個圖不是一個 update 陳述句的執行流程嗎，怎么還會呼叫 commit 陳述句？

他產生這個疑問的原因，是把兩個“commit”的概念混淆了：

1. 他說的“commit 陳述句”，是指 MySQL 語法中，用于提交一個事務的命令，一般跟 begin/start transaction 配對使用，

2. 而我們圖中用到的這個“commit 步驟”，指的是事務提交程序中的一個小步驟，也是最后一步，當這個步驟執行完成后，這個事務就提交完成了，

3. “commit 陳述句”執行的時候，會包含“commit 步驟”，

而我們這個例子里面，沒有顯式地開啟事務，因此這個 update 陳述句自己就是一個事務，在執行完成后提交事務時，就會用到這個“commit 步驟“，

接下來，我們就一起分析一下在兩階段提交的不同時刻，MySQL 例外重啟會出現什么現象，

如果在圖中時刻 A 的地方，也就是寫入 redo log 處于 prepare 階段之后、寫 binlog 之前，發生了崩潰（crash），由于此時 binlog 還沒寫，redo log 也還沒提交，所以崩潰恢復的時候，這個事務會回滾，這時候，binlog 還沒寫，所以也不會傳到備庫，到這里，大家都可以理解，

大家出現問題的地方，主要集中在時刻 B，也就是 binlog 寫完，redo log 還沒 commit 前發生 crash，那崩潰恢復的時候 MySQL 會怎么處理？

我們先來看一下崩潰恢復時的判斷規則，

1. 如果 redo log 里面的事務是完整的，也就是已經有了 commit 標識，則直接提交；

2. 如果 redo log 里面的事務只有完整的 prepare，則判斷對應的事務 binlog 是否存在并完整：

a. 如果是，則提交事務；

b. 否則，回滾事務，

這里，時刻 B 發生 crash 對應的就是 2(a) 的情況，崩潰恢復程序中事務會被提交，

現在，我們繼續延展一下這個問題，

追問 1：MySQL 怎么知道 binlog 是完整的?

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

1. statement 格式的 binlog，最后會有 COMMIT；

2. row 格式的 binlog，最后會有一個 XID event，

另外，在 MySQL 5.6.2 版本以后，還引入了 binlog-checksum 引數，用來驗證 binlog 內容的正確性，對于 binlog 日志由于磁盤原因，可能會在日志中間出錯的情況，MySQL 可以通過校驗 checksum 的結果來發現，所以，MySQL 還是有辦法驗證事務 binlog 的完整性的，

追問 2：redo log 和 binlog 是怎么關聯起來的?

回答：它們有一個共同的資料欄位，叫 XID，崩潰恢復的時候，會按順序掃描 redo log：

1. 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；

2. 如果碰到只有 parepare、而沒有 commit 的 redo log，就拿著 XID 去 binlog 找對應的事務，

追問 3：處于 prepare 階段的 redo log 加上完整 binlog，重啟就能恢復，MySQL 為什么要這么設計?

回答：其實，這個問題還是跟我們在反證法中說到的資料與備份的一致性有關，在時刻 B，也就是 binlog 寫完以后 MySQL 發生崩潰，這時候 binlog 已經寫入了，之后就會被從庫（或者用這個 binlog 恢復出來的庫）使用，

所以，在主庫上也要提交這個事務，采用這個策略，主庫和備庫的資料就保證了一致性，

追問 4：如果這樣的話，為什么還要兩階段提交呢？干脆先 redo log 寫完，再寫 binlog，崩潰恢復的時候，必須得兩個日志都完整才可以，是不是一樣的邏輯？

回答：其實，兩階段提交是經典的分布式系統問題，并不是 MySQL 獨有的，

如果必須要舉一個場景，來說明這么做的必要性的話，那就是事務的持久性問題，

對于 InnoDB 引擎來說，如果 redo log 提交完成了，事務就不能回滾（如果這還允許回滾，就可能覆寫掉別的事務的更新），而如果 redo log 直接提交，然后 binlog 寫入的時候失敗，InnoDB 又回滾不了，資料和 binlog 日志又不一致了，

兩階段提交就是為了給所有人一個機會，當每個人都說“我 ok”的時候，再一起提交，

追問 5：不引入兩個日志，也就沒有兩階段提交的必要了，只用 binlog 來支持崩潰恢復，又能支持歸檔，不就可以了？

回答：這位同學的意思是，只保留 binlog，然后可以把提交流程改成這樣：.... -> “資料更新到記憶體” -> “寫 binlog” -> “提交事務”，是不是也可以提供崩潰恢復的能力？

答案是不可以，

如果說歷史原因的話，那就是 InnoDB 并不是 MySQL 的原生存盤引擎，MySQL 的原生引擎是 MyISAM，設計之初就有沒有支持崩潰恢復，

InnoDB 在作為 MySQL 的插件加入 MySQL 引擎家族之前，就已經是一個提供了崩潰恢復和事務支持的引擎了，

InnoDB 接入了 MySQL 后，發現既然 binlog 沒有崩潰恢復的能力，那就用 InnoDB 原有的 redo log 好了，

如下 圖 2 所示為 只用 binlog 支持崩潰恢復：

這樣的流程下，binlog 還是不能支持崩潰恢復的，我說一個不支持的點吧：binlog 沒有能力恢復“資料頁”，

如果在圖中標的位置，也就是 binlog2 寫完了，但是整個事務還沒有 commit 的時候，MySQL 發生了 crash，

重啟后，引擎內部事務 2 會回滾，然后應用 binlog2 可以補回來；但是對于事務 1 來說，系統已經認為提交完成了，不會再應用一次 binlog1，

但是，InnoDB 引擎使用的是 WAL 技術，執行事務的時候，寫完記憶體和日志，事務就算完成了，如果之后崩潰，要依賴于日志來恢復資料頁，

也就是說在圖中這個位置發生崩潰的話，事務 1 也是可能丟失了的，而且是資料頁級的丟失，此時，binlog 里面并沒有記錄資料頁的更新細節，是補不回來的，

你如果要說，那我優化一下 binlog 的內容，讓它來記錄資料頁的更改可以嗎？但，這其實就是又做了一個 redo log 出來，

所以，至少現在的 binlog 能力，還不能支持崩潰恢復，

追問 6：那能不能反過來，只用 redo log，不要 binlog？

回答：如果只從崩潰恢復的角度來講是可以的，你可以把 binlog 關掉，這樣就沒有兩階段提交了，但系統依然是 crash-safe 的，

但是，如果你了解一下業界各個公司的使用場景的話，就會發現在正式的生產庫上，binlog 都是開著的，因為 binlog 有著 redo log 無法替代的功能，

一個是歸檔，redo log 是回圈寫，寫到末尾是要回到開頭繼續寫的，這樣歷史日志沒法保留，redo log 也就起不到歸檔的作用，

一個就是 MySQL 系統依賴于 binlog，binlog 作為 MySQL 一開始就有的功能，被用在了很多地方，其中，MySQL 系統高可用的基礎，就是 binlog 復制，

還有很多公司有異構系統（比如一些資料分析系統），這些系統就靠消費 MySQL 的 binlog 來更新自己的資料，關掉 binlog 的話，這些下游系統就沒法輸入了，

總之，由于現在包括 MySQL 高可用在內的很多系統機制都依賴于 binlog，所以“鳩占鵲巢”redo log 還做不到，你看，發展生態是多么重要，

追問 7：redo log 一般設定多大？

回答：redo log 太小的話，會導致很快就被寫滿，然后不得不強行刷 redo log，這樣 WAL 機制的能力就發揮不出來了，

所以，如果是現在常見的幾個 TB 的磁盤的話，就不要太小氣了，直接將 redo log 設定為 4 個檔案、每個檔案 1GB 吧，

追問 8：正常運行中的實體，資料寫入后的最終落盤，是從 redo log 更新過來的還是從 buffer pool 更新過來的呢？

回答：這個問題其實問得非常好，這里涉及到了，“redo log 里面到底是什么”的問題，

實際上，redo log 并沒有記錄資料頁的完整資料，所以它并沒有能力自己去更新磁盤資料頁，也就不存在“資料最終落盤，是由 redo log 更新過去”的情況，

1. 如果是正常運行的實體的話，資料頁被修改以后，跟磁盤的資料頁不一致，稱為臟頁，最終資料落盤，就是把記憶體中的資料頁寫盤，這個程序，甚至與 redo log 毫無關系，

2. 在崩潰恢復場景中，InnoDB 如果判斷到一個資料頁可能在崩潰恢復的時候丟失了更新，就會將它讀到記憶體，然后讓 redo log 更新記憶體內容，更新完成后，記憶體頁變成臟頁，就回到了第一種情況的狀態，

追問 9：redo log buffer 是什么？是先修改記憶體，還是先寫 redo log 檔案？

回答：這兩個問題可以一起回答，

在一個事務的更新程序中，日志是要寫多次的，比如下面這個事務：

begin;
  insert into t1 ...
  insert into t2 ...
commit;

這個事務要往兩個表中插入記錄，插入資料的程序中，生成的日志都得先保存起來，但又不能在還沒 commit 的時候就直接寫到 redo log 檔案里，

所以，redo log buffer 就是一塊記憶體，用來先存 redo 日志的，也就是說，在執行第一個 insert 的時候，資料的記憶體被修改了，redo log buffer 也寫入了日志，

但是，真正把日志寫到 redo log 檔案（檔案名是 ib_logfile+ 數字），是在執行 commit 陳述句的時候做的，

這里說的是事務執行程序中不會“主動去刷盤”，以減少不必要的 IO 消耗，但是可能會出現“被動寫入磁盤”，比如記憶體不夠、其他事務提交等情況，

單獨執行一個更新陳述句的時候，InnoDB 會自己啟動一個事務，在陳述句執行完成的時候提交，程序跟上面是一樣的，只不過是“壓縮”到了一個陳述句里面完成，

以上這些問題，就是把大家提過的關于 redo log 和 binlog 的問題串起來，做的一次集中回答，如果你還有問題，可以在評論區繼續留言補充，

業務設計問題

接下來，我再和你分享 評論區提到的跟索引相關的一個問題，我覺得這個問題挺有趣、也挺實用的，其他同學也可能會碰上這樣的場景，在這里解答和分享一下，問題是這樣的：

業務上有這樣的需求，A、B 兩個用戶，如果互相關注，則成為好友，設計上是有兩張表，一個是 like 表，一個是 friend 表，like 表有 user_id、liker_id 兩個欄位，我設定為復合唯一索引即 uk_user_id_liker_id，

陳述句執行邏輯是這樣的：以 A 關注 B 為例，第一步，先查詢對方有沒有關注自己（B 有沒有關注 A）select * from like where user_id = B and liker_id = A；如果有，則成為好友 insert into friend；如果沒有，則只是單向關注關系 insert into like；但是如果 A、B 同時關注對方，會出現不會成為好友的情況，因為上面第 1 步，雙方都沒關注對方，第 1 步即使使用了排他鎖也不行，因為記錄不存在，行鎖無法生效，請問這種情況，在 MySQL 鎖層面有沒有辦法處理？

接下來，我把這個問題的表模擬出來，方便我們討論：

CREATE TABLE `like` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `liker_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id_liker_id` (`user_id`,`liker_id`)
) ENGINE=InnoDB;

CREATE TABLE `friend` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `friend_1_id` int(11) NOT NULL,
  `friend_2_id` int(11) NOT NULL,
  UNIQUE KEY `uk_friend` (`friend_1_id`,`friend_2_id`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

雖然這個題干中，并沒有說到 friend 表的索引結構，但我猜測 friend_1_id 和 friend_2_id 也有索引，為便于描述，我給加上唯一索引，

順便說明一下，“like”是關鍵字，我一般不建議使用關鍵字作為庫名、表名、欄位名或索引名，

我把他的疑問翻譯一下，在并發場景下，同時有兩個人，設定為關注對方，就可能導致無法成功加為朋友關系，

現在，我用你已經熟悉的時刻順序表的形式，把這兩個事務的執行陳述句列出來，如下 圖3 所示為并發“喜歡”邏輯操作順序：

由于一開始 A 和 B 之間沒有關注關系，所以兩個事務里面的 select 陳述句查出來的結果都是空，

因此，session 1 的邏輯就是“既然 B 沒有關注 A，那就只插入一個單向關注關系”，session 2 也同樣是這個邏輯，

這個結果對業務來說就是 bug 了，因為在業務設定里面，這兩個邏輯都執行完成以后，是應該在 friend 表里面插入一行記錄的，

如提問里面說的，“第 1 步即使使用了排他鎖也不行，因為記錄不存在，行鎖無法生效”，不過，我想到了另外一個方法，來解決這個問題，

首先，要給“like”表增加一個欄位，比如叫作 relation_ship，并設為整型，取值 1、2、3，

1. 值是 1 的時候，表示 user_id 關注 liker_id；

2. 值是 2 的時候，表示 liker_id 關注 user_id；

3. 值是 3 的時候，表示互相關注，

然后，當 A 關注 B 的時候，邏輯改成如下所示的樣子，應用代碼里面，比較 A 和 B 的大小，如果 A<B，就執行下面的邏輯：

begin; 
insert into `like`(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;
select relation_ship from `like` where user_id=A and liker_id=B;

/* 代碼中判斷回傳的 relation_ship */
/* 如果是1，事務結束，執行 commit */
/* 如果是3，則執行下面這兩個陳述句  */

insert ignore into friend(friend_1_id, friend_2_id) values(A,B);
commit;

如果 A>B，則執行下面的邏輯：

begin; 
insert into `like`(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;
select relation_ship from `like` where user_id=B and liker_id=A;

/* 代碼中判斷回傳的 relation_ship */
/* 如果是2，事務結束，執行 commit */
/* 如果是3，則執行下面這兩個陳述句 */

insert ignore into friend(friend_1_id, friend_2_id) values(B,A);
commit;

這個設計里，讓“like”表里的資料保證 user_id < liker_id，這樣不論是 A 關注 B，還是 B 關注 A，在操作“like”表的時候，如果反向的關系已經存在，就會出現行鎖沖突，

然后，insert … on duplicate 陳述句，確保了在事務內部，執行了這個 SQL 陳述句后，就強行占住了這個行鎖，之后的 select 判斷 relation_ship 這個邏輯時就確保了是在行鎖保護下的讀操作，

運算子 “|” 是按位或，連同最后一句 insert 陳述句里的 ignore，是為了保證重復呼叫時的冪等性，

這樣，即使在雙方“同時”執行關注操作，最終資料庫里的結果，也是 like 表里面有一條關于 A 和 B 的記錄，而且 relation_ship 的值是 3， 并且 friend 表里面也有了 A 和 B 的這條記錄，

不知道你會不會吐槽：之前明明還說盡量不要使用唯一索引，結果這個例子一上來我就創建了兩個，這里我要再和你說明一下，之前文章我們討論的，是在“業務開發保證不會插入重復記錄”的情況下，著重要解決性能問題的時候，才建議盡量使用普通索引，

而像這個例子里，按照這個設計，業務根本就是保證“我一定會插入重復資料，資料庫一定要要有唯一性約束”，這時就沒啥好說的了，唯一索引建起來吧，

小結

我針對前 14 篇文章，大家在評論區中的留言，從中摘取了關于日志和索引的相關問題，串成了今天這篇文章，這里我也要再和你說一聲，有些我答應在答疑文章中進行擴展的話題，今天這篇文章沒來得及擴展，后續我會再找機會為你解答，所以，篇幅所限，評論區見吧，

最后，雖然這篇是答疑文章，但課后問題還是要有的，

我們創建了一個簡單的表 t，并插入一行，然后對這一行做修改，

CREATE TABLE `t` (
  `id` int(11) NOT NULL primary key auto_increment,
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB;
insert into t values(1,2);

這時候，表 t 里有唯一的一行資料 (1,2)，假設，我現在要執行：

update t set a=2 where id=1;

你會看到這樣的結果：

結果顯示，匹配 (rows matched) 了一行，修改 (Changed) 了 0 行，

僅從現象上看，MySQL 內部在處理這個命令的時候，可以有以下三種選擇：

1. 更新都是先讀后寫的，MySQL 讀出資料，發現 a 的值本來就是 2，不更新，直接回傳，執行結束；

2. MySQL 呼叫了 InnoDB 引擎提供的“修改為 (1,2)”這個介面，但是引擎發現值與原來相同，不更新，直接回傳；

3. InnoDB 認真執行了“把這個值修改成 (1,2)"這個操作，該加鎖的加鎖，該更新的更新，

你覺得實際情況會是以上哪種呢？你可否用構造實驗的方式，來證明你的結論？進一步地，可以思考一下，MySQL 為什么要選擇這種策略呢？