MySQL45講基礎篇

基礎篇

01 | 基礎架構：一條SQL查詢語句是如何執行的？

你好，我是林曉斌。

這是專欄的第一篇文章，我想來跟你聊聊 MySQL的基礎架構。我們經常說，看一個事兒千萬不要直接陷入細節裡，你應該先鳥瞰其全貌，這樣能夠幫助你從高維度理解問題。同樣，對於MySQL的學習也是這樣。平時我們使用資料庫，看到的通常都是一個整體。比如，你有個最簡單的表，表裡只有一個ID 欄位，在執行下面這個查詢語句時：

select * from T where ID=10;

我們看到的只是輸入一條語句，返回一個結果，卻不知道這條語句在 MySQL內部的執行過程。

所以今天我想和你一起把 MySQL拆解一下，看看裡面都有哪些"零件"，希望藉由這個拆解過程，讓你對 MySQL有更深入的理解。這樣當我們碰到 MySQL的一些異常或者問題時，就能夠直戳本質，更為快速地定位並解決問題。

下面我給出的是 MySQL 的基本架構示意圖，從中你可以清楚地看到 SQL 語句在MySQL的各個功能模組中的執行過程。

MySQL 的邏輯架構圖大體來說，MySQL 可以分為Server 層和儲存引擎層兩部分。

Server 層包括聯結器、查詢快取、分析器、最佳化器、執行器等，涵蓋 MySQL的大多數核心服務功能，以及所有的內建函式（如日期、時間、數學和加密函式等），所有跨儲存引擎的功能都在這一層實現，比如儲存過程、觸發器、檢視等。

而儲存引擎層負責資料的儲存和提取。其架構模式是外掛式的，支援InnoDB、MyISAM、 Memory 等多個儲存引擎。現在最常用的儲存引擎是InnoDB，它從 MySQL 5.5.5 版本開始成為了預設儲存引擎。

也就是說，你執行 create table 建表的時候，如果不指定引擎型別，預設使用的就是InnoDB。不過，你也可以透過指定儲存引擎的型別來選擇別的引擎，比如在create table語句中使用 engine=memory 來指定使用記憶體引擎建立表。不同儲存引擎的表資料存取方式不同，支援的功能也不同，在後面的文章中，我們會討論到引擎的選擇。

從圖中不難看出，不同的儲存引擎共用一個Server層，也就是從聯結器到執行器的部分。你可以先對每個元件的名字有個印象，接下來我會結合開頭提到的那條SQL 語句，帶你走一遍整個執行流程，依次看下每個元件的作用。

聯結器

第一步，你會先連線到這個資料庫上，這時候接待你的就是聯結器。聯結器負責跟客戶端建立連線、獲取許可權、維持和管理連線。連線命令一般是這麼寫的：

mysql -h $ip -P$port -u $user -p

輸完命令之後，你就需要在互動對話裡面輸入密碼。雖然密碼也可以直接跟在 -p後面寫在命令列中，但這樣可能會導致你的密碼洩露。如果你連的是生產伺服器，強烈建議你不要這麼做。

連線命令中的 mysql 是客戶端工具，用來跟服務端建立連線。在完成經典的TCP握手後，聯結器就要開始認證你的身份，這個時候用的就是你輸入的使用者名稱和密碼。

• 如果使用者名稱或密碼不對，你就會收到一個"Access denied for user"的錯誤，然後客戶端程式結束執行。

• 如果使用者名稱密碼認證透過，聯結器會到許可權表裡面查出你擁有的許可權。之後，這個連線裡面的許可權判斷邏輯，都將依賴於此時讀到的許可權。

這就意味著，一個使用者成功建立連線後，即使你用管理員賬號對這個使用者的許可權做了修改，也不會影響已經存在連線的許可權。修改完成後，只有再新建的連線才會使用新的許可權設定。

連線完成後，如果你沒有後續的動作，這個連線就處於空閒狀態，你可以在show processlist 命令中看到它。文字中這個圖是 show processlist的結果，其中的 Command列顯示為"Sleep"的這一行，就表示現在系統裡面有一個空閒連線。

客戶端如果太長時間沒動靜，聯結器就會自動將它斷開。這個時間是由引數wait_timeout控制的，預設值是 8 小時。

如果在連線被斷開之後，客戶端再次傳送請求的話，就會收到一個錯誤提醒：Lost connection to MySQL server during query。這時候如果你要繼續，就需要重連，然後再執行請求了。

資料庫裡面，長連線是指連線成功後，如果客戶端持續有請求，則一直使用同一個連線。短連線則是指每次執行完很少的幾次查詢就斷開連線，下次查詢再重新建立一個。

建立連線的過程通常是比較複雜的，所以我建議你在使用中要儘量減少建立連線的動作，也就是儘量使用長連線。

但是全部使用長連線後，你可能會發現，有些時候 MySQL佔用記憶體漲得特別快，這是因為 MySQL在執行過程中臨時使用的記憶體是管理在連線物件裡面的。這些資源會在連線斷開的時候才釋放。所以如果長連線累積下來，可能導致記憶體佔用太大，被系統強行殺掉（OOM），從現象看就是 MySQL異常重啟了。

怎麼解決這個問題呢？你可以考慮以下兩種方案。

1. 定期斷開長連線。使用一段時間，或者程式裡面判斷執行過一個佔用記憶體的大查詢後，斷開連線，之後要查詢再重連。

2. 如果你用的是 MySQL 5.7或更新版本，可以在每次執行一個比較大的操作後，透過執行
   mysql_reset_connection來重新初始化連線資源。這個過程不需要重連和重新做許可權驗證，但是會將連線恢復到剛剛建立完時的狀態。

查詢快取

連線建立完成後，你就可以執行 select語句了。執行邏輯就會來到第二步：查詢快取。

MySQL拿到一個查詢請求後，會先到查詢快取看看，之前是不是執行過這條語句。之前執行過的語句及其結果可能會以key-value 對的形式，被直接快取在記憶體中。key 是查詢的語句，value是查詢的結果。如果你的查詢能夠直接在這個快取中找到 key，那麼這個 value
就會被直接返回給客戶端。

如果語句不在查詢快取中，就會繼續後面的執行階段。執行完成後，執行結果會被存入查詢快取中。你可以看到，如果查詢命中快取，MySQL不需要執行後面的複雜操作，就可以直接返回結果，這個效率會很高。

但是大多數情況下我會建議你不要使用查詢快取，為什麼呢？因為查詢快取往往弊大於利。

查詢快取的失效非常頻繁，只要有對一個表的更新，這個表上所有的查詢快取都會被清空。因此很可能你費勁地把結果存起來，還沒使用呢，就被一個更新全清空了。對於更新壓力大的資料庫來說，查詢快取的命中率會非常低。除非你的業務就是有一張靜態表，很長時間才會更新一次。比如，一個系統配置表，那這張表上的查詢才適合使用查詢快取。

好在 MySQL 也提供了這種"按需使用"的方式。你可以將引數 query_cache_type
設定成 DEMAND，這樣對於預設的 SQL語句都不使用查詢快取。而對於你確定要使用查詢快取的語句，可以用SQL_CACHE 顯式指定，像下面這個語句一樣：

select SQL_CACHE * from T where ID=10;

需要注意的是，MySQL 8.0 版本直接將查詢快取的整塊功能刪掉了，也就是說8.0 開始徹底沒有這個功能了。

分析器

如果沒有命中查詢快取，就要開始真正執行語句了。首先，MySQL需要知道你要做什麼，因此需要對 SQL 語句做解析。

分析器先會做"詞法分析"。你輸入的是由多個字串和空格組成的一條 SQL語句， MySQL 需要識別出裡面的字串分別是什麼，代表什麼。

MySQL從你輸入的"select"這個關鍵字識別出來，這是一個查詢語句。它也要把字串"T"識別成"表名T"，把字串"ID"識別成"列 ID"。

做完了這些識別以後，就要做"語法分析"。根據詞法分析的結果，語法分析器會根據語法規則，判斷你輸入的這個SQL 語句是否滿足 MySQL 語法。

如果你的語句不對，就會收到"You have an error in your SQL syntax"的錯誤提醒，比如下面這個語句 select 少打了開頭的字母"s"。

mysql> elect * from t where ID=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

一般語法錯誤會提示第一個出現錯誤的位置，所以你要關注的是緊接"use near"的內容。

最佳化器

經過了分析器，MySQL就知道你要做什麼了。在開始執行之前，還要先經過最佳化器的處理。

最佳化器是在表裡面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join）的時候，決定各個表的連線順序。比如你執行下面這樣的語句，這個語句是執行兩個表的join：

select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

• 既可以先從表 t1 裡面取出 c=10 的記錄的 ID 值，再根據 ID 值關聯到表t2，再判斷 t2裡面 d 的值是否等於 20。
• 也可以先從表 t2 裡面取出 d=20 的記錄的 ID 值，再根據 ID 值關聯到t1，再判斷 t1 裡面 c 的值是否等於 10。

這兩種執行方法的邏輯結果是一樣的，但是執行的效率會有不同，而最佳化器的作用就是決定選擇使用哪一個方案。

最佳化器階段完成後，這個語句的執行方案就確定下來了，然後進入執行器階段。如果你還有一些疑問，比如最佳化器是怎麼選擇索引的，有沒有可能選擇錯等等，沒關係，我會在後面的文章中單獨展開說明最佳化器的內容。

執行器

MySQL透過分析器知道了你要做什麼，透過最佳化器知道了該怎麼做，於是就進入了執行器階段，開始執行語句。

開始執行的時候，要先判斷一下你對這個表 T有沒有執行查詢的許可權，如果沒有，就會返回沒有許可權的錯誤，如下所示(在工程實現上，如果命中查詢快取，會在查詢快取返回結果的時候，做許可權驗證。查詢也會在最佳化器之前呼叫precheck 驗證許可權)。

mysql> select * from T where ID=10;

ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

如果有許可權，就開啟表繼續執行。開啟表的時候，執行器就會根據表的引擎定義，去使用這個引擎提供的介面。

比如我們這個例子中的表 T 中，ID欄位沒有索引，那麼執行器的執行流程是這樣的：

1. 呼叫 InnoDB 引擎介面取這個表的第一行，判斷 ID 值是不是10，如果不是則跳過，如果是則將這行存在結果集中；
2. 呼叫引擎介面取"下一行"，重複相同的判斷邏輯，直到取到這個表的最後一行。
3. 執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端。

至此，這個語句就執行完成了。

對於有索引的表，執行的邏輯也差不多。第一次呼叫的是"取滿足條件的第一行"這個介面，之後迴圈取"滿足條件的下一行"這個介面，這些介面都是引擎中已經定義好的。

你會在資料庫的慢查詢日誌中看到一個 rows_examined的欄位，表示這個語句執行過程中掃描了多少行。這個值就是在執行器每次呼叫引擎獲取資料行的時候累加的。

在有些場景下，執行器呼叫一次，在引擎內部則掃描了多行，因此引擎掃描行數跟
rows_examined並不是完全相同的。我們後面會專門有一篇文章來講儲存引擎的內部機制，裡面會有詳細的說明。

小結

今天我給你介紹了 MySQL 的邏輯架構，希望你對一個 SQL語句完整執行流程的各個階段有了一個初步的印象。由於篇幅的限制，我只是用一個查詢的例子將各個環節過了一遍。如果你還對每個環節的展開細節存有疑問，也不用擔心，後續在實戰章節中我還會再提到它們。

我給你留一個問題吧，如果表 T 中沒有欄位 k，而你執行了這個語句 select * from T where k=1;， 那肯定是會報"不存在這個列"的錯誤： "Unknown column 'k' in 'where clause'"。你覺得這個錯誤是在我們上面提到的哪個階段報出來的呢？

答案是分析器。根據文章分析器主要做兩個事情，先做詞法分析後做語法分析,詞法分析主要做的是根據mysql的關鍵字進行驗證和解析，而語法分析會在詞法解析的基礎上進一步做表名和欄位名稱的驗證和解析；

評論問題

1. 有個問題不太明白，為什麼對許可權的檢查不在最佳化器之前做？

   作者回復: 有些時候，SQL語句要操作的表不只是SQL字面上那些。比如如果有個觸發器，得在執行器階段（過程中）才能確定。最佳化器階段前是無能為力的

2. 我建立了一個沒有select許可權的使用者，執行select * from T wherek=1，報錯"select command denied"，並沒有報錯"unknown
   column"，是不是可以說明是在開啟表之後才判斷讀取的列不存在？

   作者回復:這個是一個安全方面的考慮。你想想一個使用者如果沒有檢視這個表的許可權，你是會告訴他欄位不對還是沒許可權？如果告訴他欄位不對，其實給的資訊太多了，因為沒許可權的意思還包含了：沒許可權知道欄位是否存在😄

02 | 日誌系統：一條SQL更新語句是如何執行的？

前面我們系統瞭解了一個查詢語句的執行流程，並介紹了執行過程中涉及的處理模組。相信你還記得，一條查詢語句的執行過程一般是經過聯結器、分析器、最佳化器、執行器等功能模組，最後到達儲存引擎。

那麼，一條更新語句的執行流程又是怎樣的呢？

之前你可能經常聽 DBA 同事說，MySQL可以恢復到半個月內任意一秒的狀態，驚歎的同時，你是不是心中也會不免會好奇，這是怎樣做到的呢？

我們還是從一個表的一條更新語句說起，下面是這個表的建立語句，這個表有一個主鍵ID和一個整型欄位 c：

create table T(ID int primary key, c int);

如果要將 ID=2 這一行的值加 1，SQL 語句就會這麼寫：

update T set c=c+1 where ID=2;

前面我有跟你介紹過 SQL語句基本的執行鏈路，這裡我再把那張圖拿過來，你也可以先簡單看看這個圖回顧下。首先，可以確定的說，查詢語句的那一套流程，更新語句也是同樣會走一遍。

你執行語句前要先連線資料庫，這是聯結器的工作。

前面我們說過，在一個表上有更新的時候，跟這個表有關的查詢快取會失效，所以這條語句就會把表 T 上所有快取結果都清空。這也就是我們一般不建議使用查詢快取的原因。

接下來，分析器會透過詞法和語法解析知道這是一條更新語句。最佳化器決定要使用ID 這個索引。然後，執行器負責具體執行，找到這一行，然後更新。

與查詢流程不一樣的是，更新流程還涉及兩個重要的日誌模組，它們正是我們今天要討論的主角：redo log（重做日誌）和 binlog（歸檔日誌）。如果接觸MySQL，那這兩個詞肯定是繞不過的，我後面的內容裡也會不斷地和你強調。不過話說回來，redo log 和binlog在設計上有很多有意思的地方，這些設計思路也可以用到你自己的程式裡。

重要的日誌模組：redo log

不知道你還記不記得《孔乙己》這篇文章，酒店掌櫃有一個粉板，專門用來記錄客人的賒賬記錄。如果賒賬的人不多，那麼他可以把顧客名和賬目寫在板上。但如果賒賬的人多了，粉板總會有記不下的時候，這個時候掌櫃一定還有一個專門記錄賒賬的賬本。

如果有人要賒賬或者還賬的話，掌櫃一般有兩種做法：

• 一種做法是直接把賬本翻出來，把這次賒的賬加上去或者扣除掉；

• 另一種做法是先在粉板上記下這次的賬，等打烊以後再把賬本翻出來核算。

在生意紅火櫃檯很忙時，掌櫃一定會選擇後者，因為前者操作實在是太麻煩了。首先，你得找到這個人的賒賬總額那條記錄。你想想，密密麻麻幾十頁，掌櫃要找到那個名字，可能還得帶上老花鏡慢慢找，找到之後再拿出算盤計算，最後再將結果寫回到賬本上。

這整個過程想想都麻煩。相比之下，還是先在粉板上記一下方便。你想想，如果掌櫃沒有粉板的幫助，每次記賬都得翻賬本，效率是不是低得讓人難以忍受？

同樣，在 MySQL裡也有這個問題，如果每一次的更新操作都需要寫進磁碟，然後磁碟也要找到對應的那條記錄，然後再更新，整個過程IO 成本、查詢成本都很高。為了解決這個問題，MySQL的設計者就用了類似酒店掌櫃粉板的思路來提升更新效率。

而粉板和賬本配合的整個過程，其實就是 MySQL 裡經常說到的 WAL 技術，WAL的全稱是 Write-Ahead Logging，它的關鍵點就是先寫日誌，再寫磁碟，也就是先寫粉板，等不忙的時候再寫賬本。

具體來說，當有一條記錄需要更新的時候，InnoDB 引擎就會先把記錄寫到 redo log（粉板）裡面，並更新記憶體，這個時候更新就算完成了。同時，InnoDB引擎會在適當的時候，將這個操作記錄更新到磁碟裡面，而這個更新往往是在系統比較空閒的時候做，這就像打烊以後掌櫃做的事。

如果今天賒賬的不多，掌櫃可以等打烊後再整理。但如果某天賒賬的特別多，粉板寫滿了，又怎麼辦呢？這個時候掌櫃只好放下手中的活兒，把粉板中的一部分賒賬記錄更新到賬本
中，然後把這些記錄從粉板上擦掉，為記新賬騰出空間。

與此類似，InnoDB 的 redo log 是固定大小的，比如可以配置為一組 4個檔案，每個檔案的大小是 1GB，那麼這塊"粉板"總共就可以記錄 4GB的操作。從頭開始寫，寫到末尾就又回到開頭迴圈寫，如下面這個圖所示。

write pos 是當前記錄的位置，一邊寫一邊後移，寫到第 3號檔案末尾後就回到 0 號檔案開頭。checkpoint是當前要擦除的位置，也是往後推移並且迴圈的，擦除記錄前要把記錄更新到資料檔案。

write pos 和 checkpoint之間的是"粉板"上還空著的部分，可以用來記錄新的操作。如果 write pos追上checkpoint，表示"粉板"滿了，這時候不能再執行新的更新，得停下來先擦掉一些記錄，把checkpoint 推進一下。

有了 redo log，InnoDB就可以保證即使資料庫發生異常重啟，之前提交的記錄都不會丟失，這個能力稱為crash-safe。

要理解 crash-safe這個概念，可以想想我們前面賒賬記錄的例子。只要賒賬記錄記在了粉板上或寫在了賬本上，之後即使掌櫃忘記了，比如突然停業幾天，恢復生意後依然可以透過賬本和粉板上的資料明確賒賬賬目。

重要的日誌模組：binlog

前面我們講過，MySQL 整體來看，其實就有兩塊：一塊是 Server層，它主要做的是 MySQL
功能層面的事情；還有一塊是引擎層，負責儲存相關的具體事宜。上面我們聊到的粉板redo log 是 InnoDB 引擎特有的日誌，而 Server 層也有自己的日誌，稱為binlog（歸檔日誌）。

我想你肯定會問，為什麼會有兩份日誌呢？

因為最開始 MySQL 裡並沒有 InnoDB 引擎。MySQL 自帶的引擎是 MyISAM，但是MyISAM 沒有 crash-safe 的能力，binlog 日誌只能用於歸檔。而 InnoDB是另一個公司以外掛形式引入 MySQL 的，既然只依靠 binlog 是沒有crash-safe 能力的，所以 InnoDB使用另外一套日誌系統——也就是 redolog 來實現 crash-safe 能力。

這兩種日誌有以下三點不同。

1. redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server層實現的，所有引擎都可以使用。

2. redo log 是物理日誌，記錄的是"在某個資料頁上做了什麼修改"；binlog是邏輯日誌，記錄的是這個語句的原始邏輯，比如"給 ID=2 這一行的 c欄位加 1 "。

3. redo log 是迴圈寫的，空間固定會用完；binlog是可以追加寫入的。"追加寫"是指 binlog檔案寫到一定大小後會切換到下一個，並不會覆蓋以前的日誌。

我可以認為redo log 記錄的是這個行在這個頁更新之後的狀態，binlog記錄的是sql嗎？

• Redo log不是記錄資料頁"更新之後的狀態"，而是記錄這個頁"做了什麼改動"。

• Binlog有兩種模式，statement 格式的話是記sql語句;row格式會記錄行的內容，記兩條，更新前和更新後都有。

有了對這兩個日誌的概念性理解，我們再來看執行器和 InnoDB引擎在執行這個簡單的 update 語句時的內部流程。

1. 執行器先找引擎取 ID=2 這一行。ID是主鍵，引擎直接用樹搜尋找到這一行。如果 ID=2 這一行所在的資料頁本來就在記憶體中，就直接返回給執行器；否則，需要先從磁碟讀入記憶體，然後再返回。

2. 執行器拿到引擎給的行資料，把這個值加上 1，比如原來是 N，現在就是N+1，得到新的一行資料，再呼叫引擎介面寫入這行新資料。

3. 引擎將這行新資料更新到記憶體中，同時將這個更新操作記錄到 redo log裡面，此時 redo log 處於 prepare狀態。然後告知執行器執行完成了，隨時可以提交事務。

4. 執行器生成這個操作的 binlog，並把 binlog 寫入磁碟。

5. 執行器呼叫引擎的提交事務介面，引擎把剛剛寫入的 redo log改成提交（commit）狀態，更新完成。

這裡我給出這個 update 語句的執行流程圖，圖中淺色框表示是在 InnoDB內部執行的，深色框表示是在執行器中執行的。

你可能注意到了，最後三步看上去有點"繞"，將 redo log的寫入拆成了兩個步驟： prepare 和 commit，這就是"兩階段提交"。

兩階段提交

為什麼必須有"兩階段提交"呢？這是為了讓兩份日誌之間的邏輯一致。要說明這個問題，我們得從文章開頭的那個問題說起：怎樣讓資料庫恢復到半個月內任意一秒的狀態？

前面我們說過了，binlog會記錄所有的邏輯操作，並且是採用"追加寫"的形式。如果你的 DBA承諾說半個月內可以恢復，那麼備份系統中一定會儲存最近半個月的所有binlog，同時系統會定期做整庫備份。這裡的"定期"取決於系統的重要性，可以是一天一備，也可以是一週一備。

當需要恢復到指定的某一秒時，比如某天下午兩點發現中午十二點有一次誤刪表，需要找回資料，那你可以這麼做：

• 首先，找到最近的一次全量備份，如果你運氣好，可能就是昨天晚上的一個備份，從這個備份恢復到臨時庫；

• 然後，從備份的時間點開始，將備份的 binlog依次取出來，重放到中午誤刪表之前的那個時刻。

這樣你的臨時庫就跟誤刪之前的線上庫一樣了，然後你可以把表資料從臨時庫取出來，按需要恢復到線上庫去。

好了，說完了資料恢復過程，我們回來說說，為什麼日誌需要"兩階段提交"。這裡不妨用反證法來進行解釋。

由於 redo log 和 binlog是兩個獨立的邏輯，如果不用兩階段提交，要麼就是先寫完 redo log 再寫binlog，或者採用反過來的順序。我們看看這兩種方式會有什麼問題。仍然用前面的 update 語句來做例子。假設當前 ID=2 的行，欄位 c 的值是0，再假設執行 update
語句過程中在寫完第一個日誌後，第二個日誌還沒有寫完期間發生了crash，會出現什麼情況呢？

1. 先寫 redo log 後寫 binlog。假設在 redo log 寫完，binlog還沒有寫完的時候， MySQL 程序異常重啟。由於我們前面說過的，redo log寫完之後，系統即使崩潰，仍然能夠把資料恢復回來，所以恢復後這一行 c的值是 1。但是由於 binlog 沒寫完就 crash 了，這時候 binlog裡面就沒有記錄這個語句。因此，之後備份日誌的時候，存起來的 binlog裡面就沒有這條語句。然後你會發現，如果需要用這個 binlog 來恢復臨時庫的話，由於這個語句的binlog 丟失，這個臨時庫就會少了這一次更新，恢復出來的這一行 c 的值就是0，與原庫的值不同。

2. 先寫 binlog 後寫 redo log。如果在 binlog 寫完之後 crash，由於redo log 還沒寫，崩潰恢復以後這個事務無效，所以這一行 c 的值是0。但是 binlog 裡面已經記錄了"把 c 從 0 改成1"這個日誌。所以，在之後用 binlog來恢復的時候就多了一個事務出來，恢復出來的這一行 c 的值就是1，與原庫的值不同。

可以看到，如果不使用"兩階段提交"，那麼資料庫的狀態就有可能和用它的日誌恢復出來的庫的狀態不一致。

你可能會說，這個機率是不是很低，平時也沒有什麼動不動就需要恢復臨時庫的場景呀？

其實不是的，不只是誤操作後需要用這個過程來恢復資料。當你需要擴容的時候，也就是需要再多搭建一些備庫來增加系統的讀能力的時候，現在常見的做法也是用全量備份加上應用
binlog來實現的，這個"不一致"就會導致你的線上出現主從資料庫不一致的情況。

簡單說，redo log 和 binlog都可以用於表示事務的提交狀態，而兩階段提交就是讓這兩個狀態保持邏輯上的一致。

小結

今天，我介紹了 MySQL 裡面最重要的兩個日誌，即物理日誌 redo log
和邏輯日誌 binlog。

redo log 用於保證 crash-safe 能力。innodb_flush_log_at_trx_commit這個引數設定成 1 的時候，表示每次事務的 redo log都直接持久化到磁碟。這個引數我建議你設定成 1，這樣可以保證 MySQL異常重啟之後資料不丟失。

sync_binlog 這個引數設定成 1 的時候，表示每次事務的 binlog都持久化到磁碟。這個引數我也建議你設定成 1，這樣可以保證 MySQL異常重啟之後 binlog 不丟失。

我還跟你介紹了與 MySQL日誌系統密切相關的"兩階段提交"。兩階段提交是跨系統維持資料邏輯一致性時常用的一個方案，即使你不做資料庫核心開發，日常開發中也有可能會用到。

文章的最後，我給你留一個思考題吧。前面我說到定期全量備份的週期"取決於系統重要性，有的是一天一備，有的是一週一備"。那麼在什麼場景下，一天一備會比一週一備更有優勢呢？或者說，它影響了這個資料庫系統的哪個指標？

好處是"最長恢復時間"更短。

在一天一備的模式裡，最壞情況下需要應用一天的 binlog。比如，你每天 0點做一次全量備份，而要恢復出一個到昨天晚上 23 點的備份。一週一備最壞情況就要應用一週的 binlog 了。

系統的對應指標就是 RTO（恢復目標時間）。

當然這個是有成本的，因為更頻繁全量備份需要消耗更多儲存空間，所以這個RTO 是成本換來的，就需要你根據業務重要性來評估了。

03 | 事務隔離：為什麼你改了我還看不見？

提到事務，你肯定不陌生，和資料庫打交道的時候，我們總是會用到事務。最經典的例子就是轉賬，你要給朋友小王轉100 塊錢，而此時你的銀行卡只有 100 塊錢。

轉賬過程具體到程式裡會有一系列的操作，比如查詢餘額、做加減法、更新餘額等，這些操作必須保證是一體的，不然等程式查完之後，還沒做減法之前，你這100塊錢，完全可以藉著這個時間差再查一次，然後再給另外一個朋友轉賬，如果銀行這麼整，不就亂了麼？這時就要用到"事務"這個概念了。

簡單來說，事務就是要保證一組資料庫操作，要麼全部成功，要麼全部失敗。在MySQL 中，事務支援是在引擎層實現的。你現在知道，MySQL是一個支援多引擎的系統，但並不是所有的引擎都支援事務。比如 MySQL原生的 MyISAM 引擎就不支援事務，這也是 MyISAM 被 InnoDB取代的重要原因之一。

今天的文章裡，我將會以 InnoDB 為例，剖析 MySQL在事務支援方面的特定實現，並基於原理給出相應的實踐建議，希望這些案例能加深你對MySQL 事務原理的理解。

隔離性與隔離級別

提到事務，你肯定會想到ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一致性、隔離性、永續性），今天我們就來說說其中I，也就是"隔離性"。

當資料庫上有多個事務同時執行的時候，就可能出現髒讀（dirtyread）、不可重複讀（non-repeatable read）、幻讀（phantom
read）的問題，為了解決這些問題，就有了"隔離級別"的概念。

在談隔離級別之前，你首先要知道，你隔離得越嚴實，效率就會越低。因此很多時候，我們都要在二者之間尋找一個平衡點。SQL標準的事務隔離級別包括：讀未提交（read uncommitted）、讀提交（read committed）、可重複讀（repeatable read）和序列化（serializable）。下面我逐一為你解釋：

• 讀未提交是指，一個事務還沒提交時，它做的變更就能被別的事務看到。
• 讀提交是指，一個事務提交之後，它做的變更才會被其他事務看到。
• 可重複讀是指，一個事務執行過程中看到的資料，總是跟這個事務在啟動時看到的資料是一致的。當然在可重複讀隔離級別下，未提交變更對其他事務也是不可見的。
• 序列化，顧名思義是對於同一行記錄，"寫"會加"寫鎖"，"讀"會加"讀鎖"。當出現讀寫鎖衝突的時候，後訪問的事務必須等前一個事務執行完成，才能繼續執行。

其中"讀提交"和"可重複讀"比較難理解，所以我用一個例子說明這幾種隔離級別。假設資料表
T 中只有一列，其中一行的值為 1，下面是按照時間順序執行兩個事務的行為。

mysql> create table T(c int) engine=InnoDB;

insert into T(c) values(1);

{width="7.133569553805774in"
height="8.875in"}我們來看看在不同的隔離級別下，事務 A會有哪些不同的返回結果，也就是圖裡面 V1、 V2、V3 的返回值分別是什麼。

• 若隔離級別是"讀未提交"， 則 V1 的值就是 2。這時候事務 B雖然還沒有提交，但是結果已經被 A 看到了。因此，V2、V3 也都是 2。

• 若隔離級別是"讀提交"，則 V1 是 1，V2 的值是 2。事務 B的更新在提交後才能被 A看到。所以， V3 的值也是 2。

• 若隔離級別是"可重複讀"，則 V1、V2 是 1，V3 是 2。之所以 V2 還是1，遵循的就是這個要求：事務在執行期間看到的資料前後必須是一致的。

• 若隔離級別是"序列化"，則在事務 B 執行"將 1 改成2"的時候，會被鎖住。直到事務 A 提交後，事務 B 才可以繼續執行。所以從 A的角度看， V1、V2 值是 1，V3 的值是 2。

在實現上，資料庫裡面會建立一個檢視，訪問的時候以檢視的邏輯結果為準。在"可重複讀"隔離級別下，這個檢視是在事務啟動時建立的，整個事務存在期間都用這個檢視。

在"讀提交"隔離級別下，這個檢視是在每個 SQL語句開始執行的時候建立的。這裡需要注意的是，"讀未提交"隔離級別下直接返回記錄上的最新值，沒有檢視概念；而"序列化"隔離級別下直接用加鎖的方式來避免並行訪問。

我們可以看到在不同的隔離級別下，資料庫行為是有所不同的。Oracle資料庫的預設隔離級別其實就是"讀提交"，因此對於一些從 Oracle 遷移到MySQL 的應用，為保證資料庫隔離級別的一致，你一定要記得將 MySQL的隔離級別設定為"讀提交"。

配置的方式是，將啟動引數 transaction-isolation 的值設定成READ-COMMITTED。你可以用 show variables 來檢視當前的值。

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

總結來說，存在即合理，哪個隔離級別都有它自己的使用場景，你要根據自己的業務情況來定。我想你可能會問那什麼時候需要"可重複讀"的場景呢？我們來看一個資料校對邏輯的

案例。

假設你在管理一個個人銀行賬戶表。一個表存了每個月月底的餘額，一個表存了賬單明細。這時候你要做資料校對，也就是判斷上個月的餘額和當前餘額的差額，是否與本月的賬單明細一致。你一定希望在校對過程中，即使有使用者發生了一筆新的交易，也不影響你的校對結果。

這時候使用"可重複讀"隔離級別就很方便。事務啟動時的檢視可以認為是靜態的，不受其他事務更新的影響。

事務隔離的實現

理解了事務的隔離級別，我們再來看看事務隔離具體是怎麼實現的。這裡我們展開說明"可重複讀"。

在 MySQL中，實際上每條記錄在更新的時候都會同時記錄一條回滾操作。記錄上的最新值，透過回滾操作，都可以得到前一個狀態的值。

假設一個值從 1 被按順序改成了2、3、4，在回滾日誌裡面就會有類似下面的記錄。

當前值是 4，但是在查詢這條記錄的時候，不同時刻啟動的事務會有不同的read-view。如圖中看到的，在檢視 A、B、C 裡面，這一個記錄的值分別是1、2、4，同一條記錄在系統中可以存在多個版本，就是資料庫的多版本併發控制（MVCC）。對於read-view A，要得到 1，就必須將當前值依次執行圖中所有的回滾操作得到。

同時你會發現，即使現在有另外一個事務正在將 4 改成 5，這個事務跟read-view A、B、 C 對應的事務是不會衝突的。

你一定會問，回滾日誌總不能一直保留吧，什麼時候刪除呢？答案是，在不需要的時候才刪除。也就是說，系統會判斷，當沒有事務再需要用到這些回滾日誌時，回滾日誌會被刪除。

什麼時候才不需要了呢？就是當系統裡沒有比這個回滾日誌更早的 read-view的時候。基於上面的說明，我們來討論一下為什麼建議你儘量不要使用長事務。

長事務意味著系統裡面會存在很老的事務檢視。由於這些事務隨時可能訪問資料庫裡面的任何資料，所以這個事務提交之前，資料庫裡面它可能用到的回滾記錄都必須保留，這就會導致大量佔用儲存空間。

在 MySQL 5.5 及以前的版本，回滾日誌是跟資料字典一起放在 ibdata檔案裡的，即使長事務最終提交，回滾段被清理，檔案也不會變小。我見過資料只有20GB，而回滾段有 200GB 的庫。最終只好為了清理回滾段，重建整個庫。

除了對回滾段的影響，長事務還佔用鎖資源，也可能拖垮整個庫，這個我們會在後面講鎖的時候展開。

事務的啟動方式

如前面所述，長事務有這些潛在風險，我當然是建議你儘量避免。其實很多時候業務開發同學並不是有意使用長事務，通常是由於誤用所致。MySQL的事務啟動方式有以下幾種：

1. 顯式啟動事務語句， begin 或 start transaction。配套的提交語句是commit，回滾語句是 rollback。

2. set autocommit=0，這個命令會將這個執行緒的自動提交關掉。意味著如果你只執行一個
   select語句，這個事務就啟動了，而且並不會自動提交。這個事務持續存在直到你主動執行 commit 或 rollback 語句，或者斷開連線。

有些客戶端連線框架會預設連線成功後先執行一個 set autocommit=0的命令。這就導致接下來的查詢都在事務中，如果是長連線，就導致了意外的長事務。

因此，我會建議你總是使用 set autocommit=1,透過顯式語句的方式來啟動事務。

但是有的開發同學會糾結"多一次互動"的問題。對於一個需要頻繁使用事務的業務，第二種方式每個事務在開始時都不需要主動執行一次"begin"，減少了語句的互動次數。如果你也有這個顧慮，我建議你使用commit work and chain 語法。

在 autocommit 為 1 的情況下，用 begin 顯式啟動的事務，如果執行 commit則提交事務。如果執行 commit work and chain，則是提交事務並自動啟動下一個事務，這樣也省去了再次執行 begin 語句的開銷。同時帶來的好處是從程式開發的角度明確地知道每個語句是否處於事務中。

你可以在 information_schema 庫的 innodb_trx 這個表中查詢長事務，比如下面這個語句，用於查詢持續時間超過 60s的事務。

select * from information_schema.innodb_trx 
where TIME_TO_SEC(timediff(now(),trx_started))>60;

小結

這篇文章裡面，我介紹了 MySQL的事務隔離級別的現象和實現，根據實現原理分析了長事務存在的風險，以及如何用正確的方式避免長事務。希望我舉的例子能夠幫助你理解事務，並更好地使用MySQL 的事務特性。

我給你留一個問題吧。你現在知道了系統裡面應該避免長事務，如果你是業務開發負責人同時也是資料庫負責人，你會有什麼方案來避免出現或者處理這種情況呢？

這個問題，我們可以從應用開發端和資料庫端來看。

首先，從應用開發端來看：

1. 確認是否使用了 set autocommit=0。這個確認工作可以在測試環境中開展，把 MySQL 的 general_log 開起來，然後隨便跑一個業務邏輯，透過 general_log 的日誌來確認。一般框架如果會設定這個值，也就會提供引數來控制行為，你的目標就是把它改成 1。
2. 確認是否有不必要的只讀事務。有些框架會習慣不管什麼語句先用 begin/commit 框起來。我見過有些是業務並沒有這個需要，但是也把好幾個 select 語句放到了事務中。這種只讀事務可以去掉。
3. 業務連線資料庫的時候，根據業務本身的預估，透過 SET MAX_EXECUTION_TIME 命令，來控制每個語句執行的最長時間，避免單個語句意外執行太長時間。（為什麼會意外？在後續的文章中會提到這類案例）

其次，從資料庫端來看：

1. 監控 information_schema.Innodb_trx 表，設定長事務閾值，超過就報警 / 或者 kill；
2. Percona 的 pt-kill 這個工具不錯，推薦使用；
3. 在業務功能測試階段要求輸出所有的 general_log，分析日誌行為提前發現問題；
4. 如果使用的是 MySQL 5.6 或者更新版本，把 innodb_undo_tablespaces 設定成 2（或更大的值）。如果真的出現大事務導致回滾段過大，這樣設定後清理起來更方便。

04 | 深入淺出索引（上）

提到資料庫索引，我想你並不陌生，在日常工作中會經常接觸到。比如某一個 SQL 查詢比較慢，分析完原因之後，你可能就會說“給某個欄位加個索引吧”之類的解決方案。但到底什麼是索引，索引又是如何工作的呢？今天就讓我們一起來聊聊這個話題吧。

資料庫索引的內容比較多，我分成了上下兩篇文章。索引是資料庫系統裡面最重要的概念之一，所以我希望你能夠耐心看完。在後面的實戰文章中，我也會經常引用這兩篇文章中提到的知識點，加深你對資料庫索引的理解。

一句話簡單來說，索引的出現其實就是為了提高資料查詢的效率，就像書的目錄一樣。一本 500 頁的書，如果你想快速找到其中的某一個知識點，在不借助目錄的情況下，那我估計你可得找一會兒。同樣，對於資料庫的表而言，索引其實就是它的“目錄”。

索引的常見模型

索引的出現是為了提高查詢效率，但是實現索引的方式卻有很多種，所以這裡也就引入了索引模型的概念。可以用於提高讀寫效率的資料結構很多，這裡我先給你介紹三種常見、也比較簡單的資料結構，它們分別是雜湊表、有序陣列和搜尋樹。

下面我主要從使用的角度，為你簡單分析一下這三種模型的區別。

雜湊表是一種以鍵 - 值（key-value）儲存資料的結構，我們只要輸入待查詢的值即 key，就可以找到其對應的值即 Value。雜湊的思路很簡單，把值放在陣列裡，用一個雜湊函式把 key 換算成一個確定的位置，然後把 value 放在陣列的這個位置。

不可避免地，多個 key 值經過雜湊函式的換算，會出現同一個值的情況。處理這種情況的一種方法是，拉出一個連結串列。

假設，你現在維護著一個身份證資訊和姓名的表，需要根據身份證號查詢對應的名字，這時對應的雜湊索引的示意圖如下所示：

圖中，User2 和 User4 根據身份證號算出來的值都是 N，但沒關係，後面還跟了一個連結串列。假設，這時候你要查 ID_card_n2 對應的名字是什麼，處理步驟就是：首先，將 ID_card_n2 透過雜湊函式算出 N；然後，按順序遍歷，找到 User2。

需要注意的是，圖中四個 ID_card_n 的值並不是遞增的，這樣做的好處是增加新的 User 時速度會很快，只需要往後追加。但缺點是，因為不是有序的，所以雜湊索引做區間查詢的速度是很慢的。

你可以設想下，如果你現在要找身份證號在 [ID_card_X, ID_card_Y] 這個區間的所有使用者，就必須全部掃描一遍了。所以，雜湊表這種結構適用於只有等值查詢的場景，比如 Memcached 及其他一些 NoSQL 引擎。

而有序陣列在等值查詢和範圍查詢場景中的效能就都非常優秀。還是上面這個根據身份證號查名字的例子，如果我們使用有序陣列來實現的話，示意圖如下所示：

這裡我們假設身份證號沒有重複，這個陣列就是按照身份證號遞增的順序儲存的。這時候如果你要查 ID_card_n2 對應的名字，用二分法就可以快速得到，這個時間複雜度是 O(log(N))。

同時很顯然，這個索引結構支援範圍查詢。你要查身份證號在 [ID_card_X, ID_card_Y] 區間的 User，可以先用二分法找到 ID_card_X（如果不存在 ID_card_X，就找到大於 ID_card_X 的第一個 User），然後向右遍歷，直到查到第一個大於 ID_card_Y 的身份證號，退出迴圈。

如果僅僅看查詢效率，有序陣列就是最好的資料結構了。但是，在需要更新資料的時候就麻煩了，你往中間插入一個記錄就必須得挪動後面所有的記錄，成本太高。所以，有序陣列索引只適用於靜態儲存引擎，比如你要儲存的是 2017 年某個城市的所有人口資訊，這類不會再修改的資料。

二叉搜尋樹也是課本里的經典資料結構了。還是上面根據身份證號查名字的例子，如果我們用二叉搜尋樹來實現的話，示意圖如下所示：

二叉搜尋樹的特點是：每個節點的左兒子小於父節點，父節點又小於右兒子。這樣如果你要查 ID_card_n2 的話，按照圖中的搜尋順序就是按照 UserA -> UserC -> UserF -> User2 這個路徑得到。這個時間複雜度是 O(log(N))。

當然為了維持 O(log(N)) 的查詢複雜度，你就需要保持這棵樹是平衡二叉樹。為了做這個保證，更新的時間複雜度也是 O(log(N))。

樹可以有二叉，也可以有多叉。多叉樹就是每個節點有多個兒子，兒子之間的大小保證從左到右遞增。二叉樹是搜尋效率最高的，但是實際上大多數的資料庫儲存卻並不使用二叉樹。其原因是，索引不止存在記憶體中，還要寫到磁碟上。

你可以想象一下一棵 100 萬節點的平衡二叉樹，樹高 20。一次查詢可能需要訪問 20 個資料塊。在機械硬碟時代，從磁碟隨機讀一個資料塊需要 10 ms 左右的定址時間。也就是說，對於一個 100 萬行的表，如果使用二叉樹來儲存，單獨訪問一個行可能需要 20 個 10 ms 的時間，這個查詢可真夠慢的。

為了讓一個查詢儘量少地讀磁碟，就必須讓查詢過程訪問儘量少的資料塊。那麼，我們就不應該使用二叉樹，而是要使用“N 叉”樹。這裡，“N 叉”樹中的“N”取決於資料塊的大小。

以 InnoDB 的一個整數字段索引為例，這個 N 差不多是 1200。這棵樹高是 4 的時候，就可以存 1200 的 3 次方個值，這已經 17 億了。考慮到樹根的資料塊總是在記憶體中的，一個 10 億行的表上一個整數字段的索引，查詢一個值最多隻需要訪問 3 次磁碟。其實，樹的第二層也有很大機率在記憶體中，那麼訪問磁碟的平均次數就更少了。

N 叉樹由於在讀寫上的效能優點，以及適配磁碟的訪問模式，已經被廣泛應用在資料庫引擎中了。

不管是雜湊還是有序陣列，或者 N 叉樹，它們都是不斷迭代、不斷最佳化的產物或者解決方案。資料庫技術發展到今天，跳錶、LSM 樹等資料結構也被用於引擎設計中，這裡我就不再一一展開了。

你心裡要有個概念，資料庫底層儲存的核心就是基於這些資料模型的。每碰到一個新資料庫，我們需要先關注它的資料模型，這樣才能從理論上分析出這個資料庫的適用場景。

截止到這裡，我用了半篇文章的篇幅和你介紹了不同的資料結構，以及它們的適用場景，你可能會覺得有些枯燥。但是，我建議你還是要多花一些時間來理解這部分內容，畢竟這是資料庫處理資料的核心概念之一，在分析問題的時候會經常用到。當你理解了索引的模型後，就會發現在分析問題的時候會有一個更清晰的視角，體會到引擎設計的精妙之處。

現在，我們一起進入相對偏實戰的內容吧。

在 MySQL 中，索引是在儲存引擎層實現的，所以並沒有統一的索引標準，即不同儲存引擎的索引的工作方式並不一樣。而即使多個儲存引擎支援同一種型別的索引，其底層的實現也可能不同。由於 InnoDB 儲存引擎在 MySQL 資料庫中使用最為廣泛，所以下面我就以InnoDB 為例，和你分析一下其中的索引模型。

InnoDB 的索引模型

在 InnoDB 中，表都是根據主鍵順序以索引的形式存放的，這種儲存方式的表稱為索引組織表。又因為前面我們提到的，InnoDB 使用了 B+ 樹索引模型，所以資料都是儲存在 B+ 樹中的。

每一個索引在 InnoDB 裡面對應一棵 B+ 樹。

假設，我們有一個主鍵列為 ID 的表，表中有欄位 k，並且在 k 上有索引。這個表的建表語句是：

create table T(
    id int primary key, 
    k int not null, 
    name varchar(16),
    index (k)
)engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分別為 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，兩棵樹的示例示意圖如下。

從圖中不難看出，根據葉子節點的內容，索引型別分為主鍵索引和非主鍵索引。

• 主鍵索引的葉子節點存的是整行資料。在 InnoDB 裡，主鍵索引也被稱為聚簇索引（clustered index）。

• 非主鍵索引的葉子節點內容是主鍵的值。在 InnoDB 裡，非主鍵索引也被稱為二級索引（secondary index）。

根據上面的索引結構說明，我們來討論一個問題：基於主鍵索引和普通索引的查詢有什麼區別？

• 如果語句是 select * from T where ID=500，即主鍵查詢方式，則只需要搜尋 ID 這棵 B+ 樹；
• 如果語句是 select * from T where k=5，即普通索引查詢方式，則需要先搜尋 k 索引樹，得到 ID 的值為 500，再到 ID 索引樹搜尋一次。這個過程稱為回表。

也就是說，基於非主鍵索引的查詢需要多掃描一棵索引樹。因此，我們在應用中應該儘量使用主鍵查詢。

索引維護

B+ 樹為了維護索引有序性，在插入新值的時候需要做必要的維護。以上面這個圖為例，如果插入新的行 ID 值為 700，則只需要在 R5 的記錄後面插入一個新記錄。如果新插入的 ID 值為 400，就相對麻煩了，需要邏輯上挪動後面的資料，空出位置。

而更糟的情況是，如果 R5 所在的資料頁已經滿了，根據 B+ 樹的演算法，這時候需要申請一個新的資料頁，然後挪動部分資料過去。這個過程稱為頁分裂。在這種情況下，效能自然會受影響。

除了效能外，頁分裂操作還影響資料頁的利用率。原本放在一個頁的資料，現在分到兩個頁中，整體空間利用率降低大約 50%。

當然有分裂就有合併。當相鄰兩個頁由於刪除了資料，利用率很低之後，會將資料頁做合併。合併的過程，可以認為是分裂過程的逆過程。

基於上面的索引維護過程說明，我們來討論一個案例：

你可能在一些建表規範裡面見到過類似的描述，要求建表語句裡一定要有自增主鍵。當然事無絕對，我們來分析一下哪些場景下應該使用自增主鍵，而哪些場景下不應該。

自增主鍵是指自增列上定義的主鍵，在建表語句中一般是這麼定義的： NOT NULL PRIMARY KEY AUTO_INCREMENT。

插入新記錄的時候可以不指定 ID 的值，系統會獲取當前 ID 最大值加 1 作為下一條記錄的 ID 值。

也就是說，自增主鍵的插入資料模式，正符合了我們前面提到的遞增插入的場景。每次插入一條新記錄，都是追加操作，都不涉及到挪動其他記錄，也不會觸發葉子節點的分裂。

而有業務邏輯的欄位做主鍵，則往往不容易保證有序插入，這樣寫資料成本相對較高。

除了考慮效能外，我們還可以從儲存空間的角度來看。假設你的表中確實有一個唯一欄位，比如字串型別的身份證號，那應該用身份證號做主鍵，還是用自增欄位做主鍵呢？

由於每個非主鍵索引的葉子節點上都是主鍵的值。如果用身份證號做主鍵，那麼每個二級索引的葉子節點佔用約 20 個位元組，而如果用整型做主鍵，則只要 4 個位元組，如果是長整型（bigint）則是 8 個位元組。

顯然，主鍵長度越小，普通索引的葉子節點就越小，普通索引佔用的空間也就越小。

所以，從效能和儲存空間方面考量，自增主鍵往往是更合理的選擇。

有沒有什麼場景適合用業務欄位直接做主鍵的呢？還是有的。比如，有些業務的場景需求是這樣的：

1. 只有一個索引；
2. 該索引必須是唯一索引。

你一定看出來了，這就是典型的 KV 場景。

由於沒有其他索引，所以也就不用考慮其他索引的葉子節點大小的問題。

這時候我們就要優先考慮上一段提到的“儘量使用主鍵查詢”原則，直接將這個索引設定為主鍵，可以避免每次查詢需要搜尋兩棵樹。

小結

今天，我跟你分析了資料庫引擎可用的資料結構，介紹了 InnoDB 採用的 B+ 樹結構，以及為什麼 InnoDB 要這麼選擇。B+ 樹能夠很好地配合磁碟的讀寫特性，減少單次查詢的磁碟訪問次數。

由於 InnoDB 是索引組織表，一般情況下我會建議你建立一個自增主鍵，這樣非主鍵索引佔用的空間最小。但事無絕對，我也跟你討論了使用業務邏輯欄位做主鍵的應用場景。

最後，我給你留下一個問題吧。對於上面例子中的 InnoDB 表 T，如果你要重建索引 k，你的兩個 SQL 語句可以這麼寫：

alter table T drop index k;
alter table T add index(k);

如果你要重建主鍵索引，也可以這麼寫：

alter table T drop primary key;
alter table T add primary key(id);

我的問題是，對於上面這兩個重建索引的作法，說出你的理解。如果有不合適的，為什麼，更好的方法是什麼？

上面的問題是，透過兩個 alter 語句重建索引 k，以及透過兩個 alter 語句重建主鍵索引是否合理。

在評論區，有同學問到為什麼要重建索引。我們文章裡面有提到，索引可能因為刪除，或者頁分裂等原因，導致資料頁有空洞，重建索引的過程會建立一個新的索引，把資料按順序插入，這樣頁面的利用率最高，也就是索引更緊湊、更省空間。

這道題目，我給你的“參考答案”是：

重建索引 k 的做法是合理的，可以達到省空間的目的。但是，重建主鍵的過程不合理。不論是刪除主鍵還是建立主鍵，都會將整個表重建。所以連著執行這兩個語句的話，第一個語句就白做了。這兩個語句，你可以用這個語句代替 ： alter table T engine=InnoDB。在專欄的第 12 篇文章《為什麼表資料刪掉一半，表檔案大小不變？》中，我會和你分析這條語句的執行流程。

05 | 深入淺出索引（下）

在開始這篇文章之前，我們先來看一下這個問題：

在下面這個表 T 中，如果我執行 select * from T where k between 3 and 5，需要執行幾次樹的搜尋操作，會掃描多少行？

下面是這個表的初始化語句。

create table T (
    ID int primary key,
    k int NOT NULL DEFAULT 0, 
    s varchar(16) NOT NULL DEFAULT '',
    index k(k)
)engine=InnoDB;
 
insert into T values(100,1, 'aa'),(200,2,'bb'),(300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');

現在，我們一起來看看這條 SQL 查詢語句的執行流程：

1. 在 k 索引樹上找到 k=3 的記錄，取得 ID = 300；
2. 再到 ID 索引樹查到 ID=300 對應的 R3；
3. 在 k 索引樹取下一個值 k=5，取得 ID=500；
4. 再回到 ID 索引樹查到 ID=500 對應的 R4；
5. 在 k 索引樹取下一個值 k=6，不滿足條件，迴圈結束。

在這個過程中，回到主鍵索引樹搜尋的過程，我們稱為回表。可以看到，這個查詢過程讀了 k 索引樹的 3 條記錄（步驟 1、3 和 5），回表了兩次（步驟 2 和 4）。

在這個例子中，由於查詢結果所需要的資料只在主鍵索引上有，所以不得不回表。那麼，有沒有可能經過索引最佳化，避免回表過程呢？

覆蓋索引

如果執行的語句是 select ID from T where k between 3 and 5，這時只需要查 ID 的值，而 ID 的值已經在 k 索引樹上了，因此可以直接提供查詢結果，不需要回表。也就是說，在這個查詢裡面，索引 k 已經“覆蓋了”我們的查詢需求，我們稱為覆蓋索引。

由於覆蓋索引可以減少樹的搜尋次數，顯著提升查詢效能，所以使用覆蓋索引是一個常用的效能最佳化手段。

需要注意的是，在引擎內部使用覆蓋索引在索引 k 上其實讀了三個記錄，R3~R5（對應的索引 k 上的記錄項），但是對於 MySQL 的 Server 層來說，它就是找引擎拿到了兩條記錄，因此 MySQL 認為掃描行數是 2。

備註：關於如何檢視掃描行數的問題，我將會在第 16 文章《如何正確地顯示隨機訊息？》中，和你詳細討論。

基於上面覆蓋索引的說明，我們來討論一個問題：在一個市民資訊表上，是否有必要將身份證號和名字建立聯合索引？

假設這個市民表的定義是這樣的：

CREATE TABLE `tuser` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

我們知道，身份證號是市民的唯一標識。也就是說，如果有根據身份證號查詢市民資訊的需求，我們只要在身份證號欄位上建立索引就夠了。而再建立一個（身份證號、姓名）的聯合索引，是不是浪費空間？

如果現在有一個高頻請求，要根據市民的身份證號查詢他的姓名，這個聯合索引就有意義了。它可以在這個高頻請求上用到覆蓋索引，不再需要回表查整行記錄，減少語句的執行時間。

當然，索引欄位的維護總是有代價的。因此，在建立冗餘索引來支援覆蓋索引時就需要權衡考慮了。這正是業務 DBA，或者稱為業務資料架構師的工作。

最左字首原則

看到這裡你一定有一個疑問，如果為每一種查詢都設計一個索引，索引是不是太多了。如果我現在要按照市民的身份證號去查他的家庭地址呢？雖然這個查詢需求在業務中出現的機率不高，但總不能讓它走全表掃描吧？反過來說，單獨為一個不頻繁的請求建立一個（身份證號，地址）的索引又感覺有點浪費。應該怎麼做呢？

這裡，我先和你說結論吧。B+ 樹這種索引結構，可以利用索引的“最左字首”，來定位記錄。

為了直觀地說明這個概念，我們用（name，age）這個聯合索引來分析。

可以看到，索引項是按照索引定義裡面出現的欄位順序排序的。

當你的邏輯需求是查到所有名字是“張三”的人時，可以快速定位到 ID4，然後向後遍歷得到所有需要的結果。

如果你要查的是所有名字第一個字是“張”的人，你的 SQL 語句的條件是"where name like ‘張 %’"。這時，你也能夠用上這個索引，查詢到第一個符合條件的記錄是 ID3，然後向後遍歷，直到不滿足條件為止。

可以看到，不只是索引的全部定義，只要滿足最左字首，就可以利用索引來加速檢索。這個最左字首可以是聯合索引的最左 N 個欄位，也可以是字串索引的最左 M 個字元。

基於上面對最左字首索引的說明，我們來討論一個問題：在建立聯合索引的時候，如何安排索引內的欄位順序。

這裡我們的評估標準是，索引的複用能力。因為可以支援最左字首，所以當已經有了 (a,b) 這個聯合索引後，一般就不需要單獨在 a 上建立索引了。因此，第一原則是，如果透過調整順序，可以少維護一個索引，那麼這個順序往往就是需要優先考慮採用的。

所以現在你知道了，這段開頭的問題裡，我們要為高頻請求建立 (身份證號，姓名）這個聯合索引，並用這個索引支援“根據身份證號查詢地址”的需求。

那麼，如果既有聯合查詢，又有基於 a、b 各自的查詢呢？查詢條件裡面只有 b 的語句，是無法使用 (a,b) 這個聯合索引的，這時候你不得不維護另外一個索引，也就是說你需要同時維護 (a,b)、(b) 這兩個索引。

這時候，我們要考慮的原則就是空間了。比如上面這個市民表的情況，name 欄位是比 age 欄位大的 ，那我就建議你建立一個（name,age) 的聯合索引和一個 (age) 的單欄位索引。

索引下推

上一段我們說到滿足最左字首原則的時候，最左字首可以用於在索引中定位記錄。這時，你可能要問，那些不符合最左字首的部分，會怎麼樣呢？

我們還是以市民表的聯合索引（name, age）為例。如果現在有一個需求：檢索出表中“名字第一個字是張，而且年齡是 10 歲的所有男孩”。那麼，SQL 語句是這麼寫的:

select * from tuser where name like '張 %' and age=10 and ismale=1;

你已經知道了字首索引規則，所以這個語句在搜尋索引樹的時候，只能用 “張”，找到第一個滿足條件的記錄 ID3。當然，這還不錯，總比全表掃描要好。

然後呢？

當然是判斷其他條件是否滿足。

在 MySQL 5.6 之前，只能從 ID3 開始一個個回表。到主鍵索引上找出資料行，再對比欄位值。

而 MySQL 5.6 引入的索引下推最佳化（index condition pushdown)， 可以在索引遍歷過程中，對索引中包含的欄位先做判斷，直接過濾掉不滿足條件的記錄，減少回表次數。

下面兩個圖是這兩個過程的執行流程圖。

這兩個圖裡面，每一個虛線箭頭表示回表一次。

第一個圖在 (name,age) 索引裡面我特意去掉了 age 的值，這個過程 InnoDB 並不會去看 age 的值，只是按順序把“name 第一個字是’張’”的記錄一條條取出來回表。因此，需要回表 4 次。

第二個圖跟第一個圖的區別是，InnoDB 在 (name,age) 索引內部就判斷了 age 是否等於 10，對於不等於 10 的記錄，直接判斷並跳過。在我們的這個例子中，只需要對 ID4、ID5 這兩條記錄回表取資料判斷，就只需要回表 2 次。

小結

今天這篇文章，我和你繼續討論了資料庫索引的概念，包括了覆蓋索引、字首索引、索引下推。你可以看到，在滿足語句需求的情況下， 儘量少地訪問資源是資料庫設計的重要原則之一。我們在使用資料庫的時候，尤其是在設計表結構時，也要以減少資源消耗作為目標。

接下來我給你留下一個問題吧。

實際上主鍵索引也是可以使用多個欄位的。DBA 小呂在入職新公司的時候，就發現自己接手維護的庫裡面，有這麼一個表，表結構定義類似這樣的：

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

公司的同事告訴他說，由於歷史原因，這個表需要 a、b 做聯合主鍵，這個小呂理解了。

但是，學過本章內容的小呂又納悶了，既然主鍵包含了 a、b 這兩個欄位，那意味著單獨在欄位 c 上建立一個索引，就已經包含了三個欄位了呀，為什麼要建立“ca”“cb”這兩個索引？

同事告訴他，是因為他們的業務裡面有這樣的兩種語句：

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

我給你的問題是，這位同事的解釋對嗎，為了這兩個查詢模式，這兩個索引是否都是必須的？為什麼呢？

我直接貼 @老楊同志 的回覆略作修改如下：

表記錄
–a--|–b--|–c--|–d--
1 2 3 d
1 3 2 d
1 4 3 d
2 1 3 d
2 2 2 d
2 3 4 d
主鍵 a，b 的聚簇索引組織順序相當於 order by a,b ，也就是先按 a 排序，再按 b 排序，c 無序。

索引 ca 的組織是先按 c 排序，再按 a 排序，同時記錄主鍵
–c--|–a--|–主鍵部分b-- （注意，這裡不是 ab，而是隻有 b）
2 1 3
2 2 2
3 1 2
3 1 4
3 2 1
4 2 3
這個跟索引 c 的資料是一模一樣的。

索引 cb 的組織是先按 c 排序，在按 b 排序，同時記錄主鍵
–c--|–b--|–主鍵部分a-- （同上）
2 2 2
2 3 1
3 1 2
3 2 1
3 4 1
4 3 2

所以，結論是 ca 可以去掉，cb 需要保留。

06 | 全域性鎖和表鎖 ：給表加個欄位怎麼有這麼多阻礙？

今天我要跟你聊聊 MySQL 的鎖。資料庫鎖設計的初衷是處理併發問題。作為多使用者共享的資源，當出現併發訪問的時候，資料庫需要合理地控制資源的訪問規則。而鎖就是用來實現這些訪問規則的重要資料結構。

根據加鎖的範圍，MySQL 裡面的鎖大致可以分成全域性鎖、表級鎖和行鎖三類。今天這篇文章，我會和你分享全域性鎖和表級鎖。而關於行鎖的內容，我會留著在下一篇文章中再和你詳細介紹。

這裡需要說明的是，鎖的設計比較複雜，這兩篇文章不會涉及鎖的具體實現細節，主要介紹的是碰到鎖時的現象和其背後的原理。

全域性鎖

顧名思義，全域性鎖就是對整個資料庫例項加鎖。MySQL 提供了一個加全域性讀鎖的方法，命令是 Flush tables with read lock (FTWRL)。當你需要讓整個庫處於只讀狀態的時候，可以使用這個命令，之後其他執行緒的以下語句會被阻塞：資料更新語句（資料的增刪改）、資料定義語句（包括建表、修改表結構等）和更新類事務的提交語句。

全域性鎖的典型使用場景是，做全庫邏輯備份。也就是把整庫每個表都 select 出來存成文字。

以前有一種做法，是透過 FTWRL 確保不會有其他執行緒對資料庫做更新，然後對整個庫做備份。注意，在備份過程中整個庫完全處於只讀狀態。

但是讓整庫都只讀，聽上去就很危險：

• 如果你在主庫上備份，那麼在備份期間都不能執行更新，業務基本上就得停擺；
• 如果你在從庫上備份，那麼備份期間從庫不能執行主庫同步過來的 binlog，會導致主從延遲。

看來加全域性鎖不太好。但是細想一下，備份為什麼要加鎖呢？我們來看一下不加鎖會有什麼問題。

假設你現在要維護“極客時間”的購買系統，關注的是使用者賬戶餘額表和使用者課程表。

現在發起一個邏輯備份。假裝置份期間，有一個使用者，他購買了一門課程，業務邏輯裡就要扣掉他的餘額，然後往已購課程裡面加上一門課。

如果時間順序上是先備份賬戶餘額表 (u_account)，然後使用者購買，然後備份使用者課程表 (u_course)，會怎麼樣呢？你可以看一下這個圖：

圖 1 業務和備份狀態圖

可以看到，這個備份結果裡，使用者 A 的資料狀態是“賬戶餘額沒扣，但是使用者課程表裡面已經多了一門課”。如果後面用這個備份來恢復資料的話，使用者 A 就發現，自己賺了。

作為使用者可別覺得這樣可真好啊，你可以試想一下：如果備份表的順序反過來，先備份使用者課程表再備份賬戶餘額表，又可能會出現什麼結果？

也就是說，不加鎖的話，備份系統備份的得到的庫不是一個邏輯時間點，這個檢視是邏輯不一致的。

說到檢視你肯定想起來了，我們在前面講事務隔離的時候，其實是有一個方法能夠拿到一致性檢視的，對吧？

是的，就是在可重複讀隔離級別下開啟一個事務。

備註：如果你對事務隔離級別的概念不是很清晰的話，可以再回顧一下第 3 篇文章《事務隔離：為什麼你改了我還看不見？》中的相關內容。

官方自帶的邏輯備份工具是 mysqldump。當 mysqldump 使用引數–single-transaction 的時候，導資料之前就會啟動一個事務，來確保拿到一致性檢視。而由於 MVCC 的支援，這個過程中資料是可以正常更新的。

你一定在疑惑，有了這個功能，為什麼還需要 FTWRL 呢？一致性讀是好，但前提是引擎要支援這個隔離級別。比如，對於 MyISAM 這種不支援事務的引擎，如果備份過程中有更新，總是隻能取到最新的資料，那麼就破壞了備份的一致性。這時，我們就需要使用 FTWRL 命令了。

所以，single-transaction 方法只適用於所有的表使用事務引擎的庫。如果有的表使用了不支援事務的引擎，那麼備份就只能透過 FTWRL 方法。這往往是 DBA 要求業務開發人員使用 InnoDB 替代 MyISAM 的原因之一。

你也許會問，既然要全庫只讀，為什麼不使用 set global readonly=true 的方式呢？確實 readonly 方式也可以讓全庫進入只讀狀態，但我還是會建議你用 FTWRL 方式，主要有兩個原因：

• 一是，在有些系統中，readonly 的值會被用來做其他邏輯，比如用來判斷一個庫是主庫還是備庫。因此，修改 global 變數的方式影響面更大，我不建議你使用。
• 二是，在異常處理機制上有差異。如果執行 FTWRL 命令之後由於客戶端發生異常斷開，那麼 MySQL 會自動釋放這個全域性鎖，整個庫回到可以正常更新的狀態。而將整個庫設定為 readonly 之後，如果客戶端發生異常，則資料庫就會一直保持 readonly 狀態，這樣會導致整個庫長時間處於不可寫狀態，風險較高。

業務的更新不只是增刪改資料（DML)，還有可能是加欄位等修改表結構的操作（DDL）。不論是哪種方法，一個庫被全域性鎖上以後，你要對裡面任何一個表做加欄位操作，都是會被鎖住的。

但是，即使沒有被全域性鎖住，加欄位也不是就能一帆風順的，因為你還會碰到接下來我們要介紹的表級鎖。

表級鎖

MySQL 裡面表級別的鎖有兩種：一種是表鎖，一種是後設資料鎖（meta data lock，MDL)。

表鎖的語法是 lock tables … read/write。與 FTWRL 類似，可以用 unlock tables 主動釋放鎖，也可以在客戶端斷開的時候自動釋放。需要注意，lock tables 語法除了會限制別的執行緒的讀寫外，也限定了本執行緒接下來的操作物件。

舉個例子, 如果在某個執行緒 A 中執行 lock tables t1 read, t2 write; 這個語句，則其他執行緒寫 t1、讀寫 t2 的語句都會被阻塞。同時，執行緒 A 在執行 unlock tables 之前，也只能執行讀 t1、讀寫 t2 的操作。連寫 t1 都不允許，自然也不能訪問其他表。

在還沒有出現更細粒度的鎖的時候，表鎖是最常用的處理併發的方式。而對於 InnoDB 這種支援行鎖的引擎，一般不使用 lock tables 命令來控制併發，畢竟鎖住整個表的影響面還是太大。

另一類表級的鎖是 MDL（metadata lock)。MDL 不需要顯式使用，在訪問一個表的時候會被自動加上。MDL 的作用是，保證讀寫的正確性。你可以想象一下，如果一個查詢正在遍歷一個表中的資料，而執行期間另一個執行緒對這個表結構做變更，刪了一列，那麼查詢執行緒拿到的結果跟表結構對不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，當對一個表做增刪改查操作的時候，加 MDL 讀鎖；當要對錶做結構變更操作的時候，加 MDL 寫鎖。

• 讀鎖之間不互斥，因此你可以有多個執行緒同時對一張表增刪改查。
• 讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性。因此，如果有兩個執行緒要同時給一個表加欄位，其中一個要等另一個執行完才能開始執行。

雖然 MDL 鎖是系統預設會加的，但卻是你不能忽略的一個機制。比如下面這個例子，我經常看到有人掉到這個坑裡：給一個小表加個欄位，導致整個庫掛了。

你肯定知道，給一個表加欄位，或者修改欄位，或者加索引，需要掃描全表的資料。在對大表操作的時候，你肯定會特別小心，以免對線上服務造成影響。而實際上，即使是小表，操作不慎也會出問題。我們來看一下下面的操作序列，假設表 t 是一個小表。

備註：這裡的實驗環境是 MySQL 5.6。

我們可以看到 session A 先啟動，這時候會對錶 t 加一個 MDL 讀鎖。由於 session B 需要的也是 MDL 讀鎖，因此可以正常執行。

之後 session C 會被 blocked，是因為 session A 的 MDL 讀鎖還沒有釋放，而 session C 需要 MDL 寫鎖，因此只能被阻塞。

如果只有 session C 自己被阻塞還沒什麼關係，但是之後所有要在表 t 上新申請 MDL 讀鎖的請求也會被 session C 阻塞。前面我們說了，所有對錶的增刪改查操作都需要先申請 MDL 讀鎖，就都被鎖住，等於這個表現在完全不可讀寫了。

如果某個表上的查詢語句頻繁，而且客戶端有重試機制，也就是說超時後會再起一個新session 再請求的話，這個庫的執行緒很快就會爆滿。

你現在應該知道了，事務中的 MDL 鎖，在語句執行開始時申請，但是語句結束後並不會馬上釋放，而會等到整個事務提交後再釋放。

基於上面的分析，我們來討論一個問題，如何安全地給小表加欄位？

首先我們要解決長事務，事務不提交，就會一直佔著 MDL 鎖。在 MySQL 的information_schema 庫的 innodb_trx 表中，你可以查到當前執行中的事務。如果你要做 DDL 變更的表剛好有長事務在執行，要考慮先暫停 DDL，或者 kill 掉這個長事務。

但考慮一下這個場景。如果你要變更的表是一個熱點表，雖然資料量不大，但是上面的請求很頻繁，而你不得不加個欄位，你該怎麼做呢？

這時候 kill 可能未必管用，因為新的請求馬上就來了。比較理想的機制是，在 alter table 語句裡面設定等待時間，如果在這個指定的等待時間裡面能夠拿到 MDL 寫鎖最好，拿不到也不要阻塞後面的業務語句，先放棄。之後開發人員或者 DBA 再透過重試命令重複這個過程。

MariaDB 已經合併了 AliSQL 的這個功能，所以這兩個開源分支目前都支援 DDL NOWAIT / WAIT n 這個語法。

LTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

小結

今天，我跟你介紹了 MySQL 的全域性鎖和表級鎖。

全域性鎖主要用在邏輯備份過程中。對於全部是 InnoDB 引擎的庫，我建議你選擇使用–single-transaction 引數，對應用會更友好。

表鎖一般是在資料庫引擎不支援行鎖的時候才會被用到的。如果你發現你的應用程式裡有 lock tables 這樣的語句，你需要追查一下，比較可能的情況是：

• 要麼是你的系統現在還在用 MyISAM 這類不支援事務的引擎，那要安排升級換引擎；
• 要麼是你的引擎升級了，但是程式碼還沒升級。我見過這樣的情況，最後業務開發就是把 lock tables 和 unlock tables 改成 begin 和 commit，問題就解決了。

MDL 會直到事務提交才釋放，在做表結構變更的時候，你一定要小心不要導致鎖住線上查詢和更新。

說明：這篇文章沒有介紹到物理備份，物理備份會有一篇單獨的文章。

最後，我給你留一個問題吧。備份一般都會在備庫上執行，你在用–single-transaction 方法做邏輯備份的過程中，如果主庫上的一個小表做了一個 DDL，比如給一個表上加了一列。這時候，從備庫上會看到什麼現象呢？

假設這個 DDL 是針對表 t1 的， 這裡我把備份過程中幾個關鍵的語句列出來：

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 時刻 1 */
Q4:show create table `t1`;
/* 時刻 2 */
Q5:SELECT * FROM `t1`;
/* 時刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 時刻 4 */
/* other tables */

在備份開始的時候，為了確保 RR（可重複讀）隔離級別，再設定一次 RR 隔離級別 (Q1);

啟動事務，這裡用 WITH CONSISTENT SNAPSHOT 確保這個語句執行完就可以得到一個一致性檢視（Q2)；

設定一個儲存點，這個很重要（Q3）；

show create 是為了拿到表結構 (Q4)，然後正式導資料 （Q5），回滾到 SAVEPOINT sp，在這裡的作用是釋放 t1 的 MDL 鎖 （Q6）。當然這部分屬於“超綱”，上文正文裡面都沒提到。

DDL 從主庫傳過來的時間按照效果不同，我打了四個時刻。題目設定為小表，我們假定到達後，如果開始執行，則很快能夠執行完成。

參考答案如下：

1. 如果在 Q4 語句執行之前到達，現象：沒有影響，備份拿到的是 DDL 後的表結構。
2. 如果在“時刻 2”到達，則表結構被改過，Q5 執行的時候，報 Table definition has changed, please retry transaction，現象：mysqldump 終止；
3. 如果在“時刻 2”和“時刻 3”之間到達，mysqldump 佔著 t1 的 MDL 讀鎖，binlog 被阻塞，現象：主從延遲，直到 Q6 執行完成。
4. 從“時刻 4”開始，mysqldump 釋放了 MDL 讀鎖，現象：沒有影響，備份拿到的是 DDL 前的表結構。

07 | 行鎖功過：怎麼減少行鎖對效能的影響？

在上一篇文章中，我跟你介紹了 MySQL 的全域性鎖和表級鎖，今天我們就來講講 MySQL 的行鎖。

MySQL 的行鎖是在引擎層由各個引擎自己實現的。但並不是所有的引擎都支援行鎖，比如 MyISAM 引擎就不支援行鎖。不支援行鎖意味著併發控制只能使用表鎖，對於這種引擎的表，同一張表上任何時刻只能有一個更新在執行，這就會影響到業務併發度。InnoDB 是支援行鎖的，這也是 MyISAM 被 InnoDB 替代的重要原因之一。

我們今天就主要來聊聊 InnoDB 的行鎖，以及如何透過減少鎖衝突來提升業務併發度。

顧名思義，行鎖就是針對資料表中行記錄的鎖。這很好理解，比如事務 A 更新了一行，而這時候事務 B 也要更新同一行，則必須等事務 A 的操作完成後才能進行更新。

當然，資料庫中還有一些沒那麼一目瞭然的概念和設計，這些概念如果理解和使用不當，容易導致程式出現非預期行為，比如兩階段鎖。

從兩階段鎖說起

我先給你舉個例子。在下面的操作序列中，事務 B 的 update 語句執行時會是什麼現象呢？假設欄位 id 是表 t 的主鍵。

這個問題的結論取決於事務 A 在執行完兩條 update 語句後，持有哪些鎖，以及在什麼時候釋放。你可以驗證一下：實際上事務 B 的 update 語句會被阻塞，直到事務 A 執行 commit 之後，事務 B 才能繼續執行。

知道了這個答案，你一定知道了事務 A 持有的兩個記錄的行鎖，都是在 commit 的時候才釋放的。

也就是說，在 InnoDB 事務中，行鎖是在需要的時候才加上的，但並不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協議。

知道了這個設定，對我們使用事務有什麼幫助呢？那就是，如果你的事務中需要鎖多個行，要把最可能造成鎖衝突、最可能影響併發度的鎖儘量往後放。我給你舉個例子。

假設你負責實現一個電影票線上交易業務，顧客 A 要在影院 B 購買電影票。我們簡化一點，這個業務需要涉及到以下操作：

1. 從顧客 A 賬戶餘額中扣除電影票價；
2. 給影院 B 的賬戶餘額增加這張電影票價；
3. 記錄一條交易日誌。

也就是說，要完成這個交易，我們需要 update 兩條記錄，並 insert 一條記錄。當然，為了保證交易的原子性，我們要把這三個操作放在一個事務中。那麼，你會怎樣安排這三個語句在事務中的順序呢？

試想如果同時有另外一個顧客 C 要在影院 B 買票，那麼這兩個事務衝突的部分就是語句 2 了。因為它們要更新同一個影院賬戶的餘額，需要修改同一行資料。

根據兩階段鎖協議，不論你怎樣安排語句順序，所有的操作需要的行鎖都是在事務提交的時候才釋放的。所以，如果你把語句 2 安排在最後，比如按照 3、1、2 這樣的順序，那麼影院賬戶餘額這一行的鎖時間就最少。這就最大程度地減少了事務之間的鎖等待，提升了併發度。

好了，現在由於你的正確設計，影院餘額這一行的行鎖在一個事務中不會停留很長時間。但是，這並沒有完全解決你的困擾。

如果這個影院做活動，可以低價預售一年內所有的電影票，而且這個活動只做一天。於是在活動時間開始的時候，你的 MySQL 就掛了。你登上伺服器一看，CPU 消耗接近 100%，但整個資料庫每秒就執行不到 100 個事務。這是什麼原因呢？

這裡，我就要說到死鎖和死鎖檢測了。

死鎖和死鎖檢測

當併發系統中不同執行緒出現迴圈資源依賴，涉及的執行緒都在等待別的執行緒釋放資源時，就會導致這幾個執行緒都進入無限等待的狀態，稱為死鎖。這裡我用資料庫中的行鎖舉個例子。

這時候，事務 A 在等待事務 B 釋放 id=2 的行鎖，而事務 B 在等待事務 A 釋放 id=1 的行鎖。 事務 A 和事務 B 在互相等待對方的資源釋放，就是進入了死鎖狀態。當出現死鎖以後，有兩種策略：

• 一種策略是，直接進入等待，直到超時。這個超時時間可以透過引數 innodb_lock_wait_timeout 來設定。
• 另一種策略是，發起死鎖檢測，發現死鎖後，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行。將引數 innodb_deadlock_detect 設定為 on，表示開啟這個邏輯。

在 InnoDB 中，innodb_lock_wait_timeout 的預設值是 50s，意味著如果採用第一個策略，當出現死鎖以後，第一個被鎖住的執行緒要過 50s 才會超時退出，然後其他執行緒才有可能繼續執行。對於線上服務來說，這個等待時間往往是無法接受的。

但是，我們又不可能直接把這個時間設定成一個很小的值，比如 1s。這樣當出現死鎖的時候，確實很快就可以解開，但如果不是死鎖，而是簡單的鎖等待呢？所以，超時時間設定太短的話，會出現很多誤傷。

所以，正常情況下我們還是要採用第二種策略，即：主動死鎖檢測，而且 innodb_deadlock_detect 的預設值本身就是 on。主動死鎖檢測在發生死鎖的時候，是能夠快速發現並進行處理的，但是它也是有額外負擔的。

你可以想象一下這個過程：每當一個事務被鎖的時候，就要看看它所依賴的執行緒有沒有被別人鎖住，如此迴圈，最後判斷是否出現了迴圈等待，也就是死鎖。

那如果是我們上面說到的所有事務都要更新同一行的場景呢？

每個新來的被堵住的執行緒，都要判斷會不會由於自己的加入導致了死鎖，這是一個時間複雜度是 O(n) 的操作。假設有 1000 個併發執行緒要同時更新同一行，那麼死鎖檢測操作就是 100 萬這個量級的。雖然最終檢測的結果是沒有死鎖，但是這期間要消耗大量的 CPU 資源。因此，你就會看到 CPU 利用率很高，但是每秒卻執行不了幾個事務。

根據上面的分析，我們來討論一下，怎麼解決由這種熱點行更新導致的效能問題呢？問題的癥結在於，死鎖檢測要耗費大量的 CPU 資源。

一種頭痛醫頭的方法，就是如果你能確保這個業務一定不會出現死鎖，可以臨時把死鎖檢測關掉。但是這種操作本身帶有一定的風險，因為業務設計的時候一般不會把死鎖當做一個嚴重錯誤，畢竟出現死鎖了，就回滾，然後透過業務重試一般就沒問題了，這是業務無損的。而關掉死鎖檢測意味著可能會出現大量的超時，這是業務有損的。

另一個思路是控制併發度。根據上面的分析，你會發現如果併發能夠控制住，比如同一行同時最多隻有 10 個執行緒在更新，那麼死鎖檢測的成本很低，就不會出現這個問題。一個直接的想法就是，在客戶端做併發控制。但是，你會很快發現這個方法不太可行，因為客戶端很多。我見過一個應用，有 600 個客戶端，這樣即使每個客戶端控制到只有 5 個併發執行緒，彙總到資料庫服務端以後，峰值併發數也可能要達到 3000。

因此，這個併發控制要做在資料庫服務端。如果你有中介軟體，可以考慮在中介軟體實現；如果你的團隊有能修改 MySQL 原始碼的人，也可以做在 MySQL 裡面。基本思路就是，對於相同行的更新，在進入引擎之前排隊。這樣在 InnoDB 內部就不會有大量的死鎖檢測工作了。

可能你會問，如果團隊裡暫時沒有資料庫方面的專家，不能實現這樣的方案，能不能從設計上最佳化這個問題呢？

你可以考慮透過將一行改成邏輯上的多行來減少鎖衝突。還是以影院賬戶為例，可以考慮放在多條記錄上，比如 10 個記錄，影院的賬戶總額等於這 10 個記錄的值的總和。這樣每次要給影院賬戶加金額的時候，隨機選其中一條記錄來加。這樣每次衝突機率變成原來的 1/10，可以減少鎖等待個數，也就減少了死鎖檢測的 CPU 消耗。

這個方案看上去是無損的，但其實這類方案需要根據業務邏輯做詳細設計。如果賬戶餘額可能會減少，比如退票邏輯，那麼這時候就需要考慮當一部分行記錄變成 0 的時候，程式碼要有特殊處理

小結

今天，我和你介紹了 MySQL 的行鎖，涉及了兩階段鎖協議、死鎖和死鎖檢測這兩大部分內容。

其中，我以兩階段協議為起點，和你一起討論了在開發的時候如何安排正確的事務語句。這裡的原則 / 我給你的建議是：如果你的事務中需要鎖多個行，要把最可能造成鎖衝突、最可能影響併發度的鎖的申請時機儘量往後放。

但是，調整語句順序並不能完全避免死鎖。所以我們引入了死鎖和死鎖檢測的概念，以及提供了三個方案，來減少死鎖對資料庫的影響。減少死鎖的主要方向，就是控制訪問相同資源的併發事務量。

最後，我給你留下一個問題吧。如果你要刪除一個表裡面的前 10000 行資料，有以下三種方法可以做到：

• 第一種，直接執行 delete from T limit 10000;
• 第二種，在一個連線中迴圈執行 20 次 delete from T limit 500;
• 第三種，在 20 個連線中同時執行 delete from T limit 500。

你會選擇哪一種方法呢？為什麼呢？

比較多的留言都選擇了第二種方式，即：在一個連線中迴圈執行 20 次 delete from T limit 500。

確實是這樣的，第二種方式是相對較好的。

第一種方式（即：直接執行 delete from T limit 10000）裡面，單個語句佔用時間長，鎖的時間也比較長；而且大事務還會導致主從延遲。

第三種方式（即：在 20 個連線中同時執行 delete from T limit 500），會人為造成鎖衝突。

08 | 事務到底是隔離的還是不隔離的？

我在第 3 篇文章和你講事務隔離級別的時候提到過，如果是可重複讀隔離級別，事務 T 啟動的時候會建立一個檢視 read-view，之後事務 T 執行期間，即使有其他事務修改了資料，事務 T 看到的仍然跟在啟動時看到的一樣。也就是說，一個在可重複讀隔離級別下執行的事務，好像與世無爭，不受外界影響。

但是，我在上一篇文章中，和你分享行鎖的時候又提到，一個事務要更新一行，如果剛好有另外一個事務擁有這一行的行鎖，它又不能這麼超然了，會被鎖住，進入等待狀態。問題是，既然進入了等待狀態，那麼等到這個事務自己獲取到行鎖要更新資料的時候，它讀到的值又是什麼呢？

我給你舉一個例子吧。下面是一個只有兩行的表的初始化語句。

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

圖 1 事務 A、B、C 的執行流程

這裡，我們需要注意的是事務的啟動時機。

begin/start transaction 命令並不是一個事務的起點，在執行到它們之後的第一個操作 InnoDB 表的語句，事務才真正啟動。如果你想要馬上啟動一個事務，可以使用 start transaction with consistent snapshot 這個命令。

第一種啟動方式，一致性檢視是在第執行第一個快照讀語句時建立的；
第二種啟動方式，一致性檢視是在執行 start transaction with consistent snapshot 時建立的。

還需要注意的是，在整個專欄裡面，我們的例子中如果沒有特別說明，都是預設 autocommit=1。

在這個例子中，事務 C 沒有顯式地使用 begin/commit，表示這個 update 語句本身就是一個事務，語句完成的時候會自動提交。事務 B 在更新了行之後查詢 ; 事務 A 在一個只讀事務中查詢，並且時間順序上是在事務 B 的查詢之後。

這時，如果我告訴你事務 B 查到的 k 的值是 3，而事務 A 查到的 k 的值是 1，你是不是感覺有點暈呢？

所以，今天這篇文章，我其實就是想和你說明白這個問題，希望藉由把這個疑惑解開的過程，能夠幫助你對 InnoDB 的事務和鎖有更進一步的理解。

在 MySQL 裡，有兩個“檢視”的概念：

• 一個是 view。它是一個用查詢語句定義的虛擬表，在呼叫的時候執行查詢語句並生成結果。建立檢視的語法是 create view … ，而它的查詢方法與表一樣。
• 另一個是 InnoDB 在實現 MVCC 時用到的一致性讀檢視，即 consistent read view，用於支援 RC（Read Committed，讀提交）和 RR（Repeatable Read，可重複讀）隔離級別的實現。

它沒有物理結構，作用是事務執行期間用來定義“我能看到什麼資料”。

在第 3 篇文章《事務隔離：為什麼你改了我還看不見？》中，我跟你解釋過一遍 MVCC 的實現邏輯。今天為了說明查詢和更新的區別，我換一個方式來說明，把 read view 拆開。你可以結合這兩篇文章的說明來更深一步地理解 MVCC。

“快照”在 MVCC 裡是怎麼工作的？

在可重複讀隔離級別下，事務在啟動的時候就“拍了個快照”。注意，這個快照是基於整庫的。

這時，你會說這看上去不太現實啊。如果一個庫有 100G，那麼我啟動一個事務，MySQL 就要複製 100G 的資料出來，這個過程得多慢啊。可是，我平時的事務執行起來很快啊。

實際上，我們並不需要複製出這 100G 的資料。我們先來看看這個快照是怎麼實現的。

InnoDB 裡面每個事務有一個唯一的事務 ID，叫作 transaction id。它是在事務開始的時候向 InnoDB 的事務系統申請的，是按申請順序嚴格遞增的。

而每行資料也都是有多個版本的。每次事務更新資料的時候，都會生成一個新的資料版本，並且把 transaction id 賦值給這個資料版本的事務 ID，記為 row trx_id。同時，舊的資料版本要保留，並且在新的資料版本中，能夠有資訊可以直接拿到它。

也就是說，資料表中的一行記錄，其實可能有多個版本 (row)，每個版本有自己的 row trx_id。

如圖 2 所示，就是一個記錄被多個事務連續更新後的狀態。

圖 2 行狀態變更圖

圖中虛線框裡是同一行資料的 4 個版本，當前最新版本是 V4，k 的值是 22，它是被 transaction id 為 25 的事務更新的，因此它的 row trx_id 也是 25。

你可能會問，前面的文章不是說，語句更新會生成 undo log（回滾日誌）嗎？那麼，undo log 在哪呢？

實際上，圖 2 中的三個虛線箭頭，就是 undo log；而 V1、V2、V3 並不是物理上真實存在的，而是每次需要的時候根據當前版本和 undo log 計算出來的。比如，需要 V2 的時候，就是透過 V4 依次執行 U3、U2 算出來。

明白了多版本和 row trx_id 的概念後，我們再來想一下，InnoDB 是怎麼定義那個“100G”的快照的。

按照可重複讀的定義，一個事務啟動的時候，能夠看到所有已經提交的事務結果。但是之後，這個事務執行期間，其他事務的更新對它不可見。

因此，一個事務只需要在啟動的時候宣告說，“以我啟動的時刻為準，如果一個資料版本是在我啟動之前生成的，就認；如果是我啟動以後才生成的，我就不認，我必須要找到它的上一個版本”。

當然，如果“上一個版本”也不可見，那就得繼續往前找。還有，如果是這個事務自己更新的資料，它自己還是要認的。

在實現上， InnoDB 為每個事務構造了一個陣列，用來儲存這個事務啟動瞬間，當前正在“活躍”的所有事務 ID。“活躍”指的就是，啟動了但還沒提交。

陣列裡面事務 ID 的最小值記為低水位，當前系統裡面已經建立過的事務 ID 的最大值加 1 記為高水位。

這個檢視陣列和高水位，就組成了當前事務的一致性檢視（read-view）。

而資料版本的可見性規則，就是基於資料的 row trx_id 和這個一致性檢視的對比結果得到的。

這個檢視陣列把所有的 row trx_id 分成了幾種不同的情況。

這樣，對於當前事務的啟動瞬間來說，一個資料版本的 row trx_id，有以下幾種可能：

1. 如果落在綠色部分，表示這個版本是已提交的事務或者是當前事務自己生成的，這個資料是可見的；
2. 如果落在紅色部分，表示這個版本是由將來啟動的事務生成的，是肯定不可見的；
3. 如果落在黃色部分，那就包括兩種情況
   a. 若 row trx_id 在陣列中，表示這個版本是由還沒提交的事務生成的，不可見；
   b. 若 row trx_id 不在陣列中，表示這個版本是已經提交了的事務生成的，可見。

比如，對於圖 2 中的資料來說，如果有一個事務，它的低水位是 18，那麼當它訪問這一行資料時，就會從 V4 透過 U3 計算出 V3，所以在它看來，這一行的值是 11。

你看，有了這個宣告後，系統裡面隨後發生的更新，是不是就跟這個事務看到的內容無關了呢？因為之後的更新，生成的版本一定屬於上面的 2 或者 3(a) 的情況，而對它來說，這些新的資料版本是不存在的，所以這個事務的快照，就是“靜態”的了。

所以你現在知道了，InnoDB 利用了“所有資料都有多個版本”的這個特性，實現了“秒級建立快照”的能力。

接下來，我們繼續看一下圖 1 中的三個事務，分析下事務 A 的語句返回的結果，為什麼是 k=1。

這裡，我們不妨做如下假設：

1. 事務 A 開始前，系統裡面只有一個活躍事務 ID 是 99；
2. 事務 A、B、C 的版本號分別是 100、101、102，且當前系統裡只有這四個事務；
3. 三個事務開始前，(1,1）這一行資料的 row trx_id 是 90。

這樣，事務 A 的檢視陣列就是 [99,100], 事務 B 的檢視陣列是 [99,100,101], 事務 C 的檢視陣列是 [99,100,101,102]。

為了簡化分析，我先把其他干擾語句去掉，只畫出跟事務 A 查詢邏輯有關的操作：

圖 4 事務 A 查詢資料邏輯圖

從圖中可以看到，第一個有效更新是事務 C，把資料從 (1,1) 改成了 (1,2)。這時候，這個資料的最新版本的 row trx_id 是 102，而 90 這個版本已經成為了歷史版本。

第二個有效更新是事務 B，把資料從 (1,2) 改成了 (1,3)。這時候，這個資料的最新版本（即 row trx_id）是 101，而 102 又成為了歷史版本。

你可能注意到了，在事務 A 查詢的時候，其實事務 B 還沒有提交，但是它生成的 (1,3) 這個版本已經變成當前版本了。但這個版本對事務 A 必須是不可見的，否則就變成髒讀了。

好，現在事務 A 要來讀資料了，它的檢視陣列是 [99,100]。當然了，讀資料都是從當前版本讀起的。所以，事務 A 查詢語句的讀資料流程是這樣的：

• 找到 (1,3) 的時候，判斷出 row trx_id=101，比高水位大，處於紅色區域，不可見；
• 接著，找到上一個歷史版本，一看 row trx_id=102，比高水位大，處於紅色區域，不可見；
• 再往前找，終於找到了（1,1)，它的 row trx_id=90，比低水位小，處於綠色區域，可見。

這樣執行下來，雖然期間這一行資料被修改過，但是事務 A 不論在什麼時候查詢，看到這行資料的結果都是一致的，所以我們稱之為一致性讀。

這個判斷規則是從程式碼邏輯直接轉譯過來的，但是正如你所見，用於人肉分析可見性很麻煩。

所以，我來給你翻譯一下。一個資料版本，對於一個事務檢視來說，除了自己的更新總是可見以外，有三種情況：

1. 版本未提交，不可見；
2. 版本已提交，但是是在檢視建立後提交的，不可見；
3. 版本已提交，而且是在檢視建立前提交的，可見。

現在，我們用這個規則來判斷圖 4 中的查詢結果，事務 A 的查詢語句的檢視陣列是在事務 A 啟動的時候生成的，這時候：

• (1,3) 還沒提交，屬於情況 1，不可見；
• (1,2) 雖然提交了，但是是在檢視陣列建立之後提交的，屬於情況 2，不可見；
• (1,1) 是在檢視陣列建立之前提交的，可見。

你看，去掉數字對比後，只用時間先後順序來判斷，分析起來是不是輕鬆多了。所以，後面我們就都用這個規則來分析。

更新邏輯

細心的同學可能有疑問了：事務 B 的 update 語句，如果按照一致性讀，好像結果不對哦？

你看圖 5 中，事務 B 的檢視陣列是先生成的，之後事務 C 才提交，不是應該看不見 (1,2) 嗎，怎麼能算出 (1,3) 來？

圖 5 事務 B 更新邏輯圖

是的，如果事務 B 在更新之前查詢一次資料，這個查詢返回的 k 的值確實是 1。

但是，當它要去更新資料的時候，就不能再在歷史版本上更新了，否則事務 C 的更新就丟失了。因此，事務 B 此時的 set k=k+1 是在（1,2）的基礎上進行的操作。

所以，這裡就用到了這樣一條規則：更新資料都是先讀後寫的，而這個讀，只能讀當前的值，稱為“當前讀”（current read）。

因此，在更新的時候，當前讀拿到的資料是 (1,2)，更新後生成了新版本的資料 (1,3)，這個新版本的 row trx_id 是 101。

所以，在執行事務 B 查詢語句的時候，一看自己的版本號是 101，最新資料的版本號也是 101，是自己的更新，可以直接使用，所以查詢得到的 k 的值是 3。

這裡我們提到了一個概念，叫作當前讀。其實，除了 update 語句外，select 語句如果加鎖，也是當前讀。

所以，如果把事務 A 的查詢語句 select * from t where id=1 修改一下，加上 lock in share mode 或 for update，也都可以讀到版本號是 101 的資料，返回的 k 的值是 3。下面這兩個 select 語句，就是分別加了讀鎖（S 鎖，共享鎖）和寫鎖（X 鎖，排他鎖）。

mysql> select k from t where id=1 lock in share mode;
mysql> select k from t where id=1 for update;

再往前一步，假設事務 C 不是馬上提交的，而是變成了下面的事務 C’，會怎麼樣呢？

務 C’的不同是，更新後並沒有馬上提交，在它提交前，事務 B 的更新語句先發起了。前面說過了，雖然事務 C’還沒提交，但是 (1,2) 這個版本也已經生成了，並且是當前的最新版本。那麼，事務 B 的更新語句會怎麼處理呢？

這時候，我們在上一篇文章中提到的“兩階段鎖協議”就要上場了。事務 C’沒提交，也就是說 (1,2) 這個版本上的寫鎖還沒釋放。而事務 B 是當前讀，必須要讀最新版本，而且必須加鎖，因此就被鎖住了，必須等到事務 C’釋放這個鎖，才能繼續它的當前讀。

圖 7 事務 B 更新邏輯圖（配合事務 C'）

到這裡，我們把一致性讀、當前讀和行鎖就串起來了。

現在，我們再回到文章開頭的問題：事務的可重複讀的能力是怎麼實現的？

可重複讀的核心就是一致性讀（consistent read）；而事務更新資料的時候，只能用當前讀。如果當前的記錄的行鎖被其他事務佔用的話，就需要進入鎖等待。

而讀提交的邏輯和可重複讀的邏輯類似，它們最主要的區別是：

• 在可重複讀隔離級別下，只需要在事務開始的時候建立一致性檢視，之後事務裡的其他查詢都共用這個一致性檢視；
• 在讀提交隔離級別下，每一個語句執行前都會重新算出一個新的檢視。

那麼，我們再看一下，在讀提交隔離級別下，事務 A 和事務 B 的查詢語句查到的 k，分別應該是多少呢？

這裡需要說明一下，“start transaction with consistent snapshot; ”的意思是從這個語句開始，建立一個持續整個事務的一致性快照。所以，在讀提交隔離級別下，這個用法就沒意義了，等效於普通的 start transaction。

下面是讀提交時的狀態圖，可以看到這兩個查詢語句的建立檢視陣列的時機發生了變化，就是圖中的 read view 框。（注意：這裡，我們用的還是事務 C 的邏輯直接提交，而不是事務 C’）

圖 8 讀提交隔離級別下的事務狀態圖

這時，事務 A 的查詢語句的檢視陣列是在執行這個語句的時候建立的，時序上 (1,2)、(1,3) 的生成時間都在建立這個檢視陣列的時刻之前。但是，在這個時刻：

• (1,3) 還沒提交，屬於情況 1，不可見；
• (1,2) 提交了，屬於情況 3，可見。

所以，這時候事務 A 查詢語句返回的是 k=2。

顯然地，事務 B 查詢結果 k=3。

小結

InnoDB 的行資料有多個版本，每個資料版本有自己的 row trx_id，每個事務或者語句有自己的一致性檢視。普通查詢語句是一致性讀，一致性讀會根據 row trx_id 和一致性檢視確定資料版本的可見性。

• 對於可重複讀，查詢只承認在事務啟動前就已經提交完成的資料；
• 對於讀提交，查詢只承認在語句啟動前就已經提交完成的資料；

而當前讀，總是讀取已經提交完成的最新版本。

你也可以想一下，為什麼表結構不支援“可重複讀”？這是因為表結構沒有對應的行資料，也沒有 row trx_id，因此只能遵循當前讀的邏輯。

當然，MySQL 8.0 已經可以把表結構放在 InnoDB 字典裡了，也許以後會支援表結構的可重複讀。

又到思考題時間了。我用下面的表結構和初始化語句作為試驗環境，事務隔離級別是可重複讀。現在，我要把所有“欄位 c 和 id 值相等的行”的 c 值清零，但是卻發現了一個“詭異”的、改不掉的情況。請你構造出這種情況，並說明其原理。

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, c) values(1,1),(2,2),(3,3),(4,4);

這樣，session A 看到的就是我截圖的效果了。

其實，還有另外一種場景，同學們在留言區都還沒有提到。

這個操作序列跑出來，session A 看的內容也是能夠復現我截圖的效果的。這個 session B’啟動的事務比 A 要早，其實是上期我們描述事務版本的可見性規則時留的彩蛋，因為規則裡還有一個“活躍事務的判斷”，我是準備留到這裡再補充的。