数据库查询优化中的查询重写（Query Rewriting）技术

描述
查询重写是数据库优化器在逻辑优化阶段采用的核心技术之一，指在不改变查询语义的前提下，将用户提交的SQL查询转换为一种更高效、执行代价更低的等价形式。这种转换基于关系代数的等价规则和数据库内部的启发式规则，旨在减少数据扫描量、降低连接复杂度或利用索引优势。查询重写无需访问实际数据，仅通过语法树分析即可完成，是优化查询性能的第一道关口。

解题过程

1. 理解查询重写的目标与原则

   • 目标：减少查询执行时的资源消耗（如I/O、CPU），缩短响应时间。
   • 原则：重写后的查询必须与原始查询语义完全等价，即对任何合法数据输入，输出结果均一致。
   • 常见优化方向：
     • 消除冗余操作（如不必要的子查询、连接）；
     • 提前过滤数据（通过谓词下推）；
     • 简化计算表达式（如常量折叠）；
     • 利用索引友好结构（如将NOT IN改为LEFT JOIN ... IS NULL）。

2. 分析典型重写规则及其应用场景

   • 谓词下推（Predicate Pushdown）

     • 原理：将过滤条件尽可能靠近数据源，减少后续处理的数据量。
     • 示例：
       原始查询：

       SELECT * FROM (SELECT * FROM orders WHERE status = 'shipped') AS t 
       WHERE t.amount > 1000;  
       

       重写后：

       SELECT * FROM orders WHERE status = 'shipped' AND amount > 1000;  
       

     • 优势：避免对全表数据生成临时表后再过滤。

   • 视图合并（View Merging）

     • 原理：将内联视图或公共表表达式（CTE）拆解后与主查询合并，消除临时中间表。
     • 示例：
       原始查询：

       SELECT * FROM (SELECT id, name FROM users WHERE age > 18) AS v 
       JOIN orders ON v.id = orders.user_id;  
       

       重写后：

       SELECT users.id, users.name, orders.*  
       FROM users JOIN orders ON users.id = orders.user_id  
       WHERE users.age > 18;  
       

     • 优势：避免物化视图结果，直接利用表连接优化。

   • 连接消除（Join Elimination）

     • 原理：若连接的表不贡献查询结果中的列，且不影响语义（如外键保证完整性），可移除连接。
     • 示例：
       表orders有外键user_id引用users.id，且users表有主键索引。
       原始查询：

       SELECT orders.* FROM orders JOIN users ON orders.user_id = users.id;  
       

       重写后：

       SELECT * FROM orders;  
       

     • 优势：减少连接操作开销。

   • 常量传播与折叠（Constant Propagation & Folding）

     • 原理：在编译时计算常量表达式，并将结果传播到其他条件中。
     • 示例：
       原始查询：

       SELECT * FROM products WHERE price > 10 + 5;  
       

       重写后：

       SELECT * FROM products WHERE price > 15;  
       

     • 优势：减少运行时计算负担。

3. 掌握复杂重写技巧：子查询转换

   • 问题：相关子查询（Correlated Subquery）可能导致逐行执行，性能低下。
   • 解决方案：将子查询转换为连接操作。
     • 示例1：IN子查询转JOIN
       原始查询：

       SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE age > 18);  
       

       重写后：

       SELECT orders.* FROM orders JOIN users ON orders.user_id = users.id  
       WHERE users.age > 18;  
       

     • 示例2：EXISTS子查询转SEMI-JOIN
       原始查询：

       SELECT * FROM orders o WHERE EXISTS (  
         SELECT 1 FROM users u WHERE u.id = o.user_id AND u.age > 18  
       );  
       

       重写后（数据库内部可能使用半连接算法）：

       SELECT o.* FROM orders o SEMI JOIN users u ON o.user_id = u.id AND u.age > 18;  
       

   • 注意事项：需确保转换后无重复结果（如使用DISTINCT或半连接语义）。

4. 识别重写限制与边界条件

   • 聚合函数与HAVING子句：
     • 聚合查询中，WHERE条件可下推至聚合前，但HAVING条件涉及聚合结果，不可下推。
     • 示例：

       SELECT department, AVG(salary) FROM employees GROUP BY department  
       HAVING AVG(salary) > 5000;  
       

       HAVING条件无法重写到WHERE中，但优化器可能提前过滤掉不符合条件的组。
   • 嵌套聚合与窗口函数：复杂嵌套结构可能限制重写空间，需依赖优化器规则扩展。

5. 实践验证与执行计划分析

   • 使用EXPLAIN命令对比重写前后执行计划，观察扫描行数、临时表使用、连接顺序等指标。
   • 示例：在MySQL中对比以下查询计划：

     EXPLAIN SELECT * FROM orders WHERE user_id IN (SELECT id FROM users);  
     EXPLAIN SELECT orders.* FROM orders JOIN users ON orders.user_id = users.id;  
     

   • 关键指标：
     • 是否利用索引（如Using index）；
     • 连接类型（如Nested Loop vs Hash Join）；
     • 额外操作（如Using temporary、Using filesort）。

总结
查询重写是优化器自动化性能调优的核心手段，其效果依赖于统计信息的准确性和规则覆盖度。实际应用中，开发者可通过简化查询逻辑、避免嵌套过度、显式提示（如STRAIGHT_JOIN）辅助优化器决策。掌握重写原理有助于编写优化器友好的SQL语句，从源头提升数据库性能。