SQL批量删除大表数据_分批删除与限速控制

大表批量删除须分批删、限速控、避长事务；单次删百万级易锁表、涨日志、拖垮性能；应按主键分批、加延迟、走索引、删后更新统计信息。

大表批量删除数据不能直接用 delete FROM table，否则容易锁表、占满事务日志、拖垮数据库性能。核心思路是：分批删 + 限速控 + 避免长事务。

为什么要分批删除？

单次删除百万级以上数据会触发：

• 长时间持有行锁或表锁，阻塞其他读写操作
• 事务日志暴涨，可能填满日志文件（尤其在 FULL 恢复模式下）
• 回滚段压力大，失败后恢复慢
• mysql 可能触发 OOM 或被 kill，SQL Server 容易出现阻塞链

怎么安全地分批删除？

以主键（或唯一有序字段）为切片依据，每次只删固定数量的记录：

MySQL 示例（按主键 id 分页）：

SET @batch_size = 10000; SET @low_id = 0; SET @high_id = 0; <p>WHILE @high_id < (SELECT MAX(id) FROM orders WHERE status = 'cancelled') DO SELECT @high_id := MIN(id) FROM orders  WHERE id > @low_id AND status = 'cancelled'  ORDER BY id LIMIT 1 OFFSET @batch_size - 1;</p><p>DELETE FROM orders  WHERE id > @low_id AND id <= @high_id AND status = 'cancelled';</p><p>SET @low_id = @high_id; DO SLEEP(0.1); -- 控制节奏 END WHILE;

SQL Server 示例（使用 TOP + 循环）：

DECLARE @BatchSize INT = 5000; WHILE (1=1) BEGIN   DELETE TOP (@BatchSize) FROM sales_log    WHERE created_date < '2020-01-01'; <p>IF @@ROWCOUNT < @BatchSize BREAK;</p><p>WAITFOR DELAY '00:00:00.2'; -- 暂停 200ms END

如何实现限速与资源保护？

不光控制“删多少”，更要控制“删多快”：

• 加延迟：每批后 SLEEP（MySQL）或 WAITFOR DELAY（SQL Server），避免 CPU 和 I/O 扛不住
• 查负载再删：可结合 SHOW PROCESSLIST 或 sys.dm_exec_requests 判断当前阻塞/等待情况，高负载时跳过本轮
• 监控日志增长：SQL Server 注意 log_reuse_wait_desc；MySQL 关注 innodb_log_waits
• 避免全表扫描：WHERE 条件必须走索引，否则分批也慢——先确认执行计划是否用了索引

其他关键注意事项

别忽略这些细节：

• 删除前备份关键数据，或先在测试库验证逻辑和耗时
• 避免在业务高峰期执行；设好超时和中断机制（如时间限制、行数上限）
• 删除后及时 ANALYZE TABLE（MySQL）或更新统计信息（SQL Server），防止后续查询走错执行计划
• 如果只是清空整表且无需条件筛选，优先用 TRUNCATE TABLE（注意不可回滚、重置自增、需更高权限）