MySQL大量DELETE后需执行OPTIMIZE TABLE才能释放磁盘空间，因其仅逻辑标记删除、不物理回收页，必须重建表（即创建新.ibd、顺序写入有效数据、原子替换）才可真正缩容。
为什么不能直接用 DELETE FROM table WHERE create_time
千万级数据单次执行会锁表、耗尽事务日志、触发超时或 OOM，MySQL 可能报 Lock wait timeout exceeded，PostgreSQL 容易卡住 WAL 归档。真实生产中，这条语句往往跑不完就失败，还拖垮线上查询。
核心矛盾在于：DELETE 是事务性操作，每行都记 undo log，全量扫描+逐行标记+清理，I/O 和内存压力极大。
- MySQL InnoDB 下，大事务会导致
innodb_log_file_size快速写满，进而阻塞其他写入 - PostgreSQL 中长事务会阻止 vacuum 清理 dead tuple，表膨胀不可逆
- 即使加了索引，WHERE 条件字段（如
create_time）若未被有效利用（比如函数包裹：DATE(create_time) ），仍会全表扫描
怎么安全分批删：用主键 or 时间字段做游标
关键不是“分多少批”，而是“每次只碰一小块连续数据，且不依赖 OFFSET（它会越来越慢）”。推荐两种游标方式：
-
主键游标（最稳）：适用于有自增
id或唯一递增字段的表。先查出最小id，再按WHERE id BETWEEN x AND y删，删完更新边界 -
时间字段游标（需谨慎）：仅当
create_time有索引、且该字段值基本单调递增（无大量重复或乱序插入）时可用。避免用ORDER BY create_time LIMIT N配合OFFSET，因为偏移越大越慢
示例（MySQL，按主键分批）：
SELECT MIN(id), MAX(id) FROM orders WHERE create_time < '2022-01-01';
拿到范围后，循环执行：
DELETE FROM orders WHERE id >= 1000000 AND id < 1010000 AND create_time < '2022-01-01';
每次删 1w 行，id 范围固定、走索引、不扫描无关数据。
每批删多少行？别迷信“1000”或“5000”
批量大小必须结合你的硬件和负载动态调：SSD 延迟低可稍大（如 5k–10k），HDD 或高并发场景建议 1k–3k；重点看执行时间是否稳定在 1s 内、binlog/vacuum 压力是否可控。
- MySQL：观察
SHOW PROCESSLIST中Time列，持续 >2s 就该减小批次 - PostgreSQL：监控
pg_stat_progress_vacuum和pg_stat_activity，避免长时间deleting状态 - 无论哪种数据库，都要在 DELETE 后加
SLEEP(0.1)（MySQL）或pg_sleep(0.1)（PG），让出 I/O 和 CPU，防止抖动影响线上请求
删完还要做什么？很多人漏掉这步
DELETE 不等于释放磁盘空间——尤其 MySQL InnoDB 表空间文件（ibdata1 或独立表空间）不会自动收缩，PostgreSQL 的 bloat 也不会自动回收。
- MySQL：对大表执行
OPTIMIZE TABLE orders（注意：会锁表，建议在低峰或用pt-online-schema-change） - PostgreSQL：运行
VACUUM FULL orders（阻塞读写）或更稳妥的VACUUM orders+ 后续CLUSTER（需额外磁盘空间） - 无论哪种，删完务必检查表统计信息是否更新：
ANALYZE orders，否则后续查询可能走错执行计划
真正麻烦的不是删数据本身，而是删完之后的碎片整理和统计同步——这两步常被跳过，结果发现查询变慢、磁盘没降、监控报警照常响。
