Smarticen Notes

自增主键为什么不连续

6 min read

自增主键是递增的，但并不是连续的。了解一下什么时候自增主键会出现“空洞”。

创建一个表t，id是自增主键字段，c是唯一索引。

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;

自增值保存在哪？

在表中插入一条语句insert into t values(null, 1, 1);，再执行show create table就可以看到AUTO_INCREMENT=2，表示下一次插入数据时，如果需要生成自动自增值，会生成id=2。

表的结构定义存储在后缀名为.frm的文件中，但是并不会保存自增值。

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

  • MyISAM引擎的自增值保存在数据文件中。
  • InnoDB引擎的自增值与版本有关
    • 5.7及之前的版本，是保存在了内存当中的没有持久化。每次重启后，第一次打开表的时候，都会取找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。
    • 8.0版本，将自增值的变更记录在了redo log中，重启的时候以来redo log回复重启之前的值。

自增值修改机制

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

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

如果要插入的值是X，当前自增值是Y

  1. 如果X小于Y，那么自增值不变。
  2. 如果X大于等于Y，那么自增值变为新的自增值。算法为从 auto_increment_offset 开始，以 auto_increment_increment 为步长，持续叠加，直到找到第一个大于 X 的值，作为新的自增值。

自增值的修改时机

在执行insert插入数据行的时候，会先获取当前自增值，并将其加1，然后继续执行插入操作。 也就是说，不管最后的插入成功与否，自增值都已经加1了。这样就出现了主键不连续的情况。

还有如果事务中执行了插入操作，但是最后回滚了，那么这些插入操作造成的自增值却是不会回滚的。

所以说，insert语句执行但没有成功就会造成自增值不连续。

自增锁的优化

参数innodb_autoinc_lock_mode，默认值是1。

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

insert … select之所以采用语句锁，是为了binlog_format=statement的时候，从库执行binlog的时候能够与主库数据保持一致。

普通的insert语句能够知道一次性插入多少数据，可以直接批量申请自增值，所以不需要使用语句级锁，换句话说，是因为不知道要插入多少行，所以insert … select需要使用语句级锁。

将binlog_format设置为row可以解决这个问题。

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

  1. 语句执行过程中，第一次申请自增 id，会分配 1 个；
  2. 1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；
  3. 2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；
  4. 依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。 所以在批量插入数据的情况下，可能申请到的自增值并没有用到，也会造成自增值不连续的情况。

Graph View