2008查询优化技术

查询优化技术一． 关系系统的查询优化理论与技术关系模型是建立在数学基础上的。

关系数学不仅是关系操作和关系规范化的理论基础，同时也是关系查询优化技术的基础。

1. 一个实例首先通过一个简单的例子来看一下查询优化的必然性。

设学生选课库的关系模式为：学生（学号，姓名，年龄，所在系）； 选课（学号，课程号，成绩）求选修了C1号课程的学生姓名，用SQL 语言表达如下： SELECT 学生，姓名 FROM 学生，选课WHERE 学生.学号=选课.学号AND 选课 . 课程号=’C1’；假定学生选课库中有1000个学生记录，10000个选课记录，其中选修C1号课程的选课记录有50个。

这个查询系统可以用多种等价的关系代数表达式来完成。

这里列出其中3种：选课））σ（学生Q3选课））学生（σQ2选课））学生（σQ1c1''课程号选课姓名c1''课程号选课姓名1'c '课程号选课学号选课学号学生姓名(∏=(∏=×(∏=ٛ=.ٛ=.=.∧.=.><><下面分析这3种查询的策略，看看策略的不同会对查询的时间效率产生怎样的影响。

（1）第1种情况1）计算广义笛卡儿积。

把学生和选课的每个元组连接起来。

连接的做法一般是这样的：在内存中尽可能多地装入某个表（如学生表）的若干元组，留出一块存放另一个表（如选课表）的元组。

然后把选课中的每个元组和学生中的每个元组连接，连接后的元组装满一块后就写到中间文件上，再从选课中读入一块和内存中的学生元组连接，直到选课表处理完。

这时再一次读入若干块学生元组，读入一块选课元组，重复上述处理过程，直到把学生表处理完。

设一个块能装10个学生元组或100个选课元组，在内存中存放5块学生元组和一块选课元组，则读取总块数为：其中读学生表100块。

每次读5块，共需读20次，也即需读选课表20遍，每遍100块。

若读写20块/s, 则总计要花105s 。

连接后的元组元组数为103*104=107。

设每块能装10个元组，则写出这些块要用5*104s 。

2）作选择操作。

依次读入连接后的元组，按照选择条件选取满足要求的记录。

假定内存处理时间忽略。

这一步读取中间文件花费的时间（同写中间文件一样）需5*104s 。

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

3）作投影操作。

把第2步的结果在姓名上作投影输出，得到最终结果。

因此第1种情况下执行查询的总时间≈105＋2×5×104s ≈105s 。

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

（2）第2种情况选课））学生（课程号选课姓名><(∏=ٛ=.c1''σQ21）计算自然连接花费的时间。

执行自然连接仍需读取学生和选课表，因此读取块数仍为2100块，花费时间为105s 。

但自然连接的结果比广义笛卡儿积减少很多，为104个，所以写出这些元组的时间为50s ，仅为第一种情况的千分之一。

2） 作选择运算花费时间为50s 。

3） 第2步结果投影输出。

第2种情况总的执行时间≈105+50+50≈205s ，比第一种情况大大减少。

（3）第3种情况选课））（学生课程号选课姓名(∏=ٛ=.c1''σQ3><1）先对选课表作选择运算。

只需读一遍选课表，存取100块花费时间为5s ，因此满足条件的元组仅50个，不必使用中间文件。

2）读取学生表。

把读入的学生元组和内存中的选课表作连接。

同样只需读一遍学生表共100块花费时间为5s 。

3）把连接结果投影输出。

第3种情况总的执行时间≈5+5≈10s ，比第2种情况又减少了很多。

如果在选课表的课程号上建有索引，则第1步就不必读取所有的选课元组而只需读取课程号=‘C1’的那些元组（50个）。

存取的索引块和选课表中满足条件的数据块大约总共3～4块。

如果学生表在学号上也建有索引，则第2步也不必读取所有的学生元组，因为满足条件的选课记录只有50个，最多涉及50个学生记录，因此读取学生表的块数也可大大减少。

总的存取时间将进一步减少到数秒。

2．查询优化的一般准则下面介绍的优化策略能提高查询的效率。

但它们不一定是最优的策略。

实际上“优化”一词并不是很确切，用“改进”获“改善”或许更恰当些。

（1）选择运算尽可能先做在优化策略中这是最重要的、最基本的一条。

因为选择运算一般使计算结果大大变小，常常使执行时间降低几个数量级。

（2）在执行连接前对关系适当地预处理预处理方法主要有两种，在连接属性上建立索引和对关系进行排序，然后执行连接。

前者称为索引连接方法；后者称为排序连接方法。

假如要对学生表和选课表进行自然连接，用索引连接方法的步骤是：1）对选课表的学号建立索引；2）对学生表中每一个元组，由学号值通过选课表的索引查找相应的选课元组；3）把这些选课元组和学生元组连接起来；这样学生表和选课表均只需扫描一遍。

处理时间是两个关系大小的线性函数。

用排序合并连接方法的步骤是：1）首先对学生表和选课表按连接学号排序。

2）取学生表中的第一个学号，依次扫描选课表中具有相同学号的元组，把它们连接起来。

3）当扫描到学号不相同的第一个选课元组时，返回学生表扫描下一个元组，再扫描选课表中具有相同学号的元组，把它们连接起来。

重复上述步骤直到学生表扫描完。

这样学生表和选课表也只要扫描一遍。

当然，执行时间要加上对两个表的排序时间，即使这样，使用预处理方法执行连接的时间一般仍大大减少。

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

（4）把投影同其前或其后的双目运算结合起来没有必要为了某些字段而重复扫描一遍关系。

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

（6） 找出公共子表达式如果这种重复出现的子表达式的结果不是很大的关系，并且从外存中读入这个关系比计算该子表达式的时间少得多，则先计算一次公共子表达式并把结果写入中间文件是合算的，当查询的是视图时，定义视图的表达式就是公共子表达式的情况。

3．关系代数等价变换规则前面介绍的优化策略大都涉及到了代数表达式的等价变换。

两个关系表达式E 1和E 2是等价的，可记作E 1≡E 2。

常用的等价变换规则有：（1） 连接、笛卡尔积交换律设E 1和E 2是关系代数表达式，F 是连接运算的条件，则有：122112211221E E E E E E E E E E E E FF><><><><≡≡×≡× （2） 连接、笛卡尔积交换律，F 1和F 2是连接运算的条件，则有：3213213213213213212121E E E E E E E E E E E EF F F F ><><><><><><><><≡≡设E 1，E 2和E 3是关系代数表达式)()()()()()(E E E E E E ××≡×× （3） 投影的串接定律212E A A A E B B n m L L )(,,,))(,,,(,,,121B A A A n L ∏≡∏∏其中，E是关系代数表达式，A i （i=1,2,…,n）,B j (j=1,2, …,n)是属性（42121E F F F F ∧名{A 1，A 2，…A N }构成{B 1，B 2，…B N }的子集。

） 选择的串接定律)())((E ≡σσσ其中，E是关系代数表达式，F 1和F 2是连接运算的条件， （5,E F An σL这里选择条件F 只涉及属性A 1，A 2，…A N 。

若F中有不属于A 1，A 2，…A N 的,,2,1,,,E Bm B B An L L （6） 选择与笛卡尔积的交换律） 选择与投影的交换律))(())((2,1,2,1E A A An A A F σL ∏≡∏属性B 1，B 2，…B m 则有更一般的规则：(())((2,1,2,1,2,1E A A F An A A F An A A L L ∏∏≡∏σσ)))(如果F 中的涉及的属性都是E 1中的属性，则：×≡×2121F F )()(E E E E σσ如果1∧F 2，并且F 1只涉及E 1中的属性，F 2只涉及E 2中的属性，则可推出：F F F=F)()()(1E E E E F 2211σσσ×≡×若F 1只涉及E 1中属性，F 2涉及E 和E 2两者的属性，则仍有：E E E E 1)211221F F F )(()(×≡×σσσ （7） 选择与并的交换有相同的属性名，则：E E E E 设E=E 1∪E 2，E 1，E 2)()()(2121F F F σσσ∪≡∪ （8） 选择与差运算的交换，则：E E E E 若E 1与E 2有相同的属性名)()()(2121F F F σσσ−≡− （9） 投影与笛卡尔积的交换，A 1，A 2，，A n 是E 1的属性，B 1，B 2，…，B EBm B B An A A L L （10） 投影与并的交换性名，则：E E E E An A A L ∏∪∏≡∪∏ 4．关系代数表达式的优化算法法：1） 利用等价变换规则（4）把设E 1和E 2是关系代数表达式m 是2的属性，则：)()()(,2,1E E E E Bm B B An A A L L ∏×∏≡×∏ 2,,2,11,,2,121,,2,1,,若E1和E2有相同的属)()()(2,2,11,2,121,2,1An A A An A A L L 下面给出关系表达式的优化算)(21E Fn F F L ∧∧σ代数式变换为))))((((21Fn F F L L E σσσ。

（目的是使选择操作可以灵活地沿查询树下移）2） 对每一个选择，利用等价变换规则（4）～（8）尽可能地移向树的叶端。

（目的是使选择操作尽早执行））对每一个投影利用等价变换规则（3）（9）、（10）和（5）中的一般形式，尽可能地移向树的叶端。

（目的是使投影操作尽早执行）值得注意的是，法则（3）会使一些投影消失，（5）会把一个投影分裂为两个，其中一个有可能被移向树的叶端。

4） 利用规则（3）～（5）单个投影、或一个选择后跟一个投影，（目的是使多个选择和投影能同3 、而一般规则把选择和投影的串接合并成单个选择、时执行或在一次扫描中全部完成）。