SQL查询优化中的查询重写优化原理解析（终极篇）

您好，很高兴为您讲解数据库查询优化的又一个高级主题。本次我们将深入探讨“查询重写优化”的终极原理，这被视为查询优化器的核心“大脑”之一，它决定了优化器能否将一个“用户怎么写方便”的查询，转换成一个“数据库怎么执行高效”的查询。

一、 什么是查询重写优化？

1. 核心定义：查询重写是查询优化器在早期（通常是逻辑优化阶段）对用户提交的SQL查询语句进行分析，并基于一套预定义的等价变换规则，将其转换为一个语义完全相同但执行效率通常更高的新查询的过程。
2. 本质：这是一个语法驱动、基于规则的转换过程。它不关心数据的具体分布（如表大小、索引选择性），只关注SQL语句的结构，并应用数学和集合论中的等价规则进行变换。
3. 目标：为后续的物理优化（如连接算法选择、访问路径选择）提供一个更优的“起点”或“中间表示”，使其能够生成更高效的执行计划。
4. 终极篇聚焦：本篇我们将超越基础的重写规则，探讨如何将多个规则组合、嵌套应用，以解决复杂查询场景的优化问题，并理解其背后的理论基础。

二、 为什么需要查询重写？（终极视角）

1. 弥补用户与优化器之间的“表达鸿沟”：用户倾向于书写易于理解和维护的SQL（使用视图、子查询、CTE等），但这可能引入性能瓶颈。优化器需要将其“扁平化”和“简单化”。
2. 发掘隐藏的优化机会：很多优化（如谓词下推、连接消除）必须在查询被重写为更规范的形式（如连接-选择-投影的代数形式）后才能被识别。
3. 降低后续优化的复杂度：将复杂的嵌套查询展开为连接，可以为动态规划等连接顺序选择算法提供更统一的操作对象，避免算法复杂度爆炸。
4. 实现“语义优化”：利用数据库模式中的约束（如主键、外键、唯一约束、NOT NULL）来大胆地进行重写，这些是纯语法分析做不到的。

三、 查询重写的核心规则体系（终极组合）

我们可以将重写规则视为一个工具箱，解决复杂问题时需要组合使用多种工具。以下是一个分层、组合的规则视图：

第一层：简化与规范化（打下基础）

• 常量折叠/传播：WHERE col = 1+1 -> WHERE col = 2。为后续优化提供精确的常量。
• 条件化简：
  • 逻辑简化：NOT (a > 5) -> a <= 5；a > 5 AND a > 5 -> a > 5。
  • 吸收律：(a>5) OR (a>5 AND b<3) -> a>5。消除冗余条件。
• 投影/选择下推的预备：将查询树转换为最规范的形式，为下推创造条件。

第二层：子查询与嵌套的征服（核心战场）
这是“终极篇”的重点，展示了规则的组合应用。

• 场景：SELECT * FROM orders o WHERE o.amount > (SELECT AVG(amount) FROM orders WHERE customer_id = o.customer_id)。
• 解决步骤（组合拳）：
  1. 识别：这是一个“相关子查询”，对主查询每一行都要执行一次，效率极低。
  2. 子查询展开/反嵌套：
     • 第一步-引入中间聚合：尝试将子查询转换为一个可独立计算的单元。但因为有相关性（o.customer_id），不能独立计算。
     • 第二步-相关去关联化：优化器会尝试将相关性“外移”。它可能将查询重写为一种“窗口函数”思路或使用“Lateral Join”的语义。
     • 一种可能的等价重写（通过引入GROUP BY和外连接）：

       -- 原查询
       SELECT * FROM orders o 
       WHERE o.amount > (SELECT AVG(amount) FROM orders s WHERE s.customer_id = o.customer_id);
       
       -- 重写后（逻辑等价）
       WITH customer_avg AS (
           SELECT customer_id, AVG(amount) as avg_amount
           FROM orders
           GROUP BY customer_id
       )
       SELECT o.* 
       FROM orders o
       LEFT JOIN customer_avg ca ON o.customer_id = ca.customer_id
       WHERE o.amount > COALESCE(ca.avg_amount, 0); -- 处理没有分组的情况
       -- 或者，更优的，利用聚合函数特性，用内连接（如果确信每个customer_id都有订单）
       SELECT o.*
       FROM orders o
       INNER JOIN (
           SELECT customer_id, AVG(amount) as avg_amount
           FROM orders
           GROUP BY customer_id
       ) ca ON o.customer_id = ca.customer_id
       WHERE o.amount > ca.avg_amount;
       

  3. 后续优化：重写后的查询，可以继续应用谓词下推、连接顺序选择、聚合下推等优化。例如，customer_avg这个CTE可以被物化，其聚合操作可以并行执行。

第三层：基于语义的激进优化（大胆假设）

• 场景：SELECT e.name FROM employees e, departments d WHERE e.dept_id = d.id AND d.name = 'Sales'， 且 departments.id 是主键，employees.dept_id 是外键且非空。
• 解决步骤：
  1. 连接消除：
     • 识别：departments表只贡献了过滤条件 d.name = 'Sales'，而查询最终只需要e.name。
     • 利用外键约束：由于存在外键且非空，e.dept_id的每一个值都必然在d.id中存在。d.name = 'Sales'这个条件本质上是在departments表中找出部门名为‘Sales’的id。
     • 等价变换：原查询等价于“找出那些dept_id等于‘Sales部门ID’的员工”。
     • 重写结果：SELECT e.name FROM employees e WHERE e.dept_id IN (SELECT id FROM departments WHERE name = 'Sales')。
  2. 子查询展开：对上一步的结果，继续应用子查询展开，可能转换为半连接（Semi-Join）或直接展开为带有常量条件的查询。
  3. 最终简化：如果‘Sales’部门ID是已知常量（通过常量传播），查询可能被简化为SELECT name FROM employees WHERE dept_id = 10。

第四层：复杂操作的融合与分解（权衡艺术）

• 场景：多个具有重叠GROUP BY和聚合的查询，或复杂的UNION/窗口函数。
• 规则组合：
  • 公共表达式提取：将多个子查询中相同的计算部分提取为CTE或临时视图，避免重复计算。
  • 谓词传递闭包：在连接条件下，推导出新的过滤条件，并将其下推到各个表，提前减少数据流。例如，A.x = B.y AND B.y = 10 可以推导出 A.x = 10 并下推到A表。
  • 视图合并：如果查询使用了视图，优化器会尝试将视图定义“内联”展开到主查询中，使得所有优化规则都能应用到完整的查询图上。

四、 查询重写的流程与挑战

1. 流程：原始SQL -> 语法解析树 -> 初始查询代数表达式（关系代数树）-> 应用重写规则 -> 优化后的查询代数表达式。
2. 挑战：
   • 规则冲突与顺序：应用规则的顺序可能影响最终结果。优化器需要定义规则优先级或进行代价估算的试探。
   • 搜索空间爆炸：理论上，可以不断应用等价规则生成无数个逻辑等价的查询。优化器必须使用启发式方法（如“Greedy”算法）或基于代价的方法，在有限时间内停止搜索并选择“足够好”的重写结果。
   • 保持语义等价：这是铁律。尤其是在处理NULL值、重复行、外连接时，任何重写必须保证在任何数据情况下结果都与原查询一致。

五、 总结

查询重写的“终极”要义，在于将一系列基础的、局部的等价变换规则，通过系统性的组合和嵌套应用，来解决全局的、复杂的查询性能问题。它像一个经验丰富的翻译家，不仅逐字翻译，更能理解深层含义并用更地道的目标语言表达出来。

高级优化器（如Oracle、SQL Server、PostgreSQL的优化器）的强大之处，正是在于它们内置了成百上千条这样的重写规则，并能智能地决定在何时、以何种顺序、组合哪些规则，将一个“笨拙”的查询，魔术般地转化为一个高效的执行计划蓝图。理解这个原理，有助于我们在编写SQL时，既保持代码的清晰可读，又对优化器最终能将其优化为何种形式抱有合理的预期。