第九章 关系系统及其查询优化
第09章 关系查询处理和查询优化
连接操作的实现
student sc
200215121 200215122 200215123
200215121 1 92 200215121 2 85 200215121 3 88 200215122 2 90
200215124 . . .
200215122 3 80
. . .
排序-合并连接方法示意图
=
5秒
• Q3: 10秒
• Q4: <10秒
二、优化的一般策略
1、选择、投影运算应尽可能先做 好处：减少下一步运算的数据量 2、把选择和投影运算同时进行 好处：减少扫描关系的次数 SN SD=‘CS’ ）） （ （S 表S S# S1 S2 S3 S4 SN SD SA A CS 20 B CS 21 C MA 19 D CI 22
对这一查询，可以考虑下面几种实现方式： 1、先求S和SC的笛卡尔积，然后从中选出两学号字段值相等、
课程号为C2的元组： Q1 = SN （ S.S#=SC.S# SC.C#=‘C2’ （SSC））
2、先做S和SC的自然连接，然后从中选出课程号为C2的元组： Q2 = （ （S SN SC.C#=‘C2’ SC））
3、先从SC中选出课程号为C2的元组，然后将该结果与S 连接： Q3 = （S SN （SC）） SC.C#=‘C2’
系统假设:
– 元组数：student 1000个, SC 10000个，其中有关2号
课程的50个 – 内存数：5个内存块用于装student, 1个内存块用于装 SC – 块大小：一个内存块可装10个student元组(或10个 student与SC笛卡尔积元组)，或100个SC元组 – 读写速度: 20块/秒 (读写速度一样) – 连接方法：基于数据块的嵌套循环法 – 连接后的元组装满一块后就写到中间文件上
第九章 关系查询处理和查询优化
精品课件！
精品课件！
关系系统的查询优化:
优化:
Sname Sname
Sname Sname
c
c’
c’’
Sname
c
p
SP P S
p
...
索引连接(在SC 的连接列Sno上 建立索引):
95004… 95002... 95003... 95001… ...
...
关系系统的查询优化:
把投影运算和选择运算同时进行 sno (cno=‘2’(SC)) 把投影和其前或后的双目运算结合起来 Cname(Course SC)
把某些选择同在它前面要执行的笛卡尔积 结合起来成为一个连接运算 Students.Sno=SC.Sno and Cno=‘2’(StudentsSC) 找出公共子表达式
Students 95004… 95002... 95003... 95001… ...
...
95003 1 … 95003 2 … 95004 2 ... 95004 3 ... 95001 1 … ...
关系系统的查询优化:
排序合并连接(连 接的关系分别排 序): Students 95001… 95002... 95003... 95004… ... Students SC 95001 1 … 95003 1 … 95003 2 … 95004 2 ... 95004 3 ... ... SC索引 95001 95003 95003 95004 95004 ... SC 95003 1 … 95003 2 … 95004 2 ... 95004 3 ... 95001 1 … ...
23页
关系系统的查询优化:
第9章-关系查询处理和查询优化
An Introduction to Database System
查询处理步骤（续）
查询处理步骤
An Introduction to Database System
1. 查询分析
❖ 对查询语句进行扫描、词法分析和语法分析 ❖ 从查询语句中识别出语言符号 ❖ 进行语法检查和语法分析
• 试探阶段(probing phase)：也称为连接阶段(join phase)
– 对另一个表(S)进行一遍处理 – 把S的元组散列到适当的hash桶中 – 把元组与桶中所有来自R并与之相匹配的元组连接起来
An Introduction to Database System
连接操作第一阶段后可以完全放入内存的hash桶中
An Introduction to Database System
2. 查询检查
❖ 根据数据字典对合法的查询语句进行语义检查 ❖ 根据数据字典中的用户权限和完整性约束定义对用户的存
取权限进行检查 ❖ 检查通过后把SQL查询语句转换成等价的关系代数表达式 ❖ RDBMS一般都用查询树(语法分析树)来表示扩展的关系
An Introduction to Database System
一、 选择操作的实现
❖［例1］Select * from student where <条件表达 式> ； 考虑<条件表达式>的几种情况：
C1：无条件； C2：Sno＝'200215121'； C3：Sage>20； C4：Sdept＝'CS' AND Sage>20；
数据库原理九
关系查询优化的总目标是：选择有效的策略，求得给定 关系表达式的值。 查询优化一般的实现步骤： 1. 将查询转换为某种内部表示，通常是语法树。 2. 根据一定的等价变换规则把语法树转换成标准优化形 式。 3. 选择底层的操作算法。 4. 生成查询计划。 查询计划也称为查询执行方案，是由一系列的内部操 作组成的。这些内部操作按一定的次序构成查询的一个执 行方案。通常这样的方案有多个，需要对每个方案计算执 行代价，选择代价最小的一个。在集中式数据库中，代价 主要考虑磁盘读写的I/O次数，也有一些考虑CPU的处理时 主要考虑磁盘读写的I/O次数，也有一些考虑CPU的处理时 间。
9.2 关系数据库系统的查询优化
查询优化在数据库系统中有着非常重要的地位。关系 数据库系统和非过程化的SQL语言能够取得巨大的成功，关 数据库系统和非过程化的SQL语言能够取得巨大的成功，关 键得益于查询优化技术的发展。关系查询优化是影响关系数 据库管理系统性能的关键。 优化对于关系系统来说既是挑战，又是机遇。挑战是指 为了达到用户接受的性能必须进行查询优化。机遇是指关系 系统可以从关系表达中分析查询语义，提供了执行查询优化 的可能性。这就为关系系统在性能上接近甚至超过非关系系 统提供了机遇。
π
A1, A2, …, An
(πB1, B2, …, Bm(E)) ==
π
A1, A2, …, An
(E)
4. 选择的串接定律
σF1(σF2(E )) == σF1^F2(E)
5. 投影与选择的交换律
σF(πA1, A2, …, An(E)) == πA1, A2, …, An(σF(E))
如果F中有不属于A1，A2， An的属性B1，B2， 如果F中有不属于A1，A2，… An的属性B1，B2，… ，Bm，则有： Bm，则有： (σF(E)) == πA1, A2, …, An(σF(πA1, A2, …, An, B1, B2, …, Bm(E))) 6. 选择与笛卡尔积的交换律 如果F中的属性都是E1中的属性。 如果F中的属性都是E1中的属性。 σF(E1 × E2) == σF(E1) × E2 如果F 如果F = F1 ^ F2，F1只涉及E1中的属性，F2只涉及 F2，F1只涉及E1中的属性，F2只涉及 E2中的属性，则： E2中的属性，则：
第9章关系查询处理和查询优化
6/14
ПSname(Student.Sno=SC.Sno∧Cno='2'(Student×SC)) 笛卡尔积：假设内存中一次可存放5块Student元组、1块SC元 组及若干块连接元组，一块能装10个Student元组或100个SC 元组或10个连接元组，每秒读写200块，则： 读块数=读Student表块数+读SC表遍数×每遍块数 =1000/10+(1000/(10×5))*(10000/100)=2100=10.5秒。 连接元组数=1000*10000，写块时间=107/10/200=5000秒。 选择：读中间块，作选择运算时间=5000秒。 投影：时间可以忽略。 结论：查询总时间=(10.5+5000×2)秒=10010.5秒=2.78小时。
查询树 结果 project(Sname) 关系代数语法树
∏Sname
Cno=2
Student.Sno=SC. Sno × Student
10/14
优化后的查询树
∏Sname
Student.Sno=SC.Sno × Cno=2 SC
select(Cno=2)
join(Student.Sno=SC.Sno) Student SC
9/14
9.3
代数优化
代数优化的概念：通过等价变换规则(见教材P270)改变查询语 句的操作顺序。 查询树的启发式优化 选择运算尽可能先做。这是最重要、最基本的一条。 同一关系的投影和选择运算可以同时进行。 将投影与其前后的双目运算结合，以减少扫描关系的遍数。 将某些选择与其前面的笛卡尔积组合成一个连接运算。
Student SC
9.4
物理优化
物理优化的概念：选择高效合理的操作算法或存取路径。 启发式优化 选择操作：对小表使用全表顺序扫描。对大表则有如下规则： 若选择条件是主码=值，则用主码索引。 若选择条件是非主属性(已建索引)=值，且估算查询结果的比例 <10%，则用索引扫描，否则用全表顺序扫描。 连接操作 若两个表都已按连接属性排序，则用排序合并。 若一个表在连接属性上有索引，则用索引连接。 若以上规则不适用且一表较小，则用散列连接。 最后选用小表作为外循环表的嵌套循环。 设Br<Bs，则有Br+Bs×Br/k＜Bs+Bs×Br/k。
9数据库系统原理 第九章 关系查询处理和查询优化
9.1.2实现查询操作的算法示例
• 一、选择操作的实现
–简单的全表扫描方法 •对查询的基本表顺序扫描，逐一检查每个 元组是否满足选择条件。 •简单，但对大表进行扫描时效率偏低。 –索引扫描方法 •通过索引先找到满足条件的元组主码或元 组指针。 •通过元组指针直接在查询的基本表中找到 元组。
一、选择操作的实现－索引扫描方法
9.3.1关系代数表达式等价变换规则
• 6.选择与笛卡儿集的交换律（*）
–假设：F中涉及的属性都是E1中的属性 σF（ E1×E2）≡ σF （ E1 ） ×E2 –假设：F=F1∧F2，并且F1只涉及E1中的属性， F2只涉及E2中的属性 则由上面的等价变换规则1，4，6可推出： σF（ E1×E2）≡ σF 1（ E1 ） × σF 2 （E2 ） –假设： F=F1∧F2 ，并且F1只涉及E1中的属性， F2涉及E1和E2两者的属性 σF（ E1×E2）≡ σF
二、连接操作的实现
•
例2: SELECT * FROM student S,SC WHERE S.sno=SC.sno –嵌套循环方法 • 对外层循环的每一个元组，检索内层循环中的每一个元组， 并检查这两个元组在连接属性上是否相等。若满足，则串接 后输出，直到外层循环表中的元组处理完毕。 –排序－合并方法 • 如果连接的表没有排序，首先对两个表按连接属性排序； • 取S表中第一个sno，一次扫描SC表中具有相同sno的元组， 把它们连接起来； • 当扫描到sno不相同的第一个SC元组时，返回S表扫描它的下 一个元组，再扫描SC表中具有相同sno的元组，把它们连接 起来； • 重复上述2个步骤，直到S表扫描完毕。
二、连接操作的实现
例2: SELECT * FROM student S, SC WHERE S.sno=SC.sno –索引连接方法 •在SC表上建立属性sno的索引； •对Student表中每一个元组，由sno值通过SC的索引 查找相应的SC元组； •把这些SC元组和Student表中的元组处理完为止； •重复上述2个步骤，直到Student表扫描完毕。
9查询优化
9.2 关系数据库系统的查询优化
9.2.1 关系系统及其查询优化
9.2.2 一个实例 9.2.3 查询优化的一般准则 9.2.4 关系代数等价变换规则 9.2.5 关系代数表达式的优化算法 9.2.6 优化的一般步骤
14
9.2 关系数据库系统的查询优化
9.2.1 关系系统及其查询优化
6
9.1.1关系系统的定义
说明 – 除了要支持关系数据结构外，还必须支持选择、投 影、连接运算，用户使用才方便，才能提高用户的 生产率。提高用户生产率正是关系系统的主要目标 之一。 – 依赖物理存取路径来实现关系运算就降低或丧失了 数据的物理独立性。不依赖物理存取路径来实现关 系运算就要求关系系统自动地选择路径。为此系统 要进行查询优化，以获得较好的性能。这正是关系 系统实施的关键技术。
34
9.2 关系数据库系统的查询优化
9.2.1 关系系统及其查询优化
9.2.2 一个实例 9.2.3 查询优化的一般准则 9.2.4 关系代数等价变换规则 9.2.5 关系代数表达式的优化算法 9.2.6 优化的一般步骤
35
9.2.6 优化的一般步骤
把查询转换成某种内部表示。
18
9.2.1 关系系统及其查询优化
– 生成查询计划（查询执行方案）。通常执行 方案有多个，需要对每个执行计划计算代价， 从中选择代价最小的一个。
目前的商品化RDBMS大都采用基于代价
的优化算法 总代价＝I/O代价＋CPU代价＋内存代价
19
9.2 关系数据库系统的查询优化
9.2.1 关系系统及其查询优化
代数表达式的优化是查询优化的基本课题。研 究关系代数表达式的优化最好从研究关系代数 表达式的等价变换规则开始。 所谓关系代数表达式的等价是指用相同的关系 代替两个表达式中相应的关系所得到的结果是 相同的。 两个关系表达式E1和E2等价，记为E1＝E2。
第9章 关系查询优化
（2）索引（或散列）扫描方法：选择条件中的属性 上有索引，通过索引先找到满足条件的元组主码或 元组指针，通过指针直接在基本表中找到元组。 C2：在Sno上有索引 C3：在Sage上有索引,B+索引，先找到＝20的索引， 找到＞20的所有元组。 C4：在Sdept和Sage上都有索引，法一用上述两种 方法求交集，法二，找‘CS’的一组指针，到 student中对这些元组检查另一选择条件＞20。
（6）选择与笛卡尔积的交换律
F中涉及的都是E1中的属性 σF(E1 ×E2) ≡ σF(E1 ）× E2 F=F1×F2,F1只涉及E1的属性，F2只涉及E2的属性
σF(E1 × E2) ≡ σF1(E1 ）× σF2(E2 ）
F1只涉及E1的属性，F2涉及E1和E2的属性 在笛卡尔积之前做。
σF(E1 × E2) ≡ σF2（σF1 (E1 ）× E2 ）使部分选择
（3）查询优化： 查询优化的有多种方法，按照优化的层次分 为代数优化和物理优化。代数优化是指关系代数 表达式的优化，按照一定的规则，改变代数表达 式中操作的次序和组合，使查询执行更高效。物 理优化则是指存取路径和底层操作算法的选择。 选择的依据可以是基于规则的、基于代价的和基 于语义的。 （4）查询执行：生成并选择最低代价的查询计划 依据优化器得到的执行策略生成查询计划， 由代码生成器生成执行这个查询计划的代码。
9.4 物理优化
代数优化改变查询语句中操作的次序和组合， 不涉及底层的存取路径。物理优化就是要选择高 效合理的操作算法或存取路径，求得优化的查询 计划，达到查询优化的目标。选择的方法： （1）基于规则的启发式优化。针对大多数情况， 不是对每种情况。 （2）基于代价估算的优化。优化器选择具有最小 代价的执行计划。 （3）两者结合的优化方法。一般先使用启发式规 则，选取若干好的候选方案，减少代价估算的工 作量，再分别计算这些候选方案的执行代价，选 出最优方案。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
一单项选择题
1 设E是关系代数表达式，F1，F2是选取条件表达式，则有______。
A σF1（σF2（E））≡σF1∨F2（E）
B σF1（σF2（E））≡σF1∧F2（E）
C σF1（σF2（E））≡σF1（E）
D σF1（σF2（E））≡σF2（E）
2 设E是关系代数表达式，F是选取条件表达式，并且只涉及A1，…，An属性，则有____。
A σF（πA1，…，An（E））≡πA1，…，An（σF（E））
B σF（πA1，…，An（E））≡πA1，…，An（E）
C σF（πA1，…，An（E））≡πA1（σF（E））
D πA1，…，An（σF（E））≡πA1，…，An（σF（πA1，…，An，B1，…Bm（E）））
3 如果条件F形为F1∧F2，F1仅涉及到E1中的属性，F2仅涉及到E2中的属性，则有_____。
A σF（E1×E2）≡σF1（E1）×σF2（E2）
B σF（E1×E2）≡σF1（σF1（E1）×σF2（E2））
C σF（E1×E2）≡σF2（σF1（E1）×σF2（E2））
D σF（E1×E2）≡σF1（E2）×σF2（E1）
4 如果一个系统定义为关系系统，则它必须___________。
A 支持关系数据库
B 支持选择、投影和连接运算
C A和B均成立
D A和B都不需要
5 如果一个系统为表式系统，则它支持__________。
A 关系数据结构
B A与选择、投影和连接运算
C A与所有的关系代数操作
D C与实体完整性、参照完整性
6 如果一个系统为关系完备系统，那么它支持___________。
A 关系数据结构
B A与选择、投影和连接运算
C A与所有的关系代数操作
D C与实体完整性、参照完整性
7 如果一个系统为全关系系统，那么它支持___________。
A 关系数据结构
B A与选择、投影和连接运算
C A与所有的关系代数操作
D C与实体完整性、参照完整性
8 关系代数表达式的优化策略中，首先要做的是__________。
A 对文件进行预运算
B 尽早执行选择运算
C 执行笛卡尔积运算
D 投影运算
9 在关系代数运算中，最费时间和空间的是_________________。
A 选择和投影运算
B 除法运算
C 笛卡尔积和连接运算
D 差运算
10 在关系代数表达式的等价优化中，不正确的叙述是____________。
A 尽可能早的执行连接运算
B 尽可能早的执行选择运算
C 尽可能早的执行投影运算
D 把笛卡尔积和随后的选择合并成连接运算
11 根据系统所提供的存取路径，选择合理的存取策略，这种优化方式称为__________。
A 物理优化
B 代数优化
C 规则优化
D 代价估算优化
二填空题
1 关系系统的查询优化既是关系数据库管理系统实现的关键技术，又是关系系统的优点。
因为用户只要提出_____________，不必指出______________。
2 在关系代数运算中，___________和__________________运算最费时间和空间。
究竟应采用什么样的策略才能节省时间和空间，这就是优化的准则。
3 在优化算法中，首先应将形如σF1∧F2∧…∧Fn（E）变换为___________________________。
4 在RDBMS中，通过某种代价模型计算各种查询的执行代价。
在集中式数据库中，查询的执行开销主要包括________________和_________________代价。
在多用户数据库中，还应考虑查询的内存代价开销。
三问答题
1 为什么要对关系代数表达式进行优化？
答：
2 代数查询优化的总目标是什么？
答：代数查询优化的总目标是：选取有效地存取路径，求得给定关系代数表达式的值。
3 有哪3条启发式优化规则？对优化起什么作用？
答：3条启发式优化规则是：尽可能早的执行选择运算；尽可能早的执行投影运算；把笛卡尔积与附近的一连串选择和投影合并起来做。
使用这3条启发式优化规则可以使计算时尽可能减少中间关系的数据量。
4 查询优化有哪些途径？
答：查询优化有多种途径。
一种途径是对查询优化语句进行变换，例如改变基本运算的次序，使查询语句执行起来更有效。
这种查询优化方法仅涉及查询语句本身，而不涉及存取路径，称为独立于存取路径的优化，或代数优化。
查询优化的另一种途径是根据系统所提供的存取路径，选择合理的存取策略，这称为依赖于存取路径的优化，或物理优化。
四应用题
1 设教学数据库有3个关系：
S（S#，SNAME，AGE，SEX）
C（C#，CNAME，TEACHER）
SC（S#，C#，GRADE）
用户有一查询语句：检索不学“数据库”课程的学生姓名与年龄。
（1）试写出该查询的关系代数表达式；
（2）试写出查询优化的关系代数表达式；
（3）画出该查询初始的关系代数表达式的语法树；
（4）使用优化算法，对语法树进行优化，并画出优化后的语法树。
2 在上题的教学数据库S，C和SC中，用户有一查询语句：检索女同学选修课程的课程名和任课教师名。
（1）试写出该查询的关系代数表达式；
（2）试写出经查询优化的关系代数表达式。
（3）画出该查询初始的关系代数表达式的语法树；
（4）使用优化算法，对语法树进行优化，并画出优化后的语法树。
3已知一个关系数据库的模式如下：
S（SNO，SNAME，SCITY）
P（PNO，PNAME，COLOR，WEIGHT）
J（JNO，JNAME，JCITY）
SPJ（SNO，PNO，JNO，QTY）
其中，S表示供应商，它的各属性依次为：供应商号、供应商名和供应商所在城市；P 表示零件，它的各属性依次为：零件号、零件名、零件颜色和零件重量；J表示工程，它的各属性依次为：工程号、工程名和工程所在城市；SPJ表示供货关系，它的各属性依次为：
供应商号、零件号、工程号和供货数量。
用户有一查询语句：检索使用上海供应商生产的红色零件的工程号。
（1）试写出该查询的关系代数表达式；
（2）试写出经查询优化的关系代数表达式。
（3）画出该查询初始的关系代数表达式的语法树；
（4）使用优化算法，对语法树进行优化，并画出优化后的语法树。
4 图书馆数据库中有如下关系：
BOOKS（Title，Author，Pname，Bno）
PUBLISHERS（Pname，Paddr，Pcity）
BORROWERS（Name，Addr，City，Cno）
LOANS（Cno，Bno，Date）
其中，BOOKS为图书表，对应的各个属性依次为：书名、作者名、出版社名和图书编号；PUBLISHERS为出版社表，对应的各个属性依次为：出版社名、出版社地址和出版社所在城市名；BORROWERS为借书人，对应的各个属性依次为：借书人姓名、借书人地址、借书人所在城市名和借书证号；LOANS为借书表，对应的各个属性依次是借书证号、图书编号和图书借出日期。
若查询“2003-10-1”以前借出的书籍的书名和借书人姓名，请说明优化的过程。