为什么 MySQL 的自增主键不单调也不连续( 二 ) _小知识

并发事务为了提高事务的吞吐量，MySQL 可以处理并发执行的多个事务，但是如果并发执行多个插入新记录的 SQL 语句，可能会导致主键的不连续。如下图所示，事务 1 向数据库中插入 id = 10 的记录，事务 2 向数据库中插入 id = 11 和 id = 12 的两条记录：

文章插图

图 4 - 并发事务的执行
不过如果在最后事务 1 由于插入的记录发生了唯一键冲突导致了回滚，而事务 2 没有发生错误而正常提交，在这时我们会发现当前表中的主键出现了不连续的现象，后续新插入的数据也不再会使用 10 作为记录的主键。

文章插图

图 5 - 不连续的主键
这个现象背后的原因也很简单，虽然在获取 AUTO_INCREMENT 时会加锁，但是该锁是语句锁，它的目的是保证 AUTO_INCREMENT 的获取不会导致线程竞争，而不是保证 MySQL 中主键的连续4 。
上述行为是由 InnoDB 存储引擎提供的 innodb_autoinc_lock_mode 配置控制的，该配置决定了获取 AUTO_INCREMENT 计时器时需要先得到的锁，该配置存在三种不同的模式，分别是传统模式（Traditional）、连续模式（Consecutive）和交叉模式（Interleaved）5，其中 MySQL 使用连续模式作为默认的锁模式：

传统模式 innodb_autoinc_lock_mode = 0；在包含 AUTO_INCREMENT 属性的表中插入数据时，所有的 INSERT 语句都会获取表级别的 AUTO_INCREMENT 锁，该锁会在当前语句执行后释放；
连续模式 innodb_autoinc_lock_mode = 1；INSERT ... SELECT、REPLACE ... SELECT 以及 LOAD DATA 等批量的插入操作需要获取表级别的 AUTO_INCREMENT 锁，该锁会在当前语句执行后释放；简单的插入语句（预先知道插入多少条记录的语句）只需要获取获取 AUTO_INCREMENT 计数器的互斥锁并在获取主键后直接释放，不需要等待当前语句执行完成；
交叉模式 innodb_autoinc_lock_mode = 2；所有的插入语句都不需要获取表级别的 AUTO_INCREMENT 锁，但是当多个语句插入的数据行数不确定时，可能存在分配相同主键的风险；

这三种模式都不能解决 MySQL 自增主键不连续的问题，想要解决这个问题的终极方案是串行执行所有包含插入操作的事务，也就是使用数据库的最高隔离级别 —— 可串行化（Serialiable）。当然直接修改数据库的隔离级别相对来说有些简单粗暴，基于 MySQL 或者其他存储系统实现完全串行的插入也可以保证主键在插入时的连续，但是仍然不能避免删除数据导致的不连续。
总结早期 MySQL 的主键既不是单调的，也不是连续的，这些都是在当时工程上做出的一些选择，如果严格地按照关系型数据库的设计规范，MySQL 最初的设计造成问题的概率也比较低，只有当被删除的主键被外部系统引用时才会影响数据的一致性，但是今天使用方式的不同却增加出错的可能性，而 MySQL 也在 8.0 中持久化了 AUTO_INCREMENT 以避免该问题的出现。
MySQL 中不连续的主键又是一个工程设计向性能低头的例子，牺牲主键的连续性来支持数据的并发插入，最终提高了 MySQL 服务的吞吐量，作者在几年前刚刚使用 MySQL 时就遇到过这个问题，但是当时并没有深究背后的原因，今天重新理解该问题背后的设计决策也是个非常有趣的过程。我们在这里简单总结一下本文的内容，重新回到今天的问题 — 为什么 MySQL 的自增主键不单调也不连续：

MySQL 5.7 版本之前在内存中存储 AUTO_INCREMENT 计数器，实例重启后会根据表中的数据重新设置，在删除记录后重启就可能出现重复的主键，该问题在 8.0 版本使用重做日志解决，保证了主键的单调性；
MySQL 插入数据获取 AUTO_INCREMENT 时不会使用事务锁，而是会使用互斥锁，并发的插入事务可能出现部分字段冲突导致插入失败，想要保证主键的连续需要串行地执行插入语句；

到最后，我们还是来看一些比较开放的相关问题，有兴趣的读者可以仔细思考一下下面的问题：