MySQL的自增主键是连续自增吗？ _MySQL

在普通的 insert 语句里面包含多个 value 值的情况下，即使 innodb_autoinc_lock_mode 设置为 1，也不会等语句执行完成才释放锁。因为这类语句在申请自增 id 的时候，是可以精确计算出需要多少个 id 的，然后一次性申请，申请完成后锁就可以释放了。从开始接触MySQL，我们就知道在设计主键时，要设置为自增主键，使用自增主键有以下几个优点：

效率高：使用自增主键可以避免频繁生成主键值的操作，节省了数据库的资源，提高了查询效率。
索引优化：自增主键一般是整数类型，可以方便地使用B-tree索引来加速数据查询。
数据唯一性：自增主键可以保证数据的唯一性，防止重复插入数据。
方便性：使用自增主键可以方便地进行更新、删除和查询操作，不需要复杂的联合主键或其他索引操作。

我们在使用自增主键统计数据库的数据量时，也会经常使用id的最大值与最小值之间的差值作为数据库当前已有数据的条数，但是这种统计方式是否正确？是否存在误差？
笔者先给出本文结论：自增主键可以保持主键递增顺序插入，避免页分裂，索引更为紧凑，但是自增主键并不能保证连续递增，即出现空洞。
但是问题再次出现，为什么明明是自增主键，为什么不能保证连续递增？为什么会出现空洞？
在本文中，我们使用如下的数据库配置：

文章插图
1 自增值的存储在如上的空表 t 里面执行 insert into t values(null, 1, 1); 插入一行数据，再执行 show create table 命令，就可以看到如下图所示的结果：

文章插图
表定义里面出现了一个 AUTO_INCREMENT=2，表示下一次插入数据时，如果需要自动生成自增值，会生成 id=2 。
不同的引擎对于自增至的保存策略不同：

MyISAM 引擎的自增值保存在数据文件中。
InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”，具体情况是：

在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。举例来说，如果一个表当前数据行里最大的id 是10，AUTO_INCREMENT=11 。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11 。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10 。也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。
在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

2 自增值修改机制在 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 。
但是在一些场景下，使用的就不全是默认值。比如，双 M 的主备结构里要求双写的时候，我们就可能会设置成 auto_increment_increment=2 ，让一个库的自增 id 都是奇数，另一个库的自增 id 都是偶数，避免两个库生成的主键发生冲突。