MySQL 自增主鍵

以下僅考慮 InnoDB 存儲引擎。

自增主鍵有兩個性質需要考慮：

• 單調性

  每次插入一條資料，其 ID 都是比上一條插入的資料的 ID 大，就算上一條資料被删除。

• 連續性

  插入成功時，其資料的 ID 和前一次插入成功時資料的 ID 相鄰。

自增主鍵的單調性

為何會有單調性的問題？

這主要跟自增主鍵最大值的擷取方式，以及存放位置有關系。

如果最大值是通過計算擷取的，并且在某些情況下需要重新擷取時，會因為最新的資料被删除而減小。

自增主鍵最大值怎麼取的？存放到哪裡？

MySQL 5.7 及之前的版本，自增主鍵最大值會在啟動（重新開機）後從資料庫中取出放到記憶體：

SELECT MAX(ai_col) FROM table_name FOR UPDATE; 
           

這樣擷取是通過計算的，并且由于存放在記憶體而容易丢失。

如果删除最新一條資料（假設 ID 為 10），因故障或者其他必要重新開機後再插入一條資料時會使用之前的 ID （即 ID 為 10）。

問題在于如果有其他表依賴了該 ID，則其他表的資料關聯到的資料就符合要求了。除非設定了外鍵。

比如我要向最大一個 ID 的賬号充了 100 萬。但是在充值之前，該賬号被删除，然後伺服器故障重新開機，重新開機後有人新注冊了一個賬号。結果我的 100 萬充到了他的新賬号上。注冊新賬号的人以為是新手福利，笑嘻嘻。

如何解決單調性的問題？

從 MySQL 8.0 開始，自增主鍵最大值會在每次修改後寫入到 redo log，并且在每個檢查點寫入引擎私有的系統表。

• 如果是正常重新開機，則讀取系統表裡的值。
• 如果是故障重新開機，則先讀取系統表裡的值放到記憶體。接着掃描 redo log 裡存儲的值。如果掃描到的值大于記憶體的值，則将該值覆寫到記憶體。

但由于資料庫可能在 redo log 刷入磁盤前就故障了，是以可能會用到之前申請的 ID。

注：如果 redo log 都沒刷入，就更不用說将資料插入資料表了。

InnoDB AUTO_INCREMENT Counter Initialization

https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization

自增主鍵插入時的連續性

這裡不考慮由于删除導緻的連續性問題

為何會有連續性問題？

這主要是跟插入事務復原有關系。

對于兩個插入事務，事務 A 先執行插入語句，之後事務 B 執行插入語句。在這之後，事務 A 復原，導緻 A 執行插入語句時占用的 ID 被抛棄。

之是以事務 A 沒送出的情況下，事務 B 就能執行插入語句，跟 InnoDB 的自增長鎖（AUTO-INC Locking）相關。該鎖是一種特殊的表鎖（table-level lock），但會在插入語句執行後立即釋放，不會等到事務結束。

如何解決連續性問題？

使用最高隔離級别 SERIALIZABLE （串行）。

由于性能上的考慮，通常不這樣做。

多事務批量插入的連續性

事務 A 和事務 B 都在執行 不确定數量 的批量插入（INSERT ... SELECT）：

• 保證事務 A 的資料的 ID 連續： innodb_autoinc_lock_mode = 0 （AUTO-INC Locking）

  必須等待語句執行結束才釋放鎖。

• 保證事務 A 的資料的 ID 連續： innodb_autoinc_lock_mode = 1 （AUTO-INC Locking）

  和上面的差別在于，當執行 确定數量 的批量插入時，使用輕量級互斥量（mutex）而不是特殊表鎖（AUTO-INC Locking），進而提前向記憶體的計數器申請相應數量的 ID。之後立即釋放，不用等語句執行結束。

  會因為復原而使得全局 ID 不連續。

• 不保證事務 A 的資料的 ID 連續： innodb_autoinc_lock_mode = 2 （mutex）

三種插入定義：

• 簡單插入

  能夠提前知道插入的行數

• 批量插入

  不能提前知道插入的行數

• 混合插入

  批量插入中的一部分的 ID 是指定的（非 0 且非 NULL），另一部分未指定，使用資料庫生成的自增 ID。

其他

如果主動指定 ID 為 0 或者 NULL 插入，則會使用資料庫生成的自增 ID。

參考文檔

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

https://database.51cto.com/art/202004/614923.htm

《MySQL技術内幕——InnoDB存儲引擎》 第 6 章：鎖

本文采用知識共享署名 2.5 中國大陸許可協定進行許可。歡迎轉載，演繹或用于商業目的，但是必須保留本文的署名 schaepher（包含連結）。如您有任何疑問或者授權方面的協商，請給我留言。