数据库的查询执行计划中的公共子查询消除优化技术

1. 知识点描述

公共子查询消除是一种数据库查询优化技术。当同一个子查询在SQL语句的多个地方被重复使用时（例如，在SELECT列表、WHERE条件、HAVING子句中多次出现），优化器会识别出这些结构相同且语义等价的子查询块，将其结果计算一次并缓存（物化），然后在多个引用点重复使用这个缓存的结果，而不是重复执行相同的子查询。这能显著减少计算和I/O开销，提升查询性能。

2. 问题场景与核心挑战

• 场景示例：假设有一张销售订单表 orders 和一张客户表 customers。查询“找出那些总消费额高于所有客户平均总消费额，并且最近一年也有订单的客户”。
  • 一个可能未经优化的SQL写法中，计算“所有客户平均总消费额”这个聚合值的子查询可能会在HAVING子句和另一个子查询的WHERE条件中被重复书写两次，尽管它们逻辑完全相同。
• 核心挑战：
  1. 识别难度：优化器需要准确判断两个子查询块在语法结构、引用表和计算逻辑上是否完全等价，即使它们的写法（如列名别名、空格）略有不同。
  2. 等价性保证：必须确保子查询是确定性的（即，相同的输入永远产生相同的输出，不包含随机函数、不依赖会话变量等），并且其执行结果不依赖于外层查询的当前行（即，它是一个不相关子查询或可被安全地转换为不相关子查询）。
  3. 物化权衡：缓存子查询结果（物化）需要占用内存或临时存储空间。优化器需要评估重复执行的代价高，还是一次性物化的代价高。

3. 优化技术的执行过程（步骤详解）

步骤1：查询解析与标准化

• 查询优化器首先接收到SQL字符串，由解析器将其转换为内部的抽象语法树。
• 进行标准化处理，例如，统一操作符的写法、消除冗余括号、展开视图等，为后续的等价性比较打下基础。

步骤2：子查询识别与标记

• 优化器遍历语法树，识别出所有的子查询块（通常是以 SELECT 开头的嵌套部分）。
• 为每个子查询块生成一个唯一的标识符，并记录其在语法树中的位置、引用的表、选择的列、过滤条件等信息。

步骤3：公共子查询探测

• 优化器会比较所有识别出的子查询块，寻找“公共”的候选。
• 判断等价的维度：
  • 结构等价：语法树结构是否相同。
  • 语义等价：涉及的基表、连接条件、过滤谓词、聚合函数、分组列等在逻辑上是否完全一致。
  • 上下文无关：子查询本身是独立、确定性的。如果子查询引用了外层查询的列（相关子查询），则需要分析是否可以通过某些查询变换（如子查询提升/反嵌套）将其转换为不相关子查询，从而使其成为公共子查询消除的候选。

步骤4：代价评估与决策

• 对于识别出的公共子查询，优化器会进行代价评估。
  • 评估重复执行的代价：估算这个子查询在查询中被引用了N次，如果独立执行N次，总的I/O、CPU开销是多少。
  • 评估物化执行的代价：估算将这个子查询执行一次，将其结果集保存到内存中的临时表或磁盘上的临时文件，并建立简单索引（如果需要）的代价，再加上后续N次读取这个临时结果的代价。
• 比较两者代价。只有当物化执行的代价显著低于重复执行的代价时，优化器才会决定应用公共子查询消除优化。

步骤5：查询重写与执行计划生成

• 一旦决定优化，优化器会进行内部查询重写：
  1. 提取：将公共子查询从原始查询文本的逻辑结构中“提取”出来，作为一个独立的计算单元。
  2. 物化节点：在逻辑执行计划中，为这个独立的计算单元创建一个“物化”（Materialize）或“临时表扫描”（Temp Table Scan）操作节点。这个节点负责计算子查询，并将结果存储在临时空间中。
  3. 替换引用：将原始查询中所有引用这个公共子查询的地方，替换为对这个临时结果集的扫描或查找操作。
• 生成新的物理执行计划，其中包含了对公共子查询的一次性计算和后续的多次复用。

步骤6：执行与清理

• 执行引擎按照优化后的计划运行：
  1. 首先执行“物化”节点，计算出公共子查询的结果集，存入临时存储。
  2. 然后执行查询的主体部分，每当需要该子查询的结果时，直接从临时存储中读取。
• 查询执行完毕后，清理临时存储空间。

4. 举例说明

考虑一个简化的例子：

-- 可能包含公共子查询的写法
SELECT customer_id
FROM orders o1
WHERE o1.total_amount > (SELECT AVG(total_amount) FROM orders)
   AND EXISTS (SELECT 1 FROM orders o2 WHERE o2.customer_id = o1.customer_id AND o2.year = 2023)
   AND o1.customer_id IN (SELECT customer_id FROM customers WHERE status = 'ACTIVE');

-- 假设另一个复杂的计算（如`(SELECT AVG(total_amount) FROM orders)`）在WHERE和HAVING中重复出现多次

• 优化前：计算 AVG(total_amount) 的子查询如果被引用多次，可能会被执行多次。
• 优化后：优化器识别出这是多个相同的、确定性的、不相关的标量子查询。它会将其重写为：
  1. 计算一次 SELECT AVG(total_amount) INTO #temp_avg FROM orders，将结果存入临时变量或单行临时表 #temp_avg。
  2. 将原查询中所有 (SELECT AVG(total_amount) FROM orders) 替换为对 #temp_avg 的引用。
• 这样，昂贵的聚合计算只发生一次。

5. 技术要点与限制

• 触发条件：子查询必须是确定性的、不相关的（或可被安全地去相关化），且被多次引用。
• 与物化视图的区别：这是针对单次查询内的优化，物化的结果在查询结束后即丢弃。而物化视图是持久化的，可用于优化多个查询。
• 数据库支持：主流数据库（如Oracle、SQL Server、PostgreSQL、MySQL 8.0+）的优化器通常都具备某种形式的公共子查询消除或公共表达式消除（CTE物化也类似）能力，但具体的实现和触发条件有差异。
• 开发者提示：在编写复杂SQL时，可以有意识地将重复的子查询逻辑用CTE（Common Table Expression）或临时变量显式定义，这既是良好的编程实践，也能明确地引导优化器进行此类优化，避免其因等价性判断失败而错过优化机会。