SQL Server資料庫效能最佳化之SQL語句篇【轉】

標籤：des   style   blog   http   color   io   os   使用   ar   

SQL Server資料庫效能最佳化之SQL語句篇http://www.blogjava.net/allen-zhe/archive/2010/07/23/326927.html

 

近期項目需要， 做了一段時間的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條件中做Function Compute
這樣做的後果是將在每個行上進行運算，這將導致該列的索引失效而觸發全表掃描。如下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。

 

SQL Server資料庫效能最佳化之SQL語句篇【轉】