mysql的innodb自增主键为什么不是连续的

　　　　　　　　　　　　图1

图1中是表t原有的数据，这个时候我们执行show create table t会看到如下输出，如图二所示现在的自增值是2，也就是下一个不指定主键值的插入的数据的主键就是2

图2

Innodb引擎的自增值，是保存在内存中的，并且到了mysql8.0版本后，才有了“自增值持久化”的能力，也就是才实现了"如果发生重启，表的自增值可以恢复为mysql重启前的值"

也就说在mysql5.7及之前的版本，自增值保存在内存中，并没有持久化，每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id),然后讲max(id)+1作为当前表的自增值

 

举例来说，如果一个表中当前数据行的最大id=3，自增值=4，这个时候我们删除id=3的数据后，自增值还是=4，但是这个时候如果马上重启，重启后这个表的自增值就会变成3，

我们可以做个实验:

 

 

这个时候的自增值=4

 

 我们删掉了id=3的数据

然后我们在查询一下自增值，我们可以看到这个时候的自增值还是4

然后我们在重启以后在查询一下，可以看到这个时候的自增值就是3了

 

 在mysql8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值

接下来我们在做个实验说明一下自增值为啥不是连续的，我们刚才建的表t的c字段我加了唯一索引,现在我们表中的自增值应该是3对吧，因为刚才我重启过了，然后我们执行INSERT INTO t VALUES(NULL,2,3)

这条语句，会报错插入失败，这条语句的执行流程是这样的

1，执行器调用innodb引擎接口写入一行，传入的这一行的值是(0,2,3);

2，innodb发现用户没有指定自增id的值，获取表t当前的自增值3；

3，将传入的行的值改成(3,1,1);

4，将表的自增值改成4；

5，继续执行插入数据操作，由于已经存在C=2的记录，所以报Duplicate key eror，语句返回

 

 

 然后我们在查询一下自增值，看到了没有，现在的自增值已经变成了4，也就说上面这条语句插入失败以后，自增值没有回滚，这样下次我们在插入数据的时候的主键值就是4，就会造成我们的主键值不一致

 

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

同样的，事务回滚也会产生类似的现象，这就是第二种原因

你可能会问，为什么在出现唯一键冲突或者事务回滚的时候,mysql没有把表t的自增值改回去呢？如果把表t的当前自增值从4改回3，再插入新数据的时候，不就可以生成id=3的一行数据了吗？

其实,mysql这么设计是为了提升性能，接下来，分析一下这个设计思路，看看自增值为什么不能回退

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增id，肯定要加锁，然后顺序申请

1.假设事务A申请到了id=2,事务B申请到id=3，那么这时候表t的自增值是4，之后继续执行

2.事务B正确提交了，但事务A出现了唯一键冲突

3.如果允许事务A把自增id回退，也就是把表t的当前自增值改成2，那么就会出现这样的情况:表里面已经有id=3的行，而当前的自增id值是2

4.接下来，继续执行的其他事务就会申请到id=2，然后在申请到id=3，这时候，就会出现插入语句报错“主键冲突"

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

1，每次申请id之前，先判断表里面是否已经存在这个id，如果存在，就跳过这个id,但是，这个方法的成本很高，因为，本来申请id是一个很快的操作，现在还要再去主键索引树上判断id是否存在

2，把自增id的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增id，这个方法的问题，就是锁的粒度太大，系统并发能力大大下降

可见，这两个方法都会导致性能问题，造成这些麻烦的罪魁祸首，就是我们假设的这个”允许自增id回退“的前提导致的，

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

了解更多：https://www.toutiao.com/c/user/83293539887/#mid=1633933053814798