MySQL 自增值

本文将介绍 MySQL 中的自增值。

一、自增值

MySQL 支持使用 AUTO_INCREMENT 将列的值设置为自增。

通常，我们会将主键设置为自增，这能让主键索引尽量地保持递增顺序插入，避免页分裂，使索引更加紧凑。

二、自增值的保存

  • 对于 InnoDB，
    • MySQL 5.7 及之前，自增值并不会持久化，第一次打开表时会计算自增值，将自增值保存在内存之中
    • 在 MySQL 8.0 中，自增值的变更会被记录在 redo log 中
  • 对于 MyISAM，自增值会被保存在数据文件中

三、自增值的增长

  • 自增值的每次增长以 “增长步长” 为单位
  • 如果插入数据时自增字段没有填入值，获取自增值填入自增字段，增长自增值，继续执行插入操作
  • 如果插入数据时自增字段填入了值，判断该值是否大于自增值，如果大于，持续增长自增值至超过该值，继续执行插入操作

四、自增值不连续的原因

1. 插入执行失败

根据上文，自增值会在执行插入之前增长。

处于性能考虑，假如插入执行失败，InnoDB 不会将已增长的自增值回滚。

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增 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 的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增 id。这个方法的问题，就是锁的粒度太大，系统并发能力大大下降

可见，这两个方法都会导致性能问题。造成这些麻烦的罪魁祸首，就是我们假设的这个“允许自增 id 回退”的前提导致的。

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

因此，只要有失败的插入，自增值便会产生 “空洞”，导致自增值不连续。

1
2
3
4
5
6
7
8
insert into t values(null, 1, 1);
-- 插入成功，id 为 1

insert into t values(null, 1, 1);
-- 插入失败

insert into t values(null, 2, 2);
-- 插入成功，id 为 3

在这种情况下，自增值 2 被跳过，自增值不连续

2. “无法预先知道插入数量” 的批量插入语句

对于类似 insert into ... values (...), (...) 的 “预先知道插入数量” 的批量插入语句，可以根据插入数量一次性申请多个自增值。

对于类似 insert ... select 的 “无法预先知道插入数量” 的批量插入语句，如果每需要一个申请一个，在大批量插入数据的情况下会影响性能。

MySQL 的批量申请自增值的策略是：每次申请到的自增值个数是上一次的两倍。

因此，可能申请到过量的自增值，从而导致自增值不连续。

五、自增锁

在 InnoDB 中，自增值引入了锁机制，通过加锁保证同一个自增值只被使用一次。

在 MySQL 5.0 时，自增锁的范围是语句级。一个语句申请了一个自增值，则自增锁会在语句执行结束后才释放。这种设计会影响并并发度。

在 MySQL 5.1.22 版本中，增加了参数 innodb_autoinc_lock_mode，

  • 设置为 0 时，采用 MySQL 5.0 版本的策略
  • 默认设置为 1，设置为 1 时，
    • 普通 insert 语句，自增锁会在申请后立即释放
    • 类似 insert ... select 的 “无法预先知道插入数量” 的批量插入语句，自增值会在语句执行结束后释放
  • 设置为 2 时，自增锁会在申请后立即释放

为什么类似 insert ... select 的 “无法预先知道插入数量” 的批量插入语句需要额外处理？

类似 insert ... select 的 “无法预先知道插入数量” 的批量插入语句，在执行过程中需要申请多次自增值。

如果自增锁在申请后释放，在语句执行过程中假如有其它语句申请自增值，则批量插入语句将会获取到不连续的自增值。

Session A Session B
INSERT INTO A SELECT * FROM B
申请到自增值 1，插入 (1, A)
申请到自增值 2，插入 (2, B)
INSERT INTO A VALUES (null, X)
申请到自增值 3，插入 (3, X)
申请到自增值 4，插入 (4, C)

Session A 申请到的自增值是 1，2，4

对于这样的语句，假如 binlog 基于语句，则主从复制或通过 binlog 恢复实例时，将会产生不一致的现象。

这是因为基于行的 binlog 无法描述 “语句 1 先执行，语句 2 在语句 1 执行到一半时执行” 这样的情况，它只能简单地先记录语句 1 或先记录语句 2。在这种情况下，如果根据 binlog 中的内容执行，则语句 1 会获取到连续的自增值，产生不一致。

对于这个问题的解决方案是：

  • 将 innodb_autoinc_lock_mode 设置为 1
  • 将 innodb_autoinc_lock_mode 设置为 2，设置 binlog 为基于行

参考

  • MySQL 技术内幕
  • MySQL 实战 45 讲
  • MySQL 是怎样运行的：从根儿上理解 MySQL