安全删除百万级大表数据必须分批DELETE，用WHERE id > last_id ORDER BY id LIMIT 1000–5000控制范围，每次删完显式COMMIT并SLEEP(0.1)，避免锁表、日志膨胀和主从延迟。
直接用 DELETE FROM table WHERE 清历史数据，90% 的线上事故都从这儿开始。它不是“慢”，而是会锁表、撑爆日志、拖垮主从、触发 Lock wait timeout exceeded ——尤其当表超 500 万行时，DBA 往往在你刚执行完就 kill 掉连接。
MySQL 存储过程里怎么写安全的 DELETE 循环
关键不是语法多复杂，而是每次只动一小块、删完立刻提交、游标能稳定推进。
- 必须用带索引的单调字段驱动（推荐
id或created_at），别依赖OFFSET：它越往后越慢，且无法避免重复或漏删 - 每次
DELETE后必须调ROW_COUNT()判断是否还有数据可删，不能硬写循环次数 - 显式加
COMMIT（或确保autocommit = 1），否则整个WHILE被包在一个事务里，undo log 不释放 - 加
DO SLEEP(0.1)让出 I/O，高负载库上这句不是可选，是保命 -
LIMIT值设 1000–5000：SSD 环境可试 5000，HDD 或写入密集场景建议 1000–2000
示例骨架（id 主键 + created_at 有索引）：
DELIMITER $$ CREATE PROCEDURE batch_delete_old() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE last_id BIGINT DEFAULT 0; DECLARE batch_size INT DEFAULT 2000; <p>WHILE NOT done DO DELETE FROM logs WHERE id > last_id AND created_at < '2022-01-01' ORDER BY id LIMIT batch_size;</p><pre class='brush:php;toolbar:false;'>IF ROW_COUNT() < batch_size THEN SET done = TRUE; ELSE SELECT MAX(id) INTO last_id FROM logs WHERE id > last_id AND created_at < '2022-01-01' LIMIT 1; END IF; DO SLEEP(0.1);
END WHILE; END$$ DELIMITER ;
PostgreSQL 为什么不用 OFFSET 而要用 RETURNING
PG 没有 MySQL 那种带 LIMIT 的非事务安全 DELETE，靠先 SELECT id 再 DELETE WHERE id IN () 容易因并发导致重复删或漏删。
- 必须基于索引字段（如
(created_at, id)复合索引）做条件，否则 CTE 扫描成本爆炸 - 用
WITH batch AS (DELETE ... RETURNING id)一次定位、一次删除、拿到结果继续下一批 - 别用
ctid当游标：VACUUM 后失效；优先用业务主键或时间+自增组合 - 每批删完立刻
COMMIT，避免长事务阻塞autovacuum
典型模式：
WITH batch AS ( DELETE FROM logs WHERE created_at < '2022-01-01' ORDER BY created_at, id LIMIT 1000 RETURNING id ) SELECT MAX(id) FROM batch;
WHERE 条件字段没索引，分批也没用
哪怕你把LIMIT 设成 10，只要 created_at 没索引，每次 DELETE 前都要全表扫描——不仅慢，还可能让优化器误判，锁住大量无关行。
- 先跑
EXPLAIN SELECT * FROM logs WHERE created_at < '2022-01-01'确认是否走索引 - 没索引就加：
ALTER TABLE logs ADD INDEX idx_created_at (created_at) - 注意：MySQL 5.6+ 可用
ALGORITHM=INPLACE在线加索引，但依然会短暂锁表，务必挑低峰操作 - SQL Server 里对应的是非聚集索引，PostgreSQL 是普通 B-tree 索引，原理一致
删完不等于磁盘空间释放
DELETE 只是逻辑标记，物理空间不会自动回收。
- MySQL InnoDB：必须后续执行
OPTIMIZE TABLE logs（本质是重建表），否则.ibd文件大小不变 - PostgreSQL：长期未
VACUUM会导致表膨胀，需手动VACUUM FULL logs（注意锁表）或等 autovacuum 渐进清理 - SQL Server：大删后建议跑
DBCC SHRINKFILE，但别频繁用，容易引发碎片
真正麻烦的从来不是“怎么删”，而是删到一半连接断了怎么办、中间被 kill 了有没有残留、删完会不会影响备份一致性——这些才是生产环境里最常卡住的地方。
