280 likes | 415 Views
L. 第十二章 查询优化技术 关系数据库系统的查询自下而上分为三个层次: 第一层是关系代数的操作; 第二层是由关系代数的操作组成的查询计划; 第三层是用户查询的高级描述。 第十一章讨论了第一层的各种实现算法。 本章主要讨论第二层的优化处理技术。 本章最后一节简单介绍了第三层的一些优化方法。 第一节 问题的提出 第二节 启发式关系代数优化方法 第三节 启发式关系演算优化方法 第四节 基于复杂性估计的查询优化方法 第五节 语义查询优化方法. L1. 第一节 问题的提出
E N D
L 第十二章 查询优化技术 关系数据库系统的查询自下而上分为三个层次: 第一层是关系代数的操作; 第二层是由关系代数的操作组成的查询计划; 第三层是用户查询的高级描述。 第十一章讨论了第一层的各种实现算法。 本章主要讨论第二层的优化处理技术。 本章最后一节简单介绍了第三层的一些优化方法。 第一节 问题的提出 第二节 启发式关系代数优化方法 第三节 启发式关系演算优化方法 第四节 基于复杂性估计的查询优化方法 第五节 语义查询优化方法
L1 第一节 问题的提出 多个关系代数构成的查询可以有多种操作策略,不同的操 作策略一般有不同的磁盘存取复杂性,因此选择复杂性较低的 一种策略是提高访问数据库效率的重要的优化技术。 下例说明不同的操作策略有不同的存取复杂性。 [例]设关系S有1000个学生的记录,其属性是姓名sn和学号s#。 关系SC有10000个选课记录,其属性是学号s#和课程号c#。 现要查询选修C2课程的学生姓名。 策略Q1=SN((S.S#=SC.S#)(SC.C#=‘C2’)(SSC)) 策略Q2=SN(S.C#=‘C2’(SS.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表示相同的关 系,则称之为互相等价,记作AB。 常用的关系代数等价变换规律包括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),其中, L1L2…Ln .即:逐次投影操作可一次投影到投影域交集。 4 选择投映交换率:关于选择与投影连续操作的次序交换问题, 若条件c仅包括投映域L中的属性,则L(c(A))c(L(A)) 若条件c包括投映域L外的属性x,则L(c(A))L(c(L,x(A))) 5 连接和笛卡儿乘积的交换率:ABBA,AcBBcA 6 集合操作的交换率:ABBA,ABBA 接下页
7.连接、笛卡儿乘积和集合操作的结合率 (AB)CA(BC) (AcB)dCAc(BdC) (AB)CA(BC) (AB)CA(BC) 其中A、B和C是关系代数表达式,小写字母c和d表示连接条件。 8.选择、连接和笛卡儿乘积的分配率 设A和B是关系代数表达式,d是连接条件。 c(AdB)(cA)dB (选择条件c仅涉及A的属性) c(AdB)(c1A)d(c2B) (三个选择条件满足c=c1c2,c1仅涉及A属性, c2仅涉及B属性) 用代替上边两式的d,式子同样成立。 9.投影、连接和笛卡儿乘积的分配率 设A和B是关系代数表达式,d是连接条件。 L(AdB)(L1A)d(L2B) (L=L1L2,L1仅涉及A的属性, L2仅涉及B的属性) 用代替上式的d,式子同样成立。 10.选择与集合操作的分配率 c(AB)c(A)c(B),c(AB)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’(RST)), 再表示为左图所示的查询树。 步骤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((loabor)boo)))) 接下页
查询树的优化 T1(C1(L(C2((loabor)boo)))) L23b1 C1 |da<2/1/1994 |ti |ti |boo.nc=loa.nc |ti,nc |ti,au,pn,nc,na,ad,ci,cn,da |nc boo boo |boo.nc=loa.nc & bor.cn=loa.cn C2 |da<2/1/1994 |bor.cn=loa.cn loa |nc,cn |cn bor loa bor boo loa bor 把与合并为连接,划分子树 两投映合并(变换规则3,4) 选择分解下移(变换规则1,2,8) |ti | boo.nc=loa.nc |ti,nc |nc boo boo |boo.nc=loa.nc |ti boo |da<2/1/1994 | bor.cn=loa.cn | bor.cn=loa.cn loa |nc,cn |cn bor loa bor |da<2/1/1994 loa bor 投映分散下移
L3 第三节 启发式关系演算优化方法 本节介绍QUEL查询语言使用的启发式优化方法。 这种方法使用超图表示QUEL查询,通过对超图逐 次消解消边产生查询算法。这种方法还提供一种 选择消边的策略和投影规则,按照这些策略和规 则处理消图,所产生的查询算法就能缩小操作的 关系和减少查询的中间结果,从而达到提高查询 效率的目的。 一 多重自然连接的优化处理 二 查询的超图HG表示 三 超图的消解算法
L31 一 多重自然连接的优化处理 问题:设关系R含属性A和B,关系S含属性B和C, 关系T含属性C和D。计算自然连接RST. 因自然连接满足结合率和交换率,故RST可按任何顺序执行, 但不同的执行顺序有不同的效率,故必须合理选择。例如,因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}T1T2Tn; 写结果入result; endfor; 上述策略可推广到一般形式的 多重自然连接Q=RS1…Sn, 设在属性结构上,{Si}两两不交, 但任Si 与R交集非空,算法为:
L32 二 查询的超图HG表示 假设查询Q的关系代数表达式是:C1C2…Cn(R1R2…Rk) 其中Ci是不含操作的任意条件,在超图表示中Ci分为三类: (1)A=B; (2)A=a或a=A; (3)其它 其中大写字母表示属性名,小写字母表示常量。 超图由结点和超边组成,表示为HG=(V,HE), 其中,V是结点集合,每个结点表示某关系的一个属性; HE是超边集合,每个超边表示包含一组结点的集合。 超边分为两种类型: 关系超边:由某关系的属性结点组成,用包围结点的实线框表示. 条件超边:由第三类Ci属性结点组成,用包围结点的虚线框表示. 对查询Q按下述方法建立超图(这样的超图称为连接超图): 1)按每个关系的属性结构画出对应的关系超边, 不同关系的属性即使同名也视为不同的结点, 若有第一类条件规定了属于不同关系的两个属性的等值约束, 则这两个属性无论同名与否,都视为相同结点; 2)按每个第三类条件Ci画出对应的条件超边。 接下页
L321 [例] 画出查询C1C2C3C4(R1R2R3)的连接超图,其中 三个关系的属性结构是: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<CB<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<CB<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<CB<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=RS,其中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=RS, 其中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重要结点或NE; 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(R1R2R3),其中 关系: R1(A,B),R2(C,D,E),R3(F,G) 条件C:(A=C)(B<CB<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<CB<D D4(R({AB})RESULT(HG5)); 6)RESULT(HG2)=D2R({FG}); 7)RESULT(HG)= D0G<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.ncU and boo.ncV