當(dāng)我們?cè)谑褂藐P(guān)系型數(shù)據(jù)庫(kù)時(shí)，主鍵(Primary Key)是無(wú)法避開的概念，主鍵的作用就是充當(dāng)記錄的標(biāo)識(shí)符，我們能夠通過(guò)標(biāo)識(shí)符在一張表中定位到唯一的記錄。

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

圖 1 - MySQL 的主鍵

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

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 打交道的過(guò)程中會(huì)遇到兩個(gè)問(wèn)題，首先是記錄的主鍵并不連續(xù)，其次是可能會(huì)創(chuàng)建多個(gè)主鍵相同的記錄，我們將從以下的兩個(gè)角度回答 MySQL 不單調(diào)和不連續(xù)的原因：

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

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

刪除記錄

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

圖 2 - AUTO_INCREMENT 的使用

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

SELECT MAX(ai_col) FROM table_name FOR UPDATE; 

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

圖 3 - 5.7 版本之前的 AUTO_INCMRENT

因?yàn)橹貑⒘?MySQL 的實(shí)例，所以內(nèi)存中的 AUTO_INCREMENT 計(jì)數(shù)器會(huì)被重置成表中的最大值，當(dāng)我們?cè)傧虮碇胁迦胄碌?trades 記錄時(shí)會(huì)重新使用 10 作為主鍵，主鍵也就不是單調(diào)的了。在新的 trades 記錄插入之后，executions 表中的記錄就錯(cuò)誤的引用了新的 trades，這其實(shí)是一個(gè)比較嚴(yán)重的錯(cuò)誤。

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

然而在 MySQL 8.0 中，AUTO_INCREMENT 計(jì)數(shù)器的初始化行為發(fā)生了改變，每次計(jì)數(shù)器的變化都會(huì)寫入到系統(tǒng)的重做日志(Redo log)并在每個(gè)檢查點(diǎn)存儲(chǔ)在引擎私有的系統(tǒng)表中[^3]。

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

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

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

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

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

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

這個(gè)現(xiàn)象背后的原因也很簡(jiǎn)單，雖然在獲取 AUTO_INCREMENT 時(shí)會(huì)加鎖，但是該鎖是語(yǔ)句鎖，它的目的是保證 AUTO_INCREMENT 的獲取不會(huì)導(dǎo)致線程競(jìng)爭(zhēng)，而不是保證 MySQL 中主鍵的連續(xù)[^4]。

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

(1) 傳統(tǒng)模式 innodb_autoinc_lock_mode = 0;

在包含 AUTO_INCREMENT 屬性的表中插入數(shù)據(jù)時(shí)，所有的 INSERT 語(yǔ)句都會(huì)獲取表級(jí)別的 AUTO_INCREMENT 鎖，該鎖會(huì)在當(dāng)前語(yǔ)句執(zhí)行后釋放;

(2) 連續(xù)模式 innodb_autoinc_lock_mode = 1;

• INSERT ... SELECT、REPLACE ... SELECT 以及 LOAD DATA 等批量的插入操作需要獲取表級(jí)別的 AUTO_INCREMENT 鎖，該鎖會(huì)在當(dāng)前語(yǔ)句執(zhí)行后釋放;
• 簡(jiǎn)單的插入語(yǔ)句(預(yù)先知道插入多少條記錄的語(yǔ)句)只需要獲取獲取 AUTO_INCREMENT 計(jì)數(shù)器的互斥鎖并在獲取主鍵后直接釋放，不需要等待當(dāng)前語(yǔ)句執(zhí)行完成;

(3) 交叉模式 innodb_autoinc_lock_mode = 2;

所有的插入語(yǔ)句都不需要獲取表級(jí)別的 AUTO_INCREMENT 鎖，但是當(dāng)多個(gè)語(yǔ)句插入的數(shù)據(jù)行數(shù)不確定時(shí)，可能存在分配相同主鍵的風(fēng)險(xiǎn);

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

總結(jié)

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

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

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

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

• MyISAM 和其他的存儲(chǔ)引擎如何存儲(chǔ) AUTO_INCREMENT 計(jì)數(shù)器?
• MySQL 中的 auto_increment_increment 和 auto_increment_offset 是用來(lái)做什么的?