當(dāng)我們在使用關(guān)系型數(shù)據(jù)庫時，主鍵（Primary Key）是無法避開的概念，主鍵的作用就是充當(dāng)記錄的標(biāo)識符，我們能夠通過標(biāo)識符在一張表中定位到唯一的記錄，作者在 為什么總是需要無意義的 ID 曾經(jīng)介紹過為什么不應(yīng)該使用有意義的字段來充當(dāng)唯一標(biāo)識符，感興趣的讀者可以了解一下。

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

圖 1 - MySQL 的主鍵

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

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

在很多開發(fā)者的認(rèn)知中，MySQL 的主鍵都應(yīng)該是單調(diào)遞增的，但是在我們與 MySQL 打交道的過程中會遇到兩個問題，首先是記錄的主鍵并不連續(xù)，其次是可能會創(chuàng)建多個主鍵相同的記錄，我們將從以下的兩個角度回答 MySQL 不單調(diào)和不連續(xù)的原因：

• 較早版本的 MySQL 將 AUTO_INCREMENT 存儲在內(nèi)存中，實例重啟后會根據(jù)表中的數(shù)據(jù)重新設(shè)置該值；
• 獲取 AUTO_INCREMENT 時不會使用事務(wù)鎖，并發(fā)的插入事務(wù)可能出現(xiàn)部分字段沖突導(dǎo)致插入失??；

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

刪除記錄

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

圖 2 - AUTO_INCREMENT 的使用

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

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

如果讓作者實現(xiàn) AUTO_INCREMENT，在最開始也會使用這種方法。不過這種實現(xiàn)雖然非常簡單，但是如果使用者不嚴(yán)格遵循關(guān)系型數(shù)據(jù)庫的設(shè)計規(guī)范，就會出現(xiàn)如下所示的數(shù)據(jù)不一致的問題：

圖 3 - 5.7 版本之前的 AUTO_INCMRENT

因為重啟了 MySQL 的實例，所以內(nèi)存中的 AUTO_INCREMENT 計數(shù)器會被重置成表中的最大值，當(dāng)我們再向表中插入新的 trades 記錄時會重新使用 10 作為主鍵，主鍵也就不是單調(diào)的了。在新的 trades 記錄插入之后，executions 表中的記錄就錯誤的引用了新的 trades，這其實是一個比較嚴(yán)重的錯誤。

然而這也不完全是 MySQL 的問題，如果我們嚴(yán)格遵循關(guān)系型數(shù)據(jù)庫的設(shè)計規(guī)范，使用外鍵處理不同表之間的聯(lián)系，就可以避免上述問題，因為當(dāng)前 trades 記錄仍然有外部的引用，所以外鍵會禁止 trades 記錄的刪除，不過多數(shù)公司內(nèi)部的 DBA 都不推薦或者禁止使用外鍵，所以確實存在出現(xiàn)這種問題的可能。

然而在 MySQL 8.0 中，AUTO_INCREMENT 計數(shù)器的初始化行為發(fā)生了改變，每次計數(shù)器的變化都會寫入到系統(tǒng)的重做日志（Redo log）并在每個檢查點存儲在引擎私有的系統(tǒng)表中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.

當(dāng) MySQL 服務(wù)被重啟或者處于崩潰恢復(fù)時，它可以從持久化的檢查點和重做日志中恢復(fù)出最新的 AUTO_INCREMENT 計數(shù)器，避免出現(xiàn)不單調(diào)的主鍵也解決了這里提到的問題。

并發(fā)事務(wù)

為了提高事務(wù)的吞吐量，MySQL 可以處理并發(fā)執(zhí)行的多個事務(wù)，但是如果并發(fā)執(zhí)行多個插入新記錄的 SQL 語句，可能會導(dǎo)致主鍵的不連續(xù)。如下圖所示，事務(wù) 1 向數(shù)據(jù)庫中插入 id = 10 的記錄，事務(wù) 2 向數(shù)據(jù)庫中插入 id = 11 和 id = 12 的兩條記錄：

圖 4 - 并發(fā)事務(wù)的執(zhí)行

不過如果在最后事務(wù) 1 由于插入的記錄發(fā)生了唯一鍵沖突導(dǎo)致了回滾，而事務(wù) 2 沒有發(fā)生錯誤而正常提交，在這時我們會發(fā)現(xiàn)當(dāng)前表中的主鍵出現(xiàn)了不連續(xù)的現(xiàn)象，后續(xù)新插入的數(shù)據(jù)也不再會使用 10 作為記錄的主鍵。

圖 5 - 不連續(xù)的主鍵

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

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

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

這三種模式都不能解決 MySQL 自增主鍵不連續(xù)的問題，想要解決這個問題的終極方案是串行執(zhí)行所有包含插入操作的事務(wù)，也就是使用數(shù)據(jù)庫的最高隔離級別 —— 可串行化（Serialiable）。當(dāng)然直接修改數(shù)據(jù)庫的隔離級別相對來說有些簡單粗暴，基于 MySQL 或者其他存儲系統(tǒng)實現(xiàn)完全串行的插入也可以保證主鍵在插入時的連續(xù)，但是仍然不能避免刪除數(shù)據(jù)導(dǎo)致的不連續(xù)。

總結(jié)

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

MySQL 中不連續(xù)的主鍵又是一個工程設(shè)計向性能低頭的例子，犧牲主鍵的連續(xù)性來支持?jǐn)?shù)據(jù)的并發(fā)插入，最終提高了 MySQL 服務(wù)的吞吐量，作者在幾年前剛剛使用 MySQL 時就遇到過這個問題，但是當(dāng)時并沒有深究背后的原因，今天重新理解該問題背后的設(shè)計決策也是個非常有趣的過程。我們在這里簡單總結(jié)一下本文的內(nèi)容，重新回到今天的問題 — 為什么 MySQL 的自增主鍵不單調(diào)也不連續(xù)：

• MySQL 5.7 版本之前在內(nèi)存中存儲 AUTO_INCREMENT 計數(shù)器，實例重啟后會根據(jù)表中的數(shù)據(jù)重新設(shè)置，在刪除記錄后重啟就可能出現(xiàn)重復(fù)的主鍵，該問題在 8.0 版本使用重做日志解決，保證了主鍵的單調(diào)性；
• MySQL 插入數(shù)據(jù)獲取 AUTO_INCREMENT 時不會使用事務(wù)鎖，而是會使用互斥鎖，并發(fā)的插入事務(wù)可能出現(xiàn)部分字段沖突導(dǎo)致插入失敗，想要保證主鍵的連續(xù)需要串行地執(zhí)行插入語句；

到最后，我們還是來看一些比較開放的相關(guān)問題，有興趣的讀者可以仔細(xì)思考一下下面的問題：

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