子查询写在WHERE中易锁表，因优化器可能转为嵌套循环连接，导致主表逐行扫描并反复执行子查询，扩大锁范围；若子查询无索引或返回大量数据，还可能升级为间隙锁或表锁。
子查询写在 WHERE 中为什么容易锁表
因为数据库优化器可能将 WHERE 里的子查询转为嵌套循环连接（Nested Loop），尤其当子查询没走索引、或返回大量行时，会逐行扫描主表并反复执行子查询逻辑，导致主表扫描范围扩大、锁住更多行甚至整个表。MySQL 5.7+ 在 READ-COMMITTED 隔离级别下虽默认行级锁，但若子查询触发全表扫描或临时表物化失败，仍可能升级为间隙锁或表锁。
用 JOIN 替代 WHERE 子查询的关键条件
不是所有子查询都能直接改写成 JOIN，必须满足：子查询是单值或可聚合的关联逻辑，且关联字段有索引。常见可替换场景包括：IN、EXISTS、= (SELECT ...) 类型。
-
IN子查询 → 改为INNER JOIN+DISTINCT或GROUP BY去重（避免笛卡尔积） -
EXISTS→ 改为LEFT JOIN ... ON ... WHERE xxx IS NOT NULL，但需确保ON条件含索引字段 -
SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 WHERE t2.id = t1.ref_id LIMIT 1)→ 改为JOIN并加ORDER BY ... LIMIT 1的派生表，或用窗口函数（MySQL 8.0+）
子查询必须物化时怎么控制锁范围
当子查询逻辑复杂、无法改写为 JOIN（如含聚合、多层嵌套、非等值关联），数据库可能选择物化子查询结果到临时表。此时锁表风险取决于物化时机和临时表引擎：
- 显式用
WITH（CTE）定义子查询，在 MySQL 8.0+ 中默认物化，但可通过/*+ NO_MERGE() */提示强制物化，避免重复计算 - 把子查询提前查出存入带主键的临时表（
CREATE TEMPORARY TABLE tmp AS SELECT ...），再与主表JOIN—— 这样主表扫描只发生一次，且锁集中在最终UPDATE或DELETE语句本身 - 避免在子查询中使用
SELECT FOR UPDATE或LOCK IN SHARE MODE，否则物化过程也会持锁
UPDATE/DELETE 中嵌套子查询的高危写法
这类语句最容易引发长事务锁表，因为 DML 语句本身就要加行锁，嵌套子查询又可能拉长执行时间。典型错误：
UPDATE orders SET status = 'shipped' WHERE user_id IN (SELECT id FROM users WHERE last_login < '2023-01-01');
正确做法：
- 先确认
users.last_login和orders.user_id都有索引 - 拆成两步：先
CREATE TEMPORARY TABLE old_users AS SELECT id FROM users WHERE last_login < '2023-01-01' - 再
UPDATE orders JOIN old_users ON orders.user_id = old_users.id SET status = 'shipped' - 如果数据量极大（千万级），考虑分页更新，用
WHERE id BETWEEN ? AND ?控制每次锁的行数
真正难处理的是跨库、跨分片或含函数依赖的子查询——这时候锁表往往不是语法问题，而是架构约束，得靠异步任务或冗余字段规避。
