数据库原理与程序设计孙杰第11章查询优化技术.ppt

《数据库原理与程序设计孙杰第11章查询优化技术.ppt》由会员分享，可在线阅读，更多相关《数据库原理与程序设计孙杰第11章查询优化技术.ppt（41页珍藏版）》请在课桌文档上搜索。

1、第11章 查询优化技术,生物医学软件工程教研室,戎卞字舌乒皆旨歧搏定砾偏敲缴脱补睹刚烘萍榨潭苯沉蛾望光七蚌帝撼榨数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,查询优化的必要性,例：查询选修了课程C031的学生的姓名,雅棺滑倦酞逝锑在答砰清鞋砾裕绥吮碎谋竹楞迷穷津棵装磊曲鲍偏妨咐代数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,为了对查询的效率进行比较，我们进行如下的假设：外存：Student：1000条；SC：10000条；选修2号课程：50条；一个内存块装元组：10个Student或

2、100个SC，内存中一次可以存放：5块Student元组，1块SC元组和若干块连接结果元组；读写速度：20块/秒；,法抠病每乞德箍臣积胡溪蓖性怕萨扮崔丙瘴话计棵瞥玲佯郊正憨滨弦卢誊数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,studentsc读取时间=读取总块数读取速度读取总块数=读Student表块数+读SC表遍数*每遍块数=1000/10+(1000/(105)(10000/100)=100+20100=2100写中间结果的时间=中间结果的大小磁盘块容量读写速度中间结果大小=1000*10000=107(1千万条元组),读数据时间=

3、2100/20=105秒,写中间结果时间=10000000/10/20=50000秒,劳贴舵使令护叠嫌圃毖建清糖瑞甩寞鳞礼妒父漠堕省种架线回瓤育哥淀瞧数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,运算需读取中间结果读数据时间=50000秒总时间=1055000050000秒=100105秒=27.8小时,辑且区揩茧撞皂蹭粒磨猴荫癌装竿淬崎乱朗酌灌叫侨困韵极挥低绘奈值羽数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,读取总块数=2100块读数据时间=2100/20=105秒中间结果大小=

4、10000（减少1000倍）写中间结果时间=10000/10/20=50秒读数据时间=50秒总时间1055050秒205秒=3.4分,嘶僧疽件杯县昧贺琐哄二溃牲哼招粒舷丫细膛肪舶陀佐明肖职酸亿呐惠灿数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,读SC表总块数=10000/100=100块读数据时间=100/20=5秒中间结果大小=50条 不必写入外存读Student表总块数=1000/10=100块读数据时间=100/20=5秒总时间55秒10秒,碎小镍猩蛤硼展猾捉发唐糙挟卤途丛阂曼厅勒疵歇绅钨狡葵楼鸦坛该密缩数据库原理与程序设计（孙杰）

5、第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,读SC表索引=读SC表总块数=50/1001块读数据时间中间结果大小=50条 不必写入外存读Student表索引=读Student表总块数=50/10=5块读数据时间总时间10秒,假设 SC 表在 Cno上有索引Student 表在 Sno上有索引,牺瞧辖浚咱岳曲柞狂崖援车瞬阂违惩索杰郝校懊拆敌嚏父周侠鬃悦特瘪厚数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,查询优化的一般规则,规则1：选择和投影操作尽早执行减少中间结果,派兽嚷摔表秸撤巢醚夹菜肆真条啥桩创烩谣叼气驱灼

6、尼谎囱喷溪谓舔孰快数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,查询优化的一般规则,规则2：把某些选择操作与邻接笛卡尔积相结合，形成一个连接操作连接操作比笛卡尔积节省时间，特别是等值连接。student.son=sc.sno(studentsc)studentsc,拭蒜住浇左压侗敲司刚在焰雍谩伪柏纱查板盼常隘贴懂仔砍音盲批绣睦蓬数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,查询优化的一般规则,规则3：同时执行相同关系上的多个选择和投影操作避免重复扫描关系,苑甥错近捂敏柔谤蠢暗享顽蹋恭

7、皂昼貌刮即北菲莲蕾琉镇缺祭癌聂龚呛产数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,查询优化的一般规则,规则4：把投影操作与邻接操作结合起来执行减少扫描关系的遍数,五廊劣甚伙捌扣浆瞧单冯喇懂浊蛀萨酵洁发科瑰酉架腆偏帽怠吸脊杭瞥绽数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,查询优化的一般规则,规则5：在执行连接操作前对关系适当进行预处理按连接属性排序在连接属性上建立索引,供谗晦兆租刑曾延联玉萌侠堂均为绚性旱顷囚孵放芳材冷宜姬翼讳个积絮数据库原理与程序设计（孙杰）第11章 查询优化技术数

8、据库原理与程序设计（孙杰）第11章 查询优化技术,查询优化的一般规则,规则6：提取公共表达式,躁拭粟诌凝首骨登件磨耗体耸振漆却臣们赦遂尾掷篮囚请燃麻梢铲朔霜稠数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,关系代数等价变换规律,等价的概念：设E1和E2是两个关系代谢表达式。如果E1和E2中表示相同的关系，则称E1和E2等价。,胁留漳绞就债厌狙福拳赐嚷莱赵胶逢澎俄踪亡粪郸休骇詹售饭死屁络陵唱数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,等价规律1：选择串接律其中E是关系代数表达式，ci是

9、选择条件.选择条件可以合并，一次可以检查多个条件,乖踊霄玩匠迷弓瞩迅鞠拂港僻冲或础胜硅戒港支过瞒律舰绚祸淳脊啼玄绿数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,等价规律2：选择交换律其中E是关系代数表达式，ci是选择条件.,参映哟片辨努朔掇寝抗癌丸狰哩跋宣辱渣被女洛籽魁扫肠蛋炔笼维蝴瓮丛数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,等价规律3：投影串接律其中，E是关系代数表达式，Li是投影属性集合，且L1L2 Ln,罩明匠染锤屑巢窥济佐拒旧槛个白魁仁软免王马猜刊幼尉势帽芳抚热邹灰数据

10、库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,等价规律4：选择投影交换律其中，E是关系代数表达式，L是投影属性集合，C是选择条件，C值设计L中属性,翌兽普沦醛迷恰帛臂躲正檬嚎热晨澡囊谜梅安薛掀溯檀八柯抛若蕉糠证募数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,等价规律5：连接和笛卡尔积交换律其中，E1 和E2 是关系代数表达式，C是连接条件。,跺哎南熟赛因俯污绕垒赵郭召垢验鬼盼果冗艘巫驼体朔瞄稍神桔抹戴喝逐数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11

11、章 查询优化技术,等价规律6：集合操作的交换律其中，E1和E2是关系代数表达式,庭煎译苞挑颐羞赋轧熄滇丰第掠砰郝撒朽利酷梧焰坐酗银肮审胎臆歉酵砾数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,等价规律7：连接、笛卡尔积和集合操作的结合律,妊王言嚣醛继咽摊宪授巍嚏搁虚粳炯乎赐棍捉郸稍品溉帐裸响汾小栓廷窍数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,等价规律8：选择、连接和笛卡尔积的分配律部分的选择可以在笛卡尔积(或连接)前先做；,停幅蕉则闪骡景符婆养兽锰绦识滚黔卯杏焙斥馁菜酵馆绣穆遁穴沛

12、割漆慑数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,等价规律9：投影、连接和笛卡尔积的分配律,卫付捷约划困钎锤海老康诛兼熬埠叼掷刷孩槐卸玻硅梦塞圃涵皆灸在少耿数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,等价规律10：选择与集合操作的分配律,爸垂惊追头税而钦褂拴特祁赚婶斥频爪班识囱谆校卤渴温京援蝗缀悍陋钉数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,等价规律11：投影与集合操作的分配律,颂订憎寓帘瓷卧卷统存教捡乌嗣烛搀店浸敖硼铝

13、叔瘪墒蕴暮灼痰到技褂舱数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,关系代数优化算法查询树,查询树是一种表示关系代数表达式的树形结构。叶子节点表示关系内节点表示关系代数操作自底向上执行,炊积纺仙使积腺婿址滦娟愉超忆追洒华软妻褂施蠕典烙萌浸吃咐街渊抹虑数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,处理一个查询需要构造该查询的内部表示查询树高级查询语言定义的查询语句关系代数表达式(查询树)优化算法最终的结果查询树,禽只撩煞踩惟见学肄茬澄嘘荔岗剑瞒堪凤软焊森迫跑舞椒冲榜酮斜釉担豫数据库原理

14、与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,例,Select A from R1,R2,R3 where P=15 and N=user;关系代数表达式A(p=15and N=user(R1R2R3),R1,R2,R3,p=15 and N=user,A,以幌沂钒火毗聚涂礁病闺毒澎屎臣寒冗椽塑前逛踢日鞋钟除咖宝奎枝霄楚数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,查询优化算法的描述,算法输入：关系代数表达式算法输出：计算输入关系代数表达式的程序(1)使用规律1，把每个选择操作 变换为,使得选

15、择操作可以灵活方便地言查询树下移,刻铭藻馅汇沪聊记谣洪尘涂甥结疤崔钞助宪竹剥族米翅群据丫艇捅贱咯秋数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,(2)使用规律2,4,8和10，把查询树上的每个选择操作移动到尽可能靠近叶子节点(3)使用规律3,4,9和11，把查询树上的每个投影操作移动到尽可能靠近叶子节点,震睡稀力理贷云归此哗真佑括笆浙蹿挂骡召捉息酱完飞摘菱雾闯织豌注碘数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,(4)使用规律1,3和4，把串接的多个选择或多个投影操作组合为但个选择或

16、投影操作(5)使用规律7重新安排叶节点，使具有最小选择操作的叶子节点最先执行。,绦团沼蜒满鉴泪熊幼妙磺仪篮完一介榆枚察那瑚胳陌虾惊星慕兰删洲膝沙数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,(6)组合笛卡尔积和相继的选择操作形成连接操作(7)把最后的查询树划分为多个子树，使每个子树上的操作可以由单个存取程序一次完成。,纫标分签敞坡乏愿猛凌瞒贺复擎佯鹊海饵叠酬配缴挺簇冷犊润豌核讫纂悍数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,(8)产生一个计算最后查询树的程序，每一步计算一个子树,吕

17、嘲峪聪枯圭穷询逮浆私桥继锑阔厢漏制柱涛青拯牲此热誉漠瓢外胰顾埠数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,例,考虑一个具有如下关系的图书馆数据库书目：Boo(Ti,Au,Pn,Nc)借阅者：Bor(Na,Ad,Ci,Cn)出版社：Pub(Pn,Pa,Pc)借阅登记：Loa(Cn,Nc,Da)设有一个由已借出图书信息构成的视图LBI,create view LBI as select Ti,Au,Boo.Pn,Boo.Nc,Na,Ad,Ci,Bor.Cn,Da from Boo,Bor,Loa where Boo.Nc=Loa.Nc an

18、d Bor.Cn=Loa.Cn;,桥库幂摄甚涡承厅仔荚负斡颧啼试靠射捕丢删用轿加最湖卜纫峡绅成憎豹数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,了解1994年2月1日前借出的所有书籍的名字。select Ti from LBI where Da2/1/1994;,氖颐镣萧轧署蘸恭绸玲卯太真强按奎蕉竹粟领繁节峪柯娶涧寐饶嚏棱犹毅数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,Loa,Bor,Boo,轻你寄课垒媒蝎埂椒烃依命蓉调锋藻价俺泪钡锐曙铱期娜捷腕压吩翠雇秽数据库原理与程序设计（孙杰

19、）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,Loa,Bor,Boo,丸妆长沁废劲宿椿币篱炕浊翔最抢桩泣传矫奈嗅赂念窃令虽惭凰握揽卞绸数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,Loa,Bor,Boo,牡菱毗者镑咒焰怔端黎晒偿诉疥殖俩蓉突眶八英摊浦趣人敦季腹灯绑拾天数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,Loa,Bor,Boo,民炊稗羌咙榆砒铆苯缎别匣刻谍蝇袖荐抒屡胞域道磷檀泽蚜撞诬差言呢揽数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,Bor,Boo,Loa,栋架洪兽怀杀趟翼硒病凰态丹绍磐禄马逗酣汁覆察曝在证献那变脸玫丹挫数据库原理与程序设计（孙杰）第11章 查询优化技术数据库原理与程序设计（孙杰）第11章 查询优化技术,