中国石油大学华东数据库课件.ppt

1、中国石油大学计算机科学系中国石油大学计算机科学系数据库原理数据库原理第四章第四章 关系系统及其查询优化关系系统及其查询优化第四章第四章 关系系统及其查询优化关系系统及其查询优化4.1关系系统4.2关系系统的查询处理4.3关系系统的查询优化4.4小结关系系统与关系模型关系系统与关系模型n关系数据结构n域及域上定义的关系n关系操作n并、交、差、广义笛卡尔积、选择、投影、连接、除等n关系完整性n实体完整性、参照完整性、用户自己定义的完整性关系系统关系系统n能够在一定程度上支持关系模型的数据库管理系统是关系系统。n由于关系模型中并非每一部分都是同等重要的n所以并不苛求一个实际的关系系统必须完全支持关系

2、模型。关系系统的定义关系系统的定义 一个数据库管理系统可定义为关系系统，当且仅当它至少支持：1.关系数据库（即关系数据结构）系统中只有表这种结构2.支持选择、投影和（自然）连接运算对这些运算不要求用户定义任何物理存取路径对关系系统的最低要求关系系统的定义关系系统的定义 不支持关系数据结构的系统显然不能称为关系系统仅支持关系数据结构，但没有选择、投影和连接运算功能的系统仍不能算作关系系统。n原因：不能提高用户的生产率n支持选择、投影和连接运算，但要求定义物理存取路径，这种系统也不能算作真正的关系系统n原因：就降低或丧失了数据的物理独立性n选择、投影、连接运算是最有用的运算关系系统的分类关系系统的

3、分类 n分类依据：支持关系模型的程度n分类表式系统：支持关系数据结构(即表)(最小)关系系统支持：关系数据结构选择、投影、连接关系操作关系完备的系统支持：关系数据结构所有的关系代数操作全关系系统支持：关系模型的所有特征特别是:数据结构中域的概念关系系统的分类关系系统的分类（续）（续）数据结构数据结构数据操作数据操作完整性完整性表式系统表式系统表表 (最小最小)关系系统关系系统表表选选择择、投投影影、连接连接 关系完备的系统关系完备的系统表表 全关系系统全关系系统 第四章第四章 关系系统及其查询优化关系系统及其查询优化4.1关系系统4.2关系系统的查询处理4.3关系系统的查询优化4.4小结4.2

4、.1 查询处理步骤查询处理步骤nRDBMS查询处理分为四个阶段：n1.查询分析n2.查询检查n3.查询优化n4.查询执行查询处理步骤（续）查询处理步骤（续）1.查询分析查询分析n对查询语句进行扫描、词法分析和语法分析n从查询语句中识别出语言符号n进行语法检查和语法分析2.查询检查查询检查n根据数据字典对合法的查询语句进行语义检查n根据数据字典中的用户权限和完整性约束定义对用户的存取权限进行检查n检查通过后把SQL查询语句转换成等价的关系代数表达式nRDBMS一般都用查询树(语法分析树)来表示扩展的关系代数表达式n把数据库对象的外部名称转换为内部表示3.查询优化查询优化n查询优化：选择一个高效执

5、行的查询处理策略n查询优化分类：n代数优化：指关系代数表达式的优化n物理优化：指存取路径和底层操作算法的选择n查询优化方法选择的依据：n基于规则(rulebased)n基于代价(costbased)n基于语义(semanticbased)4.查询执行查询执行n依据优化器得到的执行策略生成查询计划n代码生成器(codegenerator)生成执行查询计划的代码4.2.2 实现查询操作的算法示例实现查询操作的算法示例n一、选择操作的实现n二、连接操作的实现一、选择操作的实现一、选择操作的实现例1Select*fromstudentwhere；考虑的几种情况：C1：无条件；C2：Sno95001；C

6、3：Sage20；C4：SdeptCSANDSage20；选择操作的实现选择操作的实现(续续)选择操作典型实现方法：选择操作典型实现方法：n1.简单的全表扫描方法n对查询的基本表顺序扫描，逐一检查每个元组是否满足选择条件，把满足条件的元组作为结果输出n适合小表，不适合大表n2.索引(或散列)扫描方法n适合选择条件中的属性上有索引(例如B+树索引或Hash索引)n通过索引先找到满足条件的元组主码或元组指针，再通过元组指针直接在查询的基本表中找到元组选择操作的实现选择操作的实现(续续)n例1-C2以C2为例，Sno95001，并且Sno上有索引(或Sno是散列码)n使用索引(或散列)得到Sno为9

7、5001元组的指针n通过元组指针在student表中检索到该学生n例1-C3以C3为例，Sage20，并且Sage上有B+树索引n使用B+树索引找到Sage20的索引项，以此为入口点在B+树的顺序集上得到Sage20的所有元组指针n通过这些元组指针到student表中检索到所有年龄大于20的学生。选择操作的实现选择操作的实现(续续)例1-C4以C4为例，SdeptCSANDSage20，如果Sdept和Sage上都有索引：n算法一：分别用上面两种方法分别找到SdeptCS的一组元组指针和Sage20的另一组元组指针n求这2组指针的交集n到student表中检索n得到计算机系年龄大于20的学生n

8、算法二：找到SdeptCS的一组元组指针n通过这些元组指针到student表中检索n对得到的元组检查另一些选择条件(如Sage20)是否满足n把满足条件的元组作为结果输出。二、连接操作的实现二、连接操作的实现n连接操作是查询处理中最耗时的操作之一n本节只讨论等值连接(或自然连接)最常用的实现算法n例2SELECT*FROMStudent，SCWHEREStudent.Sno=SC.Sno；连接操作的实现（续）连接操作的实现（续）n1.嵌套循环方法(nestedloop)n2.排序-合并方法(sort-mergejoin或mergejoin)n3.索引连接(indexjoin)方法n4.Hash

9、Join方法连接操作的实现（续）连接操作的实现（续）1.嵌套循环方法(nestedloop)n对外层循环(Student)的每一个元组(s)，检索内层循环(SC)中的每一个元组(sc)n检查这两个元组在连接属性(sno)上是否相等n如果满足连接条件，则串接后作为结果输出，直到外层循环表中的元组处理完为止连接操作的实现（续）连接操作的实现（续）2.排序-合并方法(sort-merge join 或merge join)n适合连接的诸表已经排好序的情况n排序合并连接方法的步骤：n如果连接的表没有排好序，先对Student表和SC表按连接属性Sno排序n取Student表中第一个Sno，依次扫描SC

10、表中具有相同Sno的元组，把它们连接起来连接操作的实现（续）连接操作的实现（续）n排序合并连接方法的步骤（续）：n当扫描到Sno不相同的第一个SC元组时，返回Student表扫描它的下一个元组，再扫描SC表中具有相同Sno的元组，把它们连接起来n重复上述步骤直到Student表扫描完连接操作的实现（续）连接操作的实现（续）nStudent表和SC表都只要扫描一遍n如果2个表原来无序，执行时间要加上对两个表的排序时间n对于2个大表，先排序后使用sort-mergejoin方法执行连接，总的时间一般仍会大大减少连接操作的实现（续）连接操作的实现（续）3.索引连接(index join)方法n步骤：

11、在SC表上建立属性Sno的索引，如果原来没有该索引对Student中每一个元组，由Sno值通过SC的索引查找相应的SC元组把这些SC元组和Student元组连接起来循环执行，直到Student表中的元组处理完为止连接操作的实现（续）连接操作的实现（续）4.Hash Join方法n把连接属性作为hash码，用同一个hash函数把R和S中的元组散列到同一个hash文件中n步骤：n划分阶段(partitioningphase)：对包含较少元组的表(比如R)进行一遍处理把它的元组按hash函数分散到hash表的桶中n试探阶段(probingphase)：也称为连接阶段(joinphase)对另一个表(

12、S)进行一遍处理把S的元组散列到适当的hash桶中把元组与桶中所有来自R并与之相匹配的元组连接起来连接操作的实现（续）连接操作的实现（续）n上面hashjoin算法前提：假设两个表中较小的表在第一阶段后可以完全放入内存的hash桶中n以上的算法思想可以推广到更加一般的多个表的连接算法上第四章第四章 关系系统及其查询优化关系系统及其查询优化4.1关系系统4.2关系系统的查询处理4.3关系系统的查询优化4.4小结4.3 关系系统的查询优化关系系统的查询优化 4.3.1查询优化概述4.3.2查询优化的必要性4.3.3关系代数等价变换规则4.3.4查询树的启发式优化4.3.5物理优化4.3.1 查询优

13、化概述查询优化概述n查询优化的必要性n查询优化极大地影响RDBMS的性能。n查询优化的可能性n关系数据语言的级别很高，使DBMS可以从关系表达式中分析查询语义。由由DBMS进行查询优化的好处进行查询优化的好处n用户不必考虑如何最好地表达查询以获得较好的效率n系统可以比用户程序的优化做得更好(1)优化器可以从数据字典中获取许多统计信息，而用户程序则难以获得这些信息由由DBMS进行查询优化的好处进行查询优化的好处(2)如果数据库的物理统计信息改变了，系统可以自动对查询重新优化以选择相适应的执行计划。在非关系系统中必须重写程序，而重写程序在实际应用中往往是不太可能的。(3)优化器可以考虑数百种不同的

14、执行计划，而程序员一般只能考虑有限的几种可能性。(4)优化器中包括了很多复杂的优化技术，这些优化技术往往只有最好的程序员才能掌握。系统的自动优化相当于使得所有人都拥有这些优化技术代价模型代价模型RDBMS通过某种代价模型计算出各种查询执行策略的执行代价，然后选取代价最小的执行方案n集中式数据库n单用户系统总代价=I/O代价+CPU代价n多用户系统总代价=I/O代价+CPU代价+内存代价n分布式数据库总代价=I/O代价+CPU代价+内存代价+通信代价查询优化目标查询优化目标查询优化的总目标n选择有效策略n求得给定关系表达式的值n使得查询代价最小(实际上是较小)实际系统的查询优化步骤实际系统的查询

15、优化步骤1.将查询转换成某种内部表示，通常是语法树2.根据一定的等价变换规则把语法树转换成标准(优化)形式3.选择低层的操作算法对于语法树中的每一个操作n计算各种执行算法的执行代价n选择代价小的执行算法4.生成查询计划(查询执行方案)n查询计划是由一系列内部操作组成的。4.3.2 查询优化的必要性查询优化的必要性 例：求选修了2号课程的学生姓名SELECTStudent.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=2;查询优化的必要性（续）查询优化的必要性（续）假设1：外存：Student:1000条,SC:10000条,选修2号课程

16、:50条假设2：一个内存块装元组:10个Student,或100个SC,内存中一次可以存放:5块Student元组,1块SC元组和若干块连接结果元组假设3：读写速度：20块/秒假设4：连接方法：基于数据块的嵌套循环法执行策略执行策略11.Q1name(Student.Sno=SC.SnoSC.Cno=2(StudentSC)StudentSC读取总块数=读Student表块数+读SC表遍数*每遍块数=1000/10+(1000/(105)(10000/100)=100+20100=2100块读数据时间=2100/20=105秒不同的执行策略不同的执行策略,考虑考虑I/O时间时间中间结果大小=1000*10000=107(1千万条元组)写中间结果时间=10000000/10/20=50000秒读数据时间=50000秒总时间=1055000050000秒=100105秒=27.8小时查询优化的必要性（续）查询优化的必要性（续）2.2name(SC.Cno=2(StudentSC)读取总块数=2100块读数据时间=2100/20=105秒中间结果大小=10000（减少1000倍）写中间结果时