為了獲得穩(wěn)定的執(zhí)行性能，SQL語句越簡單越好。對復(fù)雜的SQL語句，要設(shè)法對之進(jìn)行簡化，本文給大家介紹優(yōu)化SQL語句提高數(shù)據(jù)庫性能。

現(xiàn)在數(shù)據(jù)越來越復(fù)雜和龐大，很多時候影響程序運行性能不理想的原因中除了一部分是因為應(yīng)用程序的負(fù)載確實超過了服務(wù)器的實際處理能力外，更多的是因為系統(tǒng)存在大量的SQL語句需要優(yōu)化。

一、問題的提出

在項目實際使用中，數(shù)據(jù)是一個長期累計的過程，隨著數(shù)據(jù)庫中數(shù)據(jù)的增加，系統(tǒng)的響應(yīng)速度就成為目前系統(tǒng)需要解決的最主要的問題之一。系統(tǒng)優(yōu)化中一個很重要的方面就是SQL語句的優(yōu)化。對于海量數(shù)據(jù)，劣質(zhì)SQL語句和優(yōu)質(zhì)SQL語句之間的速度差別可以達(dá)到成千上百倍，因此高質(zhì)量的SQL語句，更能提高系統(tǒng)的可用性。

二、SQL語句編寫注意問題

下面就某些SQL語句的where子句編寫中需要注意的問題作詳細(xì)介紹。在這些where子句中，即使某些列存在索引，但是由于編寫了劣質(zhì)的SQL，系統(tǒng)在運行該SQL語句時也不能使用該索引，而同樣使用全表掃描，這就造成了響應(yīng)速度的極大降低。

1. 操作符優(yōu)化

(a) IN 操作符

 在使用中盡量用EXISTS替代IN、用NOT EXISTS替代NOT IN  。

在許多基于基礎(chǔ)表的查詢中，為了滿足一個條件，往往需要對另一個表進(jìn)行聯(lián)接。在這種情況下， 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率。。在子查詢中，NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并。 無論在哪種情況下，NOT IN都是最低效的 (因為它對子查詢中的表執(zhí)行了一個全表遍歷)。。為了避免使用NOT IN ，我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS。

例子： 
（推薦）select* from dt_article where exists(select id from dt_article_category wheredt_article_category。id=dt_article。category_id andtitle='公司新聞')
（不推薦）select* from dt_article where category_id in (select id from dt_article_categorywhere title='公司新聞')

(b) IS NULL 或IS NOT NULL操作（判斷字段是否為空）

判斷字段是否為空一般是不會應(yīng)用索引的，因為索引是不索引空值的。不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值，即使對該列建索引也不會提高性能。任何在where子句中使用is null或is not null的語句優(yōu)化器是不允許使用索引的。 

    例子：

（推薦）select* from dt_article where title>'';
 （不推薦）select* from dt_article where title is null;

(c) > 及 < 操作符（大于或小于操作符）

（推薦）select * from dt_article where id>=101;

（不推薦）select * from dt_article where id>100;

兩者的區(qū)別在于， 前者將直接跳到第一個id等于101的記錄而后者將首先定位到id=100的記錄并且向前掃描到第一個id大于100的記錄。

(d)LIKE操作符

LIKE操作符可以應(yīng)用通配符查詢，里面的通配符組合可能達(dá)到幾乎是任意的查詢，但是如果用得不好則會產(chǎn)生性能上的問題，如like '%福瑞希%'這種查詢不會引用索引，而like'福瑞希%'則會引用范圍索引。

一個實際例子：用dt_article表中內(nèi)容可來查詢， content like'%福瑞希%'這個條件會產(chǎn)生全表掃描，如果改成contentlike '福瑞希%'則會利用content的索引進(jìn)行范圍的查詢，性能肯定大大提高。

在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當(dāng)通配符出現(xiàn)在字符串其他位置時，優(yōu)化器就能利用索引。

(e) UNION操作符

當(dāng)SQL語句需要UNION兩個查詢結(jié)果集合時，這兩個結(jié)果集合會以UNION-ALL的方式被合并， 然后在輸出最終結(jié)果前進(jìn)行去重和排序。 假如用UNION ALL替代UNION， 這樣排序就不是必要了。 效率就會因此得到提高。 需要注重的是，UNION ALL 將重復(fù)輸出兩個結(jié)果集合中相同記錄。 因此各位還是要從業(yè)務(wù)需求分析使用UNIONALL的可行性。 UNION 將對結(jié)果集合去重排序，這個操作會使用到SORT_AREA_SIZE這塊內(nèi)存。 對于這塊內(nèi)存的優(yōu)化也是相當(dāng)重要的。

(f) NOT

我們要避免在索引列上使用NOT， NOT會產(chǎn)生在和在索引列上使用函數(shù)相同的影響。 當(dāng)查詢列碰到”NOT，他就會停止使用索引轉(zhuǎn)而執(zhí)行全表掃描。

(g) OR

    通常情況下， 用UNION替換WHERE子句中的OR將會起到較好的效果。 對索引列使用OR將造成全表掃描。 注重， 以上規(guī)則只針對多個索引列有效。 假如有column沒有被索引， 查詢效率可能會因為你沒有選擇OR而降低。 在下面的例子中， title和category_id上都建有索引。

（推薦）select * from dt_article where title='清洗空氣' union all select * from dt_article where category_id=92

（不推薦）select * from dt_article where title='清洗空氣' or category_id=92 假如你堅持要用OR， 那就需要返回記錄最少的索引列寫在最前面。 
       另外在一些情況下，也可以使用IN來替代OR，     這是一條簡單易記的規(guī)則，但是實際的執(zhí)行效果還須檢驗。

（推薦）select * from dt_article where category_id in (89，92)

（不推薦）select * from dt_article where category_id=92 or category_id=89

(h) DISTINCT

     當(dāng)提交一個包含一對多表信息的查詢時，避免在SELECT子句中使用DISTINCT。 一般可以考慮用EXIST替換， EXISTS 使查詢更為迅速，因為RDBMS核心模塊將在子查詢的條件一旦滿足后，馬上返回結(jié)果。 

2. SQL書寫的影響

 (a) WHERE后面的條件順序影響

WHERE子句后面的條件順序?qū)Υ髷?shù)據(jù)量表的查詢會產(chǎn)生直接的影響。如：

select * from dt_article where category_id=92 and is_hot=1

select * from dt_article where is_hot=1 and category_id=92 

以上兩個SQL中category_id（電壓等級）及is_hot（銷戶標(biāo)志）兩個字段都沒進(jìn)行索引，所以執(zhí)行的時候都是全表掃描，第一條SQL的is_hot=1在記錄集內(nèi)比率為99%，而category_id=92的比率只為1%，在進(jìn)行第一條SQL的時候99%條記錄都進(jìn)行category_id及is_hot的比較，而在進(jìn)行第二條SQL的時候1%條記錄都進(jìn)行category_id及is_hot的比較，以此可以得出第二條SQL的CPU占用率明顯比第一條低。

WHERE解析是采用自下而上的順序解析WHERE子句，根據(jù)這個原理，表之間的連接必須寫在其他WHERE條件之前， 那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。 

3. 更多方面SQL優(yōu)化資料分享

（1） 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)：

ORACLE 的解析器按照從右到左的順序處理FROM子句中的表名，F(xiàn)ROM子句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理，在FROM子句中包含多個表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。如果有3個以上的表連接查詢, 那就需要選擇交叉表(intersectiontable)作為基礎(chǔ)表, 交叉表是指那個被其他表所引用的表.

（2） SELECT子句中避免使用 ‘ * ‘：

ORACLE在解析的過程中, 會將'*' 依次轉(zhuǎn)換成所有的列名, 這個工作是通過查詢數(shù)據(jù)字典完成的, 這意味著將耗費更多的時間。

（3） 減少訪問數(shù)據(jù)庫的次數(shù)：

ORACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等。

（4） 整合簡單,無關(guān)聯(lián)的數(shù)據(jù)庫訪問：

如果你有幾個簡單的數(shù)據(jù)庫查詢語句,你可以把它們整合到一個查詢中(即使它們之間沒有關(guān)系) 。

（5） 用TRUNCATE替代DELETE：

當(dāng)刪除表中的記錄時,在通常情況下, 回滾段(rollbacksegments ) 用來存放可以被恢復(fù)的信息. 如果你沒有COMMIT事務(wù),ORACLE會將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說是恢復(fù)到執(zhí)行刪除命令之前的狀況) 而當(dāng)運用TRUNCATE時, 回滾段不再存放任何可被恢復(fù)的信息.當(dāng)命令運行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時間也會很短. (譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML) 。

（6） 盡量多使用COMMIT：

只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會因為COMMIT所釋放的資源而減少，COMMIT所釋放的資源:

a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息.
b. 被程序語句獲得的鎖
c. redo log buffer 中的空間

（7） 通過內(nèi)部函數(shù)提高SQL效率：

復(fù)雜的SQL往往犧牲了執(zhí)行效率. 能夠掌握上面的運用函數(shù)解決問題的方法在實際工作中是非常有意義的。

（8） 使用表的別名(Alias)：

當(dāng)在SQL語句中連接多個表時, 請使用表的別名并把別名前綴于每個Column上.這樣一來,就可以減少解析的時間并減少那些由Column歧義引起的語法錯誤。

（9） 總是使用索引的第一個列：

如果索引是建立在多個列上, 只有在它的第一個列(leading column)被where子句引用時,優(yōu)化器才會選擇使用該索引. 這也是一條簡單而重要的規(guī)則，當(dāng)僅引用索引的第二個列時,優(yōu)化器使用了全表掃描而忽略了索引。

（10） 避免使用耗費資源的操作：

帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎執(zhí)行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序. 通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫. 如果你的數(shù)據(jù)庫的SORT_AREA_SIZE調(diào)配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強。