直接DELETE大表会卡住其他查询，因其一次性加锁导致事务长、锁持有久、并发阻塞；应改用LIMIT分批删除并显式COMMIT，或按主键范围切片以避免漏删重复删。
为什么直接 DELETE 大表会卡住其他查询？
因为 DELETE FROM table WHERE ... 会尝试一次性获取满足条件的所有行的行锁（甚至升级为页锁或表锁），事务持续时间越长，锁持有时间越久，其他会话在访问相同数据页时就会被阻塞。尤其在高并发 OLTP 场景下，Waiting for table metadata lock 或 Lock wait timeout exceeded 错误频发，本质是锁粒度和事务时长失控。
用 LIMIT + WHILE 循环分批删除（MySQL / PostgreSQL 兼容写法）
核心思路：每次只删固定行数（如 1000 行），提交事务，释放锁，再删下一批。避免单事务过长、日志暴涨、锁堆积。
MySQL 示例（存储过程）：
DELIMITER $$ CREATE PROCEDURE batch_delete_expired() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE affected_rows INT DEFAULT 1; <p>WHILE affected_rows > 0 DO DELETE FROM orders WHERE status = 'cancelled' AND created_at < '2023-01-01' LIMIT 1000;</p><pre class='brush:php;toolbar:false;'>SET affected_rows = ROW_COUNT(); -- 强制提交，释放锁 COMMIT; -- 可选：休眠 100ms 减轻 I/O 压力 DO SLEEP(0.1);
END WHILE; END$$ DELIMITER ;
PostgreSQL 需改用 DELETE ... USING + CTE 或游标，但逻辑一致：控制单次影响行数 + 立即 COMMIT。
-
LIMIT是关键，不加会导致全量扫描+全量加锁 - 必须在循环体内显式
COMMIT（即使自动提交开启，也建议显式写出） - 避免在 WHERE 条件中使用未索引字段，否则每次循环都全表扫描
- 若删除条件涉及 JOIN 或子查询，优先重写为基于主键/索引字段的范围删除（如
id BETWEEN ? AND ?）
用主键范围分片替代 LIMIT（更稳定，适合超大表）
当表有自增 id 或时间戳主键时，按主键切片比 LIMIT 更可靠——不会因并发 INSERT 导致漏删或重复删。
示例（MySQL）：
SET @min_id = 1; SET @max_id = (SELECT MAX(id) FROM orders WHERE status = 'cancelled' AND created_at < '2023-01-01'); <p>WHILE @min_id <= @max_id DO DELETE FROM orders WHERE id BETWEEN @min_id AND LEAST(@min_id + 999, @max_id) AND status = 'cancelled' AND created_at < '2023-01-01';</p><p>COMMIT; SET @min_id = @min_id + 1000; DO SLEEP(0.05); END WHILE;
- WHERE 中必须同时保留业务条件（
status,created_at）和主键范围，防止误删 - 用
LEAST()防止越界，避免报错中断 - 该方式对索引友好，执行计划稳定；而
LIMIT在高并发下可能跳过新插入的匹配行
别忘了检查 autocommit 和 binlog 格式
分批删失效的常见原因不是逻辑错，而是环境配置没对齐：
- 确认
autocommit=1（默认），否则COMMIT不生效，锁仍被持有 - MySQL 若用
binlog_format=STATEMENT，带LIMIT的DELETE在从库可能行为不一致，建议设为ROW - PostgreSQL 注意
work_mem是否足够，否则 CTE 分批可能触发落盘排序，拖慢速度 - 删除前先
EXPLAIN验证是否走了索引；没走索引的分批只是“分批扫全表”，毫无意义
真正难的不是写循环，而是判断哪一列能支撑高效定位——有时候加个 (status, created_at, id) 联合索引，比调任何循环参数都管用。
