数据库第14章 查询处理与优化Word文档格式.docx

数据库第14章 查询处理与优化Word文档格式.docx

《数据库第14章 查询处理与优化Word文档格式.docx》由会员分享，可在线阅读，更多相关《数据库第14章 查询处理与优化Word文档格式.docx（19页珍藏版）》请在冰豆网上搜索。

假设要查询选修了“C001”课程的学生姓名。

相应的SQL语句为：

SELECTSname

FROMStudentSJOINSCONS.Sno=SC.Sno

WHERECno='

C001'

假设数据库中有1000个学生记录，10000个选课记录，其中选了“C001”课程的记录有50个。

与该查询等价的关系代数表达式可以有多种形式：

Q1=∏Sname（σStudent.Sno=SC.Sno（Student×

SC））

Q2=∏Sname（σSC.Cno=‘C001’（Student

Q3=∏Sname（Student

σSC.Cno=‘C001’（SC））

还有其他几种形式，但这三种形式是典型的与该查询语句等价的代数表达式，分析这三种形式的表达式就足够说明问题了。

下面我们分析这三种查询执行策略在查询时间上的差异。

1．Q1的执行过程

（1）进行广义笛卡尔积操作

把Student表的每个元组和SC表的每个元组连接起来。

一般的连接做法是：

在内存中尽可能多地装入某个表（比如Student表）的若干块，并留出一块存放另一个表（比如SC表）的元组。

把SC表中的每个元组与Student表中的每个元组进行连接，连接后的元组装满一块后就写到中间文件上，再从SC表中读入一块数据，然后再和内存中的Student元组进行连接，直到SC表处理完。

然后再一次读入若干块Student元组，再读入一块SC元组，重复上述处理过程，直到处理完Student表的所有元组。

假设一个块能装10个Student表的元组或100个SC表的元组，在内存中最多可存放5块Student表数据和1块SC表数据，则读取的总块数为：

1000/10+1000/（10×

5）×

10000/100=100+20×

100=2100（块）

其中，读取Student表100块，读取SC表20遍，每遍10000/100=100块。

设每秒能读写20块，则该过程总共要花费2100/20=105s。

Student和SC表连接后的元组数为1000×

10000=107。

设每块能装10个连接后的元组，则写出这些连接后的元组需要（107/10）/20=5×

104s。

（2）进行选择操作

依次读入连接后的元组，选取满足选择条件的元组。

假定忽略内存处理时间，则这一步读取存放连接结果的中间文件需花费的时间同写中间文件一样，也是5×

假设满足条件的元组只有50个，均可放在内存中。

（3）进行投影操作

对第2步得到的结果再在Sname列上进行投影，得到最终结果。

这个步骤由于不需要读写磁盘，因此，时间忽略不计。

则Q1的总执行时间约为：

105+2×

5×

104≈105（s）。

这里所有的内存处理时间均忽略不计。

2．Q2的执行过程

（1）进行自然连接操作

进行自然连接同进行笛卡尔积一样，同样需要读取Student表和SC表的所有元组，假设这里的读取策略同Q1，则Q2总的读取块数仍为2100块，需要105s。

但自然连接的结果比Q1大大减少，为10000=104个（即SC表元组数）。

因此，写出这些元组需要的时间为：

（104/10）/20=50（s）。

仅为Q1执行时间的千分之一。

读取中间文件块，这同写元组一样，也是50s。

对第2步的结果在Sname列上进行投影，花费时间忽略不计。

则Q2的总执行时间约为：

105+50+50=205（s）。

3．Q3的执行过程

（1）对SC表进行选择运算

这只需读一遍SC表，共计100块数据，所花费时间为100/20=5（s）。

由于满足条件的元组仅有50个，因此不必使用中间文件。

（2）进行自然连接操作

读取Stduent表，把读入的Student元组和内存中的SC的元组进行连接操作，只需读取一遍Student表共计100块，花费时间为100/20=5（s）。

（3）对连接的结果进行投影操作

将第2步的结果在Sname列上进行投影，花费时间忽略不计。

则Q3的总执行时间约为：

5+5=10（s）。

对于Q3的执行过程，如果SC表的Cno列上建有索引，则第一步就不需要读取SC表的所有元组，而只需读取Cno=‘C001’的50个元组。

若Student表在Sno列上也建有索引，则第二步也不必读取Student表的所有元组，因为满足条件的SC表记录仅50个，因此，最多涉及50个Student记录，这也可以极大地减少读取Student表的块数。

从而减少总体的读取时间。

从这个简单的例子可以看出查询优化的必要性，同时该例子也给出了一些查询优化的初步概念。

把关系代数表达式Q1变换为Q2和Q3，即先进行选择操作，后进行连接操作，这样就可以极大地减少参加连接的元组数，这就是代数优化的含义。

对于Q3的执行过程，对SC表的选择操作有全表扫描和索引扫描两种方法，经过初步估算，索引扫描方法更优。

同样对于Student表和SC表的连接操作，如果能利用Student表上的索引，则会提高连接操作的效率，这就是物理优化的含义。

14.3代数优化

代数优化是对查询进行等价变换，以减少执行的开销。

所谓等价是指变换后的关系代数表达式与变换前的关系代数表达式所得到的结果是相同的。

14.3.1转换规则

查询优化器使用的转换规则就是将一个关系代数表达式转换为另一个等价的能更有效执行的表达式。

最常用的变换原则是尽可能减少查询过程中产生的中间结果。

由于选择、投影等一元操作分别从水平和垂直方向减少关系的大小，而连接等二元操作不但操作本身开销很大，而且还会产生大的中间结果，因此，在变换时，总是尽可能先做选择和投影操作，然后再做连接操作。

在连接时，也是先做小关系之间的连接，再做大关系的连接。

两个关系代数表达式E1和E2是等价的，记为：

E1≡E2。

假设有关系R、S和T，R的属性集为A={A1,A2,…,An}，S的属性集为B={B1,B2,…,Bn}，c={c1,c2,…,cn}代表选择条件，L、L1和L2代表属性集合。

下面是一些常用的等价转换规则。

1．多重选择（σ）

设R是某个关系，则有：

σC1ΛC2Λ…ΛCn（R）≡σC1（σC2（…（σCn（R））…））

示例：

σSdept=‘计算机系’ΛSsex=‘男’（Student）≡σSdept=‘计算机系’（σSsex=‘男’（Student））

2．选择（σ）的交换律

σC1（σC2（R））≡σC2（σC1（R））

σSdept=‘计算机系’（σSsex=‘男’（Student））≡σSsex=‘男’（σSdept=‘计算机系’（Student））

3．多重投影（∏）

∏A1（∏A1，A2（…∏A1，A2,…,An（R）））≡∏A1（R）

∏sname（∏Sdept,Sname（Student））≡∏Sname（Student）

4．选择（σ）与投影（∏）的交换律

σc（∏A1,A1,…,An（R））≡∏A1,A1,…,An（σc（R））

σSage>

=20（∏sname,sdept,sage（Student））≡∏sname,sdept,sage（σSage>

=20（Student））

5．连接（

）和笛卡尔积（×

）的交换律

R×

S≡S×

R

6．并（∪）和交（∩）运算的交换律

R∪S≡S∪R

R∩S≡S∩R

7．选择（σ）和连接（

σc（R

S）≡（σc（R））

S，假设c只涉及R中的属性。

同样，如果选择条件是（c1∧c2）这种形式的，并且c1只涉及R中的属性，c2只涉及S中的属性，则选择和连接操作可变换成如下形式：

σc1∧c2（R

S）≡σc1（R）

σc2（S）

σSdept=’计算机系’ΛGrade>

=90（Student

SC）≡

（σSdept=’计算机系’（Student））

（σGrade>

=90（SC））

8．投影（∏）和连接（

）的分配律

设R和S的连接属性在L1和L2中，则

∏L1∪L2（R

S）≡∏L1（R）

∏L2（S）

∏Sdept,Sno,Sname,Grade（Student

SC）≡

（∏Sdept,Sno,Sname（Student）

（∏Sno,Grade（SC））

如果R和S的连接属性不在L1和L2中，则在进行∏L1（R）和∏L2（S）操作时，必须保留连接属性。

∏Sdept,Sname,Grade（Student

SC）≡

（∏Sno,Sdept,Sname（Student）

9．选择（σ）与集合并、交、差运算的分配律

设R和S有相同的属性，则：

σc（R∪S）≡σc（R）∪σc（S）

σc（R∩S）≡σc（R）∩σc（S）

σc（R-S）≡σc（R）-σc（S）

10．投影（∏）与并运算的分配律

∏L（R∪S）≡∏L（R）∪∏L（S）

11．连接（

）的结合律

（R×

S）×

T≡R×

（S×

T）

（R

S）

T≡R

（S

T）

如果连接条件c仅涉及来自关系R和T的属性，则连接以下列方式结合：

12．并（∪）和交（∩）的结合律

（R∪S）∪T≡R∪（S∪T）

（R∩S）∩T≡R∩（S∩T）

14.3.2启发式规则

启发式规则（heuristicrules）作为一个优化技术，用于对关系代数表达式的查询树进行优化。

查询树也称为关系代数树，它用形象的树的形式来表达关系代数的执行过程。

查询树包括如下几个部分：

●叶结点：

代表查询的基本输入关系。

●非叶结点：

代表在关系代数表达式中应用操作的中间关系。

●根结点：

代表查询的结果。

查询树的操作顺序为：

从叶到根。

例如，关系代数表达式：

Q2＝∏Sname（σSC.Cno=‘C001’（Student

对应的查询树如图14-2所示。

图14-2与Q2表达式对应的查询树

从14.1.2的例子我们可以看到，一个SQL查询可以有多种不同形式的关系代数表达式，因此也会有多种不同的查询树。

一般情况下，查询解析器首先产生一个与SQL查询对应的初始标准查询树，这个查询树是没有经过任何优化的。

然后运用启发式规则对查询树进行优化。

典型的启发式规则有：

（1）尽可能先做选择运算。

在优化策略中这是最重要、最基本的一条。

它常常可以节省几个数量级的执行时间，因为选择运算一般会极大地减少中间结果。

（2）投影运算和选择运算同时进行。

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

（3）把投影运算和其之前或之后的二元运算结合起来，这样可以减少关系的扫描次数。

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

（5）找出公共子表达式。

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

当是对视图进行查询时，定义视图的语句就是公共子表达式。

下面我们通过一个查询示例说明代数优化的过程。

例：

查询计算机系VB课程考试成绩大于等于90分的学生的姓名和VB成绩。

查询语句为：

SELECTSname,GradeFROMStudentJOINSCONStudent.Sno=SC.Sno

JOINCourseONCourse.Cno=SC.Cno

WHERESdept='

计算机系'

ANDCname='

VB'

ANDGrade>

=90

优化过程：

（1）转换为初始关系代数表达式（未经优化的）：

∏Sname,Grade（σStudent.Sno=SC.SnoΛCo=SC.cnoΛSdept=‘计算机系’ΛCname=‘VB’ΛGrade>

=90

（Student×

SC×

Course））

该查询的初始查询树如图14-3所示。

图14-3初始的关系代数查询树

（2）利用转换规则进行优化

●用规则1将选择操作的连接操作部分分解到各个选择操作中，使尽可能先执行选择操作。

用规则2和6重新排列选择操作，然后交换选择和笛卡尔积，得到的关系代数表达式如下，对应的查询树如图14-4所示。

∏Sname,Grade（（σStudent.Sno=SC.Sno（σSdept=‘计算机系’（Student））×

σGrade>

×

（σCo=SC.cno（σCname=‘VB’（Course））））

●将笛卡尔积操作替换为等值连接操作。

得到的关系代数表达式如下，对应的查询树如图14-5所示。

∏Sname,Grade（σSdept=‘计算机系’（Student）

σGrade>

=90（SC））

σCname=‘VB’（Course）

●由于WHERECname=’VB’返回的结果行数（如果VB只开设一次的话，则返回的行数就是1）远远小于WHERESdept=’计算机系’返回的结果行数（计算机系的学生一定有很多个），因此，先执行对Course表的选择可以减少参与连接的元组数。

用转换规则11重新排列等值连接，先执行WHERECname=’VB’部分，产生的关系代数表达式如下，对应的查询树如图14-6所示。

∏Sname,Grade（（σCname=‘VB’（Course）

σSdept=‘计算机系’（Student））

图14-4先做选择操作得到的改进查询树

图14-5将笛卡尔积改为等值连接得到的改进查询树

图14-6用等值连接的结合律得到的改进查询树

●用规则4和规则7将投影向下移动到等值连接下面以减少连接产生的中间结果所占用的空间，并根据需要创建一个新的投影等式，新的投影等式保留用于进行连接的列以及查询列。

得到的关系代数表达式如下，对应的查询树如图14-7所示。

∏Sname,Grade（∏Cno（σCname=‘VB’（Course））

（∏Sno,Sname（σSdept=‘计算机系’（Student）））

至此该查询语句优化结束。

图14-7下移投影操作得到的改进查询树

14.4物理优化

代数优化不涉及底层的存取路径。

因此，对各种操作的执行策略无从选择，只能在操作次序和组合上根据启发式规则做一些变换和调整。

单纯依靠代数优化是不完善的，优化的效果也是有限的。

实践证明，合理地选择存取路径，往往能收到显著的优化效果，应成为优化的重点。

本节将讨论依赖于存取路径的优化规则，即物理优化。

结合存取路径，讨论各种操作执行的策略以及选择原则。

14.4.1选择操作的实现和优化

选择操作的执行策略与选择条件、可用存取路径以及选取的元组数在整个关系中所占的比例有关。

选择条件有等值、范围和集合操作等。

等值条件即属性等于某个给定值。

范围条件指属性在某个给定范围内，一般由比较运算符（>

、≥、<

、≤或BETWEEN…AND…）构成。

集合条件指用集合关系表示的条件，如用IN、NOTIN、EXISTS、NOTEXISTS表示的条件。

集合条件比较的一方往往是一些常量的集合或者是子查询块。

验证这些条件一般没有专门的存取路径。

复合条件由简单选择条件通过AND、OR连接而成。

选择操作最原始的实现方法是顺序扫描被选择的关系，即按关系存放的自然顺序读取各元组，逐个按选择条件进行检验，选取满足条件的元组。

这种方法不需要特殊的存取路径，如果选择的元组较多或者是关系本身很小，则这种方法不失为是一种有效的方法。

在无其他存取路径时，这也是唯一可行的方法。

对于大的关系，顺序扫描非常费时，为此，DBMS在技术上支持建立各式各样的存取路径，供数据库设计人员根据需要进行配置。

目前用的最多的存取路径是以B＋树或其他变种结构的各种索引。

近年来，也有些DBMS支持动态散列及其各种变种。

散列技术对于散列属性上的等值查询很有效，但对于散列属性上的范围查询、整个关系的顺序访问以及非散列属性上的查询都很慢，加之不能充分利用存取空间，因此，除特殊情况外，一般不用这种技术。

索引是用的最多的一种存取路径。

从数据访问的观点看，索引可分为两大类，一类是无序索引，即非聚集索引；

另一类是有序索引，即聚集索引。

非聚集索引是建立在堆文件上的。

在这种存取结构中，具有相同索引值的元组被分散存放在堆文件中，每读取一个元组，一般都需要访问一个物理块。

如果仅查询一个关系中的少量元组，则这种索引很有效，它比顺序扫描节省大量的I/O操作。

但如果查询一个关系中的较多元组，则可能要访问这个关系的大部分物理块，再加上索引本身的I/O操作，则很可能还不如顺序扫描有效。

聚集索引是排序索引，即关系按某个索引属性排序，具有相同索引属性值的元组聚集（即连续）存放在一起。

如果查询的是聚集索引的属性，则聚集存放在同一个物理块中的元组的索引属性值是依次相邻的。

这种存放方式对按主键进行的范围查询非常有利，因为每访问一个物理块可以获得多个所需的元组，从而大大减少I/O次数。

如果查询语句要求查询结果按主键排序，则还可以省去对结果进行排序的操作。

对数据按索引属性值排序和聚集存放虽然对某些查询有利，但不利于插入新数据，因为每次插入数据时都有可能造成对其他元组的移动，并且有可能需要修改该关系上的所有索引，这项工作非常耗时。

由于一个关系只能有一种物理排序或聚集方式，因此，只对包含这些排序属性的查询比较有利，对其他属性的查询可能不会带来任何好处。

连接操作可按下列启发式规则选用存取路径。

（1）对于小关系，不必考虑其他存取路径，直接用顺序扫描。

（2）如果无索引或散列等存取路径可用，或估计选择的元组数在关系中占有较大的比例（例如大于15％），且有关属性无聚集索引，则用顺序扫描。

（3）对于主键的等值条件查询，最多只有一个元组可以满足条件，因此应优先采用主键上的索引或散列。

（4）对于非主键的等值条件查询，要估计选择的元组数在关系中所占的比例。

如果比例较小（例如小于15％），可用非聚集索引，否则只能用聚集索引或顺序扫描。

（5）对于范围条件查询，一般先通过索引找到范围的边界，再通过索引的有序集沿相应的方向进行搜索。

例如，对于条件Sage>

=20，可先找到Sage=20的有序集的结点，再沿有序集向右搜索。

若选择的元组数在关系中所占的比例较大，且没有有关属性的聚集索引，则宜采用顺序扫描。

（6）对于用AND连接的合取选择条件，若有相应的多属性索引，则应先采用多属性索引。

否则，可检查各个条件中是否有多个可用的二次索引检索的，若有，则用预查找法处理。

即通过二次索引找出满足条件的元组id（用tid表示）集合，然后再求出这些tid集合的交集。

最后取出交集中tid所对应的元组，并在获取这些元组的同时，用合取条件中的其余条件检查。

凡能满足所有其余条件的元组即为所检索的元组。

如果上述途径都不可行，但合取条件中有个别条件具有规则（3）、（4）、（5）所描述的存取路径，则可用此存取路径来选择满足条件的元组，再将这项元组用合取条件中的其他条件筛选。

若在所有合取条件中，没有一个具有合适的存取路径，则只能用顺序扫描。

（7）对于用OR连接的析取选择条件，还没有好的优化方法，只能按其中各个条件分别选出一个元组集，然后再计算这些元组的并集。

我们知道，并操作是开销大的操作，而且在OR连接的诸条件中，只要有一个条件无合适的存取路径，就必须采用顺序扫描来处理查询。

因此，在编写查询语句时，应尽可能避免采用OR运算符。

（8）有些选择操作只要访问索引就可以获得结果。

例如查询索引属性的最大值、最小值、平均值等。

在这种情况行啊，应优先利用索引，避免访问数据。

14.4.2连接操作的实现和优化

连接操作是开销很大的操作，一直以来是查询优化研究的重点。

本节主要讨论二元连接的优化，这也是最基本、使用的最多的连接操作。

多元操作也是以二元为基础的。

实现连接操作一般有嵌套循环、利用索引和散列匹配元组、排序归并以及散列连接四种方法，下面分别介绍这四种方法。

1．嵌套循环（nestedloop）法

设有关系R和S进行如下连接操作：

最基本的方法是读取R的一个元组，然后与S的所有元