MySQL的自增主键一定是连续的吗？

简介

MySQL自增主键的作用是在自增列上定义一个主键，让主键的值由数据库系统自动分配，而不需要用户输入。这样可以避免主键重复或缺失，提高插入数据的效率，保持主键索引的递增顺序，减少索引的碎片化。

索引的碎片化是指索引中的数据在物理存储上不连续，而是分散在磁盘上的不同位置。这样会导致查询需要更多的磁盘I/O操作，降低查询性能和资源利用率。

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

先创建一张表，其中id为主键且自增，字段a创建了一个唯一索引。

自增值保存在哪？

执行insert test_incr(a, b) values(1,1);插入一条数据；再执行show create table test_incr;命令查看表的结构。

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

从上述表结构可以看到，表定义里面出现了一个 AUTO_INCREMENT=2，表示下一次插入数据时，如果需要自动生成自增值，会生成 id = 2。

但需要注意的是，自增值并不会保存在这个表结构也就是 .frm 文件中，不同的引擎对于自增值的保存策略不同：

MyISAM 引擎的自增值保存在数据文件中
InnoDB 引擎的自增值，其实是保存在了内存里，并没有持久化。第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。

举个例子：现在表里当前数据行里最大的 id 是 1，AUTO_INCREMENT=2对吧。这时候，我们删除 id=1 的行，AUTO_INCREMENT 还是 2。

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

我们趁MySQL不注意，使用kill -9 pid杀掉MySQL服务（主打的就是一个不讲武德），然后重启服务，再次执行show create table test_incr;

此时我们发现AUTO_INCREMENT没有显示，是因为默认等于1不会展示。

以上测试基于MySQL数据库的5.7.36版本，存储引擎为INNODB。

实际上，到了 MySQL 8.0 版本后，自增值的变更记录被放在了 redo log 中，提供了自增值持久化的能力，也就是实现了“如果发生重启，表的自增值可以根据 redo log 恢复为 MySQL 重启前的值”。

也就是对于上面这个例子来说，重启实例后这个表的 AUTO_INCREMENT 仍然是 2。

理解了 MySQL 自增值到底保存在哪里以后，再来看看自增值的修改机制，并为大家介绍一些自增值不连续的场景，以下结论基于MySQL8，存储引擎INNODB。

自增列指定值

在 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…

一定如此吗？

凡是没有绝对~~~

MySQL中是通过auto_increment_offset 和 auto_increment_increment 这两个参数来实现主键自增的，这俩分别用来表示自增的初始值和步长，默认值都是 1。

所以，上面的例子中生成新的自增值的步骤实际是这样的：从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 100 的值，作为新的自增值。

也就是说，这种情况下，当步长不等于1时，自增值可能会是 102，103 等等之类的，就会导致不连续的主键 id。

更遗憾的是，即使在自增初始值和步长这两个参数都设置为 1 的时候，自增主键 id 也不一定能保证主键是连续的。

唯一键冲突

我们之前往表里插入一条 (null,1,1) 的记录，生成的主键是 1，AUTO_INCREMENT= 2。

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

但是我们可以发现，虽然插入失败了，AUTO_INCREMENT的值从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,1,1) 的记录，并且AUTO_INCREMENT = 3：

然后我们手动开启事务，插入一条(null,2,2)的数据，再回滚。

显然，我们执行了插入语句，但是回滚了事务，所以数据库中并没有刚才的(null,2,2)数据。

但是我们可以发现自增值并没有同样发生回滚！如上图所示，自增值仍然固执地从 3 增加到了 4。

如果这时候我们再去插入一条数据（null, 2, 2）的时候，主键 id 就会被自动赋为 4 了：

所以，当出现唯一键冲突或者回滚的时候，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。这时，就会出现插入语句报错“主键冲突”。