MySQL深入35-自增主键为什么不连续

# MySQL深入35-自增主键为什么不连续

由于自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑。但是自增主键不能保证连续递增，自增主键是会出现“空洞”。

现在创建一个表t，进行案例分析：

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

# 自增值保存在何处

空表t执行insert into t values(null, 1, 1);插入一行数据，再执行show create table命令，查看一下结果：

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

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

但是这个自增值不是保存在表结构定义里面的，表的结构定义存放在后缀名.frm的文件中，但是并不会保存自增值。

不同的引擎对于自增值的保存策略不同：

MyISAM 引擎的自增值保存在数据文件中。
InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力：
- 在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。
- 在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

# 自增值修改机制

在MySQL里面，如果字段id被定义为AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；
如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。

同时根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，某次要插入的值是 X，当前的自增值是 Y。

如果 X < Y，那么这个表的自增值不变
如果 X≥Y，就需要把当前自增值修改为新的自增值。

新的自增值生成算法：从auto_increment_offset开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。

auto_increment_offset 和 auto_increment_increment 是两个系统参数，分别用来表示自增的初始值和步长，默认值都是 1。

当 auto_increment_offset 和 auto_increment_increment 都是 1 的时候，新的自增值生成逻辑很简单，就是：

如果准备插入的值 >= 当前自增值，新的自增值就是“准备插入的值 +1”；
否则，自增值不变。

# 自增值的修改时机

假设，表t里面已经有了（1，1，1）这条记录，这时候我们再执行一条插入数据命令：

insert into t values(null, 1, 1);

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

执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0，1，1)；
InnoDB发现用户没有指定自增id的值，获取表t当前的自增值2；
将传入的行的值改为（2，1，1）；
将表的自增值改为3；
继续执行插入数据操作，由于存在c=1的记录，报错，语句插入失败。

可以看到，自增值已经更换为3了，但是语句由于唯一键c冲突，所以id=2这一行并没有插入成功，但是并没有将自增值再改回去。所以在这之后，再插入新的数据行时，拿到的自增就是3。这样就出现了自增主键不连续的情况。对于这种情况，是因为唯一键冲突导致自增主键 id 不连续。

下面来看第二种情况：


insert into t values(null,1,1);
begin;
insert into t values(null,2,2);
rollback;
insert into t values(null,2,2);
//插入的行是(3,2,2)

也就是说回滚的时候，MySQL没有把表t的自增值改回去。其实，MySQL这么设计是为了提升性能。下面我们来分析一下自增值为什么不能回退？

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

假设事务 A 申请到了 id=2，事务 B 申请到 id=3，那么这时候表 t 的自增值是 4，之后继续执行。
事务B正确提交了，但事务A出现了唯一键冲突。
如果允许事务 A 把自增 id 回退，也就是把表 t 的当前自增值改回 2，那么就会出现这样的情况：表里面已经有 id=3 的行，而当前的自增 id 值是 2。
接下来，继续执行的其他事务就会申请到 id=2，然后再申请到 id=3。这时，就会出现插入语句报错“主键冲突”。

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

每次申请 id 之前，先判断表里面是否已经存在这个 id。如果存在，就跳过这个 id。但是去主键索引树上判断id是否存在这个成本较高。
把自增 id 的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增 id。但是这样会影响系统并发能力。

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

# 自增锁的优化

自增id锁并不是一个事务锁，而是每次申请完就马上释放，以便允许别的事务再申请。

对于批量插入数据的语句，MySQL有一个批量申请自增id的策略：

语句执行过程中，第一次申请自增 id，会分配 1 个；
1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；
2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；
依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。


insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);
create table t2 like t;
insert into t2(c,d) select c,d from t;
insert into t2 values(null, 5,5);

insert…select，实际上往表 t2 中插入了 4 行数据。但是，这四行数据是分三次申请的自增 id，第一次申请到了 id=1，第二次被分配了 id=2 和 id=3，第三次被分配到 id=4 到 id=7。

这就是第三种情况会出现自增id不连续的情况。

# 小结

自增主键不连续的情况：

插入语句出错
事务回滚
批量插入数据

# 参考

MySQL 实战 45 讲-极客时间 (opens new window)

#MySQL

上次更新: 2024/06/29, 15:13:44

← MySQL深入34-InnoDB和Memory MySQL深入36-insert语句的锁→