我们在使用关系型数据库的时候，主键（PrimaryKey）是一个绕不开的概念。在表中找到唯一记录。在关系型数据库中，我们会选择记录中多个字段的最小子集作为记录在表中的唯一标识[^1]。根据关系数据库中主键的定义，我们可以选择单列作为主键，也可以选择多列作为主键，但是主键必须存在并且在整条记录中是唯一的。最常见的方式当然是使用MySQL默认的自增ID作为主键。尽管使用由其他策略设置的主键是合法的，但这不是一种常见和推荐的做法。图1——MySQL的主键MySQL中默认的AUTO_INCREMENT属性在大多数情况下可以保证主键的连续性。我们可以通过showcreatetable命令查看表定义中AUTO_INCREMENT属性的当前值。当我们向当前表插入数据时，它会使用这个属性的值作为插入记录的主键，每获取一次值，它也会加一。CREATETABLE`trades`(`id`bigint(20)NOTNULLAUTO_INCREMENT,...`created_at`timestampNULLDEFAULTNULL,PRIMARYKEY(`id`),)ENGINE=InnoDBAUTO_INCREMENT=17130DEFAULTCHARSET=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]。SELECTMAX(ai_col)FROMtable_nameFORUPDATE;如果让作者实现AUTO_INCREMENT，一开始也会用到这个方法。但是，虽然这个实现很简单，但是如果用户没有严格按照关系数据库的设计规范，就会出现数据不一致的问题，如下：内存中的AUTO_INCREMENT计数器会被重置为表中的最大值。当我们向表中插入新的交易记录时，我们会重新使用10作为主键，这样主键就不会单调了。插入新交易记录后，执行表中的记录错误地引用了新交易，这实际上是一个严重的错误。然而，这不完全是MySQL的问题。如果我们严格按照关系型数据库的设计规范，使用外键来处理不同表之间的关系，就可以避免上述问题。因为当前的交易记录还有外部引用，外键会禁止删除交易记录，但是大部分内部DBA并不推荐或者禁止使用外键，所以确实存在这个问题的可能。不过在MySQL8.0中，AUTO_INCREMENT计数器的初始化行为发生了变化，每次计数器变化都会写入系统的重做日志（Redolog），并在每个checkpoint[^3]存储在引擎的私有系统表中。在MySQL8.0中，此行为已更改。当前的最大自动增量计数器值在每次更改时写入重做日志，并保存到每个检查点上的引擎专用系统表中。这些更改使当前最大自动递增计数器值在服务器重新启动后保持不变。当MySQL服务重启或崩溃恢复时，它可以从持久化检查点和重做日志中恢复最新的AUTO_INCREMENT计数器，避免了非单调主键，解决了这里提到的问题。并发事务为了提高事务的吞吐量，MySQL可以处理并发执行的多个事务，但是如果同时执行多条插入新记录的SQL语句，可能会造成主键不连续。如下图，事务1向数据库中插入一条id=10的记录，事务2向数据库中插入两条id=11和id=12的记录：图4-并发事务的执行但是如果在最后transaction1由于insert记录中唯一键发生冲突，导致回滚。但是事务2正常提交，没有报错。这时我们会发现当前表中的主键是不连续的，后续新插入的数据将不再使用。10作为记录的主键。图5-主键不连续现象背后的原因也很简单。虽然在获取AUTO_INCREMENT的时候会加锁，但是锁是语句锁。它的目的是保证AUTO_INCREMENT的获取不会造成线程竞争，不是保证MySQLConcatenationofprimarykeysin[^4]。上述行为由InnoDB存储引擎提供的innodb_autoinc_lock_mode配置控制。该配置决定了在获取AUTO_INCREMENT定时器时首先需要获取的锁。这个配置有三种不同的模式，分别是传统模式（Traditional）、连续模式（Consecutive）和交错模式（Interleaved）[^5]，其中MySQL使用连续模式作为默认的锁模式：（1）传统模式innodb_autoinc_lock_mode=0;向包含AUTO_INCREMENT属性的表插入数据时，所有的INSERT语句都会获取表级的AUTO_INCREMENT锁，该锁会在当前语句执行完后释放；(2)连续模式innodb_autoinc_lock_mode=1；INSERT...SELECT,REPLACE...SELECT和LOADDATA等批量插入操作需要获取表级的AUTO_INCREMENT锁，当前语句执行完后释放锁；一个简单的插入语句（提前知道插入多少条记录的语句）只需要获取AUTO_INCREMENT计数器的互斥锁，获取主键后直接释放，无需等待当前语句完成；（3）交叉模式innodb_autoinc_lock_mode=2;所有的insert语句都不需要获取表级的AUTO_INCREMENT锁，但是当多个语句插入的数据行数不确定时，可能会存在分配相同主键的风险；这三种模式都不能解决MySQL自增主键不连续的问题。这个问题的最终解决方案是串行执行包括插入操作在内的所有事务，即使用数据库的最高隔离级别——可序列化（Serialiable）。当然直接修改数据库的隔离级别比较简单粗暴。基于MySQL或其他存储系统实现完全串行插入，也可以保证插入时主键的连续性，但仍然无法避免删除数据造成的不连续性。综上所述，早期MySQL的主键既不单调也不连续。这是当时工程上的一些选择。如果严格按照关系型数据库的设计规范，MySQL的原始设计出现问题的概率是比较低的，只有当被删除的主键被外部系统引用时才会影响数据的一致性，但是今天不同的使用方式增加了出错的可能性，而MySQL在8.0也坚持了AUTO_INCREMENT来避免这个问题。MySQL中的不连续主键是工程设计向性能屈服的又一个例子，牺牲主键的连续性来支持并发插入数据，最终提高MySQL服务的吞吐量。笔者几年前在使用MySQL时遇到过这个问题。我已经通过了这个问题，但当时并没有深究其背后的原因。今天重新理解这个问题背后的设计决策，也是一个非常有趣的过程。在这里简单总结一下本文的内容，回到今天的问题——为什么MySQL的自增主键不单调不连续：MySQL5.7之前，AUTO_INCREMENT计数器是存放在内存中的。设置，删除记录重启后可能会出现重复的主键。8.0版本通过使用redolog来保证主键的单调性，解决了这个问题；MySQL在插入数据获取AUTO_INCREMENT时不会使用事务锁，而是会使用互斥锁，并发插入事务可能会导致某些字段冲突导致插入失败。为了保证主键的连续性，insert语句需要串行执行；最后，我们会看一些相对开放的相关问题。有兴趣的读者可以仔细思考提出以下问题：MyISAM等存储引擎是如何存储AUTO_INCREMENT计数器的？MySQL中的auto_increment_increment和auto_increment_offset是干什么用的？
