近期項目需要，做了一段時間的SQL Server性能優(yōu)化，遇到了一些問題，也積累了一些經(jīng)驗，現(xiàn)總結(jié)一下，與君共享。SQL Server性能優(yōu)化涉及到許多方面，如良好的系統(tǒng)和數(shù)據(jù)庫設(shè)計，優(yōu)質(zhì)的SQL編寫，合適的數(shù)據(jù)表索引設(shè)計，甚至各種硬件因素：網(wǎng)絡(luò)性能、服務(wù)器的性能、操作系統(tǒng)的性能，甚至網(wǎng)卡、交換機等。這篇文章主要講到如何改善SQL語句，還將有另一篇討論如何改善索引。
如何改善SQL語句的一些原則：
1. 按需索取字段，跟“SELECT *”說拜拜
字段的提取一定要按照“用多少提多少”的原則，避免使用“SELECT *”這樣的操作。做了這樣一個實驗，表tblA有1000萬數(shù)據(jù)：
select top 10000 c1, c2, c3, c4 from tblA order by c1 desc --用時：4673毫秒
select top 10000 c1, c2, c3 from tblA order by c1 desc --用時：1376毫秒
select top 10000 c1, c2 from tblA order by c1 desc --用時：80毫秒
由此看來，我們每少提取一個字段，數(shù)據(jù)的提取速度就會有相應(yīng)的提升。但提升的速度還要看您舍棄的字段的大小來判斷。
另外，關(guān)于“SELECT *“的問題，可以參考這篇文章：
http://www.cnblogs.com:80/goodspeed/archive/2007/07/20/index_coverage.html
2. 字段名和表名要寫規(guī)范，注意大小寫
這一點要多注意，如果大小寫寫錯的話，雖然SQL仍然能正常執(zhí)行，但數(shù)據(jù)庫系統(tǒng)會花一定的開銷和時間先要把您寫的規(guī)范成正確的，然后再執(zhí)行SQL。寫對的話，這個時間就省了。
正常的： select top 10 dteTransaction, txtSystem_id from tblTransactionSystem
不小心的：select top 10 dtetransaction, txtsystem_id from tbltransactionsystem
3. 適當使用過渡表
把表的一個子集進行排序并創(chuàng)建臨時表，有時能加速查詢。它有助于避免多重排序操作，而且在其他方面還能簡化優(yōu)化器的工作。例如：
SELECT cust.name，rcvbles.balance，……other columns
FROM cust，rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
AND cust.postcode>“98000”
ORDER BY cust.name
如果這個查詢要被執(zhí)行多次而不止一次，可以把所有未付款的客戶找出來放在一個臨時文件中，并按客戶的名字進行排序：
SELECT cust.name，rcvbles.balance，……other columns
INTO temp_cust_with_balance
FROM cust，rcvbles
WHERE cust.customer_id = rcvlbes.customer_id
AND rcvblls.balance>0
ORDER BY cust.name
然后以下面的方式在臨時表中查詢：
SELECT cl,c2 FROM temp_cust_with_balance WHERE postcode>“98000”
臨時表中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁盤I/O，所以查詢工作量可以得到大幅減少。注意：過渡臨時表創(chuàng)建后不會反映主表的修改。在主表中數(shù)據(jù)頻繁修改的情況下，注意不要丟失數(shù)據(jù)。
4. 別在where條件中做函數(shù)計算
這樣做的后果是將在每個行上進行運算，這將導(dǎo)致該列的索引失效而觸發(fā)全表掃描。如下SQL：
select * from users where YEAR(dteCreated) < 2007
可以改成
select * from users where dteCreated <‘2007-01-01’
這樣會使用針對dteCreated的索引，提高查詢效率。
5. IN（NOT IN）操作符與EXISTS（NOT EXISTS）操作符
有時候會將一列和一系列值相比較。最簡單的辦法就是在where子句中使用子查詢。在where子句中可以使用兩種方式的子查詢。如下：
第一種方式使用IN操作符：
select a.id from tblA a where a.id in (select b.id from tblB b)
第二種方式使用EXIST操作符：
select a.id from tblA a where exists (select 1 from tblB b where b.id = a.id)
用IN寫出來的SQL的優(yōu)點是比較容易寫及清晰易懂，這比較適合現(xiàn)代軟件開發(fā)的風格。但是用IN的SQL性能總是比較低的，而第二種格式要遠比第一種格式的效率高。從SQL執(zhí)行的步驟來分析用IN的SQL與不用IN的SQL有以下區(qū)別：
SQL試圖將其轉(zhuǎn)換成多個表的連接，如果轉(zhuǎn)換不成功則先執(zhí)行IN里面的子查詢，再查詢外層的表記錄，如果轉(zhuǎn)換成功則直接采用多個表的連接方式查詢。由此可見用IN的SQL至少多了一個轉(zhuǎn)換的過程。一般的SQL都可以轉(zhuǎn)換成功，但對于含有分組統(tǒng)計等方面的SQL就不能轉(zhuǎn)換了。
第二種格式中，子查詢以’select 1’開始。運用EXISTS子句不管子查詢從表中抽取什么數(shù)據(jù)它只查看where子句。這樣優(yōu)化器就不必遍歷整個表而僅根據(jù)索引就可完成工作（這里假定在where語句中使用的列存在索引）。相對于IN子句來說，EXISTS使用相連子查詢，構(gòu)造起來要比IN子查詢困難一些。
通過使用EXIST，數(shù)據(jù)庫系統(tǒng)會首先檢查主查詢，然后運行子查詢直到它找到第一個匹配項，這就節(jié)省了時間。數(shù)據(jù)庫系統(tǒng)在執(zhí)行IN子查詢時，首先執(zhí)行子查詢，并將獲得的結(jié)果列表存放在一個加了索引的臨時表中。在執(zhí)行子查詢之前，系統(tǒng)先將主查詢掛起，待子查詢執(zhí)行完畢，存放在臨時表中以后再執(zhí)行主查詢。這也就是使用EXISTS比使用IN通常查詢速度快的原因。
同時應(yīng)盡可能使用NOT EXISTS來代替NOT IN，盡管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查詢效率更高。
6. IS NULL 或 IS NOT NULL操作（判斷字段是否為空）
不能用null作索引，任何包含null值的列都將不會被包含在索引中，因為B樹索引是不索引空值的。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值，即使對該列建索引也不會提高性能。
任何在where子句中使用is null或is not null的語句優(yōu)化器是不允許使用索引的。
推薦方案：用其它相同功能的操作運算代替，如a is not null 改為 a>0 或a>’等。另外還設(shè)置字段不允許為空，而用一個缺省值代替空值，如一個datetime字段，可以將默認時間設(shè)為“1900-01-01”。
7. > 及 < 操作符（大于或小于操作符）
大于或小于操作符一般情況下是不用調(diào)整的，因為它有索引就會采用索引查找，但有的情況下可以對它進行優(yōu)化，如一個表有100萬記錄，一個數(shù)值型字段A，30 萬記錄的A=0，30萬記錄的A=1，39萬記錄的A=2，1萬記錄的A=3。那么執(zhí)行A>2與A>=3的效果就有很大的區(qū)別了，因為 A>2時sql會先找出為2的記錄索引再進行比較，而A>=3時sql則直接找到=3的記錄索引。可結(jié)合非聚集索引一起考慮。
8. LIKE操作符
LIKE 操作符可以應(yīng)用通配符查詢，里面的通配符組合可能達到幾乎是任意的查詢，但是如果用得不好則會產(chǎn)生性能上的問題，如LIKE ‘%5400%’ 這種查詢不會引用索引，而LIKE ‘X5400%’則會引用范圍索引。因為索引的擺放是依據(jù)字段值升序或降序排列，like'%*'這種用法，不能利用有序的數(shù)據(jù)結(jié)構(gòu)，利用二分法查找數(shù)據(jù)。一個實際例子：用YW_YHJBQK表中營業(yè)編號后面的戶標識號可來查詢營業(yè)編號 YY_BH LIKE ‘%5400%’ 這個條件會產(chǎn)生全表掃描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 則會利用YY_BH的索引進行兩個范圍的查詢，性能肯定大大提高。
9. 查詢條件中的適當與不適當
查詢參數(shù)可以包含一下操作：=、<、>、>=、<=、BETWEEN、部分like。其中，like當這樣使用時會用到索引：like '*%',但like'%*'就用不到索引。
不適當?shù)牟樵儏?shù)有：NOT 、!= 、<>、 !>、 !< 、NOT EXISTS、 NOT IN 、NOT LIKE等，還有一些不當?shù)挠梅ǎ纾簩?shù)據(jù)進行計算，負向查詢、等號左邊使用函數(shù)、使用OR。上述語法都不用不上索引，降低程序的效率。
10. 慎用DELETE
一般在存儲過程中或多或少都會實現(xiàn)一些刪除數(shù)據(jù)的邏輯。對小數(shù)量的表來說，問題倒是不大。但對于大數(shù)據(jù)量的表來說，采用delete刪除數(shù)據(jù)會對儲存過程的性能產(chǎn)生一定的影響。因為delete采用的是全表逐條掃描的方式進行，是一種事務(wù)性操作，會計入SQL Server的事務(wù)日志中。不但增加了運行時間，同時也頻繁寫入LOG文件，導(dǎo)致LOG文件過大，過分消耗磁盤空間。所以，可以用truncate操作代替delete，truncate并不會計入事務(wù)日志中，同時也是不帶條件的刪除，執(zhí)行速度很快。又或者直接drop掉表重新創(chuàng)建，有時都會比delete來得快。
PS: 第10點引出的兩種清空SQL Server日志文件的方法
一種方法：清空日志。
1．打開查詢分析器，輸入命令DUMP TRANSACTION 數(shù)據(jù)庫名 WITH NO_LOG
2.再打開企業(yè)管理器--右鍵你要壓縮的數(shù)據(jù)庫--所有任務(wù)--收縮數(shù)據(jù)庫--收縮文件--選擇日志文件--在收縮方式里選擇收縮至XXM,這里會給出一個允許收縮到的最小M數(shù),直接輸入這個數(shù),確定就可以了。
另一種方法有一定的風險性，因為SQL SERVER的日志文件不是即時寫入數(shù)據(jù)庫主文件的，如處理不當，會造成數(shù)據(jù)的損失。
1: 刪除LOG
分離數(shù)據(jù)庫 企業(yè)管理器－＞服務(wù)器－＞數(shù)據(jù)庫－＞右鍵－＞分離數(shù)據(jù)庫
2：刪除LOG文件
附加數(shù)據(jù)庫 企業(yè)管理器－＞服務(wù)器－＞數(shù)據(jù)庫－＞右鍵－＞附加數(shù)據(jù)庫
此法生成新的LOG，大小只有500多K。
THE END
posted on 2010-07-23 13:27
小立飛刀 閱讀(9561)
評論(1) 編輯 收藏 所屬分類:
Database