北工大計(jì)算機(jī)學(xué)院軟件工程研究所 程穎
目前,幾乎所有的應(yīng)用程序都要和數(shù)據(jù)庫打交道。通過查詢數(shù)據(jù)庫可很容易地獲得想
要的數(shù)據(jù)。但是,令人不滿意的是:某些查詢時(shí)間長,響應(yīng)速度慢。究其原因,一是硬件設(shè)
備(如CPU、磁盤)的存取速度跟不上,內(nèi)存容量不夠大,這需要計(jì)算機(jī)制造商的努力;另一
方面是沒有進(jìn)行查詢優(yōu)化。本文就查詢優(yōu)化問題,談點(diǎn)實(shí)踐體會(huì)。
分解查詢
這種方法是把查詢分解執(zhí)行,根據(jù)付出開銷的多少來決定如何分解,如何執(zhí)行。
為方便敘述,先給出一個(gè)例子。
關(guān)系: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:現(xiàn)有數(shù)量
QU:要用的數(shù)量
這個(gè)查詢是找出某城市能提供黑色轎車,且供應(yīng)量大于1000的供應(yīng)商名單。
1. 一般查詢方法
(1) 形成卡氏積 S×P×J×V×Y;
(2) 從卡氏積中選擇出滿足條件的元組;
(3) 在S.SNAME上投影。
這是個(gè)5元查詢。當(dāng)查詢涉及到卡氏積時(shí),卡氏積的元組數(shù)將組合性增長,這樣不僅需
要大量的存儲(chǔ)空間,而且執(zhí)行查詢時(shí)間很長。
2. 優(yōu)化查詢方法
該方法是把查詢分解處理。這里介紹兩種方法:
(1) 一元子查詢提取
任一N元查詢Q(X1,X2......Xn)被替換為一個(gè)一元查詢Q1和一個(gè)在其后執(zhí)行的Q2,即
Q→(Q1,Q2)。
(2) 化簡
Q被替換為兩個(gè)查詢Q1和Q2,Q2在Q1執(zhí)行后執(zhí)行,它們只有一個(gè)變化,即
Q1(X1,X2......Xm), Q2(Xm,Xm+1......Xn)。
例如上例的查詢可以分成兩個(gè)一元查詢
SELECT　P#
FROM　P
WHERE　P.PNAME=‘轎車’ AND P.COLOR=‘黑色’
和
SELECT　S#,J#,P#,QO
FROM　Y
WHERE　V.QO>1000
另一部分查詢?yōu)?
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#)
上面例子的查詢也很容易化簡化為一個(gè)涉及(P,V)的查詢和在其后執(zhí)行的涉及(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. 綜上所述
·一元子查詢提取幾乎總會(huì)得到好處,因?yàn)樵陉P(guān)系運(yùn)算之前盡可能減少關(guān)系的體積對(duì)
減少相應(yīng)的系統(tǒng)開銷起很大的作用;
·通常會(huì)得到期待的優(yōu)化結(jié)果,但也并不絕對(duì)如此。
選擇最優(yōu)存取路徑
在計(jì)算查詢表達(dá)式值時(shí)要充分考慮索引、數(shù)據(jù)的存儲(chǔ)分布等存取路徑,以進(jìn)一步提高
查詢效率。例如,選擇字段、連接字段上是否有索引,利用索引和HASHING算法可快速地存
取包含索引屬性特定值的記錄。建立索引,用戶可按順序讀文件中的記錄,依照接近于物
理順序的順序讀文件中的記錄是非常有效的。這種接近的物理順序讀取文件中記錄的索
引稱為聚簇索引。聚簇索引使我們可利用存儲(chǔ)塊中的記錄物理聚簇的優(yōu)點(diǎn),加快查詢速度
。下面具體談一點(diǎn)實(shí)踐體會(huì)。
前不久,筆者參加了一個(gè)在國內(nèi)開發(fā)的國外數(shù)據(jù)庫應(yīng)用系統(tǒng)的編程工作。該系統(tǒng)后臺(tái)
使用Oracle 7.3數(shù)據(jù)庫,Oracle的DBMS處理SQL執(zhí)行語句的順序如下:
(1) 根據(jù)WHERE子句選擇行;
(2) 根據(jù)GROUP BY子句對(duì)這些行進(jìn)行聚合;
(3) 對(duì)每一分組用組函數(shù)計(jì)算結(jié)果;
(4) 根據(jù)HAVING子句選擇和排除分組;
(5) 根據(jù)ORDER BY子句中的組函數(shù)所得的結(jié)果對(duì)組進(jìn)行排序。
這是一個(gè)體現(xiàn)查詢優(yōu)化思路的執(zhí)行順序,它對(duì)查詢的性能具有直接影響。一般來說,
被WHERE子句濾去的記錄越多,查詢速度就越快。因?yàn)闇p少了在GROUP BY運(yùn)算中必須處理
的行數(shù)量。在這次工作中筆者的體會(huì)如下。
1. 盡量避免連接
例如:
PowerBuilder 5.0數(shù)據(jù)窗口在選擇庫表時(shí)自動(dòng)把各個(gè)表中的相同屬性名(域也相同)
連接起來,如圖所示:
@@0644900.JPG;圖1@@
這種自動(dòng)連接多數(shù)情況下是有益的,但有些情況卻需要取消連接。如查詢條件為P20
00、P3000、P4000的有效區(qū)分都為1,這時(shí)有兩種方法:
(1) WHERE(P2000.有效區(qū)分=P3000.有效區(qū)分
　 P3000.有效區(qū)分=P4000.有效區(qū)分
P2000.有效區(qū)分=1
)
(2) WHERE(P2000.有效區(qū)分=1
P3000.有效區(qū)分=1
P4000.有效區(qū)分=1
)
第一種方法在自動(dòng)生成的基礎(chǔ)上添加P2000.有效區(qū)分=1就可以了,第二種方法先要取
消連接,然后再重新寫WHERE語句。表面看,第一種方法簡單,其實(shí)第一種方法大大降低了
執(zhí)行效率,因?yàn)樗胁槐匾倪B接。所以筆者在這里提醒使用者,不要為了一時(shí)省事而降
低了系統(tǒng)的效率。
2. 選擇最佳的解決方案
解決同一問題的方法固然很多,但應(yīng)用中應(yīng)該選擇最佳的解決方法。例如,對(duì)某一問
題的數(shù)據(jù)庫查詢有兩種方法,執(zhí)行結(jié)果一樣,效率卻不同。
這個(gè)問題是這樣的:
@@0644901.JPG;圖2@@
查詢要求是:如果在程序運(yùn)行界面上輸入了負(fù)責(zé)人代碼(放到code中),那么將查詢P2
000表中負(fù)責(zé)人代碼等于code的負(fù)責(zé)人名;如果沒有輸入負(fù)責(zé)人代碼,那么查詢P2000表中
所有負(fù)責(zé)人名。負(fù)責(zé)人代碼的取值范圍是0～9999,兩種解決方法分別是:
(1) IF 沒有輸入負(fù)責(zé)人代碼 THEN
code1=0
code2=9999
ELSE
code1=code2=負(fù)責(zé)人代碼
END IF
執(zhí)行SQL語句為:
SELECT 負(fù)責(zé)人名
FROM P2000
WHERE 負(fù)責(zé)人代碼>=:code1 AND負(fù)責(zé)人代碼
　 <=:code2
(2) IF 沒有輸入負(fù)責(zé)人代碼 THEN
　 執(zhí)行SQL語句為:
　SELECT 負(fù)責(zé)人名
FROM P2000
ELSE
code= 負(fù)責(zé)人代碼
執(zhí)行SQL語句為:
SELECT 負(fù)責(zé)人代碼
FROM P2000
WHERE 負(fù)責(zé)人代碼=:code
END IF
第一種方法只用了一條SQL語句,第二種方法用了兩條SQL語句。在沒有輸入負(fù)責(zé)人代
碼時(shí),第二種方法顯然比第一種方法執(zhí)行效率高,因?yàn)樗鼪]有限制條件;在輸入了負(fù)責(zé)人代
碼時(shí),第二種方法仍然比第一種方法效率高,不僅是少了一個(gè)限制條件,還因相等運(yùn)算是最
快的查詢運(yùn)算。分析到這里,讀者優(yōu)劣自明。
此外,Oracle提供存儲(chǔ)過程功能,它是編譯好、優(yōu)化過、且存儲(chǔ)在數(shù)據(jù)庫中的SQL語句
和控制流語言的集合,如果利用好存儲(chǔ)過程,可極大地增強(qiáng)SQL語言的功能、效率和靈活性
。
以上著重從實(shí)現(xiàn)的角度討論了查詢優(yōu)化,實(shí)際上要想根本解決查詢優(yōu)化問題,還需從
設(shè)計(jì)上進(jìn)行優(yōu)化,如盡量使用大的內(nèi)存,數(shù)據(jù)可適度冗余,庫結(jié)構(gòu)優(yōu)化,對(duì)于頻繁使用的表
建立索引,面向?qū)ο蟮臄?shù)據(jù)庫設(shè)計(jì)方法等等。