為什麼 MySQL 的自增主鍵不單調也不連續

推薦閱讀：

面試BAT，MySQL是攔路虎？這份數據庫文檔讓你吊打BAT面試官！

為什麼這麼設計（Why’s THE Design）是一系列關於計算機領域中程序設計決策的文章，我們在這個系列的每一篇文章中都會提出一個具體的問題並從不同的角度討論這種設計的優缺點、對具體實現造成的影響。如果你有想要了解的問題，可以在文章下面留言。

當我們在使用關係型數據庫時，主鍵（Primary Key）是無法避開的概念，主鍵的作用就是充當記錄的標識符，我們能夠通過標識符在一張表中定位到唯一的記錄，作者在 為什麼總是需要無意義的 ID 曾經介紹過為什麼不應該使用有意義的字段來充當唯一標識符，感興趣的讀者可以瞭解一下。

在關係型數據庫中，我們會選擇記錄中多個字段的最小子集作為該記錄在表中的唯一標識符1，根據關係型數據庫對主鍵的定義，我們既可以選擇單個列作為主鍵，也可以選擇多個列作為主鍵，但是主鍵在整個記錄中必須存在並且唯一。最常見的方式當然是使用 MySQL 默認的自增 ID 作為主鍵，雖然使用其他策略設置的主鍵也是合法的，但是不是通用的以及推薦的做法。

圖 1 - MySQL 的主鍵

MySQL 中默認的 AUTO_INCREMENT 屬性在多數情況下可以保證主鍵的連續性，我們通過 show create table 命令可以在表的定義中能夠看到 AUTO_INCREMENT 屬性的當前值，當我們向當前表中插入數據時，它會使用該屬性的值作為插入記錄的主鍵，而每次獲取該值也都會將它加一。

<code>CREATE TABLE `trades` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  ...
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=17130 DEFAULT CHARSET=utf8mb4
/<code>

在很多開發者的認知中，MySQL 的主鍵都應該是單調遞增的，但是在我們與 MySQL 打交道的過程中會遇到兩個問題，首先是記錄的主鍵並不連續，其次是可能會創建多個主鍵相同的記錄，我們將從以下的兩個角度回答 MySQL 不單調和不連續的原因：

• 較早版本的 MySQL 將 AUTO_INCREMENT 存儲在內存中，實例重啟後會根據表中的數據重新設置該值；
• 獲取 AUTO_INCREMENT 時不會使用事務鎖，併發的插入事務可能出現部分字段衝突導致插入失敗；

需要注意的是，我們在這篇文章中討論的是 MySQL 中最常見的 InnoDB 存儲引擎，MyISAM 等其他引擎提供的 AUTO_INCREMENT 實現原理不在本文的討論範圍中。

刪除記錄

AUTO_INCREMENT 屬性雖然在 MySQL 中十分常見，但是在較早的 MySQL 版本中，它的實現還比較簡陋，InnoDB 引擎會在內存中存儲一個整數表示下一個被分配到的 ID，當客戶端向表中插入數據時會獲取 AUTO_INCREMENT 值並將其加一。

圖 2 - AUTO_INCREMENT 的使用

因為該值存儲在內存中，所以在每次 MySQL 實例重新啟動後，當客戶端第一次向 table_name 表中插入記錄時，MySQL 會使用如下所示的 SQL 語句查找當前表中 id 的最大值，將其加一後作為待插入記錄的主鍵，並作為當前表中 AUTO_INCREMENT 計數器的初始值2。

<code>SELECT MAX(ai_col) FROM table_name FOR UPDATE;
/<code>

如果讓作者實現 AUTO_INCREMENT，在最開始也會使用這種方法。不過這種實現雖然非常簡單，但是如果使用者不嚴格遵循關係型數據庫的設計規範，就會出現如下所示的數據不一致的問題：

圖 3 - 5.7 版本之前的 AUTO_INCMRENT

因為重啟了 MySQL 的實例，所以內存中的 AUTO_INCREMENT 計數器會被重置成表中的最大值，當我們再向表中插入新的 trades 記錄時會重新使用 10 作為主鍵，主鍵也就不是單調的了。在新的 trades 記錄插入之後，executions 表中的記錄就錯誤的引用了新的 trades，這其實是一個比較嚴重的錯誤。

然而這也不完全是 MySQL 的問題，如果我們嚴格遵循關係型數據庫的設計規範，使用外鍵處理不同表之間的聯繫，就可以避免上述問題，因為當前 trades 記錄仍然有外部的引用，所以外鍵會禁止 trades 記錄的刪除，不過多數公司內部的 DBA 都不推薦或者禁止使用外鍵，所以確實存在出現這種問題的可能。

然而在 MySQL 8.0 中，AUTO_INCREMENT 計數器的初始化行為發生了改變，每次計數器的變化都會寫入到系統的重做日誌（Redo log）並在每個檢查點存儲在引擎私有的系統表中3。

In MySQL 8.0, this behavior is changed. The current maximum auto-increment counter value is written to the redo log each time it changes and is saved to an engine-private system table on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts.

當 MySQL 服務被重啟或者處於崩潰恢復時，它可以從持久化的檢查點和重做日誌中恢復出最新的 AUTO_INCREMENT 計數器，避免出現不單調的主鍵也解決了這裡提到的問題。

併發事務

為了提高事務的吞吐量，MySQL 可以處理併發執行的多個事務，但是如果併發執行多個插入新記錄的 SQL 語句，可能會導致主鍵的不連續。如下圖所示，事務 1 向數據庫中插入 id = 10 的記錄，事務 2 向數據庫中插入 id = 11 和 id = 12 的兩條記錄：

圖 4 - 併發事務的執行

不過如果在最後事務 1 由於插入的記錄發生了唯一鍵衝突導致了回滾，而事務 2 沒有發生錯誤而正常提交，在這時我們會發現當前表中的主鍵出現了不連續的現象，後續新插入的數據也不再會使用 10 作為記錄的主鍵。

圖 5 - 不連續的主鍵

這個現象背後的原因也很簡單，雖然在獲取 AUTO_INCREMENT 時會加鎖，但是該鎖是語句鎖，它的目的是保證 AUTO_INCREMENT 的獲取不會導致線程競爭，而不是保證 MySQL 中主鍵的連續4。

上述行為是由 InnoDB 存儲引擎提供的 innodb_autoinc_lock_mode 配置控制的，該配置決定了獲取 AUTO_INCREMENT 計時器時需要先得到的鎖，該配置存在三種不同的模式，分別是傳統模式（Traditional）、連續模式（Consecutive）和交叉模式（Interleaved）5，其中 MySQL 使用連續模式作為默認的鎖模式：

• 傳統模式 innodb_autoinc_lock_mode = 0；在包含 AUTO_INCREMENT 屬性的表中插入數據時，所有的 INSERT 語句都會獲取表級別的 AUTO_INCREMENT 鎖，該鎖會在當前語句執行後釋放；
• 連續模式 innodb_autoinc_lock_mode = 1；INSERT ... SELECT、REPLACE ... SELECT 以及 LOAD DATA 等批量的插入操作需要獲取表級別的 AUTO_INCREMENT 鎖，該鎖會在當前語句執行後釋放；簡單的插入語句（預先知道插入多少條記錄的語句）只需要獲取獲取 AUTO_INCREMENT 計數器的互斥鎖並在獲取主鍵後直接釋放，不需要等待當前語句執行完成；
• 交叉模式 innodb_autoinc_lock_mode = 2；所有的插入語句都不需要獲取表級別的 AUTO_INCREMENT 鎖，但是當多個語句插入的數據行數不確定時，可能存在分配相同主鍵的風險；

這三種模式都不能解決 MySQL 自增主鍵不連續的問題，想要解決這個問題的終極方案是串行執行所有包含插入操作的事務，也就是使用數據庫的最高隔離級別 —— 可串行化（Serialiable）。當然直接修改數據庫的隔離級別相對來說有些簡單粗暴，基於 MySQL 或者其他存儲系統實現完全串行的插入也可以保證主鍵

在插入時的連續，但是仍然不能避免刪除數據導致的不連續。

總結

早期 MySQL 的主鍵既不是單調的，也不是連續的，這些都是在當時工程上做出的一些選擇，如果嚴格地按照關係型數據庫的設計規範，MySQL 最初的設計造成問題的概率也比較低，只有當被刪除的主鍵被外部系統引用時才會影響數據的一致性，但是今天使用方式的不同卻增加出錯的可能性，而 MySQL 也在 8.0 中持久化了 AUTO_INCREMENT 以避免該問題的出現。

MySQL 中不連續的主鍵又是一個工程設計向性能低頭的例子，犧牲主鍵的連續性來支持數據的併發插入，最終提高了 MySQL 服務的吞吐量，作者在幾年前剛剛使用 MySQL 時就遇到過這個問題，但是當時並沒有深究背後的原因，今天重新理解該問題背後的設計決策也是個非常有趣的過程。我們在這裡簡單總結一下本文的內容，重新回到今天的問題 — 為什麼 MySQL 的自增主鍵不單調也不連續：

• MySQL 5.7 版本之前在內存中存儲 AUTO_INCREMENT 計數器，實例重啟後會根據表中的數據重新設置，在刪除記錄後重啟就可能出現重複的主鍵，該問題在 8.0 版本使用重做日誌解決，保證了主鍵的單調性；
• MySQL 插入數據獲取 AUTO_INCREMENT 時不會使用事務鎖，而是會使用互斥鎖，併發的插入事務可能出現部分字段衝突導致插入失敗，想要保證主鍵的連續需要串行地執行插入語句；

到最後，我們還是來看一些比較開放的相關問題，有興趣的讀者可以仔細思考一下下面的問題：

• MyISAM 和其他的存儲引擎如何存儲 AUTO_INCREMENT 計數器？
• MySQL 中的 auto_increment_increment 和 auto_increment_offset 是用來做什麼的？