被问到Mysql自增主键为啥不是连续的，我又懵了

共计 1330 个字符，预计需要花费 4 分钟才能阅读完成。

面试官：mysql自增主键用过吧？

小菜：用过，建表时指定自动递增就行，然后插入数据就可以不用指定主键的值了

面试官：不错，那你知道Mysql自增主键是严格递增的吗？

小菜：肯定是递增的呀，诶，好像不对，也有见过不是连续的

面试官：那你知道为什么会出现不连续的呢？

小菜：emmm……这个岗位可能不是很适合我

面试官：那今天就面到这里，还记得进来的路吧……

一、自增主键

自增主键采用数字型，占用空间小，自动编号，没有指定序号也不用担心主键冲突。可以让主键索引尽可能的保持递增顺序插入，避免了页分裂，大量的随机IO。

可以使用查看建表语句来查询下一次的自增序号

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

看到这，如果你觉得自增值是存在表里的话，那你就错了。

二、自增主键为何不连续

表里最大id=4，也可能出现上面这种AUTO_INCREMENT=6，下一次插入数据，自增主键=6的情况，那为什么呢？

2.1 自增值的存储策略

不同的存储引擎，自增值的存储策略也是不一样的。

1. MyISAM引擎的自增值保存在数据文件中
2. InnoDB引擎的自增值，保存在内存中，只有到了Mysql8.0版本，才有了自增值持久化的功能
   1. 在Mysql5.7及之前的版本，自增值保存在内存中。所以每次重启后，第一次打开表的时候，都会去表里找最大值max(id)，使用max(id)+1当做这个表下一次的自增值。
      • eg.:当自增值=10，假设你删除了id=9的记录，重启了数据库实例，这时候，自增值就会变成9，不重启的话，就还是10。所以说，Mysql重启是可能修改一个表AUTO_INCREMENT的值
   2. 在Mysql8.0之后的版本，将自增值的变更记录在了redo log中，重启时依靠这个文件回复重启之前的值

2.2 自增值修改机制

1. 如果插入时id字段指定的是0、null或未指定值，那么就使用这个表的AUTO_INCREMENT 的值当做自增值
2. 如果插入时指定了具体的值，就使用sql语句里指定的值

2.3 自增值新增机制

1. 如果插入的记录指定的 id >= 当前的自增值，那么新的自增值就是 这个 id 的值+1
2. 否则，自增值不变

2.4 自增值的修改时机

假设有一张表，结构如下

自增主键id，唯一键a，普通字段b

表里已经存在一条记录，id=1,a=1,b=1

这时候，我再执行一条插入语句

insert into t values(0,1,1);

这条语句的执行顺序：

1. 执行器调用InnoDB引擎接口写入一行，传入的值是 (0,1,1)
2. InnoDB发现没有指定id，故获取表t的自增值3
3. 将传入的值改为 (3,1,1)
4. 将表的自增值改为4
5. 继续执行插入操作，由于这时候已经存在a=1的记录，故触发唯一键冲突Duplicate key error，插入失败

自增值是在真正执行插入操作之前变更的，但是插入失败后，自增值也不会再被改回去。

所以，唯一索引冲突是导致自增主键id不连续的一种原因

同样，事务回滚也会产生类似的现象，事务回滚也是另外一种原因

而Mysql为什么设计成不让自增值回退呢？

因为多事务并发插入时，回退自增值可能会导致主键冲突，那么就要另外的方案来解决这个问题，无疑影响了性能。