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

灰太狼 2023-06-20 04:58 29阅读 0赞

表结构如下：

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。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70]  
**（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；  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70 1]

**（3）事务回滚**  
开启一个事务，插入一条数据之后回滚，AUTO\_INCREMENT的值也不会因此减1。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70 2]

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20191207191209257.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20191207201304939.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20191207191907526.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0lUXzEw,size_16,color_FFFFFF,t_70