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

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

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

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

自增值保存在哪里？

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

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

img

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

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

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

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

舉個(gè)例子：我們現(xiàn)在表里當(dāng)前數(shù)據(jù)行里最大的 id 是 1，AUTO_INCREMENT=2，對(duì)吧。這時(shí)候，我們刪除 id=1 的行，AUTO_INCREMENT 還是 2。

但如果馬上重啟 MySQL 實(shí)例，重啟后這個(gè)表的 AUTO_INCREMENT 就會(huì)變成 1。?也就是說(shuō)，MySQL 重啟可能會(huì)修改一個(gè)表的 AUTO_INCREMENT 的值。

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

也就是說(shuō)對(duì)于上面這個(gè)例子來(lái)說(shuō)，重啟實(shí)例后這個(gè)表的 AUTO_INCREMENT 仍然是 2。

理解了 MySQL 自增值到底保存在哪里以后，我們?cè)賮?lái)看看自增值的修改機(jī)制，并以此引出第一種自增值不連續(xù)的場(chǎng)景。

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

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

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

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

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

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

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

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

一定是這樣嗎？

非也~

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

這個(gè)奇數(shù)偶數(shù)其實(shí)是通過(guò) auto_increment_offset 和 auto_increment_increment 這兩個(gè)參數(shù)來(lái)決定的，這倆分別用來(lái)表示自增的初始值和步長(zhǎng)，默認(rèn)值都是 1。

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

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

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

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

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

img

這時(shí)我再執(zhí)行一條插入 (null,1,1) 的命令，很顯然會(huì)報(bào)錯(cuò) Duplicate entry，因?yàn)槲覀冊(cè)O(shè)置了一個(gè)唯一索引字段 a：

img

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

這是為啥？

我們來(lái)分析下這個(gè) insert 語(yǔ)句的執(zhí)行流程：

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

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

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

將表的自增值改成 3；

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

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

這個(gè)語(yǔ)句真正執(zhí)行的時(shí)候，因?yàn)榕龅轿ㄒ绘I a 沖突，所以 id = 2 這一行并沒(méi)有插入成功，但也沒(méi)有將自增值再改回去。所以，在這之后，再插入新的數(shù)據(jù)行時(shí)，拿到的自增 id 就是 3。也就是說(shuō)，出現(xiàn)了自增主鍵不連續(xù)的情況。

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

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

唯一鍵沖突

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

自增值不連續(xù)場(chǎng)景 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 進(jìn)行回滾了，所以數(shù)據(jù)庫(kù)中是沒(méi)有這條記錄的：

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

所以這時(shí)候我們?cè)偃ゲ迦胍粭l數(shù)據(jù)（null, 3, 3）的時(shí)候，主鍵 id 就會(huì)被自動(dòng)賦為 5 了：

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

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

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

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

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

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

接下來(lái)，繼續(xù)執(zhí)行的其他事務(wù)就會(huì)申請(qǐng)到 id=2。這時(shí)，就會(huì)出現(xiàn)插入語(yǔ)句報(bào)錯(cuò)“主鍵沖突”。

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

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

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

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

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

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

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

對(duì)于批量插入數(shù)據(jù)的語(yǔ)句，MySQL 有一個(gè)批量申請(qǐng)自增 id 的策略：

語(yǔ)句執(zhí)行過(guò)程中，第一次申請(qǐng)自增 id，會(huì)分配 1 個(gè)；

1 個(gè)用完以后，這個(gè)語(yǔ)句第二次申請(qǐng)自增 id，會(huì)分配 2 個(gè)；

2 個(gè)用完以后，還是這個(gè)語(yǔ)句，第三次申請(qǐng)自增 id，會(huì)分配 4 個(gè)；

依此類(lèi)推，同一個(gè)語(yǔ)句去申請(qǐng)自增 id，每次申請(qǐng)到的自增 id 個(gè)數(shù)都是上一次的兩倍。

注意，這里說(shuō)的批量插入數(shù)據(jù)，不是在普通的 insert 語(yǔ)句里面包含多個(gè) value 值！??！，因?yàn)檫@類(lèi)語(yǔ)句在申請(qǐng)自增 id 的時(shí)候，是可以精確計(jì)算出需要多少個(gè) id 的，然后一次性申請(qǐng)，申請(qǐng)完成后鎖就可以釋放了。

而對(duì)于 insert … select、replace … select 和 load data 這種類(lèi)型的語(yǔ)句來(lái)說(shuō)，MySQL 并不知道到底需要申請(qǐng)多少 id，所以就采用了這種批量申請(qǐng)的策略，畢竟一個(gè)一個(gè)申請(qǐng)的話(huà)實(shí)在太慢了。

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

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

img

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

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

再來(lái)看下 test_pk2 的自增值是多少：

如上分析，是 8 而不是 6

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

第一次申請(qǐng)到了一個(gè) id：id=1

第二次被分配了兩個(gè) id：id=2 和 id=3

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

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

小結(jié)

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

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

唯一鍵沖突

事務(wù)回滾

批量插入（如 insert...select 語(yǔ)句）

審核編輯：劉清