WITH 在 PostgreSQL 中默认是 CTE materialization，MySQL / Oracle 又如何？两者性能差异？

考察要点

这道题考察的是候选人对 Common Table Expressions (CTE) 的深入理解，远不止是“语法糖”的层面。面试官想看你是否懂以下三点：

1. CTE 的两种执行模式：物化（Materialization）和内联（Inlining/Merging），以及它们各自的优缺点。
2. 主流数据库的实现差异：特别是 PostgreSQL、MySQL、Oracle 在处理 CTE 时的默认行为、历史演变和版本差异。
3. 查询优化思维：你是否能根据具体场景（如 CTE 引用次数、数据量）判断哪种模式更优，并知道如何通过 Hint 或特定语法去影响优化器的决策。

核心概念

首先，CTE（WITH 子句）是定义在一个主查询（SELECT, INSERT, UPDATE, DELETE）范围内的临时命名结果集。其核心执行方式有两种：

1. 物化 (Materialization)

物化是指数据库优化器决定将 CTE 的结果集完整计算出来，并存放在一个内部的临时表或内存结构中。后续查询中任何对该 CTE 的引用，都会直接读取这个物化了的临时结果。

• 优点：当一个 CTE 在主查询中被多次引用时，物化可以避免重复计算，从而显著提升性能。它就像一个计算结果的缓存。
• 缺点：
  • I/O 开销：即使 CTE 只被引用一次，也需要将其结果写入临时存储再读出，存在额外开销。
  • 优化屏障 (Optimization Fence)：优化器无法将主查询的 WHERE 条件“下推”到 CTE 内部，可能导致 CTE 计算了大量最终会被丢弃的数据。

示例：

 1WITH sales_summary AS (
 2  SELECT product_id, SUM(amount) AS total_sales
 3  FROM sales
 4  GROUP BY product_id
 5)
 6SELECT -- 引用 1
 7  s.product_id, s.total_sales
 8FROM sales_summary s
 9WHERE s.total_sales > 1000
10UNION ALL
11SELECT -- 引用 2
12  s.product_id, s.total_sales
13FROM sales_summary s
14WHERE s.product_id IN (1, 2, 3);

在这个例子中，物化 sales_summary 是高效的，因为它只需要计算一次 GROUP BY。

2. 内联 (Inlining / Merging)

内联是指数据库优化器将 CTE 的定义直接展开，并合并到主查询中，类似于宏替换或视图展开。

• 优点：
  • 全局优化：优化器可以通盘考虑整个查询，将主查询的过滤条件（Predicate Pushdown）下推到 CTE 内部，从源头减少计算量。
  • 无 I/O 开销：对于单次引用的 CTE，没有创建临时表的开销。
• 缺点：当一个 CTE 被多次引用时，内联会导致其逻辑被重复执行多次，造成性能浪费。

示例：

 1WITH high_value_customers AS (
 2  SELECT user_id, name, city FROM users
 3)
 4SELECT user_id, name
 5FROM high_value_customers
 6WHERE city = 'Beijing';

在这个例子中，内联更优。优化器会将查询改写为：

 1SELECT user_id, name
 2FROM users
 3WHERE city = 'Beijing';

WHERE city = 'Beijing' 条件被直接应用在 users 表上，可以有效利用 city 列上的索引。如果采用物化，则会先全表扫描 users 生成临时表，再对其进行过滤。

数据库行为对比

常见低分回答

1. “CTE 就是一个带名字的子查询，没什么区别。”
   • 这是最常见的错误。它完全忽略了物化和内联的核心差异，以及由此带来的巨大性能影响。正确的回答必须点出这两种执行模式。
2. “PostgreSQL 的 CTE 总是会物化。”
   • 这个答案已经过时了。这只在 PostgreSQL 12 之前的版本成立。一个优秀的候选人应该了解主流数据库近年来的重要变化。
3. “用 CTE 性能更好，因为代码更清晰。”
   • 将代码可读性与性能混为一谈。虽然 CTE 确实能提高可读性，但其性能影响是双刃剑，取决于具体场景和数据库的实现，并非总是“更好”。
4. 混淆 CTE 和临时表 (TEMPORARY TABLE)。
   • CTE 的作用域仅限于单个 SQL 语句，而临时表的作用域可以是整个会话或事务。两者生命周期、是否支持索引、使用场景完全不同。
5. “MySQL 的 CTE 性能不行，因为它总是内联。”
   • 这同样是过时的认知（针对 MySQL 5.7），并且结论过于绝对。内联在单次引用且可下推谓词的场景下性能更优。不知道 MySQL 8.0 引入了优化器决策和 Hint 是一个减分项。

工程实践与优化

• 利用物化作为优化屏障：当发现优化器因为错误的成本估算（如选择了糟糕的 JOIN 顺序或索引）而生成了低效的执行计划时，可以故意将查询的一部分放入 CTE，并强制物化它。这会迫使优化器先完成 CTE 的计算，固定住这一部分的执行计划，从而简化剩余部分的优化，有时能“引导”优化器走向正轨。
• 使用 EXPLAIN 分析行为：
  • 在 PostgreSQL 中，EXPLAIN 的输出会明确显示。物化的 CTE 会有一个 CTE Scan on <cte_name> 的节点。内联的 CTE 则其逻辑会融入到主查询计划树中。
  • 在 MySQL 中，EXPLAIN 的 select_type 会有所体现。物化的 CTE 通常显示为 DERIVED，并且可以通过 EXPLAIN FORMAT=JSON 看到更详细的 materialized_from_subquery 信息。
• 递归 CTE 的性能：递归 CTE 必须通过物化来实现，因为它需要在每次迭代时引用上一次迭代的结果。其性能关键在于：
  1. 初始集合（Base Case） 的选择要尽可能小。
  2. 递归连接（Recursive Join） 的列上必须有高效的索引。
  3. 终止条件要明确，避免无限循环。
• 谨慎对待大结果集 CTE：如果一个 CTE 产生的结果集非常大，即使它被多次引用，物化的 I/O 和内存开销也可能非常高昂。此时需要权衡“重复计算”和“物化开销”哪个更大。可以考虑改写 SQL，或使用临时表等其他技术。

常见追问与延伸题目

1. 追问：如何决定一个查询应该用 CTE、子查询，还是临时表？

   • 回答要点 1 (CTE)：当逻辑复杂、需要提高可读性时；当同一中间结果集在单条语句中需要被引用多次时；当需要进行递归查询时。
   • 回答要点 2 (子查询)：当逻辑简单、仅需在查询中某处使用一次时，嵌套子查询最直接。
   • 回答要点 3 (临时表)：当中间结果集需要在多条独立 SQL 语句之间共享时；当需要对中间结果集创建索引以加速后续查询时；当中间结果集巨大，希望明确其生命周期以控制资源时。

2. 追问：PostgreSQL 12 为何要修改 CTE 的默认行为？解决了什么问题？

   • 回答要点 1 (问题)：在 12 之前，“总是物化”的行为虽然可预测，但在大量“单次引用”的场景下是次优的。它阻止了优化器进行谓词下推等关键优化，导致性能不佳。例如，WITH t AS (SELECT * FROM big_table) SELECT * FROM t WHERE id = 1; 会全量物化 big_table，非常低效。
   • 回答要点 2 (解决)：修改后的行为让 PostgreSQL 的优化器变得更“智能”，能够像其他现代数据库一样，根据成本做出内联或物化的决策。这使得在简单场景下性能自动达到最优，同时通过 MATERIALIZED 关键字保留了用户手动控制的能力，实现了性能、灵活性和向后兼容的平衡。

3. 追问：如果一个被多次引用的 CTE 强制内联，会发生什么？在什么罕见情况下这反而是有利的？

   • 回答要点 1 (发生什么)：CTE 的计算逻辑会被复制到每一个引用它的地方。如果 CTE 本身计算昂贵，这会导致严重的性能下降，因为同样的工作被重复执行了多次。
   • 回答要点 2 (罕见有利情况)：假设 CTE 本身会产生一个巨大的结果集，但每次引用它的地方都有极高选择性的过滤条件，且这些过滤条件各不相同。在这种情况下，如果强制内联，每个分支都可以将自己独特的、高选择性的过滤条件推入 CTE 内部。这可能使得每次“重复计算”的实际工作量都非常小，其总和甚至小于一次性物化整个巨大结果集的成本（磁盘 I/O + 内存）。这是一个典型的“计算换 I/O”的权衡。