【数据库系统】数据库系统概论====第九章 关系查询处理和查询优化

第九章 关系查询处理和查询优化

---

前言

本章首先介绍关系数据库管理系统的查询处理步骤，然后介绍查询的优化。
查询优化分类：

• 代数优化：也称逻辑优化，是指关系代数表达式的优化。
• 物理优化：也称非代数优化。是指存取路径和底层操作算法的选择。

9.1关系数据库系统的查询处理

9.1.1查询处理步骤

关系数据库管理系统查询处理分为：查询分析、查询检查、查询优化、查询执行。

1. 查询分析
   对查询语句进行扫描、词法分析和语法分析。

• 词法分析：从查询语句中识别出正确的语言符号。
• 语法分析：进行语法检查。

2. 查询检查
   （1）合法性检查：根据数据字典中有关的模式定义检查语句中的数据库对象，如关系名、属性名是否存在和有效。
   （2）视图转换：如果是对视图的操作，则要用视图消解方法把对视图的操作转换成对基本表的操作。
   （3）安全性和完整性初步检查：根据数据字典中的用户权限和完整性约束定义对用户的存取权限进行检查。
   检查通过后把SQL查询语句转换成内部表示，即等价的关系代数表达式。关系数据库管理系统一般都用查询树，也称为语法分析树来表示扩展的关系代数表达式。
3. 查询优化
   查询优化即选择一个高效执行的查询处理策略。
   （1）查询优化分类

• 代数优化/逻辑优化：指关系代数表达式的优化，即按照一定的规则，通过对关系表达式进行等价变化，改变关系代数操作的次序和组合，使查询执行更高效。
• 物理优化：指存取路径和底层操作算法的选择。
  （2）查询优化的选择依据
• 基于规则（rule based）
• 基于代价（cost based）
• 基于语义（semantic based）

4. 查询执行
   依据优化器得到的执行策略生成查询执行计划，由代码生成器生成执行查询计划的代码，然后执行这个查询计划，回送查询结果。
   

9.1.2实现查询操作的算法示例

1. 选择操作的典型实现
   （1）全表扫描方法
   对查询的基本表顺序扫描，逐一检查每个元组是否满足选择条件，把满足条件的元组作为结果输出。该方法适合小表，不适合大表。
   （2）索引扫描方法
   适合于选择条件中的属性上有索引（例如B+树索引或Hash索引），通过索引先找到蛮子条件的元组主码或元组指针，再通过元组指针直接在查询的基本表中找到元组。
   全表扫描算法思想：
   假设可以使用的内存为M块。
   ①按照物理次序读Studentd M块到内存；
   ②检查内存的每个元组t，如果满足选择条件，则输出t；
   ③如果Student还有其他块未被处理，重复①和②。
2. 连接操作的实现
   连接操作是查询处理中最耗时的操作之一，本节只讨论等值连接（或自然连接）最常用的实现算法。
   （1）嵌套循环算法
   （2）排序-合并算法
   （3）索引连接算法
   （4）Hash Join算法
   第一阶段：划分阶段，也称为创建阶段
   对包含较少元组的表进行一遍处理，把它的元组按Hash函数分散到Hash表的桶中。
   第二阶段：试探阶段也称为连接阶段
   对包含较多元组的表进行一遍处理，把表的元组也按照同一个Hash函数（Hash码是连接属性）进行散列，把元组与桶中来自较少元组的表并与之相匹配的元组连接起来。

• 算法前提：较小的表在第一阶段后可完全放入内存Hash桶中。

9.2关系数据库系统的查询优化

查询优化在关系数据库系统中有着非常重要的地位。关系查询优化是影响数据库管理系统性能的关键因素。由于关系表达式的语义级别很高，使关系系统可以从关系表达式中分析查询语义，提供了执行查询优化的可能性。

9.2.1查询优化概述

关系系统的查询优化：是关系数据库管理系统实现的关键技术又是关系系统的优点所在，减轻了用户选择存取路径的负担。用户只要提出“干什么”，不必指出“怎么干”。
非关系系统：用户使用过程化的语言表达查询要求，执行何种记录级的操作，以及操作的序列是由用户来决定的。因此，用户必须了解存取路径，系统要提供用户选择存取路径的手段，查询效率由用户的存取策略决定，如果用户做了不当的选择，系统是无法对此加以改进的。
查询优化的优点不仅在于用户不必考虑如何最好地表达查询以获得较好的效率，还在于系统可比用户程序的“优化”做得更好。主要原因是：
（1）优化器可以从数据字典中获取许多统计信息，而用户程序则难以获得这些信息。
（2）如果数据库的物理统计信息改变了，系统可自动对查询重新优化以选择相适应的执行计划。在非关系系统中必须重写程序，而重写程序在实际应用中往往是不太可能的。
（3）优化器可以考虑数百种不同的执行计划，程序员一般只能考虑有限的几种可能。
（4）优化器中包括了很多复杂的优化技术，这些优化技术往往只有最好的程序员才能掌握。系统的自动优化相当于使得所有人都拥有这些优化技术。
关系数据库管理系统通过某种代价模型计算出各种查询执行策略的执行代价，然后选取代价最小的执行方案。
在集中式数据库中，执行代价主要包括：
磁盘存取块数（I/O代价）、处理时间（CPU代价）、查询的内存开销，其中I/O代价是最主要的，因为I/O操作涉及机械动作。
在分布式数据库中，执行代价主要包括：
总代价=I/O代价+CPU代价+内存代价+通信代价
说明：

• 因为I/O操作涉及机械动作较耗时，所以在计算查询代价时一般用查询处理读写的块数作为衡量单位。
• 查询优化的总目标是选择有效的策略，求得给定关系表达式的值，使得查询代价最小（实际上是较小）。

9.2.2一个实例

一个关系查询可以对应不同的执行方案，其效率可能相差非常大。

例：求选修了2号课程的学生姓名。 SELECT Student.Sname FROM Student，SC WHERE
Student.Sno=SC.Sno AND SC.Cno=‘2’；
假定学生-课程数据库中有1000个学生记录，10000个选课记录选修2号课程的选课记录为50个。
可以用多种等价的关系代数表达式来完成这一查询：
$$Q_1={\pi }_{S_{name}}({\sigma }_{S_{student}.Sno=SC.Sno\wedge SC.Cno{=}^{\prime }{2}^{\prime }}(Student\times SC))$$$$Q_2={\pi }_{S_{name}}({\sigma }_{SC.Cno{=}^{\prime }{2}^{\prime }}(Student\bowtie SC))$$$$Q_3={\pi }_{Sname}(Student\bowtie {\sigma }_{SC.Cno{=}^{\prime }{2}^{\prime }}(SC))$$

1. 第一种情况
   $$Q_1={\pi }_{S_{name}}({\sigma }_{S_{student}.Sno=SC.Sno\wedge SC.Cno{=}^{\prime }{2}^{\prime }}(Student\times SC))$$
   （1）计算广义笛卡尔积 ①在内存中尽可能多地装入某个表（Student表）的若干块，留出一块 存放另一个表（如SC表）的元组。
   ②把SC中的每个元组和Student中的每个元组连接，连接后的元组装满一块后就写到中间文件上。
   ③从SC中读入一块和内存中的Student元组连接，直到SC表处理完。 ④重复上述处理过程，直到把Student表处理完。
   设一个块能装10个Student元组或100个SC元组，在内存中存放5块Student元组和1块SC元组，则读取总块数为：
   $$\frac{1000}{10}+\frac{1000}{10\times 5}\times \frac{10000}{100}=100+20\times 100=2100块$$
   其中：读Student表100块，读SC表20遍，每遍100块，则总计要读取2100数据块。
   连接后的元组数为$10^3\times 10^4=10^7$。设每块能装10个元组，则写出$10^6$块。 （2）作选择操作
   依次读入连接后的元组，按照选择条件选取满足要求的记录，假设内存处理时间忽略。读取中间文件花费的时间（同写中间文件一样）需读入$10^6$块。若满足条件的元组仅50个，均可放在内存。
   （3）作投影操作 把第（2）步的结果在Sname上作投影输出，得到最终结果。
   第一种情况下执行查询的总读写数据块$=2100+10^6+10^6$。
2. 第二种情况
   $$Q_2={\pi }_{S_{name}}({\sigma }_{SC.Cno{=}^{\prime }{2}^{\prime }}(Student\bowtie SC))$$
   （1）计算自然连接
   执行自然连接，读取Student和SC表的策略不变，总的读取块数仍为2100块，自然连接的结果比第一种情况大大减少，为$10^4$个元组，写出数据块=$10^3$块。
   （2）读取中间文件块，执行选择运算，读取的数据块$=10^3$块。 （3）把第（2）步结果投影输出。
   第二种情况下执行查询的总读写数据块$=2100+10^3+10^3$，其执行代价大约是第一种情况的$\frac{1}{488}$。
3. 第三种情况
   $$Q_3={\pi }_{Sname}(Student\bowtie {\sigma }_{SC.Cno{=}^{\prime }{2}^{\prime }}(SC))$$
   （1）先对SC表作选择运算，只需要读一遍SC表，存取100块，因为满足条件的元组仅50个，不必使用中间文件。
   （2）读取Student表，把读入的Student元组和内存中的SC元组做连接。也只需读一遍Student表共100块。
   （3）把连接结果投影输出。 第三种情况总的读写数据块=100+100.其执行代价大约是第一种情况的万分之一，是第二种情况的20分之一。
   分析：
   假如SC表的Cno字段上有索引，第一步就不必读取所有的SC元组而只需读取Cno=‘2’的元组（50个）。存取的索引块和SC中满足条件的数据块大约总共3~4块。
   若Student表在Sno上也有索引，不必读所有的Student元组，因为满足条件的SC记录仅50个，涉及最多50个Student记录，读取Student表的块数也可大大减少。
   总结： 把代数表达式$Q_1$变换为$Q_2$、$Q_3$
   
   有选择和连接操作时，先做选择操作，这样参加连接的元组就可以大大减少，这是代数优化。
   在$Q_3$中，SC表的选择操作算法有全表扫描或索引扫描，经过初步估算，索引扫描方法较优。
   对于Student和SC表的连接，利用Student表上的索引，采用索引连接代价也较小，这就是物理优化。

9.3代数优化

9.3.1关系代数表达式等价变换规则

代数优化策略是通过对关系代数表达式的等价变换来提高查询效率。
关系代数表达式的等价是指用相同的关系代替两个表达式中相应的关系，所得到的结果是相同的，两个关系表达式$E_1$和$E_2$是等价的，可记为$E_1=E_2$。
常用的等价变换规则：

1. 连接、笛卡尔积交换律
   设$E_1$和$E_2$是关系代数表达式，F是连接运算的条件，则有
   $$E_1\times E_2\equiv E_2\times E_1$$$$E_1\bowtie E_2\equiv E_2\bowtie E_1$$$$E_1{\bowtie }_F{E}_2\equiv E_2{\bowtie }_F{E}_1$$
2. 连接、笛卡尔积的结合律
   设$E_1$，$E_2$，$E_3$是关系代数表达式，$F_1$和$F_2$是连接运算的条件
   $$(E_1\times E_2)\times E_3\equiv E_1\times (E_2\times E_3)$$$$(E_1\bowtie E_2)\bowtie E_3\equiv E_1\bowtie (E_2\bowtie E_3)$$$$(E_1{\bowtie }_{F_1}{E}_2){\bowtie }_{F_2}{E}_3\equiv E_1{\bowtie }_{F_2}(E_2{\bowtie }_{F_1}{E}_3)$$
3. 投影的串接定律
   $${\pi }_{A_1,A_2,...,A_n}({\pi }_{B_1,B_2,...,B_m}(E))\equiv {\pi }_{A_1,A_2,...,A_n}(E)$$
   E是关系代数表达式，Ai（i=1,2，…，n），Bj（j=1,2，…，m）是属性名，{$A_1，A_2，...，A_n$}构成{$B_1，B_2，...，B_m$}的子集。
4. 选择的串接定律
   $${\sigma }_{F_1}({\sigma }_{F_2}(E))\equiv {\sigma }_{F_1\wedge F_2}(E)$$
   E是关系代数表达式，$F_1$，$F_2$是选择条件，选择的串接律说明选择条件可以合并，这样一次就可检查全部条件。
5. 选择与投影操作的交换律
   $${\sigma }_F({\pi }_{A_1,A_2,...,A_n}(E))\equiv {\pi }_{A_1,A_2,...,A_n}({\sigma }_F(E))$$
   选择条件F只涉及属性$A_1，A_2，...，A_n$，若F中有不属于$A_1，A_2，...，A_n$的属性$B_1，B_2，...，B_m$有更一般规则：
   $${\pi }_{A_1,A_2,...,A_n}({\sigma }_F(E))\equiv {\pi }_{A_1,A_2,...,A_n}({\sigma }_F({\pi }_{A_1,A_2,...,A_n,B_1,B_2,...,B_m}(E)))$$
6. 选择与笛卡尔积的交换律
   如果F中涉及的属性都是$E_1$中的属性，则：
   $${\sigma }_F(E_1\times E_2)\equiv {\sigma }_F(E_1)\times E_2$$
   如$F=F_1\wedge F_2$，且$F_1$只涉及$E_1$中的属性，$F_2$只涉及$E_2$中的属性，则由上面的等价变换规则1,4,6可推出：
   $${\sigma }_F(E_1\times E_2)\equiv {\sigma }_{F_1}(E_1)\times {\sigma }_{F_2}(E_2)$$
   若$F_1$只涉及$E_1$中的属性，$F_2$涉及$E_1$和$E_2$两者的属性，则仍有：
   $${\sigma }_F(E_1\times E_2)\equiv {\sigma }_{F_2}({\sigma }_{F_1}(E_1)\times E_2)$$
   它使部分选择在笛卡尔积前先做。
7. 选择与并的分配律
   设$E\equiv E_1\cup E_2$，$E_1$，$E_2$有相同的属性名，则
   $${\sigma }_F(E_1\cup E_2)\equiv {\sigma }_F(E_1)\cup {\sigma }_F(E_2)$$
8. 与差运算的分配律
   若$E_1$，$E_2$有相同的属性名，则
   $${\sigma }_F(E_1-E_2)\equiv {\sigma }_F(E_1)-{\sigma }_F(E_2)$$
9. 选择对自然连接的分配律
   $${\sigma }_F(E_1\bowtie E_2)\equiv {\sigma }_F(E_1)\bowtie {\sigma }_F(E_2)$$
10. 投影与笛卡尔积的分配律
    设$E_1$和$E_2$是两个关系表达式，$A_1，A_2，...，A_n$是$E_1$的属性，$B_1，B_2，...，B_m$是$E_2$的属性，则：
    $${\pi }_{A_1,A_2,...,A_n,B_1,B_2,...,B_m}(E_1\times E_2)\equiv {\pi }_{A_1,A_2,...,A_n}(E_1)\times {\pi }_{B_1,B_2,...,B_m}(E_2)$$
11. 投影与并的分配律
    设$E_1$和$E_2$有相同的属性名，则：
    $${\pi }_{A_1,A_2,...,A_n}(E_1\cup E_2)\equiv {\pi }_{A_1,A_2,...,A_n}(E_1)\cup {\pi }_{A_1,A_2,...,A_n}(E_2)$$

9.3.2查询树的启发式优化

典型的启发式规则：
（1）选择运算应尽可能先做
在优化策略中这是最重要、最基本的一条。
（2）把投影运算和选择运算同时进行
如有若干投影和选择运算，且它们都对同一个关系操作，则可以在扫描此关系的同时完成所有的这些运算以避免重复扫描关系。
（3）把投影同其前或其后的双目运算结合起来，没有必要为了去掉某些字段而扫描一遍关系。
（4）把某些选择同在它前面要执行的笛卡尔积结合起来成为一个连接运算，连接特别是等值连接运算要比同样关系上的笛卡尔积省很多时间。
（5）找出公共子表达式。
如果这种重复出现的子表达式的结果不是很大的关系，并且从外存中读入这个关系比计算该子表达式的时间少得多，则先计算一次公共子表达式并把结果写入中间文件。
当查询的是视图时，定义视图的表达式就是公共子表达式的情况。
遵循这些启发式规则，应用9.3.1的等价变换公式来优化关系表达式的算法。
算法：关系表达式的优化。
输入：一个关系表达式的查询树。
输出：优化的查询树。
方法：
（1）利用等价变换规则4把形如$\sigma F_1\wedge F_2\wedge ...\wedge F_n(E)$变换为$\sigma F_1(\sigma F_2(...(\sigma F_n（E)...))$。
（2）对每一个选择利用等价变换规则4~9尽可能把它移到树的叶端。
（3）对每一个投影利用等价变换规则3,5,10,11中的一般形式尽可能把它移向树的叶端。
注意：等价变换规则3使一些投影消失或使一些投影出现，规则5把一个投影分裂成两个，其中一个有可能被移向树的叶端。
（4）利用等价变换规则3~5.把选择和投影的串接合并成单个选择、单个投影或一个选择后跟一个投影，使多个选择或投影能同时执行，或在一次扫描中全部完成。
（5）把上述得到的语法树的内节点分组。
每一双目运算(×,⋈,$\cup$,-)和它所有的直接祖先为一组(这些直接祖先是$\sigma ,\pi$运算)。
如果其后代直到叶子全是单目运算，则也将它们并入该组，但当双目运算时笛卡尔积（×），而且后面不是与它组成等值连接的选择时，则不能把选择与这个双目运算组成同一组。

9.4物理优化

代数优化改变查询语句中操作的次序和组合，不涉及底层的存取路径。对于查询语句有许多存取方案，它们的执行 效率不同，仅仅进行代数优化是不够的。
物理优化就是要选择高效合理的操作算法或存取路径，求得优化的查询计划。
物理优化方法：

1. 基于规则的启发式优化
   启发式规则是指那些在大多数情况下都适用，但不是在每种情况下都适用的规则。
2. 基于代价估算的优化
   优化器估算不同执行策略的代价，并选出具有最小代价的执行计划。
3. 两者结合的优化方法
   常常先使用启发式规则，选取若干较优的候选方案，减少代价估算的工作量。然后分别计算这些候选方案的执行代价，较快地选出最终的优化方案。

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

1. 选择操作的启发式规则
   对于小关系，使用全表顺序扫描，即使选择列上有索引。
   对于大关系，启发式规则有：
   （1）对于选择条件“主码=值”的查询，查询结果最多是一个元组，可以选择主码索引。一般的关系数据库管理系统会自动建立主码索引。
   （2）对于选择条件是“非主属性=值”的查询，并且选择列上有索引，要估算查询结果的元组数目。如果比例较小（<10%）可以使用索引扫描方法，否则还是使用全表顺序扫描。
   （3）对于选择条件是属性上的非等值查询或者范围查询，并且选择列上有索引，要估算查询结果的元组数目，如果比例较小（<10%）可以使用索引扫描方法，否则还是使用全表顺序扫描。
   （4）对于用AND连接的合取选择条件，如果有涉及这些属性的组合索引，优先采用组合索引扫描方法。
   如果某些属性上有一般的索引，可以用索引扫描方法，其他情况：使用全表扫描。
   （5）对于用OR连接的析取选择条件，一般使用全表顺序扫描。
2. 连接操作的启发式规则
   （1）如果2个表都已按照连接属性排序，选用排序-合并算法。
   （2）如果一个表在连接属性上有索引，选用索引连接算法。
   （3）如果上面2个规则都不适用，其中一个表较小，选用Hash join算。
   （4）可以选用嵌套循环方法，并选择其中较小的表（占用块数较少的表），作为外表（外循环的表）。理由：
   设连接表R与S分别占用的块数为Br与Bs，连接操作使用的内存缓冲区块数为K，分配K-1块给外表，如果R为外表，则嵌套循环法存取的块数为Br+BrBs/（K-1），显然应该选块数小的表作为外表。

9.4.2基于代价的优化

启发式规则优化是定性的选择，适合解释执行的系统。解释执行的系统，优化开销包含在查询总开销之中，编译执行的系统中查询优化和查询执行是分开的。因此，可以采用精细复杂一些的基于代价的优化方法。

1. 统计信息
   基于代价的优化方法要计算查询的各种不同执行方案的执行代价，它与数据库的状态相关，优化器需要的统计信息主要包括：
   （1）对每个基本表
   该表的元组总数（N）、元组长度（l）、占用块数（B）、占用的溢出块数（BO）。
   （2）对基表的每个列
   该列不同值的个数（m）、列最大值、最小值、列上是否已经建立了索引、那种索引（B+树索引、Hash索引、聚集索引）。
   可以计算选择率（f）：如果不同值分布均匀，f=1/m；如果不同分布不均匀，每个值的选择率，f=具有该值的元组数/N。
   （3）对索引
   索引的层数（L）、不同索引值的个数、索引的选择基数S（S个元组具有某个索引值）、索引的叶结点数（Y）。
2. 代价估算示例
   （1）全表扫描算法的代价估算公式
   如果基本表大小为B块，全表扫描算法的代价cost=B。
   如果选择条件是“码=值”，那么平均搜索代价cost=B/2。
   （2）索引扫描算法的代价估算公式
   如果选择条件是“码=值”，则采用该表的主索引。若为B+树，层数为L，需要存取B+树中从根结点到叶结点L块，再加上基本表中该元组所在的那一块，所以cost=L+1。
   如果选择条件涉及非码属性，若为B+树索引，选择条件是相等比较，S是索引的选择基数（有S个元组满足条件），满足条件的元组可能会保存在不同的块上，所以（最坏的情况）cost=L+S。
   如果比较条件是>，≥，＜，≤操作，假设有一半元组满足条件，就要存取一半的叶结点，通过索引访问一半的表存储块，cost=L+Y/2+B/2，如果可以获得更准确的选择基数，可以进一步修正Y/2与B/2。
   （3）嵌套循环连接算法的代价估算公式
   嵌套循环连接算法的代价：cost=Br+BrBs/（K-1）
   如果需要把连接结果写回磁盘，则代价为：
   $$cost=Br+BrBs/(K-1)+(Frs\ast Nr\ast Ns)/Mrs$$
   其中Frs为连接选择性，表示连接结果元组数的比例。
   Mrs是存放连接结果的块因子，表示每块中可以存放的结果元组数目。
   （4）排序-合并连接算法的代价估算公式
   如果连接表已经按照连接属性排好序，则代价为：
   $$cost=Br+Bs+(Frs\ast Nr\ast Ns)/Mrs$$
   如果必须对文件排序，还需要在代价函数中加上排序的代价，对于包含B个块的文件排序的代价大约为：
   $$(2\ast B)+(2\ast B\ast lo{g}_{2}B)$$

9.5查询计划的执行

查询优化完成后，关系数据库管理系统为用户查询生成一个查询计划，该查询计划可分为自顶向下和自底向上。

1. 自顶向下
   系统反复向查询计划顶端的操作符发出需要查询结果元组的请求，操作符收到请求后，试图计算下一个（几个）元组并返回这些元组。
2. 自底向上
   查询计划从叶节点开始执行，叶节点操作符不断产生元组并放入缓冲区，直到缓冲区满，这时必须等待父操作符将元组从缓冲区取走才能继续执行。

9.6小结

查询处理是关系数据库管理系统的核心，查询优化技术是查询处理的关键技术。
本章内容：

1. 查询处理过程。
2. 查询优化：代数优化和物理优化。
3. 查询执行。
   本章的目的是让大家掌握查询优化方法的概念和技术，而不是查询处理和优化的内部实现技术。