MySQL知识学习08（MySQL自增主键一定是连续的吗）

梦里梦外; 2024-03-22 12:28 145阅读 0赞

众所周知，自增主键可以让聚集索引尽量地保持递增顺序插入，避免了随机查询，从而提高了查询效率。

但实际上，MySQL 的自增主键并不能保证一定是连续递增的。

下面举个例子来看下，如下所示创建一张表：

![在这里插入图片描述][5877bba1f9084ebd97422e1a4d765b79.png]

***1、自增值保存在哪里？***

使用 `insert into test_pk values(null, 1, 1)` 插入一行数据，再执行 `show create table` 命令来看一下表的结构定义：

![在这里插入图片描述][9731707ba6e24ba989c714803150ef22.png]

上述表的结构定义存放在后缀名为 `.frm` 的本地文件中，在 MySQL 安装目录下的 data 文件夹下可以找到这个 `.frm` 文件：

![在这里插入图片描述][cc2c4acaf5df4c8f874ce63a6e682b1d.png]

从上述表结构可以看到，表定义里面出现了一个 `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。

![在这里插入图片描述][d2f833043eef404898fe70f5716d2390.png]

但如果马上重启 MySQL 实例，重启后这个表的 AUTO\_INCREMENT 就会变成 1。 也就是说，MySQL 重启可能会修改一个表的 AUTO\_INCREMENT 的值。

![在这里插入图片描述][a361fb9c21f846dc9b27b55dc9b9742c.png]

![在这里插入图片描述][fac09f2828844c409d38c89f3d670a5a.png]

以上，是在我本地 MySQL 5.x 版本的实验，实际上，**到了 MySQL 8.0 版本后，自增值的变更记录被放在了 redo log 中，提供了自增值持久化的能力 ，也就是实现了“如果发生重启，表的自增值可以根据 redo log 恢复为 MySQL 重启前的值”也就是说对于上面这个例子来说，重启实例后这个表的 AUTO\_INCREMENT 仍然是 2**。

理解了 MySQL 自增值到底保存在哪里以后，我们再来看看自增值的修改机制，并以此引出第一种自增值不连续的场景。

***2、自增值不连续的场景***

**自增值不连续场景 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，对吧

![在这里插入图片描述][d832b2b69e3e42039887bf4b7dbc6c62.png]

这时我再执行一条插入 (null,1,1) 的命令，很显然会报错 Duplicate entry，因为我们设置了一个唯一索引字段 a：

![在这里插入图片描述][30c03e7234704c9bad99ffd60ae1e96a.png]

但是，你会惊奇的发现，虽然插入失败了，但自增值仍然从 2 增加到了 3！

这是为啥？

我们来分析下这个 insert 语句的执行流程：

1.  执行器调用 InnoDB 引擎接口准备插入一行记录 (null,1,1);
2.  InnoDB 发现用户没有指定自增 id 的值，则获取表 test\_pk 当前的自增值 2；
3.  将传入的记录改成 (2,1,1);将表的自增值改成 3；继续执行插入数据操作，由于已经存在 a=1 的记录，所以报 Duplicate key error，语句返回

**可以看到，自增值修改的这个操作，是在真正执行插入数据的操作之前**。

这个语句真正执行的时候，因为碰到唯一键 a 冲突，所以 id = 2 这一行并没有插入成功，但也没有将自增值再改回去。所以，在这之后，再插入新的数据行时，拿到的自增 id 就是 3。也就是说，出现了自增主键不连续的情况。

至此，我们已经罗列了两种自增主键不连续的情况：

*  自增初始值和自增步长设置不为 1
 *  唯一键冲突

除此之外，事务回滚也会导致这种情况

**自增值不连续场景 3**

我们现在表里有一行 (1,1,1) 的记录，AUTO\_INCREMENT = 3：

![在这里插入图片描述][a8f671571d9c41d2aea0615ef272cd64.png]

我们先插入一行数据 (null, 2, 2)，也就是 (3, 2, 2) 嘛，并且 AUTO\_INCREMENT 变为 4：

![在这里插入图片描述][fad58006d6654ad4b473f4801172e34b.png]

再去执行这样一段 SQL：

![在这里插入图片描述][f5a6feb3da644d2c981546ca60613c40.png]

虽然我们插入了一条 (null, 3, 3) 记录，但是使用 rollback 进行回滚了，所以数据库中是没有这条记录的：

![在这里插入图片描述][a9142d0ed6124037b11a18fbebfeaa5b.png]

在这种事务回滚的情况下，自增值并没有同样发生回滚！如下图所示，自增值仍然固执地从 4 增加到了 5：

![在这里插入图片描述][97d64265c5cd4a22ab723e7f425de31d.png]

所以这时候我们再去插入一条数据（null, 3, 3）的时候，主键 id 就会被自动赋为 5 了：

![在这里插入图片描述][470d788a643b41cb826437a07e35c647.png]

那么，为什么在出现唯一键冲突或者回滚的时候，MySQL 没有把表的自增值改回去呢？回退回去的话不就不会发生自增 id 不连续了吗？

事实上，这么做的主要原因是为了提高性能。我们直接用反证法来验证：假设 MySQL 在事务回滚的时候会把自增值改回去，会发生什么？

现在有两个并行执行的事务 A 和 B，在申请自增值的时候，为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请，对吧。

1.  假设事务 A 申请到了 id = 1， 事务 B 申请到 id=2，那么这时候表 t 的自增值是 3，之后继续执行。
2.  事务 B 正确提交了，但事务 A 出现了唯一键冲突，也就是 id = 1 的那行记录插入失败了，那如果允许事务 A 把自增 id 回退，也就是把表的当前自增值改回 1，那么就会出现这样的情况：表里面已经有 id = 2 的行，而当前的自增 id 值是 1。
3.  接下来，继续执行的其他事务就会申请到 id=2。这时，就会出现插入语句报错“主键冲突”。

![在这里插入图片描述][d1bf0db72f5945969b57649f71605a1d.png]

而为了解决这个主键冲突，有两种方法：

1.  每次申请 id 之前，先判断表里面是否已经存在这个 id，如果存在，就跳过这个 id
2.  把自增 id 的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增 id

很显然，上述两个方法的成本都比较高，会导致性能问题。而究其原因呢，是我们假设的这个 “允许自增 id 回退”。

因此，InnoDB 放弃了这个设计，语句执行失败也不回退自增 id。也正是因为这样，所以才只保证了自增 id 是递增的，但不保证是连续的。

综上，已经分析了三种自增值不连续的场景，还有第四种场景：批量插入数据。

**自增值不连续场景 4**

对于批量插入数据的语句，MySQL 有一个批量申请自增 id 的策略：

1.  语句执行过程中，第一次申请自增 id，会分配 1 个；
2.  1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；
3.  2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。

注意，这里说的批量插入数据，不是在普通的 insert 语句里面包含多个 value 值！！！，因为这类语句在申请自增 id 的时候，是可以精确计算出需要多少个 id 的，然后一次性申请，申请完成后锁就可以释放了。

而对于 insert … select、replace … select 和 load data 这种类型的语句来说，MySQL 并不知道到底需要申请多少 id，所以就采用了这种`批量申请`的策略，毕竟一个一个申请的话实在太慢了。

举个例子，假设我们现在这个表有下面这些数据：

![在这里插入图片描述][cb4fb3e161144845a24ce9c8791685ca.png]

我们创建一个和当前表 test\_pk 有相同结构定义的表 test\_pk2：

![在这里插入图片描述][3b23ec58bac34579919dc8e207f2ad1f.png]

然后使用 insert…select 往 teset\_pk2 表中批量插入数据：

![在这里插入图片描述][247cd160d2e244c1aff9f5c5f28b2cd0.png]

可以看到，成功导入了数据。

再来看下 test\_pk2 的自增值是多少：

![在这里插入图片描述][48a9fa2a39bd4087b65c34c143fec4cb.png]

如上分析，是 8 而不是 6

具体来说，insert…select 实际上往表中插入了 5 行数据 （1 1）（2 2）（3 3）（4 4）（5 5）。但是，这五行数据是分三次申请的自增 id，结合批量申请策略，每次申请到的自增 id 个数都是上一次的两倍，所以：

*  第一次申请到了一个 id：id=1
 *  第二次被分配了两个 id：id=2 和 id=3
 *  第三次被分配到了 4 个 id：id=4、id = 5、id = 6、id=7

由于这条语句实际只用上了 5 个 id，所以 id=6 和 id=7 就被浪费掉了。之后，再执行 **insert into test\_pk2 values(null,6,6)**，`实际上插入的数据就是（8,6,6)：`

***小结***

本文总结下自增值不连续的 4 个场景：

1.  自增初始值和自增步长设置不为 1
2.  唯一键冲
3.  突事务回滚
4.  批量插入（如 insert…select 语句）

[5877bba1f9084ebd97422e1a4d765b79.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/ac20d12e3f7a4c87915d68bb5f31200b.png
[9731707ba6e24ba989c714803150ef22.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/0742ac56fdef45869665742c63c5daec.png
[cc2c4acaf5df4c8f874ce63a6e682b1d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/ca12bc3d0e334e92a0812f5d08fa4596.png
[d2f833043eef404898fe70f5716d2390.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/c3f3220a00fe4a9eb5d1f5c5262c6332.png
[a361fb9c21f846dc9b27b55dc9b9742c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/c8536221cebd45f3b6ecd4fa654b6b5f.png
[fac09f2828844c409d38c89f3d670a5a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/841a4367dc6d4801b9c63bcaaaaecd7a.png
[d832b2b69e3e42039887bf4b7dbc6c62.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/2db7579d9e4348338f721ad54912c26d.png
[30c03e7234704c9bad99ffd60ae1e96a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/8fd3d96406704197b1a3f7e878c28db7.png
[a8f671571d9c41d2aea0615ef272cd64.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/6659e73b2aef4f38abe01ac27cbaa78e.png
[fad58006d6654ad4b473f4801172e34b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/69e78130558d4d26bff7a9707645cb8e.png
[f5a6feb3da644d2c981546ca60613c40.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/17e5397249d24886adc364513861f799.png
[a9142d0ed6124037b11a18fbebfeaa5b.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/00d0577d0941475ca6d72625d5b549fc.png
[97d64265c5cd4a22ab723e7f425de31d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/2066d5faacf643bfbb98ac0215ae18a6.png
[470d788a643b41cb826437a07e35c647.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/7421346119f6442685b24bd573a309c5.png
[d1bf0db72f5945969b57649f71605a1d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/2903486511764ceab739f2bc2f58274f.png
[cb4fb3e161144845a24ce9c8791685ca.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/d5e3438519ed4f7b96c0d9c8acf56224.png
[3b23ec58bac34579919dc8e207f2ad1f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/d3e2299608d84048b98e69d7c2ceccbd.png
[247cd160d2e244c1aff9f5c5f28b2cd0.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/559643b0f5004f06adbda47fff18b8a2.png
[48a9fa2a39bd4087b65c34c143fec4cb.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/86fe29be7cbc422397a130b81a96b2cb.png