MySQL自增主键为何不是连续的呢？

1、前文

众所周知，由于自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，大量的随机IO。自增主键不连续。

这是大家已经熟知的知识点，但是，可能也有大部分朋友和之前的小龙一样不知道为何自增主键不是严格递增的？

今天这篇文章，我们就来说说这个问题，看看什么情况下自增主键会出现 “断层”？

为了更加形象，这里创建一个表xl_tb，其中id是自增主键字段、a是唯一索引，然后插入一条数据，我们再查看它的表结构。

CREATE TABLE `NewTable` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`a`  int(11) NULL ,
`b`  int(11) NULL ,
PRIMARY KEY (`id`),
UNIQUE INDEX `a` (`a`) USING BTREE 
);

insert into xl_tb values(null, 1, 1)

mysql> show create table xl_tb\G;
*************************** 1. row ***************************
       Table: xl_tb
Create Table: CREATE TABLE `xl_tb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.04 sec)

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

大家也可以自己手动试试！

但是，看到这里，可能有朋友以为自增值存在表结构里吧！哈哈，你这样想就有错啦！

2、自增主键为何不连续

2.1、自增主键存储策略

接下来，我们一起看看自增值保存在哪里吧！

其实，不同的存储引擎，自增值保存策略不一样的。

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

• InnoDB引擎的自增值，其实是保存在了内存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”，具体情况是：

  • 在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。

  • 举例来说，如果一个表当前数据行里最大的id是10，AUTO_INCREMENT=11。这时候，我们删除id=10的行，AUTO_INCREMENT还是11。但如果马上重启实例，重启后这个表的AUTO_INCREMENT就会变成10。 也就是说，MySQL重启可能会修改一个表的AUTO_INCREMENT的值。

  • 在MySQL 8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。

理解了MySQL对自增值的保存策略以后，我们再看看自增值修改机制。

2.2、自增值修改机制

1. 如果插入数据时id字段指定为0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT值填到自增字段；

2. 如果插入数据时id字段指定了具体的值，就直接使用语句里指定的值。

2.3、自增值新增机制

1. 如果准备插入的值>=当前自增值，新的自增值就是“准备插入的值+1”；

2. 否则，自增值不变。

2.4、自增值的修改时机

eg：

假设，表xl_tb里面已经有了(1,1,1)这条记录，这时我再执行一条插入数据命令：

insert into t values(null, 1, 1); (自增id,唯一键a,普通字段b)

这个语句的执行流程就是：

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

2. InnoDB发现用户没有指定自增id的值，获取表xl_tb当前的自增值4；

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

4. 将表的自增值改成5；

5. 继续执行插入数据操作，由于已经存在a=1的记录，所以报Duplicate key error，语句返回。

这个表的自增值改成5，是在真正执行插入数据的操作之前。这个语句真正执行的时候，因为碰到唯一键a冲突，所以id=2这一行并没有插入成功，但也没有将自增值再改回去。

所以，在这之后，再插入新的数据行时，拿到的自增id就是5。也就是说，出现了自增主键不连续的情况。因此，唯一键冲突是导致自增主键id不连续的第一种原因。

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

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

那么，接下来跟着我的思路来看看，为何不让自增主键后退吧！

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

• 首先，事务A申请到 id=2，此时当前自增值为3，由于加锁顺序申请，事务B申请到 id=3（当前自增值），此时，当前自增值变为 3+1=4

• 然后，事务 A、B都插入，假设事务B先插入然后成功插入，然后事务A插入发生了唯一键冲突

• 如果假设允许自增值后退，自增值就变为2啦，假如事务A继续插入，申请到 id=2，成功插入，申请到 id=3，插入，由于之前事务B已经插入 id=3的数据，此时发生主键冲突

那怎样解决呢？

• 每次申请id之前，先判断表里面是否已经存在这个id。

• 扩大锁范围，必须等事务执行完，才能申请下一个

虽然这两种方法可以解决，但是无疑性能极低。

于是，便让自增值不能回退，而避免造成主键冲突等问题。（也许还有其他问题我没有想到）

 

3、总结

为什么自增主键不连续？

• 在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化

• 事务回滚（自增值不能回退，因为并发插入数据时，回退自增ID可能造成主键冲突）

• 唯一键冲突（由于表的自增值已变，但是主键发生冲突没插进去，下一次插入主键=现在变了的子增值+1，所以不连续）

  转载：阿里二面差点败在这道题：MySQL自增主键为何不是连续的呢？