为什么MySQL InnoDB引擎自增id为什么不连续

表结构如下：

CREATE TABLE `t` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`a` INT (11) DEFAULT NULL,
	`b` INT (11) DEFAULT NULL,
	PRIMARY KEY (`id`),
	UNIQUE KEY `a` (`a`)
) ENGINE = INNODB;

自增值的存储

存储自增值的变量名为：AUTO_INCREMENT

MyISAM引擎自增值保存在数据文件中。

MySQL8.0之前的版本，InnoDB自增值保存在内存里。

当数据库重启之后，第一次打开一张表的时候，AUTO_INCREMENT取max(id)+1作为当前表的自增值。假如当前数据库自增值为5，插入一条数据后自增值变为6，删除id=5的记录，重启数据库，则AUTO_INCREMENT值重新变为5。

MySQL8.0及其之后，自增值保存在redo logo中，有了“持久化”的功能。

导致自增值不连续的原因

（1）唯一键冲突
像下面这样，表t已经存在a为1的记录，当试图再插入一条a为1的记录时会报唯一键重复，虽然插入失败，但是AUTO_INCREMENT的值已经加1了，并且不会因为插入失败减1。

（2）批量插入语句insert…select…导致自增值不连续
MySQL5.2及其之后，新增了参数innodb_autoinc_lock_mode：

• 取值0的时候，SQL语句执行结束之后，立即释放自增id锁

• 取值1的时候（默认值），
  普通insert语句，自增锁在申请之后立即释放
  类似insert…select…批量插入语句，SQL语句执行结束之后，立即释放自增id锁

  之所以有第二点，insert…select…提前不知道需要多少个id，申请一个id，插入一条数据，设想这样一个场景：
  假如事务B要向表t连续插入5条数据，当插入完第3条的时候，事务A向表t中插入一条记录，事务B接着插入剩下的2条记录，那么事务B插入的5条记录id就不连续。虽然这在逻辑上暂时没问题，但是假如binlog_format=statement的时候，即binlog日志记录的是事务执行的逻辑SQL语句，事务提交的时候，要么先记录A事务的逻辑SQL语句，要么先记录B事务的逻辑SQL语句，因此使用binlog做数据恢复的时候，重新执行事务B的这个SQL语句，id就是连续的，这就导致了数据不一致。

• 取值2的时候，自增锁在申请之后立即释放

取值2的时候，可以将binlog_format设置为row，即binglog中记录具体的修改数据的操作，这样，当用binlog恢复数据的时候，无需依赖自增值生成id，这就避免的上面所说的场景，也提高了有批量插入数据时的并发性能。

对于批量插入语句insert…select…，MySQL并不是每次需要插入一条记录就去申请一个id，因为这样能消耗性能，MySQL的策略是批量申请自增id：第一次申请自增id，分配1个，这个批量语句接下来的每一次申请自增id都会得到上一次分配数量的2倍。

因此，像下面这种场景，执行insert into t4 values(null, 5,5);的时候，申请到的id是8；

（3）事务回滚
开启一个事务，插入一条数据之后回滚，AUTO_INCREMENT的值也不会因此减1。

为什么AUTO_INCREMENT不会回退

为了提升性能。

假设这样一种场景：
A、B两个事物，A事物申请到id为2，B事物申请到id为3，此时自增值为4，B事物提交成功，A事物回滚。如果自增值允许回退减1，那自增值减为3，那下次申请自增值的时候得到3，插入这条记录的时候就会报主键id错误。
解决方案可以是：

1. 申请id之前，先判断这个id在表中是否存在，如果存在则继续取下一个。这样查找id是否存在的耗能太大。
2. 等到A事务提交之后，B事务才能申请id。大大降低了事务并发度。

以上两种解决方案都有很大的弊端，因此InnoDB就使用了不连续的自增值。