SQL如何分批删除千万级历史数据？-SQL-PHP中文网

不能直接 DELETE FROM table WHERE create_time，因为缺少比较操作符和具体时间值，语法不完整，无法构成有效SQL条件。

sql如何分批删除千万级历史数据？

为什么不能直接 `DELETE FROM table WHERE create_time`

这条语句在千万级表上大概率会卡死或触发锁超时、事务日志爆满、主从延迟飙升。InnoDB 默认走全表扫描+逐行加锁，删除过程会长时间持有行锁甚至升级为表锁；同时生成大量 undo log 和 redo log，磁盘 IO 和内存压力陡增。更糟的是，如果中途失败，回滚成本极高——你不是删数据，是在删数据库的稳定性。

用 `WHERE` + `LIMIT` 分批删，但必须带 `ORDER BY primary_key`

只写 DELETE FROM t WHERE create_time 是危险的：MySQL 不保证每次删的是“最小 ID 的那批”，可能重复删或漏删。正确做法是锚定主键（或唯一递增字段），显式排序：

DELETE FROM t 
WHERE create_time 
<p>实际更常用的是“游标式”分页（避免子查询性能衰减）：</p>

先查出当前批次最大 id：SELECT MIN(id) FROM t WHERE create_time
然后循环执行：DELETE FROM t WHERE create_time ，每次把 ? 替换为上一批删完后的最大 id
每批之间加 SLEEP(0.1)（应用层控制），避免冲击 buffer pool 和复制线程

考虑 `pt-archiver` 而不是手写 SQL

Percona Toolkit 的 pt-archiver 是专为这类场景设计的工具，它自动处理游标、限流、事务大小、错误重试和进度报告。比手写脚本更稳，尤其适合夜间批量作业：

pt-archiver \
  --source h=localhost,D=db,t=orders \
  --where "create_time 
<p>注意几个关键点：</p>

--bulk-delete 启用批量删除（比单条 DELETE 快数倍）
--sleep 控制每批间隔，单位秒，别设成 0
务必加 --where 且字段要有索引，否则全表扫描照旧
运行前用 --dry-run 检查逻辑，用 --test-mysql 验证连接权限

删完后记得 `ANALYZE TABLE` 和检查碎片

大量删除不会自动回收磁盘空间，ibd 文件大小基本不变。InnoDB 的页空闲空间只是标记为可复用，不是立即归还 OS。所以：

执行 ANALYZE TABLE t 更新统计信息，避免后续查询走错执行计划
如果确实需要收缩文件，只能 ALTER TABLE t ENGINE=InnoDB（会锁表，选业务低峰）
更轻量的做法是建新表 + INSERT INTO ... SELECT 迁移有效数据，再原子替换（需额外磁盘空间）
长期看，按时间分区（PARTITION BY RANGE (TO_DAYS(create_time))）才是千万级历史数据的正解——删整月数据只需 DROP PARTITION，毫秒级完成

分批删不是终点，而是给分区改造争取时间。别等锁住三天才想起加索引或改表结构。