第九章关系查询处理和查询优化.ppt

第九章关系查询处理和查询优化.ppt

《第九章关系查询处理和查询优化.ppt》由会员分享，可在线阅读，更多相关《第九章关系查询处理和查询优化.ppt（52页珍藏版）》请在冰豆网上搜索。

南昌大学信工学院计算机系南昌大学信工学院计算机系数据库系统概论数据库系统概论AnIntroductiontoDatabaseSystem第九章第九章关系查询处理和查询优化关系查询处理和查询优化AnIntroductiontoDatabaseSystem查询优化查询优化n查询优化极大地影响RDBMS的性能。

n关系数据语言的级别很高，使DBMS可以从关系表达式中分析查询语义。

n用户不必考虑如何最好地表达查询以获得较好的效率，系统可以比用户程序的优化做得更好。

AnIntroductiontoDatabaseSystem第九章第九章关系查询处理和查询优化关系查询处理和查询优化9.1关系数据库的查询处理9.2关系数据库的查询优化9.3代数优化9.4物理优化AnIntroductiontoDatabaseSystem9.19.1关系数据库的查询处理关系数据库的查询处理9.1.1查询处理步骤9.1.2实现查询操作的算法示例AnIntroductiontoDatabaseSystem9.1.19.1.1查询处理步骤查询处理步骤RDBMS查询处理可以分为下列4个阶段：

1查询分析2查询检查3查询优化4查询执行（如P263中的图9.1）AnIntroductiontoDatabaseSystem1、查询分析、查询分析n首先对查询语句进行扫描、词法分析和语法分析。

从查询语句中识别出语言符号，如SQL关键字、属性名和关系名等，进行语法检查和语法分析，判断查询语句是否符合SQL语法规则。

AnIntroductiontoDatabaseSystem2、查询检查、查询检查n根据数据字典对合法的查询语句进行语义检查，即检查语句中的数据库对象，如属性、关系名，是否存在和有效。

n根据数据库字典中的权限或违反完整性约束定义对用户的存取权限进行检查。

n检查通过后将SQL查询语句转换成等价的关系代数表达式。

AnIntroductiontoDatabaseSystem2、查询检查（续）、查询检查（续）nRDBMS一般都用查询树（querytree，也称语法树），来表示扩展的关系代数表达式。

这个过程把数据库对象的外部名称转换为内部表示。

AnIntroductiontoDatabaseSystem3、查询优化、查询优化n查询优化就是在许多查询策略中选择一个高效执行的查询处理策略。

n查询优化有许多优化方法。

按其层次可以分为代数优化与物流优化两个。

n实际的RDBMS中的查询优化器都综合地运用了这些优化技术，以获取最好的查询优化效果。

AnIntroductiontoDatabaseSystem4、查询执行、查询执行n依据优化器得到的执行策略生成查询计划，由代码生成器生成执行这个查询计划的代码。

AnIntroductiontoDatabaseSystem9.1.2实现查询操作的算法示例实现查询操作的算法示例一、选择操作的实现见P264265中的例1。

二、连接操作的实现见P265266中的例2。

AnIntroductiontoDatabaseSystem9.2关系数据库系统的查询优化关系数据库系统的查询优化n9.2.1查询优化概述n9.2.2一个实例AnIntroductiontoDatabaseSystemn查询优化的必要性n查询优化极大地影响RDBMS的性能。

n查询优化的可能性n关系数据语言的级别很高，使DBMS可以从关系表达式中分析查询语义。

9.2.1查询优化概述查询优化概述AnIntroductiontoDatabaseSystem由由DBMS进行查询优化的好处进行查询优化的好处n用户不必考虑如何最好地表达查询以获得较好的效率n系统可以比用户程序的优化做得更好

（1）优化器可以从数据字典中获取许多统计信息，而用户程序则难以获得这些信息AnIntroductiontoDatabaseSystem由由DBMS进行查询优化的好处进行查询优化的好处

（2）如果数据库的物理统计信息改变了，系统可以自动对查询重新优化以选择相适应的执行计划。

在非关系系统中必须重写程序，而重写程序在实际应用中往往是不太可能的。

（3）优化器可以考虑数百种不同的执行计划，而程序员一般只能考虑有限的几种可能性。

（4）优化器中包括了很多复杂的优化技术AnIntroductiontoDatabaseSystem代价模型代价模型n集中式数据库n单用户系统总代价=I/O代价+CPU代价n多用户系统总代价=I/O代价+CPU代价+内存代价n分布式数据库总代价=I/O代价+CPU代价+内存代价+通信代价其中：

I/O代价是主要的AnIntroductiontoDatabaseSystem9.2.2一个实例一个实例查询优化的必要性查询优化的必要性例：

求选修了2号课程的学生姓名SELECTStudent.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=2;AnIntroductiontoDatabaseSystem查询优化的必要性（续）查询优化的必要性（续）假设1：

外存：

Student:

1000条,SC:

10000条,选修2号课程:

50条假设2：

一个内存块装元组:

10个Student,或100个SC,内存中一次可以存放:

5块Student元组,1块SC元组和若干块连接结果元组假设3：

读写速度：

20块/秒假设4：

连接方法：

基于数据块的嵌套循环法AnIntroductiontoDatabaseSystem执行策略执行策略11name（Student.Sno=SC.SnoSC.Cno=2（StudentSC）StudentSC读取总块数=读Student表块数+读SC表遍数*每遍块数=1000/10+（1000/（105）（10000/100）=100+20100=2100读数据时间=2100/20=105秒AnIntroductiontoDatabaseSystem不同的执行策略不同的执行策略,考虑考虑I/O时间时间中间结果大小=1000*10000=107（1千万条元组）写中间结果时间=10000000/10/20=50000秒读数据时间=50000秒总时间=1055000050000秒=100105秒=27.8小时AnIntroductiontoDatabaseSystem执行策略执行策略22.2name（SC.Cno=2（StudentSC）读取总块数=2100块读数据时间=2100/20=105秒中间结果大小=10000（减少1000倍）写中间结果时间=10000/10/20=50秒读数据时间=50秒总时间1055050秒205秒=3.4分AnIntroductiontoDatabaseSystem执行策略执行策略33.3Sname（StudentSC.Cno=2（SC）读SC表总块数=10000/100=100块读数据时间=100/20=5秒中间结果大小=50条不必写入外存读Student表总块数=1000/10=100块读数据时间=100/20=5秒总时间55秒10秒AnIntroductiontoDatabaseSystem执行策略执行策略3（续）（续）4.3name（StudentSC.Cno=2（SC）假设SC表在Cno上有索引，Student表在Sno上有索引读SC表索引=读SC表总块数=50/1001块读数据时间中间结果大小=50条不必写入外存AnIntroductiontoDatabaseSystem执行策略执行策略3（续）（续）读Student表索引=读Student表总块数=50/10=5块读数据时间总时间10秒AnIntroductiontoDatabaseSystem比较比较Q1、Q2、Q3，说明查询优化的必要性。

，说明查询优化的必要性。

nQ1总时间约：

27.8小时nQ2总时间约：

3.4分nQ3总时间约：

总时间连接运算例：

Student.Sno=SC.Sno（StudentSC）StudentSCn5、提取公共子表达式其他：

在执行连接操作前对关系适当进行预处理等，如：

按连接属性排序，在连接属性上建立索引属于物理优化。

AnIntroductiontoDatabaseSystem关系代数表达式的优化算法关系代数表达式的优化算法下面给出遵循上述启发式规则的优化算法。

算法：

关系表达式的优化。

输入：

一个关系表达式的语法树。

输出：

优化的查询树（可转化为计算该表达式的优化程序）。

方法：

（1）分解选择运算利用规则4把形如F1F2Fn（E）变换为F1（F2（Fn（E）AnIntroductiontoDatabaseSystem关系代数表达式的优化算法关系代数表达式的优化算法（续续）

（2）通过交换选择运算，将其尽可能移到叶端对每一个选择，利用规则49尽可能把它移到树的叶端。

（3）通过交换投影运算，将其尽可能移到叶端对每一个投影利用规则3，5，l0，11中的一般形式尽可能把它移向树的叶端。

AnIntroductiontoDatabaseSystem关系代数表达式的优化算法关系代数表达式的优化算法（续续）（4）合并串接的选择和投影，以便能同时执行或在一次扫描中完成n利用规则35把选择和投影的串接合并成单个选择、单个投影或一个选择后跟一个投影。

n使多个选择或投影能同时执行，或在一次扫描中全部完成n尽管这种变换似乎违背“投影尽可能早做”的原则，但这样做效率更高。

AnIntroductiontoDatabaseSystem关系代数表达式的优化算法关系代数表达式的优化算法（续续）（5）对内结点分组n把上述得到的语法树的内节点分组。

n每一双目运算（，-）和它所有的直接祖先为一组（这些直接祖先是，运算）。

n如果其后代直到叶子全是单目运算，则也将它们并入该组，但当双目运算是笛卡尔积（），而且其后的选择不能与它结合为等值连接时除外。

把这些单目运算单独分为一组。

最后生成程序AnIntroductiontoDatabaseSystem

（1）把查询转换成某种内部表示）把查询转换成某种内部表示语法树结果结果project（Sname）select（SC.Cno=2）join（Student.Sno=SC.Sno）StudentSCAnIntroductiontoDatabaseSystem关系代数语法树关系代数语法树SnameSC.Cno=2Student.Sno=SC.SStudentSCAnIntroductiontoDatabaseSystem利用优化算法把语法树转换成标准（优化）形式SnameStudent.Sno=SC.SnoSC.Cno=2StudentSCAnIntroductiontoDatabaseSystem9.4物理优化物理优化物理优化：

选择低层的存取路径，达到查询优化的目标。

选择的方法可以是：

基于规则的启发式优化。

基于代价估算的优化。

两者结合的优化方法。

AnIntroductiontoDatabaseSystem9.4.1基于启发式规则的存储路径选择优化基于启发式规则的存储路径选择优化一、选择操作的启发式规则有：

1、对于小关系，使用全表顺序扫描（即使列上有索引）对于大关系：

2、对于选择条件是主码=值的查询，查询结果最多是一个元组，可以选择主码索引。

一般RDBMS会自动建立主码索引。

3、对于选择条件是非主属性=值的查询，并且选择列上有索引，则要估算查询结果的元组数目，如果比较小（10%）可以使用索引扫描方法，否则还是使用全表顺序扫描。

4、对于选择条件是属性上的非等值查询或者范围查询，并且选择列上有索引，同样要估算查询结果的元组数目，如果比较小（10%）可以使用索引扫描方法，否则还是使用全表顺序扫描。

5、对于用AND连接的合取选择条件，如果有涉及这些属性的组合索引，则优先采用组合索引方法；如果某些属性上有一般索引，则可以用例1-C4中介绍的索引扫描方法，否则使用全表顺序扫描。

6、对于OR连接的分析选择条件，一般使用全表顺序扫描。

AnIntroductiontoDatabaseSystem9.4.1基于启发式规