尧图网页设计 
一、从一个面试问题说起不知道你有没有遇到过这样的场景你正信心满满地面试一个后端开发岗位前面几轮关于 JVM、Redis、分布式锁的问题都回答得还算流畅。轮到数据库环节面试官看似不经意地问了一句你们项目里主键一般怎么生成你回答MySQL 自增主键比较多。面试官点点头紧接着追问那你知道 MySQL 的自增主键为什么经常不是连续的吗很多同学听到这个问题会愣一下。因为在直觉里AUTO_INCREMENT既然号称自增那么 1、2、3、4、5 一路排下去似乎是天经地义的事。但实际生产中你会发现表里的主键经常出现 1、2、4、7、9 这样的空洞甚至隔几个数就跳一次号。这到底是 MySQL 的 Bug还是它刻意为之的设计这篇文章我们就从现象到原理从源码到实验把自增主键为什么不连续这个问题彻底讲透。全文会覆盖以下几个方面自增主键的基础机制与内部计数器自增锁与innodb_autoinc_lock_mode三种模式造成主键不连续的六大核心原因不同插入方式的底层差异MySQL 8.0 对自增值持久化的改进可以亲手复现空洞的实验步骤自增值耗尽、面试追问与生产实践读完本文你不仅能从容应对这个问题还能从自增值分配这条主线出发串起事务、锁、回滚、并发控制、持久化等一系列 MySQL 核心知识。二、自增主键的基础知识2.1 什么是 AUTO_INCREMENT在 MySQL 中给某一列加上AUTO_INCREMENT属性后当插入一行数据却没有显式指定该列的值时MySQL 会自动为它分配一个递增的数值。最典型的用法是把它加在整数类型的主键上sqlCREATE TABLE t_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;上面的建表语句中id列就是自增主键。执行INSERT INTO t_user(name) VALUES (张三)时我们并没有给 id 赋值MySQL 会自动生成一个值。这个值由表内部维护的一个自增计数器决定而不是简单地在当前最大值基础上加一这么简单。需要注意的是AUTO_INCREMENT 只能用于整数类型的列包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。如果你试图把它用在CHAR、VARCHAR、DECIMAL或者浮点类型上MySQL 会直接报错。这个限制本身就提醒我们自增值是一个计数语义而不是业务编号语义。一个很重要的认知是自增主键保证的是唯一且单调递增的一般趋势它从来没有承诺过一定连续。官方文档对 AUTO_INCREMENT 的描述里只强调了自动生成值的唯一性并没有任何一条保证生成的值是连续的。理解了这一点本文后续的所有不连续现象就都顺理成章了。2.2 为什么大家都爱用自增主键在深入不连续问题之前我们先搞清楚一个问题为什么自增主键在业务中这么流行答案主要来自 InnoDB 存储引擎的物理结构。InnoDB 默认使用聚簇索引来组织数据。所谓聚簇索引就是数据行本身和主键索引存储在一起叶子节点里直接存放整行记录。这意味着如果主键是顺序递增的新插入的数据会追加到 B 树的最右侧叶子页。只有当右侧叶子页写满时才会发生一次新的页分配。页分裂的概率极低写入效率高磁盘空间利用率也高。反过来如果主键是随机值比如 UUID那么每次插入都可能落在 B 树的中间某个位置。一旦目标叶子页已经写满就会触发页分裂。页分裂会带来额外的数据搬运、索引节点调整还可能留下大量页内空洞最终导致写入性能下降、碎片增多。所以自增主键之所以流行根本原因是它和聚簇索引的追加写特性天然契合。它是为了写入性能和索引紧凑性服务的而不是为了号码连续服务的。这也解释了为什么数据库设计者宁可让主键出现空洞也要优先保证分配机制的简单和高效。2.3 两个容易混淆的概念自增列与自增计数器很多同学觉得自增主键就是上一个值加一这个理解有两个隐藏的误区第一个误区是把下一个值简单地等同于max(id) 1。实际上MySQL 为每张带自增列的表维护了一个独立的计数器这个计数器的当前值并不总是等于表中的最大 id。计数器有自己的生命周期和持久化策略它可能比 max(id) 大也可能在极端情况下如 MySQL 8.0 之前的重启出现回退。第二个误区是把自增列的值和物理行号混为一谈。自增列只是一个普通的数值列只是因为带了 AUTO_INCREMENT 属性MySQL 会在你没有指定值时帮你填。它并不代表数据在磁盘上的物理顺序更不承担任何行号必须连续的责任。在 InnoDB 内部自增计数器被保存在数据字典结构dict_table_t的autoinc字段中。每次需要生成自增值时InnoDB 会读取并推进这个计数器。计数器在内存中的推进非常快但它和实际写入磁盘的数据并不完全同步这正是诸多跳号现象的根源之一。2.4 LAST_INSERT_ID() 与自增值的可见性在讲计数器之前还需要掌握一个非常实用的函数LAST_INSERT_ID()。当你执行完一条插入语句后可以通过SELECT LAST_INSERT_ID()拿到本次连接刚刚生成的自增值。sqlINSERT INTO t_user(name) VALUES (李四); SELECT LAST_INSERT_ID();这里有几个值得注意的细节LAST_INSERT_ID()是会话级的不同连接之间互相隔离。A 连接拿到的是 A 自己插入生成的值不会被 B 连接的插入干扰。如果一条语句一次插入了多行LAST_INSERT_ID()返回的是第一条记录被分配的自增值而不是最后一条。例如一次插入 3 行生成 10、11、12函数返回 10。如果插入失败或者被回滚已经生成的自增值不会退回计数器。即使你拿不到结果这个号也已经消耗掉了。通过 JDBC 等驱动执行插入后调用getGeneratedKeys()底层其实就是向 MySQL 协议发起LAST_INSERT_ID请求。理解LAST_INSERT_ID()的会话隔离性有助于后面理解为什么并发插入时 A 和 B 拿到的号可能是交错的但各自又能准确地取得自己的值。这个特性是连续和唯一能够同时被权衡处理的重要前提。三、自增值的生成机制3.1 自增计数器如何推进要理解不连续必须先理解连续是怎么来的。MySQL 中自增值的分配并不直接发生在 SQL 层的INSERT解析之后而是发生在 InnoDB 存储引擎内部。当一个插入请求进入 InnoDB 时大致会经历这样的流程SQL 层解析插入语句判断哪些行需要自动生成主键。InnoDB 根据插入类型向自增计数器申请一段连续的值。根据innodb_autoinc_lock_mode的配置选择是否加自增锁、锁的粒度如何。计数器向前推进申请到的值被分配给各条待插入记录。记录写入聚簇索引事务提交或回滚。从这个流程可以清楚地看到自增值的分配发生在写入之前的申请阶段而不是写入成功之后的结算阶段。也就是说一个自增值一旦从计数器里被拿走不管这条记录最终是否真的成功落库它都不会再被还回去。这是自增值不连续的第一个也是最根本的机制基础。我们可以把计数器想象成一个只能前进、不能后退的发号机。你今天去银行取号排队拿到的号是 57 号。即使你拿完号之后突然有事走了没有真正办理业务也不会有人把你的 57 号回收再发给下一个人。下一个人拿到的仍然是 58 号。数据库的自增值分配本质上就是这样一个发号逻辑。3.2 自增锁AUTO-INC Lock在并发场景下如果多个事务同时申请自增值计数器就必须加锁保护否则会出现两个事务拿到同一个号的情况。InnoDB 为此设计了一种特殊的锁叫AUTO-INC 锁也叫自增锁。自增锁有几个非常鲜明的特点它是一种表级锁而不是行锁。加锁对象是整张表而不是某一行。它的生命周期很短通常在申请完自增值之后就会释放而不是像普通行锁那样直到事务结束才释放。它存在的核心目的是为了保证一次插入语句生成的自增值是连续的以及兼容基于语句的复制。为什么要引入表级锁考虑这样一种情况事务 A 执行一条普通插入生成 id10事务 B 几乎同时执行插入生成 id11。对于实际业务来说这完全没问题因为主键 10 和 11 谁先拿到并不重要。但对于基于语句的主从复制来说问题就大了。在 statement 格式的 binlog 中主库执行的每条 SQL 会被原样记录从库再原样执行一遍。如果主库上两条插入语句并发执行它们的自增值分配顺序是 10、11但从库是单线程回放执行顺序可能变成先 11、后 10。这样主从两边的数据就错位了。因此在早期版本中为了保证 statement 复制的正确性自增锁必须把并发插入串行化让每次插入拿到的号在语句级别是连续的。3.3 innodb_autoinc_lock_mode 三种模式随着 MySQL 的发展尤其是基于行复制ROW 格式 binlog成为主流之后语句级连续的必要性下降了。为了让并发插入性能更好MySQL 引入了innodb_autoinc_lock_mode参数。它有三种取值取值名称主要行为优点缺点0传统锁定模式所有插入都使用 AUTO-INC 表级锁语句执行完才释放复制最安全兼容 statement binlog并发性能差1连续锁定模式简单插入不加表锁用轻量级互斥量批量插入仍加表级锁兼顾性能与复制安全批量插入仍可能阻塞2交错锁定模式所有插入都不加 AUTO-INC 表锁自增值可能交错并发性能最好不兼容 statement 复制下的不连续场景这里需要对简单插入和批量插入做一下区分。MySQL 官方把插入分成三类简单插入Simple inserts插入前能够预先确定行数的语句比如单行INSERT INTO ... VALUES (...)。这种语句生成的自增值个数是明确的。批量插入Bulk inserts插入前无法预先确定行数的语句比如INSERT INTO ... SELECT ...、LOAD DATA。这类语句可能要插入很多行且行数事先未知。混合模式插入Mixed-mode inserts一条语句中部分行指定了自增列的值部分行没有指定。比如INSERT INTO t(id, name) VALUES (1, a), (NULL, b), (NULL, c)。在innodb_autoinc_lock_mode1的连续锁定模式下对于简单插入行数已知MySQL 可以预先用非常轻量的互斥量一次性分配足够数量的自增值不需要加表级 AUTO-INC 锁。对于批量插入因为行数不确定为了保证语句内部的连续性和复制安全仍然会使用表级 AUTO-INC 锁并且锁会持续到语句执行结束。对于混合模式插入由于部分行指定了值分配逻辑更复杂MySQL 的处理也要更谨慎。在innodb_autoinc_lock_mode2的交错锁定模式下所有插入都不再用表级锁多个事务的自增值可以互相交错。举例来说事务 A 一次插入 3 行事务 B 一次插入 2 行最终分配结果可能是 A 拿到 1、3、5B 拿到 2、4。这在 ROW 格式 binlog 下是安全的因为从库回放的是具体行数据不依赖自增值的语句级连续性。不同 MySQL 版本的默认值不一样MySQL 5.7 及更早版本默认innodb_autoinc_lock_mode1MySQL 8.0 默认值调整为 2。主从复制的 binlog 格式也相应推荐使用 ROW 格式。3.4 预分配与预留机制除了锁模式自增值的预分配也是造成跳号的重要机制。在innodb_autoinc_lock_mode1下对于批量插入InnoDB 不知道要插入多少行于是会按照一定策略一次向计数器申请一段值。比如它可能一次性申请 8 个号然后从这段号里依次分配给实际插入的行。如果最终只插入了 5 行那么剩下 3 个号就被浪费掉了。这种浪费在LOAD DATA导入大量数据时尤其明显。假设 InnoDB 每次预申请 8 个号而你的数据行数不是 8 的整数倍那么每一批的最后都会有若干个号被空出来。数据量越大累积起来的空洞就越多。预分配机制体现了 MySQL 的一个核心权衡用号码的浪费换取并发和批量写入的效率。与其在每插入一行时都去争抢一次计数器锁不如一次申请一批让后续的行在无锁或少锁的情况下快速写入。号码的连续性和写入的吞吐量在这里形成了一对取舍。四、导致自增主键不连续的六大核心原因铺垫了这么多接下来进入本文最核心的部分。我们把自增主键不连续的原因归纳为六大类每一类都从为什么讲到怎么复现。这六类原因分别是事务回滚导致的自增值消耗DELETE 删除数据留下的空洞主键或唯一键冲突INSERT ... ON DUPLICATE KEY UPDATE 的多余消耗批量插入的预分配浪费MySQL 8.0 之前服务重启导致的自增值回退4.1 原因一事务回滚这是最常见、也是面试中最常被问到的一个原因。请看下面这个例子sql-- 假设当前表里 max(id) 9 START TRANSACTION; INSERT INTO t_user(name) VALUES (王五); -- 生成了 id 10 ROLLBACK;执行完 ROLLBACK 后我们再查询这张表会发现 id 为 10 的记录并不存在。但不妨接着执行下一条插入sqlINSERT INTO t_user(name) VALUES (赵六); -- 生成 id 11 SELECT * FROM t_user ORDER BY id DESC LIMIT 3;你会发现赵六拿到的是 11而不是 10。也就是说虽然王五那行被回滚了但它占用的 10 号没有被回收。原因就是前面反复强调的自增值是在事务执行过程中从计数器里申请走的回滚只会撤销数据行不会撤销计数器的推进。这里还可以再深挖一层。InnoDB 并不会为回滚后归还自增值设计一套复杂的回收机制。如果真的归还就需要考虑并发事务可能已经按 10、11、12 的顺序拿走了后续号码归还 10 就可能和后续已分配的号冲突甚至危害主键唯一性。因此从实现成本和数据安全两个角度看让自增值只进不退都是更合理的选择。4.2 原因二DELETE 删除数据留下的空洞第二种情况更加直观。假设表里已经有 id 为 1 到 10 的十条数据某一天业务删除了一些老数据sqlDELETE FROM t_user WHERE id IN (3, 5, 8);删除之后表里剩下 1、2、4、6、7、9、10。此时继续插入一条新数据你会拿到 11而不是 3、5 或 8。原因很简单普通 DELETE 只是把数据行标记删除并不会重置自增计数器。计数器的当前值是 10下一次自然继续分配 11。很多同学会想那我把表清空号会不会从 1 重新开始这里要区分两个操作DELETE FROM t_user;只删除数据不重置计数器。下一次插入仍然从 11 开始。TRUNCATE TABLE t_user;相当于删除表后重建会重置计数器。下一次插入从 1 开始。所以如果你只是用 DELETE 清空数据主键空洞仍然会持续存在。只有 TRUNCATE 或 DROP TABLE 后重建才会真正让自增值重新开始。4.3 原因三主键或唯一键冲突第三种情况是插入时发生了主键冲突。看下面的例子sql-- 假设当前表里已经有 id 1 的记录 INSERT INTO t_user(id, name) VALUES (1, 孙七); -- 报错Duplicate entry 1 for key PRIMARY这条插入因为主键冲突失败了但你可能想不到如果表中还有其他列是唯一键冲突也同样会消耗自增值sql-- 假设 mobile 列上建立了唯一索引 INSERT INTO t_user(id, name, mobile) VALUES (NULL, 周八, 13800000000); -- 如果 mobile 已存在同样报 Duplicate entry关键是在 MySQL 的插入流程里自增值的申请往往发生在唯一性检查之前。InnoDB 要先拿到一个候选主键值才能构建整条记录之后再检查唯一索引是否冲突。即使最终因为冲突插入失败刚才申请的那个自增值也已经消耗掉了。可以理解为去餐厅取号不管最后有没有吃上饭号都已经叫过了。下一次取号一定是下一个号码。4.4 原因四INSERT ... ON DUPLICATE KEY UPDATE 的多余消耗这是生产中极容易被忽略的一个坑。INSERT ... ON DUPLICATE KEY UPDATE的语义是先尝试插入如果冲突就转成更新。问题在于即使最终走了更新分支MySQL 也已经在插入阶段申请并消耗了一个自增值。sql-- 假设表里已经有 id 1 的记录 INSERT INTO t_user(id, name) VALUES (1, 吴九) ON DUPLICATE KEY UPDATE name VALUES(name);这条语句最终执行的是 UPDATE并没有新增行。但如果你紧接着再插入一条不冲突的记录会发现主键跳过了好几个号。因为在内部MySQL 先尝试用自增值生成一条新记录失败后才改为更新。这个尝试的动作已经把号拿走了。这类语句在存在即更新、不存在即插入的业务场景里非常常见比如用户签到、订单状态兜底、幂等写入。如果 QPS 很高而冲突比例也比较高自增主键会以肉眼可见的速度跳号。虽然这通常不影响功能但如果你给主键选了偏小的类型比如 INT就需要留意号段消耗速度了。4.5 原因五批量插入的预分配浪费这个原因在前面 3.4 节已经详细讲过这里再从不连续的角度收一下尾。对于INSERT INTO ... SELECT ...、LOAD DATA这类插入前无法确定行数的语句在innodb_autoinc_lock_mode1下InnoDB 会一次向计数器申请一批号码而不是一条一条申请。比如某个内部批次一次预留 8 个号。如果这一批实际只插入了 5 行剩下的 3 个号不会再分配给下一批。连续的大批量导入之后主键序列里就会留下一串规律性的空洞。这些空洞的本质是用一部分号码空间换批量分配效率。在innodb_autoinc_lock_mode2下由于并发事务的号码会交错分配虽然单个事务内部不再预留一长段但从整张表的视角看号码仍然会呈现交错跳跃。只要 ROW 格式 binlog 下主从能正确回放这种跳号就是可接受的。4.6 原因六MySQL 8.0 之前服务重启导致的自增值回退最后一个原因是版本相关的也是很多同学在低版本 MySQL 里偶然踩到的灵异事件。在 MySQL 8.0 之前InnoDB 的自增计数器主要保存在内存中服务运行期间它不断增长。但如果 MySQL 突然重启计数器不一定能恢复到重启前的值。sql-- 假设当前 max(id) 100 INSERT INTO t_user(name) VALUES (郑十); -- 拿到 101 DELETE FROM t_user WHERE id 101; -- 删掉刚插入的行 -- 此时 MySQL 重启 INSERT INTO t_user(name) VALUES (冯十一); -- 可能又拿到 101为什么因为重启后MySQL 需要重新计算自增计数器的起点。在没有持久化的版本里它会扫描表里的最大值 max(id)然后把计数器设置为 max(id) 1。既然 id101 的那行已经被删除那么 max(id) 就是 100重启后计数器退回 101。这样一来如果删除的数据已经被其他表外键引用或者删除的流水号曾被下游系统消费自增值回退就可能带来主键重复、上下游对账不准等问题。这是早期版本一个非常隐蔽的坑。MySQL 8.0 通过把自增值的修改写入 redo log解决了重启丢失计数状态的问题我们在第六章会展开讲。五、不同插入方式的底层差异讲完六大原因我们再横向对比一下不同插入方式对自增值的影响。面试官如果继续追问通常会从这里切入。5.1 普通 INSERT VALUES普通单行插入是最简单的场景。因为没有显式指定自增列的值MySQL 会申请 1 个自增值。高峰并发下这种场景由锁模式决定是否加 AUTO-INC 表锁。MySQL 8.0 默认innodb_autoinc_lock_mode2多事务可以交错拿号吞吐很高。5.2 一条语句插入多行sqlINSERT INTO t_user(name) VALUES (A), (B), (C);一条语句多行 VALUES行数在解析阶段就能确定属于简单插入。MySQL 会一次性申请 3 个连续号比如 11、12、13。如果这条语句本身失败了这 3 个号同样会被消耗掉。5.3 INSERT ... SELECTsqlINSERT INTO t_user(name) SELECT name FROM t_user_bak;这类语句的行数在插入前不确定属于批量插入。在锁模式 1 下会申请表级 AUTO-INC 锁并持续到语句结束同时可能按批次预留号码产生号段浪费。在锁模式 2 下不再全程持有表锁但逐段分配的方式仍可能带来交错和跳跃。5.4 REPLACE INTOsqlREPLACE INTO t_user(id, name) VALUES (1, 王替换);REPLACE INTO 会先尝试插入如果主键或唯一键冲突就删除冲突行再插入新行。这个先插后删再插的过程会消耗额外的自增号。如果冲突频繁号段消耗会比想象中更快。5.5 INSERT ... ON DUPLICATE KEY UPDATE前面 4.4 已经分析过它可能不新增行却仍然消耗自增值。这里再强调一句如果你希望用自增主键精确反映插入次数就不要依赖它因为它连尝试插入都算号。5.6 LOAD DATAsqlLOAD DATA LOCAL INFILE /tmp/user.csv INTO TABLE t_user FIELDS TERMINATED BY , LINES TERMINATED BY \n (name);LOAD DATA 是典型的批量插入行数未知。它的自增值分配和INSERT ... SELECT类似容易产生预分配空洞。数据导入量越大空洞往往越多。大量导入后如果发现主键跳跃严重不必惊慌多数情况下这是正常现象。六、MySQL 8.0 对自增值持久化的改进4.6 节提到MySQL 8.0 之前自增计数器主要保存在内存里重启后通过扫描 max(id) 来重建这就会导致回退。MySQL 8.0 的一个重要改进就是让自增值具备了持久化能力。6.1 8.0 之前为什么不持久化在那个阶段InnoDB 认为自增值本质上是一个可以重新推导的状态。重启后算出max(id) 1就足够了。但这个推导忽略了已分配但已删除的情况一旦发生就会出现号段回退。6.2 8.0 的持久化机制MySQL 8.0 开始InnoDB 每次修改自增计数器的当前值时会把这次变更写入 redo log。即使服务器突然宕机重启后也能从 redo log 中恢复出正确的计数器位置从而避免回退。sql-- 查看当前自增值 SHOW CREATE TABLE t_user\G -- 输出中的 AUTO_INCREMENTxxx 表示下一个可分配值SHOW CREATE TABLE展示的AUTO_INCREMENTxxx就是当前计数器的下一个可用值。在 MySQL 8.0 中它会随着 redo log 一起被更可靠地恢复。6.3 实际使用中还要注意的边界即使 MySQL 8.0 解决了自增值回退也不代表主键就连续了。回滚、DELETE、冲突、预分配这些因素依然存在。持久化改进解决的是重启不回退而不是分配不跳号。理解这一点能帮你更准确地向面试官表达你对版本差异的认知。七、可以亲手复现空洞的实验步骤光看理论容易忘记建议你用本机 MySQL 把下面几个实验跑一遍。建议使用 MySQL 5.7 和 8.0 各准备一套环境有些差异只有亲手验证过才会有感觉。7.1 环境准备sqlCREATE DATABASE IF NOT EXISTS demo; USE demo; CREATE TABLE t_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, mobile VARCHAR(20) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_mobile (mobile) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;7.2 复现回滚空洞sqlSTART TRANSACTION; INSERT INTO t_user(name) VALUES (a); ROLLBACK; INSERT INTO t_user(name) VALUES (b); SELECT * FROM t_user;预期结果只有一条记录且 id 为 2。说明 id1 已经被回滚事务消耗。7.3 复现唯一键冲突sqlINSERT INTO t_user(name, mobile) VALUES (c, 13800000000); -- 下面这条会因为 mobile 冲突而失败 INSERT INTO t_user(name, mobile) VALUES (d, 13800000000); INSERT INTO t_user(name, mobile) VALUES (e, 13900000000); SELECT * FROM t_user;预期结果c 的 id 为 3d 插入失败但号被消耗e 的 id 跳到 5。7.4 复现 ON DUPLICATE KEY UPDATE 跳号sqlINSERT INTO t_user(name, mobile) VALUES (f, 13800000000) ON DUPLICATE KEY UPDATE name VALUES(name); INSERT INTO t_user(name, mobile) VALUES (g, 13700000000); SELECT * FROM t_user;预期结果f 最终走了更新没有新增行g 的 id 却比前一次分配又跳了号说明更新路径仍然消耗了自增值。7.5 复现 DELETE 与 TRUNCATE 的区别sqlDELETE FROM t_user; INSERT INTO t_user(name) VALUES (h); SELECT * FROM t_user; -- id 不会从 1 开始 TRUNCATE TABLE t_user; INSERT INTO t_user(name) VALUES (i); SELECT * FROM t_user; -- id 从 1 重新开始7.6 观察批量插入的号段跨度sql-- 造一张测试源表 CREATE TABLE t_src AS SELECT CONCAT(name_, n) AS name FROM ( SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) tmp; INSERT INTO t_user(name) SELECT name FROM t_src; SHOW CREATE TABLE t_user\G观察SHOW CREATE TABLE中的AUTO_INCREMENT值与实际插入行数之间的差异再结合innodb_autoinc_lock_mode的值理解分配行为。八、自增值耗尽、面试追问与生产实践8.1 自增值耗尽会发生什么自增列既然是一个整数就有最大值。以 INT 为例无符号最大值是 4294967295。一旦计数器到达上限继续插入就会失败sql-- 当 AUTO_INCREMENT 到达 INT UNSIGNED 上限后 INSERT INTO t_user(name) VALUES (x); -- 报错Duplicate entry 4294967295 for key PRIMARY 或自增值耗尽相关错误虽然普通业务很难把 INT 用到上限但一些高频日志、流水、埋点表完全有可能。尤其是前面提到的ON DUPLICATE KEY UPDATE和回滚会加速号段消耗所以这类表建议直接使用BIGINT UNSIGNED或者评估是否需要自增主键。8.2 面试高频追问追问一自增值为什么不能简单归还因为归还后可能与其他已分配的号码冲突。例如事务 A 拿了 10 但回滚事务 B 已经拿了 11、12如果归还 10下一次分配又给 10那么 10 和 11 的相对顺序就被打乱客户端也无法保证有序。此外实现归还并保证唯一的成本远高于直接浪费一个号码。追问二为什么 MySQL 8.0 之后还要用 BIGINT8.0 虽然解决了自增值重启回退但号段仍然会因为回滚、冲突、预分配而消耗。高频表在几年内消耗数十亿号并不罕见INT UNSIGNED 上限只有 42 亿稍有不慎就会撞线。所以生产环境的高频表推荐 BIGINT UNSIGNED。追问三能不能关闭自增锁提升并发innodb_autoinc_lock_mode2已经默认不再使用表级 AUTO-INC 锁主从复制使用 ROW 格式时完全安全。但要注意两个前提binlog 格式必须是 ROW不依赖语句级的自增连续。如果你的场景恰好依赖INSERT ... SELECT里行的顺序与自增值顺序严格一致就应当避免使用模式 2。追问四如果就是要主键连续怎么办可以自己维护一个序列表在同一个事务里通过SELECT ... FOR UPDATE加锁取号再写入业务表。但这会引入额外的写操作和锁竞争性能远不如原生自增。除非业务必须要求连续编号比如发票号、订单号否则不建议这样做。8.3 生产实践建议建议一主键类型优先 BIGINT UNSIGNED。尤其在日志、流水、埋点、消息表这类高频写入场景INT 的号段消耗速度远超想象。建议二不要试图用自增主键表示业务连续性。自增主键只保证唯一和趋势递增它不承担业务含义。发票号、订单号、账单号这类需要连续的业务编号应该单独生成并持久化。建议三避免在高频表上滥用 ON DUPLICATE KEY UPDATE。如果冲突比例很高号段消耗速度会被放大。对于幂等写入场景可以考虑先查后写、唯一索引冲突重试等方式。建议四主从复制优先使用 ROW 格式。ROW 格式下自增值的分配顺序不再影响主从一致性可以放心使用innodb_autoinc_lock_mode2获得更高并发。建议五不要依赖主键顺序推断写入时间。由于事务回滚、并发交错主键大小与插入时间并不严格对应。如果业务需要精确时间应显式记录 create_time。九、总结MySQL 自增主键为什么不连续看似只是一个面试问题实际上它串起了 InnoDB 的多个核心机制自增计数器的生命周期、AUTO-INC 锁与锁模式、聚簇索引的追加写特性、事务回滚与持久化、复制格式与主从一致。掌握这条主线之后你会发现很多看似孤立的知识点都连成了一张网。把要点再回顾一遍自增主键只保证唯一和趋势递增不保证连续这是它的设计前提而不是 Bug。自增值的分配发生在写入之前一旦分配就不会因为回滚而归还这是不连续最根本的原因。AUTO-INC 锁与 innodb_autoinc_lock_mode决定并发插入时的分配粒度模式 2 是 MySQL 8.0 的默认选择也是现代高并发场景的推荐。事务回滚、DELETE、唯一键冲突、ON DUPLICATE KEY UPDATE、批量预分配、8.0 之前重启是六大不连续来源掌握每一类背后的事务与锁机制是面试中的加分项。MySQL 8.0 的自增值持久化解决了重启回退问题但没有解决分配跳号问题两个问题要分开理解。生产实践中优先使用 BIGINT UNSIGNED不要用自增主键承担业务连续性遇到高频写入表要提前规划号段容量。 面试官：MySQL自增主键为什么不是连续的？
一、从一个面试问题说起不知道你有没有遇到过这样的场景你正信心满满地面试一个后端开发岗位前面几轮关于 JVM、Redis、分布式锁的问题都回答得还算流畅。轮到数据库环节面试官看似不经意地问了一句你们项目里主键一般怎么生成你回答MySQL 自增主键比较多。面试官点点头紧接着追问那你知道 MySQL 的自增主键为什么经常不是连续的吗很多同学听到这个问题会愣一下。因为在直觉里AUTO_INCREMENT既然号称自增那么 1、2、3、4、5 一路排下去似乎是天经地义的事。但实际生产中你会发现表里的主键经常出现 1、2、4、7、9 这样的空洞甚至隔几个数就跳一次号。这到底是 MySQL 的 Bug还是它刻意为之的设计这篇文章我们就从现象到原理从源码到实验把自增主键为什么不连续这个问题彻底讲透。全文会覆盖以下几个方面自增主键的基础机制与内部计数器自增锁与innodb_autoinc_lock_mode三种模式造成主键不连续的六大核心原因不同插入方式的底层差异MySQL 8.0 对自增值持久化的改进可以亲手复现空洞的实验步骤自增值耗尽、面试追问与生产实践读完本文你不仅能从容应对这个问题还能从自增值分配这条主线出发串起事务、锁、回滚、并发控制、持久化等一系列 MySQL 核心知识。二、自增主键的基础知识2.1 什么是 AUTO_INCREMENT在 MySQL 中给某一列加上AUTO_INCREMENT属性后当插入一行数据却没有显式指定该列的值时MySQL 会自动为它分配一个递增的数值。最典型的用法是把它加在整数类型的主键上sqlCREATE TABLE t_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;上面的建表语句中id列就是自增主键。执行INSERT INTO t_user(name) VALUES (张三)时我们并没有给 id 赋值MySQL 会自动生成一个值。这个值由表内部维护的一个自增计数器决定而不是简单地在当前最大值基础上加一这么简单。需要注意的是AUTO_INCREMENT 只能用于整数类型的列包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。如果你试图把它用在CHAR、VARCHAR、DECIMAL或者浮点类型上MySQL 会直接报错。这个限制本身就提醒我们自增值是一个计数语义而不是业务编号语义。一个很重要的认知是自增主键保证的是唯一且单调递增的一般趋势它从来没有承诺过一定连续。官方文档对 AUTO_INCREMENT 的描述里只强调了自动生成值的唯一性并没有任何一条保证生成的值是连续的。理解了这一点本文后续的所有不连续现象就都顺理成章了。2.2 为什么大家都爱用自增主键在深入不连续问题之前我们先搞清楚一个问题为什么自增主键在业务中这么流行答案主要来自 InnoDB 存储引擎的物理结构。InnoDB 默认使用聚簇索引来组织数据。所谓聚簇索引就是数据行本身和主键索引存储在一起叶子节点里直接存放整行记录。这意味着如果主键是顺序递增的新插入的数据会追加到 B 树的最右侧叶子页。只有当右侧叶子页写满时才会发生一次新的页分配。页分裂的概率极低写入效率高磁盘空间利用率也高。反过来如果主键是随机值比如 UUID那么每次插入都可能落在 B 树的中间某个位置。一旦目标叶子页已经写满就会触发页分裂。页分裂会带来额外的数据搬运、索引节点调整还可能留下大量页内空洞最终导致写入性能下降、碎片增多。所以自增主键之所以流行根本原因是它和聚簇索引的追加写特性天然契合。它是为了写入性能和索引紧凑性服务的而不是为了号码连续服务的。这也解释了为什么数据库设计者宁可让主键出现空洞也要优先保证分配机制的简单和高效。2.3 两个容易混淆的概念自增列与自增计数器很多同学觉得自增主键就是上一个值加一这个理解有两个隐藏的误区第一个误区是把下一个值简单地等同于max(id) 1。实际上MySQL 为每张带自增列的表维护了一个独立的计数器这个计数器的当前值并不总是等于表中的最大 id。计数器有自己的生命周期和持久化策略它可能比 max(id) 大也可能在极端情况下如 MySQL 8.0 之前的重启出现回退。第二个误区是把自增列的值和物理行号混为一谈。自增列只是一个普通的数值列只是因为带了 AUTO_INCREMENT 属性MySQL 会在你没有指定值时帮你填。它并不代表数据在磁盘上的物理顺序更不承担任何行号必须连续的责任。在 InnoDB 内部自增计数器被保存在数据字典结构dict_table_t的autoinc字段中。每次需要生成自增值时InnoDB 会读取并推进这个计数器。计数器在内存中的推进非常快但它和实际写入磁盘的数据并不完全同步这正是诸多跳号现象的根源之一。2.4 LAST_INSERT_ID() 与自增值的可见性在讲计数器之前还需要掌握一个非常实用的函数LAST_INSERT_ID()。当你执行完一条插入语句后可以通过SELECT LAST_INSERT_ID()拿到本次连接刚刚生成的自增值。sqlINSERT INTO t_user(name) VALUES (李四); SELECT LAST_INSERT_ID();这里有几个值得注意的细节LAST_INSERT_ID()是会话级的不同连接之间互相隔离。A 连接拿到的是 A 自己插入生成的值不会被 B 连接的插入干扰。如果一条语句一次插入了多行LAST_INSERT_ID()返回的是第一条记录被分配的自增值而不是最后一条。例如一次插入 3 行生成 10、11、12函数返回 10。如果插入失败或者被回滚已经生成的自增值不会退回计数器。即使你拿不到结果这个号也已经消耗掉了。通过 JDBC 等驱动执行插入后调用getGeneratedKeys()底层其实就是向 MySQL 协议发起LAST_INSERT_ID请求。理解LAST_INSERT_ID()的会话隔离性有助于后面理解为什么并发插入时 A 和 B 拿到的号可能是交错的但各自又能准确地取得自己的值。这个特性是连续和唯一能够同时被权衡处理的重要前提。三、自增值的生成机制3.1 自增计数器如何推进要理解不连续必须先理解连续是怎么来的。MySQL 中自增值的分配并不直接发生在 SQL 层的INSERT解析之后而是发生在 InnoDB 存储引擎内部。当一个插入请求进入 InnoDB 时大致会经历这样的流程SQL 层解析插入语句判断哪些行需要自动生成主键。InnoDB 根据插入类型向自增计数器申请一段连续的值。根据innodb_autoinc_lock_mode的配置选择是否加自增锁、锁的粒度如何。计数器向前推进申请到的值被分配给各条待插入记录。记录写入聚簇索引事务提交或回滚。从这个流程可以清楚地看到自增值的分配发生在写入之前的申请阶段而不是写入成功之后的结算阶段。也就是说一个自增值一旦从计数器里被拿走不管这条记录最终是否真的成功落库它都不会再被还回去。这是自增值不连续的第一个也是最根本的机制基础。我们可以把计数器想象成一个只能前进、不能后退的发号机。你今天去银行取号排队拿到的号是 57 号。即使你拿完号之后突然有事走了没有真正办理业务也不会有人把你的 57 号回收再发给下一个人。下一个人拿到的仍然是 58 号。数据库的自增值分配本质上就是这样一个发号逻辑。3.2 自增锁AUTO-INC Lock在并发场景下如果多个事务同时申请自增值计数器就必须加锁保护否则会出现两个事务拿到同一个号的情况。InnoDB 为此设计了一种特殊的锁叫AUTO-INC 锁也叫自增锁。自增锁有几个非常鲜明的特点它是一种表级锁而不是行锁。加锁对象是整张表而不是某一行。它的生命周期很短通常在申请完自增值之后就会释放而不是像普通行锁那样直到事务结束才释放。它存在的核心目的是为了保证一次插入语句生成的自增值是连续的以及兼容基于语句的复制。为什么要引入表级锁考虑这样一种情况事务 A 执行一条普通插入生成 id10事务 B 几乎同时执行插入生成 id11。对于实际业务来说这完全没问题因为主键 10 和 11 谁先拿到并不重要。但对于基于语句的主从复制来说问题就大了。在 statement 格式的 binlog 中主库执行的每条 SQL 会被原样记录从库再原样执行一遍。如果主库上两条插入语句并发执行它们的自增值分配顺序是 10、11但从库是单线程回放执行顺序可能变成先 11、后 10。这样主从两边的数据就错位了。因此在早期版本中为了保证 statement 复制的正确性自增锁必须把并发插入串行化让每次插入拿到的号在语句级别是连续的。3.3 innodb_autoinc_lock_mode 三种模式随着 MySQL 的发展尤其是基于行复制ROW 格式 binlog成为主流之后语句级连续的必要性下降了。为了让并发插入性能更好MySQL 引入了innodb_autoinc_lock_mode参数。它有三种取值取值名称主要行为优点缺点0传统锁定模式所有插入都使用 AUTO-INC 表级锁语句执行完才释放复制最安全兼容 statement binlog并发性能差1连续锁定模式简单插入不加表锁用轻量级互斥量批量插入仍加表级锁兼顾性能与复制安全批量插入仍可能阻塞2交错锁定模式所有插入都不加 AUTO-INC 表锁自增值可能交错并发性能最好不兼容 statement 复制下的不连续场景这里需要对简单插入和批量插入做一下区分。MySQL 官方把插入分成三类简单插入Simple inserts插入前能够预先确定行数的语句比如单行INSERT INTO ... VALUES (...)。这种语句生成的自增值个数是明确的。批量插入Bulk inserts插入前无法预先确定行数的语句比如INSERT INTO ... SELECT ...、LOAD DATA。这类语句可能要插入很多行且行数事先未知。混合模式插入Mixed-mode inserts一条语句中部分行指定了自增列的值部分行没有指定。比如INSERT INTO t(id, name) VALUES (1, a), (NULL, b), (NULL, c)。在innodb_autoinc_lock_mode1的连续锁定模式下对于简单插入行数已知MySQL 可以预先用非常轻量的互斥量一次性分配足够数量的自增值不需要加表级 AUTO-INC 锁。对于批量插入因为行数不确定为了保证语句内部的连续性和复制安全仍然会使用表级 AUTO-INC 锁并且锁会持续到语句执行结束。对于混合模式插入由于部分行指定了值分配逻辑更复杂MySQL 的处理也要更谨慎。在innodb_autoinc_lock_mode2的交错锁定模式下所有插入都不再用表级锁多个事务的自增值可以互相交错。举例来说事务 A 一次插入 3 行事务 B 一次插入 2 行最终分配结果可能是 A 拿到 1、3、5B 拿到 2、4。这在 ROW 格式 binlog 下是安全的因为从库回放的是具体行数据不依赖自增值的语句级连续性。不同 MySQL 版本的默认值不一样MySQL 5.7 及更早版本默认innodb_autoinc_lock_mode1MySQL 8.0 默认值调整为 2。主从复制的 binlog 格式也相应推荐使用 ROW 格式。3.4 预分配与预留机制除了锁模式自增值的预分配也是造成跳号的重要机制。在innodb_autoinc_lock_mode1下对于批量插入InnoDB 不知道要插入多少行于是会按照一定策略一次向计数器申请一段值。比如它可能一次性申请 8 个号然后从这段号里依次分配给实际插入的行。如果最终只插入了 5 行那么剩下 3 个号就被浪费掉了。这种浪费在LOAD DATA导入大量数据时尤其明显。假设 InnoDB 每次预申请 8 个号而你的数据行数不是 8 的整数倍那么每一批的最后都会有若干个号被空出来。数据量越大累积起来的空洞就越多。预分配机制体现了 MySQL 的一个核心权衡用号码的浪费换取并发和批量写入的效率。与其在每插入一行时都去争抢一次计数器锁不如一次申请一批让后续的行在无锁或少锁的情况下快速写入。号码的连续性和写入的吞吐量在这里形成了一对取舍。四、导致自增主键不连续的六大核心原因铺垫了这么多接下来进入本文最核心的部分。我们把自增主键不连续的原因归纳为六大类每一类都从为什么讲到怎么复现。这六类原因分别是事务回滚导致的自增值消耗DELETE 删除数据留下的空洞主键或唯一键冲突INSERT ... ON DUPLICATE KEY UPDATE 的多余消耗批量插入的预分配浪费MySQL 8.0 之前服务重启导致的自增值回退4.1 原因一事务回滚这是最常见、也是面试中最常被问到的一个原因。请看下面这个例子sql-- 假设当前表里 max(id) 9 START TRANSACTION; INSERT INTO t_user(name) VALUES (王五); -- 生成了 id 10 ROLLBACK;执行完 ROLLBACK 后我们再查询这张表会发现 id 为 10 的记录并不存在。但不妨接着执行下一条插入sqlINSERT INTO t_user(name) VALUES (赵六); -- 生成 id 11 SELECT * FROM t_user ORDER BY id DESC LIMIT 3;你会发现赵六拿到的是 11而不是 10。也就是说虽然王五那行被回滚了但它占用的 10 号没有被回收。原因就是前面反复强调的自增值是在事务执行过程中从计数器里申请走的回滚只会撤销数据行不会撤销计数器的推进。这里还可以再深挖一层。InnoDB 并不会为回滚后归还自增值设计一套复杂的回收机制。如果真的归还就需要考虑并发事务可能已经按 10、11、12 的顺序拿走了后续号码归还 10 就可能和后续已分配的号冲突甚至危害主键唯一性。因此从实现成本和数据安全两个角度看让自增值只进不退都是更合理的选择。4.2 原因二DELETE 删除数据留下的空洞第二种情况更加直观。假设表里已经有 id 为 1 到 10 的十条数据某一天业务删除了一些老数据sqlDELETE FROM t_user WHERE id IN (3, 5, 8);删除之后表里剩下 1、2、4、6、7、9、10。此时继续插入一条新数据你会拿到 11而不是 3、5 或 8。原因很简单普通 DELETE 只是把数据行标记删除并不会重置自增计数器。计数器的当前值是 10下一次自然继续分配 11。很多同学会想那我把表清空号会不会从 1 重新开始这里要区分两个操作DELETE FROM t_user;只删除数据不重置计数器。下一次插入仍然从 11 开始。TRUNCATE TABLE t_user;相当于删除表后重建会重置计数器。下一次插入从 1 开始。所以如果你只是用 DELETE 清空数据主键空洞仍然会持续存在。只有 TRUNCATE 或 DROP TABLE 后重建才会真正让自增值重新开始。4.3 原因三主键或唯一键冲突第三种情况是插入时发生了主键冲突。看下面的例子sql-- 假设当前表里已经有 id 1 的记录 INSERT INTO t_user(id, name) VALUES (1, 孙七); -- 报错Duplicate entry 1 for key PRIMARY这条插入因为主键冲突失败了但你可能想不到如果表中还有其他列是唯一键冲突也同样会消耗自增值sql-- 假设 mobile 列上建立了唯一索引 INSERT INTO t_user(id, name, mobile) VALUES (NULL, 周八, 13800000000); -- 如果 mobile 已存在同样报 Duplicate entry关键是在 MySQL 的插入流程里自增值的申请往往发生在唯一性检查之前。InnoDB 要先拿到一个候选主键值才能构建整条记录之后再检查唯一索引是否冲突。即使最终因为冲突插入失败刚才申请的那个自增值也已经消耗掉了。可以理解为去餐厅取号不管最后有没有吃上饭号都已经叫过了。下一次取号一定是下一个号码。4.4 原因四INSERT ... ON DUPLICATE KEY UPDATE 的多余消耗这是生产中极容易被忽略的一个坑。INSERT ... ON DUPLICATE KEY UPDATE的语义是先尝试插入如果冲突就转成更新。问题在于即使最终走了更新分支MySQL 也已经在插入阶段申请并消耗了一个自增值。sql-- 假设表里已经有 id 1 的记录 INSERT INTO t_user(id, name) VALUES (1, 吴九) ON DUPLICATE KEY UPDATE name VALUES(name);这条语句最终执行的是 UPDATE并没有新增行。但如果你紧接着再插入一条不冲突的记录会发现主键跳过了好几个号。因为在内部MySQL 先尝试用自增值生成一条新记录失败后才改为更新。这个尝试的动作已经把号拿走了。这类语句在存在即更新、不存在即插入的业务场景里非常常见比如用户签到、订单状态兜底、幂等写入。如果 QPS 很高而冲突比例也比较高自增主键会以肉眼可见的速度跳号。虽然这通常不影响功能但如果你给主键选了偏小的类型比如 INT就需要留意号段消耗速度了。4.5 原因五批量插入的预分配浪费这个原因在前面 3.4 节已经详细讲过这里再从不连续的角度收一下尾。对于INSERT INTO ... SELECT ...、LOAD DATA这类插入前无法确定行数的语句在innodb_autoinc_lock_mode1下InnoDB 会一次向计数器申请一批号码而不是一条一条申请。比如某个内部批次一次预留 8 个号。如果这一批实际只插入了 5 行剩下的 3 个号不会再分配给下一批。连续的大批量导入之后主键序列里就会留下一串规律性的空洞。这些空洞的本质是用一部分号码空间换批量分配效率。在innodb_autoinc_lock_mode2下由于并发事务的号码会交错分配虽然单个事务内部不再预留一长段但从整张表的视角看号码仍然会呈现交错跳跃。只要 ROW 格式 binlog 下主从能正确回放这种跳号就是可接受的。4.6 原因六MySQL 8.0 之前服务重启导致的自增值回退最后一个原因是版本相关的也是很多同学在低版本 MySQL 里偶然踩到的灵异事件。在 MySQL 8.0 之前InnoDB 的自增计数器主要保存在内存中服务运行期间它不断增长。但如果 MySQL 突然重启计数器不一定能恢复到重启前的值。sql-- 假设当前 max(id) 100 INSERT INTO t_user(name) VALUES (郑十); -- 拿到 101 DELETE FROM t_user WHERE id 101; -- 删掉刚插入的行 -- 此时 MySQL 重启 INSERT INTO t_user(name) VALUES (冯十一); -- 可能又拿到 101为什么因为重启后MySQL 需要重新计算自增计数器的起点。在没有持久化的版本里它会扫描表里的最大值 max(id)然后把计数器设置为 max(id) 1。既然 id101 的那行已经被删除那么 max(id) 就是 100重启后计数器退回 101。这样一来如果删除的数据已经被其他表外键引用或者删除的流水号曾被下游系统消费自增值回退就可能带来主键重复、上下游对账不准等问题。这是早期版本一个非常隐蔽的坑。MySQL 8.0 通过把自增值的修改写入 redo log解决了重启丢失计数状态的问题我们在第六章会展开讲。五、不同插入方式的底层差异讲完六大原因我们再横向对比一下不同插入方式对自增值的影响。面试官如果继续追问通常会从这里切入。5.1 普通 INSERT VALUES普通单行插入是最简单的场景。因为没有显式指定自增列的值MySQL 会申请 1 个自增值。高峰并发下这种场景由锁模式决定是否加 AUTO-INC 表锁。MySQL 8.0 默认innodb_autoinc_lock_mode2多事务可以交错拿号吞吐很高。5.2 一条语句插入多行sqlINSERT INTO t_user(name) VALUES (A), (B), (C);一条语句多行 VALUES行数在解析阶段就能确定属于简单插入。MySQL 会一次性申请 3 个连续号比如 11、12、13。如果这条语句本身失败了这 3 个号同样会被消耗掉。5.3 INSERT ... SELECTsqlINSERT INTO t_user(name) SELECT name FROM t_user_bak;这类语句的行数在插入前不确定属于批量插入。在锁模式 1 下会申请表级 AUTO-INC 锁并持续到语句结束同时可能按批次预留号码产生号段浪费。在锁模式 2 下不再全程持有表锁但逐段分配的方式仍可能带来交错和跳跃。5.4 REPLACE INTOsqlREPLACE INTO t_user(id, name) VALUES (1, 王替换);REPLACE INTO 会先尝试插入如果主键或唯一键冲突就删除冲突行再插入新行。这个先插后删再插的过程会消耗额外的自增号。如果冲突频繁号段消耗会比想象中更快。5.5 INSERT ... ON DUPLICATE KEY UPDATE前面 4.4 已经分析过它可能不新增行却仍然消耗自增值。这里再强调一句如果你希望用自增主键精确反映插入次数就不要依赖它因为它连尝试插入都算号。5.6 LOAD DATAsqlLOAD DATA LOCAL INFILE /tmp/user.csv INTO TABLE t_user FIELDS TERMINATED BY , LINES TERMINATED BY \n (name);LOAD DATA 是典型的批量插入行数未知。它的自增值分配和INSERT ... SELECT类似容易产生预分配空洞。数据导入量越大空洞往往越多。大量导入后如果发现主键跳跃严重不必惊慌多数情况下这是正常现象。六、MySQL 8.0 对自增值持久化的改进4.6 节提到MySQL 8.0 之前自增计数器主要保存在内存里重启后通过扫描 max(id) 来重建这就会导致回退。MySQL 8.0 的一个重要改进就是让自增值具备了持久化能力。6.1 8.0 之前为什么不持久化在那个阶段InnoDB 认为自增值本质上是一个可以重新推导的状态。重启后算出max(id) 1就足够了。但这个推导忽略了已分配但已删除的情况一旦发生就会出现号段回退。6.2 8.0 的持久化机制MySQL 8.0 开始InnoDB 每次修改自增计数器的当前值时会把这次变更写入 redo log。即使服务器突然宕机重启后也能从 redo log 中恢复出正确的计数器位置从而避免回退。sql-- 查看当前自增值 SHOW CREATE TABLE t_user\G -- 输出中的 AUTO_INCREMENTxxx 表示下一个可分配值SHOW CREATE TABLE展示的AUTO_INCREMENTxxx就是当前计数器的下一个可用值。在 MySQL 8.0 中它会随着 redo log 一起被更可靠地恢复。6.3 实际使用中还要注意的边界即使 MySQL 8.0 解决了自增值回退也不代表主键就连续了。回滚、DELETE、冲突、预分配这些因素依然存在。持久化改进解决的是重启不回退而不是分配不跳号。理解这一点能帮你更准确地向面试官表达你对版本差异的认知。七、可以亲手复现空洞的实验步骤光看理论容易忘记建议你用本机 MySQL 把下面几个实验跑一遍。建议使用 MySQL 5.7 和 8.0 各准备一套环境有些差异只有亲手验证过才会有感觉。7.1 环境准备sqlCREATE DATABASE IF NOT EXISTS demo; USE demo; CREATE TABLE t_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, mobile VARCHAR(20) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_mobile (mobile) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;7.2 复现回滚空洞sqlSTART TRANSACTION; INSERT INTO t_user(name) VALUES (a); ROLLBACK; INSERT INTO t_user(name) VALUES (b); SELECT * FROM t_user;预期结果只有一条记录且 id 为 2。说明 id1 已经被回滚事务消耗。7.3 复现唯一键冲突sqlINSERT INTO t_user(name, mobile) VALUES (c, 13800000000); -- 下面这条会因为 mobile 冲突而失败 INSERT INTO t_user(name, mobile) VALUES (d, 13800000000); INSERT INTO t_user(name, mobile) VALUES (e, 13900000000); SELECT * FROM t_user;预期结果c 的 id 为 3d 插入失败但号被消耗e 的 id 跳到 5。7.4 复现 ON DUPLICATE KEY UPDATE 跳号sqlINSERT INTO t_user(name, mobile) VALUES (f, 13800000000) ON DUPLICATE KEY UPDATE name VALUES(name); INSERT INTO t_user(name, mobile) VALUES (g, 13700000000); SELECT * FROM t_user;预期结果f 最终走了更新没有新增行g 的 id 却比前一次分配又跳了号说明更新路径仍然消耗了自增值。7.5 复现 DELETE 与 TRUNCATE 的区别sqlDELETE FROM t_user; INSERT INTO t_user(name) VALUES (h); SELECT * FROM t_user; -- id 不会从 1 开始 TRUNCATE TABLE t_user; INSERT INTO t_user(name) VALUES (i); SELECT * FROM t_user; -- id 从 1 重新开始7.6 观察批量插入的号段跨度sql-- 造一张测试源表 CREATE TABLE t_src AS SELECT CONCAT(name_, n) AS name FROM ( SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) tmp; INSERT INTO t_user(name) SELECT name FROM t_src; SHOW CREATE TABLE t_user\G观察SHOW CREATE TABLE中的AUTO_INCREMENT值与实际插入行数之间的差异再结合innodb_autoinc_lock_mode的值理解分配行为。八、自增值耗尽、面试追问与生产实践8.1 自增值耗尽会发生什么自增列既然是一个整数就有最大值。以 INT 为例无符号最大值是 4294967295。一旦计数器到达上限继续插入就会失败sql-- 当 AUTO_INCREMENT 到达 INT UNSIGNED 上限后 INSERT INTO t_user(name) VALUES (x); -- 报错Duplicate entry 4294967295 for key PRIMARY 或自增值耗尽相关错误虽然普通业务很难把 INT 用到上限但一些高频日志、流水、埋点表完全有可能。尤其是前面提到的ON DUPLICATE KEY UPDATE和回滚会加速号段消耗所以这类表建议直接使用BIGINT UNSIGNED或者评估是否需要自增主键。8.2 面试高频追问追问一自增值为什么不能简单归还因为归还后可能与其他已分配的号码冲突。例如事务 A 拿了 10 但回滚事务 B 已经拿了 11、12如果归还 10下一次分配又给 10那么 10 和 11 的相对顺序就被打乱客户端也无法保证有序。此外实现归还并保证唯一的成本远高于直接浪费一个号码。追问二为什么 MySQL 8.0 之后还要用 BIGINT8.0 虽然解决了自增值重启回退但号段仍然会因为回滚、冲突、预分配而消耗。高频表在几年内消耗数十亿号并不罕见INT UNSIGNED 上限只有 42 亿稍有不慎就会撞线。所以生产环境的高频表推荐 BIGINT UNSIGNED。追问三能不能关闭自增锁提升并发innodb_autoinc_lock_mode2已经默认不再使用表级 AUTO-INC 锁主从复制使用 ROW 格式时完全安全。但要注意两个前提binlog 格式必须是 ROW不依赖语句级的自增连续。如果你的场景恰好依赖INSERT ... SELECT里行的顺序与自增值顺序严格一致就应当避免使用模式 2。追问四如果就是要主键连续怎么办可以自己维护一个序列表在同一个事务里通过SELECT ... FOR UPDATE加锁取号再写入业务表。但这会引入额外的写操作和锁竞争性能远不如原生自增。除非业务必须要求连续编号比如发票号、订单号否则不建议这样做。8.3 生产实践建议建议一主键类型优先 BIGINT UNSIGNED。尤其在日志、流水、埋点、消息表这类高频写入场景INT 的号段消耗速度远超想象。建议二不要试图用自增主键表示业务连续性。自增主键只保证唯一和趋势递增它不承担业务含义。发票号、订单号、账单号这类需要连续的业务编号应该单独生成并持久化。建议三避免在高频表上滥用 ON DUPLICATE KEY UPDATE。如果冲突比例很高号段消耗速度会被放大。对于幂等写入场景可以考虑先查后写、唯一索引冲突重试等方式。建议四主从复制优先使用 ROW 格式。ROW 格式下自增值的分配顺序不再影响主从一致性可以放心使用innodb_autoinc_lock_mode2获得更高并发。建议五不要依赖主键顺序推断写入时间。由于事务回滚、并发交错主键大小与插入时间并不严格对应。如果业务需要精确时间应显式记录 create_time。九、总结MySQL 自增主键为什么不连续看似只是一个面试问题实际上它串起了 InnoDB 的多个核心机制自增计数器的生命周期、AUTO-INC 锁与锁模式、聚簇索引的追加写特性、事务回滚与持久化、复制格式与主从一致。掌握这条主线之后你会发现很多看似孤立的知识点都连成了一张网。把要点再回顾一遍自增主键只保证唯一和趋势递增不保证连续这是它的设计前提而不是 Bug。自增值的分配发生在写入之前一旦分配就不会因为回滚而归还这是不连续最根本的原因。AUTO-INC 锁与 innodb_autoinc_lock_mode决定并发插入时的分配粒度模式 2 是 MySQL 8.0 的默认选择也是现代高并发场景的推荐。事务回滚、DELETE、唯一键冲突、ON DUPLICATE KEY UPDATE、批量预分配、8.0 之前重启是六大不连续来源掌握每一类背后的事务与锁机制是面试中的加分项。MySQL 8.0 的自增值持久化解决了重启回退问题但没有解决分配跳号问题两个问题要分开理解。生产实践中优先使用 BIGINT UNSIGNED不要用自增主键承担业务连续性遇到高频写入表要提前规划号段容量。