第4章关系系统及其查询优化n.pptVIP

课后练习 学生课程数据库有以下查询 Select cname from student,course,sc where student.sno=sc.sno and sc.cno=course.cno and student.sdept=‘IS’ 画出该查询优化后的标准语法树。 * 物理优化(*) 1.基于启发式规则的存取路径选择优化 一、 选择操作的启发式规则 二、 连接操作的启发式规则 2. 基于代价的优化 4.2关系系统的查询优化 * 基于启发式规则的存取路径选择优化(续) 一、 选择操作的启发式规则: 1. 对于小关系，使用全表顺序扫描，即使选择列上有索引 对于大关系，启发式规则有： 2. 对于选择条件是主码＝值的查询 查询结果最多是一个元组，可以选择主码索引 一般的RDBMS会自动建立主码索引。 * 3. 对于选择条件是非主属性＝值的查询，并且选择列上有索引 要估算查询结果的元组数目 如果比例较小(10%)可以使用索引扫描方法 否则还是使用全表顺序扫描 4. 对于选择条件是属性上的非等值查询或者范围查询，并且选择列上有索引 要估算查询结果的元组数目 如果比例较小(10%)可以使用索引扫描方法 否则还是使用全表顺序扫描 基于启发式规则的存取路径选择优化(续) * 5. 对于用AND连接的合取选择条件 如果有涉及这些属性的组合索引 优先采用组合索引扫描方法 如果某些属性上有一般的索引 则用索引扫描方法 否则使用全表顺序扫描。 6. 对于用OR连接的析取选择条件，一般使用全表顺序扫描 基于启发式规则的存取路径选择优化(续) * 基于启发式规则的存取路径选择优化(续) 二、 连接操作的启发式规则： 1. 如果2个表都已经按照连接属性排序 选用排序-合并方法 2. 如果一个表在连接属性上有索引 选用索引连接方法 3. 如果上面2个规则都不适用，其中一个表较小 选用Hash join方法 * 4. 可以选用嵌套循环方法，并选择其中较小的表，确切地讲是占用的块数(b)较少的表，作为外表(外循环的表) 。 理由： 设连接表R与S分别占用的块数为Br与Bs 连接操作使用的内存缓冲区块数为K 分配K-1块给外表 如果R为外表，则嵌套循环法存取的块数为Br+( Br/K-1)Bs,Br,Br越小越好 显然应该选块数小的表作为外表 基于启发式规则的存取路径选择优化(续) * 基于代价的优化 启发式规则优化是定性的选择，适合解释执行的系统 解释执行的系统，优化开销包含在查询总开销之中 编译执行的系统中查询优化和查询执行是分开的 可以采用精细复杂一些的基于代价的优化方法 * 基于代价的优化（续） 基于代价的优化方法要计算各种操作算法的执行代价，与数据库的状态密切相关 数据字典中存储的优化器需要的统计信息： 1. 对每个基本表 该表的元组总数(N) 元组长度(l) 占用的块数(B) * 基于代价的优化（续） 2. 对基表的每个列 该列不同值的个数(m) 选择率(f) 如果不同值的分布是均匀的，f＝1/m 如果不同值的分布不均匀，则每个值的选择率＝具有该值的元组数/N 该列最大值 该列最小值 该列上是否已经建立了索引 索引类型(B树索引、Hash索引、聚集索引) * 基于代价的优化（续） 1.代价估算示例，以全表扫描算法的代价估算公式 如果基本表大小为B块，全表扫描算法的代价 cost＝B 如果选择条件是码＝值，那么平均搜索代价 cost＝B/2 * 基于代价的优化（续） 2. 索引扫描算法的代价估算公式 如果选择条件是码＝值 则采用该表的主索引，若为B+树，层数为L，需要存取B+树中从根结点到叶结点L块，再加上基本表中该元组所在的那一块，所以cost=L+1 如果选择条件涉及非码属性 若为B+树索引，选择条件是相等比较，S是索引的选择基数(有S个元组满足条件) 最坏的情况下，满足条件的元组可能会保存在不同的块上，此时，cost=L+S * 基于代价的优化（续） 如果比较条件是＞，＞＝，＜，＜＝操作 假设有一半的元组满足条件就要存取一半的叶结点 通过索引访问一半的表存储块cost=L+Y/2+B/2 (B为基表所占块数) 如果可以获得更准确的选择基数，可以进一步修正Y/2与B/2 * 小 结 查询处理是RDBMS的核心，查询优化技术是查询处理的关键技术 本章讲解的优化方法 启发式代数优化 基于规则的存取路径优化 基于代价的优化 本章的目的：希望读者掌握查询优化方法的概念和技术 * 小 结（续） 比较复杂的查询，尤其是涉及连接和嵌套的查询 不要把优化的任务全部放在RDBMS上 应该找出RDBMS的优化规律，以写出适合RDBMS自动优化的SQL语句