单次大 delete 会锁表，因扫描范围大、无合适索引或事务未提交导致行锁升级为间隙锁或表锁；根本原因是持锁时间长而非删除量大。
为什么单次大 Delete 会锁表
MySQL 在执行 DELETE FROM t WHERE ... 时，InnoDB 默认走行级锁，但若扫描范围大、没有合适索引或事务未提交，就会升级为间隙锁甚至表级锁。更常见的是长事务阻塞后续 DML，导致 SHOW PROCESSLIST 里一堆 Waiting for table metadata lock。
根本原因不是“删得多”，而是“一次扫得太多 + 持锁时间太长”。尤其在主从架构下，大 Delete 还会拖慢 binlog 回放，引发从库延迟。
用 LIMIT 分批删除的实操要点
核心是控制每次影响行数，并确保 WHERE 条件能稳定推进（避免漏删/重复删）。
- 必须有高效索引支撑 WHERE 条件，否则每次
SELECT ... LIMIT都全表扫描，反而更慢 - 推荐用主键或唯一递增字段分页，例如：
WHERE id > ? ORDER BY id LIMIT 1000，别用OFFSET - 每次执行后检查
ROW_COUNT()，等于 0 就停；小于预期说明删完了 - 加
SLEEP(0.1)（毫秒级）缓解主库压力，避免瞬间 IO/CPU 冲高
示例循环逻辑（伪代码）：
SET @last_id = 0; WHILE ROW_COUNT() > 0 DO DELETE FROM orders WHERE id > @last_id AND status = 'cancelled' ORDER BY id LIMIT 1000; SELECT @last_id := MAX(id) FROM orders WHERE id > @last_id AND status = 'cancelled' LIMIT 1; DO SLEEP(0.05); END WHILE;
替代方案：用临时表 + RENAME 原子切换
适合可离线维护的场景，比如凌晨清理历史日志表。本质是“重建”而非“删除”，完全规避锁竞争。
- 先
CREATE TABLE orders_new AS SELECT * FROM orders WHERE keep_condition - 加索引、统计信息、检查数据量
- 用
RENAME TABLE orders TO orders_bak, orders_new TO orders原子切换 - 最后
DROP TABLE orders_bak
注意：CREATE TABLE ... SELECT 期间源表只读，且新表无外键、触发器、注释等元数据需手动补全。
容易被忽略的坑：事务隔离级别和 autocommit
默认 AUTOCOMMIT=1 时，每个 DELETE ... LIMIT 是独立事务，没问题；但若显式开启事务，没及时 COMMIT，锁会一直挂着。
- 确认当前 session 的
autocommit状态：SELECT @@autocommit - 如果关了 autocommit，每次分批后必须
COMMIT，否则锁不释放 -
READ-COMMITTED和REPEATABLE-READ对锁范围影响不同，线上建议统一用READ-COMMITTED - 别在从库上直接删——即使你有权限，也可能因复制延迟导致主从不一致
真正难的不是写几行 SQL，而是判断“这次删多少才不影响线上查询响应时间”，这得看监控里的 Innodb_row_lock_time_avg 和慢查趋势。
