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

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

查询处理阶段

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

• 词法分析：从查询语句中识别出正确的语言符号
• 语法分析：进行语法检查

查询检查

• 任务

  • 语义分析

  • 符号名转换

  • 安全性检查

  • 完整性初步检查

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

• 如果是对视图的操作，则要用视图消解方法把对视图的操作转换成对基本表的操作

• 根据数据字典中的用户权限和完整性约束定义对用户的存取权限进行检查

• 检查通过后把SQL查询语句转换成内部表示，即等价的关系代数表达式。

• 关系数据库管理系统一般都用查询树，也称为语法分析树来表示扩展的关系代数表达式。

查询优化

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

查询执行

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

实现查询操作的算法示例

选择操作的实现

（1） 全表扫描方法 (Table Scan)

• 对查询的基本表顺序扫描，逐一检查每个元组是否满足选择条件，把满足条件的元组作为结果输出
• 适合小表，不适合大表

（2）索引扫描方法 (Index Scan)

• 适合于选择条件中的属性上有索引(例如B+树索引或Hash索引)
• 通过索引先找到满足条件的元组主码或元组指针，再通过元组指针直接在查询的基本表中找到元组

连接操作的实现

（1）嵌套循环算法(nested loop join)

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

（2）排序-合并算法(sort-merge join 或merge join)

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

（3）索引连接(index join)算法

• 1. 在SC表上已经建立属性Sno的索引。
  2. 对Student中每一个元组，由Sno值通过SC的索引查找相应的SC元组。
  3. 把这些SC元组和Student元组连接起来
  4. 循环执行23，直到Student表中的元组处理完为止

（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桶中

• 

• 

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

含义：

关系数据库管理系统通过某种代价模型计算出各种查询执行策略的执行代价，然后选取代价最小的执行方案

对于集中式数据库

• 执行开销主要包括
  • 磁盘存取块数(I/O代价)
  • 处理机时间(CPU代价)
  • 查询的内存开销
• I/O代价是最主要的 ，故主要通过减少IO代价来进行优化

直接看例子

求选修了81003号课程的学生姓名。 用SQL表达：

SELECT  Student.Sname
FROM      Student, SC
WHERE   Student.Sno=SC.Sno AND  SC.Cno='81003'

题设：

• 假定学生-课程数据库中有1000个学生记录，10000个选课记录
• 选修81003号课程的选课记录为50个

可以用多种等价的关系代数表达式来完成这一查询：

$$Q1={\pi }_{Sname}({\sigma }_{Student.Sno=SC.Sno\wedge SC.Cno=81003}(Student\times SC))$$

$$Q2={\pi }_{Sname}({\sigma }_{SC.Cno=81003}(Student\bowtie SC))$$

$$Q3={\pi }_{Sname}(Student\bowtie {\sigma }_{SC.Cno=81003}(SC))$$

首先区分一下笛卡尔积（$\times$ ），等值连接（$\underset{R.S=S.B}{\bowtie }$），自然连接($\bowtie$)

笛卡尔积（$\times$ ）

假设：

• 关系R：n列，k1行元组
• 关系S：m列，k2行元组

则R和S的广义笛卡尔积是一个 n+m列的元组的集合，有k1*k2个元组。看下图理解：

等值连接（$\underset{R.S=S.B}{\bowtie }$）

自然连接($\bowtie$)

注意：自然连接是一种特殊的等值连接

特殊在：

• 两个关系中进行比较的分量必须是同名的属性组
• 结果去掉重复的属性列

现在对刚才提出的三种方案进行分析：

1.

$$Q1={\pi }_{Sname}({\sigma }_{Student.Sno=SC.Sno\wedge SC.Cno=81003}(Student\times SC))$$

1.1首先要对Student和SC做广义笛卡尔积，怎么做？

算法：

• 在内存中尽可能多地装入某个表(如Student表)的若干块，留出一块存放另一个表(如SC表)的元组。
• 把SC中的每个元组和Student中每个元组连接，连接后的元组装满一块后就写到中间文件上从SC中读入一块和内存中的Student元组连接，直到SC表处理完。
• 再读入若干块Student元组，读入一块SC元组重复上述处理过程，直到把Student表处理完

设一个块能装10个Student元组或100个SC元组，在内存中存放5块Student元组和1块SC元组

以上条件之后还要用

计算在做笛卡尔积时读写的总块数：

1. **明确：**有1000个student元组，故有1000/10=100块。有10000个sc元组，故有10000/100=100块。
2. 每次读5块student可以读1块sc，注意对于这5块student中的元组，我们需要让其接触到sc表上的每个元组，也就是要读sc表的100个块。则一次处理5块student共需要读的块数为：5+100=105
3. 总共有多少次呢？有100/5 = 20次，故总读入的块数为105*20 = 2100块
4. 或者套公式cost＝Br+BrBs/(K-1) Br是外表的块数，Bs是内表的块数，K是占用内存的块数，则：=100+100*100/(5+1-1) = 2100 。此公式，在基于代价的优化会解释。

那么做笛卡尔积生成了多少块的数据呢？

连接后的元组数为10³×10⁴=10⁷。设每块能装10个元组，则写出10⁶ 块。

1.2作选择操作

• 依次读入连接后的元组，按照选择条件选取满足要求的记录
• 假定内存处理时间忽略。读取中间文件花费的时间(同写中间文件一样)需读入10⁶块。 若满足条件的元组假设仅50个，均可放在内存。

1.3作投影操作

• 把第2步的结果在Sname上作投影输出，得到最终结果

1.4执行查询的总读写数据块

• 2100+10⁶ +10⁶

2.

$$Q2={\pi }_{Sname}({\sigma }_{SC.Cno=81003}(Student\bowtie SC))$$

1.1计算自然连接

• 执行自然连接，读取Student和SC表的策略不变，总的读取块数仍为2100块
• 自然连接的结果比第一种情况大大减少，为10⁴个元组(因为sc有10⁴个元组，自然连接后元组个数最多为10⁴个)
• 写出数据块= 10³ 块

2.2读取中间文件块，执行选择运算

• 读取的数据块= 10³块

2.3把第2步结果投影输出。

2.4执行查询的总读写数据块

• =2100+ 10³ +10³
• 其执行代价大约是第一种情况的488分之一

3.

$$Q3={\pi }_{Sname}(Student\bowtie {\sigma }_{SC.Cno=81003}(SC))$$

3.1先对SC表作选择运算

• 只需读一遍SC表，存取100块，因为满足条件的元组仅50个，不必使用中间文件。

3.2读取Student表

• 把读入的Student元组和内存中的SC元组作连接。也只需读一遍Student表共100块。

3.3把连接结果投影输出

3.4总的读写数据块

• =100+100

3包含的优化方式：

• 有选择和连接操作时，先做选择操作，这样参加连接的元组就可以大大减少，这是代数优化

• 对于Student和SC表的连接，利用Student表上的索引，采用索引连接代价也较小，这就是物理优化。

10.3 代数优化

含义：

通过对关系代数表达式的等价变换来提高查询效率

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

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

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

   

3. 投影的串接定律

   $${\Pi }_{X1,X2,..Xm}({\Pi }_{Y1,Y2,...,Yn}(R))={\Pi }_{X1,X2,..Xm}(R)$$

   条件：

   $X1,X2,..Xm$是$Y1,Y2,...,Yn$的子集

4. 选择的串接定律

   $${\sigma }_P({\sigma }_Q(R))={\sigma }_{P\wedge Q}(R)$$

5. 选择与投影操作的交换律
   $${\sigma }_P({\Pi }_{X1,X2,..Xm}(R))={\Pi }_{X1,X2,..Xm}({\sigma }_P(R))$$

   条件：

   选择条件P只涉及属性X1，…，Xm。

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

   

7. 选择与并的分配律

   

8. 选择与差运算的分配律

   

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

   

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

    

11. 投影与并的分配律

    

查询树的启发式优化

书上写了一大堆概念，建议先看例子或者视频，对着例子理解概念。

SELECT  Student.Sname
FROM      Student, SC
WHERE   Student.Sno=SC.Sno AND  SC.Cno='81003'

1. SQL转换为查询树：

   

2. 使用关系代数表达式

   

3. 本例当中，哪个选择先做是没区别的。我们想要尽可能少的从硬盘往内存中读块，故把选择${\sigma }_{SC.Cno=2}$移到叶端

   

   接下来给出概念：

   1. 选择运算应尽可能先做
   2. 把投影运算和选择运算同时进行
   3. 把投影同其前或其后的双目运算结合起来，没有必要为了去掉某些字段而扫描一遍关系。
   4. 把某些选择同在它前面要执行的笛卡尔积结合起来成为一个连接运算，连接特别是等值连接运算要比同样关系上的笛卡尔积省很多时间。
   5. 找出公共子表达式
      • 如果这种重复出现的子表达式的结果不是很大的关系
      • 并且从外存中读入这个关系比计算该子表达式的时间少得多
      • 则先计算一次公共子表达式并把结果写入中间文件是合算的。
      • 当查询的是视图时，定义视图的表达式就是公共子表达式的情况

   再来个例子加深理解：

   
   
   
   

10.4 物理优化

含义：选择高效合理的操作算法或存取路径，求得优化查询计划

基于启发式规则的优化

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

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

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

（1）如果2个表都已经按照连接属性排序

• 选用排序-合并算法

（2）如果一个表在连接属性上有索引

• 选用索引连接算法

（3）如果上面2个规则都不适用，其中一个表较小

• 选用Hash join算法

（4）可以选用嵌套循环方法，并选择其中较小的表，即占用的块数较少的表，作为外表(外循环的表) 。

基于代价的优化

1.统计信息

2.代价估算示例

（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
  • 如果可以获得更准确的选择基数，可以进一步修正Y/2与B/2

（3）嵌套循环连接算法的代价估算公式

• 嵌套循环连接算法的代价

  • cost＝Br+BrBs/(K-1)

• 如果需要把连接结果写回磁盘

  • cost＝Br+BrBs/(K-1)+(Frs*Nr*Ns)/Mrs
  • 其中Frs为连接选择性(join selectivity)，表示连接结果元组数的比例
  • Mrs是存放连接结果的块因子，表示每块中可以存放的结果元组数目

（4）排序-合并连接算法的代价估算公式

• 如果连接表已经按照连接属性排好序，则
  • cost＝Br+Bs+(Frs*Nr*Ns)/Mrs
• 如果必须对文件排序
  • 还需要在代价函数中加上排序的代价
  • 对于包含B个块的文件排序的代价大约是 4*B