数据库：查询优化，等价变化，代数优化，物理优化

燕双嘤

已于 2024-08-05 11:54:21 修改

阅读量7.8k

点赞数 28

分类专栏： MySQL/数据库原理文章标签：数据库 oracle sql

于 2020-11-19 21:33:33 首次发布

本文链接：https://blog.csdn.net/qq_42192693/article/details/109823556

版权

MySQL/数据库原理专栏收录该内容

21 篇文章

订阅专栏

1，查询处理

1.1，查询处理步骤

查询分析	对查询语句进行扫描、词法分析和语法分析
查询检查	对合法的查询语句进行语义检查，即根据数据字典中有关的模式定义检查语句中的数据库对象，如关系名、属性名是否有效；如果是对视图的操作，则要用视图消解方法把对视图的操作转换成对基本表的操作根据数据字典中的用户权限和完整性约束定义对用户的存取权限进行检查检查通过后把SQL查询语句转换成内部表示，即等价的关系代数表达式
查询优化	按照优化的层次，可以分为：代数优化、物理优化查询优化的选择依据：基于规则、基于代价和基于语义
查询执行	依据优化器得到的执行策略，由代码生成器生成执行这个查询计划的代码两种执行方法：自顶向下和自底向上 `【例】: SELECT * FROM Student WHERE Sage>20 使用B+树索引找到Sage=20的索引项，以此为入口点在B+树的顺序集上得到Sage>20的所有元组指针通过这些元组指针到student表中检索到所有年龄大于20的学生。`

1.2，选择运算的查询处理

★全表扫描算法，优点：简单有效，适用于数据量较小的表

应用于 SELECT * FROM STUDENT

①假设可以使用的内存为M块，按照物理次序读Student的M块到内存；

②检查内存的每个元组t，如果满足选择条件，则输出t

③如果student还有其他块未被处理，重复①和②

★索引扫描算法（等值）

应用于 SELECT * FROM STUDENT WHERE SNO= '201215121'

①当选择条件的属性上有索引，并且为等值条件查询时，使用索引得到符合条件的元组的指针

②通过元组指针在Student表中检索到该学生

★索引扫描算法（非等值或范围查询）

①当选择条件的属性上有索引，并且选择条件是范围查询或非等值查询，或非主属性的等值查询
【例】: SELECT * FROM Student WHERE Sage>20
使用B+树索引找到Sage=20的索引项，以此为入口点在B+树的顺序集上得到Sage>20的所有元组指针
通过这些元组指针到student表中检索到所有年龄大于20的学生。 

1.3，连接运算的查询处理

连接操作是查询处理中最耗时的操作之一

★嵌套循环算法

对外层循环(Student表)的每一个元组(s)，检索内层循环(SC表)中的每一个元组(sc)，检查这两个元组在连接属性(Sno)上是否相等，如果满足连接条件，则串接后作为结果输出，直到外层循环表中的元组处理完为止。

★排序-合并算法

如果连接的表没有排好序，先对Student表和SC表按连接属性Sno排序，取Student表中第一个Sno，依次扫描SC表中具有相同Sno的元组；当扫描到Sno不相同的第一个SC元组时，返回Student表扫描它的下一个元组，再扫描SC表中具有相同Sno的元组，把它们连接起来；重复上述步骤直到Student 表扫描完。

★索引连接算法

在SC表上已经建立属性Sno的索引。
对Student中每一个元组，由Sno值通过SC的索引查找相应的SC元组。
把这些SC元组和Student元组连接起来
循环执行②③，直到Student表中的元组处理完为止

★Hash Join算法

把连接属性作为hash码，用同一个hash函数把Student表和SC表中的元组散列到hash表中。
在划分阶段，对包含较少元组的表(如Student表)进行一遍处理，把它的元组按hash函数分散到hash表的桶中。
在试探阶段对另一个表(SC表)进行一遍处理，把SC表的元组也按同一个hash函数（hash码是连接属性）进行散列，把SC元组与桶中来自Student表并与之相匹配的元组连接起来。

2，查询优化

2.1，查询优化概述

关系系统的查询优化是关系数据库管理系统实现的关键技术又是关系系统的优点所在，它减轻了用户选择存取路径的负担。关系系统的查询优化由系统完成, 而不是由用户完成。

查询优化的总目标：寻求最优的执行计划使查询执行开销尽量小。

2.2，查询优化的必要性

【例】:求选修了2号课程的学生姓名。
用SQL表达：SELECT Student.Sname FROM Student,SC WHERE Student.Sno=SC.Sno AND SC.Cno=’2’

①先求Student和SC的笛卡尔积，然后从中选出两学号字段值相等、课程号为2的元组：

②先做Student和SC的自然连接，然后从中选出课程号为2的元组：

③先从Student中选出课程号为2的元组，然后将该结果与S连接：

①计算广义笛卡尔积 Student×SC

假定学生-课程数据库中有1000个学生记录，10000个选课记录；选修2号课程的选课记录为50个

设一块能装10个学生记录或100个选课记录，每次在内存中存放5块Student的元组、1块SC的元组，则Student表和SC表的总块数各为100。外循环一次，内循环20次

硬盘到内存：读取的总块数，设每秒能读写20块，则读的时间：2100块÷20块/秒 = 105秒

连接后的元组数为10^3×10^4=10^7。设每块能装10个元组，则写出10^6 块。

从内存到硬盘，写笛卡尔集中间结果的总花费时间：1000000块÷20块/秒 = 50000秒

读的时间：10^6块 ¸ 20块/秒 = 50000秒，满足条件的元组50个，设全部放入内存（不再临时存储）

假设选择后剩的50个元组均可放入内存，因此只需要CPU时间：105 + 50000 + 50000 » 10万秒（近28小时）

②计算自然连接 StudentSC

读取S表和SC表的策略不变，执行时间还是105秒。

因为SC表中的每一个学号都在S表中出现，而S表中无重复学号，故连接后的表和SC表的行数一样，为10000个元组，将它们临时存入盘中需（10000 ÷ 10）块=1000块，每秒能读写20块，那么内存到硬盘，写自然连接中间结果的总花费1000÷20块/秒 = 50秒

读取中间文件块，执行选择运算，读取的数据块= 1000 块，耗时50秒

假设选择后剩的50个元组均可放入内存，因此只需要CPU时间：105 + 50+ 50 » 205秒（约3.4分钟）

③从Student中选出课程号为2的元组，然后将该结果与S连接

对SC表作选择运算，只需读一遍SC表，存取（10000÷100)=100块，耗时100÷20块/秒=5秒，因为满足条件的元组仅50个，不必使用中间文件。

读取Student表，把读入的Student元组和内存中的SC元组作连接。也只需读一遍Student表，结果仍为50个元组需时 5秒。

把连接结果投影输出：5 + 5 = 10秒

假如SC表的Cno字段上有索引

第一步就不必读取所有的SC元组而只需读取Cno=‘2’的那些元组(50个)
存取的索引块和SC中满足条件的数据块大约总共3～4块

若Student表在Sno上也有索引

不必读取所有的Student元组
因为满足条件的SC记录仅50个，涉及最多50个Student记录
读取Student表的块数也可大大减少

2.3，关系代数等价变换规则

代数优化是基于关系代数等价变换规则的优化方法。

代数优化策略：通过对关系代数表达式的等价变换来提高查询效率

设E、E1、E2是关系代数表达式，若用相同的关系代替E1、E2中相应的关系变量后所得的结果关系相同，则称E1、E2等价，记作 E1≡E2

2.3，关系代数等价变换

2.4，关系代数经典的启发式规则

①选择、投影运算应尽可能先做

在优化策略中这是最重要、最基本的一条。因为这样参加连接的元组就可以大大减少，从而减少下一步运算的数据量。

②把选择和投影运算同时进行

如有若干投影和选择运算，并且它们都对同一个关系操作，则可以在扫描此关系的同时完成所有的这些运算以避免重复扫描关系。

③把投影同其前或其后的双目运算结合起来，没有必要为了去掉某些字段而扫描一遍关系。

每形成一个连接后的元组，就立即取出投影字段。而不是先连接形成一个临时关系，然后在再此临时关系上投影。

每取出Student的一个元组，先取出投影字段，然后与SC进行连接

④把某些选择同在它前面要执行的笛卡尔积结合起来成为一个连接运算，连接特别是等值连接运算要比同样关系上的笛卡尔积省很多时间。

⑤找出公共子表达式

如果这种重复出现的子表达式的结果不是很大的关系并且从外存中读入这个关系比计算该子表达式的时间少得多，则先计算一次公共子表达式并把结果写入中间文件是合算的。

当查询的是视图时，定义视图的表达式就是公共子表达式的情况

2.5，物理优化

基于规则的启发式优化：启发式规则是指那些在大多数情况下都适用，但不是在每种情况下都是适用的规则。

基于代价估算的优化：优化器估算不同执行策略的代价，并选出具有最小代价的执行计划。

两者结合的优化方法：常常先使用启发式规则，选取若干较优的候选方案，减少代价估算的工作量；然后分别计算这些候选方案的执行代价，较快地选出最终的优化方案。

2.6，基于启发式规则的存取路径选择优化

选择操作的启发式规则

★对于小关系，使用全表顺序扫描，即使选择列上有索引

★对于大关系，启发式规则有：

对于选择条件是“主码＝值”的查询：查询结果最多是一个元组，可以选择主码索引；一般的关系数据库管理系统会自动建立主码索引。
对于选择条件是“非主属性＝值”的查询，并且选择列上有索引：要估算查询结果的元组数目，如果比例较小(<10%)可以使用索引扫描方法，否则还是使用全表顺序扫描。
对于选择条件是属性上的非等值查询或者范围查询，并且选择列上有索引：要估算查询结果的元组数目，如果比例较小(<10%)可以使用索引扫描方法，否则还是使用全表顺序扫描
对于用AND连接的合取选择条件：①如果有涉及这些属性的组合索引，优先采用组合索引扫描方法；②如果某些属性上有一般的索引，可以用索引扫描方法，通过分别查找满足每个条件的指针，求指针的交集；通过索引查找满足部分条件的元组，然后在扫描这些元组时判断是否满足剩余条件；③其他情况则使用全表顺序扫描
对于用OR连接的析取选择条件，一般使用全表顺序扫描。

连接操作的启发式规则

①如果2个表都已经按照连接属性排序，选用排序-合并算法

②如果一个表在连接属性上有索引，选用索引连接算法

③如果上面2个规则都不适用，其中一个表较小，选用Hash join算法

④可以选用嵌套循环方法，并选择其中较小的表，确切地讲是占用的块数(b)较少的表，作为外表(外循环的表) 。

理由：设连接表R与S分别占用的块数为Br与Bs，连接操作使用的内存缓冲区块数为K，分配K-1块给外表；如果R为外表，则嵌套循环法存取的块数为Br+BrBs/(K-1)，显然应该选块数小的表作为外表。