子查询被优化器转为临时表的典型场景是IN/NOT IN、含GROUP BY/ORDER BY/聚合的嵌套SELECT，以及外层无索引驱动时；EXPLAIN中出现Using temporary; Using filesort即为物化标志。
子查询被优化器转成临时表的典型场景
MySQL 5.7+ 和 PostgreSQL 在遇到 IN、NOT IN 或嵌套 SELECT 且外层无索引驱动时，常把子查询物化为临时表——尤其是子查询含 GROUP BY、ORDER BY 或聚合函数时。你看到 Using temporary; Using filesort 出现在 EXPLAIN 的 Extra 列里，基本就是它了。
- 如果子查询只用于过滤（如
WHERE id IN (SELECT user_id FROM logs WHERE ts > '2024-01-01')），优先改用JOIN或EXISTS -
NOT IN遇到子查询结果含NULL会整个返回空集，不仅逻辑错，还强制物化；换成NOT EXISTS可避免 - PostgreSQL 中
IN (VALUES (...))比IN (SELECT ...)更可能走哈希计划，不建临时表
用 EXISTS 替代 IN 时要注意的执行路径
EXISTS 是半连接（semi-join），优化器通常能下推关联条件，避免生成完整中间结果。但前提是子查询里有明确的关联谓词（correlation）。
- 写成
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid')，u.id必须来自外层表，否则退化为独立子查询，仍可能物化 - MySQL 8.0.20+ 支持
LATERAL，可在子查询中安全引用外层列并保持延迟计算，但需显式开启SET optimizer_switch='lateral_derived=on' - SQL Server 的
EXISTS自动抑制排序；而 PostgreSQL 对简单EXISTS子查询可能仍做Unique去重，加LIMIT 1可提示优化器早停
子查询带 ORDER BY 却不需要排序结果？立刻删掉
除非你在用 SELECT ... LIMIT 1 取“最新一条”，否则子查询里的 ORDER BY 几乎总是多余开销。优化器无法跳过它，哪怕外层没要求顺序。
- 错误写法：
(SELECT name FROM users ORDER BY created_at DESC LIMIT 1)作为标量子查询——ORDER BY强制排序，即使只取一行 - 正确做法：去掉
ORDER BY，改用窗口函数或依赖索引顺序（如created_at有降序索引时，MAX(created_at)+ 关联查更高效） - PostgreSQL 中若子查询是
UNION结果，ORDER BY必须放在最外层，内部ORDER BY无效且触发物化
标量子查询（scalar subquery）如何避免重复执行
标量子查询在每行外层数据上都执行一次，若没命中缓存或无法去重，性能雪崩。常见于 SELECT (SELECT COUNT(*) FROM tags t WHERE t.post_id = p.id) AS tag_count 这类写法。
- 把它提到
FROM子句，改写为LEFT JOIN LATERAL (SELECT COUNT(*) FROM tags t WHERE t.post_id = p.id) AS t(c)（PostgreSQL / MySQL 8.0.14+） - MySQL 中可先用
GROUP BY聚合再JOIN，例如预计算SELECT post_id, COUNT(*) AS c FROM tags GROUP BY post_id，再关联主表 - 注意：如果标量子查询含不确定函数（如
NOW()、RAND()），数据库不会缓存结果，必须重构逻辑
子查询是否物化，不取决于语法嵌套深度，而取决于优化器能否证明其结果可复用、可下推、可提前终止。看 EXPLAIN 比背规则管用，尤其注意 rows 和 filtered 列的实际值——它们比“看起来简洁”更能说明问题。
