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

1. 知识点描述
公共表达式消除（Common Subexpression Elimination，CSE）是数据库查询优化中的一种技术，用于识别并重用重复的计算逻辑。在复杂查询中，同一表达式（如子查询、计算列或连接条件）可能被多次使用，CSE通过将其计算结果暂存（例如存入临时表或中间结果），避免重复计算，从而提升查询性能。该优化常见于包含多个相似子查询或复杂标量表达式的SQL语句中。

2. 问题场景举例
假设有一个销售数据库，需要统计“每个部门销售额最高的员工及其销售额”，可能会写出如下SQL：

SELECT   
  d.dept_name,  
  (SELECT e.emp_name FROM employees e WHERE e.dept_id = d.id ORDER BY e.sales DESC LIMIT 1) AS top_employee,  
  (SELECT MAX(e.sales) FROM employees e WHERE e.dept_id = d.id) AS top_sales  
FROM departments d;  

此查询中，子查询(SELECT ... FROM employees e WHERE e.dept_id = d.id)被重复使用了两次，分别用于获取员工姓名和最高销售额，导致对同一部门的数据扫描两次。

3. 优化步骤详解
步骤1：识别公共表达式
查询优化器会解析SQL，识别语义相同的子表达式。例如，上述查询中的两个子查询均依赖相同的表（employees）、相同的关联条件（e.dept_id = d.id）和相同的排序逻辑（按sales降序）。

步骤2：重写查询逻辑
优化器将公共表达式提取为一个独立单元，避免重复计算。以上述查询为例，可能重写为：

WITH dept_top_sales AS (  
  SELECT   
    dept_id,  
    MAX(sales) AS top_sales,  
    -- 通过窗口函数或附加查询获取对应员工  
    FIRST_VALUE(emp_name) OVER (PARTITION BY dept_id ORDER BY sales DESC) AS top_employee  
  FROM employees  
  GROUP BY dept_id  
)  
SELECT d.dept_name, t.top_employee, t.top_sales  
FROM departments d  
JOIN dept_top_sales t ON d.id = t.dept_id;  

或生成等效的执行计划，将公共部分计算一次后复用结果。

步骤3：选择物化策略
优化器决定如何存储中间结果：

• 临时物化：将公共表达式的结果存入临时表，后续步骤直接引用。
• 内联计算：在流水线执行中通过缓存中间值复用（如向量化处理时保留计算结果）。
  选择策略时需权衡计算开销与存储开销，例如数据量小时可能直接缓存到内存，数据量大时可能需磁盘临时表。

步骤4：执行计划生成
优化器将重写后的逻辑转化为执行计划。例如：

1. 扫描employees表，按dept_id分组并计算每组MAX(sales)，同时通过窗口函数获取top_employee。
2. 将结果与departments表连接。
   此计划只需对employees表扫描一次，而非原查询的两次。

4. 优化效果与限制

• 优势：
  • 减少重复I/O操作（如多次表扫描）和计算量。
  • 尤其适用于复杂表达式或子查询嵌套较深的场景。
• 限制：
  • 若公共表达式结果较小，物化可能引入额外开销。
  • 优化器需准确判断表达式语义一致性，如涉及非确定性函数（如RAND()）则不能复用。

5. 实际应用提示

• 开发者可通过数据库提供的优化器提示（如Oracle的MATERIALIZE）强制或禁止公共表达式物化。
• 使用CTE（Common Table Expression）时，数据库可能自动应用CSE，但需注意不同数据库的实现差异（如PostgreSQL的CTE默认物化，而SQL Server可能内联处理）。

通过以上步骤，公共表达式消除技术有效减少了冗余计算，提升了复杂查询的执行效率。