SQL嵌套查询如何利用缓存机制_减少重复执行子查询开销-SQL-PHP中文网

子查询默认不缓存，相关子查询一定无法缓存，非相关子查询主流数据库也不自动缓存；可用PostgreSQL的WITH MATERIALIZED或MySQL的/+ MATERIALIZE() /强制物化，或改写为JOIN提升性能与稳定性。

sql嵌套查询如何利用缓存机制_减少重复执行子查询开销

子查询不缓存是常态，别指望数据库自动优化

绝大多数关系型数据库（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 的 MATERIALIZED CTE 是少数能强制“缓存”中间结果的机制

用 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';</p>

JOIN 方案优势在于：索引可覆盖、执行计划稳定、所有版本 MySQL/PG 都适用。