咨询InnoDB表自增主键值无故缺失的原因

核心原因：批量插入的自增值预分配机制 #

InnoDB对INSERT...SELECT这类批量插入操作，会采用预分配自增值的策略，而非逐行按需分配：

• 执行批量插入前，InnoDB会预估所需ID数量，一次性预分配一段连续的自增ID范围（预分配数量由innodb_autoinc_lock_mode参数和当前数据量决定）。
• 如果实际插入行数少于预分配的ID数量，未被使用的ID会直接被丢弃，不会被后续插入复用，这就是你看到15个自增值缺失的直接原因——某次INSERT...SELECT执行时，预分配的ID比实际插入行数多了15个，这些ID被浪费了。

关联参数：innodb_autoinc_lock_mode的影响 #

这个参数控制InnoDB自增锁的工作模式，默认是模式1（连续锁）：

• 对于单行插入这类简单操作，会使用轻量级锁逐行分配ID，不会出现缺失。
• 但对于INSERT...SELECT、LOAD DATA这类批量操作，仍会触发预分配ID的逻辑，未使用的ID直接丢弃。这是InnoDB为平衡并发性能和ID连续性做出的设计——如果强制逐行分配，会使用表级锁，严重降低批量插入的并发效率。

你的场景排除其他可能 #

你明确说明没有服务器重启、崩溃、触发器/存储过程，且所有SELECT返回的行都成功插入，因此可以排除以下常见原因：

• 事务回滚导致的ID丢失（若插入后回滚，已分配的ID会被丢弃，但你确认所有行都已插入，所以不成立）。
• 服务器重启导致的自增计数器重置（InnoDB自增计数器存在内存中，重启后会从表中最大ID重新初始化，你无重启操作，可排除）。

解决方案建议 #

• 接受ID非连续性：InnoDB自增主键的设计初衷是唯一标识行，而非保证连续编号，业务逻辑不要依赖自增ID的连续性，这是最省心的方案。
• 强制连续ID（不推荐高并发场景）：将innodb_autoinc_lock_mode设置为0（传统锁模式），此时批量插入会使用表级锁，直到语句结束才释放，会逐行分配ID保证连续，但会严重降低批量插入的并发性能。
• 业务层维护连续编号：若必须需要连续的业务编号，不要依赖自增主键，可单独创建序列表，通过事务获取下一个编号（比如每次插入前查询序列表最大值+1，同时更新序列表），但这会增加额外的开发和性能开销。

内容的提问来源于stack exchange，提问作者beslana