子查询是否被反复执行需用EXPLAIN验证，而非凭直觉；PostgreSQL用EXPLAIN (ANALYZE, BUFFERS)看实际执行次数，MySQL 8.0+用FORMAT=TREE观察物化标记。
子查询被反复执行？用 EXPLAIN 看真实执行计划
SQL 优化的第一步不是改写，而是确认问题是否存在。很多“冗余子查询”只是直觉判断，实际执行时数据库可能已做了物化或缓存。必须用 EXPLAIN（或 EXPLAIN ANALYZE）验证子查询是否真被多次调用。
常见错误现象：EXPLAIN 输出中出现多行相同子查询的 SubPlan 或 InitPlan，且其 Rows 和 Cost 被重复计算多次；或者在 WHERE 条件里引用了带聚合/关联的子查询，却没加 LATERAL 导致隐式笛卡尔积放大。
- PostgreSQL 中，
EXPLAIN (ANALYZE, BUFFERS)能显示子查询实际执行次数，比仅看计划更可靠 - MySQL 8.0+ 的
EXPLAIN FORMAT=TREE可直观看到子查询嵌套层级和物化标记（MATERIALIZED） - 避免只看
SELECT * FROM t1 WHERE id IN (SELECT id FROM t2)这类写法——若t2结果集大且未索引，子查询可能退化为循环嵌套（Nested Loop）
WITH 子句不是万能的：哪些场景它不减少计算
WITH（CTE）常被当成“提取公共逻辑”的银弹，但它在多数数据库里默认是**非物化**的——每次引用都重算，除非显式声明 MATERIALIZED（PostgreSQL 12+）或满足特定优化条件（如 PostgreSQL 的递归 CTE 或引用一次以上）。
使用场景：当子查询逻辑复杂、多处复用、且结果集不大（WITH 才值得考虑；若只被引用一次，它反而增加解析开销。
- PostgreSQL：加
MATERIALIZED强制物化，但会占用临时内存，大结果集可能触发磁盘落盘，反而更慢 - MySQL：CTE 默认物化（8.0+），但不支持
RECURSIVE以外的并行物化，且无法在子查询中引用外部表字段 - SQL Server：
WITH是逻辑视图，是否物化取决于优化器决策，加OPTION (MATERIALIZE)（2022+）才可控
把相关子查询转成 JOIN：关键在 ON 条件和去重
相关子查询（correlated subquery）——即子查询里引用了外层表字段——最容易成为性能瓶颈，因为每行外层数据都触发一次子查询执行。最直接的重构方式是改写为 JOIN，但要注意语义等价性。
典型错误现象：SELECT a.id, (SELECT MAX(b.ts) FROM b WHERE b.a_id = a.id) 改成 LEFT JOIN b ON a.id = b.a_id 后结果行数暴增，因未处理一对多关系。
- 用
GROUP BY a.id+ 聚合函数替代子查询中的MAX/COUNT等，确保行数不变 - 若子查询带
WHERE ... EXISTS，优先用LEFT JOIN ... ON ... WHERE b.a_id IS NOT NULL或EXISTS本身（现代优化器对EXISTS处理通常优于IN） - 避免
DISTINCT滥用：它可能掩盖 JOIN 导致的重复，但会强制排序或哈希去重，成本未必低于原子查询
窗口函数能替代部分子查询，但别在 WHERE 里用
窗口函数（ROW_NUMBER(), RANK(), LAG() 等）适合替代那些“按分组取 Top N”或“前后行比较”的子查询，但它们不能出现在 WHERE 或 ON 子句中——因为执行顺序上，窗口函数在 WHERE 之后计算。
容易踩的坑：写成 WHERE ROW_NUMBER() OVER (...) = 1，会报错；或误以为 SELECT *, COUNT(*) OVER (PARTITION BY x) AS cnt FROM t WHERE cnt > 1 能生效，实际 cnt 在 WHERE 阶段不可见。
- 正确做法：先用子查询或 CTE 计算窗口值，再在外层
WHERE过滤，例如SELECT * FROM (SELECT *, ROW_NUMBER() OVER (...) rn FROM t) t1 WHERE rn = 1 - 注意
PARTITION BY字段必须有索引，否则窗口函数的排序开销会很大 - 在 PostgreSQL 中，
OFFSET ... FETCH+ 窗口函数组合可用于分页 Top N，比子查询LIMIT 1更稳定
真正难的不是识别冗余，而是判断“哪次计算可以合并、哪次必须保留”。比如时间窗口内最新状态、用户最近三次操作这类逻辑，强行 JOIN 或 CTE 可能引入额外排序或临时表，反而不如原生相关子查询清晰高效。动手前，先让 EXPLAIN 说话。
