数据库查询优化（转）

北工大计算机学院软件工程研究所 程颖
目前,几乎所有的应用程序都要和数据库打交道。通过查询数据库可很容易地获得想
要的数据。但是,令人不满意的是:某些查询时间长,响应速度慢。究其原因,一是硬件设
备(如CPU、磁盘)的存取速度跟不上,内存容量不够大,这需要计算机制造商的努力;另一
方面是没有进行查询优化。本文就查询优化问题,谈点实践体会。
分解查询
这种方法是把查询分解执行,根据付出开销的多少来决定如何分解,如何执行。
为方便叙述,先给出一个例子。
关系:SUPPLIER(S#,SNAME,CITY)
　:S
PARTS　 (P#,PNAME,SIZE)
　:P
PROJECT (J#,JNAME,COLOR)
:J
INVENTORY(S#,P#,QO)
:V
SUPPLY(S#,J#,P#,QU)
:Y
其中,QO:现有数量
QU:要用的数量
这个查询是找出某城市能提供黑色轿车,且供应量大于1000的供应商名单。
1. 一般查询方法
(1) 形成卡氏积 S×P×J×V×Y;
(2) 从卡氏积中选择出满足条件的元组;
(3) 在S.SNAME上投影。
这是个5元查询。当查询涉及到卡氏积时,卡氏积的元组数将组合性增长,这样不仅需
要大量的存储空间,而且执行查询时间很长。
2. 优化查询方法
该方法是把查询分解处理。这里介绍两种方法:
(1) 一元子查询提取
任一N元查询Q(X1,X2......Xn)被替换为一个一元查询Q1和一个在其后执行的Q2,即
Q→(Q1,Q2)。
(2) 化简
Q被替换为两个查询Q1和Q2,Q2在Q1执行后执行,它们只有一个变化,即
Q1(X1,X2......Xm), Q2(Xm,Xm+1......Xn)。
例如上例的查询可以分成两个一元查询
SELECT　P#
FROM　P
WHERE　P.PNAME=‘轿车’ AND P.COLOR=‘黑色’
和
SELECT　S#,J#,P#,QO
FROM　Y
WHERE　V.QO>1000
另一部分查询为:
SELECT　S.SNAME
FROM　S,P,J,V,Y
WHERE (S.S#=V.S# AND S.S#=Y.S# AND
　 S.CITY=J.CITY AND P.P#=V.P# AND
　 T.P#=V.P# AND J.J#=Y.J#)
上面例子的查询也很容易化简化为一个涉及(P,V)的查询和在其后执行的涉及(S,J,
Y,V)的查询:
SELECT　S.SNAME
FORM　S,J,V,Y
WHERE　S.CITY=J.CITY AND S.S#=Y.S# AND
　 J.J#=Y.J# AND V.QO>1000 AND P.#=Y.P#
AND V.S#=Y.S# AND
　V.P#=(SELECT V.P#
　 FROM　 V,P
WHERE V.P#=P P#AND P.PNAME=‘轿车’ AND　P.COLOR=‘黑色’)
3. 综上所述
·一元子查询提取几乎总会得到好处,因为在关系运算之前尽可能减少关系的体积对
减少相应的系统开销起很大的作用;
·通常会得到期待的优化结果,但也并不绝对如此。
选择最优存取路径
在计算查询表达式值时要充分考虑索引、数据的存储分布等存取路径,以进一步提高
查询效率。例如,选择字段、连接字段上是否有索引,利用索引和HASHING算法可快速地存
取包含索引属性特定值的记录。建立索引,用户可按顺序读文件中的记录,依照接近于物
理顺序的顺序读文件中的记录是非常有效的。这种接近的物理顺序读取文件中记录的索
引称为聚簇索引。聚簇索引使我们可利用存储块中的记录物理聚簇的优点,加快查询速度
。下面具体谈一点实践体会。
前不久,笔者参加了一个在国内开发的国外数据库应用系统的编程工作。该系统后台
使用Oracle 7.3数据库,Oracle的DBMS处理SQL执行语句的顺序如下:
(1) 根据WHERE子句选择行;
(2) 根据GROUP BY子句对这些行进行聚合;
(3) 对每一分组用组函数计算结果;
(4) 根据HAVING子句选择和排除分组;
(5) 根据ORDER BY子句中的组函数所得的结果对组进行排序。
这是一个体现查询优化思路的执行顺序,它对查询的性能具有直接影响。一般来说,
被WHERE子句滤去的记录越多,查询速度就越快。因为减少了在GROUP BY运算中必须处理
的行数量。在这次工作中笔者的体会如下。
1. 尽量避免连接
例如:
PowerBuilder 5.0数据窗口在选择库表时自动把各个表中的相同属性名(域也相同)
连接起来,如图所示:
@@0644900.JPG;图1@@
这种自动连接多数情况下是有益的,但有些情况却需要取消连接。如查询条件为P20
00、P3000、P4000的有效区分都为1,这时有两种方法:
(1) WHERE(P2000.有效区分=P3000.有效区分
　 P3000.有效区分=P4000.有效区分
P2000.有效区分=1
)
(2) WHERE(P2000.有效区分=1
P3000.有效区分=1
P4000.有效区分=1
)
第一种方法在自动生成的基础上添加P2000.有效区分=1就可以了,第二种方法先要取
消连接,然后再重新写WHERE语句。表面看,第一种方法简单,其实第一种方法大大降低了
执行效率,因为它有不必要的连接。所以笔者在这里提醒使用者,不要为了一时省事而降
低了系统的效率。
2. 选择最佳的解决方案
解决同一问题的方法固然很多,但应用中应该选择最佳的解决方法。例如,对某一问
题的数据库查询有两种方法,执行结果一样,效率却不同。
这个问题是这样的:
@@0644901.JPG;图2@@
查询要求是:如果在程序运行界面上输入了负责人代码(放到code中),那么将查询P2
000表中负责人代码等于code的负责人名;如果没有输入负责人代码,那么查询P2000表中
所有负责人名。负责人代码的取值范围是0～9999,两种解决方法分别是:
(1) IF 没有输入负责人代码 THEN
code1=0
code2=9999
ELSE
code1=code2=负责人代码
END IF
执行SQL语句为:
SELECT 负责人名
FROM P2000
WHERE 负责人代码>=:code1 AND负责人代码
　 <=:code2
(2) IF 没有输入负责人代码 THEN
　 执行SQL语句为:
　SELECT 负责人名
FROM P2000
ELSE
code= 负责人代码
执行SQL语句为:
SELECT 负责人代码
FROM P2000
WHERE 负责人代码=:code
END IF
第一种方法只用了一条SQL语句,第二种方法用了两条SQL语句。在没有输入负责人代
码时,第二种方法显然比第一种方法执行效率高,因为它没有限制条件;在输入了负责人代
码时,第二种方法仍然比第一种方法效率高,不仅是少了一个限制条件,还因相等运算是最
快的查询运算。分析到这里,读者优劣自明。
此外,Oracle提供存储过程功能,它是编译好、优化过、且存储在数据库中的SQL语句
和控制流语言的集合,如果利用好存储过程,可极大地增强SQL语言的功能、效率和灵活性
。
以上着重从实现的角度讨论了查询优化,实际上要想根本解决查询优化问题,还需从
设计上进行优化,如尽量使用大的内存,数据可适度冗余,库结构优化,对于频繁使用的表
建立索引,面向对象的数据库设计方法等等。