信息科学与技术学院计算机系课件

信息科学与技术学院计算机系数据库系统概论数据库系统概论An Introduction to Database System第九章第九章 关系查询处理和查询优化关系查询处理和查询优化AnIntroductiontoDatabaseSystem信息科学与技术学院计算机系数据库系统概论AnIntrodu1第九章 关系系统及其查询优化9.1关系数据库系统的查询处理9.2关系数据库系统的查询优化9.3代数优化9.4物理优化9.3小结AnIntroductiontoDatabaseSystem第九章关系系统及其查询优化9.1关系数据库系统的查询处29.1关系数据库系统的查询处理9.1.1查询处理步骤9.1.2实现查询操作的算法示例AnIntroductiontoDatabaseSystem9.1关系数据库系统的查询处理9.1.1查询处理步骤An39.1.1 查询处理步骤p查询分析n词法/语法/语义分析n符号名转换p查询检查n语义检查n安全性检查n完整性检查p查询优化n代数优化n物理优化p查询执行n查询计划生成n代码生成AnIntroductiontoDatabaseSystem9.1.1查询处理步骤查询分析AnIntroductio49.1关系数据库系统的查询处理9.1.1查询处理步骤9.1.2实现查询操作的算法示例AnIntroductiontoDatabaseSystem9.1关系数据库系统的查询处理9.1.1查询处理步骤An59.1.2 实现查询操作的算法示例p一一 选择操作的实现选择操作的实现p二二 连接操作的实现连接操作的实现AnIntroductiontoDatabaseSystem9.1.2实现查询操作的算法示例一选择操作的实现AnI69.1.2 实现查询操作的算法示例一一 选择操作的实现选择操作的实现n1、简单的全表扫描方法、简单的全表扫描方法n2、索引、索引(或散列或散列)扫描方法扫描方法例例1 Select*from student where p表达式情况表达式情况:nC1:无条件无条件;nC2:Sno=200215121;nC3:Sage 20;nC4:Sdept=CS AND Sage 20;AnIntroductiontoDatabaseSystem9.1.2实现查询操作的算法示例一选择操作的实现AnI79.1.2 实现查询操作的算法示例p1、简单的全表扫描方法、简单的全表扫描方法AnIntroductiontoDatabaseSystem9.1.2实现查询操作的算法示例1、简单的全表扫描方法An89.1.2 实现查询操作的算法示例p2、索引、索引(或散列或散列)扫描方法扫描方法p例例1-C2 nSno上有索引上有索引p例例1-C3 nSage上有上有B+树索引树索引p例例1-C4 nSdept和和Sage上都有索引上都有索引AnIntroductiontoDatabaseSystem9.1.2实现查询操作的算法示例2、索引(或散列)扫描方法99.1.2 实现查询操作的算法示例二二 连接操作的实现连接操作的实现n1、嵌套循环方法、嵌套循环方法(nested loop)n2、排序、排序-合并方法合并方法(sort-merge join)n3、索引连接、索引连接(Index Join)方法方法n4、Hash Join方法方法例例2 Select*from student,scwhere student.sno=sc.sno;AnIntroductiontoDatabaseSystem9.1.2实现查询操作的算法示例二连接操作的实现AnI109.1.2 实现查询操作的算法示例p1、嵌套循环方法、嵌套循环方法AnIntroductiontoDatabaseSystem9.1.2实现查询操作的算法示例1、嵌套循环方法AnIn119.1.2 实现查询操作的算法示例p2、排序合并方法、排序合并方法20021512120021512220021512320021512420021512122002151213200215121120021512222002151223200215123520021512332002151231SNOSNOCNOAnIntroductiontoDatabaseSystem9.1.2实现查询操作的算法示例2、排序合并方法20021129.1.2 实现查询操作的算法示例p3、索引连接方法、索引连接方法20021512320021512220021512120021512420021512122002151233200215123120021512122002151223200215121520021512232002151231SNOSNOCNO索引表AnIntroductiontoDatabaseSystem9.1.2实现查询操作的算法示例3、索引连接方法20021139.1.2 实现查询操作的算法示例p3、Hash Join方法方法20021512320021512220021512120021512412002151233200215122520021512132002151222200215121120021512332002151232200215121SNOSNOCNOAnIntroductiontoDatabaseSystem9.1.2实现查询操作的算法示例3、HashJoin方法14第四章 关系系统及其查询优化9.1关系数据库系统的查询处理9.2关系数据库系统的查询优化9.3代数优化9.4物理优化9.3小结AnIntroductiontoDatabaseSystem第四章关系系统及其查询优化9.1关系数据库系统的查询处159.2关系数据库系统的查询优化p查询优化的必要性n查询优化极大地影响RDBMS的性能。p查询优化的可能性n关系数据语言的级别很高，使DBMS可以从关系表达式中分析查询语义。AnIntroductiontoDatabaseSystem9.2关系数据库系统的查询优化查询优化的必要性AnIntr169.2.1 查询优化概述p关系系统的查询优化既是RDBMS的关键技术又是关系系统的优点所在;p大大减轻了用户的负担。AnIntroductiontoDatabaseSystem9.2.1查询优化概述关系系统的查询优化既是RDBMS的关179.2.1 查询优化概述p由DBMS进行查询优化的好处n用户不必考虑如何最好地表达查询以获得较好的效率n系统可以比用户程序的优化做得更好(1)优化器可以从数据字典中获取许多统计信息，而用户程序则难以获得这些信息AnIntroductiontoDatabaseSystem9.2.1查询优化概述由DBMS进行查询优化的好处AnI18由DBMS进行查询优化的好处p(2)如果数据库的物理统计信息改变了，系统可以自动对查询重新优化以选择相适应的执行计划。在非关系系统中必须重写程序，而重写程序在实际应用中往往是不太可能的。p(3)优化器可以考虑数百种不同的执行计划，而程序员一般只能考虑有限的几种可能性。p(4)优化器中包括了很多复杂的优化技术AnIntroductiontoDatabaseSystem由DBMS进行查询优化的好处(2)如果数据库的物理统计信息改199.2.1 查询优化概述p集中式数据库查询开销：nI/O+CPU+内存p分布式数据库查询开销：nI/O+CPU+内存+通信代价p查询优化的总目标n选择有效策略，求得给定关系表达式的值，使得查询代价较小AnIntroductiontoDatabaseSystem9.2.1查询优化概述集中式数据库查询开销：AnIntr209.2.2 一个实例例3：求选修了课程2的学生姓名SELECTStudent.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=2;AnIntroductiontoDatabaseSystem9.2.2一个实例例3：求选修了课程2的学生姓名AnI219.2.2 一个实例（续）假设1：外存：Student:1000条,SC:10000条,选修2号课程:50条假设2：一个内存块装元组:10个Student,或100个SC,内存中一次可以存放:5块Student元组,1块SC元组和若干块连接结果元组假设3：读写速度：20块/秒假设4：连接方法：基于数据块的嵌套循环法AnIntroductiontoDatabaseSystem9.2.2一个实例（续）假设1：外存：AnIntrod229.2.2 一个实例（续）p三种策略：p1name(Student.Sno=SC.SnoSC.Cno=2(StudentSC)p2name(SC.Cno=2(StudentSC)p2Sname(StudentSC.Cno=2(SC)AnIntroductiontoDatabaseSystem9.2.2一个实例（续）三种策略：AnIntroduc23执行策略11name(Student.Sno=SC.SnoSC.Cno=2(StudentSC)StudentSC读取总块数=读Student表块数+读SC表遍数*每遍块数=1000/10+(1000/(105)(10000/100)=100+20100=2100读数据时间=2100/20=105秒AnIntroductiontoDatabaseSystem执行策略11name(Student.Sno=S24不同的执行策略,考虑I/O时间中间结果大小=1000*10000=107(1千万条元组)写中间结果时间=10000000/10/20=50000秒读数据时间=50000秒总时间=1055000050000秒=100105秒=27.8小时AnIntroductiontoDatabaseSystem不同的执行策略,考虑I/O时间中间结果大小=1000259.2.2 一个实例（续）策略2.2name(SC.Cno=2(StudentSC)读取总块数=2100块读数据时间=2100/20=105秒中间结果大小=10000（减少1000倍）写中间结果时间=10000/10/20=50秒读数据时间=50秒总时间1055050秒205秒=3.4分AnIntroductiontoDatabaseSystem9.2.2一个实例（续）策略2.2name(269.2.2 一个实例（续）策略3.3Sname(StudentSC.Cno=2(SC)读SC表总块数=10000/100=100块读数据时间=100/20=5秒中间结果大小=50条不必写入外存读Student表总块数=1000/10=100块读数据时间=100/20=5秒总时间55秒10秒AnIntroductiontoDatabaseSystem9.2.2一个实例（续）策略3.3Sname(S279.2.2 一个实例（续）策略4.2name(StudentSC.Cno=2(SC)假设SC表在Cno上有索引，Student表在Sno上有索引 读SC表索引=读SC表总块数=50/1001块读数据时间中间结果大小=50条不必写入外存AnIntroductiontoDatabaseSystem9.2.2一个实例（续）策略4.2name(S289.2.2 一个实例（续）读Student表索引=读Student表总块数=50/10=5块读数据时间总时间连接运算例：Student.Sno=SC.Sno(StudentSC)StudentSCp提取公共子表达式AnIntroductiontoDatabaseSystem9.3.2查询树的启发式优化典型启发式规则：某些选择运算479.3.2 查询树的启发式优化算法算法：关系表达式的优化输入：一个关系表达式的语法树。输出：计算该表达式的程序。方法：（1）分解选择运算利用规则4把形如F1F2Fn(E)变换为F1(F2(Fn(E)AnIntroductiontoDatabaseSystem9.3.2查询树的启发式优化算法算法：关系表达式的优化A48关系代数表达式的优化算法(续)（2）通过交换选择运算，将其尽可能移到叶端对每一个选择，利用规则49尽可能把它移到树的叶端。（3）通过交换投影运算，将其尽可能移到叶端对每一个投影利用规则3，10，11，5中的一般形式尽可能把它移向树的叶端。AnIntroductiontoDatabaseSystem关系代数表达式的优化算法(续)（2）通过交换选择运算，将其49关系代数表达式的优化算法(续)（4）合并串接的选择和投影，以便能同时执行或在一次扫描中完成n利用规则35把选择和投影的串接合并成单个选择、单个投影或一个选择后跟一个投影。n使多个选择或投影能同时执行，或在一次扫描中全部完成n尽管这种变换似乎违背“投影尽可能早做”的原则，但这样做效率更高。AnIntroductiontoDatabaseSystem关系代数表达式的优化算法(续)（4）合并串接的选择和投影，50关系代数表达式的优化算法(续)（5）对内结点分组n把上述得到的语法树的内节点分组。n每一双目运算(，-)和它所有的直接祖先为一组(这些直接祖先是，运算)。n如果其后代直到叶子全是单目运算，则也将它们并入该组，但当双目运算是笛卡尔积()，而且其后的选择不能与它结合为等值连接时除外。把这些单目运算单独分为一组。AnIntroductiontoDatabaseSystem关系代数表达式的优化算法(续)（5）对内结点分组AnIn51关系代数表达式的优化算法(续)（6）生成程序n生成一个程序，每组结点的计算是程序中的一步。n各步的顺序是任意的，只要保证任何一组的计算不会在它的后代组之前计算。AnIntroductiontoDatabaseSystem关系代数表达式的优化算法(续)（6）生成程序AnIntr52优化的一般步骤 1把查询转换成某种内部表示2代数优化：把语法树转换成标准（优化）形式3物理优化：选择低层的存取路径4生成查询计划，选择代价最小的AnIntroductiontoDatabaseSystem优化的一般步骤1把查询转换成某种内部表示AnIntro53优化的一般步骤(续)（1）把查询转换成某种内部表示例：求选修了课程2的学生姓名SELECTStudent.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=2;AnIntroductiontoDatabaseSystem优化的一般步骤(续)（1）把查询转换成某种内部表示AnI54（1）把查询转换成某种内部表示语法树结果结果project(Sname)select(SC.Cno=2)join(Student.Sno=SC.Sno)StudentSCAnIntroductiontoDatabaseSystem（1）把查询转换成某种内部表示语法树结果project(S55关系代数语法树Sname SC.Cno=2 Student.Sno=SC.S StudentSCAnIntroductiontoDatabaseSystem关系代数语法树SnameSC.Cno=2Stu56（2）代数优化利用优化算法把语法树转换成标准（优化）形式Sname Student.Sno=SC.Sno SC.Cno=2 StudentSCAnIntroductiontoDatabaseSystem（2）代数优化利用优化算法把语法树转换成标准（优化）形式S57第九章 关系系统及其查询优化9.1关系数据库系统的查询处理9.2关系数据库系统的查询优化9.3代数优化9.4物理优化9.3小结AnIntroductiontoDatabaseSystem第九章关系系统及其查询优化9.1关系数据库系统的查询处589.4 物理优化1、基于规则的启发式优化2、基于代价估算的优化3、两者结合的优化方法AnIntroductiontoDatabaseSystem9.4物理优化1、基于规则的启发式优化AnIntrodu599.4 物理优化9.4.1基于启发式规则的存取路径选择优化9.4.2基于代价的优化AnIntroductiontoDatabaseSystem9.4物理优化9.4.1基于启发式规则的存取路径选择优化609.4.1基于启发式规则的存取路径选择优化一、选择操作的启发式规则二、连接操作的启发式规则P273AnIntroductiontoDatabaseSystem9.4.1基于启发式规则的存取路径选择优化AnIntrod619.4 物理优化9.4.1基于启发式规则的存取路径选择优化9.4.2基于代价的优化AnIntroductiontoDatabaseSystem9.4物理优化9.4.1基于启发式规则的存取路径选择优化629.4.2 基于代价的优化p统计信息n表的元组数、元组长度、占用的块数等等n属性列不同值的个数、选择率，最大最小值n索引的层数、不同索引值的个数、索引叶结点数等p代价估算示例n全表扫描算法的代价估算公式n索引扫描算法的代价估算公式n嵌套循环连接算法的代价估算公式n排序-合并连接算法的代价估算公式AnIntroductiontoDatabaseSystem9.4.2基于代价的优化统计信息AnIntroducti63第九章 关系系统及其查询优化9.1关系数据库系统的查询处理9.2关系数据库系统的查询优化9.3代数优化9.4物理优化9.3小结AnIntroductiontoDatabaseSystem第九章关系系统及其查询优化9.1关系数据库系统的查询处649.3 小结p查询处理是核心、优化是关键p查询优化的必要性p代数优化及其启发式规则（查询树）p物理优化（存取路径启发式规则、代价优化）AnIntroductiontoDatabaseSystem9.3小结查询处理是核心、优化是关键AnIntroduc65 下课了。休息一会儿。休息一会儿。AnIntroductiontoDatabaseSystem下课了。休息一会儿。攀AnIntro66