SQL查詢語句優(yōu)化研究

摘 要:數(shù)據(jù)庫性能問題一直是決策者及技術人員共同關注的焦點，影響數(shù)據(jù)庫性能的一個重要因素就是SQL查詢語句的低效率。為了提高數(shù)據(jù)庫應用系統(tǒng)的執(zhí)行效率，在此從分析關系數(shù)據(jù)庫查詢處理過程入手討論查詢優(yōu)化技巧，指出關系數(shù)據(jù)庫查詢優(yōu)化的一般原則。通過幾種優(yōu)化策略的研究，在時間和空間上提高了系統(tǒng)的性能，在一定程度上提高了查詢效率。

關鍵詞:SQL;優(yōu)化策略;數(shù)據(jù)庫性能;謂詞

中圖分類號:TP301.6文獻標識碼:A

文章編號:1004-373X(2010)04-152-02

Optimization of SQL Query

ZHANG Min1，2

(1.Northwest University，Xi′an，710069，China;

2.Computer Faculty Working Office，Xi′an Fanyi University，Xi′an，710105，China)

Abstract:Database performance issue has been the decision-makers and technical staff of the focus of common concern，affecting database performance is an important factor in the low-SQL query efficiency.In order to improve the implementation of database application system efficiency，from analysis of the process of query processing relational database query optimization techniques to start the discussion，pointing out that the relational database query optimization as a general principle.Optimization strategy in several studies，in time and space to improve the performance of the system，to a certain extent to improve efficiency of the query.

Keywords:SQL;optimization strategy;database performance;predicate

0 引 言

查詢是數(shù)據(jù)庫中最基本、最常用、最復雜的操作。在數(shù)據(jù)庫的管理信息系統(tǒng)中，查詢操作是所有數(shù)據(jù)庫操作中所占據(jù)比重最大的操作。當數(shù)據(jù)庫系統(tǒng)積累到一定程度，若查詢時采用單條順序掃描，那么掃描一遍所有的記錄可能就得花上幾十分鐘，甚至幾小時，這樣的系統(tǒng)就失去了現(xiàn)實的使用價值。采取什么樣的查詢策略，使查詢時間降為幾分鐘或者幾秒鐘，就是這里需要研究的查詢優(yōu)化問題。

1 優(yōu)化原理[1]

查詢優(yōu)化力圖找出給定表達式等價，但執(zhí)行效率更高的一個表達式，一個查詢往往會有許多實現(xiàn)方法，關鍵是如何找出一個與之等價的且操作時間又少的表達式，查詢優(yōu)化關注的問題是怎樣省時、省空間以及效率高。優(yōu)化的核心問題是盡可能減少查詢中各表的參與加工的數(shù)據(jù)量，從而達到優(yōu)化時間和空間的目的。

2 SQL優(yōu)化的方法

2.1 模糊匹配的避免

LIKE關鍵字支持通配符匹配，技術上稱為正則表達式。但這種匹配特別耗費時間，盡量避免使用模糊匹配。例:

SELECT*FROM student WHERErxscoreLIKE′4′

即使在rxscore 字段上建立了索引，在這種情況下也還是采用順序掃描的方式。

可改寫為:

SELECT*FROM student WHERE rxscore>′400′

這樣，在執(zhí)行查詢時就會利用索引來查詢，顯然會大大提高速度。

2.2 邏輯表達式的等價變換

由于執(zhí)行引擎對各種謂詞的處理方法不同，因此把邏輯表達式重寫成等價的且效率較高的表達式是提高查詢效率的有效方法，同時也是切實可行的[2]。通過查閱大量的文獻資料以及大量的實驗，分析了RDBMS執(zhí)行引擎對各種謂詞執(zhí)行效率的不同，總結出以下幾種邏輯表達式轉換規(guī)則:

2.2.1 將多個OR連接的表達式轉化為ANY表達式

當條件表達式中同層次上出現(xiàn)由連接詞OR連接的表達式，并且OR所連接的表達式的左表達式相同且謂詞符號也相同時，那么可以將這些表達式合并為一個右表達式用ANY來描述的表達式。例:

rxscore>400 OR rxscore>score+50 OR rxscore>score×2

可改寫為:

rxscore>ANY(400，score+50，score×2)

2.2.2 將ANY或ALL轉化為簡單的比較表達式

當謂詞的右表達式為ANY或ALL的形式，并且ANY(ALL)包含的各表達式均有固定值，并且可以比較大小，則可根據(jù)謂詞符號 (僅限于比較大小的操作符 ) 將ANY(ALL)重寫為簡單的比較表達式。例:

x>ANY(100，200，300)可改寫為:x>100;

x>ALL(100，200，300)可改寫為:x>300

2.2.3 將BETWEEN…AND轉化為AND連接的表達式

可以把由 BETWEEN expr1 AND expr2的形式重寫為用AND連接的兩個表達式，效率往往有一定的提高。例:

rxscore BETW EEN 300 AND 400

可改寫為:

rxscore>=30 AND rxscore<=40

2.2.4 將IN謂詞表達式轉換為OR連接的謂詞表達式

例:年齡IN(20，30，40)

可寫為:年齡=20 OR年齡=30 OR 年齡=40

以上提到的4類謂詞重寫規(guī)則均有其特定的條件，在條件滿足的情況下才可以使用。對于簡單謂詞的重寫，每條規(guī)則提高的效率可能不太明顯，但如果查詢語句的WHERE條件同時使用多條規(guī)則進行重寫時，效率的提高將非?？捎^。

2.3 子查詢合并

子查詢合并是將某些特定的子查詢重寫為等價的多個表的連接操作。子查詢合并的作用在于能使查詢語句的層次盡可能地減少，從而可提高查詢的效率。子查詢合并的一般規(guī)則為:

(1) 如果外層查詢的結果沒有重復，即SELECT子句中包含主碼，則可以合并其子查詢，并且合并后的SELECT子句前應加上DISTINCT標志;

(2) 如果外層查詢的SELECT子句中有DISTINCT標志，那么可以直接進行子查詢合并;

(3) 如果內部子查詢結果沒有重復元組，則可以合并。

例:查詢選修002號課程的學生基本信息。

用子查詢的方法如下所示，例:

SELECT student.sno，sname，age FROM student WHERE sno IN(SELECT sno FROM sc WHERE cno=′002′)

可改寫為:

SELECT student.sno，sname，age FROM student，sc WHERE student.sno=sc.sno AND cno=′002′

2.4 用集合運算來代替邏輯運算

0R在嵌套查詢中，表的順序存取對查詢效率可能產(chǎn)生致命的影響，避免這種情況的方法就是對連接的列進行索引。例如兩個表:student(sno，sname，age)和sc(sno，cno，score)。如果兩個表要做連接，就要在“sno”這個連接字段上建立索引。還可以使用并集來避免順序存取，盡管在所有檢查列上都有索引，但某些形式的WHERE子句強迫優(yōu)化器使用順序存取。下面的查詢將強迫對student表執(zhí)行順序操作:

SELECT*FROM student WHERE sno>′23005′AND sno<′23020′

OR xb=′計算機系′

雖然在sno和系別名上都建有索引，但是在上面的語句中優(yōu)化器還是使用順序存取的方法掃描整個表。因為這個語句要檢索的是分離的行的集合，所以應該改為如下語句:

SELECT*FROM student WHERE sno>′23005′AND sno<′23020′

UNION SELECT*FROM student WHERE xb=′計算機系′

2.5 多表連接優(yōu)化

最能體現(xiàn)查詢復雜性的就是多表連接，多表連接操作往往要耗費大量的CPU時間和內存，因此多表連接查詢性能優(yōu)化往往是SQL優(yōu)化的重點與難點[3]。

2.5.1 充分利用連接條件

在某種情況下，兩個表之間可能不只一個的連接條件，這時在WHERE子句中將連接條件完整的寫上，有可能大大提高查詢速度。例:

SELECT SUM(A.AMOUNT) FROM A，B WHERE

A.CARDNO=B.CARDNO SELECT SUM(A.AMOUNT) FROM A，B WHERE A.CARDNO=B.CARDNO AND

A.ACCOUNTNO=B.ACCOUNTNO

這里，第二句將比第一句執(zhí)行快得多。

2.5.2 先篩選后連接

當查詢多個數(shù)據(jù)表時，要先過濾后再連接。例:

SELECT*FROM A，B WHERE A.INTID=B.INTADID AND A.INTID>1 AND YEAR(B.DATDATE)=2500

SELECT*FROM A，B WHERE A.INTID>1 AND YEAR(B.DATDATE)=2 500 AND A.INTID=B.INTADID它們的執(zhí)行效率相差很大。第一個查詢語句首先將兩個數(shù)據(jù)表按照用戶ID進行連接，然后再將符合條件的記錄篩選。由于兩個數(shù)據(jù)表進行連接時記錄有些是以后還要篩選掉的，這顯然會占用更多的時間，且多個數(shù)據(jù)表連接是笛卡兒積運算，消耗的時間會隨著記錄個數(shù)的增加很快地增長。第二個查詢語句克服了這個缺點，首先篩選出符合條件的記錄，減少了進行連接的記錄個數(shù)，然后再執(zhí)行連接查詢，大大提高了查詢效率。

3 結 語

查詢優(yōu)化要抓住關鍵問題，對于數(shù)據(jù)庫應用程序，重點在于如何提高SQL的執(zhí)行效率。在數(shù)據(jù)庫的開發(fā)和維護過程中，查詢的優(yōu)化設計可以提高系統(tǒng)性能，對于數(shù)據(jù)量大的數(shù)據(jù)庫系統(tǒng)尤為重要。以上介紹的幾種優(yōu)化策略使查詢在時間和空間上提高了系統(tǒng)的性能，在一定程度上提高了查詢效率。

參考文獻

[1]王珊，薩師煊.數(shù)據(jù)庫系統(tǒng)概論[M].4版.北京:高等教育出版社，2006.

[2]丁寶康，董健全.數(shù)據(jù)庫實用教程[M].2版.北京:清華大學出版社，2003.

[3]范劍波，張曉云.網(wǎng)絡數(shù)據(jù)庫技術與應用[M].西安:西安電子科技大學出版社，2004.

[4]劉志成，彭勇.數(shù)據(jù)庫系統(tǒng)原理與應用[M].北京:機械工業(yè)出版社，2007.

[5]羅運模.SQL Server數(shù)據(jù)庫系統(tǒng)基礎[M].北京:高等教育出版社，2006.

[6]宋瀚濤，李新社.數(shù)據(jù)庫編程與應用[M].北京:電子工業(yè)出版社，1998.

[7]史嘉權.數(shù)據(jù)庫系統(tǒng)概論[M].北京:清華大學出版社，2006.

[8]馬李明，王守桃，徐艷蕾.SQL語句的優(yōu)化在提高數(shù)據(jù)查詢中的應用[J].電腦知識與技術，2008(20):200，223.

[9]華蓓，楊柳.SQL Server數(shù)據(jù)庫性能優(yōu)化探究[J].實踐與經(jīng)驗，2008(7):98-100.