数据管理基础-ch58-61

ch 58 查询处理

查询处理步骤

查询分析

• 查询分析的任务：对查询语句进行扫描、词法分析和语法分析
  • 词法分析：从查询语句中识别出正确的语言符号
  • 语法分析：进行语法检查

查询检查

**查询检查 1 **

• 查询检查的任务
  • 合法权检查
  • 视图转换
  • 安全性检查
  • 完整性初步检查
• 根据数据字典中有关的模式定义检查语句中的数据库对象，如关系名、属性名是否存在和有效
• 如果是对视图的操作，则要用视图消解方法把对视图的操作转换成对基本表的操作

查询检查 2

• 根据数据字典中的用户权限和完整性约束定义对用户的存取权限进行检查
• 检查通过后把SQL查询语句转换成内部表示，即等价的关系代数表达式。
• 关系数据库管理系统一般都用查询树，也称为语法分析树来表示扩展的关系代数表达式。

查询优化

• 查询优化：选择一个高效执行的查询处理策略
• 查询优化分类
  • 代数优化/逻辑优化：指关系代数表达式的优化
  • 物理优化：指存取路径和底层操作算法的选择
• 查询优化的选择依据
  • 基于规则(rule based)
  • 基于代价(cost based)
  • 基于语义(semantic based)

查询执行

• 依据优化器得到的执行策略生成查询执行计划
• 代码生成器(code generator)生成执行查询计划的代码
• 两种执行方法
  • 自顶向下
  • 自底向上

选择操作的实现

选择操作的实现 1

• 选择操作典型实现方法：
  • 全表扫描方法 (Table Scan)
    • 对查询的基本表顺序扫描，逐一检查每个元组是否满足
    • 选择条件，把满足条件的元组作为结果输出
    • 适合小表，不适合大表
  • 索引扫描方法 (Index Scan)
    • 适合于选择条件中的属性上有索引(例如B+树索引或Hash索引)
    • 通过索引先找到满足条件的元组主码或元组指针，再通过元组指针直接在查询的基本表中找到元组

选择操作的实现 2

选择操作的实现 3

• 全表扫描算法
  • 假设可以使用的内存为M块，全表扫描算法思想：
    1. 按照物理次序读Student的M块到内存
    2. 检查内存的每个元组t，如果满足选择条件，则输出t
    3. 如果student还有其他块未被处理，重复①和②

选择操作的实现 4

• 索引扫描算法

选择操作的实现 5

选择操作的实现 6

连接操作的实现

• 连接操作是查询处理中最耗时的操作之一
• 本节只讨论等值连接(或自然连接)最常用的实现算法
  • 嵌套循环算法(nested loop join)
  • 排序-合并算法(sort-merge join 或merge join)
  • 索引连接(index join)算法
  • Hash Join算法

嵌套循环算法

• 嵌套循环算法(nested loop join)
  • 对外层循环(Student表)的每一个元组(s)，检索内层循环(SC表)中的每一个元组(sc)
  • 检查这两个元组在连接属性(Sno)上是否相等
  • 如果满足连接条件，则串接后作为结果输出，直到外层循环表中的元组处理完为止

排序-合并算法

排序-合并算法 1

• 排序-合并算法(sort-merge join 或merge join)
  • 如果连接的表没有排好序，先对Student表和SC表按连接属性Sno排序
  • 取Student表中第一个Sno，依次扫描SC表中具有相同Sno的元组
  • 当扫描到Sno不相同的第一个SC元组时，返回Student表扫描它的下一个元组，再扫描SC表中具有相同Sno的元组，把它们连接起来
  • 重复上述步骤直到Student 表扫描完
• Student表和SC表都只要扫描一遍
• 如果两个表原来无序，执行时间要加上对两个表的排序时间
• 对于大表，先排序后使用排序-合并连接算法执行连接，总的时间一般仍会减少

排序-合并算法 2

索引连接算法

• 索引连接(index join)算法
  • 步骤：
  • 在SC表上已经建立属性Sno的索引。
  • 对Student中每一个元组，由Sno值通过SC的索引查找相应的SC元组。
  • 把这些SC元组和Student元组连接起来
  • 循环执行②③，直到Student表中的元组处理完为止

Hash Join算法

• Hash Join算法
  • 把连接属性作为hash码，用同一个hash函数把Student表和SC表中的元组散列到hash表中。
  • 划分阶段(building phase, 也称为partitioning phase)
    • 对包含较少元组的表(如Student表)进行一遍处理
    • 把它的元组按hash函数分散到hash表的桶中
  • 试探阶段(probing phase,也称为连接阶段join phase)
    • 对另一个表(SC表)进行一遍处理
    • 把SC表的元组也按同一个hash函数（hash码是连接属性）进行散列
    • 把SC元组与桶中来自Student表并与之相匹配的元组连接起来
• hash join算法前提：假设两个表中较小的表在第一阶段后可以完全放入内存的hash桶中

ch 59 查询优化

查询优化概述

查询优化概述 1

• 关系系统的查询优化
  • 是关系数据库管理系统实现的关键技术又是关系系统的优点所在
  • 减轻了用户选择存取路径的负担
• 关系查询优化是影响关系数据库管理系统性能的关键因素
• 由于关系表达式的语义级别很高，使关系系统可以从关系表达式中分析查询语义，提供了执行查询优化的可能性

查询优化概述 2

• 非关系系统
  • 用户使用过程化的语言表达查询要求，执行何种记录级的操作，以及操作的序列是由用户来决定的
  • 用户必须了解存取路径，系统要提供用户选择存取路径的手段，查询效率由用户的存取策略决定
  • 如果用户做了不当的选择，系统是无法对此加以改进的

查询优化概述 3

• 查询优化的优点
  • 用户不必考虑如何最好地表达查询以获得较好的效率
  • 系统可以比用户程序的“优化”做得更好
    • 优化器可以从数据字典中获取许多统计信息，而用户程序则难以获得这些信息。
    • 如果数据库的物理统计信息改变了，系统可以自动对查询重新优化以选择相适应的执行计划。在非关系系统中必须重写程序，而重写程序在实际应用中往往是不太可能的。
    • 优化器可以考虑数百种不同的执行计划，程序员一般只能考虑有限的几种可能性。
    • 优化器中包括了很多复杂的优化技术，这些优化技术往往只有最好的程序员才能掌握。系统的自动优化相当于使得所有人都拥有这些优化技术。

查询优化的总目标

• 关系数据库管理系统通过某种代价模型计算出各种查询执行策略的执行代价，然后选取代价最小的执行方案
  • 集中式数据库
    • 执行开销主要包括：磁盘存取块数(I/O代价)+处理机时间(CPU代价)+查询的内存开销
    • I/O代价是最主要的
  • 分布式数据库
    • 总代价=I/O代价+CPU代价+内存代价＋通信代价
• 查询优化的总目标
  • 选择有效的策略
  • 求得给定关系表达式的值
  • 使得查询代价最小(实际上是较小)
• 一个关系查询可以对应不同的执行方案，其效率可能相差非常大。

查询优化的实例

方案A

方案A 1

$Q1=\pi Sname(\sigma Student.Sno=SC.Sno\wedge SC.Cno{=}^{\prime }{2}^{\prime }(Student\times SC))$

方案A 2

方案B

方案C

拥有索引的实例

优化实例

ch 60 代数优化

关系代数表达式等价变换规则

• 代数优化策略：通过对关系代数表达式的等价变换来提高查询效率
• 关系代数表达式的等价：指用相同的关系代替两个表达式中相应的关系所得到的结果是相同的
• 两个关系表达式E1和E2是等价的，可记为E1≡E2

常用的等价变换规则

常用的等价变换规则 1

常用的等价变换规则 2

常用的等价变换规则 3

常用的等价变换规则 4

典型的启发式规则

• 笛卡尔积的使用比较少

查询树的启发式优化

查询树的启发式优化 1

• 遵循这些启发式规则，应用等价变换公式来优化关系表达式的算法

  

查询树的启发式优化 2

查询树的启发式优化 3

• 把上述得到的语法树的内节点分组。

一个例子

一个例子 1

• **[例9.4]**下面给出[例9.3]中 SQL语句的代数优化示例
  把SQL语句转换成查询树，如下图所示

一个例子 2

• 为了使用关系代数表达式的优化法，假设内部表示是关系代数语法树，则上面的查询树如图9.4所示

一个例子 3

• 对查询树进行优化
  • 利用规则4、6把选择${\sigma }_{SC.Cno{=}^{\prime }{2}^{\prime }}$移到叶端，图9.4查询树便转换成下图优化的查询树。这就是9.2.2节中Q3的查询树表示。

ch 61 物理优化

物理优化

• 代数优化改变查询语句中操作的次序和组合，不涉及底层的存取路径

• 对于一个查询语句有许多存取方案，它们的执行效率不同， 仅仅进行代数优化是不够的

• 物理优化就是要选择高效合理的操作算法或存取路径，求得优化的查询计划

• 物理优化方法

  • 基于规则的启发式优化
    • 启发式规则是指那些在大多数情况下都适用，但不是在每种情况下都是最好的规则。
  • 基于代价估算的优化
    • 优化器估算不同执行策略的代价，并选出具有最小代价的执行计划。
  • 两者结合的优化方法：
    • 常常先使用启发式规则，选取若干较优的候选方案，减少代价估算的工作量
    • 然后分别计算这些候选方案的执行代价，较快地选出最终的优化方案

选择操作的启发式规则

• 对于小关系，使用全表顺序扫描，即使选择列上有索引
• 对于大关系，启发式规则有：

1. 对于选择条件是“主码＝值”的查询
   • 查询结果最多是一个元组，可以选择主码索引
   • 一般的关系数据库管理系统会自动建立主码索引
2. 对于选择条件是“非主属性＝值”的查询，并且选择列上有索引
   • 要估算查询结果的元组数目
     • 如果比例较小(<10%)可以使用索引扫描方法
     • 否则还是使用全表顺序扫描
3. 对于选择条件是属性上的非等值查询或者范围查询，并且选择列上有索引
   • 要估算查询结果的元组数目
     • 如果比例较小(<10%)可以使用索引扫描方法
     • 否则还是使用全表顺序扫描
4. 对于用AND连接的合取选择条件
   • 如果有涉及这些属性的组合索引
     • 优先采用组合索引扫描方法
   • 如果某些属性上有一般的索引，可以用索引扫描方法
     • 通过分别查找满足每个条件的指针，求指针的交集
     • 通过索引查找满足部分条件的元组，然后在扫描这些元组时判断是否满足剩余条件
   • 其他情况：使用全表顺序扫描
5. 对于用OR连接的析取选择条件，一般使用全表顺序扫描

连接操作的启发式规则

• 如果2个表都已经按照连接属性排序
  • 选用排序-合并算法
• 如果一个表在连接属性上有索引
  • 选用索引连接算法
• 如果上面2个规则都不适用，其中一个表较小
  • 选用hash join算法
• 可以选用嵌套循环方法，并选择其中较小的表，确切地讲是占用的块数(B)较少的表，作为外表(外循环的表) 。
  • 理由：
  • 设连接表R与S分别占用的块数为Br与Bs，连接操作使用的内存缓冲区块数为K，分配K-1块给外表，如果R为外表，则嵌套循环法存取的块数为$Br+BrBs/(K-1)$
  • 显然应该选块数小的表作为外表

基于代价的优化

• 启发式规则优化是定性的选择，适合解释执行的系统
  • 解释执行的系统，优化开销包含在查询总开销之中
• 编译执行的系统中查询优化和查询执行是分开的
  • 可以采用精细复杂一些的基于代价的优化方法

统计信息

• 基于代价的优化方法要计算查询的各种不同执行方案的执行代价，它与数据库的状态密切相关
• 优化器需要的统计信息

代价估算

代价估算 1

• 全表扫描算法的代价估算公式
  • 如果基本表大小为B块，全表扫描算法的代价 cost＝B
  • 如果选择条件是“码＝值”，那么平均搜索代价 cost＝B/2
• 索引扫描算法的代价估算公式
  • 如果选择条件是“码＝值”
    • 则采用该表的主索引
    • 若为B+树，层数为L，需要存取B+树中从根结点到叶结点L块，再加上基本表中该元组所在的那一块，所以cost=L+1
  • 如果选择条件涉及非码属性
    • 若为B+树索引，选择条件是相等比较，S是索引的选择基数(有S个元组满足条件)
    • 满足条件的元组可能会保存在不同的块上，所以(最坏的情况)cost=L+S
  • 如果比较条件是＞，＞＝，＜，＜＝操作
    • 假设有一半的元组满足条件
    • 就要存取一半的叶结点
    • 通过索引访问一半的表存储块
    • cost=L+Y/2+B/2
    • 如果可以获得更准确的选择基数，可以进一步修正Y/2与B/2

代价估算 2