MySQL5.7主键id自增，断电重启后，缓存的id将丢失带来的灾难

上周三凌晨两点，老张被电话炸醒——线上系统数据乱了。订单执行表里出现了id重复的记录，关联查询全部跑偏，历史数据跟活跃数据串了。查了三个小时，最终定位到的原因竟然是一次断电重启。

说白了，就是MySQL 5.7自增id在断电后"失忆"了。

事情是怎么发生的

先说场景。系统里有两张表：

执行表A：存当前正在执行的记录，id自增
历史表B：和A表结构完全相同，但做了分表处理

业务逻辑不复杂：A表的数据满足一定条件后，搬进B表，然后从A表删除。典型的"活跃-归档"模式，很多业务系统都这么干。

问题出在一次意外的断电重启。

重启之后，A表新插入的数据，id居然用到了之前已经删除过的值。而这些id对应的数据，早就搬进B表了。结果就是——A表和B表出现了相同的id，但指向的是完全不同的业务数据。所有基于id的关联、去重、对比逻辑，全废了。

为什么会"失忆"

这是MySQL 5.7 InnoDB引擎的一个设计特点，严格来说不算bug，但确实是个坑。

在MySQL 5.7中，InnoDB的自增计数器（auto_increment counter）是存在内存里的，而不是持久化到磁盘。数据库正常运行时，计数器递增没问题。但一旦非正常关闭（断电、kill -9、崩溃），内存里的计数器就丢了。

重启之后，InnoDB会这么算新的自增值：

新自增值 = SELECT MAX(id) FROM 表

看着没问题？但如果之前有删除操作，MAX(id)就会比内存里记的值小。于是新插入的数据，就会用到已经"释放"的id。

举个具体例子：

A表插入了id=1到100的记录
id=1到80的数据搬进B表，A表删除这80条
此时A表里id=81到100，内存里自增计数器是101
断电了
重启后，InnoDB算MAX(id)=100，新自增值变成101

这个例子还好，id没有真正冲突。但如果删除得更彻底：

A表插入了id=1到100
全部搬到B表，A表清空
内存里自增计数器是101
断电重启
InnoDB算MAX(id)=NULL，新自增值从1开始

这下id=1到100就重复了。A表的新数据和B表的旧数据撞id了。

复现方案

光说不练假把式，下面给出完整的复现步骤，可以在本地MySQL 5.7环境验证。

环境准备

-- 创建执行表A
CREATE TABLE table_a (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 创建历史表B
CREATE TABLE table_b (
    id INT PRIMARY KEY,
    content VARCHAR(100),
    created_at TIMESTAMP
) ENGINE=InnoDB;

正常业务流程

-- 步骤1：向A表插入数据
INSERT INTO table_a (content) VALUES ('task_1'), ('task_2'), ('task_3'), 
('task_4'), ('task_5');

-- 步骤2：满足条件，搬迁到B表并删除A表数据
INSERT INTO table_b SELECT * FROM table_a WHERE id <= 3;
DELETE FROM table_a WHERE id <= 3;

-- 此时A表有id=4,5，自增计数器=6
SELECT * FROM table_a;
-- +----+---------+---------------------+
-- | id | content | created_at          |
-- +----+---------+---------------------+
-- |  4 | task_4  | 2025-01-01 10:00:00 |
-- |  5 | task_5  | 2025-01-01 10:00:01 |
-- +----+---------+---------------------+

模拟断电重启

# 模拟断电：直接杀掉MySQL进程（不要用正常关闭）
kill -9 $(pidof mysqld)

# 重启MySQL
systemctl start mysqld

验证id重复

-- 重启后插入新数据
INSERT INTO table_a (content) VALUES ('task_6');

-- 查看新数据的id
SELECT * FROM table_a;
-- 预期结果：新数据的id可能是1、2或3（已被B表使用过的id）
-- 而不是期望的6

-- 对比B表
SELECT * FROM table_b;
-- B表中已有id=1,2,3的记录

-- 冲突！A表和B表出现了相同id

如果A表被清空后断电，重启后新数据会从id=1开始，和B表完全重叠。这就是灾难。

也可以用更快捷的方式验证，不用真的断电：

-- 插入数据后删除
INSERT INTO table_a (content) VALUES ('test');
DELETE FROM table_a;

-- 正常重启（这个场景下也会重现）
-- 或者直接kill -9后重启

解决方案

知道了原因，解决起来思路就清晰了。分短期止血和长期根治。

方案一：升级到MySQL 8.0（根本解决）

MySQL 8.0把自增计数器的值持久化到了redo log里，重启后不会丢失。这是最彻底的方案。

-- MySQL 8.0中，自增计数器的变化会写入redo log
-- 崩溃恢复时会从redo log中恢复最新值
-- 不再依赖SELECT MAX(id)重新计算

但升级数据库不是闹着玩的，需要充分测试兼容性，短期不一定能落地。

方案二：在业务层维护id分配

不依赖数据库的自增，用Redis或独立序列服务来生成id。

import redis

r = redis.Redis(host='localhost', port=6379, db=0)

def get_next_id():
    return r.incr('table_a_id_seq')

# 插入时指定id
next_id = get_next_id()
cursor.execute(
    "INSERT INTO table_a (id, content) VALUES (%s, %s)", 
    (next_id, 'task_x')
)

Redis的incr是原子操作，数据也持久化在磁盘上（配置AOF或RDB），重启不会丢。但引入了新的依赖，架构变复杂了。

方案三：修改归档逻辑，保留最大id（最实用的短期方案）

核心思路：不要删除A表的最新记录，让MAX(id)始终大于已归档的最大id。

-- 归档时，不要删除A表中最大的那条记录
-- 或者：删除后立刻插入一条占位记录
INSERT INTO table_b SELECT * FROM table_a WHERE id <= 80;
DELETE FROM table_a WHERE id <= 80;

-- 保留一条哨兵记录，确保MAX(id)不会回退
-- 如果A表必须清空，插入一条占位记录：
INSERT INTO table_a (content) VALUES ('__placeholder__');

简单粗暴但有效。只要A表里始终有一条id大于已归档记录的行，重启后MAX(id)就不会回退。

占位记录可以在下次真实插入前删除，不影响业务。

方案四：使用自增步长+起始偏移

给A表和B表设置不同的自增起始值和步长，即使id回退也不会和已归档数据重叠。

-- A表：id从1开始，步长2（奇数）
ALTER TABLE table_a AUTO_INCREMENT=1;
SET @@auto_increment_increment=2;
SET @@auto_increment_offset=1;

-- B表：id从2开始，步长2（偶数）
-- 或使用独立的id序列

这个方案的问题是步长是全局设置，影响所有表。生产环境一般不这么搞。更适合的场景是双主互备，而不是这里的问题。

方案五：归档时不删除，用状态字段标记

彻底避免删除操作，自增值就永远不会回退。

-- A表增加状态字段
ALTER TABLE table_a ADD COLUMN status TINYINT DEFAULT 1 COMMENT '1-执行中 2-已归档';

-- 归档操作改为更新状态
INSERT INTO table_b SELECT * FROM table_a WHERE status = 1 AND 满足条件;
UPDATE table_a SET status = 2 WHERE 满足条件;

-- 查询时过滤状态
SELECT * FROM table_a WHERE status = 1;

数据不删除，MAX(id)永远是最大的，重启后自增不会回退。代价是A表会越来越大，需要定期清理很老的已归档记录。但至少id不会重复。

阶段	方案	理由
短期止血	方案三（哨兵记录）	零侵入，改动最小
中期优化	方案五（状态字段）	根除删除导致的问题
长期根治	方案一（升级8.0）	从引擎层面解决

还要注意的

这个问题不仅影响A表B表这种归档场景。任何有大量DELETE操作的表，在MySQL 5.7下断电重启后都可能碰到自增id回退。比如：

临时表清空后重启
定期清理过期数据的表
使用DELETE而非TRUNCATE清表的场景

如果你还在用MySQL 5.7，赶紧检查一下线上有没有类似的归档+删除逻辑。断电这种事，不是会不会发生的问题，是什么时候发生的问题。

别等到凌晨两点被电话叫醒才想起来查。

MySQL5.7主键id自增，断电重启后，缓存的id将丢失带来的灾难