子查询默认不缓存，相关子查询一定无法缓存，非相关子查询主流数据库也不自动缓存；可用PostgreSQL的WITH MATERIALIZED或MySQL的/+ MATERIALIZE() /强制物化，或改写为JOIN提升性能与稳定性。
子查询不缓存是常态，别指望数据库自动优化
绝大多数关系型数据库（MySQL 5.7/8.0、PostgreSQL 12+、SQL Server）对普通嵌套子查询 SELECT * FROM t1 WHERE id IN (SELECT id FROM t2 WHERE ...) 不做结果缓存。每次外层行扫描都会重跑子查询（尤其在 IN / EXISTS 中），数据量一大，性能断崖式下跌。
原因很简单：子查询可能依赖外层变量（如相关子查询）、或涉及未提交事务、或受隔离级别影响，数据库不敢擅自复用结果。
- 相关子查询（含外层列引用）一定无法缓存，例如
SELECT name FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.created_at > '2024-01-01') - 非相关子查询（独立可执行）理论上可缓存，但主流引擎默认不缓存——除非你显式干预
- MySQL 的
MATERIALIZED提示（8.0.22+）和 PostgreSQL 的MATERIALIZEDCTE 是少数能强制“缓存”中间结果的机制
用 WITH MATERIALIZED 强制物化中间结果（PostgreSQL 12+）
PostgreSQL 12 起支持 MATERIALIZED 关键字，明确告诉优化器：这个 CTE 必须执行一次、结果暂存内存/临时表，后续引用直接读取。
WITH active_users AS MATERIALIZED ( SELECT id FROM users WHERE status = 'active' AND last_login > '2024-01-01' ) SELECT * FROM orders WHERE user_id IN (SELECT id FROM active_users);
对比不加 MATERIALIZED：若外层 orders 有 10 万行，子查询可能被执行 10 万次；加了之后只执行 1 次。
- 不加
MATERIALIZED时，PostgreSQL 可能选择内联展开（inlining），失去物化机会 -
MATERIALIZED会增加首次执行延迟（要算完再继续），但大幅降低整体耗时 - 注意内存限制：
work_mem不足时，物化结果会落盘，反而变慢
MySQL 8.0.22+ 用 /*+ MATERIALIZE() */ 提示触发物化
MySQL 默认把子查询转成半连接（semijoin），但不保证只执行一次。用优化器提示 /*+ MATERIALIZE() */ 可强制将子查询结果物化为临时表。
SELECT /*+ MATERIALIZE() */ * FROM orders WHERE user_id IN ( SELECT id FROM users WHERE status = 'active' );
该提示仅对派生表（derived table）和某些子查询生效，且要求 MySQL ≥ 8.0.22。
- 如果子查询含聚合或
DISTINCT，MATERIALIZE更容易生效 - 避免在子查询里用
ORDER BY或LIMIT—— 物化后排序失效，还可能触发错误ERROR 3575 - 检查执行计划：出现
materialized_from_subquery或Derived类型即表示成功物化
更稳的替代方案：用 JOIN + 去重代替 IN 子查询
当子查询逻辑固定、不依赖外层值时，手动“提前提取”并 JOIN，比依赖数据库缓存更可控、兼容性更好。
-- 原写法（重复执行） SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE status = 'active'); <p>-- 改写为 JOIN（一次查出关联用户，天然去重） SELECT DISTINCT o.* FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE u.status = 'active';
JOIN 方案优势在于：索引可覆盖、执行计划稳定、所有版本 MySQL/PG 都适用。
-
DISTINCT成本通常远低于反复执行子查询，尤其当users表有合适索引（如(status, id)）时 - 若外层需保留无匹配订单的行，改用
LEFT JOIN+WHERE u.id IS NOT NULL - 注意：
IN对NULL敏感，而JOIN天然过滤NULL，语义是否一致需核对业务逻辑
真正难的不是知道“能物化”，而是判断什么时候该物化、什么时候该改写、以及物化后内存是否扛得住。这些权衡没法靠一个提示符解决，得看执行计划、查数据分布、测真实负载。
