第4章关系系统及其查询优化

假设学生～课程数据库有1000个Student记录、10000个SC记 记录、 假设学生～课程数据库有 个 记录 个 记 其中选修2号课程的选课记录为 号课程的选课记录为50条 对于上述SQL查询 录，其中选修 号课程的选课记录为 条。对于上述 查询 可以转化为如下几种等价的关系代数表达式： ，DBMS可以转化为如下几种等价的关系代数表达式： 可以转化为如下几种等价的关系代数表达式
数据库原理及应用
6
关系完备的系统： 关系完备的系统：支持关系数据结构和所有的 关系代数操作。 关系代数操作。如Oracle 7.X、 Sybase SQL 、 SERVER 10等90年代的许多 年代的许多RDBMS。 等 年代的许多 。 全关系系统：支持关系模型的所有特征。如目 全关系系统：支持关系模型的所有特征。 前大多数主流关系系统产品： 前大多数主流关系系统产品：Oracle 9i/10g/11i、Sybase Adaptive Server 12、 、 、 MicroSoft SQL SERVER 2003/2005等已不同 等已不同 程度上接近或达到了这个目标。 程度上接近或达到了这个目标。
数据库原理及应用
9
查询优化的必要性 一个实例： 求选修2号课程的学生姓名 号课程的学生姓名， 一个实例 ： 求选修 号课程的学生姓名 ， 用 SQL语言表达为 语言表达为 ：
SELECT Student.Sname FROM Student, SC WHERE Student.Sno=SC.Sno AND o='2';
数据库原理及应用
13
执行策略4： бo=‘2’ (SC))，假设 表 执行策略 ：ПＳname(Student ，假设SC表 上有索引， 表在Sno上有索引。 上有索引。 在Cno上有索引，Student表在 上有索引 表在 上有索引 作选择操作 根据SC表索引读取 表总块数 根据 表索引读取SC表总块数 表索引读取 表总块数=50/100<1块，共计时间 块 共计时间<1/20 因满足条件的元组只有50个 秒。因满足条件的元组只有 个，不必使用中间文件写入外存 作自然连接 根据Student表索引 ， 读 Student表总块数 根据 表索引， 表总块数=50/10=5块 ， 共计 块 表索引 表总块数 时间=5/20秒。 时间 秒 作投影操作 时间可以忽略。 时间可以忽略。 结论：查询总时间 秒 结论：查询总时间<1秒。
由于执行策略不同，查询的时间相差很大。 由于执行策略不同，查询的时间相差很大。
数据库原理及应用
10
执行策略1： 执行策略 ：ПＳname(бStudent.Sno=SC.Sno ∧o='2' (Student×SC)) × 计算广义笛卡尔积： 的每个元组连接起来。 计算广义笛卡尔积：把Student和SC的每个元组连接起来。 和 的每个元组连接起来 方法是：在内存中尽可能多地装入某个表(如 的若干块元组， 方法是：在内存中尽可能多地装入某个表 如Student表)的若干块元组，留 表 的若干块元组 出一块存放另一个表(如 表 的元组 然后把SC中的每个元组和 的元组。 中的每个元组和Student 出一块存放另一个表 如SC表)的元组。然后把 中的每个元组和 中每个元组连接，连接后的元组装满一块后就写到中间文件上，再从SC中 中每个元组连接，连接后的元组装满一块后就写到中间文件上，再从 中 读入一块和内存中的Student元组连接，直到 表处理完。再次读入若干 元组连接， 表处理完。 读入一块和内存中的 元组连接 直到SC表处理完 元组， 元组， 块Student元组，读入一块 元组，重复上述处理过程至 元组 读入一块SC元组 重复上述处理过程至Student表处理完 表处理完 假设一个内存块能装10个 元组或100个SC元组， 内存中一次可以 元组， 假设一个内存块能装 个 Student元组或 元组或 个 元组 存放5块 元组和1块 元组及若干块连接结果元组 元组及若干块连接结果元组， 存放 块Student元组和 块SC元组及若干块连接结果元组，则 元组和 读取总块数= 表遍数× 读取总块数 读Student表块数 + 读SC表遍数×每遍块数 表块数 表遍数 =1000/10+(1000/(10×5)) ×(10000/100) =2100 × 若每秒读写20块 则读块总计时间=2100/20=105秒。连接后的元组数为 若每秒读写 块，则读块总计时间 秒 7 1000*10000 = 10 ，设每块能装 个这样的元组，则写出这些块总计时间 设每块能装10个这样的元组 个这样的元组， 7 = 10 /10/20 = 50000秒。 秒 作选择操作：忽略内存处理时间，这一步共计时间同写中间文件50000秒 作选择操作：忽略内存处理时间，这一步共计时间同写中间文件 秒 作投影操作： 作投影操作：时间可以忽略 数据库原理及应用 11 结论： 小时。 结论：查询总时间 =(105＋50000＋50000)秒 = 100105秒 = 27.8小时。 ＋ ＋ 秒 秒 小时
数据库原理及应用
Principle and Application of Database
第四章 关系系统及其查询优化
数据库原理及应用
1
学习目标
掌握关系系统的定义和分类 掌握关系系统查询优化的总目标和步骤 掌握关系系统查询优化的总目标和步骤 理解关系系统查询优化的一般准则
数据库原理及应用
2
4.1
数据库原理及应用
8
查询优化的目标：选择有效策略，求得给定关系表达式的值。 查询优化的目标：选择有效策略，求得给定关系表达式的值。 实际系统查询优化的步骤 将查询转换成某种内部表示，通常是语法树。 将查询转换成某种内部表示，通常是语法树。 根据一定的等价变换规则把语法树转换成标准(优化 形式。 优化)形式 根据一定的等价变换规则把语法树转换成标准 优化 形式。 选择低层的操作算法。对于语法树中的每一个操作， 选择低层的操作算法。对于语法树中的每一个操作，计算各种 执行算法的执行代价，选择代价小的执行算法。 执行算法的执行代价，选择代价小的执行算法。 生成查询计划(查询执行方案 由一系列内部操作组成的。 查询执行方案)： 生成查询计划 查询执行方案 ：由一系列内部操作组成的。 代价模型 集中式数据库： 代价； 集中式数据库：单用户系统的总代价 = I/O代价 + CPU代价； 代价 代价 多用户系统的总代价 = I/O代价 + CPU代价 + 内存代价 代价 代价 分布式数据库： 代价[+ 分布式数据库：总代价 = I/O代价 + CPU代价 内存代价 + 代价 代价 内存代价] 通信代价
数据库原理及应用
4
关系系统定义的三个问题
为什么关系系统除了要支持关系数据结构外， 为什么关系系统除了要支持关系数据结构外， 还必须 支持选择、投影和连接运算呢？ 支持选择、投影和连接运算呢？ 为什么要求这三种运算不能依赖于物理存取路径呢？ 为什么要求这三种运算不能依赖于物理存取路径呢？ 为什么要求关系系统支持这三种最重要的运算而不是 关系代数的全部运算功能呢？ 关系代数的全部运算功能呢？
执行策略1： 执行策略 ：ПＳname(бStudent.Sno=SC.Sno ∧o='2' (Student×SC)) × 执行策略2： SC)) 执行策略 ：ПＳname(бo=' 2' (Student 执行策略3： бo=' 2' (SC)) 执行策略 ：ПSname(Student 执行策略4： бo=‘2’ (SC))，假设 表在 表在Cno上有索 执行策略 ：ПＳname(Student ，假设SC表在 上有索 表在Sno上有索引。 上有索引。 引，Student表在 表在 上有索引
数据库原理及应用
5
关系系统的分类
按照E.F.CODD的思想，依据系统支持关系模型的 的思想， 按照 的思想 程度，可分成四类： 程度，可分成四类： 表式系统：仅支持关系(即表 数据结构，不支持集合 即表)数据结构 表式系统：仅支持关系 即表 数据结构， 级的操作，如倒排表列系统，它不能算作关系系统。 级的操作，如倒排表列系统，它不能算作关系系统。 (最小 关系系统：如前面定义所述，支持关系数据结 最小)关系系统 如前面定义所述， 最小 关系系统： 构和选择、投影、连接三种关系操作， 构和选择、投影、连接三种关系操作，如FoxBASE 、FoxPro等。 等
数据查询优化
查询优化概述： 关系查询优化是影响RDBMS性能的关键因 查询优化概述 ： 关系查询优化是影响 性能的关键因 由于关系表达式的语义级别很高， 素 ， 由于关系表达式的语义级别很高， 使关系系统可以从关 系表达式中分析查询语义，提供了执行查询优化的可能性。 系表达式中分析查询语义，提供了执行查询优化的可能性。 查询优化的优点： 查询优化的优点 ：用户不必考虑如何最好地表达查询就可以 获得较好的效率，系统优化比用户程序优化做得更好， 获得较好的效率 ， 系统优化比用户程序优化做得更好， 这是 因为： 因为： 优化器可以从数据字典中获取许多统计信息，而用户程序则 优化器可以从数据字典中获取许多统计信息， 难以获得这些信息 。 如果数据库的物理统计信息改变了， 如果数据库的物理统计信息改变了，系统可以自动对查询重 新优化以选择相适应的执行计划。 新优化以选择相适应的执行计划。在非关系系统中必须重写 程序，而重写程序在实际应用中往往是不太可能的。 程序，而重写程序在实际应用中往往是不太可能的。 优化器可以考虑数百种不同的执行计划， 优化器可以考虑数百种不同的执行计划，而程序员一般只能 考虑有限的几种可能性。 考虑有限的几种可能性。 优化器中包括了很多复杂的优化技术。 优化器中包括了很多复杂的优化技术。
执行策略2： 执行策略 ：ПＳname(бo=' 2' (Student 作自然连接
SC))
为了执行自然连接，读取Student和SC表的策略不变，读取总 表的策略不变， 为了执行自然连接，读取 和 表的策略不变 块数2100， 共计时间 块数 ， 共计时间105秒 。 但自然连接的结果为 秒 但自然连接的结果为10000， 比 ， 策略1大大减少了 写出这些元组时间为10000/10/20=50秒。 大大减少了， 策略 大大减少了，写出这些元组时间为 秒 作选择操作 读取中间文件块，作选择运算共计时间 秒 读取中间文件块，作选择运算共计时间50秒。 作投影操作 时间可以忽略。 时间可以忽略。 结论：查询总时间＝ 分钟。 结论：查询总时间＝(105＋50＋50)秒＝205秒=3.4分钟。 ＋ ＋ 秒 秒 分钟