第19卷第3期天津理工學院學報Vol. 19 No.32003年9月JOURNAL OF TIANJIN INSTTTUTE OF TECHNOLOGYSep. 2003文章編號104-2262(0 2003 )3-007703SQL語句的優(yōu)化.高天迎.(天津城市建設(shè)學院計算機科學與技術(shù)系天津300384 )摘要在數(shù)據(jù)庫應(yīng)用系統(tǒng)中隨著數(shù)據(jù)庫中數(shù)據(jù)的增加系統(tǒng)的響應(yīng)速度就成為系統(tǒng)需要解決的主要的問題之-.系統(tǒng)優(yōu)化中一個很重要的方面就是SQL語句的優(yōu)化.本文從應(yīng)用的角度論述了SQL語句優(yōu)化的主要方法并提供了實例.關(guān)鍵詞數(shù)據(jù)庫;優(yōu)化;SQL;索引中圖分類號:TP311.132.2文獻標識碼ASQL optimizationGAO Tian-ying( Dept. of Computer Sci. and Tech. ,Tianjin Institute of Urban Construction , Tianjin 300384 , China )Abstract :In database application systems , performance becomes one of its main problems needed to be resolvedwith the increase of data in it. SQL optimization is a very important aspect in system optimization. This paperstates the main methods of SQL optimization from the applied angle and offers some instances..Keywords database optimization SQL jindex條原本糟糕的SQL語句經(jīng)過系統(tǒng)的優(yōu)化之后不可能1問題的提出變得高效因此用戶所寫SQL語句的優(yōu)劣至關(guān)重要.數(shù)據(jù)庫管理系統(tǒng)是管理信息系統(tǒng)( MIS )的核心，2問題的解決基于數(shù)據(jù)庫的聯(lián)機事務(wù)處理(OLTP)以及聯(lián)機分析處理(OLAP)是銀行、企業(yè)、政府等部門最為重要的計算下面以SQLServer為例介紹-些優(yōu)化SQL語句機應(yīng)用之一.從大多數(shù)系統(tǒng)的應(yīng)用實例來看隨著數(shù)據(jù)的方法.庫中數(shù)據(jù)的增加系統(tǒng)的響應(yīng)速度就成為系統(tǒng)需要解2.1避免順序存取決的最主要的問題之一.系統(tǒng)優(yōu)化中一個很重要的方在嵌套查詢中對表的順序存取對查詢效率可能面就是SQL語句的優(yōu)化.對于海量數(shù)據(jù),劣質(zhì)SQL語產(chǎn)生致命的影響.比如采用順序存取策略,一個嵌套3句和優(yōu)質(zhì)SQL語句之間的速度差別可以達到上百倍.層的查詢,如果每層都查詢1000行,那么這個查詢就SQL語言是面向結(jié)果而不是面向過程的關(guān)系數(shù)據(jù)要查詢10億行數(shù)據(jù).避免這種情況的主要方法就是對庫查詢語言,在實際的數(shù)據(jù)庫產(chǎn)品如SQL Server、Or-連接的列進行索引利用索引路徑處理查詢.acle等)的高版本中大都采用基于代價的優(yōu)化方法這例如:SELECT * FROM titles ,titleauthor WHERE種優(yōu)化能根據(jù)從系統(tǒng)字典表所得到的信息來估計不同title. title_ id = titleauthor. title_ _id ;規(guī)劃的代價然后選擇一個最優(yōu)的規(guī)劃1.雖然現(xiàn)在應(yīng)中國煤化工上建立索引.的數(shù)據(jù)庫產(chǎn)品在SQL語句優(yōu)化方面已經(jīng)做得越來越還YHCNMHG來避免順序存取.盡管好但是用戶提交的SQL語句是系統(tǒng)優(yōu)化的基礎(chǔ),-在所有的檢查列上都有索引但某些形式的where子收稿日期_2003_06-23作)者鬻夫趣1977-)男助教天津理工學院學報第19卷第3期句強迫優(yōu)化器使用順序存取(2].2.4 避免對搜索參數(shù)使用其它操作符(如數(shù)學、字符例如SELECT * FROMauthors WHERE( au_ _id=串函數(shù)等)172-32-1176'ANDzip>94618')ORzip=例如:SELECT au_ Iname FROM authors WHERE95428' ;SUBSTRING( state ,1 ,1) =' M';雖然在au_ id”和zip" 上建有索引,但是上面的SELECT title FROM titles WHERE ytd_ sales .語句還是順序掃描整個表,因為這個語句要檢索的是* 5> 30000分離的行的集合.所以應(yīng)該改為如下語句:WHERE子句中對列的任何操作結(jié)果都是在SQLSELECT * FROM authors WHERE au_ _id = 172 -語句運行時逐列計算得到的,因此它不得不進行表搜32- 1176' AND zip> 94618'索,而沒有使用該列上面的索引31.因此將SQL語句UNION SELECT * FROM authors WHERE zip =重寫成下面這樣:SELECT au _ lname FROM authors WHERE state2.2 提供一些冗余的搜索參數(shù),使優(yōu)化器有更多的LIKE M%';選擇余地SELECT title FROM titles WHERE ytd_ sales >例如3] :itle 和titleauthor兩張表是一對 多的關(guān)6000 ;系同樣的查詢條件有3種表現(xiàn)方法:2.5 避免使用不兼容的數(shù)據(jù)類型●SELECT title _ id ,title FROM titles ,titleauthor數(shù)據(jù)類型的不兼容可能使優(yōu)化器無法執(zhí)行- -些本W(wǎng)HERE titles. title_ id = titleauthor. title_ id AND title-來可以進行的優(yōu)化操作.例如:SELECT title FROM ti-author. title_ id =' TC7777' ;tles WHERE price > 100 ;在這條語句中"，price" 字段是money" 型的優(yōu)化WHERE titles. title_ id = titleauthor. title_ id AND titles.器很難對其進行優(yōu)化因為100是個整型數(shù).應(yīng)當在編title_ _id =' TC7777' ;程時將整型轉(zhuǎn)化成為貨幣型而不要等到運行時轉(zhuǎn)化.2.6 避免使用相關(guān)子查詢WHERE titles. title_ id = titleauthor. tile_ _id AND titles.相關(guān)查詢執(zhí)行效率不高,因為對于外層查詢的每title_ id =' TC7777' AND titleauthor. title_ id ='一個元組都要進行-次內(nèi)層子查詢的運算,從而可能TC7777' ;導致大量的隨機磁盤I/O操作.可以用一個具有連接顯然3種方法-種比-種要好,因為后者為優(yōu)化的查詢?nèi)ヌ娲短鬃硬樵?如果子查詢不可避免那么器提供了更多的選擇機會.要在子查詢中過濾掉盡可能多的行.2.3 避免使用< >”或NOT"這樣的操作符例如:SELECT title FROM titles WHERE EXIST”<>”是排斥性的操作符而不是包括性的操作( SELECT * FROM titleauthor WHERE titles. title_ id =符這會使系統(tǒng)無法使用索引而只能直接搜索表中的titleauthor. title_ id );數(shù)據(jù)3].應(yīng)該改為:例如:SELECT * FROM authors WHERE au_SELECT title FROM titles , titleauthor WHERE title-lname =' Green' ;author. title_ id = titles. title_ id ;優(yōu)化器將無法通過索引來確定將要命中的行數(shù).2.7避免通配符(%)在搜尋詞首出現(xiàn)試著使用:通配符( % )出現(xiàn)在搜尋詞首,系 統(tǒng)不使用索引,SELECT * FROM authors WHERE au_ .lname<會降低SQL語句的執(zhí)行速度.然而當通配符出現(xiàn)在字Green' OR au_ lname >' Green' ;符串其他位置時優(yōu)化器就能利用索引.在下面的查詢?nèi)绻褂肗OT ,應(yīng)在取反的短語前面加上括中索引得到了使用號并在短語前面加上NOT運算符. NOT運算符包含中國煤化工: authors WHERE state在另外一個邏輯運算符中這就是< >”運算符.LIKEYHCNMHG例如SELECT * FROM authors WHERE NOT( au2.8 索引列值不能是NULL值_Iname =' Green' );如果索引列值可以是NULL值在SQL語句中那些需要返回NULL值的操作將不會用到索引.2003年9月高天迎SQL語句的優(yōu)化79.例如在WHERE子句中使用IS NULL或IS NOT是在更多的情況下往往需要反復試驗比較不同的語NULL的語句優(yōu)化器是不允許使用索引的.句以得到最佳方案.此外SQL的性能優(yōu)化是- -個復雜2.9 COMMIT 提交的數(shù)據(jù)量不宜過大的過程,以 上著重從實現(xiàn)的角度討論了SQL語句優(yōu)如果-次COMMIT提交的數(shù)據(jù)量過大,則會占用化實際上要想根本解決查詢優(yōu)化問題還會涉及數(shù)據(jù)大量的系統(tǒng)內(nèi)存造成COMMIT的時間過長,系統(tǒng)性庫的結(jié)構(gòu)設(shè)計與資源配置、網(wǎng)絡(luò)的流量控制以及操作能下降.-次COMMIT提交的數(shù)據(jù)量大小應(yīng)根據(jù)系統(tǒng)系統(tǒng)的總體設(shè)計等等.的具體情況在程序中加以控制.參考文獻:2.10 要善于使用存儲過程存儲過程是編譯好、優(yōu)化過且存儲在數(shù)據(jù)庫中的[1 ] Patrick 0 Neil , Elizabeth Oneil. DATABASE :Principles ,SQL語句和控制流語言的集合如果在程序中利用好Programming and Performance[ M ] Bejing :Higher Educa-存儲過程,可極大地增強SQL語言的效率、功能和靈tion Press ,Morgan Kaufmann Publishers 2001.[2]孟小峰王珊.數(shù)據(jù)庫系統(tǒng)導論[M]北京機械工業(yè)出活性.版社2001.[3] 袁鵬飛. SQL Server7.0 數(shù)據(jù)庫系統(tǒng)管理與應(yīng)用開發(fā)3結(jié)束語[ M ]北京人民郵電出版社1999.[4]微軟公司. Microsoft SQL Server 7.0系統(tǒng)管理[ M]北京:以上提到的是-些基本的提高SQL語句效率的北京希望電子出版社1999.方法其中的關(guān)鍵就是合理的建立并充分利用索引.但(上接第38頁)使用身份.管理員負責系統(tǒng)設(shè)置、變量的確定和初始數(shù)夠分析計算、數(shù)據(jù)輸出,而且能夠繪制圖形輸出,使結(jié)據(jù)的輸入而普通用戶則只負責數(shù)據(jù)的運行、計算結(jié)果果形象、一目了然.另外在系統(tǒng)的設(shè)計中還引入了一的輸出實現(xiàn)分級負責管理管理員和普通用戶分別有些技巧比如對于因素個數(shù)聚集狀態(tài)的個數(shù)能夠動態(tài)口令進入系統(tǒng)非法用戶不能進入和使用系統(tǒng)，從而保地改變窗口的排列既能縱向又能橫向等等.因此,本證了系統(tǒng)的安全性.系統(tǒng)的功能強.3 )數(shù)學模型先進:本系統(tǒng)采用了兩種數(shù)學模型.一個是經(jīng)典的統(tǒng)計分析方法一主成份分析法一個是6結(jié)論現(xiàn)代的評價分析方法一因素重構(gòu)分析法.主成份分析法可以進行降維處理、減少指標、簡化結(jié)構(gòu)使分析容該系統(tǒng)在天津市發(fā)展計劃委員會發(fā)展規(guī)劃處測易進行而因素重構(gòu)分析法則是近十年來我國系統(tǒng)科試、使用后系統(tǒng)試運行良好.該系統(tǒng)使用功能完善實學研究人員新研究的且經(jīng)實踐證明是先進的評價分析踐預(yù)測結(jié)果可行符合天津市的實際情況.可作為各級方法.兩種方法可以互補使用.因此,本系統(tǒng)的數(shù)學模領(lǐng)導及經(jīng)濟規(guī)劃部門]進行區(qū)域經(jīng)濟規(guī)劃決策的輔助工型具有先進性.具因此,該系統(tǒng)的研制開發(fā)是成功的具有廣闊的應(yīng)4)良好的可維護性和可擴充性:油于本系統(tǒng)采用用前景.自頂向下的模塊結(jié)構(gòu)設(shè)計不同模塊可分別進行設(shè)計和調(diào)試因此可維護性好;另外可根據(jù)需要較容易地增加或減少功能和模塊,有良好的可擴充性.[1 ]羅積玉.邢 瑛.經(jīng)濟統(tǒng)計分析方法及預(yù)測[ M]北京:5)系統(tǒng)功能強本系統(tǒng)不僅能夠進行統(tǒng)計計算分清華大學出版社,1987.析,而且能夠進行預(yù)測分析;既能預(yù)測某年的經(jīng)濟總[2]舒中國煤化工M ]北京:中國科學院系量，又能根據(jù)經(jīng)濟總量預(yù)測出各指標的增長值不僅能統(tǒng)YHCNMH G