嵌套查询加剧锁竞争是因为内层子查询物化为临时表，延长锁持有时间并扩大锁范围；用WITH替代可缓解，但需确保条件下沉、避免全表扫描。
嵌套查询为什么会让锁竞争变严重
因为大多数数据库（比如 MySQL InnoDB）在执行 SELECT ... FROM (SELECT ...) AS t 这类嵌套时，会把内层子查询结果物化成临时表——这个过程可能加锁、占内存、阻塞其他事务读写同一张基表。尤其当内层含 WHERE 条件但没走索引，或外层要 JOIN 多次时，锁范围容易从行级扩大到间隙甚至全表。
- 常见错误现象：
Lock wait timeout exceeded或Deadlock found when trying to get lock频繁出现，而单查内外层 SQL 却很快 - 典型场景：报表汇总页用嵌套查“每个用户最新订单”，并发一上来就卡住
- 关键点：嵌套本身不锁，但物化临时表 + 后续访问基表的时机，会放大锁持有时间
用 WITH 语句替代嵌套能缓解锁吗
在支持 CTE 的数据库（PostgreSQL、MySQL 8.0+、SQL Server）中，WITH 默认是**非物化**的——它只是逻辑重命名，执行计划仍可下推谓词、复用索引，避免中间结果落地，自然减少锁时间和临时资源占用。
- 但注意：
WITH不等于自动优化，如果写成WITH t AS (SELECT * FROM orders)再外层过滤，还是全表扫描+锁全表 - 真正有效的写法是让条件尽可能下沉，比如：
WITH latest AS (SELECT user_id, MAX(created_at) AS max_time FROM orders GROUP BY user_id)，再和orders关联，这样GROUP BY可走索引，锁只落在涉及的分组行上 - MySQL 5.7 及更早不支持
WITH，强行改写会退化为派生表（即原嵌套），反而更差
哪些嵌套结构必须拆成应用层处理
当嵌套里混用了不可下推的操作（如窗口函数、GROUP_CONCAT、自定义函数），或需要跨库/跨分片关联时，数据库已无法安全优化锁行为，硬压在 SQL 层只会让锁竞争更隐蔽、更难定位。
- 典型信号：执行计划里出现
Using temporary; Using filesort且rows值远超实际返回行数 - 推荐做法：先用窄查询（只查主键或必要字段）拉出 ID 列表，再用
IN分批查详情；或者用 Redis 缓存聚合结果，过期策略设短些（比如 30s），比每次锁表强得多 - 特别注意：不要用
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE ...)替代JOIN，MySQL 5.6–5.7 对这种写法优化极差，易触发全表扫描+锁膨胀
ORDER BY + LIMIT 在嵌套里怎么不拖慢并发
ORDER BY ... LIMIT 放在内层子查询中，常被当成“取 Top N”的捷径，但它在嵌套中极易导致数据库放弃索引排序，转而物化全部中间结果再排序——锁住所有参与排序的行，而不是仅锁最终那几行。
- 正确姿势：把
LIMIT拆到最外层，且确保ORDER BY字段有联合索引覆盖（比如查“每个分类最新 3 篇文章”，索引建在(category_id, created_at)上） - 反例：
SELECT * FROM (SELECT * FROM posts ORDER BY created_at DESC LIMIT 100) t WHERE category_id = 5—— 先取全局前 100，再过滤，锁了 100 行无关数据 - 如果业务真要“每个分组 Top N”，优先用窗口函数（
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)），它不物化，锁粒度可控，且 PostgreSQL / MySQL 8.0+ 都支持
锁不是越少越好，而是越精准越好。嵌套查询的“简洁”表象下，藏着执行路径的不确定性——你写的是一条 SQL，数据库可能执行出三套锁方案。盯住执行计划里的 type 和 key 字段，比背口诀管用。
