文章概述

美团的面试过程中，数据库相关问题经常出现。这篇文章分享了一位读者在美团面试时遇到的关于MySQL自增主键的问题，旨在帮助更多的读者理解自增主键的特性及其在实际应用中的表现。

自增主键的设计主要是为了让聚集索引在插入时尽量保持按照顺序递增，从而优化随机查询的效率。然而，实际操作中，MySQL的自增主键并不能保证每次插入都产生连续的值。接下来，我将通过一个具体的例子来探讨这一问题，首先来看一下如何创建一个表。

自增值存储位置解析

在执行 insert into test_pk values(null, 1, 1) 插入数据后，可以使用 show create table 命令查看表的结构定义。该结构存在于名为 .frm 的本地文件中，可以在MySQL的安装目录下的data文件夹中找到。

从表结构定义中，可以看到 AUTO_INCREMENT=2，这意味着下一次插入数据时，如果需要生成自增值，将会生成 id=2。需要注意的是，自增值并不存储在这个表的结构文件中，而是根据不同的存储引擎有不同的保存策略：

  1. MyISAM引擎：自增值存储在数据文件中。
  2. InnoDB引擎：自增值实际上存储在内存中，而不是持久化存储。每次打开表时，都会查找自增值的最大值 max(id) 并以 max(id)+1作为当前的自增值。

例如，如果当前表中最大的 id 是1，且 AUTO_INCREMENT=2，一旦删除了 id=1 的记录， AUTO_INCREMENT值仍然会保持为2。不过，如果重启MySQL实例，表的 AUTO_INCREMENT值可能会变为1。换言之，MySQL的重启可能会导致表的 AUTO_INCREMENT值发生变化。

自增值不连续的场景

理解了自增值的存储机制后，我们再来看自增值不连续的具体场景。

场景一：自增值不连续的原因

在MySQL中，如果字段 id 被定义为 AUTO_INCREMENT，插入数据时自增值的行为如下：

  • 如果插入时没有指定 id 的值（如指定为0或null），则会将当前的 AUTO_INCREMENT值赋给 id；
  • 如果指定了具体的值，则直接使用该值。

例如，假设要插入的值为 insert_num，当前自增值为 autoIncrement_num，则自增值的变更规则如下：

  • 如果 insert_num < autoIncrement_num，则自增值不变；
  • 如果 insert_num >= autoIncrement_num，则自增值需要更新为新的值。

这种情况下，即便自增初始值和步长都为1，自增主键ID依然可能不连续。

场景二：唯一键冲突

假设我们向表中插入一条 (null, 1, 1) 的记录，生成的主键为1，此时 AUTO_INCREMENT=2。若再执行插入 (null, 1, 1)，将报错“Duplicate entry”，尽管插入失败， AUTO_INCREMENT却仍然从2增加到了3。这是因为自增值修改是在执行插入数据之前进行的。

场景三：事务回滚

在数据库中进行事务时，即使插入操作发生回滚，但自增值依旧会继续增加。这意味着即便操作未成功，自增值也不会回退。

场景四：批量插入

对于批量插入数据的语句，MySQL会使用一种批量申请自增ID的策略，可能会出现ID的不连续性。例如，在执行 insert … select 或 load data 这种语句时，MySQL不知道需要多少个ID，因此采用逐次申请的策略。

结论

自增主键的设计虽然旨在确保主键的递增性，但在多个场景下，例如自增初始值和步长设置、唯一键冲突、事务回滚以及批量插入时，依然存在不连续的可能性。了解这些机制对于优化数据库应用的设计与性能至关重要。