Apache Calcite教程-基础知识-SQL优化基础

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/QXC1281/article/details/89187221

Github SQL优化基础

基础概念

优化过程一般为以下过程

  1. 对SQL进行词法分心得到一个语法树
  2. 根据关系代数进行SQL的逻辑优化
  3. 根据代价估算算法进行物理查询的优化
  4. 执行器执行

逻辑优化

在逻辑优化主要解决的问题是,如何找出SQL等价的变换形式,使SQL执行更加高效
优化思路主要包括

  • 子句局部优化,例如,谓词重写,WHERE和Having条件化简的大部分情况都属于子句局部优化的范围
  • 子句管理优化,例如,外连接消除,连接消除,子查询优化
  • 局部与整体优化,例如:or重写,union操作
  • 形式变化优化,例如,嵌套消除
  • 语义优化
  • 其他优化

各种逻辑优化技术,主要依赖于关系代数和启发式规则进行

关系代数等价变换

关系代数表达式的等级:相同的关系代替两个表达式中相应的关系,所得到的结果相同的,两个关系表达式E1和E2等价,记为E1 = E2

查询语句可以表示为一颗二叉树:

  • 叶子是关系
  • 内部节点是云算符(或称算子,操作符,例如 LEFT OUT JOIN) ,表示左右子树的云算方式
  • 子树是子表示或者SQL片段
  • 根节点是最后操作的运算符
  • 根节点运算之后,得到的是SQL查询优化后的结果
  • 这样一棵树就是一个查询的路径
  • 多个关系连接,连接顺序不同,可以得出多个类似的二叉树
  • 查询优化就是找出代价最小的二叉树,即最优的查询路径。
  • 基于代价估算的查询优化就是通过计算和比较,找出花费最少的是优二叉树。

运算符角度优化考虑

不同的运算符优化可c减少中间生成物的大小和数量,节约IO和内存CPU等,从而提高执行速度。前提是优化前和优化后是等价的。

选择

基本选择性质

对同一个表的同样选择条件,作一次即可。
可优化的原因:

  • 幂等性:多次应用同一个选择有同样的效果
// 执行多次相等
select * from S where S.a = 1
  • 交换性:应用选择的次序在最终结果中没有影响
select * from S where S.a = 1 and S.b = 2
// 两个SQL相等
select * from S where S.b = 2 and S.a = 1 
  • 选择可有效减少在它的操作数中的元组数的运算(元组数减少)
分解有复杂条件的选择
  • 合取:合并多个选择为更少的需求值的选择,多个等式可以合并,等价于针对这些单独条件的一系列选择。
where A.a = B.b and B.b = C.c 可以合并为={A.a,B.b,C.c} 而不是两个等式={A.a,B.b}和={B.b,C.c}
  • 析取::分解它们使得其成员选择可被移动或单独优化, 等价于选择的并集。
where A.a=3 OR A.b>8 
如果A.a,A.b分别有索引,也许 select * from A where A.a = 3 union select * from A where A.b>8 可以提高效率 
// 合取 (DNF)
select * from S where  S.a=1 and ( S.b=2 or S.c=3 )

// 析取 (CNF)
select * from S where ( S.a = 1 and S.b=2 ) or ( S.a = 1 and S.c=3 )

选择和叉积
  • 尽可能选做选择:关系有N和M行,先做积运算将包含N*M行。先做选择运算,减少N和M,则可避免不满足条件的条件参与积的运算,节约时间减少结果的大小。

  • 尽可能下推选择:如果积不跟随着选择运算,可尝试使用其他规则从表达式树更高层下推选择。

select * from S join R where S.a =1 and R.b=2
// 做类似的转换
select * from (select * from S where S.a=1) ST join (select * from R where R.b=2) RT
选择和集合运算
  • 选择下推到的集合运算中:选择在差集,交集和并集算子上满足分配律

  • 选择下推到集合的运算

select * from (select * from S union select * from R) SR where SR.a = 1
// 做类似的转换
(select * from S where S.a = 1) union (select * from R where R.a = 1)
选择和集合运算图解
初始式 等级表达式1 等价表达式2 等价表达式3
选择和投影

在投影之前进行选择:如果选择条件中引用的字段是投影中的字段的子集,则选择与投影满足交换性。

投影

基本投影性质

尽可能先做投影:投影是幂等性的;投影可以减少元组大小。

select a from (select * from S ) ST
// 做类似的换换
select a from (select a from S ) ST

投影和集合云算

投影下推到集合运算中:投影在差集,交集和并集运算上满足分配律。

select a from (select * from S union select * from S) ST
// 做类似的换换
select a from (select a from S union select * from S) ST
选择和集合运算图解
初始式 等级表达式

运算规则度优化考虑

连接,笛卡尔积交换律

连接、做积运算,可交换前后位置,其结果不变。如两表连接算法中嵌套连接算法,对外表和内表有要求,外表尽可能小则有利于做“基于块的嵌套循环连接“,所以,通过交换律可以把元组少的表作为外表。

公式:

select * from S join R
// 等价于
select * from R join S

连接,笛卡尔积结合律

做连接、做积运算,如果新的结合有利于减少中间关系的大小,则可优先处理。

公式:

select * from ( S join R)  join Q
// 等价于
select * from S join ( R  join Q )

投影的串接定律

在同一个关系上,只需要做一次投影运算,且一次投影时选择多列同时完成。
所以许多数据库优化引擎为同一个关系收集齐本关系上的所有列(目标列和 WHERE, GROUP BY 等子句的本关系的列)

公式:

&space;\&space;A\in&space;B)

select a from ( select a,b,c from S)
//  串接定律
select a from ( select a from S)

选择的串接定律

选择条件可以合并,使得可一次就检查全部条件,不必多次过滤元组,所以可以把同层的合取条件收集在一起,统一判断。

公式:

select * from (select * from S where a = 1) ST where b = 2
// 等价于
select * from S where a = 1 and b = 2

投影与选择交换律

  • 先投影后选择,可以改为先选择后投影,这对于以行为存储格式的主流数据库而言,很有优化意义。存储方式总是在先获得元组后才能解析得到其中的列。
select a from (select * from S where a = 1)
// 等价于
select a from (select a from S ) ST where a = 1
  • 先择选后投影,可以改为带有选择条件中列的投影后再选择,最后完成最外层的投影,这样,使得内层的选择和投影可以同时进行。
select a from (select a from S ) ST where a = 1
// 等价于
select a from (select * from S where a = 1)

选择与笛卡尔积的分配律

条件下推到相关的关系上,先做选择后做积运算,这样可以减小中间结果的大小。

select * from S join R where S.a =1 and R.b=2
// 做类似的转换
select * from (select * from S where S.a=1) ST join (select * from R where R.b=2) RT

选择与并的分配律

条件下推到相关的关系上,先做选择后做并运算,可以减小每个关系输出结果的大小。

select * from (select * from S union select * from R) SR where SR.a = 1
// 分配律
(select * from S where S.a = 1) union (select * from R where R.a = 1)

选择与差的分配律

条件下推到相关的关系上,先做选择后做差运算,可以减小每个关系输出结果的大小

select * from (select * from S except select * from R) SR where SR.a = 1
// 分配律
(select * from S where S.a = 1) except (select * from R where R.a = 1)

投影与笛卡儿积的分配律

先做投影后做积,可减少做积前每个元组的长度,使得再做积后得到新元组的长度变短。

select a from (select * from S union select * from R) SR 
// 分配律
(select a from S) union (select a from R )

投影与并的分配律

先做投影后做并,可减少做并前每个元组的长度。

select a from (select * from S except select * from R) SR 
// 分配律
(select a from S) except (select a from R )

规则重写

选择操作

对应的是限制条件(格式类似 fieldconsant)优化方式是选择操作下推,
目的是尽量减少连接操作前的元组数,使得中间临时关系尽量少
这可减少IO和CPU等的消耗。

select * from S where sex = '男' and username = 'join'
// 优化为
select* from s where username = 'join' and sex = '男'

投影操作

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

select a from (select * from S where a = 1) ST
// 优化为
select a from (select a from S where a = 1) ST

连接操作

对应的是连接条件。(格式为field1field2, field1和field表示”不同表“上的列对象。表示两个表连接条件。

  1. ”多表连接中每个表被连接的顺序决定着效率。“,即如果ABC三个表,ABC, ACB, CBA, BCA等不同的连接后结果一样的话,
    则要计算哪种效率最高。
  2. 多表连接每个表被连接的顺序由用户语义决定,这决定着表之间的前后连接次序是不能随意更换的。

子查询优化

子查询分类

相关子查询

子查询的执行依赖于外层父查询的一些属性值.子查询因依赖于父查询的参数,当父查询改变事,子查询需要根据参数重新执行

SELECT * FROM S WHERE S.a IN (SELECT b FROM R WHERE S.a=R.b)
非相关子查询

子查询的执行不依赖于外层父查询的任何属性值,这样的子查询具有独立性,可以独自求解

SELECT * FROM S WHERE S.a IN (SELECT b FROM R WHERE R.b='a')

子查询合并

多个子查询能够合并成一个子查询.

等价的情况下。多个子查询能够合并成一个子查询。这样可以把多次表扫描,多次连接减少为单次表扫描和单次连接。

select * from S where a > 10 and a in (select c from R where b =1) or a in (select c from R where b=2 )
// 合并为
select * from S where a > 10 and a in (select c from R where b=1 or b=2)

子查询展开

又称子查询反嵌套,又称为子查询上拉。
把一些子查询置于外层的父查询中,作为连接关系与外层父查询并列。实质上是把某些子查询重写为等价的多表连接操作。

select * from t1, ( select * from t2 where t2.a2 > 10) v_t2 where t1.a1 < 10 and v_t2.a2 < 20
// 优化为
select * from t1, t2 where t1.a1 < 10 and t2.a2 < 20 and t2.a2 > 10

注意:

  • 如果子查询出现了聚集、GROUP BY, DISTINCT 子句,则子查询只能单独求解,不可以上拉到上层。
  • 如果子查询只是一个简单格式(SPJselect project join)的查询语句,则可以上拉到上层,这样往往能提高查询效率
子查询展开规则
  1. 如果上层查询的结果没有重复(即SELECT子句中包含主键),
    则可以展开其子查询,并且展开后的查询的SELECT子句前就回上DISTINCT标志。

  2. 如果上层有DISTINCT标志,则可以直接展开子查询

  3. 如果内层查询结果没有重复元组,则可以展开。

子查询展开的步骤
  1. 将子查询和上层查询的FROM子句连接,为同一个FROM子句,并修改相应的运行参数

  2. 将子查询的谓词符号进行相应修改。如 IN修改为=ANY

  3. 将子查询的WHERE条件作为一个整体与上层查询的WHERE条件合并,并用AND条件连接词连接。

ALL/SOME/ANY类型

如果子查询没有 GROUP BY 子句,也没有聚集函数。则可以使用如下表达式做等价转换:

  • val > ALL (select …) 等价为 val > MAX(select …)
  • val < ALL (select …) 等价为 val < min( select …)
  • val > any (select …) 等价为 val > min(select …)
  • val < any (select …) 等价为 val<max(select …)
  • val => ALL (select …) 等价为 val => MAX(select …)
  • val <= ALL (select …) 等价为 val <= min( select …)
  • val => any (select …) 等价为 val => min(select …)
  • val <= any (select …) 等价为 val <= max(select …)

聚集子查询消除

将聚集函数上推,将子查询转变为一个新的不包含聚集函数的子查询,并与父查询的部分或者全部表做左外连接。

等价谓词重写

LIKE规则

如:

name like 'abc%'
//重写为
name >= 'abc' and name < 'abd';

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

如果没有通配符(%或_)。则是与 = 等价

name like 'abc'
//重写为
name = 'abc'

BETWEEN-AND规则

sno BETWEEN 10 AND 20 
重写为
sno >= 10 and sno <=20

好处:如果sno建立了索引,则可以用索引扫描代替原来的BETWEEN-AND谓词限定的全表扫描,从而提高了查询的效率。

####IN转换OR规则
IN只是IN操作符,而不是IN子查询。改为OR可以更好地利用索引进行优化。将IN改为若干个OR可能会提高效率。

age IN (8, 12, 21)
//重写为
age = 8 or age = 12 or age = 21

效率是否提高,需要看数据库对IN谓词是否只支持全表扫描。如果数据库对IN谓词只支持全表扫描且OR谓词中表的age列存在索引,则转换后的查询效率会更好。

IN转换ANY规则

因为IN可以转换为OR,而OR可转换为ANY,所以可以直接把IN转换为ANY。这可能会提高效率。

age IN (8, 12, 21)
//重写为
age any (8, 12, 21)

效率是否提高,依赖于数据库对ANY操作的支持情况。
如:PostgreSQL没有显式支持 ANY 操作,但在内部实现时把IN操作转换为了ANY操作。(通过 explain 知道)

OR转换为ANY规则

这样可以更好地利用 MIN/MAX 操作进行优化。但(PG9.2.3 和 MySQL 5.6.10 目前都还没有支持)

ALL/ANT 转换为集函数规则

这样可以更好地利用 MIN/MAX 操作进行优化。如:

sno > ANY (10, 2*5+3, sqrt(9))
重写为
sno > sqrt(9)

通常,聚集函数MAX(), MIN()等的效率比ANY, ALL谓词的执行效率高。

NOT规则

NOT (col_1 != 2) 
//重写为 
col_1 = 2 

其他类似
好处:如果 col_1 上建立了索引,则可以用索引扫描代替原来的全表扫描。

OR重写并集规则

这条SQL会强迫查询优化器使用顺序存取,因为这个语句要检索的是OR操作的集合。假设,sex, age 上有索引,则可优化为:

如:

select * from student where ( sex = 'f' and sno > 15 ) or age > 18
// 重写
select * from student where sex = 'f' and sno > 15 union select * from student where age > 18

条件简化

  1. 把HAVING条件并入WHERE条件。(只有SQL语句不存在 GROUP BY 条件 或聚集函数的情况下才可以使用)

  2. 去除表达式中冗余的括号。这样子可以减少语法分析时产生的AND和OR树的层次。

  3. 常量传递。如:col_1 = col_2 and col_2 = 3 。改为:col_1 = 3 and col_2 = 3;

  4. 消除死码。如:永恒为假的条件。

  5. 表达式计算:如:where col_1 = 1 + 2 ,改为 where col_1 = 3

  6. 等式变换:化简条件(如反转关系操作符的操作数顺序)。如: -a = 3; 简化为 a = -3;

  7. 不等式变换。化简条件。如:a > 10 and b = 6 and a > 2 ,简化为 b = 6 and a > 10

  8. 布尔表达式变换。

  9. 谓词传递闭包。

  10. 任何一个布尔表达式都能被转换为一个等价的合取范式(CNF)。如:and 操作符是可交换的,所以优化器可以按先易后难的顺序计算表达式。

  11. 索引的利用。

group by优化

分组操作下移

可以减少元组个数,可以提高表连接速度

分组操作上移

如果之前有连接 or 过滤的操作,可以考虑分组上移,提高分组效率

order by 优化

排序消除

如果有默认的排序(如,索引等),可以将排序消除

排序下推

将排序结果尽量下推到基表中,有序基表进行连接,结果符合预期,防止最终在大表操作

distinct 优化

distinct消除

类似主键,唯一约束,索引,可以直接擦除distinct

distinct推入

生成含有distinct的反半连接查询时,先进行反半连接,再distinct,也许先distinct,在反半连接更优

distinct迁移

对连接操作结果distinct,可以把distinct移动到一个子查询中

物理优化

代价估算

总代价 = IO 代价 + CPU 代价
COST = P * a_page_cpu_time + W * T

P:计划运行时访问的页数,a_page_cpu_time 是每个页读取的时间花费,其积反映了IO代价
T:访问的元组。反映了CPU花费。(存储层是以页面为单位,数据以页面的形式读入内存,每个页面上可能有多个元组,访问元组需要解析元组结构,才能把元组上的字段读出,这消耗的是CPU)。如果是索引扫描,则还会包括索引读取的花费。
W:权重因子。表明IO到CPU的相关性,又称选择率(selectivity)。选择率用于表示在关系R中,满足条件“A<op>a”的元组数与R的所有元组N的比值。

其他情况不予说明,重点解释逻辑优化

展开阅读全文

没有更多推荐了,返回首页