SQL 大表删除数据的正确方式_技术教程

千万级表直接DELETE易致日志暴涨、锁表、主从延迟甚至OOM；应分批删除（主键范围切片+显式COMMIT+限流）或重建表（导出保留数据+原子替换）。

大表 `DELETE` 为什么卡死或失败

直接对千万级以上的表执行 DELETE FROM table WHERE ... 极大概率导致事务日志暴涨、锁表时间过长、主从延迟飙升，甚至触发 OOM 或被 DBA 杀掉。

根本原因在于：InnoDB 默认逐行加锁 + 记录完整 undo log，且整个语句是一个大事务，回滚段压力巨大。

常见现象包括：Lock wait timeout exceeded、Query execution was interrupted、从库 SQL 线程长时间 Waiting for table metadata lock。

别用单条 DELETE 清理超过 10 万行的数据
避免在业务高峰期执行，尤其不要在主库上直接删
确认表有合适索引——WHERE 条件字段必须走索引，否则会全表扫描+锁全表

分批删除的可靠写法（MySQL）

核心是控制每次删除的行数、显式提交、避开长事务。推荐用主键范围或自增 ID 切片，比 LIMIT 更稳定（LIMIT 在高并发更新下可能跳过或重复）。

示例：删除 orders 表中创建时间早于 2025 年的记录

SET @batch_size = 5000;
SET @low_id = 0;
SET @high_id = 0;
WHILE @high_id < (SELECT MAX(id) FROM orders WHERE created_at < '2022-01-01') DO
SELECT MIN(id) INTO @low_id FROM orders 
WHERE id > @high_id AND created_at < '2022-01-01' 
LIMIT 1;
IF @low_id IS NULL THEN LEAVE; END IF;
SELECT MIN(id) INTO @high_id FROM orders 
WHERE id >= @low_id AND created_at < '2022-01-01' 
ORDER BY id DESC LIMIT 1;
DELETE FROM orders 
WHERE id BETWEEN @low_id AND @high_id 
AND created_at < '2022-01-01';
COMMIT;
DO SLEEP(0.1); -- 控制节奏，减轻主从压力
END WHILE;

每次只删一个连续 ID 段，避免 LIMIT 的不确定性
COMMIT 必须显式调用，不能依赖自动提交（存储过程默认 autocommit=0）
DO SLEEP(0.1) 是关键缓冲，防止瞬间 IO 和复制压力打满
如果表无主键或主键不连续，改用时间字段分段（但需确保该字段有索引且值唯一性足够）

更安全的替代方案：改名 + 重建

当要删除的是历史归档数据（比如删掉 90% 以上），物理重建比逻辑删除更快、更干净，且几乎不锁原表。

步骤本质是「把要保留的数据导出 → 建新表 → 导入 → 原子替换」：

CREATE TABLE orders_new LIKE orders;
INSERT INTO orders_new SELECT * FROM orders WHERE created_at >= '2025-01-01';
RENAME TABLE orders TO orders_old, orders_new TO orders;

全程原表 orders 只在最后 RENAME 那一瞬不可写（毫秒级），业务无感
要求磁盘空间充足——临时表 + 原表共存期间占用双倍空间
外键、触发器、注释等需手动迁移，SHOW CREATE TABLE 可辅助
如果表有全文索引或 JSON 列，注意 MySQL 版本兼容性（5.7+ 对 RENAME 支持更稳）

PostgreSQL 怎么办：用 `DELETE ... WITH` 分事务

PG 没有 LIMIT 在 DELETE 中的语法，也不能在单语句里用变量，推荐用 CTE + WITH 循环配合应用层控制。

典型做法是在应用代码里循环执行：

DELETE FROM logs 
WHERE id IN (
  SELECT id FROM logs 
  WHERE created_at < '2022-01-01' 
  ORDER BY id 
  LIMIT 10000
);

每次只删 10k 行，应用层判断 rowcount == 0 退出循环
务必在 created_at 和 id 上建复合索引：CREATE INDEX idx_logs_time_id ON logs(created_at, id)
避免用 OFFSET 分页，深度分页性能断崖式下降
如果启用了 hot_standby_feedback = on，长事务可能导致备库查询被阻塞，所以每批必须及时提交

真正难的不是写出能跑的语句，而是判断「此刻该用分批删、还是重建、还是切归档库」——这取决于剩余数据比例、磁盘余量、主从延迟容忍度，以及你有没有权限停写。漏掉任一条件，都可能让操作从优化变成事故。