为什么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就使用了不连续的自增值。

版权声明:本文为IT_10原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/IT_10/article/details/103438475