查询优化技术概念

本文摘取自《数据库查询优化器的艺术》

一、查询优化技术简介 

   查询优化技术主要包括查询重用技术、查询重写规则技术、查询算法优化技术、并行查询的优化技术、分布式查询优化技术和其他优化技术6个方面的技术。

1.1 查询重用

    查询重用是指尽可能利用先前的执行结果，以达到节约查询计算全过程的时间并减少资源消耗的目的。

    目前查询重用技术主要集中在两个方面:

1. 查询结果的重用。在缓存区中分配一块缓冲块，存放该SQL语句文本和最后的结果集，当遇到同样的SQL输入是，可直接把结果返回。查询结果的重用技术节约了查询计划生成事件和查询执行过程的时间，减少了查询执行全过程的资源消耗。
2. 查询计划的重用。缓存一条查询语句的执行计划及其相应语法树结构。查询计划的重用技术减少了查询计划生成的时间和资源消耗。

    查询重用技术有利有弊：弊端，如结果集很大会消耗很大的内存资源，同样的SQL不同用户获取的结果集可能不完全相同；益处，节约了CPU和IO消耗。在使用过程中，趋利避害，应根据实际情况选用。

1.2 查询重写规则

    查询重写是查询语句的一种等价转换，即对于任何相关模式的任意状态都会产生相同的结果（相同的关系替代两个表达式中相应的关系，所得到的结果是相同的）。查询重写有两个目标：

1. 将查询转换为等价的、效率更高的形式，例如将效率低的谓词转换为效率高的谓词、消除重复条件等。
2. 尽量将查询重写为等价、简单且不受表顺序限制的形式，为物理查询优化阶段提供更多的选择，如视图的重写、子查询的合并转换等。

    查询重写的一句，是关系代数。关系代数的等价变换规则对查询重写提供了理论上的支持。查询重写后，查询优化器可能生成多个连接路径，可以从候选者中择优。

    对查询优化技术进行分类，可有以下4个角度：

1. 语法级。查询语言层的优化，基于语法进行优化
2. 代数级。查询使用形式逻辑进行优化，运用关系代数的原理进行优化
3. 语义级。根据完整性约束，对查询语句进行语义理解，推知一些可优化的操作
4. 物理级。物理优化技术，基于代价估算模型，比较得出各种执行方式中代价最小的。

    查询重写是基于语法级、代数级、语义级的优化，可以统一归属到逻辑优化的范畴：基于代价估算模型是物理层面的优化，是从连接路径中选择代价最小的路径的过程。

    查询重写技术优化思路主要包括：

1. 将过程性查询转换为描述性的查询，如视图重写
2. 将复杂的查询（如嵌套子查询、外连接、嵌套连接）尽可能转换为多表连接查询
3. 将效率低的谓词转换为等价的效率高的谓词（如等价谓词重写）
4. 利用等式和不等式的性质，简化WHERE、HAVING和ON条件

    如何改进现有查询重写规则的效率，如何发现更多更有效的重写规则，是查询优化的研究内容之一。常见的查询重写技术类型，每一类都有自己的规则，这些规则每月确定的、统一的规律。但重写的核心一定是”等价转换”，只有等价才能转换，这是需要特别强调的。

1.3 查询算法优化

    查询优化即求解给定查询语句的高效执行计划的过程。

    查询计划，也成为查询树，它由一系列内部的操作符组成，这些操作按一定的运算关系构成查询的一个执行方案。

    查询优化的目的就是生成最好的查询计划。通常有以下两个策略：

1. 基于规则优化。根据经验或一些已经探知或被证明有效的方式，定义为规则。用这些规则简化查询计划生成过程中符合可被简化的操作，使用启发式规则排除一些明显不好的存取路径，这就是基于规则的优化。   ？？？基于规则的优化和查询重写有什么区别？？？
2. 基于代价优化。根据一个代价评估模型，在生成查询计划的过程中，计算每条存取路径的花费，然后选择代价最小的作为子路径，这样直至所有表连接完毕得到一个完整的路径。

1.4 并行查询优化

    在并行数据库系统中，查询优化的目的是寻找具有最小响应时间的查询执行计划，这需要把查询工作分解为一些可以并行运行的自工作。

    一个查询是否能并行执行，取决于以下因素：

1. 系统中可用资源（如内存、缓存等）
2. CPU
3. 运算中特定代数运算符。在同一个SQL内，查询并行可以分为以下几种
4. 1. 操作内并行：将同一操作分解成多个独立的子操作，由不同的CPU同时执行
   2. 操作间并行：一条SQL查询语句可以分解成多个子操作，由多个CPU执行

1.5 分布式查询优化

    在分布式数据库系统中，查询策略优化（主要是数据传输策略）和局部处理优化（传统的单节点数据库的查询优化技术）是查询优化的重点。

    在查询优化策略中，数据的通信开销是优化算法考虑的主要因素。分布式查询优化以减少传输的次数和数据量作为查询优化的目标。

    在分布式数据库系统中，代价估算模型如下:

    总代价 = IO代价 + CPU代价 + 通信代价

1.6 其他优化

    如数据库集群系统中的SD（Share Disk）集群和SN（Share Nothing）集群。

二、逻辑查询优化

    查询优化器在逻辑优化阶段主要解决的问题是：如何找出SQL语句等价的变换形式，使得SQL执行更高效。

    逻辑查询优化的思路包括：

1. 子句局部优化，如等价谓词重写、WHERE和HAVING条件简化。
2. 子句间关联优化，如外连接消除、连接消除、子查询优化、视图重写等，它们的优化都需要借助其他子句、表定义或列属性等信息进行
3. 局部与整体优化，需要协同考虑局部表达式和整体的关系，如OR重写并集规则需要考虑UNION操作的花费和OR操作的花费
4. 形式变化优化，多个子句存在嵌套，可以通过形式的变化完成优化，如嵌套连接消除。
5. 语义优化，根据完整性约束，SQL表达的含义等信息对语句进行语义优化
6. 其他优化，根据一些规则对非SPJ做的其他优化。根据硬件环境进行的并行查询优化。

2.1 关系代数基础

    关系代数是关系型数据库查询语言的基础。关系模型由关系数据结构、关系操作集合和关系完整性约束三部分组成。

    关系代数的运算符包括以下4类：

1. 传统集合运算符。并UNION、交INTERSECTION、差DIFFERENCE、积EXTENDED CARTESIAN PRODUCT
2. 专门的关系运算符。选择SELECT、投影PROJECT、连接JOIN、除DIVEDE
3. 辅助运算符。包括算术比较符和逻辑运算符
4. 关系扩展运算符。如半连接SEMIJOIN、半差SEMIDIFFERENCE、扩展EXTEND、合计COMPOSITION、传递闭包TCLOSE

2.2 查询重写规则

    传统的OLTP使用基于选择（SELECT）（选择类似于filter）、投影（PROJECT）、连接（JOIN）3种基本操作相结合的查询，这种查询成为SPJ查询。

    数据库针对SPJ查询有一系列的优化规则，如下：

1. 选择操作：优化方式是选择操作下推，目的是尽量减少连接操作前的元组数，使得中间临时关系尽量少，减少IO和CPU消耗，节约内存空间
2. 投影操作：优化方式是投影操作下推，目的是尽量减少连接操作前的列数，使得中间关系尽量小，节约内存空间
3. 连接操作：这里涉及以下两个子问题
4. 1. 多表连接中每个表被连接的顺序决定着效率
   2. 多表连接每个表被连接的顺序由用户语义决定

2.2.1 子查询的优化

    子查询是查询语句中经常出现的一种类型，是比较耗时的操作。优化子查询对查询效率的提升有着直接的影响，所以子查询优化技术，是数据库查询优化引擎的重要研究内容。

    从子查询出现在SQL语句的位置看，它可以出现在目标列、FROM子句、WHERE子句、JOIN/ON子句、GROUPBY子句、HAVING子句、ORDERBY子句等维值。子查询出现在不同维值对优化的影响如下：

1. 目标列位置：子查询只能是标量子查询，只能返回一个字段
2. FROM子句位置：不能是相关子查询
3. WHERE子句位置：作为表达式的一部分，需要符合操作符和操作数的规范
4. JOIN/ON子句位置：JOIN同FROM，ON同WHERE
5. GROUPBY子句位置：目标列必须和GROUPBY关联（在GROUPBY中就必须在SELECT中）
6. ORDERBY子句位置：子查询在ORDERBY子句上没有实际意义

2.2.1.1 子查询分类

    根据子查询中涉及的关系对象与外层关系对象间的关系，子查询可以分为以下两类：

1. 相关子查询：子查询依赖于外层父查询的一些属性值
2. 非相关子查询：子查询具有独立性，可以独自求解，形成一个子查询计划先于外层的查询求解

    从语句构成的复杂程度看，可以分为以下三类：

1. SPJ子查询
2. GROUPBY子查询
3. 其他子查询

    从结果集的角度看，可以分为以下四类：

1. 标量子查询：返回一个单一值
2. 列子查询：返回一条单一列
3. 行子查询：返回一个单一元组
4. 表子查询：返回多行多列

2.2.1.2 子查询的优化思路

1. 子查询合并：在某些条件下（语义等价：两个子查询产生同样的结果集），多个子查询能够合并成一个子查询，减少表扫描次数
2. 子查询展开：又称为子查询上拉，即把某些子查询重写为等价的多表连接操作。减少查询层次
3. 聚集子查询消除：聚集函数上推，将子查询转变为一个新的不包含聚集函数的子查询，并与父查询的部分或全部表做左外连接

2.2.2 视图重写

    视图重写就是将对视图的引用重写为对基本表的引用。视图重写后的SQL多被元作为子查询进行进一步优化。所有的视图都可以被子查询替换，但不是所有的子查询都可以用视图替换。

2.2.3 等价谓词重写

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

    常用的谓词重写规则如下：

1. 改写LIKE规则为其他等价的谓词，以更好地利用索引进行优化。
2. BETWEEN-AND 规则
3. IN转OR
4. IN转ANY
5. OR转ANY
6. ALL/ANY转换集函数规则
7. NOT规则

2.2.4 条件简化

1. 把HAVING条件并入WHERE条件
2. 去除表达式中冗余的括号
3. 常量传递
4. 消除死码
5. 表达式计算
6. 等式变换
7. 不等式变换
8. 布尔表达式变换

2.2.5 外连接消除

    外连接分为左外连接，右外连接和全外连接。查询重写的一项技术就是把外连接转为内连接，原因如下

1. 查询优化器在处理外连接操作是所需执行的操作和时间多于内连接
2. 优化器在选择表连接顺序时，可以有更多灵活的选择，从而可以选择更好的表连接顺序，加快查询执行的速度。
3. 一些连接算法将数据量较小的表作为外表时，可以减少不必要的IO开销

    外连接可消除的条件：WHERE子句中与内表相关的条件满足“空值拒绝”，例如

select  a.id,  b.id from a left outer join b on  a.id =  b.id where  b.id is not null

可以重写为下面的inner join

select  a.id,  b.id from a inner join b on  a.id =  b.id

2.2.6 嵌套连接消除

    对于一个无嵌套的多表连接，表之间的连接次序是可以交换的，这样能灵活求解不同连接方式的花费，从而得到最小花费的连接方式。

    当执行连接的操作次序不是从左到右逐个进行时，就说明这样的连接表达式存在嵌套。

    关于嵌套连接的消除规则如下：

1. 当嵌套连接表达式只包括内连接时，括号可以去掉，这意味着表之间的次序可以交换，这是关系代数中连接的交换律的应用
2. 当连接表达式包括外连接时，括号不可以去掉，意味着表之间的次序只能按照原语义进行，至多能执行的就是外连接向内连接转换的优化

2.2.7 连接消除

1. 主外键关系的表进行的连接，可以消除主键表，这不会影响对外键表的查询
2. 唯一键作为连接条件，三表内连接可以去掉中间表（中间表的列只作为连接条件）

2.2.8 语义优化

    语义优化的常见方式如下：

1. 连接消除
2. 连接引入
3. 谓词引入
4. 检测空回答集
5. 排序优化
6. 唯一性使用

2.2.9 针对非SPJ的优化

1. GROUPBY优化
2. 1. 分组操作下移：减少关系元祖的个数
   2. 分组操作上移：提高分组操作的效率
3. ORDERBY优化
4. 1. 排序消除
   2. 排序下推
5. DISTINCT优化
6. 1. DISTINCT消除
   2. DISTINCT下推
   3. DISTINCT迁移