数据库的查询重写与等价变换技术 题目描述 查询重写是数据库查询优化的重要环节，指在不改变查询语义的前提下，将用户提交的SQL语句转换为一种更高效、更适合查询优化器处理的等价形式。这个过程基于关系代数和数据库约束，通过应用一系列规则来实现逻辑层面的优化。 知识讲解 一、 查询重写的目标与基础 核心目标 ：提升查询执行效率。方法是将原查询转换为一个执行成本更低但结果完全相同的等价查询。 理论基础 ：关系代数。SQL查询可以转化为关系代数表达式（如选择σ、投影π、连接⋈），重写即是应用关系代数的等价定律对表达式进行变换。 关键依据 ：数据库模式信息与约束。包括主键、外键、唯一约束、非空约束、CHECK约束等。这些约束提供了关于数据的额外语义，是许多重写规则的先决条件。 二、 常见的查询重写规则与技术 下面我们循序渐进地讲解几个核心的重写技术。 步骤1：简化条件（条件化简） 这是最基础的重写，目的是减少计算复杂度。 规则 ：利用逻辑恒等式简化WHERE/HAVING/ON子句中的布尔表达式。 过程 ： 常量求值 ：直接计算包含常量的表达式。 原查询： SELECT * FROM t WHERE salary > 10000 + 5000 重写后： SELECT * FROM t WHERE salary > 15000 消除非 ：应用德摩根定律等。 原查询： SELECT * FROM t WHERE NOT (age > 30 AND dept = 'IT') 重写后： SELECT * FROM t WHERE age <= 30 OR dept <> 'IT' 常量传递 ：如果条件中存在等值比较，可以将常量替换到其他条件中。 原查询： SELECT * FROM t WHERE id = 10 AND name = (SELECT name FROM t WHERE id = 10) 重写后： SELECT * FROM t WHERE id = 10 AND name = (SELECT name FROM t WHERE id = 10) (这里看起来没变，但优化器可能将子查询求值为一个常量) 效果 ：减少后续操作需要处理的条件复杂度。 步骤2：视图展开（View Expansion） 如果查询中使用了视图，需要将视图定义合并到主查询中。 规则 ：将视图名替换为该视图的SQL定义。 过程 ： 假设有一个视图： CREATE VIEW v_emp AS SELECT id, name FROM employee WHERE dept = 'Sales' 原查询： SELECT name FROM v_emp WHERE id > 100 重写后： SELECT name FROM (SELECT id, name FROM employee WHERE dept = 'Sales') AS v_emp WHERE id > 100 进一步地，优化器会将外层条件 id > 100 下推到内层查询（参见下一步“谓词下推”），形成最终重写： SELECT name FROM employee WHERE dept = 'Sales' AND id > 100 效果 ：使得优化器可以在整个查询的范围内进行优化，而不是将视图作为一个“黑盒”。 步骤3：谓词下推（Predicate Pushdown） 这是极其重要且有效的规则，旨在尽早过滤掉不必要的数据。 规则 ：将选择操作（WHERE子句中的条件）尽可能地向数据源方向移动，越早执行过滤越好。 过程 ： 场景：连接查询 原查询： SELECT * FROM orders o JOIN customers c ON o.cid = c.id WHERE c.country = 'China' 重写思路：将条件 c.country = 'China' 从连接后（Join后过滤）下推到连接前，特别是下推到 customers 表的扫描时。 重写后（逻辑上等价于）： SELECT * FROM orders o JOIN (SELECT * FROM customers WHERE country = 'China') c ON o.cid = c.id 效果：连接操作需要处理的数据量（ customers 表的数据）大大减少，显著提升性能。 场景：子查询/视图 ：如前文视图展开的例子。 效果 ：减少连接、分组等昂贵操作需要处理的中间结果集大小。 步骤4：连接顺序调整（Join Reordering） 规则 ：根据表的大小和连接条件的选择性，改变查询中多个表的连接顺序。 过程 ： 连接操作满足结合律和交换律： (A ⋈ B) ⋈ C ≡ A ⋈ (B ⋈ C) 优化器的目标是找到一个连接顺序，使得产生的中间结果集最小。 示例 ： 假设表 A 很大， B 和 C 很小，且 A ⋈ B 会产生很大的结果，而 B ⋈ C 会产生很小的结果。 原查询计划可能是 (A ⋈ B) ⋈ C 。 重写后，优化器可能会选择 A ⋈ (B ⋈ C) 。先计算小的 B ⋈ C ，再与大的 A 连接，总成本更低。 效果 ：通过减少中间结果的数据量，降低I/O和CPU消耗。 步骤5：利用约束进行重写 规则 ：利用主键、外键、唯一约束等来简化或改变查询。 过程 ： 外连接转内连接 ： 原查询： SELECT A.*, B.* FROM A LEFT JOIN B ON A.id = B.aid WHERE B.score > 90 分析 ：WHERE子句对右表B的字段进行了过滤（ B.score > 90 ）。如果B的字段为NULL（左连接产生的），条件 NULL > 90 的结果是UNKNOWN，会使该行被过滤掉。这实际上使得左连接的效果和内连接一样。 前提 ：需要确认 B.aid 是外键且引用 A.id ，或者 B.score 上有非空约束，以确保语义完全等价。 重写后： SELECT A.*, B.* FROM A INNER JOIN B ON A.id = B.aid WHERE B.score > 90 效果 ：内连接有更多的优化策略和算法可供选择，通常效率高于外连接。 消除DISTINCT ： 原查询： SELECT DISTINCT department_id FROM employees 分析 ：如果 department_id 是表 departments 的主键，并且在 employees 表中是外键，但查询是从 employees 表出发。 此时不能直接消除DISTINCT。但如果查询是 SELECT DISTINCT d.id FROM departments d JOIN ... ，且连接条件能保证不重复，则可能消除。 更典型的例子是，如果查询中包含GROUP BY子句，且分组列是主键或唯一键，则SELECT子句中的DISTINCT可能是多余的。 效果 ：将复杂的操作转换为更简单的操作，或直接消除不必要的操作。 步骤6：子查询优化 子查询（尤其是相关子查询）通常性能较差，重写目标是将它们转换为更高效的连接（JOIN）或半连接（SEMI-JOIN）。 规则 ：将特定形式的子查询展开为连接操作。 过程 ： IN子查询转半连接（Semi-Join） ： 原查询： SELECT * FROM products p WHERE p.category_id IN (SELECT id FROM categories WHERE name = 'Electronics') 重写后（逻辑等价）： SELECT p.* FROM products p SEMI JOIN categories c ON p.category_id = c.id WHERE c.name = 'Electronics' 说明 ：半连接只返回左表（ products ）中那些在右表（ categories ）中存在匹配行的行，且对于左表的每一行，即使右表有多个匹配，也只返回一次。现代数据库的优化器能自动执行这种转换。 EXISTS子查询 ：与IN子查询的优化类似。 标量子查询解相关（Scalar Subquery De-correlation） ： 原查询（相关子查询）： SELECT id, name, (SELECT COUNT(*) FROM orders o WHERE o.cid = c.id) AS order_count FROM customers c 重写思路：将其转换为一个外连接和分组查询。 重写后： SELECT c.id, c.name, COALESCE(o_cnt.count, 0) AS order_count FROM customers c LEFT JOIN (SELECT cid, COUNT(*) AS count FROM orders GROUP BY cid) o_cnt ON c.id = o_cnt.cid 效果 ：避免了对外部查询的每一行都执行一次内部查询（Nested Loops），转而使用更高效的分组和连接。 效果 ：极大提升子查询性能，是查询优化的关键战场。 总结 查询重写是数据库优化器在生成物理执行计划前，在逻辑计划阶段进行的优化。它依赖于牢固的关系代数基础和数据库约束知识，通过应用一系列等价变换规则（如简化、下推、展开、转换等），将原始SQL语句转化为一个语义相同但执行效率更高的形式。这个过程通常是自动的、基于规则的（Rule-Based），为后续基于成本的优化（Cost-Based Optimization）打下了坚实的基础。理解这些技术有助于DBA和开发者写出更优化、更能被优化器“理解”的SQL语句。