第十二章查询优化技术关系数据库系统的查询自下而上分为三个层次：第一层是关系代数的操作；第二层是由关系代数的操作组成的查询计划；第三层是用户查询的高级描述。

L 第十二章查询优化技术关系数据库系统的查询自下而上分为三个层次：第一层是关系代数的操作；第二层是由关系代数的操作组成的查询计划；第三层是用户查询的高级描述。第十一章讨论了第一层的各种实现算法。本章主要讨论第二层的优化处理技术。本章最后一节简单介绍了第三层的一些优化方法。第一节问题的提出第二节启发式关系代数优化方法第三节启发式关系演算优化方法第四节基于复杂性估计的查询优化方法第五节语义查询优化方法

L1 第一节问题的提出多个关系代数构成的查询可以有多种操作策略，不同的操作策略一般有不同的磁盘存取复杂性，因此选择复杂性较低的一种策略是提高访问数据库效率的重要的优化技术。下例说明不同的操作策略有不同的存取复杂性。 [例]设关系S有1000个学生的记录，其属性是姓名sn和学号s#。关系SC有10000个选课记录，其属性是学号s#和课程号c#。现要查询选修C2课程的学生姓名。策略Q1=SN((S.S#=SC.S#)(SC.C#=‘C2’)(SSC)) 策略Q2=SN(S.C#=‘C2’(SS.S#=SC.S#SC)) 策略Q3=SN((S.C#=‘C2’SC)S.S#=SC.S#S) 在给定某个执行环境下，三个策略Q1、Q2、Q3的时间开销分别是200105、125、10秒。 select distinct s.sn from s,sc where s.s#=sc.s# and sc.c#=‘c2’ 这个例子不但说明了查询优化的必要性，而且给我们启示：先选择再连接，可减少中间运算，提高处理速度。

L2 第二节启发式关系代数优化方法关系代数表达式的优化是查询优化中的一个至关重要的问题。许多关系型DBMS处理查询的第一步是把查询的高级描述(例如SQL)语句转换为对应的内部表示，即关系代数表达式，或其图表示：查询树。第二步是运用关系代数等价变换规则和关系代数优化规则(策略)把内部表示转换为复杂性较低的另一等价形式。本节介绍基于关系代数等价变换规则的启发式查询优化方法。一关系代数等价变换规则二启发式代数优化规则三启发式代数优化算法

L21 一关系代数等价变换规则查询优化是在结果相同的操作方案中选择的，这引出等价关系代数表达式的概念：若两个关系代数表达式A和B表示相同的关系，则称之为互相等价，记作AB。常用的关系代数等价变换规律包括12类。下边仅列出前10类。 1 选择串接率: c1 and …and cn(A)c1(c2(…(cn(A))…)) 其中A是关系代数表达式,ci是选择条件。即：这种复合条件的选择可以转换为逐次选择操作。 2 选择交换率: c(d(A))d(c(A))，c和d是选择条件。即：逐次选择操作与选择顺序无关。 3 投映串接率:L1(L2(…(Ln(A))…))L1(A)，其中， L1L2…Ln .即：逐次投影操作可一次投影到投影域交集。 4 选择投映交换率：关于选择与投影连续操作的次序交换问题, 若条件c仅包括投映域L中的属性，则L(c(A))c(L(A)) 若条件c包括投映域L外的属性x,则L(c(A))L(c(L,x(A))) 5 连接和笛卡儿乘积的交换率：ABBA，AcBBcA 6 集合操作的交换率：ABBA，ABBA 接下页

7.连接、笛卡儿乘积和集合操作的结合率 (AB)CA(BC) (AcB)dCAc(BdC) (AB)CA(BC) (AB)CA(BC) 其中A、B和C是关系代数表达式，小写字母c和d表示连接条件。 8.选择、连接和笛卡儿乘积的分配率设A和B是关系代数表达式，d是连接条件。 c(AdB)(cA)dB (选择条件c仅涉及A的属性) c(AdB)(c1A)d(c2B) (三个选择条件满足c=c1c2，c1仅涉及A属性， c2仅涉及B属性) 用代替上边两式的d，式子同样成立。 9.投影、连接和笛卡儿乘积的分配率设A和B是关系代数表达式，d是连接条件。 L(AdB)(L1A)d(L2B) (L=L1L2，L1仅涉及A的属性， L2仅涉及B的属性) 用代替上式的d，式子同样成立。 10.选择与集合操作的分配率 c(AB)c(A)c(B)，c(AB)c(A)c(B), c(A-B)c(A)-c(B) L211

L22 二启发式代数优化规则(5条) 本节第一段列出的关系代数等价变换规则仅保证表达式的等价变形，变换结果的磁盘存取复杂性可能降低了，也可能增加了。下边列出等价变形的五条启发式代数优化规则(策略)。其目的是为了使等价变换的结果有较低的磁盘存取复杂性。 [规则1] 选择和投映尽早执行， (在笛卡儿乘积或连接操作之前尽量排除无关数据)； [规则2] 把某些选择操作与邻接的笛卡儿乘积结合为连接操作， (节省了两次操作之间的磁盘存取，特别是笛卡儿乘积大量的中间数据)； [规则3] 同时执行对同相同关系的多个选择和投映操作， (可以避免对相同关系的重复扫描)； [规则4] 把投映操作与连接操作结合起来执行， (节省了单独投影操作所需的关系扫描)； [规则5] 提取公共表达式，储存中间结果，减少重复计算。 (适合于结果数据量少、但计算量大的公共表达式，对此公共表达式的调用越频繁，效益就越大)。

三启发式代数优化算法 该算法运用关系代数的等价变换规则和启发式优化规则实现查询优化。算法的两个步骤是：步骤1：把查询的高级表示(例如SQL)转换为查询的内部表示，这个转换也分两步走： 查询高级表示关系代数表达式； 关系代数表达式查询树。查询树是查询的一种内部表示形式。在查询树中，内点表示关系代数操作，叶点表示关系。执行方向自底向上。例如，SQL语句 select A from R,S,T where P=15 and N=‘user’; 转换为关系代数示式A(P=15and N=‘user’(RST))，再表示为左图所示的查询树。步骤2：运用关系代数等价变换规则和关系代数优化规则，对查询树进行变形，产生一个优化的查询方案。查询树优化变形的实质，是对构成查询的各个关系代数操作进行优化重组。 (1) 启发式代数优化算法的非形式描述 (2) 启发式代数优化算法的例子 L23 查询树例 A P=15and N=‘user’   T R S 接下页

L23a 启发式代数优化算法的非形式描述输入：关系代数表达式输出：计算输入关系代数表达式的程序方法：顺序执行下列步骤(按优化规则对查询树作等价变形) 1 使用规律1把选择操作c1 and …and cn(E)改为c1(c2(…(cn(E))…))， (目的是增加灵活性)； 2 使用规律2、4、8、10，把选择操作沿树下移，靠近叶结点， (目的是尽早选择)； 3 使用规律3、4、9、11，把投映操作沿树下移，靠近叶结点， (目的是尽早投映)； 4 使用规律1、3、4，把多个邻接的投映选择合并为单个操作， (目的是减少访问磁盘次数)； 5 优先执行具有最小选择操作(具最小选择结果关系)的叶结点， (目的是减少中间结果)； 6 将紧邻的笛卡儿乘积和选择操作并为连接操作， (目的是避免出现笛卡儿乘积结果)； 7 划分子树，使每个子树的操作由单个存取程序一次完成； 8 产生一个计算最后查询树的程序，每步计算一个子树。

L23b 启发式代数优化算法的例子图书馆数据库例图书BOO(TI，AU，PN，NC) (属性的含义是：书名，作者，出版社，书号) 出版社PUB(PN，PA，PC) (属性的含义是：出版社，地址，城市) 读者BOR(NA，AD，CI，CN) (姓名，地址，城市，借书证号) 借阅LOA(CN，NC，DA) (属性的含义是：借书证号，书号，日期) 查询：显示94年2月1日前借出的书的书名。 SQL表示式： select TI from BOO，BOR，LOA where BOO.NC=LOA.NC and BOR.CN=LOA.CN and DA<2/1/1994；关系代数示式：T1(C1(L(C2((loabor)boo)))) 接下页

L3 第三节启发式关系演算优化方法本节介绍QUEL查询语言使用的启发式优化方法。这种方法使用超图表示QUEL查询，通过对超图逐次消解消边产生查询算法。这种方法还提供一种选择消边的策略和投影规则，按照这些策略和规则处理消图，所产生的查询算法就能缩小操作的关系和减少查询的中间结果，从而达到提高查询效率的目的。一多重自然连接的优化处理二查询的超图HG表示三超图的消解算法

L31 一多重自然连接的优化处理问题：设关系R含属性A和B，关系S含属性B和C，关系T含属性C和D。计算自然连接RST. 因自然连接满足结合率和交换率，故RST可按任何顺序执行，但不同的执行顺序有不同的效率，故必须合理选择。例如，因R 和T无共同属性，若先连接R和T，实际是求笛卡儿乘积，将产生大量中间数据，故是最差的执行方案。以下算法是较好的策略： R S T A B C D 这个策略把一个复杂的三重连接分解成两个简单的二重连接操作，有较高的执行效率。注意R和T在连接前先作选择即作半连接，目的是排除无关元组。 result=； for S每个元组(b,c) do 求(B=bR)(b,c)(C=cT)；把结果写入result； endfor； for R每个元组r do for i=1 to n do Ti=Si {r} endfor; 求{r}T1T2Tn；写结果入result； endfor；上述策略可推广到一般形式的多重自然连接Q=RS1…Sn, 设在属性结构上,{Si}两两不交, 但任Si 与R交集非空，算法为:

L32 二查询的超图HG表示假设查询Q的关系代数表达式是：C1C2…Cn(R1R2…Rk) 其中Ci是不含操作的任意条件，在超图表示中Ci分为三类： (1)A=B； (2)A=a或a=A； (3)其它其中大写字母表示属性名，小写字母表示常量。超图由结点和超边组成，表示为HG=(V，HE)，其中，V是结点集合，每个结点表示某关系的一个属性； HE是超边集合，每个超边表示包含一组结点的集合。超边分为两种类型：关系超边：由某关系的属性结点组成,用包围结点的实线框表示. 条件超边：由第三类Ci属性结点组成,用包围结点的虚线框表示. 对查询Q按下述方法建立超图(这样的超图称为连接超图)： 1)按每个关系的属性结构画出对应的关系超边，不同关系的属性即使同名也视为不同的结点，若有第一类条件规定了属于不同关系的两个属性的等值约束，则这两个属性无论同名与否，都视为相同结点； 2)按每个第三类条件Ci画出对应的条件超边。接下页

L321 [例] 画出查询C1C2C3C4(R1R2R3)的连接超图，其中三个关系的属性结构是：R1(A,B)，R2(C,D,E)，R3(F,G), 四个条件分别是：C1：A=C C2：(B<C)(B<D) C3：G<E C4：(F=f)(B=b) 小写字母f和b表示常量。等值连接条件:A=C 关系超边 R1(A,B) 条件超边C2： (B<C)(B<D) B A 关系超边 R2(C,D,E) D 关系超边 R3(F,G) 条件超边 C3：G<E F G E 除了条件C4，查询的全部参数均已反映在连接超图中， C4属于第二类条件，其作用是在算法中形成初值。 C1属于第一类条件，其作用是在R1和R2间建立连接。

L33 • 超图的消解算法 • 查询的超图消解算法原理是： • 首先给出最简单超图的查询结果；然后对一般超图，通过消除 • 一条超边得出较简单的超图以及一些语句，这些语句描述了新 • 旧两超图查询结果的联系。只要新超图得到查询结果，按照这 • 个联系，就可以得到旧超图的查询结果。故逐次消边，可把复 • 杂超图归结为最简单的超图。从而得出复杂查询的结果。如何 • 消解超图并得到一些语句，这由超图消解的四条规则决定。 • 此外，应用启发式的优化规则可以提高算法的效率。 • 1. 消解过程中的变量 • 2. 四条消解规则 • 3. 选择超边的启发式规则 • 4. 超图消解算法

超图消解过程中的变量 设HG(V,E)是查询Q的连接超图. 若E是关系超边，则 R(E)表示模式E在消解过程中的动态实例，是变量。注意加载在E的数据在消解过程中是变化的； R0(E)表示模式E对应的原始关系，是常量；变量R(E)的初值是C(R0(E))，其中C是查询Q形如A=a的选择条件(合取)。若E是条件超边，则C(E)表示E的选择条件，是常量。 result(HG)是消解过程中超图HG对应的关系。 (L321)[查询例]的变量如下： L33A 超边 (关系实例)原始关系初始值表示条件 {FG} R({FG}) R0({FG})=R3 F=f(R3) {ADE} R({ADE}) R0(ADE})=R2 R2 {AB} R({AB}) R0(AB})=R1 B=bR1 C(GE) G<E C(ABD)=C(BCD) B<CB<D

四条消解规则(用于对消边变形构造计算该图结果的程序 ) 1 若HG只含一条关系超边E，则prog(HG)仅一句：result(HG)=R(E): 2 若HG是k个不相交超图HG1、…、HGk的并，则prog(HG)有k+1句: prog(HG1)； … … … … ； prog(HGk)； result(HG)=result(HG1)…result(HGk). 3 若HG消除条件超边E，得超图HG1，则progr(HG)有两句： prog(HG1); result(HG)=C(E)(result(HG1)). 4 若HG消除关系超边E，得k个不连通超图GH1、…、HGk，则prog(HG)有k+2句： for 每个与E相交的关系超边F do R(F)=R(F)R(E); prog(HG1); …; prog(HGk); result(HG) = R(E)  result(HG1)  …  result(HGk). 注意半连接目的是让关系F删除不能与关系E连接的元组，为后边的连接做准备。 L33B 接下页

HG HG1 L33b1 B A D B A D R1 R1 HG消去条件超边GE得HG1 根据规则3，prog(HG)是 prog(hg1)； resu(hg)=G<E(resu(hg1)). E F F G G E R2 R3 R3 R2 HG2 HG3 HG1由离散子图HG2和HG3组成。HG2是最简单的超图；HG3消去条件超边ABD得 HG4。根据规则2和规则1，prog(HG1)是 prog(hg2);resu(hg2)=R({FG})； prog(hg3);prog(hg4);resu(hg3)=B<CB<D(resu(hg4)) resu(hg1)=resu(hg2)resu(hg3)； B A D E R1 HG4 R2 HG4消去关系超边AB得HG5, 根据规则4，prog(HG4)是 R({ADE})=R({ADE})R({AB})； prog(hg5):resu(hg5)=R({ADE})； resu(hg4)=R({AB})resu(hg5). HG5 (L321) [查询例] 的超图消解算法所得程序见下页: A D E R2

L33b2 (L321)[查询例]的超图消解算法所得程序初值： R({FG})=F=f(R3)； R({AB})= B=b(R1)； R({ADE})=R2； RESULT(HG2)=R({FG})； R({ADE})=R({ADE})R({AB})； RESULT(HG5)=R({ADE})； RESULT(HG4)=R({AB})  RESULT (HG5)； RESULT(HG3)=B<CB<D (RESULT(HG4))； RESULT(HG1)=RESULT(HG2)  RESULT(HG3)； RESULT(HG)=G<E ( RESULT(HG1) ). 该算法并未对连接超图消边的顺序作优化，下边介绍优化消边过程的两个启发性规则。接下页

L33C 选择消解超边的启发式规则一般的超图消解算法只能给出查询的一个实现。需要进行优化。下边介绍两个优化规则： (1)小关系规则：按此规则选择关系超边进行消解，可在关系操作前尽量使用选择和半连接缩小操作关系。使得与该超边有关的操作有较少的时延； (2)投映规则：尽早消除不需要的属性，减少查询处理的中间结果。

[小关系定义]设R是一个关系，A是一个属性，a是常量。[小关系定义]设R是一个关系，A是一个属性，a是常量。满足下列条件之一的关系R称为小关系： R=A=a(S)，其中S是某个关系； R=RS，其中S是某关系。 [小关系规则]在超图消解过程中优先选择满足下列条件之一的消边E进行处理： 1)E是小关系超边； 2)消E后，出现小关系超边。 L33c1 HG 例：图书馆查询 range of B is boo range of L is loa range of R is bor retrieve (R.na,R.ad) where R.cn=L.cn and L.nc=B.nc and B.ti=‘toef; 图书boo(ti,au,nc) 书名作者书号读者bor(na,ad,cn) 姓名地址证号借阅loa(cn,nc,da) 证号书号日期查询：借阅‘Toef’的读者的姓名和地址 cn da nc R L B 消解B(满足条件) HG1 cn da R L 消解L(满足条件) HG2 cn R 接下页

L33c11 例：图书馆查询 range of B is boo range of L is loa range of R is bor retrieve (R.na,R.ad) where R.cn=L.cn and L.nc=B.nc and B.ti=‘toef; 图书boo(ti,au,nc) 书名作者书号读者bor(na,ad,cn) 姓名地址证号借阅loa(cn,nc,da) 证号书号日期查询：借阅‘Toef’的读者的姓名和地址小关系判别条件: R=A=a(S)，其中S是某关系 R=RS，其中S是某关系规则4：R(L)=R(L)  R(B); prog(hg1); res(hg)=R(B)  res(hg1); HG cn da nc R L B 消解B(满足条件) HG1 规则4：R(R)=R(R)R(L); prog(hg2); res(hg1)=R(L)res(hg2); cn da R L 消解L(满足条件) HG2 cn 规则1：res(hg2)=R(R); R

超图消解的投映规则 在超图消解过程中运用投影规则，能尽早消除查询不需要的属性。中间结果的减少，提高了查询的效率。这个规则的关键是识别哪些属性是后边操作需要的，哪些是不需要的。为此引入下边的概念：重要结点；若在消解过程中，关系的某些属性在以后操作是必需的，则称其对应的结点为重要结点。重要结点有下述三条识别规则： 1) 查询对象属性对应的结点； 2) 设消边E使超图hg变为hg1，则N是hg1重要结点等价于N是hg重要结点或NE； 3) 若超图hg是不相交子图之并， N是hg重要结点等价于N是某一子图重要结点。下一页给出重要结点的例子。 L33c2 接下页

L33c21 2 loa bor boo 举例：图书馆查询超图重要结点： range of B is boo range of L is loa range of R is bor retrieve (R.na,R.ad) where R.cn=L.cn and L.nc=B.nc and B.ti=‘toef; 按照小关系规则选择消边的过程: HG消去boo得HG1 HG1消去loa得HG2 本页面表示各个超图的重要结点 na ad cn nc ti au da 1 超图查询属性消边boo 消边loa Hg na ad Hg1 na ad nc Hg2 na ad cn 重要结点 HG 图书boo(ti,au,nc) 书名作者书号读者bor(na,ad,cn) 姓名地址证号借阅loa(cn,nc,da) 证号书号日期查询：借阅‘Toef’的读者的姓名和地址 cn da nc R L B 消解B(满足条件) HG1 cn da R L 消解L(满足条件) HG2 cn R 接下页

L33D 超图消解算法的形式定义 [1] 执行投影规则的超图递归算法； [2] 带选边功能且执行投影规则的超图递归算法； [3] 超图消解的一般算法接下页小麦:上述三个算法见附加文档L33D1.doc/L33D2.doc/L33D3.doc

例：查询C(R1R2R3)，其中 关系： R1(A,B)，R2(C,D,E)，R3(F,G) 条件C：(A=C)(B<CB<D)(G<E)(F=f) (B=b) L33D1 条件超边C2： (B<C)(B<D) 等值连接条件:A=C 关系超边 R1(A,B) B A 关系超边 R2(C,D,E) D 关系超边 R3(F,G) 消解算法所得程序： 1)R({FG})=F=f(R3) 2)R({AB})=B=b(R1) 3)R({ADE})=R2 4)RESULT(HG5)=D5 R({ADE}); 5)RESULT(HG3)=D3 B<CB<D D4(R({AB})RESULT(HG5)); 6)RESULT(HG2)=D2R({FG}); 7)RESULT(HG)= D0G<E D1 (RESULT(HG2)RESULT(HG3)). 注意：省略了半连接；已合并某些相邻语句；可以把最后句子的投影笛卡儿乘积合并为连接。条件超边 C3：G<E F G E

L4 第四节基于复杂性估计的查询优化方法基于复杂性估计的查询优化方法分为两个阶段：阶段1：用前两节的启发性方法产生逻辑级优化的查询计划P；阶段2：为P中每个关系代数选择具最小复杂性的实现算法，即确定查询计划P的优化执行策略。集中式数据库中影响查询操作效率有三个因素： 1) 存取磁盘的磁盘块数； 2) 中间结果占用的磁盘空间量； 3) 处理机时间。这三个因素的度量就是查询执行策略的复杂性。但对于大规模数据库而言，由于主要因素是访问磁盘的磁盘块数，所以把它作为查询执行策略的复杂性。一个查询计划的执行策略复杂性，就是它调用的所有关系代数操作的磁盘存取块数的总和。

L5 第五节语义查询优化方法语义查询优化方法利用关系的完整性约束，通过修改，把给定的查询条件变换为更为有效的等价查询。例如压缩查询对象的搜索范围，然后使用一般性的查询算法。这个方法的关键是找出与给定查询有关的完整性约束条件。例：给出图书馆数据库三个关系： BOO(TI，AU，PN，NC) (图书信息) (属性的含义是：书名,作者,出版社,书号) BOR(NA，AD，CI，CN) (读者信息) (姓名,地址,城市,借书证号) LOA(CN，NC，DA) (借书信息) (属性的含义是：借书证号,书号,日期) 考察一个查询： Select Ti,Au,Pn From Boo,Bor,Loa Where Boo.nc=Loa.nc and Bor.cn=Loa.cn and ci=‘A’ 若数据库有两个完整性约束： (1) 读者只能在居住地的图书分馆借书； (2) A城分馆图书编号范围是U~V. 则查询条件作如下修改将提高查询效率： Where Boo.nc=Loa.nc and Bor.cn=Loa.cn and ci=‘A’ and boo.ncU and boo.ncV

第十二章 查询优化技术 关系数据库系统的查询自下而上分为三个层次： 第一层是关系代数的操作； 第二层是由关系代数的操作组成的查询计划； 第三层是用户查询的高级描述。