为什么在MySQL/Knex中新增记录时会跳过已删除记录的ID号

你观察到的现象和索引运行机制无关，这是MySQLAUTO_INCREMENT（自增）属性的默认设计逻辑，属于完全正常的行为，具体原理如下：

核心原理 #

• 你使用knex创建表时常用的t.increments('id')语法，默认就会给ID字段加上AUTO_INCREMENT自增属性。MySQL会为每一张带自增字段的表在元数据中维护一个独立的自增计数器，每次执行插入操作时，会直接读取当前计数器的值作为新记录的ID，随后自动将计数器+1。整个过程不会因为记录删除、插入失败、事务回滚等操作回退计数器的值，所以才会出现删除ID后新增记录ID持续递增的情况。
• 这样设计的核心目的是保障写入性能与并发安全：如果要复用已删除的ID，数据库需要额外扫描全表查找空缺的最小ID，在数据量较大、并发写入请求多的场景下会大幅降低插入效率，还可能在高并发场景下出现主键冲突的问题。
• 同时这一设计也能避免业务侧的关联混乱：已经被删除的ID大概率会在操作日志、其他关联业务表中留有记录，复用ID会导致新旧数据的关联关系错误。

特殊场景处理方案 #

如果你的业务确实有复用ID的需求（非必要场景极不推荐），可以参考以下两种实现方式：

• 创建表时不给ID字段设置自增属性，插入新记录时手动指定ID值
• 自行维护可用ID池：删除记录时将对应的ID存入专门的ID回收表，插入新记录时优先从回收表中读取可用ID使用

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