历史表数据量持续增长后，最先被拖累的往往不是磁盘空间，而是查询性能和写入稳定性。当单表行数从几百万涨到几千万甚至上亿，原本走二级索引的范围查询可能需要回表大量数据，统计信息也容易失真，优化器可能选择全表扫描。与此同时，备份、归档、DDL操作的时间都会成倍拉长。本文从评估数据分布、设计归档表、分批清理和作业监控四个角度展开，重点讨论如何在不影响在线业务的前提下，把历史数据从活跃表转移到归档表并逐步清理。
一、先评估历史表的数据规模和分布
处理历史表膨胀不能只看总行数，还要看数据时间跨度、索引体积和每月新增量。如果数据主要集中在最近三个月，历史归档的空间有限；如果三年前的数据仍占一半以上，就非常适合做归档。先通过系统表查看表与索引占用，再按时间维度统计每个月的行数，这样能确定保留策略和清理窗口。
SELECT
table_name,
table_rows,
ROUND(data_length / 1024 / 1024, 2) AS data_mb,
ROUND(index_length / 1024 / 1024, 2) AS index_mb,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'order_db'
AND table_name = 'order_history';
上述查询可以快速看到行数和索引占用。如果索引占比接近甚至超过数据体积，说明二级索引已经明显膨胀，此时即使只清理部分旧数据也能带来较大收益。接着按月份统计行数，判断哪些月份的数据已经进入冷数据区间。例如订单历史表保留最近180天，超过180天的数据就属于可归档对象。
SELECT
DATE_FORMAT(create_time, '%Y-%m') AS month_label,
COUNT(*) AS row_count,
MIN(create_time) AS min_time,
MAX(create_time) AS max_time
FROM order_history
GROUP BY DATE_FORMAT(create_time, '%Y-%m')
ORDER BY month_label ASC;
通过这份月份分布数据，就可以比较准确地估算出需要迁移的行数、占用空间以及归档后的效果。不要等磁盘真正告警才动手，很多情况下磁盘空间还在安全水位，但查询性能已经因为数据和索引规模过大而下降。评估完数据分布后，接下来要决定是采用归档表、分区交换还是直接导出文件。
二、设计归档表并转移冷数据
归档的核心是把不经常访问的旧数据从活跃表搬走，而不是直接删除。这样当业务需要回溯时，仍可以从归档表或归档文件中恢复。常见的归档方式有三种：同库归档表、分区交换、导出文件。同库归档表最简单，新建一个结构一致的表，把符合条件的数据分批迁入；分区交换速度快，但对分区设计要求较高；导出文件适合数据量大且访问频率极低的场景。
如果历史表是分区表，并且按时间范围分区，可以使用分区交换快速剥离整个分区。例如历史表 order_history 按月份分区，执行分区交换前需要保证归档表结构与分区表完全一致，并且归档表为空。交换完成后，整个老分区的数据会瞬间移动到归档表，业务表不再包含这部分数据。
-- 创建与分区表结构一致的归档表 CREATE TABLE order_history_archive_202301 LIKE order_history; -- 将2023年1月的分区数据交换到归档表 ALTER TABLE order_history EXCHANGE PARTITION p202301 WITH TABLE order_history_archive_202301;
大多数情况下表并不是分区表，此时可以先创建归档表，再分批迁移。迁移不要使用一条 INSERT INTO archive SELECT ... 完成，因为长事务可能导致undo持续膨胀、主从延迟上升。建议在应用侧或脚本里按主键或时间范围切分，每批处理500到2000行。下面给出一个MySQL存储过程模板，它按照时间条件分批删除旧数据，前提是这些数据已经由归档脚本确认写入归档表。
DELIMITER $$
CREATE PROCEDURE purge_order_history(IN p_cutoff DATE, IN p_batch_size INT)
BEGIN
DECLARE v_last_id BIGINT DEFAULT 0;
DECLARE v_affected INT DEFAULT 1;
SELECT IFNULL(MAX(order_id), 0)
INTO v_last_id
FROM order_history
WHERE create_time < p_cutoff;
WHILE v_affected > 0 DO
DELETE FROM order_history
WHERE order_id <= v_last_id
AND create_time < p_cutoff
ORDER BY order_id
LIMIT p_batch_size;
SET v_affected = ROW_COUNT();
DO SLEEP(0.05);
END WHILE;
END$$
DELIMITER ;
这个存储过程会先找到符合时间条件的最大的主键值，然后每次只删除一小批记录，提交后短暂休眠，避免长时间占用锁资源。需要注意的是，如果表的主键不是自增整型，或者业务上对主键顺序有特殊依赖，游标方式需要根据实际索引结构调整。归档前最好先验证归档表和原表的数据一致性，防止误删。
三、分批删除时的锁与空间释放问题
历史数据清理最容易踩的坑，是把几十万行乃至上千万行的删除放在一个事务里执行。InnoDB的undo log需要保留到事务提交，长事务不仅会让回滚段急剧膨胀，还可能阻塞其他修改操作，并且主从复制的延迟会在提交那一刻集中爆发。实际做法是拆成小事务，每次删除数百到数千行，提交后短暂休眠，让从库有机会追上。
清理完成后，很多管理员发现磁盘空间没有下降。这是因为InnoDB表空间并不会因为DELETE自动归还给操作系统，只是标记页面可复用。如果业务无法接受重建表造成的锁表，可以暂时保留这部分空闲空间，供后续写入复用；如果确实需要归还磁盘，可以在低峰期执行 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB，但这类操作会重建表，对大表时间较长，需要提前评估窗口。
-- 更新统计信息，帮助优化器生成更好的执行计划 ANALYZE TABLE order_history; -- 低峰期重建表，压缩碎片并释放空间 OPTIMIZE TABLE order_history;
如果清理频率较高，而不想频繁重建表，也可以接受表空间暂时不归还的状态，让后续的写入复用这些空闲页面。重点是监控碎片率和实际占用，避免误以为清理脚本没有生效。另一个实用思路是使用Percona Toolkit中的pt-archiver工具，它可以在归档的同时删除数据，并支持限速、暂停等控制参数，适合无法将业务逻辑写进存储过程的场景。
四、建立定时归档与监控机制
归档和清理不是一次性的工作，而要变成可重复执行的维护任务。可以根据数据保留周期设定参数，例如订单历史表只保留最近180天，超过180天的数据每天凌晨自动归档并删除。MySQL可以通过事件调度器执行存储过程，PostgreSQL可以使用pg_cron，SQL Server可以使用Agent作业。下面以MySQL事件为例，每天凌晨2点执行清理过程。
SET GLOBAL event_scheduler = ON;
CREATE EVENT evt_purge_order_history
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 DAY
DO
CALL purge_order_history(DATE_SUB(CURDATE(), INTERVAL 180 DAY), 1000);
事件任务还需要配合监控，否则某天脚本执行失败可能无人发现。监控指标可以包含活跃表行数、归档表行数、磁盘占用、最后归档时间和主从延迟。下面这条查询可以同时观察活跃表和归档表的大小变化，如果归档表持续增大而活跃表没有下降，说明清理条件可能写错。
SELECT
table_name,
table_rows,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb
FROM information_schema.tables
WHERE table_schema = 'order_db'
AND table_name IN ('order_history', 'order_history_archive')
ORDER BY total_mb DESC;
最后要给归档和清理操作留好日志。可以在归档表中增加 archived_at 字段，记录每条数据被归档的时间，或者在单独的维护日志表中记录每次作业的开始时间、结束时间、处理行数和错误信息。这样一旦出现问题，可以根据日志快速定位是迁移阶段、删除阶段还是调度阶段出了差错，而不需要逐条翻看数据。历史表膨胀的治理从来不是一次性的删除操作，而是评估、归档、清理、监控组合起来的持续维护流程。