多层嵌套超3层必然导致优化器放弃代价估算和条件下推，必须结构性拆解；确认依据是estimatedrows与actualrows差超3个数量级、高频materialize/spool节点耗时超70%、physical reads暴增百万级。
多层嵌套子查询一旦超过3层，优化器基本放弃代价估算和条件下推——这不是加索引或调参数能解决的，必须结构性拆解。
确认是不是真被嵌套拖垮了
别只看SQL写了几层SELECT，先用真实执行计划说话。重点盯三处：
-
EstimatedRows和ActualRows差距超3个数量级（比如预估120行，实际扫了150万行），说明谓词根本没下推到底层表 - 执行计划里高频出现
Materialize（PostgreSQL）或Table Spool（SQL Server），且耗时占总时间70%+，代表中间结果反复物化 -
physical reads暴增（用EXPLAIN ANALYZE BUFFERS或SET STATISTICS IO ON对比），从几千跳到百万级，基本锁定嵌套失控
用JOIN重写IN/EXISTS嵌套时的关键动作
把 WHERE id IN (SELECT user_id FROM logs WHERE status = 'success') 这类半连接硬写成子查询，大概率触发 DEPENDENT SUBQUERY，每行都重复执行内层扫描。
- 优先改写为
INNER JOIN：确保logs.user_id和主表关联字段都有索引；若常同时查user_id和status，建复合索引INDEX idx_logs_user_status (user_id, status) - 用
EXISTS替代IN时，必须检查子查询是否走索引——如果EXISTS (SELECT 1 FROM t2 WHERE t2.key = t1.col)中t2.key没索引，EXISTS和IN一样慢 - 注意NULL语义：JOIN自动过滤NULL，而
IN (SELECT ...)遇到子查询返回NULL则整个条件判为UNKNOWN，结果为空——行为不一致必须验证
CTE不是“嵌套美化语法”，用错反而更慢
CTE在不同数据库行为差异极大，盲目替换括号嵌套会引入新问题。
- PostgreSQL默认可能物化CTE，若只引用一次，加
NOT MATERIALIZED显式控制；MySQL 8.0.23+ 才支持MATERIALIZED提示 - 在CTE里写
SELECT *会阻止外层WHERE下推到底层基表，拖慢I/O；字段必须显式列出 - 多个CTE交叉引用（A依赖B、B又依赖A）会让优化器退化为全物化，失去线性执行优势
- 中间CTE加
ORDER BY或LIMIT，会触发无谓排序或截断，后续无法复用结果集
临时表比CTE更稳的两个硬条件
当CTE表现不稳定、或中间结果 >1万行且需多次JOIN/过滤时，临时表才是可控选择。
- 建完
CREATE TEMPORARY TABLE tmp AS SELECT ...后，立刻执行ALTER TABLE tmp ADD INDEX idx_user_id (user_id)——没索引的临时表JOIN就是全表扫描 - MySQL临时表不支持全文索引，PostgreSQL临时表索引只在当前会话有效，漏掉这步等于白建
- 临时表名别用
tmp_*这类泛化前缀，ORM可能生成同名表导致冲突 - 如果子查询本身含聚合或复杂过滤，临时表比CTE更容易让优化器获得准确行数统计
真正卡点不在“怎么写”，而在人工验证每层的 ActualRows 分布、索引是否命中、以及物化是否必要——这些没法靠工具自动发现，得一行行看执行计划里的数字。
