分批删除历史数据需避免单次大事务，应通过WHERE条件+ORDER BY+LIMIT分页删除，每次更新last_id并显式COMMIT释放undo log。
分批删除历史数据不是“加个 LIMIT 就完事”，直接 DELETE FROM table WHERE created_at 可能锁表、OOM、拖垮主库。核心是控制每次删的行数、避免长事务、减少锁竞争。
为什么不能一次性 DELETE 大量数据？
MySQL/PostgreSQL 中，大范围 DELETE 会：占用大量 undo log（MySQL）或事务快照（PG），触发 autovacuum 压力（PG），阻塞 DML；InnoDB 的聚簇索引删除还会引发页分裂和重排；SQL Server 则可能快速填满日志文件。线上表哪怕只有 500 万行，没分批就跑，大概率被 DBA 中断或引发告警。
MySQL 存储过程中用 WHILE + LIMIT 分批删除
关键点不是语法多炫，而是每次只删固定行数、用主键/时间字段驱动游标、删完立即 COMMIT。别用 OFFSET——它越往后越慢。
- 用
WHERE id > last_id ORDER BY id LIMIT N替代OFFSET - 每次循环后更新
last_id为本次最后删掉的id值（可用SELECT MAX(id)或ROW_COUNT()辅助判断） - 在循环内显式加
COMMIT，否则整个 WHILE 被包在一个事务里，undo log 不释放 - 示例骨架：
DECLARE done INT DEFAULT FALSE; DECLARE batch_size INT DEFAULT 10000; DECLARE max_id BIGINT DEFAULT 0; WHILE NOT done DO DELETE FROM orders WHERE status = 'archived' AND created_at < '2022-01-01' AND id <= (SELECT id FROM orders WHERE created_at < '2022-01-01' ORDER BY id DESC LIMIT 1 OFFSET 0) ORDER BY id ASC LIMIT batch_size; IF ROW_COUNT() = 0 THEN SET done = TRUE; END IF; COMMIT; END WHILE;
PostgreSQL 中用 DELETE ... RETURNING 避免重复扫描
PG 没有 MySQL 那种带 LIMIT 的非事务安全 DELETE，但 DELETE ... RETURNING 可以一次定位、一次删除、拿到结果继续下一批——比先 SELECT id 再 DELETE WHERE id IN (...) 更安全高效。
- 必须基于索引字段（如
created_at+id复合索引）做条件 - 用 CTE 包裹
DELETE并RETURNING id，再用该 id 作为下一批起点 - 不要依赖
ctid，它在 VACUUM 后失效；优先用业务主键或时间+自增组合 - 典型模式：
WITH batch AS ( DELETE FROM logs WHERE created_at < '2021-01-01' AND id <= (SELECT MIN(id) FROM ( SELECT id FROM logs WHERE created_at < '2021-01-01' ORDER BY id DESC LIMIT 10000 ) t) RETURNING id ) SELECT COUNT(*) FROM batch;
SQL Server 的 TOP + WHILE 容易卡住 tempdb
SQL Server 的 TOP N 在大表上配合 ORDER BY 可能触发排序溢出到 tempdb，尤其当 created_at 没建索引时。更糟的是，DELETE TOP (N) 若不加 WHERE 过滤，会随机删——这不是你想要的“按时间删”。
- 必须确保
WHERE条件字段（如created_at）有索引，且包含在ORDER BY中 - 避免
DELETE TOP (10000) FROM t ORDER BY created_at—— 这语句非法；正确写法是DELETE TOP (10000) FROM t WHERE created_at - 每批后加
WAITFOR DELAY '00:00:00.1'给 I/O 和日志留喘息时间，防日志暴涨 - 若日志模式是 FULL，确认备份链正常，否则
LOG_FULL错误会中断过程
真正难的不是写出能跑的存储过程，而是判断哪条索引有效、估算每批耗时是否超过监控阈值、以及删到一半失败时如何幂等续删——这些没法靠模板解决，得看执行计划和 EXPLAIN ANALYZE 输出。
