数据库查询优化中的子查询合并优化技术

描述
子查询合并（Subquery Merging）是数据库查询优化中的一种重要技术，它将多个逻辑上相关或结构相似的相关子查询（Correlated Subquery）或非相关子查询（Non-correlated Subquery）合并为一个单一的查询块，从而消除重复计算、减少查询复杂度，并允许优化器生成更高效的整体执行计划。这项技术主要针对包含多个子查询的复杂查询，特别是在WHERE子句或SELECT列表中并列存在多个相似子查询的场景。

解题过程/原理解析

让我们循序渐进地理解其动机、适用场景、合并过程及最终效果。

步骤1：理解问题与动机
复杂的业务查询中，一个外层查询可能包含多个子查询。例如，一个报表查询可能需要从同一张表中，根据不同条件筛选出多个不同的统计值。如果这些子查询独立执行，会导致：

1. 重复扫描：每个子查询可能都需要独立地扫描同一张基表或连接中间结果，造成I/O和计算资源的浪费。
2. 优化限制：优化器难以对多个独立的子查询块进行全局优化（如连接顺序、连接算法选择），因为每个子查询被单独优化。
3. 执行开销大：多个子查询意味着更多的查询块初始化、结果物化与传递开销。

动机：将多个子查询合并，使得它们可以在一次表扫描或连接操作中完成计算，共享中间结果，并为优化器提供一个更全局的、可优化的查询结构。

步骤2：识别可合并的子查询模式
并非所有子查询都能合并。优化器（或开发者手动改写时）需要识别以下常见模式：

1. 并列的非相关子查询：多个子查询出现在SELECT列表的不同列中，或WHERE子句的AND/OR条件中，且它们都引用相同或高度重叠的表集合，但过滤条件不同。

• 示例：SELECT (SELECT COUNT(*) FROM orders WHERE status = 'SHIPPED'), (SELECT SUM(amount) FROM orders WHERE status = 'PAID') FROM dual。这里两个子查询都扫描orders表，只是聚合函数和过滤条件不同。

2. 共享相关条件的相关子查询：多个相关子查询具有相同的外层连接键（correlation key），但内部过滤或聚合条件不同。

• 示例：SELECT e.name, (SELECT COUNT(*) FROM orders o WHERE o.emp_id = e.id AND o.year=2023), (SELECT AVG(amount) FROM orders o WHERE o.emp_id = e.id AND o.year=2023) FROM employees e。两个子查询都通过o.emp_id = e.id关联，且年份条件相同，只是聚合函数不同。

3. 逻辑等价的子查询：多个子查询在逻辑上可以合并为一个更通用的查询，然后通过CASE WHEN或GROUP BY进行区分。

步骤3：执行合并转换
合并的核心思想是将多个子查询的逻辑组合成一个单一的查询块，通过条件聚合（Conditional Aggregation）或增加分组维度来实现。

我们以步骤2中的第一个模式为例，展示手动/自动的合并过程。

• 原始查询：

  SELECT
      (SELECT COUNT(*) FROM orders WHERE status = 'SHIPPED') as shipped_count,
      (SELECT SUM(amount) FROM orders WHERE status = 'PAID') as paid_total
  FROM dual;
  

  执行计划预测：数据库会执行两次对orders表的全表扫描或索引扫描。

• 合并转换过程：

  1. 识别共同点：两个子查询都来自orders表。
  2. 设计统一扫描：考虑对orders表进行一次扫描。
  3. 条件聚合：在一次扫描中，通过CASE WHEN语句，根据不同的status条件，分别计算COUNT和SUM。
  4. 生成合并后查询：

     SELECT
         COUNT(CASE WHEN status = 'SHIPPED' THEN 1 END) as shipped_count,
         SUM(CASE WHEN status = 'PAID' THEN amount END) as paid_total
     FROM orders;
     

     • COUNT(CASE WHEN status = 'SHIPPED' THEN 1 END)：只有当status为'SHIPPED'时，CASE表达式返回1，COUNT函数才会计数这个非空值。其他行返回NULL，COUNT忽略。这等价于COUNT(*) FILTER (WHERE status = 'SHIPPED')（某些数据库语法）。
     • SUM(CASE WHEN status = 'PAID' THEN amount END)：同理，只对status为'PAID'的行的amount求和。

步骤4：分析合并后的优势

1. 减少I/O：从两次表扫描减少为一次。
2. 计算效率提升：CPU只需遍历一次数据，即可计算两个聚合值。现代CPU的流水线和分支预测对此类条件逻辑处理高效。
3. 优化器机会：现在是一个简单的单表聚合查询，优化器可以利用status列上的索引（如果有）进行快速过滤，甚至可能使用索引只扫描相关的行，而不是全表扫描。它还可以更好地估算基数（行数）。
4. 适用于更复杂场景：如果WHERE条件更复杂，合并后查询允许优化器对复合条件（如涉及多个列的过滤）进行更全面的评估，可能产生更好的过滤顺序。

步骤5：考虑相关子查询的合并
对于步骤2中的第二个模式（相关子查询），合并逻辑类似，但需要将外层表引入到合并后的查询中，通常通过LEFT JOIN或作为驱动表进行相关。

• 原始查询（相关子查询）：

  SELECT e.name,
         (SELECT COUNT(*) FROM orders o WHERE o.emp_id = e.id AND o.year=2023) as order_count,
         (SELECT AVG(amount) FROM orders o WHERE o.emp_id = e.id AND o.year=2023) as avg_amount
  FROM employees e;
  

• 合并转换过程：

  1. 识别共同点：两个子查询都关联e.id，且年份条件相同。
  2. 预聚合：可以先将orders表按emp_id和year进行分组聚合，一次性计算出每个员工2023年的订单数和平均金额。
  3. 连接替换：然后将employees表与这个聚合结果进行左外连接（LEFT JOIN）。

  SELECT e.name,
         COALESCE(o_agg.order_count, 0) as order_count,
         o_agg.avg_amount
  FROM employees e
  LEFT JOIN (
      SELECT emp_id,
             COUNT(*) as order_count,
             AVG(amount) as avg_amount
      FROM orders
      WHERE year = 2023
      GROUP BY emp_id
  ) o_agg ON e.id = o_agg.emp_id;
  

  • 这样，对orders表的扫描和聚合只执行了一次，而不是对employees表中的每一行都执行两次子查询。

总结
子查询合并优化技术的本质是识别并消除查询中的重复计算模式。它通过将多个独立的查询块融合为一个，使得数据库可以：

1. 共享数据扫描和中间结果。
2. 为查询优化器提供一个更完整、更扁平的视图，以便应用更多优化规则（如连接重排序、更好的索引选择）。
3. 显著降低查询的复杂性和执行开销。

在实际数据库系统中（如Oracle、PostgreSQL、SQL Server的查询优化器），通常会内置此类优化规则，自动尝试进行子查询合并。但作为开发者，理解此技术有助于在优化器未能自动合并时，手动重写复杂SQL，或者在设计查询时就有意识地避免写出难以合并的低效子查询结构。