数据库系统原理 (九): 关系查询处理及其查询优化

2021/4/23 19:25:23

编程Tag： 优化 查询 索引 sc 元组 Student 数据库系统 查询处理

本文主要是介绍数据库系统原理 (九): 关系查询处理及其查询优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

参考《数据库系统概论》

目录

  • 关系数据库系统的查询处理
    • 查询处理步骤
    • 实现查询操作的算法示例
      • 选择操作的实现
      • 连接操作的实现
  • 关系数据库系统的查询优化
    • 查询优化概述
    • 一个实例
  • 代数优化
    • 关系代数表达式等价变换规则
    • 查询树的启发式优化
  • 物理优化
    • 基于启发式规则的存取路径选择优化
      • 选择操作的启发式规则
      • 连接操作的启发式规则
    • 基于代价的优化
      • 统计信息
      • 代价估算示例

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

查询处理步骤

在这里插入图片描述

  • 查询分析: 对查询语句进行扫描、词法分析和语法分析，从查询语句中识别出语言符号，进行语法检查和语法分析
  • 查询检查: 根据数据字典对合法的查询语句进行语义检查；根据数据字典中的用户权限和完整性约束定义对用户的存取权限进行检查；检查通过后把SQL查询语句转换成等价的关系代数表达式，RDBMS一般都用查询树(语法分析树)来表示扩展的关系代数表达式 (把数据库对象的外部名称转换为内部表示)
  • 查询优化: 选择一个高效执行的查询处理策略; 查询优化方法选择的依据：基于规则(rule based), 基于代价(cost based), 基于语义(semantic based)
    • 代数优化：指关系代数表达式的优化
    • 物理优化：指存取路径和底层操作算法的选择
  • 查询执行: 依据优化器得到的执行策略生成查询计划, 代码生成器(code generator)生成执行查询计划的代码

实现查询操作的算法示例

选择操作的实现

简单的全表扫描方法

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

索引(或散列)扫描方法

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

例

  • Sno＝‘200215121’，并且 Sno 上有索引
    • 使用索引(或散列)得到 Sno 为 ‘200215121’ 元组的指针
    • 通过元组指针在 Student 表中检索到该学生

例

  • Sage > 20，并且 Sage 上有 B+ 树索引
    • 使用 B+ 树索引找到 Sage＝20 的索引项，以此为入口点在 B+ 树的顺序集上得到 Sage>20 的所有元组指针
    • 通过这些元组指针到 Student 表中检索到所有年龄大于 20 的学生

例

  • Sdept＝‘CS’ AND Sage>20，如果 Sdept 和 Sage 上都有索引：
    • 算法一：分别用上面两种方法分别找到 Sdept＝‘CS’ 的一组元组指针和 Sage>20 的另一组元组指针，求这2组指针的交集，到 student 表中检索得到计算机系年龄大于20的学生
    • 算法二：找到 Sdept＝‘CS’ 的一组元组指针，通过这些元组指针到 student 表中检索，对得到的元组检查另一些选择条件(如 Sage>20 )是否满足，把满足条件的元组作为结果输出

连接操作的实现

  • 连接操作是查询处理中最耗时的操作之一

本节只讨论等值连接(或自然连接)最常用的实现算法

SELECT * FROM Student, SC 
WHERE Student.Sno=SC.Sno;

嵌套循环方法 (nested loop)

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

排序-合并方法 (sort-merge join)

  • 适合连接的诸表已经排好序的情况；如果连接的表没有排好序，先对 Student 表和 SC 表按连接属性 Sno 排序
  • 取 Student 表中第一个 Sno，依次扫描 SC 表中具有相同 Sno 的元组，当扫描到 Sno 不相同的第一个 SC 元组时，返回 Student 表扫描它的下一个元组，再扫描 SC 表中具有相同 Sno 的元组，把它们连接起来
  • 重复上述步骤直到 Student 表扫描完
    在这里插入图片描述
    • 对于2个大表，先排序后使用 sort-merge join 方法执行连接，总的时间一般会大大减少

索引连接(index join)方法

  • 如果属性 Sno 原来没有索引, 则建立索引
  • 对 Student 中每一个元组，由 Sno 值通过 SC 的索引查找相应的 SC 元组，把这些 SC 元组和 Student 元组连接起来
  • 重复执行上一步，直到 Student 表中的元组处理完为止

Hash Join 方法

  • 把连接属性作为 hash 码，用同一个 hash 函数把 R R R 和 S S S 中的元组散列到同一个 hash 文件中
    • 划分阶段 (partitioning phase)：对包含较少元组的表 R R R 进行一遍处理，把它的元组按 hash 函数分散到 hash 表的桶中 (假设两个表中较小的表在第一阶段后可以完全放入内存的 hash 桶中)
    • 试探阶段 (probing phase)：也称为连接阶段(join phase)，对另一个表 S S S 进行一遍处理，把 S S S 的元组散列到适当的 hash 桶中，把元组与桶中所有来自 R R R 并与之相匹配的元组连接起来
      在这里插入图片描述

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

  • 关系查询优化是影响RDBMS性能的关键因素。由于关系表达式的语义级别很高，使关系系统可以从关系表达式中分析查询语义，提供了执行查询优化的可能性

查询优化概述

  • 查询优化的优点不仅在于用户不必考虑如何最好地表达查询以获得较好的效率，而且在于系统可以比用户程序的“优化 ”做得更好
    • (1) 优化器可以从数据字典中获取许多统计信息，而用户程序则难以获得这些信息
    • (2) 如果数据库的物理统计信息改变了，系统可以自动对查询重新优化以选择相适应的执行计划。在非关系系统中必须重写程序 ，而重写程序在实际应用中往往是不太可能的
    • (3) 优化器可以考虑数百种不同的执行计划，程序员一般只能考虑有限的几种可能性
    • (4) 优化器中包括了很多复杂的优化技术，这些优化技术往往只有最好的程序员才能掌握。系统的自动优化相当于使得所有人都拥有这些优化技术
  • RDBMS 通过某种代价模型计算出各种查询执行策略的执行代价 ，然后选取代价最小的执行方案
    • 集中式数据库：执行开销主要包括：磁盘存取块数(I/O代价)、处理机时间(CPU代价)、查询的内存开销 (I/O代价是最主要的)
    • 分布式数据库: 总代价=I/O代价+CPU代价+内存代价+通信代价

查询优化的总目标：

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

一个实例

例

# 求选修了2号课程的学生姓名
SELECT  Student.Sname
FROM  Student, SC
WHERE  Student.Sno=SC.Sno AND SC.Cno='2';
  • 假定学生-课程数据库中有1000个学生记录，10000个选课记录；其中选修2号课程的选课记录为50个
  • 系统可以用多种等价的关系代数表达式来完成这一查询
    在这里插入图片描述
  • 第一种情况需要计算广义笛卡尔积：在内存中尽可能多地装入某个表(如Student表)的若干块，留出一块存放另一个表(如SC表)的元组。把SC中的每个元组和Student中每个元组连接，连接后的元组装满一块后就写到中间文件上；从SC中读入一块和内存中的Student元组连接，直到SC表处理完。再读入若干块Student元组，读入一块SC元组；重复上述处理过程，直到把Student表处理完
    • 设一个块能装10个Student元组或100个SC元组，在内存中存放5块Student元组和1块SC元组，则读取总块数为
      在这里插入图片描述其中，读 Student 表 100 块。读 SC 表 20 遍，每遍 100 块; 若每秒读写 20 块，则总计要花 105 105 105s
    • 连接后的元组数为 1 0 3 × 1 0 4 = 1 0 7 10^3×10^4=10^7 103×104=107。设每块能装 10 个元组，则写出这些块要用 1 0 6 / 20 = 5 × 1 0 4 s 10^6/20=5×10^4s 106/20=5×104s
    • 依次读入连接后的元组，按照选择条件选取满足要求的记录。假定内存处理时间忽略。读取中间文件花费的时间 (同写中间文件一样) 需 5 × 1 0 4 5×10^4 5×104s，满足条件的元组假设仅 50 个，均可放在内存
    • 最后把上一步的结果在Sname上作投影输出，得到最终结果
    • 查询的总时间 ≈ 105 + 2 × 5 × 1 0 4 ≈ 1 0 5 ≈105+2×5×10^4≈10^5 ≈105+2×5×104≈105s
  • 第二种情况
    • 执行自然连接，读取Student和SC表的策略不变，总的读取块数仍为 2100 块花费 105 105 105s；自然连接的结果比第一种情况大大减少，为 1 0 4 10^4 104 个，写出这些元组时间为 1 0 4 / 10 / 20 = 50 10^4/10/20=50 104/10/20=50s，为第一种情况的千分之一
    • 读取中间文件块，执行选择运算，花费时间也为 50 50 50s
    • 最后把上一步的结果在Sname上作投影输出，得到最终结果
    • 查询的总时间 ≈ 105 + 50 + 50 = 205 ≈105+50+50=205 ≈105+50+50=205s
  • 第三种情况
    • 先对SC表作选择运算，只需读一遍SC表，存取100块花费时间为 5 5 5s ，因为满足条件的元组仅 50 个，不必使用中间文件
    • 读取Student表，把读入的Student元组和内存中的SC元组作连接 。也只需读一遍Student表共100块，花费时间为 5 5 5s
    • 把连接结果投影输出
    • 总的执行时间 ≈ 5 + 5 ≈ 10 s ≈5+5≈10s ≈5+5≈10s
# 求选修了2号课程的学生姓名
SELECT  Student.Sname
FROM  Student, SC
WHERE  Student.Sno=SC.Sno AND SC.Cno='2';
  • 假如 SC 表的 Cno 字段上有索引，第一步就不必读取所有的 SC 元组而只需读取 Cno=‘2’ 的那些元组 (50个)。存取的索引块和SC中满足条件的数据块大约总共3～4块
  • 若 Student 表在 Sno 上也有索引，第二步也不必读取所有的 Student 元组；因为满足条件的 SC 记录仅50个，涉及最多 50 个 Student 记录，读取 Student 表的块数也可大大减少；总的存取时间将减少到数秒

代数优化

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

  • 代数优化策略：通过对关系代数表达式的等价变换来提高查询效率
    • 两个关系表达式 E 1 E_1 E1​ 和 E 2 E_2 E2​ 是等价的，可记为 E 1 ≡ E 2 E_1≡E_2 E1​≡E2​

常用的等价变换规则

  • (1) 连接、笛卡尔积交换律: 设 E 1 E_1 E1​ 和 E 2 E_2 E2​ 是关系代数表达式， F F F 是连接运算的条件，则有
    在这里插入图片描述
  • (2) 连接、笛卡尔积的结合律
    在这里插入图片描述
  • (3) 投影的串接定律: 设 A i ( i = 1 ， 2 ， … ， n ) A_i(i=1，2，…，n) Ai​(i=1，2，…，n)， B j ( j = 1 ， 2 ， … ， m ) B_j(j=1，2，…，m) Bj​(j=1，2，…，m) 是属性名且 { A 1 ， A 2 ， … ， A n } ⊂ { B 1 ， B 2 ， … ， B m } \{A_1，A_2，…，A_n\}\subset\{B_1，B_2，…，B_m\} {A1​，A2​，…，An​}⊂{B1​，B2​，…，Bm​}
    在这里插入图片描述
  • (4) 选择的串接定律: 选择的串接律说明选择条件可以合并。这样一次就可检查全部条件
    在这里插入图片描述
  • (5) 选择与投影操作的交换律: 设条件 F F F 只涉及属性 A 1 ， … ， A n A_1，…，A_n A1​，…，An​
    在这里插入图片描述若 F F F 中有不属于 A 1 ， … ， A n A_1，…，A_n A1​，…，An​ 的属性 B 1 ， … ， B m B_1，…，B_m B1​，…，Bm​ 则有更一般的规则 (把一个投影分裂为两个，其中一个有可能被移向树的叶端)：
    在这里插入图片描述
  • (6) 选择与笛卡尔积的交换律
    • 设 F F F 中涉及的属性都是 E 1 E_1 E1​ 中的属性，则
      在这里插入图片描述
    • 如果 F = F 1 ∧ F 2 F=F_1∧F_2 F=F1​∧F2​，并且 F 1 F_1 F1​ 只涉及 E 1 E_1 E1​ 中的属性， F 2 F_2 F2​ 只涉及 E 2 E_2 E2​ 中的属性，则由上面的等价变换规则 1，4，6 可推出：
      在这里插入图片描述
    • 若 F 1 F_1 F1​ 只涉及 E 1 E_1 E1​ 中的属性， F 2 F_2 F2​ 涉及 E 1 E_1 E1​ 和 E 2 E_2 E2​ 两者的属性，则仍有
      在这里插入图片描述它使部分选择在笛卡尔积前先做
  • (7) 选择与并的分配律: 设 E = E 1 ∪ E 2 E=E_1∪E_2 E=E1​∪E2​， E 1 E_1 E1​， E 2 E_2 E2​ 有相同的属性名，则
    在这里插入图片描述
  • (8) 选择与差运算的分配律: 若 E 1 E_1 E1​ 与 E 2 E_2 E2​ 有相同的属性名，则
    在这里插入图片描述
  • (9) 选择对自然连接的分配律: 若 F F F 只涉及 E 1 E_1 E1​ 与 E 2 E_2 E2​ 的公共属性，则
    在这里插入图片描述
  • (10) 投影与笛卡尔积的分配律: 设 E 1 E_1 E1​ 和 E 2 E_2 E2​ 是两个关系表达式， A 1 ， … ， A n A_1，…，A_n A1​，…，An​ 是 E 1 E_1 E1​ 的属性， B 1 ， … ， B m B_1，…， B_m B1​，…，Bm​ 是 E 2 E_2 E2​ 的属性，则
    在这里插入图片描述
  • (11) 投影与并的分配律: 设 E 1 E_1 E1​ 和 E 2 E_2 E2​ 有相同的属性名，则
    在这里插入图片描述

查询树的启发式优化

典型的启发式规则：

  • 选择运算应尽可能先做
    • 在优化策略中这是最重要、最基本的一条
  • 把投影运算和选择运算同时进行
    • 如有若干投影和选择运算，并且它们都对同一个关系操作，则可以在扫描此关系的同时完成所有的这些运算以避免重复扫描关系
  • 把投影同其前或其后的双目运算结合起来
  • 把某些选择同在它前面要执行的笛卡尔积结合起来成为一个连接运算
  • 找出公共子表达式
    • 如果这种重复出现的子表达式的结果不是很大的关系,并且从外存中读入这个关系比计算该子表达式的时间少得多，则先计算一次公共子表达式并把结果写入中间文件是合算的
    • 当查询的是视图时，定义视图的表达式就是公共子表达式的情况

关系表达式的优化

  • 输入：一个关系表达式的查询树
  • 输出：优化的查询树
  • 方法：
    • (1) 利用等价变换规则 4 把形如 σ F 1 ∧ F 2 ∧ … ∧ F n ( E ) σ_{F_1∧F_2∧…∧F_n(E)} σF1​∧F2​∧…∧Fn​(E)​ 变换为
      σ F 1 ( σ F 2 ( … ( σ F n ( E ) ) … ) ) σ_{F_1}(σ_{F_2}(…(σ_{F_n}(E))…)) σF1​​(σF2​​(…(σFn​​(E))…))
    • (2) 对每一个选择，利用等价变换规则 4～9 尽可能把它移到树的叶端
    • (3) 对每一个投影利用等价变换规则 3，5，10，11 中的一般形式尽可能把它移向树的叶端
    • (4) 利用等价变换规则 3～5 把选择和投影的串接合并成单个选择、单个投影或一个选择后跟一个投影。使多个选择或投影能同时执行，或在一次扫描中全部完成
    • (5) 把上述得到的语法树的内节点分组。每一双目运算 ( × ， ⋈ ， ∪ ， − ) (×，⋈ ，∪，-) (×，⋈，∪，−) 和它所有的直接祖先为一组 (这些直接祖先是( σ ， π σ，π σ，π 运算)
      • 如果其后代直到叶子全是单目运算，则也将它们并入该组
      • 但当双目运算是笛卡尔积 ( × × ×)，而且后面不是与它组成等值连接的选择时，则不能把选择与这个双目运算组成同一组，把这些单目运算单独分为一组

例

  • 把SQL语句转换成查询树，如下图所示
    在这里插入图片描述
  • 为了使用关系代数表达式的优化法，假设内部表示是关系代数语法树，则上面的查询树如下图所示
    在这里插入图片描述
  • 对查询树进行优化: 利用规则4、6把选择 σ S C . C n o = ‘ 2 ’ σ_{SC.Cno=‘2’} σSC.Cno=‘2’​ 移到叶端
    在这里插入图片描述

物理优化

代数优化比较重要，物理优化了解即可

  • 对于一个查询语句有许多存取方案，它们的执行效率不同，仅仅进行代数优化是不够的. 物理优化就是要选择高效合理的操作算法或存取路径，求得优化的查询计划

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

选择操作的启发式规则

小关系

  • 全表顺序扫描，即使选择列上有索引

大关系

  • 对于选择条件是 主 码 ＝ 值 主码＝值 主码＝值 的查询: 查询结果最多是一个元组，可以选择主码索引
  • 对于选择条件是 非 主 属 性 ＝ 值 非主属性＝值 非主属性＝值 的查询，并且选择列上有索引:
    • 要估算查询结果的元组数目: 如果比例较小( < 10 % <10\% <10%)可以使用索引扫描方法; 否则还是使用全表顺序扫描
  • 对于选择条件是属性上的非等值查询或者范围查询，并且选择列上有索引:
    • 要估算查询结果的元组数目: 如果比例较小( < 10 % <10\% <10%)可以使用索引扫描方法; 否则还是使用全表顺序扫描
  • 对于用 A N D AND AND 连接的合取选择条件
    • 如果有涉及这些属性的组合索引: 优先采用组合索引扫描方法
    • 如果某些属性上有一般的索引: 则可以用索引扫描方法; 否则使用全表顺序扫描
  • 对于用 O R OR OR 连接的析取选择条件，一般使用全表顺序扫描

连接操作的启发式规则

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

基于代价的优化

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

统计信息

  • 基于代价的优化方法要计算各种操作算法的执行代价，与数据库的状态密切相关；数据字典中存储的优化器需要的统计信息：
    • 对每个基本表：该表的元组总数 ( N N N)、元组长度 ( l l l) (即属性值个数)、 占用的块数 ( B B B)、占用的溢出块数 ( B O BO BO)
    • 对基表的每个列：该列不同值的个数 ( m m m)、选择率 ( f f f) (如果不同值的分布是均匀的， f ＝ 1 / m f＝1/m f＝1/m; 如果不同值的分布不均匀，则每个值的选择率＝具有该值的元组数/ N N N)、该列最大值、该列最小值、该列上是否已经建立了索引、索引类型 (B+树索引、Hash索引、聚集索引)
    • 对索引 (如B+树索引)：索引的层数 ( L L L)、不同索引值的个数、索引的选择基数 S S S (有 S S S 个元组具有某个索引值)、索引的叶结点数 ( Y Y Y)

代价估算示例

全表扫描算法的代价估算公式

  • 如果基本表大小为 B B B 块，全表扫描算法的代价 c o s t ＝ B cost＝B cost＝B
  • 如果选择条件是 码 ＝ 值 码＝值 码＝值，那么平均搜索代价 c o s t ＝ B / 2 cost＝B/2 cost＝B/2

索引扫描算法的代价估算公式

  • 如果选择条件是 码 ＝ 值 码＝值 码＝值
    • 若索引为B+树，层数为 L L L，需要存取 B + B+ B+ 树中从根结点到叶结点 L L L 块，再加上基本表中该元组所在的那一块，所以 c o s t = L + 1 cost=L+1 cost=L+1
  • 如果选择条件涉及非码属性
    • 若为B+树索引，选择条件是相等比较， S S S 是索引的选择基数(有 S S S 个元组满足条件)，最坏的情况下，满足条件的元组可能会保存在不同的块上，此时， c o s t = L + S cost=L+S cost=L+S
  • 如果比较条件是 > ， ≥ ， < ， ≤ >，\geq，<，\leq >，≥，<，≤ 操作
    • 假设有一半的元组满足条件就要存取一半的叶结点
    • 通过索引访问一半的表存储块 c o s t = L + Y / 2 + B / 2 cost=L+Y/2+B/2 cost=L+Y/2+B/2
    • 如果可以获得更准确的选择基数，可以进一步修正 Y / 2 Y/2 Y/2 与 B / 2 B/2 B/2

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

  • 如果需要把连接结果写回磁盘，
    c o s t ＝ B r + B s / ( K − 1 ) B r + ( F r s N r N s ) / M r s cost＝B_r+B_s/(K-1) B_r +(F_{rs}N_rN_s)/Mrs cost＝Br​+Bs​/(K−1)Br​+(Frs​Nr​Ns​)/Mrs其中 F r s F_{rs} Frs​ 为连接选择性 (join selectivity)，表示连接结果元组数的比例; M r s Mrs Mrs 是存放连接结果的块因子，表示每块中可以存放的结果元组数目

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

  • 如果连接表已经按照连接属性排好序，则
    c o s t ＝ B r + B s + ( F r s N r N s ) / M r s cost＝B_r+B_s +(F_{rs}N_rN_s)/Mrs cost＝Br​+Bs​+(Frs​Nr​Ns​)/Mrs
  • 如果必须对文件排序，需要在代价函数中加上排序的代价；对于包含 B B B 个块的文件排序的代价大约是
    ( 2 B ) + ( 2 B log ⁡ 2 B ) (2B)+(2B\log_2B) (2B)+(2Blog2​B)


这篇关于数据库系统原理 (九): 关系查询处理及其查询优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！


相关编程文章

更多>
本站官方QQ
官方QQ:
23002807

沪ICP备2023033072号-1

     

扫一扫关注最新编程教程