子查询在WHERE中易触发全表扫描和间隙锁，因优化器可能将其转为嵌套循环连接，若子查询无索引或未物化，会逐行扫描主表并反复执行，扩大锁范围至索引间隙，尤其在RR隔离级别下加gap lock或next-key lock，导致写入阻塞。
子查询在WHERE中触发全表扫描和间隙锁
当子查询出现在WHERE条件里（比如id IN (SELECT ...)），且子查询结果集未被物化或优化器误判为相关子查询时，MySQL可能对子查询涉及的整张表加锁——尤其是使用REPEATABLE READ隔离级别时，InnoDB会为扫描路径上的所有索引间隙加gap lock。哪怕只查10行，若扫描了5万行索引项，就可能锁住对应范围的全部间隙，阻塞其他事务插入或更新。
常见现象是：执行一条看似简单的DELETE FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'inactive')，却让整个orders表写入卡顿数秒，监控看到innodb_row_lock_time_avg飙升。
- 用
EXPLAIN FORMAT=TRADITIONAL确认子查询是否标记为DEPENDENT SUBQUERY或MATERIALIZED；不是MATERIALIZED就大概率没走物化路径 - 检查子查询字段是否有索引：
users(status)必须存在，否则子查询本身就会全表扫，锁范围直接扩大到users全表 - 避免在子查询中使用函数或表达式（如
UPPER(email)），这会让索引失效，进而扩大锁范围
IN子查询改写为EXISTS时锁行为差异
EXISTS在MySQL中默认走半连接（semi-join）优化，执行器通常只探查匹配是否存在，不构造完整结果集，因此加锁仅限于实际命中的索引记录（record lock），不会扩展到间隙（gap lock）——前提是子查询能命中索引。
但注意：如果EXISTS里的关联条件没走索引（比如ON u.id = o.user_id但o.user_id无索引），优化器可能退化为嵌套循环+全表扫描，反而锁得更宽。
- 把
WHERE id IN (SELECT user_id FROM logs WHERE type = 'login')换成WHERE EXISTS (SELECT 1 FROM logs l WHERE l.user_id = t.id AND l.type = 'login') - 确保
logs(user_id, type)有联合索引，顺序不能颠倒；单列type索引无效 - 别写
EXISTS (SELECT * ...)，用SELECT 1更轻量，语义清晰，部分旧版本MySQL对*解析开销略高
子查询物化失败时的手动控制策略
MySQL 5.6+ 默认尝试物化子查询，但某些场景（如含LIMIT、GROUP BY、窗口函数）会强制禁用物化，导致每次外层行都重执行子查询并重复加锁。这时不能依赖自动优化，得主动干预。
最稳妥的方式是拆成两步：先用临时表存子查询结果，再JOIN。虽然多一次写入，但锁粒度可控、可预测。
- 用
CREATE TEMPORARY TABLE tmp_ids AS SELECT DISTINCT user_id FROM users WHERE status = 'banned' - 立刻在
tmp_ids上建主键：ALTER TABLE tmp_ids ADD PRIMARY KEY (user_id)，避免后续JOIN时全表扫 - 再执行
DELETE o FROM orders o INNER JOIN tmp_ids t ON o.user_id = t.user_id——此时锁只落在orders匹配的行上，且可精确预估 - 临时表生命周期随会话结束自动清理，无需手动
DROP
UPDATE/DELETE中子查询引发的隐式锁升级
在UPDATE或DELETE语句中嵌套子查询，MySQL可能将原本的record lock自动升级为next-key lock（record + gap），尤其当子查询返回空或NULL时，优化器有时会扩大扫描范围以“确保一致性”。这种行为在官方文档中极少明说，但实测高频出现。
例如：UPDATE products SET stock = stock - 1 WHERE id IN (SELECT product_id FROM cart_items WHERE session_id = 'abc')，若cart_items(session_id)无索引，InnoDB可能对products主键索引做范围扫描并加next-key锁，阻塞所有INSERT操作。
- 永远给子查询的过滤字段加索引，宁可冗余也不要靠
WHERE后推 - 避免在DML语句中直接嵌套子查询；优先用JOIN或提前物化
- 测试时用
SELECT ... FOR UPDATE复现锁行为，比直接跑UPDATE更安全
真正难处理的不是锁本身，而是锁范围在不同MySQL版本间表现不一致——比如8.0.20+对MATERIALIZED子查询的锁粒度明显收紧，而5.7下仍可能扫全索引。上线前务必在同版本环境压测锁竞争场景。
