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

当我们在使用关系型数据库时，主键（Primary Key）是无法避开的概念，主键的作用就是充当记录的标识符，我们能够通过标识符在一张表中定位到唯一的记录，作者在为什么总是需要无意义的 ID 曾经介绍过为什么不应该使用有意义的字段来充当唯一标识符，感兴趣的读者可以了解一下。
在关系型数据库中，我们会选择记录中多个字段的最小子集作为该记录在表中的唯一标识符1，根据关系型数据库对主键的定义，我们既可以选择单个列作为主键，也可以选择多个列作为主键，但是主键在整个记录中必须存在并且唯一。最常见的方式当然是使用 MySQL 默认的自增 ID 作为主键，虽然使用其他策略设置的主键也是合法的，但是不是通用的以及推荐的做法。

文章插图

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

CREATE TABLE `trades` (`id` bigint(20) NOT NULL AUTO_INCREMENT,...`created_at` timestamp NULL DEFAULT NULL,PRIMARY KEY (`id`),) ENGINE=InnoDB AUTO_INCREMENT=17130 DEFAULT CHARSET=utf8mb4

在很多开发者的认知中，MySQL 的主键都应该是单调递增的，但是在我们与 MySQL 打交道的过程中会遇到两个问题，首先是记录的主键并不连续，其次是可能会创建多个主键相同的记录，我们将从以下的两个角度回答 MySQL 不单调和不连续的原因：

较早版本的 MySQL 将 AUTO_INCREMENT 存储在内存中，实例重启后会根据表中的数据重新设置该值；
获取 AUTO_INCREMENT 时不会使用事务锁，并发的插入事务可能出现部分字段冲突导致插入失败；

需要注意的是，我们在这篇文章中讨论的是 MySQL 中最常见的 InnoDB 存储引擎，MyISAM 等其他引擎提供的 AUTO_INCREMENT 实现原理不在本文的讨论范围中。
删除记录AUTO_INCREMENT 属性虽然在 MySQL 中十分常见，但是在较早的 MySQL 版本中，它的实现还比较简陋，InnoDB 引擎会在内存中存储一个整数表示下一个被分配到的 ID，当客户端向表中插入数据时会获取 AUTO_INCREMENT 值并将其加一。

文章插图

图 2 - AUTO_INCREMENT 的使用
因为该值存储在内存中，所以在每次 MySQL 实例重新启动后，当客户端第一次向 table_name 表中插入记录时，MySQL 会使用如下所示的 SQL 语句查找当前表中 id 的最大值，将其加一后作为待插入记录的主键，并作为当前表中 AUTO_INCREMENT 计数器的初始值2 。
SELECT MAX(ai_col) FROM table_name FOR UPDATE;如果让作者实现 AUTO_INCREMENT，在最开始也会使用这种方法。不过这种实现虽然非常简单，但是如果使用者不严格遵循关系型数据库的设计规范，就会出现如下所示的数据不一致的问题：

文章插图

图 3 - 5.7 版本之前的 AUTO_INCMRENT
因为重启了 MySQL 的实例，所以内存中的 AUTO_INCREMENT 计数器会被重置成表中的最大值，当我们再向表中插入新的 trades 记录时会重新使用 10 作为主键，主键也就不是单调的了。在新的 trades 记录插入之后，executions 表中的记录就错误的引用了新的 trades，这其实是一个比较严重的错误。
然而这也不完全是 MySQL 的问题，如果我们严格遵循关系型数据库的设计规范，使用外键处理不同表之间的联系，就可以避免上述问题，因为当前 trades 记录仍然有外部的引用，所以外键会禁止 trades 记录的删除，不过多数公司内部的 DBA 都不推荐或者禁止使用外键，所以确实存在出现这种问题的可能。
然而在 MySQL 8.0 中，AUTO_INCREMENT 计数器的初始化行为发生了改变，每次计数器的变化都会写入到系统的重做日志（Redo log）并在每个检查点存储在引擎私有的系统表中3 。

In MySQL 8.0, this behavior is changed. The current maximum auto-increment counter value is written to the redo log each time it changes and is saved to an engine-private system table on each checkpoint. These changes make the current maximum auto-increment counter value persistent across server restarts.

当 MySQL 服务被重启或者处于崩溃恢复时，它可以从持久化的检查点和重做日志中恢复出最新的 AUTO_INCREMENT 计数器，避免出现不单调的主键也解决了这里提到的问题。