MySQL 大表历史数据归档和删除如何优化？

简化版

大表清理不能一次性 DELETE 大量数据，应该按索引条件小批量删除或归档，控制事务大小和速率，监控锁、undo、redo、binlog、主从延迟，并用分区或归档表提前设计生命周期。

详细版

历史数据清理常见于日志表、订单明细、消息表。直接执行 DELETE FROM logs WHERE created_at < ... 可能扫描大量数据、持有大量锁、产生巨大 undo/redo/binlog，并导致从库延迟。

优化思路是：确保清理条件走索引，按主键或时间范围分批，每批几百到几千行，提交后短暂停顿；如果需要保留历史，先插入归档表再删除源表。执行过程中要记录进度，失败后能从上次位置继续。

如果数据天然按时间淘汰，可以提前使用分区表，按月或按天分区，过期时 DROP PARTITION 通常比逐行删除更高效。但分区表也有约束和维护成本，不能临时把所有问题都丢给分区。

完整版教学

一、为什么大 DELETE 危险

下面这条 SQL 看起来简单：

DELETE FROM logs
WHERE created_at < '2025-01-01';

如果命中 5000 万行，它会产生大量行删除、索引维护、undo log、redo log 和 binlog。从库还要重放这些删除，延迟可能迅速上升。

记忆钩子：大 DELETE 删除的是数据，制造的是锁、日志、复制延迟和回滚压力。

二、必须先让条件走索引

历史清理通常按时间字段做条件，所以要有合适索引。

CREATE INDEX idx_created_id ON logs(created_at, id);

然后分批找要删的主键：

SELECT id
FROM logs
WHERE created_at < '2025-01-01'
ORDER BY created_at, id
LIMIT 1000;

再按主键删除：

DELETE FROM logs
WHERE id IN (...);

这样能控制每批锁定范围。若没有索引，数据库可能扫描整表，清理任务就会和正常业务争抢资源。

三、为什么要小批量提交

单个巨大事务会持有大量锁和 undo，失败回滚也很慢。小批量提交可以把风险切成很多小段。

常见策略：

每批 500~2000 行
每批提交一次
批间 sleep 50~200ms
记录最后处理的 created_at 和 id

如果执行 1000 批，每批删除 1000 行，总共删除 100 万行。即使第 600 批失败，也可以从记录的进度继续，而不是回滚一个超大事务。

批大小要结合业务压力调整，不是固定数字。

四、归档和删除要分开设计

如果历史数据还要查，可以先归档再删除。

INSERT INTO logs_archive
SELECT *
FROM logs
WHERE created_at < '2025-01-01'
ORDER BY created_at, id
LIMIT 1000;

DELETE FROM logs
WHERE id IN (...);

更安全的流程是：

1. 查出本批 id
2. 插入归档表
3. 校验归档行数
4. 删除源表
5. 提交并记录进度

如果要求强一致，可以放在同一事务中；如果归档系统在异地或异构存储，则要设计幂等和补偿。

五、分区表适合按生命周期删除

如果表按时间自然淘汰，分区表可以让删除历史数据变成删除分区。

ALTER TABLE logs DROP PARTITION p202501;

相比分批删除几千万行，删除整个过期分区通常更快。但分区表要提前设计分区键、唯一键约束、查询模式和维护脚本。

方案	优点	风险
分批 DELETE	通用、可控	耗时长、日志多
归档后删除	保留历史	流程更复杂
DROP PARTITION	淘汰快	需要提前分区设计

不要为了临时清理一次历史数据，匆忙改成分区表；这本身就是大表 DDL。

六、执行中要监控什么

大表清理必须边跑边看指标。

锁等待
主库 CPU / IO
事务历史长度
binlog 生成速度
主从延迟
业务错误率
表空间变化

注意 InnoDB 删除大量行后，表空间不一定马上归还给操作系统。清理能减少逻辑数据量和索引项，但文件缩小可能需要重建表，这又是另一个大表 DDL 风险。

所以“删除后磁盘没立刻变小”不是异常，要提前和业务方解释。

七、常见误区与追问

误区：历史数据直接一条 DELETE 就行。 大事务会带来锁、日志、回滚和复制延迟风险。
误区：删除条件不用索引也没事。 没索引会扫描大量数据，严重影响线上查询和写入。
误区：删完数据磁盘一定马上变小。 InnoDB 表空间通常不会自动立刻归还给操作系统。
误区：分区表能临时解决所有清理问题。 分区需要提前设计，且有唯一键和查询模式限制。
追问：如何控制清理任务影响？ 小批量、低峰、限速、监控、可暂停和可恢复。
追问：归档如何保证不丢数据？ 先记录批次 id，归档后校验行数，再删除源表，任务要幂等。

八、加强记忆

大表清理按“索引、小批、归档、监控、生命周期设计”来答。短期用分批删除控风险，长期用分区或归档体系控增长。不要追求一次删完，线上数据库更需要稳稳地删。

简化版

详细版