MySQL 的自增主鍵一定是連續的嗎？

作者：小牛肉 2022-12-06 09:00:11

數據庫 MySQL

InnoDB 引擎的自增值，其實是保存在了內存里，并沒有持久化。第一次打開表的時候，都會去找自增值的最大值 max(id)，然后將 max(id)+1 作為這個表當前的自增值。

眾所周知，自增主鍵可以讓聚集索引盡量地保持遞增順序插入，避免了隨機查詢，從而提高了查詢效率

但實際上，MySQL 的自增主鍵并不能保證一定是連續遞增的。

下面舉個例子來看下，如下所示創建一張表：

自增值保存在哪里？

使用 insert into test_pk values(null, 1, 1)? 插入一行數據，再執行 show create table 命令來看一下表的結構定義：

上述表的結構定義存放在后綴名為 .frm? 的本地文件中，在 MySQL 安裝目錄下的 data 文件夾下可以找到這個 .frm 文件：

從上述表結構可以看到，表定義里面出現了一個 AUTO_INCREMENT=2，表示下一次插入數據時，如果需要自動生成自增值，會生成 id = 2。

但需要注意的是，自增值并不會保存在這個表結構也就是 .frm 文件中，不同的引擎對于自增值的保存策略不同：

1）MyISAM 引擎的自增值保存在數據文件中

2）InnoDB 引擎的自增值，其實是保存在了內存里，并沒有持久化。第一次打開表的時候，都會去找自增值的最大值 max(id)?，然后將 max(id)+1 作為這個表當前的自增值。

舉個例子：我們現在表里當前數據行里最大的 id 是 1，AUTO_INCREMENT=2，對吧。這時候，我們刪除 id=1 的行，AUTO_INCREMENT 還是 2。

但如果馬上重啟 MySQL 實例，重啟后這個表的 AUTO_INCREMENT 就會變成 1。?也就是說，MySQL 重啟可能會修改一個表的 AUTO_INCREMENT 的值。

以上，是在我本地 MySQL 5.x 版本的實驗，實際上，到了 MySQL 8.0 版本后，自增值的變更記錄被放在了 redo log 中，提供了自增值持久化的能力，也就是實現了“如果發生重啟，表的自增值可以根據 redo log 恢復為 MySQL 重啟前的值”

也就是說對于上面這個例子來說，重啟實例后這個表的 AUTO_INCREMENT 仍然是 2。

理解了 MySQL 自增值到底保存在哪里以后，我們再來看看自增值的修改機制，并以此引出第一種自增值不連續的場景。

自增值不連續場景 1

在 MySQL 里面，如果字段 id 被定義為 AUTO_INCREMENT，在插入一行數據的時候，自增值的行為如下：

如果插入數據時 id 字段指定為 0、null 或未指定值，那么就把這個表當前的 AUTO_INCREMENT 值填到自增字段；
如果插入數據時 id 字段指定了具體的值，就直接使用語句里指定的值。

根據要插入的值和當前自增值的大小關系，自增值的變更結果也會有所不同。假設某次要插入的值是 insert_num?，當前的自增值是 autoIncrement_num：

如果 insert_num < autoIncrement_num，那么這個表的自增值不變

如果 insert_num >= autoIncrement_num，就需要把當前自增值修改為新的自增值

也就是說，如果插入的 id 是 100，當前的自增值是 90，insert_num >= autoIncrement_num，那么自增值就會被修改為新的自增值即 101

一定是這樣嗎？

非也~

了解過分布式 id 的小伙伴一定知道，為了避免兩個庫生成的主鍵發生沖突，我們可以讓一個庫的自增 id 都是奇數，另一個庫的自增 id 都是偶數

這個奇數偶數其實是通過 auto_increment_offset? 和 auto_increment_increment 這兩個參數來決定的，這倆分別用來表示自增的初始值和步長，默認值都是 1。

所以，上面的例子中生成新的自增值的步驟實際是這樣的：從 auto_increment_offset? 開始，以 auto_increment_increment 為步長，持續疊加，直到找到第一個大于 100 的值，作為新的自增值。

所以，這種情況下，自增值可能會是 102，103 等等之類的，就會導致不連續的主鍵 id。

更遺憾的是，即使在自增初始值和步長這兩個參數都設置為 1 的時候，自增主鍵 id 也不一定能保證主鍵是連續的

自增值不連續場景 2

舉個例子，我們現在往表里插入一條 (null,1,1) 的記錄，生成的主鍵是 1，AUTO_INCREMENT= 2，對吧

這時我再執行一條插入 (null,1,1)? 的命令，很顯然會報錯 Duplicate entry?，因為我們設置了一個唯一索引字段 a：

但是，你會驚奇的發現，雖然插入失敗了，但自增值仍然從 2 增加到了 3！

這是為啥？

我們來分析下這個 insert 語句的執行流程：

執行器調用 InnoDB 引擎接口準備插入一行記錄 (null,1,1);
InnoDB 發現用戶沒有指定自增 id 的值，則獲取表test_pk 當前的自增值 2；
將傳入的記錄改成 (2,1,1);
將表的自增值改成 3；
繼續執行插入數據操作，由于已經存在 a=1 的記錄，所以報 Duplicate key error，語句返回

可以看到，自增值修改的這個操作，是在真正執行插入數據的操作之前。

這個語句真正執行的時候，因為碰到唯一鍵 a 沖突，所以 id = 2 這一行并沒有插入成功，但也沒有將自增值再改回去。所以，在這之后，再插入新的數據行時，拿到的自增 id 就是 3。也就是說，出現了自增主鍵不連續的情況。

至此，我們已經羅列了兩種自增主鍵不連續的情況：

自增初始值和自增步長設置不為 1
唯一鍵沖突

除此之外，事務回滾也會導致這種情況

自增值不連續場景 3

我們現在表里有一行 (1,1,1) 的記錄，AUTO_INCREMENT = 3：

我們先插入一行數據 (null, 2, 2)，也就是 (3, 2, 2) 嘛，并且 AUTO_INCREMENT 變為 4：

再去執行這樣一段 SQL：

雖然我們插入了一條 (null, 3, 3) 記錄，但是使用 rollback 進行回滾了，所以數據庫中是沒有這條記錄的：

在這種事務回滾的情況下，自增值并沒有同樣發生回滾！如下圖所示，自增值仍然固執地從 4 增加到了 5：

所以這時候我們再去插入一條數據（null, 3, 3）的時候，主鍵 id 就會被自動賦為 5 了：

那么，為什么在出現唯一鍵沖突或者回滾的時候，MySQL 沒有把表的自增值改回去呢？回退回去的話不就不會發生自增 id 不連續了嗎？

事實上，這么做的主要原因是為了提高性能。

我們直接用反證法來驗證：假設 MySQL 在事務回滾的時候會把自增值改回去，會發生什么？

現在有兩個并行執行的事務 A 和 B，在申請自增值的時候，為了避免兩個事務申請到相同的自增 id，肯定要加鎖，然后順序申請，對吧。

假設事務 A 申請到了 id = 1，事務 B 申請到 id=2，那么這時候表 t 的自增值是3，之后繼續執行。
事務 B 正確提交了，但事務 A 出現了唯一鍵沖突，也就是 id = 1 的那行記錄插入失敗了，那如果允許事務 A 把自增 id 回退，也就是把表的當前自增值改回 1，那么就會出現這樣的情況：表里面已經有 id = 2 的行，而當前的自增 id 值是 1。
接下來，繼續執行的其他事務就會申請到 id=2。這時，就會出現插入語句報錯“主鍵沖突”。