嵌套查询在高并发下容易锁表，根本原因是MySQL 5.7默认物化派生表，触发全表扫描并加临键锁，锁持续至外层语句结束，导致锁范围大、持有时间长、加锁顺序不可控，引发锁等待或死锁。
嵌套查询为什么在高并发下容易锁表
根本原因不是语法本身，而是数据库执行时物化子查询的过程。比如 SELECT * FROM (SELECT user_id, created_at FROM orders WHERE status = 'paid') t ORDER BY created_at DESC LIMIT 10，MySQL 5.7 会把内层结果写入临时表，期间对 orders 表加读锁（甚至间隙锁），并发一上来就排队等锁。
常见错误现象：Lock wait timeout exceeded、Deadlock found when trying to get lock，但单条执行很快；SHOW PROCESSLIST 里大量线程卡在 Sending data 或 Locked 状态。
- MySQL 5.7 及更早版本默认把所有
FROM (SELECT ...)当作派生表（derived table），强制物化，无法下推条件 - 即使内层有
WHERE user_id = ?，只要没走索引或条件没下沉，锁范围仍可能是全表或大范围区间 - PostgreSQL 和 MySQL 8.0+ 的 CTE（
WITH）默认非物化，但前提是写法正确——否则照样退化成临时表
用 WITH 替代嵌套前必须检查的三件事
WITH 不是银弹，写错反而更慢。重点看执行计划是否真的避免了物化和全表扫描。
- 条件必须下沉：写成
WITH latest AS (SELECT user_id, MAX(created_at) FROM orders WHERE status = 'paid' GROUP BY user_id)，而不是WITH all_orders AS (SELECT * FROM orders)再外层过滤 - 确保
GROUP BY或JOIN字段有索引，否则MAX(created_at)还是会扫全表 - MySQL 8.0 要确认
optimizer_switch开启了cte_materialization=off（默认值），否则可能强制物化
验证方式：执行 EXPLAIN FORMAT=TREE（MySQL 8.0+）或 EXPLAIN ANALYZE（PostgreSQL），看是否有 Materialize 节点或 Using temporary 提示。
哪些嵌套结构必须拆到应用层
当数据库已无法安全下推谓词或复用索引时，硬压 SQL 层只会让锁更隐蔽、更难调。
- 含窗口函数的子查询，如
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at)，MySQL 8.0 尚不支持谓词下推到窗口内 - 使用
GROUP_CONCAT、自定义函数或 JSON 函数的嵌套，执行计划里常出现Using filesort且rows值远超实际返回行数 - 跨分片或跨库关联（比如订单库 JOIN 用户库），分布式事务下锁行为不可控
实操建议：先用窄查询拉 ID 列表，例如 SELECT order_id FROM orders WHERE status = 'paid' AND created_at > '2026-06-01' LIMIT 1000，再用 IN 分批查详情；或用 Redis 缓存聚合结果，TTL 设为 30 秒，比每次锁表强得多。
ORDER BY + LIMIT 别塞进子查询里
把 ORDER BY created_at DESC LIMIT 10 写在内层，看似减少数据量，实际多数数据库（MySQL 5.7、SQL Server）会丢掉排序上下文，导致外层结果错乱，且锁住所有参与排序的行。
- 正确做法：只在最外层加
ORDER BY和LIMIT，且确保排序字段有索引（如(status, created_at)联合索引） - 如果内层必须 Top-N，改用
LATERAL（PostgreSQL）或JOIN ... LATERAL（MySQL 8.0.14+），它能保证关联上下文不丢失 - 深度分页场景（如第 10000 页），直接放弃
LIMIT offset, size，改用游标分页：WHERE created_at
真正卡住的往往不是某条 SQL 写得差，而是嵌套里混用了不可下推的操作，又没意识到数据库根本没按你设想的方式执行。
