MYSQL45講 讀書筆記 15講答疑文章（一）：日志和索引相關問題

一 序

本文屬于極客時間MySQL45講讀書筆記系列。

  本文老師主要是用來答疑，關于日志與索引相關問題。

二 日志相關問題

  在第二篇  mysql45講 2一條SQL更新語句是如何執行的 講到binlog（歸檔日志）和redo log（重做日志）配合崩潰恢複的時候，用的是反證法，說明了如果沒有兩階段送出，會導緻MySQL出現主備資料不一緻等問題。

在兩階段送出的不同瞬間，MySQL如果發生異常重新開機，是怎麼保證資料完整性的？

注意，容易把兩個“commit”的概念混淆了：

• SQL的“commit語句”，是指MySQL文法中，用于送出一個事務的指令。一般跟begin/start transaction 配對使用。
• 而我們圖中用到的這個“commit步驟”，指的是事務送出過程中的一個小步驟，也是最後一步。當這個步驟執行完成後，這個事務就送出完成了。“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是有完整格式的：

• statement格式的binlog，最後會有COMMIT；
• row格式的binlog，最後會有一個XID event。

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

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

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

• 如果碰到既有prepare、又有commit的redo log，就直接送出；
• 如果碰到隻有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，設計之初就有沒有支援崩潰恢複。

圖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的話，這些下遊系統就沒法輸入了。

追問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消耗。但是可能會出現“被動寫入磁盤”，比如記憶體不夠、其他事務送出等情況。

三 業務設計問題

  @ithunter 同學提到一個和索引有關的問題。問題是這樣的：

業務上有這樣的需求，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鎖層面有沒有辦法處理？   

接下來，我把@ithunter 同學說的表模拟出來，友善我們讨論。

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,
  `firned_2_id` int(11) NOT NULL,
  UNIQUE KEY `uk_friend` (`friend_1_id`,`firned_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的時候，表示user_id 關注 liker_id;

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

值是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的這條記錄。

之前文章我們讨論的，是在“業務開發保證不會插入重複記錄”的情況下，着重要解決性能問題的時候，才建議盡量使用普通索引。而像這個例子裡，按照這個設計，業務根本就是保證“我一定會插入重複資料，資料庫一定要要有唯一性限制”，這時就沒啥好說的了，唯一索引建起來吧。

  如果我來解決這樣的問題，還是更傾向于外面加鎖來處理，比如使用常見的分布式鎖，key:user_id,當然1把鎖是不行的，因為user_id跟liker_id 本質上都是userID。是以要加兩把鎖，也就是說兩把鎖都拿到了才能去操作資料庫。拿不到的去等待重試。

小結

我們建立了一個簡單的表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)"這個操作，該加鎖的加鎖，該更新的更新。

啊。我選錯了，我選了2，其實答案是3.

第一個選項是，MySQL讀出資料，發現值與原來相同，不更新，直接傳回，執行結束。這裡我們可以用一個鎖實驗來确認。

假設，目前表t裡的值是(1,2)。

圖12 鎖驗證方式

session B的update 語句被blocked了，加鎖這個動作是InnoDB才能做的，是以排除選項1。

第二個選項是，MySQL調用了InnoDB引擎提供的接口，但是引擎發現值與原來相同，不更新，直接傳回。有沒有這種可能呢？這裡我用一個可見性實驗來确認。

假設目前表裡的值是(1,2)。

圖13 可見性驗證方式

session A的第二個select 語句是一緻性讀（快照讀)，它是不能看見session B的更新的。

現在它傳回的是(1,3)，表示它看見了某個新的版本，這個版本隻能是session A自己的update語句做更新的時候生成。（這裡就是快照讀與目前讀的差別）。

  是以，我們上期思考題的答案應該是選項3，即：InnoDB認真執行了“把這個值修改成(1,2)"這個操作，該加鎖的加鎖，該更新的更新。

作為複現：

最後，貼一下老師的原話，用來作為解釋我們為啥要去學習MySQL原理

加油。

說下我自己的了解。

我在帶新人的時候，要求大家在寫SQL語句的時候，心裡是有數的，知道每個語句執行的結果，以及這些代碼會消耗什麼資源、如果慢了會慢在哪裡、每個語句執行會占用哪些鎖等等。

有的新人會問“為什麼需要這麼麻煩，我執行一下，看看結果對不對，對了就行，不對就改，是不是也可以？”

我說不可以。因為如果這樣，我們就會受到很多局限，即使我們定位自己是業務開發人員。

這裡我說一個限制：

這會限制基于資料庫的業務架構能力。一個語句可以試，一個五個語句的事務分析就要試很多次，一個複雜業務系統的資料庫設計，是試不出來的。

原理可以幫我們剪枝，排除掉那些理論上明顯錯誤的方案，這樣才有精力真的去試那些有限的、可能正确的方案。

我們不需要100%精通MySQL(我自己離這個目标也相去甚遠），但是隻要多知道一些原理，就能多剪一些枝，架構設計就能少一些錯誤選項的幹擾，設計出來的項目架構正确的可能性更高。

我自己特别喜歡這個剪枝的過程和感覺，他表示我用以前學習的時間，來節省了現在工作的時間。

當然，“原理”是一個很大的概念，有的原理更接近實戰，有的遠一些。這個專欄我挑的是跟平時使用相關的原理，以便大家可以有機會邊學邊用。

一起加油吧🤝