嵌套超3层子查询应重构为CTE，因其能明确提示优化器物化中间结果、避免重复执行、提升可读性；CTE不适用时（如高频复用、大数据量、需索引）应改用临时表，并注意索引、NULL及列裁剪。
嵌套超过3层的子查询基本该重构，不是“能不能跑”，而是“会不会在下次数据量增长后突然变慢或报错”。
为什么WITH比多层子查询更可靠
CTE不是语法糖，它是向优化器发出的明确信号：“这段先算好，后面复用”。而深层嵌套会让多数数据库（如MySQL 8.0、PostgreSQL）放弃物化中间结果，导致同一子查询被反复执行——比如外层10万行，内层子查询就真跑10万次。
-
EXPLAIN中若出现DEPENDENT SUBQUERY且rows值很大，就是典型信号 - CTE在PostgreSQL和SQL Server中默认倾向物化；MySQL 8.0+需显式加
MATERIALIZED提示才强制物化 - 语义清晰：每层CTE起一个业务名（如
active_users、recent_orders），比(SELECT ... FROM (SELECT ...))易读十倍
三层以上嵌套怎么拆成WITH
关键不是“全拆”，而是找语义断点：哪一层输出是后续多处依赖的稳定中间集？从最内层开始具名化。
- 原写法：
SELECT u.name FROM users u WHERE u.id IN (SELECT user_id FROM orders WHERE amount > (SELECT AVG(amount) FROM orders)) - 第一步：把
AVG(amount)提为avg_orderCTE，它不依赖外层，纯预计算 - 第二步：把订单过滤逻辑提为
high_value_orders，基于avg_order做JOIN而非子查询 - 第三步：主查询只JOIN两个CTE，彻底消除嵌套层级
WITH avg_order AS ( SELECT AVG(amount) AS threshold FROM orders ), high_value_orders AS ( SELECT o.user_id FROM orders o CROSS JOIN avg_order a WHERE o.amount > a.threshold ) SELECT u.name FROM users u INNER JOIN high_value_orders h ON u.id = h.user_id;
什么时候WITH不够用，得上临时表
CTE适合轻量、单语句、中间结果小于几万行的场景。一旦出现以下情况，必须换CREATE TEMPORARY TABLE：
- 同一CTE被主查询引用3次以上（CTE每次调用都重算）
- 中间结果含窗口函数或跨表JOIN，且行数超5万
- 需要对中间字段建索引加速后续JOIN（如
ON temp_orders.user_id） - 调试困难：CTE无法
SELECT * FROM cte_name，但临时表可以
容易忽略的细节：索引和NULL陷阱
重构只是第一步，没配好索引或掉进NULL坑里，性能照样崩。
- 所有JOIN字段（如
user_id、order_date）必须有索引，否则临时表JOIN变成全表扫描 - 用
EXISTS替代IN (SELECT ...)，尤其当子查询可能返回NULL时——IN遇NULL整个条件变UNKNOWN，直接丢行 - CTE里别写
SELECT *，只取真正需要的列，减少内存和网络开销 - MySQL中若用
ORDER BY ... LIMIT 1子查询，组合索引必须包含排序字段+WHERE字段，否则失效
