数据库查询优化中的公共表达式消除（Common Subexpression Elimination）原理解析

1. 问题描述

在复杂SQL查询中，经常会出现多个地方使用相同的子查询或表达式的情况。例如，一个查询中可能多次计算同一聚合结果（如(SELECT AVG(salary) FROM employees)），或在多个条件中重复使用同一表达式（如(a+b)*c）。如果数据库每次遇到这样的表达式都重新计算，会导致不必要的性能开销。公共表达式消除（CSE） 是一种优化技术，通过识别并复用重复的计算结果来减少冗余计算。

2. 核心原理

CSE的核心思想是：

• 识别重复表达式：在查询解析阶段，优化器检测语法树中结构完全相同的子表达式。
• 提取公共部分：将重复表达式提取为一次计算，并将结果保存到临时存储（如临时变量或中间结果集）。
• 替换引用：将所有用到该表达式的地方替换为对临时结果的引用。

3. 具体步骤与示例

步骤1：识别重复表达式

假设有如下查询（计算薪资高于平均薪资且部门平均薪资高于公司平均薪资的员工）：

SELECT emp_id, salary  
FROM employees  
WHERE salary > (SELECT AVG(salary) FROM employees)  
  AND dept_id IN (  
    SELECT dept_id  
    FROM employees  
    GROUP BY dept_id  
    HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)  
  );  

优化器会发现子查询(SELECT AVG(salary) FROM employees)在WHERE条件和HAVING条件中重复出现两次。

步骤2：提取公共表达式

优化器将重复的子查询提取为一次计算，并生成一个临时结果（假设为@avg_salary）：

-- 提取公共表达式结果  
SET @avg_salary = (SELECT AVG(salary) FROM employees);  

-- 重写后的查询  
SELECT emp_id, salary  
FROM employees  
WHERE salary > @avg_salary  
  AND dept_id IN (  
    SELECT dept_id  
    FROM employees  
    GROUP BY dept_id  
    HAVING AVG(salary) > @avg_salary  
  );  

步骤3：处理复杂表达式

对于非子查询的表达式（如数学运算），CSE同样适用。例如：

SELECT (a+b)*c AS result1, (a+b)*c + d AS result2  
FROM table1;  

优化器会提取公共部分(a+b)*c，仅计算一次：

-- 逻辑重写  
SELECT temp_value AS result1, temp_value + d AS result2  
FROM (  
  SELECT (a+b)*c AS temp_value, d  
  FROM table1  
) AS tmp;  

4. 优化器的实现挑战

1. 等价性判断：

   • 必须确保重复表达式在语义上完全等价（例如，涉及VOLATILE函数的表达式可能每次结果不同，不能优化）。
   • 考虑上下文影响（如表达式中的列是否受外层查询条件约束）。

2. 代价权衡：

   • 如果公共表达式计算成本低，但创建临时存储的开销较高，可能放弃优化。
   • 优化器需通过代价估算比较冗余计算成本与临时结果维护成本。

3. 临时结果存储方式：

   • 简单标量值可存入内存变量。
   • 复杂结果集可能需物化为临时表（如重复的CTE或子查询）。

5. 实际应用场景

• CTE（公共表表达式）的复用：

  WITH dept_avg AS (  
    SELECT dept_id, AVG(salary) AS avg_salary  
    FROM employees  
    GROUP BY dept_id  
  )  
  SELECT * FROM employees WHERE salary > (SELECT MAX(avg_salary) FROM dept_avg);  
  

  优化器可能将CTE物化后复用，避免重复扫描employees表。

• 多个聚合条件：如同时筛选高于平均值的记录和低于平均值的记录。

6. 与其他优化的关联

• 与谓词下推结合：先消除公共表达式，再下推条件，减少中间结果规模。
• 与物化视图配合：如果公共表达式匹配物化视图定义，可直接复用视图结果。

通过CSE优化，数据库能显著减少重复计算，尤其对包含复杂子查询或表达式的查询性能提升明显。