数据库系统概论第9章关系查询处理和查询优化

还可以使用并集来避免顺序存取。 还可以使用并集来避免顺序存取。
尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用 尽管在所有的检查列上都有索引，但某些形式的 子句强迫优化器使用 顺序存取。下面的查询将强迫对orders表执行顺序操作： 表执行顺序操作： 顺序存取。下面的查询将强迫对 表执行顺序操作 SELECT ＊ FROM orders WHERE customer_num=104 OR order_num=1008 虽然在customer_num和order_num上建有索引，但是在上面的语句中优 上建有索引， 虽然在 和 上建有索引 化器还是使用顺序存取路径扫描整个表。 化器还是使用顺序存取路径扫描整个表。 因为这个语句要检索的是分离的行的集合，所以应该改为如下语句： 因为这个语句要检索的是分离的行的集合，所以应该改为如下语句： SELECT ＊ FROM orders WHERE customer_num=104 UNION SELECT ＊ FROM orders WHERE order_num=1008 这样就能利用索引路径处理查询。 这样就能利用索引路径处理查询。
几种改善用户查询计划的解决方案。 几种改善用户查询计划的解决方案。
2011-1-14 ftt@ 11
1．合理使用索引 ．
索引是数据库中重要的数据结构， 索引是数据库中重要的数据结构，它的根本目的就是为了提 高查询效率。现在大多数的数据库产品都采用IBM最先提出 高查询效率。现在大多数的数据库产品都采用 最先提出 索引结构。 的ISAM索引结构。索引的使用要恰到好处，其使用原则如 索引结构 索引的使用要恰到好处， 下：
这种算法循环执行次数与第一种算法相同，也是 × ＝ 这种算法循环执行次数与第一种算法相同，也是3×6＝18 次。 但是形成的临时表的空间只有6行 但是形成的临时表的空间只有 行，6列。大大提高了空间 列 效率。 效率。
然后对临时表做选择sname=‘李明’和投影选择列 李明’ 然后对临时表做选择 李明 score。 。 2011-1-14 ftt@ 7
2011-1-14 ftt@ 13
3．消除对大型表行数据的顺序存取 ．
在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。 在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。 比如采用顺序存取策略，一个嵌套3层的查询 如果每层都查询1000行， 层的查询， 比如采用顺序存取策略，一个嵌套 层的查询，如果每层都查询 行 那么这个查询就要查询10亿行数据 亿行数据。 那么这个查询就要查询 亿行数据。
然后在临时表上做选择， 然后在临时表上做选择，条件是 student.sno=sc.sno and sname=‘李明’ 李明’ 李明 结果从临时表上选出3条记录 条记录。 结果从临时表上选出 条记录。 作后在3条记录形成的临时表上投影 条记录形成的临时表上投影， 作后在 条记录形成的临时表上投影，即选择列 score。 。
2011-1-14 ftt@ 14
4．避免相关子查询 ．
5
1.对应的关系代数表达式中 对应的关系代数表达式中 首先做student和sc的笛卡儿积（无条件连接）形 首先做 和 的笛卡儿积（无条件连接） 的笛卡儿积 成一个临时表
student有3条记录，sc有6条记录。执行连接操作 ×6 有 条记录 条记录， 有 条记录 执行连接操作3× 条记录。 ＝18次（占用的时间） 次 占用的时间） 连接后形成的表有18行 连接后形成的表有 行，7列（占用空间） 列 占用空间）
2011-1-14 ftt@ 12
2．避免或简化排序 ．
应当简化或避免对大型表进行重复的排序。 应当简化或避免对大型表进行重复的排序。当能够 利用索引自动以适当的次序产生输出时， 利用索引自动以适当的次序产生输出时，优化器就 避免了排序的步骤。以下是一些影响因素： 避免了排序的步骤。以下是一些影响因素：
2011-1-14
李明 张海 刘宏
21 20 22 现代汉语 大学英语 社会学
计算机系 外语系 数学系
c001 c001 c001 c002 c003 c003
ft 90
4
语法分析
要求查询姓名是‘李明’的学生选修的所有课程的成 要求查询姓名是‘李明’ 绩。 SQL语句： 语句： 语句 select score from student,sc where student.sno=sc.sno and sname='李明‘ 李明‘ 李明 多种等价的关系代数表达式： 多种等价的关系代数表达式：
2011-1-14 ftt@ 8
DBMS查询优化的步骤 查询优化的步骤
step1：把查询转换成一种计算机内部表示结构（语法树） ：把查询转换成一种计算机内部表示结构（语法树） step2代数优化：利用关系代数等价变换规则以及查询优化 代数优化： 代数优化 的一般策略， 的一般策略，将语法树进行优化 优化策略： 优化策略： 一元选择首先做；投影，选择同时做；乘积选择合并做； 一元选择首先做；投影，选择同时做；乘积选择合并做；索 引排序预先做。 引排序预先做。 关系代数的等价变换规则 step3物理优化：选择适当的底层存取路径 物理优化： 物理优化 step4：生成一组查询计划，从中选择一个代价最小的计划 ：生成一组查询计划，
2011-1-14 ftt@ 2
示例
数据库关系模式是： 数据库关系模式是： student(sno,sname,age,dno) course(cno,cname) sc(sno,cno,score)
2011-1-14
ftt@
3
假如数据库中对应的3个表格如下： 假如数据库中对应的 个表格如下： 个表格如下 表student 99000011 99000021 99000031 表course c001 c002 c003 表sc 9900011 9900021 9900031 9900011 9900011 9900021
9900011 9900011 9900011 李明 李明 李明 21 21 21 计算机系 计算机系 计算机系 c001 c002 c003 95 90 80
这个自然连接操作循环次数为1*6次 次 这个自然连接操作循环次数为 形成的临时表为3行 形成的临时表为 行，6列 列
最后在做投影，选择列 最后在做投影，选择列score。 。 显然最后一种算法的时间和空间效率最优。 显然最后一种算法的时间和空间效率最优。
索引中不包括一个或几个待排序的列； 索引中不包括一个或几个待排序的列； group by或order by子句中列的次序与索引的次序不一 或 子句中列的次序与索引的次序不一 样； 排序的列来自不同的表。 排序的列来自不同的表。 为了避免不必要的排序，就要正确地增建索引， 为了避免不必要的排序，就要正确地增建索引，合理地 合并数据库表（尽管有时可能影响表的规范化， 合并数据库表（尽管有时可能影响表的规范化，但相对 于效率的提高是值得的）。如果排序不可避免， ）。如果排序不可避免 于效率的提高是值得的）。如果排序不可避免，那么应 当试图简化它，如缩小排序的列的范围等。 当试图简化它，如缩小排序的列的范围等。
∏score(σsname='李明 ∧student.sno=sc.sno (student × sc) ) 李明' 李明
∏score(σsname='李明 (student sc) ) 李明' 李明 ∏score(σsname='李明 (student ) ) sc ) 李明' 李明 2011-1-14 ftt@
2011-1-14 ftt@ 6
2.对应的关系代数表达式中 对应的关系代数表达式中 首先做student和sc的自然连接形成一个临时表如下： 的自然连接形成一个临时表如下： 首先做 和 的自然连接形成一个临时表如下
9900011 9900011 9900011 9900021 9900021 9900031 李明 李明 李明 张海 张海 刘宏 21 21 21 20 20 22 计算机系 计算机系 计算机系 外语系 外语系 数学系 c001 c002 c003 c001 c003 c001 95 90 80 80 90 85
在经常进行连接，但是没有指定为外键的列上建立索引， 在经常进行连接，但是没有指定为外键的列上建立索引，而不经常 连接的字段则由优化器自动生成索引。 连接的字段则由优化器自动生成索引。 在频繁进行排序或分组（即进行group by或order by操作）的列上 操作） 在频繁进行排序或分组（即进行 或 操作 建立索引。 建立索引。 在条件表达式中经常用到的不同值较多的列上建立检索， 在条件表达式中经常用到的不同值较多的列上建立检索，在不同值 少的列上不要建立索引。比如在雇员表的“性别”列上只有“ 少的列上不要建立索引。比如在雇员表的“性别”列上只有“男” 两个不同值，因此就无必要建立索引。 与“女”两个不同值，因此就无必要建立索引。如果建立索引不但 不会提高查询效率，反而会严重降低更新速度。 不会提高查询效率，反而会严重降低更新速度。 如果待排序的列有多个， 如果待排序的列有多个，可以在这些列上建立复合索引 （compound index）。 ）。 当数据库表更新大量数据后，删除并重建索引可以提高查询速度。 当数据库表更新大量数据后，删除并重建索引可以提高查询速度。
避免这种情况的主要方法就是对连接的列进行索引。 避免这种情况的主要方法就是对连接的列进行索引。 例如，两个表：学生表（学号、姓名、年龄……）和选课表（学号、课程号、 例如，两个表：学生表（学号、姓名、年龄 ）和选课表（学号、课程号、 成绩）。 成绩）。 如果两个表要做连接，就要在“学号”这个连接字段上建立索引。 如果两个表要做连接，就要在“学号”这个连接字段上建立索引。
3.对应的关系代数表达式中 对应的关系代数表达式中 首先从表student中选择 中选择sname=‘李明’的记录， 李明’ 首先从表 中选择 李明 的记录， 形成只有一条记录的临时表 然后将这个临时表与表sc做自然连接操作 做自然连接操作， 然后将这个临时表与表 做自然连接操作，形成临 时表如下： 时表如下：