数据库---关系查询处理和查询优化

关系数据库系统的查询处理

查询处理步骤

• 查询分析：对查询语句进行扫描、词法分析和语法分析，从查询语句中识别出语言符号，进行语法检查和语法分析。没有错误就转入下部处理，否则报告出错。

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

• 查询优化：选择一个高效执行的查询处理策略
  查询优化分类 ：代数优化（关系代数表达式的优化）和物理优化（存取路径和底层操作算法的选择）。

  查询优化方法选择的依据：基于规则(rule based)，基于代价(cost based)，基于语义(semantic based)。

• 查询执行：依据优化器得到的执行策略生成查询计划，由代码生成器(code generator)生成执行查询计划的代码，然后加以执行，回送查询结果。
  

选择操作典型实现方法

• 简单的全表扫描方法
  对查询的基本表顺序扫描，逐一检查每个元组是否满足选择条件，把满足条件的元组作为结果输出。适合小表，不适合大表。只需要很少的内存块就可以运行，控制简单。
• 索引(或散列)扫描方法
  适合选择条件中的属性上有索引(例如B+树索引或Hash索引) ，通过索引先找到满足条件的元组主码或元组指针，再通过元组指针直接在查询的基本表中找到元组。
• 一般情况下，当选择率较低时，基于索引的选择算法要优于全表扫描算法。若选择率较高，或者要查找的元组均匀的分布在查找的表中，基于索引的选择算法的性能不如全表扫描算法，因为除了对表的扫描还要加上对B+树索引的扫描操作，对每一个检索码，从B+树根节点到叶子节点路径上的每个节点都要执行一次IO操作。

连接操作典型实现方法

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

• 排序-合并方法(sort-merge join 或merge join)
  适合连接的诸表已经排好序的情况，是等值连接常用的方法。

  步骤：如果连接的表没有排好序，先对表按连接属性排序，取表1中第一个元组，依次扫描表2中具有相同属性值的元组。当扫描到属性值不相同的第一个表2的元组时，返回表1扫描它的下一个元组，再扫描2表中具有相同Sno的元组，把它们连接起来，重复上述步骤直到表1扫描完。

  表1和2表都只要扫描一遍，如果2个表原来无序，执行时间要加上对两个表的排序时间，对于2个大表，先排序后使用sort-merge join方法执行连接，总的时间一般仍会大大减少

• 索引连接(index join)方法
  在表2上建立某属性的索引，对表1中每一个元组，由该属性值通过表2的索引查找相应的表2元组，把这些表2元组和表1的元组连接起来，循环执行，直到表1中的元组处理完为止。

• Hash Join方法
  把连接属性作为hash码，用同一个hash函数把表1和表2中的元组散列到同一个hash文件中.

  划分阶段(partitioning phase)：（创建阶段）创建hash表，对包含较少元组的表进行一遍处理，把它的元组按hash函数分散到hash表的桶中。

  试探阶段(probing phase)：也称为连接阶段(join phase) 。对另一个表进行一遍处理
  把该表的元组按同一个hash函数进行散列，把元组与桶中所有来自表1并与之相匹配的元组连接起来。

  上面hash join算法前提：假设两个表中较小的表在第一阶段后可以完全放入内存的hash桶中。

关系数据库系统的查询优化

• 查询优化的优点不仅在于用户不必考虑如何最好地表达查询以获得较好的效率，而且在于系统可以比用户程序的“优化”做得更好。

  优化器可以从数据字典中获取许多统计信息，而用户程序则难以获得这些信息。

  如果数据库的物理统计信息改变了，系统可以自动对查询重新优化以选择相适应的执行计划。在非关系系统中必须重写程序，而重写程序在实际应用中往往是不太可能的。

  优化器可以考虑数百种不同的执行计划，程序员一般只能考虑有限的几种可能性。

  优化器中包括了很多复杂的优化技术，这些优化技术往往只有最好的程序员才能掌握。系统的自动优化相当于使得所有人都拥有这些优化技术。

• RDBMS通过某种代价模型计算出各种查询执行策略的执行代价，然后选取代价最小的执行方案。

  集中式数据库执行开销主要包括：磁盘存取块数(I/O代价，最主要的)，处理机时间(CPU代价)，查询的内存开销。

  分布式数据库：磁盘存取块数(I/O代价，最主要的)，处理机时间(CPU代价)，查询的内存开销+通信代价。

  在计算查询代价时一般用查询处理读写的块数作为衡量单位。

• 查询优化的总目标：选择有效的策略，求得给定关系表达式的值，使得查询代价最小(实际上是较小) 。

代数优化

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

• 代数优化策略：通过对关系代数表达式的等价变换来提高查询效率。

  关系代数表达式的等价：指用相同的关系代替两个表达式中相应的关系所得到的结果是相同的。两个关系表达式E1和E2是等价的，可记为E1≡E2。

• 连接、笛卡尔积交换律
  设E1和E2是关系代数表达式，F是连接运算的条件，则有
  

• 连接、笛卡尔积的结合律
  设E1，E2，E3是关系代数表达式，F1和F2是连接运算的条件，则有
  

• 投影的串接定律
  
  这里，E是关系代数表达式，Ai(i=1，2，…，n)，Bj(j=1，2，…，m)是属性名且{A1，A2，…，An}构成{B1，B2，…，Bm}的子集。

• 选择的串接定律
  
  这里，E是关系代数表达式，F1、F2是选择条件。选择的串接律说明选择条件可以合并。这样一次就可检查全部条件。

• 选择与投影操作的交换律
  
  选择条件F只涉及属性A1，…，An。

  若F中有不属于A1，…，An的属性B1，…，Bm则有更一般的规则：
  

• 选择与笛卡尔积的交换律
  如果F中涉及的属性都是E1中的属性，则
  
  如果F=F1∧F2，并且F1只涉及E1中的属性，F2只涉及E2中的属性，则由上面的等价变换规则可推出：
  

  若F1只涉及E1中的属性，F2涉及E1和E2两者的属性，则仍有
  

  它使部分选择在笛卡尔积前先做。

• 选择与并的分配律
  设E=E1∪E2，E1，E2有相同的属性名，则
  

• 选择与差运算的分配律
  若E1与E2有相同的属性名，则
  

• 选择对自然连接的分配律
  
  F只涉及E1与E2的公共属性

• 投影与笛卡尔积的分配律
  设E1和E2是两个关系表达式，A1，…，An是E1的属性，B1，…，Bm是E2的属性，则
  

• 投影与并的分配律
  设E1和E2有相同的属性名，则
  

查询树的启发式优化

• 典型的启发式规则：

  选择运算应尽可能先做。在优化策略中这是最重要、最基本的一条。

  把投影运算和选择运算同时进行：如有若干投影和选择运算，并且它们都对同一个关系操作，则可以在扫描此关系的同时完成所有的这些运算以避免重复扫描关系。

  把投影同其前或其后的双目运算结合起来。

  把某些选择同在它前面要执行的笛卡尔积结合起来成为一个连接运算。

  找出公共子表达式：如果这种重复出现的子表达式的结果不是很大的关系并且从外存中读入这个关系比计算该子表达式的时间少得多，则先计算一次公共子表达式并把结果写入中间文件是合算的。当查询的是视图时，定义视图的表达式就是公共子表达式的情况。

• 算法：关系表达式的优化
  输入：一个关系表达式的查询树
  输出：优化的查询树

  方法：利用等价变换规则把形如σF1∧F2∧…∧Fn(E)变换为σF1(σF2(…(σFn(E))…))。对每一个选择，利用等价变换规则尽可能把它移到树的叶端。对每一个投影利用等价变换规则中的一般形式尽可能把它移向树的叶端。利用等价变换规则把选择和投影的串接合并成单个选择、单个投影或一个选择后跟一个投影。使多个选择或投影能同时执行，或在一次扫描中全部完成。把上述得到的语法树的内节点分组。每一双目运算(×，连接，∪，-)和它所有的直接祖先为一组(这些直接祖先是(σ，π运算)。如果其后代直到叶子全是单目运算，则也将它们并入该组，但当双目运算是笛卡尔积(×)，而且后面不是与它组成等值连接的选择时，则不能把选择与这个双目运算组成同一组，把这些单目运算单独分为一组。

物理优化

• 代数优化改变查询语句中操作的次序和组合，不涉及底层的存取路径。物理优化就是要选择高效合理的操作算法或存取路径，求得优化的查询计划。
  选择的方法：
  基于规则的启发式优化：在大多数情况下都适用，但不是在每种情况下都是最好的规则。

  基于代价估算的优化

  两者结合的优化方法：常常先使用启发式规则，选取若干较优的候选方案，减少代价估算的工作量，然后分别计算这些候选方案的执行代价，较快的选出最终的优化方案。

基于启发式规则的存取路径选择优化

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

  对于大关系，启发式规则有：对于选择条件是主码＝值的查询，查询结果最多是一个元组，可以选择主码索引，一般的RDBMS会自动建立主码索引。对于选择条件是非主属性＝值的查询，并且选择列上有索引，要估算查询结果的元组数目，如果比例较小(<10%)可以使用索引扫描方法，否则还是使用全表顺序扫描。对于选择条件是属性上的非等值查询或者范围查询，并且选择列上有索引，要估算查询结果的元组数目，如果比例较小(<10%)可以使用索引扫描方法，否则还是使用全表顺序扫描。对于用AND连接的合取选择条件，如果有涉及这些属性的组合索引，优先采用组合索引扫描方法，如果某些属性上有一般的索引则可以用索引扫描方法，否则使用全表顺序扫描。对于用OR连接的析取选择条件，一般使用全表顺序扫描。

• 连接操作的启发式规则：
  如果2个表都已经按照连接属性排序，选用排序-合并方法。如果一个表在连接属性上有索引，选用索引连接方法。如果上面2个规则都不适用，其中一个表较小，选用Hash join方法。可以选用嵌套循环方法，并选择其中较小的表，确切地讲是占用的块数较少的表，作为外表(外循环的表) 。

基于代价估算的优化

• 启发式规则优化是定性的选择，比较粗糙，实现简单且优化本身的代价较小，适合解释执行的系统。因为解释执行的系统，优化开销包含在查询总开销之中，编译执行的系统中查询优化和查询执行是分开的，因此可以采用精细复杂一些的基于代价的优化方法。

• 统计信息
  基于代价的优化方法要计算各种操作算法的执行代价，与数据库的状态密切相关，数据字典中存储的优化器需要的统计信息：

  对每个基本表：该表的元组总数(N)，元组长度(l)，占用的块数(B)，占用的溢出块数(BO)。

  对基表的每个列：该列不同值的个数(m)，选择率(f)，如果不同值的分布是均匀的，f＝1/m，如果不同值的分布不均匀，则每个值的选择率＝具有该值的元组数/N，该列最大值，该列最小值，该列上是否已经建立了索引，索引类型(B+树索引、Hash索引、聚集索引)。

• 对索引(如B+树索引)：索引的层数(L)，不同索引值的个数，索引的选择基数S(有S个元组具有某个索引值)，索引的叶结点数(Y) 。

• 全表扫描算法的代价估算公式
  如果基本表大小为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。

• 嵌套循环连接算法的代价估算公式
  嵌套循环连接算法的代价cost＝Br+Bs/(K-1) Br （设连接表R与S分别占用的块数为Br与Bs，连接操作使用的内存缓冲区块数为K，分配K-1块给外表。若R为外表，则嵌套循环法存取的块数为cost）。如果需要把连接结果写回磁盘，cost＝Br+Bs/(K-1) Br +(FrsNrNs)/Mrs，其中Frs为连接选择性(join selectivity)，表示连接结果元组数的比例，Mrs是存放连接结果的块因子，表示每块中可以存放的结果元组数目。

• 排序-合并连接算法的代价估算公式
  如果连接表已经按照连接属性排好序，则cost＝Br+Bs+(FrsNrNs)/Mrs。如果必须对文件排序，需要在代价函数中加上排序的代价，对于包含B个块的文件排序的代价大约是(2B)+(2B*log2B)。

查询计划的执行*