嵌套子查询无法缓解锁竞争，反而加剧阻塞；其执行需加共享MDL锁，相关子查询重复执行会延长锁持有时间，正确替代方案是物化临时表、应用层拆解或用EXISTS替代IN。
嵌套子查询本身不会缓解锁竞争，反而常加剧阻塞
直接用嵌套子查询替代 JOIN 或视图，并不能绕过元数据锁（MDL）或行锁。MySQL 解析含子查询的语句时，仍需为所有涉及的基表加共享 MDL 锁；若子查询里引用了正被 ALTER TABLE 修改的表，照样卡在 Waiting for table metadata lock。更麻烦的是，相关子查询（如 WHERE x IN (SELECT ...)）可能被重复执行多次，放大锁持有时间与资源争抢。
哪些嵌套子查询写法会隐式延长锁持有时间
以下模式在高并发下风险突出：
-
SELECT * FROM orders WHERE customer_id IN (SELECT id FROM customers WHERE status = 'active')：子查询若未走索引，可能触发全表扫描 + 行锁累积 -
UPDATE products SET stock = stock - 1 WHERE id IN (SELECT product_id FROM cart_items WHERE session_id = 'abc')：子查询结果集大时，UPDATE会逐行加锁，且无法利用外层索引优化 - 子查询中含
ORDER BY+LIMIT却无覆盖索引：MySQL 可能先排序再裁剪，临时表+文件排序过程延长事务生命周期
真正有效的“规避锁竞争”替代方案
不靠改写子查询语法，而靠拆解执行路径、错开资源冲突点：
- 把子查询提前物化：用
CREATE TEMPORARY TABLE temp_ids AS SELECT id FROM customers WHERE status = 'active'，再JOIN temp_ids—— 临时表不参与 MDL 锁竞争 - 对高频读场景，把子查询逻辑下沉到应用层：先查出
customer_ids，再拼成WHERE customer_id IN (1,2,3...)，避免每次 SQL 解析都触发明细表 - 用
EXISTS替代IN（当子查询字段有索引时）：WHERE EXISTS (SELECT 1 FROM customers c WHERE c.id = orders.customer_id AND c.status = 'active')，可提前终止、减少扫描量 - 若子查询仅用于过滤，确认是否真需要实时性：考虑用缓存 ID 列表（如 Redis），或定期同步到只读中间表，彻底脱离主库 DDL 影响链
最容易被忽略的细节：子查询的执行计划不可信
MySQL 8.0+ 虽支持子查询物化（MATERIALIZATION），但是否启用取决于 optimizer_switch 设置和统计信息准确性。你看到 EXPLAIN 显示 materialized，不代表运行时一定生效——尤其当子查询结果集预估超阈值（默认 max_heap_table_size），就会退化为磁盘临时表，IO 开销+锁等待双双上升。务必用 EXPLAIN FORMAT=TREE 或 performance_schema.events_statements_history 实际验证执行路径。
