在使用关系型数据库时，主键是一个不可避免的概念。主键的作用是作为记录的标识符。我们可以通过标识符在表中找到唯一的记录。

在关系型数据库中，我们将选择记录中多个字段的最小子集作为表中记录的唯一标识符[^1]。根据关系型数据库中对主键的定义，可以选择单列作为主键，也可以选择多列作为主键，但是主键在整个记录中必须存在并且唯一。

当然，最常见的方法是使用MySQL的默认自增id作为主键。尽管使用其他策略设置的主键是合法的，但这不是一种通用或推荐的方法。

MySQL中默认的AUTO_INCREMENT属性在大多数情况下可以保证主键的连续性。我们可以通过show create table命令在表定义中看到AUTO_INCREMENT属性的当前值。当我们将数据插入到当前表中时，它会使用该属性的值作为插入记录的主键，而每次获取该值也都会将它加一。

在很多开发人员的认识中，MySQL的主键应该是单调递增的，在实际使用MySQL的过程中，我们会遇到两个问题：一是记录的主键不连续；二是可能会创建多个主键相同的记录。

我们将从以下两个角度回答MySQL不单调、不连续的原因：

需要注意的是，在本文中，我们讨论的是mysql中最常见的InnoDB存储引擎，MyISAM等引擎提供的AUTO_INCREMENT 实现原理在本文中没有涉及。

删除记录

虽然AUTO_INCREMENT属性在mysql中很常见，但在mysql的早期版本中，它的实现相对简单。InnoDB引擎会在内存中存储一个整数来表示下一个准备分配的ID，当客户端将数据插入到表中时，它会获取AUTO_INCREMENT值的并加1。

由于该值存储在内存中，当MySQL实例重启后客户端第一次在 table_name 中插入记录时，MySQL会使用下面的SQL语句查找当前表中ID的最大值，将其加一后作为待插入记录的主键，并将其作为当前表中AUTO_INCREMENT计数器的初始值。

SELECT MAX(ai_col) FROM table_name FOR UPDATE;

虽然这个实现非常简单，但是如果用户使用时不严格遵循关系型数据库的设计规范，就会出现如下所示的数据不一致问题：

重启了 MySQL 的实例之后，内存中的 AUTO_INCREMENT 计数器被重置为表中的最大值，当我们再向表中插入新的 trades 记录时会重新使用 10 作为主键，主键也就不是单调的了。在新的 trades 记录插入之后，executions 表中的记录就错误的引用了新的trades，这其实是一个比较严重的错误。

然而这也不完全是 MySQL 的问题，如果我们严格遵循关系型数据库的设计规范，使用外键处理不同表之间的联系，就可以避免上述问题，因为当前 trades 记录仍然有外部的引用，所以外键会禁止 trades 记录的删除，不过多数公司内部的 DBA 都不推荐或者禁止使用外键，所以确实存在出现这种问题的可能。

然而在 MySQL 8.0 中，AUTO_INCREMENT 计数器的初始化行为发生了改变，每次计数器的变化都会写入到系统的重做日志(Redo log)并在每个检查点存储在引擎私有的系统表中。

MySQL服务在重启或崩溃恢复过程中，可以从持久性检查点和重做日志中恢复最新的AUTO_INCREMENT计数器，避免主键不单调的问题，也解决了这里提到的问题。

并发事务

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

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

这种现象背后的出现原因也很简单，虽然在获取 AUTO_INCREMENT 时会加锁，但这个锁是一个语句锁。其目的是为了保证对 AUTO_INCREMENT 的获取不会导致线程竞争，而不是保证MySQL主键的连续性。

上面的行为是由 InnoDB 存储引擎提供的 innodb_autoinc_lock_mode 配置控制的，该配置决定了获取 AUTO_INCREMENT 计时器时需要先得到的锁，该配置存在三种不同的模式，分别是传统模式(Traditional)、连续模式(Consecutive)和交叉模式(Interleaved)[^5]，其中 MySQL 使用连续模式作为默认的锁模式：

很遗憾使用这三种模式都不可以解决MySQL主键自动递增不连续的问题。这个问题的最终解决方案是串行执行所有事务，包括插入操作，也就是说，使用数据库的最高隔离级别-可串行化。

当然，直接修改数据库的隔离级别是非常的简单和粗糙。基于 MySQL 或者其他存储系统实现完全串行的插入也可以保证主键在插入时的连续，但是还是不能避免删除数据时导致的不连续。

总结

早期MySQL的主键不是单调的，也不是连续的。这些都是当时在设计系统项目中做出的选择。如果严格按照关系型数据库的设计规范，MySQL的初始设计或许只有很低的出错概率。只有当删除的主键被外部系统引用时，才会影响数据的一致性。然而，现在不同的使用方式增加了出错的可能性。 MySQL 也在 8.0 中持久化了 AUTO_INCREMENT 以避免该问题的出现。

MySQL中的不连续主键是工程设计向性能低头的一个例子，它牺牲了主键的连续性来支持并发数据插入，最终提高了MySQL服务的吞吐量。

几年前刚使用过MySQL的时候我遇到了这个问题，但我没有深入探究其背后的原因。当理解这个问题背后的设计决策后也是一个非常有趣的过程。

也许从来就没有过没有缺陷的软件系统。