【课堂笔记】《数据库系统概论（第5版）》-第9章：关系查询处理和查询优化

第9章：关系查询处理和查询优化

本章介绍查询处理和查询优化；

查询优化一般可以分为代数优化（逻辑优化）和物理优化；

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

查询处理的任务是把用户提交的查询语句转化为高效的查询执行计划；

9.1.1 查询处理的步骤

关系数据库管理系统的查询处理可以分为四个阶段：

• 查询分析
• 查询检查
• 查询优化
• 查询执行

1. 查询分析

   对查询语句进行扫描、词法分析、语法分析；

   从查询语句中识别出语言符号，如SQL关键字、属性名、关系名等，判断查询语句是否符合SQL语法规则，如没有问题，转入下一步处理；

2. 查询检查

   对合法的查询语句进行语义检查；

   即根据数据字典中有关的模式定义检查语句中的数据库对象，如关系名和属性名是否有效；

   如果是对视图进行操作，则要通过视图消解将对视图的操作转化为对基本表的操作；

   同时根据数据字典中的用户权限定义和完整性约束定义对用户的存取权限进行检查（这时的完整性检查是初步的】静态的检查）；

   上述检查通过之后，将SQL查询语句转化为内部表示，即等价的关系代数表达式；

   关系数据库管理系统一般用查询树（语法分析树）来表示关系代数表达式；

3. 查询优化

   • 代数优化：

     按照一定的规则，对关系代数表达式进行等价变换，改变表达式中的操作次序和组合，提高查询效率；

   • 物理优化：

     存取路径和底层操作算法的选择；

     选择的依据可以是基于规则、代价、语义的；

4. 查询执行

   依据优化器得到的查询执行策略生成查询执行计划，由代码生成器生成执行这个查询计划的代码，然后加以执行并返回结果；（为什么作者可以把P放的这么高端）

9.1.2 实现查询操作的算法示例

本节简单介绍查询操作和连接操作的实现的算法思想（口胡）

1. 选择操作的实现

   假设有如下语句：

   select * from student where <blablabla>;
   

   1. 简单的全表扫描法

      对于规模小的表，简单有效

      对于规模大且选择率较低的表，效率很低

   2. 索引扫描算法

      如果在选择条件中的属性上建立有索引（hash索引和B+树索引），可以先通过索引找到满足条件的元组的指针，再通过元组指针找到元组；

      当选择率较低时，索引扫描的效率较高；

      如果需要查找的元组均匀分布在表中，全表扫描性能更优；

2. 连接操作的实现

   连接操作是查询处理中最常用也是最耗时的操作之一；

   这里通过简单介绍等值连接（自然连接）简单介绍常见的算法思想；

   e.g.

   select * from Student, SC where Student.Sno = SC.Sno;
   

   1. 嵌套循环算法

      传说中的双重循环；

   2. 排序-合并算法

      • 首先对Student和SC表按Sno进行排序；
      • 选取Student中的第一个Sno，依次扫描SC中具有相同Sno的元组，然后连接起来；
      • 当扫描到SC第一个Sno不同的元组时，扫描Student的下一个元组，重复上述步骤；

      一般来说，对于规模比较大的表，使用排序-合并算法执行连接操作总时间会减少；

   3. 索引连接算法

      • 在SC表上建立Sno的索引
      • 对Student中的每一个元组，由Sno的值通过索引找到相应的SC元组，然后连接起来；

   4. 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桶中；

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

查询优化是影响数据库管理系统性能的关键因素；

9.2.1 查询优化概述

查询优化的优点不仅在于用户不必考虑如何更好地表达查询以获得更高的效率，而且在于系统可以比用户程序优化的更好，这是因为：

• 优化器可以从数据字典中获取信息，做出正确的估算，选择高效的执行计划；
• 在物理统计信息改变的情况下，无需重写程序；
• 优化器可以考虑更多的可行方案；
• 优化其中的技术一般人掌握不了；

在集中式数据库中，查询执行的开销主要包括：磁盘读取块数（IO代价）、处理机时间（CPU代价）、内存开销；

总代价 = IO代价 + CPU代价 + 内存代价 + 通信代价

IO代价是总代价的主要部分（磁盘读取设计磁盘的机械操作）；

有时系统选择的方案不是最优的而是较优的；

9.2.2 一个实例

select Student.Sname
from Student, SC
where Student.Sno = SC.Sno and SC.Cno = '2';

1. ${\Pi }_{Sname}({\sigma }_{Student.Sno=SC.Sno\wedge SC.Sno{=}^{\prime }{2}^{\prime }}(Student\times SC))$

   • 先计算广义笛卡尔积
   • 再进行选择操作
   • 再进行投影操作

2. ${\Pi }_{Sname}({\sigma }_{SC.Cno{=}^{\prime }{2}^{\prime }}(Student\bowtie SC))$

   • 先计算自然连接
   • 然后进行选择操作
   • 然后进行投影

   比上一种方法减少了很多读写量；

3. ${\Pi }_{Sname}(Student\bowtie {\sigma }_{SC.Cno{=}^{\prime }{2}^{\prime }}(SC))$

   • 先进行选择操作
   • 再进行连接操作
   • 最后投影

   代价更小

9.3 代数优化

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

1. 连接、笛卡尔积的交换律

   $E1\times E2\equiv E2\times E1$

   $E1\bowtie E2\equiv E2\bowtie E1$

2. 连接、笛卡尔积结合律

   $(a\times b)\times c\equiv a\times (b\times c)$

   $(a\bowtie b)\bowtie c\equiv a\bowtie (b\bowtie c)$

3. 投影的串接定律

   ${\Pi }_{a1,a2,\cdots ,an}({\Pi }_{b1,b2,\cdots ,bm}(E))\equiv {\Pi }_{a1,a2,\cdots ,an}(E)$

4. 选择的串接定律

   ${\sigma }_{F_1}({\sigma }_{F_2}(E))\equiv {\sigma }_{F_1\wedge F_2}(E)$

5. 选择与投影的交换律

   ${\sigma }_F({\Pi }_{a1,a2,\cdots }(E))\equiv {\Pi }_{a1,a2,\cdots }({\sigma }_F(E))$

6. 选择与笛卡尔积的交换律

   ${\sigma }_F(a\times b)\equiv {\sigma }_F(a)\times b$

7. 选择与并的分配律

   ${\sigma }_F(a\cup b)\equiv {\sigma }_F(a)\cup {\sigma }_F(b)$

8. 选择与差的分配律

   ${\sigma }_F(a-b)\equiv {\sigma }_F(a)-{\sigma }_F(b)$

9. 选择对自然连接的分配律

   ${\sigma }_F(a\bowtie b)\equiv {\sigma }_F(a)\bowtie {\sigma }_F(b)$

10. 投影与笛卡尔积的分配律

    $\Pi(a \times b) \equiv \Pi(a) \times \Pi(b)

11. 投影与并的分配律

    $\Pi (a\cup b)\equiv \Pi (a)\cup \Pi (b)$

9.3.2 查询树的启发式优化

1. 选择运算尽可能先做

   因为选择运算影响中间结果的大小

2. 投影运算与选择运算同时进行

   避免重复扫描关系

3. 投影运算与其前后的双目运算符结合起来

4. 对于选择与笛卡尔积结合起来的运算，作为连接运算

5. 找出公共子表达式

9.4 物理优化

代数优化不涉及底层的存取路径；

物理优化就是选择高效的操作算法和存取路径；

选择的方法可以是：

• 基于规则的启发式优化；
• 基于代价估算的优化；
• 两者结合；

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

1. 选择操作的启发式规则

   对于小关系，全表顺序扫描；

   对于大关系：

   1. 如果查询条件是“主码=值”，查询结果最多为一个元组，可以选择主码索引；
   2. 对于其他情况，估算选择比例，如果较小（低于10%），选择索引，否则，选择全表扫描；
   3. 对于非等值查询或者范围查询，同上依据选择率来决定；
   4. 对于and连接的条件，如果有组合索引，优先采用组合索引；
   5. 对于or，一般全表扫描；

2. 连接操作的启发式规则

   • 如果两个表已经排序，采用排序-合并算法；
   • 如果有索引，可以采用索引连接算法；
   • 如果有一个表较小，采用hash join；
   • 最后可以采用嵌套循环；

9.4.2 基于代价估算的优化

启发式规则比较粗糙但是实现简单且并本身代价较小，适合解释型的系统；

在编译型的系统中，可以采用基于代价优化的算法；

1. 统计信息

   数据字典中储存了优化器需要的统计信息，主要包括以下几个方面：

   1. 对于每一个基本表，元组总数、元组长度、占用的块数、占用的溢出块数；
   2. 对基本表的每一个列，不同值的个数、最大值最小值、是否有索引、索引种类；
   3. 对于索引，层数、不同索引值的个数、叶节点数；

2. 实例（略）