雲栖号資訊：【 點選檢視更多行業資訊】
在這裡您可以找到不同行業的第一手的上雲資訊，還在等什麼，快來！
當我們在使用關系型資料庫時，主鍵（Primary Key）是無法避開的概念，主鍵的作用就是充當記錄的辨別符，我們能夠通過辨別符在一張表中定位到唯一的記錄，作者在 為什麼總是需要無意義的 ID 曾經介紹過為什麼不應該使用有意義的字段來充當唯一辨別符，感興趣的讀者可以了解一下。
在關系型資料庫中，我們會選擇記錄中多個字段的最小子集作為該記錄在表中的唯一辨別符1，根據關系型資料庫對主鍵的定義，我們既可以選擇單個列作為主鍵，也可以選擇多個列作為主鍵，但是主鍵在整個記錄中必須存在并且唯一。最常見的方式當然是使用 MySQL 預設的自增 ID 作為主鍵，雖然使用其他政策設定的主鍵也是合法的，但是不是通用的以及推薦的做法。
圖 1 - MySQL 的主鍵
MySQL 中預設的 AUTO_INCREMENT 屬性在多數情況下可以保證主鍵的連續性，我們通過 show create table 指令可以在表的定義中能夠看到 AUTO_INCREMENT 屬性的目前值，當我們向目前表中插入資料時，它會使用該屬性的值作為插入記錄的主鍵，而每次擷取該值也都會将它加一。
在很多開發者的認知中，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。
如果讓作者實作 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。
當 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 是用來做什麼的？
【雲栖号線上課堂】每天都有産品技術專家分享！
課程位址：
https://yqh.aliyun.com/live立即加入社群，與專家面對面，及時了解課程最新動态！
【雲栖号線上課堂 社群】
https://c.tb.cn/F3.Z8gvnK
原文釋出時間：2020-04-21
本文作者：面向信仰程式設計
本文來自：“
掘金”，了解相關資訊可以關注“掘金”