为什么MySQL的自增ID在数据库重启后可能会出现回溯？

MySQL自增ID重启回溯仅发生于5.7及更早版本InnoDB表，因其计数器仅存内存dict_table_struct.autoinc中，重启后执行SELECT MAX(id)+1重建导致回溯；8.0+通过redo log持久化计数器并取MAX(id)与日志值较大者加步长，彻底解决该问题。

MySQL 自增 ID 在重启后回溯，只发生在 MySQL 5.7 及更早版本的 InnoDB 表中；MySQL 8.0+ 默认已修复，不会回溯。

MySQL 5.7 及以前：自增值只存在内存里

旧版 InnoDB 把 AUTO_INCREMENT 计数器存放在表对象的内存结构 dict_table_struct.autoinc 中，不写盘、不记 redo log。只要 mysqld 进程退出（无论是 service mysqld restart 还是异常崩溃），这个值就彻底丢失。

重启后，MySQL 会执行类似 SELECT MAX(id) + 1 的逻辑重建计数器 —— 这就是回溯的根源。常见触发场景包括：

• 删掉最大 ID 记录（如 DELETE FROM t WHERE id = 100）后重启 → MAX(id) 变成 99，新 AUTO_INCREMENT 变成 100（跳号“回退”）
• DELETE FROM t 清空表后重启 → MAX(id) 为 NULL，结果取默认偏移量（通常是 1）
• 手动插入显式 ID（如 INSERT INTO t(id) VALUES(500)）后重启 → 内存中“下一个该用几”丢了，仅靠 MAX(id)+1，可能重复或跳过

MySQL 8.0+：靠 redo log 持久化计数器

新版把每次成功分配的 AUTO_INCREMENT 值当作数据变更一样写入 redo log，并在 checkpoint 时同步到数据字典表。启动时重放 redo 日志，拿到最后已分配值，再与当前表中 MAX(id) 比较，取较大者 + 步长作为新起点。

这意味着：

• 即使你删光所有数据，只要之前插过 id=10000，重启后 AUTO_INCREMENT 仍是 10001（不会回到 1）
• SHOW CREATE TABLE 显示的 AUTO_INCREMENT=xxx 是内存快照，可能滞后；真正权威的是：SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA='db' AND TABLE_NAME='t'
• 事务回滚不影响已分配的自增值 —— 因为它已在引擎层写入 redo log，这是性能与一致性的主动取舍

MyISAM 表为什么没这问题？

因为 MyISAM 把 AUTO_INCREMENT 值直接写进索引文件 .MYI 的头部，每次修改都落盘，重启时原样读回。但注意：MyISAM 不支持事务、崩溃恢复弱，生产环境基本不用。

容易被忽略的关键点

即使在 8.0+，ALTER TABLE t AUTO_INCREMENT = N 手动设值也必须 ≥ 当前表中 MAX(id) + 1，否则下条 INSERT 可能报 Duplicate entry；另外，该持久化机制只对 InnoDB 生效，且依赖 redo log 完整性 —— 如果磁盘损坏导致日志丢失，仍可能退化为 5.7 行为。