MySQL自增主鍵一定是連續(xù)的嗎？

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

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

下面舉個例子來看下，如下所示創(chuàng)建一張表：

自增值保存在哪里？

使用 insert into test_pk values(null, 1, 1) 插入一行數(shù)據(jù)，再執(zhí)行 show create table 命令來看一下表的結(jié)構(gòu)定義：

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

img

從上述表結(jié)構(gòu)可以看到，表定義里面出現(xiàn)了一個 AUTO_INCREMENT=2，表示下一次插入數(shù)據(jù)時，如果需要自動生成自增值，會生成 id = 2。

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

1）MyISAM 引擎的自增值保存在數(shù)據(jù)文件中

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

舉個例子：我們現(xiàn)在表里當(dāng)前數(shù)據(jù)行里最大的 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 中，提供了自增值持久化的能力，也就是實現(xiàn)了“如果發(fā)生重啟，表的自增值可以根據(jù) redo log 恢復(fù)為 MySQL 重啟前的值”

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

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

自增值不連續(xù)場景 1

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

如果插入數(shù)據(jù)時 id 字段指定為 0、null 或未指定值，那么就把這個表當(dāng)前的 AUTO_INCREMENT 值填到自增字段；

如果插入數(shù)據(jù)時 id 字段指定了具體的值，就直接使用語句里指定的值。

根據(jù)要插入的值和當(dāng)前自增值的大小關(guān)系，自增值的變更結(jié)果也會有所不同。假設(shè)某次要插入的值是 insert_num，當(dāng)前的自增值是 autoIncrement_num：

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

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

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

一定是這樣嗎？

非也~

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

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

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

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

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

自增值不連續(xù)場景 2

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

img

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

img

但是，你會驚奇的發(fā)現(xiàn)，雖然插入失敗了，但自增值仍然從 2 增加到了 3！

這是為啥？

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

執(zhí)行器調(diào)用 InnoDB 引擎接口準備插入一行記錄 (null,1,1);

InnoDB 發(fā)現(xiàn)用戶沒有指定自增 id 的值，則獲取表 test_pk 當(dāng)前的自增值 2；

將傳入的記錄改成 (2,1,1);

將表的自增值改成 3；

繼續(xù)執(zhí)行插入數(shù)據(jù)操作，由于已經(jīng)存在 a=1 的記錄，所以報 Duplicate key error，語句返回

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

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

至此，我們已經(jīng)羅列了兩種自增主鍵不連續(xù)的情況：

自增初始值和自增步長設(shè)置不為 1

唯一鍵沖突

除此之外，事務(wù)回滾也會導(dǎo)致這種情況

自增值不連續(xù)場景 3

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

img

我們先插入一行數(shù)據(jù) (null, 2, 2)，也就是 (3, 2, 2) 嘛，并且 AUTO_INCREMENT 變?yōu)?4：

img

再去執(zhí)行這樣一段 SQL：

img

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

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

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

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

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

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

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

假設(shè)事務(wù) A 申請到了 id = 1， 事務(wù) B 申請到 id=2，那么這時候表 t 的自增值是3，之后繼續(xù)執(zhí)行。

事務(wù) B 正確提交了，但事務(wù) A 出現(xiàn)了唯一鍵沖突，也就是 id = 1 的那行記錄插入失敗了，那如果允許事務(wù) A 把自增 id 回退，也就是把表的當(dāng)前自增值改回 1，那么就會出現(xiàn)這樣的情況：表里面已經(jīng)有 id = 2 的行，而當(dāng)前的自增 id 值是 1。

接下來，繼續(xù)執(zhí)行的其他事務(wù)就會申請到 id=2。這時，就會出現(xiàn)插入語句報錯“主鍵沖突”。

而為了解決這個主鍵沖突，有兩種方法：

每次申請 id 之前，先判斷表里面是否已經(jīng)存在這個 id，如果存在，就跳過這個 id

把自增 id 的鎖范圍擴大，必須等到一個事務(wù)執(zhí)行完成并提交，下一個事務(wù)才能再申請自增 id

很顯然，上述兩個方法的成本都比較高，會導(dǎo)致性能問題。而究其原因呢，是我們假設(shè)的這個 “允許自增 id 回退”。

因此，InnoDB 放棄了這個設(shè)計，語句執(zhí)行失敗也不回退自增 id。也正是因為這樣，所以才只保證了自增 id 是遞增的，但不保證是連續(xù)的。

綜上，已經(jīng)分析了三種自增值不連續(xù)的場景，還有第四種場景：批量插入數(shù)據(jù)。

自增值不連續(xù)場景 4

對于批量插入數(shù)據(jù)的語句，MySQL 有一個批量申請自增 id 的策略：

語句執(zhí)行過程中，第一次申請自增 id，會分配 1 個；

1 個用完以后，這個語句第二次申請自增 id，會分配 2 個；

2 個用完以后，還是這個語句，第三次申請自增 id，會分配 4 個；

依此類推，同一個語句去申請自增 id，每次申請到的自增 id 個數(shù)都是上一次的兩倍。

注意，這里說的批量插入數(shù)據(jù)，不是在普通的 insert 語句里面包含多個 value 值?。?！，因為這類語句在申請自增 id 的時候，是可以精確計算出需要多少個 id 的，然后一次性申請，申請完成后鎖就可以釋放了。

而對于 insert … select、replace … select 和 load data 這種類型的語句來說，MySQL 并不知道到底需要申請多少 id，所以就采用了這種批量申請的策略，畢竟一個一個申請的話實在太慢了。

舉個例子，假設(shè)我們現(xiàn)在這個表有下面這些數(shù)據(jù)：

我們創(chuàng)建一個和當(dāng)前表 test_pk 有相同結(jié)構(gòu)定義的表 test_pk2：

img

然后使用 insert...select 往 teset_pk2 表中批量插入數(shù)據(jù)：

可以看到，成功導(dǎo)入了數(shù)據(jù)。

再來看下 test_pk2 的自增值是多少：

如上分析，是 8 而不是 6

具體來說，insert…select 實際上往表中插入了 5 行數(shù)據(jù) （1 1）（2 2）（3 3）（4 4）（5 5）。但是，這五行數(shù)據(jù)是分三次申請的自增 id，結(jié)合批量申請策略，每次申請到的自增 id 個數(shù)都是上一次的兩倍，所以：

第一次申請到了一個 id：id=1

第二次被分配了兩個 id：id=2 和 id=3

第三次被分配到了 4 個 id：id=4、id = 5、id = 6、id=7

由于這條語句實際只用上了 5 個 id，所以 id=6 和 id=7 就被浪費掉了。之后，再執(zhí)行 insert into test_pk2 values(null,6,6)，實際上插入的數(shù)據(jù)就是（8,6,6)：

小結(jié)

總結(jié)下自增值不連續(xù)的四個場景：

自增初始值和自增步長設(shè)置不為 1

唯一鍵沖突

事務(wù)回滾

批量插入（如 insert...select 語句）

審核編輯：劉清