【数据库】数据库查询优化方法——查询重写1

查询的执行，就是查询处理的过程，即数据库按用户指定的SQL语句中的语义，执行语义所限定的操作。但SQL语句的执行效率对数据库的效率影响较大。为了提高查询语句的执行效率，对查询语句进行优化是必不可少的。对查询语句进行优化的技术就是查询优化技术，运用查询技术实现数据操纵功能的过程是确定给定查询的高效执行计划的过程。所谓执行计划就是查询树，它由一系列内部的操作符组成，这些操作符按一定的运算关系构成查询的一个执行方案。查询优化的追求目标，就是在数据库查询优化引擎生成一个执行策略的过程中，尽量使查询的总开销（总开销通常包括IO、CPU、网络传输等）达到最小。

一、逻辑查询优化

查询优化器在逻辑优化阶段主要解决的问题是：如何找出SQL语句等价的变换形式，使得SQL执行更高效。一条SQL查询语句结构复杂，包含多种类型的子句，优化操作依赖于表的一些属性信息（如索引和约束等）。可用于优化的思路包括：

❏子句局部优化。每种类型子句都可能存在优化方式，这是子句局部的优化，如等价谓词重写、WHERE和HAVING条件化简中的大部分情况，都属于这种子句范围内的局部优化。

❏子句间关联优化。子句与子句之间关联的语义存在优化的可能，如外连接消除、连接消除、子查询优化、视图重写等都属于子句间的关联优化，因为它们的优化都需要借助其他子句、表定义或列属性等信息进行。

❏局部与整体的优化。需要协同考虑局部表达式和整体的关系，如OR重写并集规则需要考虑UNION操作（UNION是变换后的整体的形式）的花费和OR操作（OR是局部表达式）的花费。

❏形式变化优化。多个子句存在嵌套，可以通过形式的变化完成优化，如嵌套连接消除。

❏语义优化。根据完整性约束、SQL表达的含义等信息对语句进行语义优化。

❏其他优化。根据一些规则对非SPJ做的其他优化、根据硬件环境进行的并行查询优化等。

各种逻辑优化技术依据关系代数和启发式规则进行。

二、查询重写

数据库在查询优化的过程中，会对这3种基本操作进行优化。优化的方式如下：

❏选择操作。对应的是限制条件（格式类似field＜op＞consant，field表示列对象，op是操作符，如=、＞等），优化方式是选择操作下推，目的是尽量减少连接操作前的元组数，使得中间临时关系尽量少（元组数少，连接得到的元组数就少），这样可减少IO和CPU的消耗，节约内存空间。

❏投影操作。对应的SELECT查询的目的列对象，优化方式是投影操作下推，目的是尽量减少连接操作前的列数，使得中间临时关系尽量小（特别注意差别：选择操作是使元组的个数“尽量少”，投影操作是使一条元组“尽量小”），这样虽然不能减少IO（多数数据库存储方式是行存储，元组是读取的最基本单位，所以要想操作列则必须读取一行数据），但可以各减少连接后的中间关系的元组大小，节约内存空间。

❏连接操作。对应的是连接条件（格式类似field_1＜op＞field_2，field_1和field_2表示不同表上的列对象，op是操作符，如=、＞等），表示两个表连接的条件。这里涉及以下两个子问题。

①.多表连接中每个表被连接的顺序决定着效率。如果一个查询语句只有一个表，则这样的语句很简单；但如果有多个表，则会涉及表之间以什么样的顺序连接效率最高效（如A、B、C三表连接，如果ABC、ACB、BCA等连接后的结果集一样，则计算哪种连接次序的效率最高，是需要考虑的问题）。

②.多表连接每个表被连接的顺序由用户语义决定。查询语句多表连接有着不同的语义（如是笛卡儿集、内连接，还是外连接中的左外连接等），这决定着表之间的前后连接次序是不能随意更换的，否则，结果集中数据是不同的。因此，表的前后连接次序是不能随意交换的。

2.1子查询的优化

在数据库实现早期，查询优化器对子查询一般采用嵌套执行的方式，即对父查询中的每一行，都执行一次子查询，这样子查询会执行很多次。这种执行方式效率很低。而对子查询进行优化，可能带来几个数量级的查询效率的提高。子查询转变成为连接操作之后，会得到如下好处：

❏子查询不用执行很多次。

❏优化器可以根据统计信息来选择不同的连接方法和不同的连接顺序。

❏子查询中的连接条件、过滤条件分别变成了父查询的连接条件、过滤条件，优化器可以对这些条件进行下推，以提高执行效率。

2.1.1子查询优化技术的思路：

❏子查询合并（Subquery Coalescing）。在某些条件下（语义等价：两个查询块产生同样的结果集），多个子查询能够合并成一个子查询（合并后还是子查询，以后可以通过其他技术消除子查询）。这样可以把多次表扫描、多次连接减少为单次表扫描和单次连接。

❏子查询展开（Subquery Unnesting）。又称子查询反嵌套，又称为子查询上拉。把一些子查询置于外层的父查询中，作为连接关系与外层父查询并列，其实质是把某些子查询重写为等价的多表连接操作（展开后，子查询不存在了，外层查询变成了多表连接）。带来的好处是，有关的访问路径、连接方法和连接顺序可能被有效使用，使得查询语句的层次尽可能地减少。常见的IN/ANY/SOME/ALL/EXISTS依据情况转换为半连接（SEMI JOIN）、普通类型的子查询消除等情况属于此类

❏聚集子查询消除（Aggregate Subquery Elimination）。聚集函数上推，将子查询转变为一个新的不包含聚集函数的子查询，并与父查询的部分或者全部表做左外连接

子查询的格式有多种，常见的子查询格式有(not)IN类型、ALL/ANY/SOME类型、(not)EXISTS类型。

2.2等价谓词重写

数据库执行引擎对一些谓词处理的效率要高于其他谓词，基于这点，把逻辑表达式重写成等价的且效率更高的形式，能有效提高查询执行效率。常见的等价谓词重写规则如下:

1.LIKE规则

LIKE谓词是SQL标准支持的一种模式匹配比较操作，LIKE规则是对LIKE谓词的等价重写，即改写LIKE谓词为其他等价的谓词，以更好地利用索引进行优化。如列名为name的LIKE操作示例如下：

select * from tableName where name like'abc%'

重写为：

select * from tableName where name >='abc' and name<'abd'

应用LIKE规则的好处是：转换前针对LIKE谓词只能进行全表扫描，如果name列上存在索引，则转换后可以进行索引范围扫描。

2.3条件化简

WHERE、HAVING和ON条件由许多表达式组成，而这些表达式在某些时候彼此之间存在一定的联系。利用等式和不等式的性质，可以将WHERE、HAVING和ON条件化简，但不同数据库的实现可能不完全相同。

❏把HAVING条件并入WHERE条件。便于统一、集中化解条件子句，节约多次化解时间。但不是任何情况下HAVING条件都可以并入WHERE条件，只有在SQL语句中不存在GROUPBY条件或聚集函数的情况下，才能将HAVING条件与WHERE条件的进行合并。

❏去除表达式中冗余的括号。这样可以减少语法分析时产生的AND和OR树的层次。如((a AND b)AND(c AND d))就可以化简为a AND b AND c ANDd。

❏常量传递。对不同关系可以使得条件分离后有效实施“选择下推”，从而可以极大地减小中间关系的规模。如col_1=col_2AND col_2=3就可以化简为col_1=3AND col_2=3。操作符=、＜、＞、＜=、＞=、＜＞、LIKE中的任何一个，在col_1＜操作符＞col_2条件中都会发生常量传递。

❏消除死码。化简条件，将不必要的条件去除。如WHERE(0＞1AND s1=5)，0＞1使得AND恒为假，则WHERE条件恒为假。此时就不必再对该SQL语句进行优化和执行了，加快了查询执行的速度。

❏表达式计算。对可以求解的表达式进行计算，得出结果。如WHEREcol_1=1+2变换为WHERE col_1=3。

❏等式变换。化简条件（如反转关系操作符的操作数的顺序），从而改变某些表的访问路径。如-a=3可化简为a=-3。这样的好处是如果a上有索引，则可以利用索引扫描来加快访问。

❏不等式变换。化简条件，将不必要的重复条件去除。如a＞10ANDb=6AND a＞2可化简为b=6AND a＞10。

❏布尔表达式变换。在上面的内容中，涉及了一些布尔表达式参与的变换（如上一条中的示例是AND的表达式）。布尔表达式还有如下规则指导化简：

①谓词传递闭包。一些比较操作符，如＜、＞等，具有传递性，可以起到化简表达式的作用。如由a＞b AND b＞2可以推导出a＞b AND b＞2AND a＞2，a＞2是一个隐含条件，这样把a＞2和b＞2分别下推到对应的关系上，就可以减少参与比较操作a＞b的元组了。

②索引的利用。如果一个合取项上存在索引，则先判断索引是否可用，如能利用索引快速得出合取项的值，则能加快判断速度。同理，OR表达式中的子项也可以利用索引。