北航数据库原理与技术第九章new

第九章查询优化查询处理概述(1)关系操作是非过程化的，其存取路径对用户透明。用户只需 说明 关于失联党员情况说明岗位说明总经理岗位说明书会计岗位说明书行政主管岗位说明书 “干什么”，不必指出“怎么干”。输入：SQL语句输出：操作的结果查询处理概述(2)对于关系数据库系统，查询优化是：挑战：必须进行好的优化，才有可接受的性能机会：关系 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 达式的语义层次高，提供了优化的可能性。查询处理概述(3)相对于由用户选择存取路径的方式：降低了对用户的要求，方便了用户的使用。避免了因用户选择了错误的存取路径而导致的效率低下。能够取得更好的优化效果，因为优化器具有丰富的可使用的信息当数据库发生变化时优化器容易再次进行优化优化器能够对多种实现策略逐一进行考虑优化器集中了最优秀的程序员的智慧和经验查询处理概述(4)查询处理的基本步骤：语法分析与翻译优化执行查询语句查询处理概述北京航空航天大学软件开发环境重点实验室查询语句查询分析查询检查关系代数表达式（查询树）查询优化数据字典统计数据执行 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载 数据数据执行引擎……查询结果查询优化的步骤将查询转换成某种内部表示，通常是语法树。根据一定的变换规则，把语法树转换为优化形式。选择低层的操作算法。生成查询执行计划（也称查询执行方案，是由一系列内部操作构成的）。提交的SQL语句SELECTNAMEFROMStudentsJOINEnrollmentOnStudents.SSN=Enrollment.SSNJOINCoursesOnEnrollment.Course_no=Courses.Course_no北京航空航天大学软件开发环境重点实验室对应的关系代数表达式∏Name(GPA>=3.5andTitle=‘AdaProgrammingLanguage’andStudents.SSN=Enrollment.SSNandEnrollment.Course_no=Course.Course_no(Students×Enrollment×Courses))北京航空航天大学软件开发环境重点实验室语法树北京航空航天大学软件开发环境重点实验室SQLServer中的查询执行计划北京航空航天大学软件开发环境重点实验室基本运算的实现每一基本的代数运算都有多种不同的实现算法。适用于不同的情况等值条件，范围条件数据是聚集的，数据是非聚集的相关属性上有索引，相关属性上没有索引执行代价不同选取运算的实现算法（1）全表扫描 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 ：依次访问表的每一个块，对于每一个元组，测试它是否满足选择条件。效率低，但对关系的存储方式没有要求，不需要索引。适用于任何选择条件。折半扫描:对于按某一属性排序的文件，且选择条件是该属性上的等值比较方法，可以使用折半的方法扫描文件。效率高，但需要有序文件选取运算的实现算法（2）索引扫描：对于在选择条件的属性上建有索引的表，可以采用访问索引，根据索引项的指示去访问数据元组的方法。无序索引：访问满足等值条件的元组有序索引：访问满足范围查找条件的一系列元组。连接操作的实现嵌套循环方法排序-合并方法索引连接方法HASHjoin方法北京航空航天大学软件开发环境重点实验室查询代价的度量查询代价：查询处理对各种资源的使用情况总代价=I/O代价+CPU代价+通信开销I/O代价的度量方式：I/O块数或者I/O的次数查询代价的度量一个重要的影响因素：主存中缓冲区的大小M最好的情形，所有的数据可以读入到缓冲区中最坏的情形，缓冲区只能容纳数目不多的数据块——大约每个关系一块。查询优化的必要性例：求选修了课程Ｃ2的学生姓名SELECTStudent.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno='2';查询优化的必要性查询优化的必要性假设:Student表中有1000条学生 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 :nstudent=1000SC表中有10000条选课记录:nsc=10000其中选修2号课程的选课记录为50条:SC(cno,SC)=50一个块可以装10个Student元组或100个SC元组：fstudent=10，fSC=100Student表占用的块:bstudent=100SC表占用的块:bSC=100查询优化的必要性一个块可以装10个Student和SC的连接结果元组：fjoin=10缓冲:内存中一次可以存放5块Student元组、1块SC元组和若干块连接结果元组读写速度：20块/秒查询优化的必要性（5）读数据时间=2100/20=105秒查询优化的必要性查询优化的必要性查询优化的必要性表达式的等价性两个表达式等价：产生的结果关系具有相同的属性集和相同的元组集。关系代数等价变换规则（1）所谓关系代数表达式的等价是指用相同的关系代替两个表达式中相应的关系所得到的结果是相同的。上面的优化策略大部分都涉及到代数表达式的变换。关系代数等价变换规则（2）关系代数等价变换规则（3）关系代数等价变换规则（4）关系代数等价变换规则（5）关系代数等价变换规则（6）关系代数等价变换规则（7）关系代数等价变换规则（8）关系代数等价变换规则（9）关系代数等价变换规则（10）变换规则小结1-2:连接、笛卡尔积的交换律、结合律3：合并或分解投影运算4：合并或分解选择运算5-8：选择运算与其他运算交换5，9，10：投影运算与其他运算交换查询优化的一般准则选择运算应尽可能先做。目的：减小中间关系。在执行连接操作前对文件适当进行预处理排序在连接属性上建立索引投影运算和选择运算同时做。目的：避免重复扫描关系。把投影运算与其前面或后面的双目运算结合起来。目的：减少扫描关系的遍数。查询优化的一般准则某些选择运算＋在其前面执行的笛卡尔积—>连接运算找出公共子表达式查询树查询树---关系代数表达式的树形表示.输入关系查询树的叶节点关系操作内部节点从底向上执行例子（1）一个未优化的关系代数表达式例子（2）初始查询树例子（3）规则1：尽可能早地进行选取操作例子（4）规则2：使用连接操作替代笛卡尔积例子（5）规则3：首先执行产生较小结果集的连接例子（6）规则4：将没有用的属性利用投影操作去掉性能优化数据库性能是由多个因素所构成的：正确性（事务完整性和数据完整性）可用性响应时间性能优化的工作是从项目的第一天就开始的。对数据库性能影响最大的是数据库的设计和开发。通常，所谓的性能优化实际上就是重新开发数据库系统中设计的很糟的那一部分。优化准则花费尽可能多的努力来设计数据库模式，所有的优化都要基于数据库模式。集中精力优化运行最频繁的代码，而不是那些运行最慢的代码。在升级硬件之前进行优化。即使在速度快的服务器上，坏代码仍旧是坏代码。优化准则列出所有可能的优化思路，即使你没有时间在现在去实施它们。新特性会与优化竞争资源；因此，最好的办法是开发一个维持现有功能，但提高了性能的版本。优化是一个研究与探索的过程，你很难对它做出预测。所以，最好不要对优化的效果或交付日期做出承诺。优化准则以索引的形式列出系统中那些易于修改和优化的部分。集中精力修改应用程序中性能最坏的部分。花费一些时间作为用户使用应用程序。去使用该程序的部门工作上一个星期，这将会使你产生很多有价值的灵感。负载测试数据负载测试只有当数据量超出了服务器内存容量好几倍的时候，才能够对数据库模式和查询进行负载测试。用户负载测试只有当使用大量的用户来测试数据库时才有可能发生锁争用，而锁争用会导致严重的性能问题。负载测试清除测试的影响数据库已经经过优化，它可以智能地将数据缓存在内存中，而这将会影响到后续测试的结果。因此，测试前要刷新内存，这可以通过停止并重新启动服务器实现。影响性能的因素规范化的数据库物理模式设计完善的和平衡的索引策略使用基于集合的查询方式编码，并避免以过程化（基于行的方式）来操作数据使用数据库约束和触发器来实施业务规则精心地设计表、索引和代码以避免锁争用数据库设计与性能将数据库规范化到第三范式，随后精心地实施使用高性能的、单列主键的物理设计。不要过度地规范化数据库，或者说使数据库过度复杂化，而应当坚持不懈地努力，直至找到简单而优雅的数据库设计为止。避免要以事务的方式在表和表之间来回倒数据的数据库设计。数据库设计与性能如果要使用代码来创建多个临时表或者额外的工作表，那就说明数据库的设计是不充分的。在设计数据库模式的时候，必须考虑那些基于它的查询。必要的时候，要勇敢地将数据从OLTP表复制到非规范化的、只读的表中去，以便加快数据库的读速度。约束和触发器要在数据库级实施规则，以便能够快速地执行这些规则，并保证在任何情况下都无法避开这些规则的检查。用数据库约束来实施数据库规则和业务规则，对于那些无法用数据库约束来实施的规则，再使用触发器来实施。触发器必须使用基于集合的DML语句，而不要使用游标。因为每一个insert、update或者delete操作都会触发触发器，所以在优化触发器的代码时应当加倍地努力。查询设计和性能争取重用查询执行计划。只使用视图支持复杂的用户查询，除此之外从不在代码中使用视图。使用子查询将庞大而复杂的查询分解为多个更小的逻辑单元。哪些查询不能利用索引使用AND连接到一起的多个条件可以利用索引，使用OR连接在一起的多个条件则不能。否定的搜索条件（<>、!>、!<、NotExists、NotIn、NotLike）是不可优化的。因为证明一行是存在的很容易，可是如果要证明一行是不存在的，就需要检查每一行。由通配符开始的条件是不能使用索引的。使用了表达式的条件也不能使用索引。如果where子句包含了函数（例如字符串函数），就需要使用表扫描，以便可以使用函数来测试每个行中的数据。均衡的索引策略基础索引将每一个主键都作为非聚集索引来创建。这是因为主键通常用于单行检索。为每个表创建一个聚集索引。对于主表，应当在那些最常用来排序的列上建立聚集索引。但应当注意不要在主键上建立聚集索引。对于从表，应当为最重要的外部键创建聚集索引。除了在第二步中已经为其创建了索引的外部键以外，对于每个外部键中的那些列创建非聚集索引。对于where子句或者orderby子句中所引用的每个列创建单列索引。索引调优索引字段应当包含足够多的不同的值表较大，但大多数的查询只会查找其中2％～4％的记录行并非索引越多，效率就会越好平衡查询和DML操作的要求创建复合索引时，应注意索引字段的顺序。使用复合索引的一种好方法是：通过在复合索引中包含所有需要的列来消除数据页读操作。注意消除重复索引。加锁和性能检查事务的隔离级别，并确保它没有高于所需要的级别。确保快速地启动和提交事务。重新设计所有包含游标的事务。如果两个程序有可能会死锁，应当确保它们按照同样的顺序对资源进行锁定。确保客户应用程序在获取数据之后立即释放所有的锁。