關(guān)于SQL文優(yōu)化問題總結(jié)

【摘 要】實際系統(tǒng)中遇到性能問題是非常常見的，性能優(yōu)化有許多方面，其中包括硬件方面，軟件方面，包括服務(wù)器端，客戶端等等。本文重點基于ORACLE分析了影響SQL文性能的原因，然后列舉了幾點性能優(yōu)化的對策，希望給開發(fā)人員在編碼時提供幫助，給SQL的性能問題調(diào)查者提供一個方向。

【關(guān)鍵詞】ORACLE；性能問題；SQL文優(yōu)化

一、問題提出

之前有個項目，其中的批處理定期調(diào)用一個存儲過程，在測試環(huán)境中運行沒有問題，但是正式運行出現(xiàn)了錯誤，執(zhí)行存儲過程時出現(xiàn)了錯誤：提示是表空間不足。為了解決這個問題，筆者對SQL文的性能優(yōu)化進行了學習和研究。

二、問題調(diào)查與解決

由于該存儲過程內(nèi)容比較多，大概有3000多行，也不能判斷那部分出了問題，首先在可能出現(xiàn)問題的地方追加了LOG信息。由于測試環(huán)境中該問題不能再現(xiàn)，所以代碼更新到了實際環(huán)境中進行運行，通過LOG發(fā)現(xiàn)是在執(zhí)行某個SQL文時出的錯誤，這個SQL文涉及到了10多個表，而其中表中的數(shù)據(jù)量比較大。執(zhí)行時用到的臨時表空間高達40G，后來通過調(diào)查對SQL的進行了調(diào)整，只是修改了WHERE條件中其中兩個條件的順序，這個問題就解決了。

三、SQL文性能原因分析

（1）在大記錄集上進行高成本操作，如使用了引起排序的謂詞等。（2）過多的I/O操作（含物理I/O與邏輯I/O），最典型的就是未建立恰當?shù)乃饕瑢?dǎo)致對查詢表進行全表掃描。減少訪問數(shù)據(jù)庫的次數(shù)，就能實際上減少ORACLE的工作量。（3）處理了太多的無用記錄，如在多表連接時過濾條件位置不當導(dǎo)致中間結(jié)果集包含了太多的無用記錄。（4）未充分利用數(shù)據(jù)庫提供的功能，如查詢的并行化處理等。

四、SQL文性能優(yōu)化總結(jié)

（1）建立恰當?shù)乃饕?jīng)常進行排序和連接操作的字段建立索引。（2）避免使用”*”，SQL文中引用”*”，使用起來的確非常方便，但是效率非常低，主要是ORACLE在解析的過程中，會將”*”一次轉(zhuǎn)化成所有的列名，這個工作是通過查詢數(shù)據(jù)字典完成的。這就意味著消耗更多的時間。（3）盡量避免多表關(guān)聯(lián)。（4）避免使用消耗資源的操作，帶有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL語句會啟動SQL引擎執(zhí)行消耗資源的排序功能。DISTINCT需要一次排序操作，其他的至少需要執(zhí)行二次排序。通常帶有執(zhí)行UNION，MINUS，INTERSECT的SQL語句都可以通過其他方式回避。例如：SELECT DISTINCT A.NO，A.NAME FROM A，B WHERE A.NO=B.NO可以替換為效率更高的EXISTS來實現(xiàn)，SELECT A.NO，A.NAME FROM A WHERE EXISTS（SELECT 1 FROM B WHRE B.NO=A.NO）。（5）避免在索引列上使用函數(shù)。例如：

SELECT NO FROM A WHERE A.SCORE * 2>180可以修改為SELECT NO FROM A WHERE A.SCORE>180/2。（6）避免在索引列上使用NOT。NOT會產(chǎn)生和在索引列上使用函數(shù)相同的影響。當ORACLE遇到NOT時，他就會停止使用索引轉(zhuǎn)而執(zhí)行全表掃描。（7）避免在索引列上使用IS NULL，IS NOT

NULL。（8）減少對表的查詢。在含有自查詢的語句中，要特別注意減少對表的查詢。（9）注意WHERE字句的連接順序。ORACLE原則上采用自下而上的順序解析WHERE子句，根據(jù)據(jù)這個原理，當在WHERE子句中有多個表聯(lián)接時，WHERE子句中排在最后的表應(yīng)當是返回行數(shù)可能最少的表，有過濾條件的子句應(yīng)放在WHERE子句的最后。（10）使用表的別名（Alias）：當在SQL語句中連接多個表時，請使用表的別名并把別名前綴于每個Column上.這樣一來，就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤。（11）用EXISTS替代IN、用NOT EXISTS替代NOT IN。在許多基于基礎(chǔ)表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯(lián)接，在這種情況下，使用EXISTS（或NOT EXISTS）通常將提高查詢的效率。在子查詢中，NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并。無論在哪種情況下，NOT IN都是最低效的（因為它對子查詢中的表執(zhí)行了一個全表遍歷）。為了避免使用NOT IN，我們可以把它改寫成外連接（Outer Joins）或NOT EXISTS。（12）sql語句用大寫的。因為oracle總是先解析sql語句，把小寫的字母轉(zhuǎn)換成大寫的再執(zhí)行。（13）用>=替代>。高效：SELECT*FROM EMP

WHERE DEPTNO>=4；低效：SELECT*FROM EMP WHERE DEPTNO>3。兩者的區(qū)別在于，前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于3的記錄。

SQL語言在數(shù)據(jù)庫應(yīng)用中占有非常重要的地位，其性能的優(yōu)劣直接影響著整個信息系統(tǒng)的可用性。因此對于開發(fā)人員來說，理解SQL調(diào)優(yōu)的基本原理，這樣可能避免一些不必要的問題。理論上SQL的優(yōu)化方法很多，具體的效果好需要在實際的環(huán)境中進行驗證。有可能需要多個方法并用。

參 考 文 獻

[1]徐鳳梅.關(guān)系數(shù)據(jù)庫中SQL語言查詢的優(yōu)化策略[J].廣西輕工業(yè).2009（5）