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

文章目錄

• 1. 按需索取欄位，跟“SELECT *”說拜拜
• 2. 欄位名和表名要寫規範，注意大小寫
• 3. 適當使用過渡表
• 4. 別在where條件中做Function Compute
• 5. IN（NOT IN）操作符與EXISTS（NOT EXISTS）操作符
• 6. IS NULL 或 IS NOT NULL操作（判斷欄位是否為空白）
• 7. > 及 < 操作符（大於或小於操作符）
• 8. LIKE操作符
• 9. 查詢條件中的適當與不適當

近期項目需要，做了一段時間的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毫秒

由此看來，我們每少提取一個欄位，資料的提取速度就會有相應的提升。但提升的速度還要看您捨棄的欄位的大小來判斷。

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   ＊   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。上述文法都不用不上索引，降低程式的效率。