39 | 自增主键为什么不是连续的？

[git-zjx]

示例表

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;

自增值保存在哪

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

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

自增值修改机制

在插入一行数据的时候，自增值的行为如下：

• 如果插入数据时，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。

自增值的修改时机

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

这个语句的执行流程：

1. 执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值为 (0,1,1)
2. InnoDB 发现用户没有直到自增 id，获取表 t 当前的自增值 2
3. 将传入的值改成 (2,1,1)
4. 将表的自增值改为 3
5. 继续执行插入数据的操作，由于已经存在 c = 1 的记录，所以报 Duplicate key err，语句返回

自增键为什么不能回退

从上面可以知道，在真正执行插入操作之前，自增值就被修改了，所以唯一键冲突会导致自增主键不连续，同样的，事务回滚也会造成自增主键不连续的情况

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

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

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

1. 每次申请 id 之前，先判断表里面是否已经存在该 id，如果存在就跳过。这个方法的问题在于，申请 id 还要再去主键索引树上判断 id 是否存在，成本很高
2. 把自增 id 的锁范围扩大，必须等到一个事务执行完成并提交，下个事务才能再神奇自增 id。这个方法的问题在于，锁的粒度太大，系统并发能力下降

所以，InnoDB 放弃了这个设计，只保证了递增，不保证连续

自增锁的优化过程

MySQL 5.0 版本是，自增锁的范围是语句级别的，也就是说，如果一个语句申请了一个表的自增锁，这个锁会等语句执行结束以后释放

MySQL 5.1.22 版本引入了 innodb_autoinc_lock_mode 参数，默认值为 1

• 值为 0 时，表示采用 MySQL 5.0 版本的策略
• 值为 1 时：
  • 普通 insert 语句，自增锁在申请之后马上释放
  • 类似 insert ... select 的批量插入数据的语句，自增锁还是要等语句结束后才释放
• 值为 2 时，所有的申请自增主键的动作都是申请后就释放锁

[git-zjx]

为什么 innodb_auotinc_lock_mode 默认设置下，insert ... select 要使用语句级的锁？为什么默认值不是 2 ？

如果 session B 申请了自增值之后马上释放自增锁，那么可能出现：

1. session B 先插入了两个记录，(1,1,1)、(2,2,2)
2. 然后，session A 来申请自增 id 得到 id =3， 插入 (3,5,5)
3. 之后，session B 继续执行，插入两条记录 (4,3,3)、(5,4,4)

当 binlog_format = statement 时，由于两个 session 是同时执行插入数据命令的，所以 binlog 里对表 t2 的更新日志只有两种情况：要么先记 session A，要么先记 session B。如果这个 binlog 去从库执行或者恢复临时实例，备库和临时实例里，session B 的语句执行出来的结果，id 都是连续的，这时就发生了数据不一致的问题

出现这个问题的原因在于原库 session B 的 insert 语句生成了不连续的 id，而这个情况无法用 statement 格式的 binlog 串行执行出来

要解决这个问题，有两种思路：

1. 让原库的批量插入数据语句，固定生成连续的 id 值。所以 ，自增锁直到语句执行结束才释放，就是 为了这个目的
2. 在 binlog 里面把插入数据的操作都如实记录进来，到备库执行的时候，不再依赖自增主键去生成。其实就是 innodb_auotinc_lock_mode 设置为 2，同时 binlog_format 设置为 row

在生产上，推荐使用第二种，尤其是有 insert ... select 、replace ... select 和 load data 语句的情况下。