千万级大表快速删除大量数据（MySQL）实战方案

CodeAmaz

1374人浏览 · 2026-01-05 11:48:55

CodeAmaz · 2026-01-05 11:48:55 发布

千万级大表快速删除大量数据（MySQL）实战方案

目标：在 千万级/亿级 表里删除大量历史数据，尽量做到：快、对线上影响小、可回滚/可验证。
适用：MySQL/InnoDB（常见电商订单、日志、事件表等）。

0. 先说真相：`DELETE WHERE ...` 一把梭通常会把你打爆

在大表上直接：

DELETE FROM t
WHERE create_time < '2024-01-01';

常见后果：

持有大量行锁/间隙锁，影响线上写入
产生巨量 undo/redo、binlog，IO 飙升
复制延迟、主从追不上
事务过大，回滚也能把你拖死
表空间不回收（InnoDB 文件一般不会自动变小）

所以“快删大量数据”的核心思路是：避免超大事务，减少锁冲突，把删除变成可控的小步或结构性操作。

1. 最推荐：按时间/范围分区（Partition）+ `DROP PARTITION` ✅（最快）

1.1 适用条件

删除条件天然按时间/范围（例如 create_time / dt / biz_date）
可以接受分区表（有一定 DDL 成本）

1.2 思路

把表按月份/天分区，删除历史数据 = 直接丢分区：

ALTER TABLE t DROP PARTITION p202401;

特点

几乎是元数据操作，极快
锁时间短，线上影响小
binlog 相对可控（但仍要关注复制）

1.3 落地建议

未来数据：新表直接用分区
旧表改分区：需要在线 DDL 规划（大概率要改造窗口期）

2. 第二推荐：新表重建（CTAS/回填）+ 原子改名 ✅（结构性“秒删”）

当你要删的比例特别大（比如删 70% 以上），最爽的方式往往不是 delete，而是：

把要保留的数据复制到新表，然后 swap 表名。

2.1 步骤（通用做法）

建新表（同结构/索引）
只把要保留的数据导入新表
校验行数/抽样校验
原子改名切换
旧表延后再 drop（留回滚窗口）

示例：

-- 1) 建新表（包含索引）
CREATE TABLE t_new LIKE t;

-- 2) 回填保留数据（建议分批）
INSERT INTO t_new
SELECT * FROM t
WHERE create_time >= '2024-01-01';

-- 4) 原子切换（瞬间完成）
RENAME TABLE t TO t_old, t_new TO t;

-- 5) 观察一段时间后再删旧表
DROP TABLE t_old;

2.2 什么时候用它

删除比例很大
想“快速见效”且可控
接受一次较大的回填 IO（可离峰执行）

3. 线上可控：分批 DELETE（Chunk Delete）✅（最常用、最稳）

3.1 原则

小事务：每次删 1k~10k 行
走索引：where 条件必须命中索引（否则全表扫=慢+锁大）
按主键/索引顺序：避免随机 IO
每批 sleep：给线上留呼吸空间

3.2 推荐 SQL 模式：先取 id，再删（避免大范围锁）

假设按时间删，且有索引 (create_time, id)：

-- 取一批主键
SELECT id
FROM t
WHERE create_time < '2024-01-01'
ORDER BY create_time, id
LIMIT 5000;

-- 按主键删除（主键命中，锁更小）
DELETE FROM t
WHERE id IN ( ...上面那批 id... );

说明：直接 DELETE ... ORDER BY ... LIMIT ... 也能用，但“先取 id 再删”更可控，日志也更好打。

3.3 Java / Spring 定时任务伪代码

while (true) {
  List<Long> ids = mapper.selectIdsToDelete(cutoff, 5000);
  if (ids.isEmpty()) break;

  int deleted = mapper.deleteByIds(ids);

  // 限速，避免打爆主库/复制
  Thread.sleep(50);
}

3.4 关键索引建议

如果按时间删：建 (create_time, id) 复合索引
如果按业务字段删：保证 where 条件能命中高选择性索引

4. “立即释放空间”：为什么删完磁盘不降？

InnoDB 常见行为：

DELETE 只是把页内记录标记为可复用
.ibd 文件通常不自动变小

要“真正释放磁盘”通常要：

OPTIMIZE TABLE t;（本质重建表，重且可能需要窗口期）
或用 方案 2：新表重建（更可控）
或分区表 drop partition（最优）

5. 复制/日志/稳定性：你必须关注的线上风险点

5.1 binlog/主从延迟

大量 delete 会产生大量 binlog，主从可能追不上。建议：

离峰执行
降低单批 size
监控 Seconds_Behind_Master / 复制延迟
必要时暂停/限速删除

5.2 锁冲突

避免长事务；where 走索引；尽量按主键删除；必要时降低隔离级别（谨慎）或调度到低峰。

5.3 外键（强烈不建议在大表上用）

有外键级联删除会非常慢且不可控。若存在：

考虑先解除/改造逻辑
或走“新表重建”策略规避

6. 选型建议（直接抄）

6.1 你删的是“按月/按天历史数据”

✅ 分区表 + DROP PARTITION（长期最佳）
短期：分批 delete 或新表重建。

6.2 你要删 50%~90%

✅ 新表重建 + 原子改名（通常比 delete 更快更稳）

6.3 你要删 5%~30%，但持续发生（每天清理）

✅ 分批 delete + 限速 + 索引（最现实）

7. 上线前检查清单（2 分钟）

删除条件是否命中索引？（EXPLAIN 看 rows）
单批删除行数是多少？（建议 1k~10k 起步）
是否会打爆 binlog/主从？（是否有监控/限速）
是否需要释放磁盘空间？（需要就考虑重建/分区）
是否能接受新表重建窗口？（删除比例大时优先）
是否有外键/触发器？（有就谨慎）

8. 经验参数（给你一个起点）

每批 2000~5000 行：多数线上比较安全
每批 sleep 20~100ms：看 QPS 和复制延迟调
先在从库/压测环境跑一遍：估算删除总时长与影响
监控指标：
- CPU/IO、Innodb_row_lock_time
- redo/undo 增长、binlog 量
- 主从延迟、慢查询
- 业务写入延迟/失败率