平滑数据迁移：28GB预警发送日志表的在线瘦身实战

背景

MES 系统的预警发送日志表 biz_notify_send_log 长到了 28GB。它记录每条预警消息的发送流水，只增不减，而业务实际只需要查询最近一段时间的数据。表太大带来一串连锁问题：

• 磁盘空间告急；
• 带全表扫描的查询越来越慢；
• 之前分析过的一次服务假死事故里，多级预警任务扫这张表的日志表 biz_notify_send_level_log 也是内存压力的来源之一——日志类的表就该保持”瘦”。

目标很明确：只保留近 30 天 + 当天的数据，扔掉历史包袱，并且全程不停机（生产库，7×24 有预警在写入）。

方案总览

核心思路是经典的 影子表（shadow table）+ 原子切换：

建新表（同结构+补索引）
   ↓
按天分批把近 30 天数据搬进新表（INSERT IGNORE，幂等可重跑）
   ↓
补当天增量（迁移期间新产生的日志）
   ↓
RENAME 原子交换两张表（毫秒级完成"切换"）
   ↓
观察无误后，再 DROP 旧表释放 28GB

为什么不能直接 DELETE FROM ... WHERE sendtime < 30天前？三个原因：

1. DELETE 大量旧数据同样产生巨量 binlog 和锁开销，而且 InnoDB 标记删除不还磁盘——删完 28GB 表文件还是 28GB，想还空间还得 OPTIMIZE TABLE（等于重建整表，更重）；
2. DELETE 是大事务的话，主从延迟、undo 膨胀、阻塞业务，全是坑；
3. 无法”反悔”。影子表方案切换前随时可以放弃，新表 DROP 掉就行，生产库上每个操作都要有撤退路线。

逐步拆解

第 1 步：建影子表

CREATE TABLE `biz_notify_send_log_new` LIKE `biz_notify_send_log`;

CREATE TABLE ... LIKE 完整复制原表结构（列、主键、所有索引），不复制数据。新表空无一行，后续写它毫无压力。

ALTER TABLE `biz_notify_send_log_new` ADD INDEX `idx_sendtime` (`sendtime`);

顺手补一个原表没有的 sendtime 索引——迁移按时间切批要用它，切换后业务按时间查日志也受益。在空表上加索引是秒级操作；这也是影子表方案的一个隐藏福利：结构优化可以在切换前从容做好，不用在 28GB 大表上干等。

第 2 步：存储过程按天分批搬运

DELIMITER $$

DROP PROCEDURE IF EXISTS `proc_migrate_notify_log_414_idempotent`$$

CREATE PROCEDURE `proc_migrate_notify_log_414_idempotent`(
    IN p_start_date DATE, -- 起始日期，例如 '2026-07-18'
    IN p_end_date   DATE  -- 结束日期，例如 '2026-08-18'
)
BEGIN
    DECLARE v_current_day DATE;
    DECLARE v_next_day    DATE;
    DECLARE v_rows        INT DEFAULT 0;

    SET v_current_day = p_start_date;

    IF p_start_date > p_end_date THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '错误：起始日期不能大于结束日期！';
    END IF;

    WHILE v_current_day <= p_end_date DO
        SET v_next_day = DATE_ADD(v_current_day, INTERVAL 1 DAY);

        -- 使用 INSERT IGNORE 保证幂等（已存在的 id 自动跳过，绝不重复）
        INSERT IGNORE INTO biz_notify_send_log_new
        SELECT * FROM biz_notify_send_log
        WHERE factoryid = 414
          AND sendtime >= v_current_day
          AND sendtime < v_next_day;

        SET v_rows = ROW_COUNT();

        -- 输出当前进度
        SELECT CONCAT('Factory 414 | 日期: ', v_current_day, ' | 新增/写入数据量: ', v_rows, ' 条') AS progress;

        -- 暂停 0.1 秒，降低磁盘 I/O 压力
        SELECT SLEEP(0.1);

        SET v_current_day = v_next_day;
    END WHILE;

    SELECT '>>> Factory 414 幂等数据迁移完成！' AS result;
END$$

DELIMITER ;

CALL proc_migrate_notify_log_414_idempotent('2026-07-18', '2026-08-18');

这段每一个设计点都有讲究：

按天切批（sendtime >= 当天 AND sendtime < 次日）。 一次搬一天的数据，单批量可控。注意边界用的是左闭右开 [当天, 次天)——这是时间范围查询的标准姿势，天然覆盖 datetime 的时分秒，永远不会像 BETWEEN 当天 AND 次天 那样把次日凌晨 00:00:00 的记录算两遍或漏掉。

INSERT IGNORE 保证幂等。 存储过程可能中途失败（网络抖动、磁盘满、手动 kill）。重跑时已迁过的行命中主键冲突被自动忽略，只补缺失的部分——一个可无限重试的迁移才是敢在生产上跑的迁移。进度行输出”新增/写入数据量”，重跑时前一天显示 0 条就是完全追平的信号。

SELECT SLEEP(0.1)。 每批之间歇 0.1 秒，把 I/O 压力摊开。这是对同库上跑着的业务最起码的礼貌。要更狠的平滑还可以把批次切到小时级，或把 SLEEP 调大。

SIGNAL SQLSTATE '45000'。 参数校验失败直接抛错退出，而不是静默跑出 0 行还提示”迁移完成”。脚本要诚实，错了就得喊。

只迁 factoryid = 414。 这是目标工厂的数据。日志表按工厂隔离，其他工厂的历史数据不在此次的保留范围内——按需圈定，别把”瘦身”做成”搬家”。

第 3 步：补迁移期间的增量

SELECT count(1) FROM biz_notify_send_log_new;

INSERT IGNORE INTO biz_notify_send_log_new
SELECT * FROM biz_notify_send_log
WHERE factoryid = 414
  AND sendtime >= CURDATE(); -- '2026-08-19'

存储过程搬的是到”昨天”为止的数据。从跑完过程到执行切换之间，业务还在往旧表写当天的新日志。所以切换前把 sendtime >= CURDATE() 的当天增量再补一轮——还是 INSERT IGNORE，重复执行无害。

这一步和下一步必须连着做，中间越短越好。两次补增量的间隙越短，切换时刻的数据缺口越小（后面会说为什么缺口可以容忍）。

第 4 步：RENAME 原子切换

RENAME TABLE biz_notify_send_log TO biz_notify_send_log_old,
             biz_notify_send_log_new TO biz_notify_send_log;

这是整个方案的高潮：一条 RENAME TABLE 同时交换两张表的名字。MySQL 对它的实现是原子的——不存在”旧表已改名、新表还没就位”的中间态，业务连接看到的要么全是旧表、要么全是新表，切换在毫秒级完成，无需停机。

这也是为什么前面所有苦力活都在影子表上干：脏活累活（建表、搬运、补增量）都发生在业务看不见的 _new 表上，业务唯一能感知的只有最后一瞬间的原子换名。

第 5 步：核对，然后才敢删

SELECT count(1) AS cnt FROM biz_notify_send_log
WHERE factoryid = 414 AND sendtime >= CURDATE()
UNION ALL
SELECT count(1) FROM biz_notify_send_log_old
WHERE factoryid = 414 AND sendtime >= CURDATE();

切换后立刻核对：新表（已顶上业务名）与旧表（已退居 _old）里当天数据的行数。第一个数字 ≥ 第二个，说明切换后新表还在正常接收写入（业务已无缝切到新表）。

-- 1. 删除旧的大表（释放 28 GB 磁盘）
-- DROP TABLE biz_notify_send_log_old;

-- 2. 清理存储过程
-- DROP PROCEDURE IF EXISTS proc_migrate_notify_log_414_idempotent;

DROP 故意注释着，不当场执行。 旧表先留着当”后悔药”：观察一两天，业务查询、预警发送都正常，再回来执行这两条。28GB 的数据一旦 DROP 就找不回来了—— irreversible 操作永远值得多等一天。

另外两个细节：

• RENAME 之后业务写入的是新表，旧表数据冻结。所以核对逻辑才成立：如果新表计数在涨、旧表不动，说明切换生效。
• 极限情况：补增量与 RENAME 之间的毫秒级间隙里，可能有一条日志写进了旧表却被留在 _old 里。对日志表来说这个量级（毫秒窗口内的 0~几条）完全可接受；若是不能丢数据的业务表，需要先短暂禁止写入或用触发器兜底，复杂度上一个台阶。

方案的通用骨架

把表名和字段换掉，这套流程可以复用到任何”大表瘦身/在线重建”场景：

1. CREATE TABLE new LIKE old;            -- 影子表
2. （可选）ALTER new 加/改索引;            -- 空表上做结构变更，秒级
3. 分批 INSERT IGNORE new SELECT old;     -- 幂等搬运历史
4. 补增量（时间戳 >= 切换前基准点）;        -- 追平迁移期间的写入
5. RENAME 原子交换;                       -- 毫秒级切换
6. 核对 → 观察 → DROP 旧表;               -- 留后悔药

对比几个常见替代方案的坑：

方案	问题
直接 DELETE 旧数据	不还磁盘；大事务阻塞 + binlog 风暴；删错无法恢复
pt-online-schema-change / gh-ost	是”改结构”的工具，用来”删数据”绕远路且不解决”要删一大半”的场景
停机导出导入	需要 maintenance window，7×24 系统伤不起

复盘要点

1. 影子表 + 原子 RENAME 是在线改表的基石：把一切昂贵操作挪到业务不可见的表上，用一次原子换名收尾；
2. 幂等设计（INSERT IGNORE + 主键） 让迁移可以无限重试，失败不是灾难而是重跑；
3. 分批 + 限速 是对同库业务的基本尊重，批次粒度和间隔就是你的流量调节阀；
4. 不可逆操作（DROP）永远最后做、缓一缓做——留着 _old 表观察一两天，代价只是一点磁盘，换的是回滚能力；
5. 空表上补索引是免费的——顺手把原表欠的 sendtime 索引还上，业务查询直接受益。