DELETE中IN(SELECT...)常全表扫描,因优化器对DELETE的执行计划生成逻辑不同于SELECT,子查询物化后外层易被当作无条件删除处理,导致主表无法利用索引驱动。

DELETE 嵌套子查询本身不直接锁全表,但极大概率触发全表扫描 + 行锁堆积 + 锁升级,最终等效于锁表。
为什么 DELETE 中的 IN (SELECT ...) 会扫全表
MySQL 对 DELETE 语句中嵌套的 IN (SELECT ...) 执行计划生成逻辑,与 SELECT 不同。即使子查询走索引、主表也有索引,优化器仍可能放弃驱动表索引,转而对主表全表扫描——因为物化子查询结果后,它把外层当作“无条件批量删除”处理。
-
EXPLAIN DELETE FROM t1 WHERE id IN (SELECT id FROM t2 WHERE status = 'done')显示type: ALL、key: NULL,就是明确信号 - 子查询返回结果越多,优化器越倾向放弃索引嵌套循环,改用物化+哈希匹配,导致主表无过滤扫描
- 若
t2.status无索引,子查询先全表扫t2,再对t1每个候选id回表判断,锁住大量无关行
真正让锁升级为“全表级”的三个条件
不是语法错,是执行路径叠加了资源压力:
-
WHERE条件未命中索引(如隐式转换:status = 1而字段是VARCHAR)→ 必全表扫描 → 每行加行锁 → 锁管理开销爆炸 → InnoDB 自动升级为表锁 - 事务内执行时间过长(比如子查询含聚合、多表 JOIN、无 LIMIT),锁持有时间拉长 → 其他事务排队等待 → 触发
lock_wait_timeout - 隔离级别为
REPEATABLE READ且子查询含范围条件(如created_at > '2026-01-01')→ 触发间隙锁(gap lock)→ 锁住整个索引区间,远超实际要删的行
避免锁表的实操路径:拆、换、控
核心是不让数据库有机会“瞎扫+乱锁”:
- **拆成两步**:先
SELECT id FROM t2 WHERE status = 'done' ORDER BY id(确保status和id有联合索引),再DELETE FROM t1 WHERE id IN (1,2,3,...);注意控制IN列表长度(受max_allowed_packet限制) - **换写法**:用
JOIN替代IN,如DELETE t1 FROM t1 JOIN t2 ON t1.id = t2.id WHERE t2.status = 'done';必须保证t2.status和t1.id都有索引,否则照样全扫 - **控执行**:加
LIMIT分批删,配合WHERE id > ?游标推进;每次事务只删 1000 行,避免单事务锁太久
容易被忽略的关键点
很多人补了索引、降了隔离级别,还是锁表——问题常出在没验证真实执行路径:
- 别只看
SELECT的EXPLAIN,DELETE的执行计划可能完全不同;必须对DELETE本身跑EXPLAIN -
FORCE INDEX是临时止痛药,掩盖了索引设计缺陷(比如单列idx_status对WHERE status = ? AND created_at > ?无效,该建联合索引) - ORM 自动生成的嵌套删除(如 MyBatis 的
+ 子查询)往往没ORDER BY,导致每次IN列表顺序随机 → 底层行锁获取顺序不可控 → 死锁温床










