為了獲得穩(wěn)定的執(zhí)行性能，SQL語(yǔ)句越簡(jiǎn)單越好。對(duì)復(fù)雜的SQL語(yǔ)句，要設(shè)法對(duì)之進(jìn)行簡(jiǎn)化，本文給大家介紹優(yōu)化SQL語(yǔ)句提高數(shù)據(jù)庫(kù)性能。
現(xiàn)在數(shù)據(jù)越來(lái)越復(fù)雜和龐大，很多時(shí)候影響程序運(yùn)行性能不理想的原因中除了一部分是因?yàn)閼?yīng)用程序的負(fù)載確實(shí)超過(guò)了服務(wù)器的實(shí)際處理能力外，更多的是因?yàn)橄到y(tǒng)存在大量的SQL語(yǔ)句需要優(yōu)化。
一、問(wèn)題的提出
在項(xiàng)目實(shí)際使用中，數(shù)據(jù)是一個(gè)長(zhǎng)期累計(jì)的過(guò)程，隨著數(shù)據(jù)庫(kù)中數(shù)據(jù)的增加，系統(tǒng)的響應(yīng)速度就成為目前系統(tǒng)需要解決的最主要的問(wèn)題之一。系統(tǒng)優(yōu)化中一個(gè)很重要的方面就是SQL語(yǔ)句的優(yōu)化。對(duì)于海量數(shù)據(jù)，劣質(zhì)SQL語(yǔ)句和優(yōu)質(zhì)SQL語(yǔ)句之間的速度差別可以達(dá)到成千上百倍，因此高質(zhì)量的SQL語(yǔ)句，更能提高系統(tǒng)的可用性。
二、SQL語(yǔ)句編寫(xiě)注意問(wèn)題
下面就某些SQL語(yǔ)句的where子句編寫(xiě)中需要注意的問(wèn)題作詳細(xì)介紹。在這些where子句中，即使某些列存在索引，但是由于編寫(xiě)了劣質(zhì)的SQL，系統(tǒng)在運(yùn)行該SQL語(yǔ)句時(shí)也不能使用該索引，而同樣使用全表掃描，這就造成了響應(yīng)速度的極大降低。
1. 操作符優(yōu)化
(a) IN 操作符
在使用中盡量用EXISTS替代IN、用NOT EXISTS替代NOT IN 。
在許多基于基礎(chǔ)表的查詢(xún)中，為了滿(mǎn)足一個(gè)條件，往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接。在這種情況下， 使用EXISTS(或NOT EXISTS)通常將提高查詢(xún)的效率。。在子查詢(xún)中，NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并。 無(wú)論在哪種情況下，NOT IN都是最低效的 (因?yàn)樗鼘?duì)子查詢(xún)中的表執(zhí)行了一個(gè)全表遍歷)。。為了避免使用NOT IN ，我們可以把它改寫(xiě)成外連接(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操作（判斷字段是否為空）
判斷字段是否為空一般是不會(huì)應(yīng)用索引的，因?yàn)樗饕遣凰饕罩档?。不能用null作索引，任何包含null值的列都將不會(huì)被包含在索引中。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會(huì)從索引中排除。也就是說(shuō)如果某列存在空值，即使對(duì)該列建索引也不會(huì)提高性能。任何在where子句中使用is null或is not null的語(yǔ)句優(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ū)別在于， 前者將直接跳到第一個(gè)id等于101的記錄而后者將首先定位到id=100的記錄并且向前掃描到第一個(gè)id大于100的記錄。
(d)LIKE操作符
LIKE操作符可以應(yīng)用通配符查詢(xún)，里面的通配符組合可能達(dá)到幾乎是任意的查詢(xún)，但是如果用得不好則會(huì)產(chǎn)生性能上的問(wèn)題，如like '%福瑞希%'這種查詢(xún)不會(huì)引用索引，而like'福瑞希%'則會(huì)引用范圍索引。
一個(gè)實(shí)際例子：用dt_article表中內(nèi)容可來(lái)查詢(xún)， content like'%福瑞希%'這個(gè)條件會(huì)產(chǎn)生全表掃描，如果改成contentlike '福瑞希%'則會(huì)利用content的索引進(jìn)行范圍的查詢(xún)，性能肯定大大提高。
在很多情況下可能無(wú)法避免這種情況，但是一定要心中有底，通配符如此使用會(huì)降低查詢(xún)速度。然而當(dāng)通配符出現(xiàn)在字符串其他位置時(shí)，優(yōu)化器就能利用索引。
(e) UNION操作符
當(dāng)SQL語(yǔ)句需要UNION兩個(gè)查詢(xún)結(jié)果集合時(shí)，這兩個(gè)結(jié)果集合會(huì)以UNION-ALL的方式被合并， 然后在輸出最終結(jié)果前進(jìn)行去重和排序。 假如用UNION ALL替代UNION， 這樣排序就不是必要了。 效率就會(huì)因此得到提高。 需要注重的是，UNION ALL 將重復(fù)輸出兩個(gè)結(jié)果集合中相同記錄。 因此各位還是要從業(yè)務(wù)需求分析使用UNIONALL的可行性。 UNION 將對(duì)結(jié)果集合去重排序，這個(gè)操作會(huì)使用到SORT_AREA_SIZE這塊內(nèi)存。 對(duì)于這塊內(nèi)存的優(yōu)化也是相當(dāng)重要的。
(f) NOT
我們要避免在索引列上使用NOT， NOT會(huì)產(chǎn)生在和在索引列上使用函數(shù)相同的影響。 當(dāng)查詢(xún)列碰到”NOT，他就會(huì)停止使用索引轉(zhuǎn)而執(zhí)行全表掃描。
(g) OR
通常情況下， 用UNION替換WHERE子句中的OR將會(huì)起到較好的效果。 對(duì)索引列使用OR將造成全表掃描。 注重， 以上規(guī)則只針對(duì)多個(gè)索引列有效。 假如有column沒(méi)有被索引， 查詢(xún)效率可能會(huì)因?yàn)槟銢](méi)有選擇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 假如你堅(jiān)持要用OR， 那就需要返回記錄最少的索引列寫(xiě)在最前面。
另外在一些情況下，也可以使用IN來(lái)替代OR， 這是一條簡(jiǎn)單易記的規(guī)則，但是實(shí)際的執(zhí)行效果還須檢驗(yàn)。
（推薦）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)提交一個(gè)包含一對(duì)多表信息的查詢(xún)時(shí)，避免在SELECT子句中使用DISTINCT。 一般可以考慮用EXIST替換， EXISTS 使查詢(xún)更為迅速，因?yàn)镽DBMS核心模塊將在子查詢(xún)的條件一旦滿(mǎn)足后，馬上返回結(jié)果。
2. SQL書(shū)寫(xiě)的影響
(a) WHERE后面的條件順序影響
WHERE子句后面的條件順序?qū)Υ髷?shù)據(jù)量表的查詢(xún)會(huì)產(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
以上兩個(gè)SQL中category_id（電壓等級(jí)）及is_hot（銷(xiāo)戶(hù)標(biāo)志）兩個(gè)字段都沒(méi)進(jìn)行索引，所以執(zhí)行的時(shí)候都是全表掃描，第一條SQL的is_hot=1在記錄集內(nèi)比率為99%，而category_id=92的比率只為1%，在進(jìn)行第一條SQL的時(shí)候99%條記錄都進(jìn)行category_id及is_hot的比較，而在進(jìn)行第二條SQL的時(shí)候1%條記錄都進(jìn)行category_id及is_hot的比較，以此可以得出第二條SQL的CPU占用率明顯比第一條低。
WHERE解析是采用自下而上的順序解析WHERE子句，根據(jù)這個(gè)原理，表之間的連接必須寫(xiě)在其他WHERE條件之前， 那些可以過(guò)濾掉最大數(shù)量記錄的條件必須寫(xiě)在WHERE子句的末尾。
3. 更多方面SQL優(yōu)化資料分享
（1） 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)：
ORACLE 的解析器按照從右到左的順序處理FROM子句中的表名，F(xiàn)ROM子句中寫(xiě)在最后的表(基礎(chǔ)表 driving table)將被最先處理，在FROM子句中包含多個(gè)表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。如果有3個(gè)以上的表連接查詢(xún), 那就需要選擇交叉表(intersectiontable)作為基礎(chǔ)表, 交叉表是指那個(gè)被其他表所引用的表.
（2） SELECT子句中避免使用 ‘ * ‘：
ORACLE在解析的過(guò)程中, 會(huì)將'*' 依次轉(zhuǎn)換成所有的列名, 這個(gè)工作是通過(guò)查詢(xún)數(shù)據(jù)字典完成的, 這意味著將耗費(fèi)更多的時(shí)間。
（3） 減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù)：
ORACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語(yǔ)句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等。
（4） 整合簡(jiǎn)單,無(wú)關(guān)聯(lián)的數(shù)據(jù)庫(kù)訪問(wèn)：
如果你有幾個(gè)簡(jiǎn)單的數(shù)據(jù)庫(kù)查詢(xún)語(yǔ)句,你可以把它們整合到一個(gè)查詢(xún)中(即使它們之間沒(méi)有關(guān)系) 。
（5） 用TRUNCATE替代DELETE：
當(dāng)刪除表中的記錄時(shí),在通常情況下, 回滾段(rollbacksegments ) 用來(lái)存放可以被恢復(fù)的信息. 如果你沒(méi)有COMMIT事務(wù),ORACLE會(huì)將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說(shuō)是恢復(fù)到執(zhí)行刪除命令之前的狀況) 而當(dāng)運(yùn)用TRUNCATE時(shí), 回滾段不再存放任何可被恢復(fù)的信息.當(dāng)命令運(yùn)行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時(shí)間也會(huì)很短. (譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML) 。
（6） 盡量多使用COMMIT：
只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會(huì)因?yàn)镃OMMIT所釋放的資源而減少，COMMIT所釋放的資源:
a. 回滾段上用于恢復(fù)數(shù)據(jù)的信息.
b. 被程序語(yǔ)句獲得的鎖
c. redo log buffer 中的空間
（7） 通過(guò)內(nèi)部函數(shù)提高SQL效率：
復(fù)雜的SQL往往犧牲了執(zhí)行效率. 能夠掌握上面的運(yùn)用函數(shù)解決問(wèn)題的方法在實(shí)際工作中是非常有意義的。
（8） 使用表的別名(Alias)：
當(dāng)在SQL語(yǔ)句中連接多個(gè)表時(shí), 請(qǐng)使用表的別名并把別名前綴于每個(gè)Column上.這樣一來(lái),就可以減少解析的時(shí)間并減少那些由Column歧義引起的語(yǔ)法錯(cuò)誤。
（9） 總是使用索引的第一個(gè)列：
如果索引是建立在多個(gè)列上, 只有在它的第一個(gè)列(leading column)被where子句引用時(shí),優(yōu)化器才會(huì)選擇使用該索引. 這也是一條簡(jiǎn)單而重要的規(guī)則，當(dāng)僅引用索引的第二個(gè)列時(shí),優(yōu)化器使用了全表掃描而忽略了索引。
（10） 避免使用耗費(fèi)資源的操作：
帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語(yǔ)句會(huì)啟動(dòng)SQL引擎執(zhí)行耗費(fèi)資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序. 通常, 帶有UNION, MINUS , INTERSECT的SQL語(yǔ)句都可以用其他方式重寫(xiě). 如果你的數(shù)據(jù)庫(kù)的SORT_AREA_SIZE調(diào)配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強(qiáng)。