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

自增主键可以让主键尽量地保持递增顺序插入，避免了也分裂，因此索引更紧凑。

但是必须说明，自增主键不能保证连续递增。

那什么情况下自增主键会出现“空洞”？

创建一个表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引擎的自增值，其实是保存在了内存里，并且到了MySQL 8.0版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”，具体情况是：

1.     MySQL5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。 意思就是，MySQL重启可能会修改一个表的AUTO_INCREMENT的值。
2. MySQL 8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。

以上是MySQL对自增值的保存策略。下面再看看自增值修改机制。

自增值修改机制

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

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

假设，某次要插入的值是X，当前值是Y：

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

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

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

当两个参数都是默认值的时候，新的自增值生成逻辑很简单：就是：

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

自增值的修改时机

假设，表t已经有了（1,1,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 error，语句返回。

自增值改成3，是在真正插入数据的操作之前。插入的时候唯一键冲突，但也没有将自增值再改回去。这就造成了不连续。

所以，唯一键冲突是导致自增主键id不连续的第一种原因。

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

事务回滚了，但是唯一键没有回滚，MySQL为了提升性能。

自增锁的优化

自增id锁并不是一个事务锁，而是每次申请完就马上释放，一遍允许别的事务再申请。但是MySQL 5.1版本之前并不是这样。

MySQL 5.0的时候，自增锁的范围是语句级别。就是说，如果一个语句申请了一个表自增锁，这个锁会等语句执行结束以后才释放。显然，这样设计会影响并发。

MySQL 5.1.22版本引入了一个新的策略，新增参数innodb_automic_lock_mode，默认值是1。

1. 参数的值为0时，表示采用之前MySQL 5.0版本的策略；
2. 设置为1的时候：

            2.1 普通insert语句，自增锁在申请之后马上释放；

            2.2 类似insert ... select 这样的批量插入数据的语句，自增锁还是要等语句结束后才释放；

     3. 设置为2时，所有的申请自增主键的动作都是申请后就释放锁。

默认情况下，insert ... select要使用语句级的锁？为什么这个参数的默认值不是2?

这么做还是为了数据一致性（可能会导致主从数据不一致，binlog_format为statement的时候）。

因此，生产上，尤其是insert ... select这种批量插入数据的场景时，从并发插入数据性能的角度考虑，建议这样设置：innodb_autoinc_lock_mode=2，并且binlog_format=row，这样做既能提升并发性，有不会出现数据一致性问题。

注意，这里说的批量插入数据，包含的语句类型是insert ... select 、replace ... select 和load data语句。

在普通的insert语句里面包含多个value值的情况下，即使innodb_automic_lock_mode设置为1，也不会等语句执行完才释放锁。因为这类语句在申请自增id的时候，是可以精确计算出需要多少个id的，然后一次性申请，申请完后锁就可以释放了。

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

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

这是主键id出现自增id不连续的第三种原因。

 

上一篇：38 | 都说InnoDB好，那还要不要使用Memory引擎

下一篇：40 | insert语句的锁为什么这么多？