SQL DELETE 批量删除与性能优化

紧急止损应立即kill执行delete的线程：mysql用kill线程id，postgresql用pg_cancel_backend(pid)，sql server用kill spid；同时须严格规范delete必须带索引where条件，并分批处理避免锁表与性能恶化。

DELETE 语句没加 WHERE 就执行了，怎么紧急止损？

直接 kill 连接是最有效的止损方式。MySQL 中用 KILL <code>查到的线程 ID（先查 SHOW PROCESSLIST 找正在执行 DELETE 的线程）；PostgreSQL 用 select pg_cancel_backend(<code>pid)；SQL Server 用 KILL <code>spid。别等它自己跑完——全表扫描 + 行锁 + binlog 写入可能让数据库卡死十几分钟。

• 生产环境所有 DELETE 必须带 WHERE，且 WHERE 字段要有索引
• 开发阶段在连接串里加 sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION（MySQL），能阻止无 WHERE 的 DELETE（5.7+ 默认启用）
• 临时补救：如果已开始删但还没结束，SHOW ENGINE INNODB STATUS 可看当前事务锁情况，判断是否还能抢在锁升级前中断

为什么 LIMIT 1000 的 DELETE 还很慢？

因为 DELETE FROM t WHERE status = 'old' LIMIT 1000 不代表只扫描 1000 行——它会先全表/全索引扫描匹配所有 status = 'old' 的行，再取前 1000 条删。当匹配行数达百万级时，I/O 和锁开销巨大。

• 改用基于主键分页：先查出一批 id，再删，例如

  SELECT id FROM t WHERE status = 'old' ORDER BY id LIMIT 1000;

  拿到结果后执行

  DELETE FROM t WHERE id IN (1,2,3,...);

• 确保 WHERE 条件字段（如 status）和排序字段（如 id）有联合索引，比如 INDEX(status, id)
• 避免在大表上用 ORDER BY RAND() 或函数索引条件，它们强制全表扫描

MySQL 中 DELETE 走不了索引？检查这三点

常见现象是 EXPLAIN DELETE FROM t WHERE created_at 显示 <code>type: ALL（全表扫描）。根本原因往往不是 SQL 写错，而是索引失效。

• 字段类型不一致：created_at 是 dateTIME，但传参用了字符串 '2022-01-01' —— MySQL 会隐式转换，导致索引失效；应统一用 '2022-01-01 00:00:00'
• 使用了函数：WHERE DATE(created_at) 会让索引失效；改写为 <code>WHERE created_at
• 索引列上有 IS NULL / IS NOT NULL 判断，且该列允许 NULL：MySQL 8.0 前对 NULL 的索引支持弱，建议设为 NOT NULL default '1970-01-01' 并统一填充默认值

PostgreSQL 批量 DELETE 卡住，其实是事务膨胀

PostgreSQL 没有真正的“删除”，只是标记 dead tuple。如果批量 DELETE 在长事务里执行，或者 autovacuum 跟不上，dead tuple 积压会导致查询变慢、磁盘暴涨、甚至触发 Error: out of shared memory。

• 切忌在一个事务里删几十万行；拆成每批 1000–5000 行，中间加 COMMIT
• 检查 pg_stat_all_tables.n_dead_tup，如果远大于 n_live_tup，说明 vacuum 滞后，手动执行 VACUUM VERBOSE t;
• 对高频删除表，调高 autovacuum_vacuum_scale_factor（比如从 0.2 降到 0.05），并设小一点的 autovacuum_vacuum_threshold

大表删数据不是拼 SQL，是拼索引设计、事务粒度和 vacuum 节奏。最容易被忽略的是：删完不 vacuum，等于把问题留给下一次查询。