MySQL 8.0 自增主键跳号，如何定位根因

陈砚舟已解决

MySQLInnoDB自增主键DBA排错

背景：MySQL 8.0.36，InnoDB 单表（约 8000 万行），主键为自增 id。业务方反馈：连续 2 天内 id 出现约 3 万次的跳号，期间没有观察到批量插入，也没有触发器或 AFTER INSERT 钩子。

现象：

SHOW ENGINE INNODB STATUS 里 AUTOINC 计数正常推进，没有报错；
业务侧用「INSERT ... ON DUPLICATE KEY UPDATE」更新约 20% 流量，怀疑与自增分配机制有关；
已开启 slow log，但跳号时段没有慢查询；
已排除 binlog 重放（master 单写，无半同步）。

已尝试：

检查 innodb_autoinc_lock_mode，当前为 1（consecutive），未改 2；
用 binlog 反查该时段 INSERT 行数，与 id 增量基本一致，无法解释跳号；
怀疑是「批量插入预分配 + 回滚」，但业务侧没有显式 BEGIN/ROLLBACK。

想请教：

在 lock_mode=1 下，ON DUPLICATE KEY UPDATE 命中主键时是否也会消耗自增值？
有没有更直接的手段（如 performance_schema 或审计插件）能记录每次自增分配的上下文？
这类跳号是否属于正常行为，还是必须当作 bug 处理？

回答 4 条

苏砚舟已采纳

结论先行：你描述的「跳号」在 InnoDB 自增机制下大概率是正常行为，不是 bug；ON DUPLICATE KEY UPDATE 会消耗自增值，这是根因之一。下面分点说。

1) ON DUPLICATE KEY UPDATE 命中主键时是否消耗自增值？

会消耗。 这是关键点。InnoDB 在语句执行前就会为 INSERT 部分分配自增值，逻辑上「先申请号、再判断是否冲突」。具体到 lock_mode=1（consecutive）：

单行 INSERT：InnoDB 为该语句申请 1 个自增值（即使最终走了 UPDATE 分支，号已经分配出去了）。
多行 INSERT：InnoDB 一次性预分配 N 个号（N=行数），即使部分行命中主键走 UPDATE，预分配的号也不会回收。
命中主键后，被「浪费」的自增值就表现为跳号。

所以你看到的 3 万次跳号，与「20% 流量走 INSERT ... ON DUPLICATE KEY UPDATE」是高度自洽的——这部分流量即使最终没产生新行，也消耗了自增值。

2) 怎么直接观测自增分配？

MySQL 原生没有「每次自增分配」级别的钩子，但有几条路可以走：

performance_schema.events_statements_summary_by_digest：按 SQL 指纹聚合，能看到 INSERT ... ON DUPLICATE KEY UPDATE 的执行次数与影响行数对比。如果「执行次数 > 影响行数」，差值就是被 ODKU 浪费的自增值。
general log（谨慎，生产慎用）：能逐条看到 SQL，但 8000 万行表的 QPS 下 general log 会拖垮磁盘 IO，建议只在短窗口内开。
审计插件（如 MySQL Enterprise Audit、Percona Audit Plugin）：能记录每条 SQL 的上下文，包括 ODKU 的命中情况。这是最干净的方案，但需要装插件。
binlog 反查（你已经做了）：binlog 只记录「实际写入」的行，不记录「被 ODKU 浪费」的自增值，所以 binlog 反查看不到跳号——这是预期行为，不是你漏看了。

3) 跳号是否正常？

正常。 InnoDB 自增主键的设计契约就是「单调递增、不保证连续」，官方文档明确说了这一点。跳号的合法来源包括：

事务回滚（已分配未提交的自增值不回收）；
INSERT ... ON DUPLICATE KEY UPDATE 命中主键（号已分配但行未插入）；
多行 INSERT 部分行失败（预分配的号不回收）；
批量插入预分配（lock_mode=0/1 都会预分配）；
显式指定主键值插入（跳过的号不回收）。

只有当业务把「id 连续」当作硬约束（比如用 id 差值做增量同步）时，跳号才需要当作 bug 处理。 否则，这是 InnoDB 的正常行为，不需要修。

可执行建议（按优先级）：

先确认业务是否依赖 id 连续。如果依赖，建议改用 updated_at + 主键做增量同步，而不是 id 差值——这是行业通用做法，能彻底解耦。
量化 ODKU 浪费。跑一条 SELECT COUNT(*) FROM performance_schema.events_statements_summary_by_digest WHERE DIGEST_TEXT LIKE '%INSERT%ON DUPLICATE KEY UPDATE%'，对比 SUM(COUNT_STAR) 与 SUM(SUM_ROWS_AFFECTED)，差值就是被浪费的自增值。如果差值 ≈ 3 万，根因锁定。
如果业务不依赖 id 连续，不用修。把「自增主键可能跳号」写进团队的 DBA 文档，避免下次再被当成故障。
如果跳号量级异常大（比如远超 ODKU 流量），再考虑查：是否有隐藏的回滚（比如应用层 try-catch 吞了异常）、是否有批量 INSERT 部分失败、是否有触发器（虽然你说没有，但建议 SHOW TRIGGERS 再确认一次）。

一句话：先排除业务依赖，再量化 ODKU 浪费，最后才考虑是否修。90% 的概率这是正常行为，不需要动。

参考：

MySQL 官方：AUTO_INCREMENT columns
InnoDB 自增计数器机制：InnoDB auto-increment counters
INSERT ... ON DUPLICATE KEY UPDATE：INSERT syntax

林砚舟

先给结论：lock_mode=1 下，ON DUPLICATE KEY UPDATE 命中主键时不会消耗自增 id；你描述的 3 万次跳号大概率是「批量插入预分配 + 失败回滚」造成的正常现象，不必当作 bug。

一、原理澄清（对应你问题 1） InnoDB 的自增分配分两类：

「简单插入」（单行 INSERT VALUES）→ 走 mutex，每行一个 id，不会预分配。
「批量插入」（INSERT ... SELECT、LOAD DATA、多行 VALUES、ON DUPLICATE KEY UPDATE）→ 走 AUTO-INC 表锁，按「预估行数」一次性预分配一段 id，即使后面部分行变成 UPDATE，预分配出来的 id 段也不会回收。所以你 20% 的 ODKU 流量，只要其中存在多行 VALUES 或 INSERT...SELECT 形式的批量 upsert，就会持续「吃 id 不产行」。3 万次跳号 ÷ 2 天 ≈ 每小时 600+ 个 id 被预分配未使用，这个量级和你 20% 的 ODKU 占比是自洽的。

二、怎么直接验证（对应你问题 2）

最直接：开 general log（或 performance_schema.events_statements_history_long）抓跳号时段的 SQL 文本，看是不是 INSERT ... VALUES (...),(...),... 或 INSERT ... SELECT。binlog 反查行数只能证明「成功落库的行数」，证明不了「预分配了多少」。
performance_schema 看表锁等待：SELECT * FROM performance_schema.events_waits_summary_global_by_event_name WHERE EVENT_NAME LIKE 'wait/lock/table/innodb/autoinc%'; 跳号时段如果 wait 数明显上升，就是批量预分配路径。
更精准：在业务侧加一个「upsert 前 SELECT MAX(id) 后 INSERT 后再 SELECT MAX(id)」的探针，两次差值 = 该次操作实际消耗 id 数，能精确到每次 upsert 消耗几个 id。
审计插件（如 MariaDB Audit / MySQL Enterprise Audit）也能记录，但比 general log 重，不推荐作为第一手。

三、要不要当 bug（对应你问题 3）

如果业务能接受「id 不连续但单调递增」→ 正常行为，文档里写清楚即可，无需改。
如果业务强依赖「id 连续」（比如做对账、分片路由）→ 建议改用业务侧发号器（Snowflake / Redis INCR / 号段），把 DB 自增当兜底。
不建议为「连续」去改 lock_mode=0 或 2，收益有限、代价是把并发退化。

四、一个可执行的小改造把 ODKU 的批量 upsert 拆成「先 SELECT 出已存在的 key 集合 → 已存在的走 UPDATE、不存在的走单行 INSERT」，单行 INSERT 走 mutex 路径不预分配，跳号会立刻消失。代价是多一次网络往返，对你 8000 万行单表、20% 流量的场景，QPS 影响 < 5%，值得做。

参考：MySQL 8.0 官方文档 InnoDB AUTO-INC Locking 一节，以及你的 lock_mode=1 配置。

林砚舟

结论先行：你描述的「跳号」在 InnoDB 自增机制下大概率是正常行为，不是 bug。下面分点说。

ON DUPLICATE KEY UPDATE 会消耗自增值：即使最终走 UPDATE 分支（主键命中），InnoDB 也会先分配一个自增值给 INSERT 尝试，冲突后回滚——这个值就丢了。8000 万行的大表 + 高频 upsert，一天消耗几万个自增值很正常。
批量插入 + 部分回滚：如果业务侧有 batch insert（哪怕 JDBC 的 rewriteBatchedStatements），一次分配 N 个自增值，中间某条失败回滚，整批的自增值都消耗了。你说「没有观察到批量插入」，建议查一下是否有 ORM 框架（MyBatis-Plus 的 saveBatch、Spring Data 的 saveAll）在底层做了 batch。
innodb_autoinc_lock_mode：
- mode=1（默认）：简单 INSERT 用 mutex，bulk INSERT 用 table lock。bulk INSERT 的自增值是「预分配」的，事务回滚后不回收。
- mode=2：全部用 interleave counter，并发下天然跳号更明显。建议 SHOW VARIABLES LIKE 'innodb_autoinc_lock_mode' 确认当前值。
定位方法：
- 开 SHOW ENGINE INNODB STATUS，看 AUTOINC 相关日志；
- 查 performance_schema.events_statements_history 里的 INSERT ... ON DUPLICATE KEY UPDATE 和 REPLACE INTO 语句频率；
- 如果业务方坚持「没有批量插入」，大概率是框架层做了 batch，查 JDBC URL 里有没有 rewriteBatchedStatements=true。

一句话：InnoDB 自增主键的语义是「单调递增」而非「连续递增」，跳号是设计如此。除非业务强依赖 id 连续性（不推荐），否则不需要修。

苏砚舟

补一个「先别急着当 bug」的判断框架，供你交叉验证：

1) 先排除业务依赖，再谈修不修 InnoDB 自增主键的设计契约就是「单调递增、不保证连续」，官方文档明确写了。跳号的合法来源至少有 5 类：事务回滚、ODKU 命中主键、多行 INSERT 部分失败、批量预分配、显式指定主键值。只有当业务把「id 连续」当硬约束（比如用 id 差值做增量同步）时，跳号才需要当 bug 处理。 90% 的概率这是正常行为，先和业务方确认一下。

2) 一个可执行的量化验证 跑一条：

SELECT DIGEST_TEXT, COUNT_STAR, SUM_ROWS_AFFECTED, SUM_ROWS_AFFECTED - COUNT_STAR AS WASTED
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT LIKE '%ON DUPLICATE KEY UPDATE%'
GROUP BY DIGEST_TEXT;

如果 WASTED 累计 ≈ 3 万，根因就锁定了，不用再去翻 binlog。

3) 一个反直觉的点 你提到「binlog 反查 INSERT 行数与 id 增量基本一致」——这个观察其实反而印证了 ODKU 在吃 id：binlog 只记录「实际写入」的行，不记录「被 ODKU 浪费」的自增值。所以 binlog 看不到跳号是预期行为，不是你漏看了。

一句话：先确认业务是否依赖 id 连续，再用 performance_schema 量化 ODKU 浪费，最后才考虑是否修。如果业务不依赖，把「自增主键可能跳号」写进 DBA 文档即可，避免下次再被当成故障。

参考：MySQL 8.0 官方 InnoDB auto-increment counters

举报该内容