目前,几乎所有的应用程序都要和数据库打交道。通过查询数据库可很容易地获得想要的数据。但是,令人不满意的是:某些查询时间长,响应速度慢。究其原因,一是硬件设备(如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数据窗口在选择库表时自动把各个表中的相同属性名(域也相同)连接起来。这种自动连接多数情况下是有益的,但有些情况却需要取消连接。如查询条件为P2000、P3000、P4000的有效区分都为1,这时有两种方法:
(1) WHERE(P2000.有效区分=P3000.有效区分
P3000.有效区分=P4000.有效区分
P2000.有效区分=1
(2) WHERE(P2000.有效区分=1
P3000.有效区分=1
P4000.有效区分=1
第一种方法在自动生成的基础上添加P2000.有效区分=1就可以了,第二种方法先要取消连接,然后再重新写WHERE语句。表面看,第一种方法简单,其实第一种方法大大降低了执行效率,因为它有不必要的连接。所以笔者在这里提醒使用者,不要为了一时省事而降低了系统的效率。
2. 选择最佳的解决方案
解决同一问题的方法固然很多,但应用中应该选择最佳的解决方法。例如,对某一问题的数据库查询有两种方法,执行结果一样,效率却不同。
查询要求是:如果在程序运行界面上输入了负责人代码(放到code中),那么将查询P2000表中负责人代码等于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语言的功能、效率和灵活性。
以上着重从实现的角度讨论了查询优化,实际上要想根本解决查询优化问题,还需从设计上进行优化,如尽量使用大的内存,数据可适度冗余,库结构优化,对于频繁使用的表建立索引,面向对象的数据库设计方法等等。