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