根本原因是mysql 5.7及更早版本中auto_increment值仅存储在innodb内存结构dict_table_struct.autoinc中，未持久化到磁盘；重启后该值丢失，mysql被迫执行select max(id)+1重建，导致回溯。
MySQL 5.7 及更早版本重启后 AUTO_INCREMENT 回溯的根本原因
根本原因是 AUTO_INCREMENT 值仅保存在 InnoDB 表对象的内存结构 dict_table_struct.autoinc 中，不写入任何持久化介质。服务一重启，这个值就彻底丢失，MySQL 被迫重新计算：执行类似 SELECT MAX(id) + 1 的逻辑来重建自增值。
这个“重建”动作看似合理，但实际埋下三类典型回溯场景：
- 删掉最大 ID 记录后重启 →
MAX(id)变小，新自增值 = 小值 + 1，跳号变回退 - 清空整张表（
DELETE FROM t）后重启 →MAX(id)为NULL，结果取默认偏移量auto_increment_offset（通常为 1） - 手动插入显式 ID（如
INSERT INTO t(id) VALUES(100)）后重启 → 内存中记录的“下一个该用多少”丢了，只靠MAX(id)+1，可能重复或跳过
show create table 显示的 AUTO_INCREMENT=XX 是假象？
是的。在 MySQL 5.7 中，SHOW CREATE TABLE 返回的 AUTO_INCREMENT=XX 值，是直接从内存里的 dict_table_struct.autoinc 读出来的，不是从磁盘查的。它反映的是“当前认为下一个该用几”，但这个“认为”本身就不持久。
你执行 INSERT、REPLACE 或显式指定 ID 后，这个内存值会更新；但只要没写盘，它就扛不住一次 service mysqld restart。
验证方法很简单：
- 插入 3 条数据 →
SHOW CREATE TABLE显示AUTO_INCREMENT=4 -
DELETE FROM t WHERE id = 3→SHOW CREATE TABLE仍显示AUTO_INCREMENT=4（内存值未变） - 重启 MySQL → 再次
SHOW CREATE TABLE，大概率变成AUTO_INCREMENT=3
为什么 MyISAM 没这问题，而 InnoDB 有？
因为 MyISAM 把 AUTO_INCREMENT 值直接写进索引文件 .MYI 的头部，每次修改都顺带落盘，重启时原样读回 —— 它本质是个文件元数据字段。
InnoDB 则不同：它的设计哲学是“所有变更走 redo log”，而旧版（5.7 及以前）没把自增计数器纳入这套机制。当时权衡性能，认为每次 INSERT 都去刷一次盘太重，于是选择内存缓存 + 重启重建的妥协方案。
这也解释了为什么修复方案直到 MySQL 8.0 才落地：必须把自增值写进 redo log 或数据字典页（最终选了后者，存在聚集索引根页页头未用的 8 字节 trx_id 位置），否则无法保证崩溃安全。
升级前临时规避回溯风险的实操建议
如果你暂时无法升级到 MySQL 8.0+，又必须用自增主键，这几个动作能大幅降低出事概率：
- 避免在业务高峰期执行
DELETE+ 重启组合操作；运维重启前先确认所有表MAX(id)是否已稳定（比如近期无 delete） - 对关键表，重启后立即执行
ALTER TABLE t AUTO_INCREMENT = N（N 设为比当前MAX(id)大 100～1000 的安全余量） - 禁用
innodb_autoinc_lock_mode=0（传统表级锁模式），改用=2（交错模式），减少并发插入时的锁争抢和隐式分配浪费 - 绝对不要依赖自增 ID 的“连续性”做业务逻辑（比如分页、ID 区间判断、归档断点）—— 它从来就不是连续的，只是单调递增
最常被忽略的一点：哪怕你没删数据，只要某次事务里用了 INSERT ... SELECT 或批量插入，InnoDB 就可能预分配一大段 ID 并写入内存，这部分“已分配但未使用”的 ID 在重启后永远丢失，且不可回收。这不是 bug，是旧版设计里明确定义的行为。
