数据库查询优化中的查询重写优化原理解析（进阶篇）

一、问题描述
查询重写是数据库优化器的核心优化技术之一，其目标是在不改变查询语义的前提下，将用户提交的SQL查询转换为更高效执行的等价形式。在基础篇中，我们介绍了常量折叠、谓词下推等基础重写规则。进阶篇将深入探讨复杂场景下的重写策略，包括基于关系代数等价变换的优化、子查询的深度优化、以及利用统计信息驱动的智能重写。

二、关键重写规则详解

1. 连接顺序调整与结合律/交换律应用

   • 原理：利用连接操作的结合律（(A ⋈ B) ⋈ C = A ⋈ (B ⋈ C)）和交换律（A ⋈ B = B ⋈ A），调整连接顺序以减少中间结果集大小。
   • 示例：
     原始查询：

     SELECT * FROM orders o JOIN customers c ON o.cid = c.id  
                       JOIN products p ON o.pid = p.id  
     WHERE c.region = 'Asia' AND p.price > 100;  
     

     • 优化器可能先执行过滤性强的表（如customers和products），再与orders连接，减少连接过程中的数据量。

2. 子查询转换为连接

   • 场景：将相关子查询（Correlated Subquery）重写为更高效的连接操作。
   • 示例：
     原始查询（查找没有订单的客户）：

     SELECT id FROM customers c  
     WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.cid = c.id);  
     

     • 重写为左连接+空值检查：

     SELECT c.id FROM customers c  
     LEFT JOIN orders o ON c.id = o.cid  
     WHERE o.cid IS NULL;  
     

     • 优势：避免对customers表的每一行执行子查询，转为单次扫描。

3. 窗口函数优化

   • 场景：合并冗余的窗口函数计算。
   • 示例：
     原始查询：

     SELECT id, 
            ROW_NUMBER() OVER (ORDER BY salary) as rn1,  
            RANK() OVER (ORDER BY salary) as rn2  
     FROM employees;  
     

     • 若ROW_NUMBER和RANK的窗口定义相同，优化器可能合并排序操作，避免多次排序。

4. 基于统计信息的谓词重写

   • 原理：利用数据分布统计（如直方图）调整谓词顺序或形式。
   • 示例：
     若status字段有95%的值为‘completed’，查询：

     SELECT * FROM orders  
     WHERE status != 'completed' AND create_date > '2023-01-01';  
     

     • 优化器可能优先执行create_date过滤，再排除‘completed’，避免扫描大量无效数据。

三、重写规则的触发条件

1. 语义等价性检查：确保重写后的查询结果与原查询完全一致。
2. 代价估算验证：通过统计信息估算重写前后执行代价，仅采纳代价更低的方案。
3. 数据结构约束：考虑索引、分区等物理结构对重写策略的影响（如谓词下推需匹配索引前缀）。

四、实际案例：复杂嵌套查询优化

• 原始查询（统计每个区域的高消费客户订单数）：

  SELECT region, COUNT(*)  
  FROM customers c  
  WHERE EXISTS (  
    SELECT 1 FROM orders o  
    WHERE o.cid = c.id AND o.amount > 1000  
  )  
  GROUP BY region;  
  

• 重写步骤：
  1. 将相关子查询转为半连接（Semi-Join）：

     SELECT region, COUNT(*)  
     FROM customers c  
     SEMI JOIN orders o ON c.id = o.cid AND o.amount > 1000  
     GROUP BY region;  
     

  2. 进一步将半连接具体化为内连接+去重：

     SELECT c.region, COUNT(DISTINCT c.id)  
     FROM customers c  
     JOIN orders o ON c.id = o.cid  
     WHERE o.amount > 1000  
     GROUP BY c.region;  
     

• 优化效果：避免对customers表全表扫描，利用orders表的索引快速过滤高额订单。

五、总结
查询重写的进阶优化依赖于对关系代数、数据分布和物理存储的深度理解。优化器通过组合应用多种重写规则，将逻辑查询转换为逼近最优的物理执行计划。实际应用中，需结合执行计划分析（如EXPLAIN）验证重写效果，并适时通过Hint机制引导优化器决策。