近期項(xiàng)目需要，做了一段時(shí)間的SQL Server性能優(yōu)化，遇到了一些問(wèn)題，也積累了一些經(jīng)驗(yàn)，現(xiàn)總結(jié)一下，與君共享。SQL Server性能優(yōu)化涉及到許多方面，如良好的系統(tǒng)和數(shù)據(jù)庫(kù)設(shè)計(jì)，優(yōu)質(zhì)的SQL編寫，合適的數(shù)據(jù)表索引設(shè)計(jì)，甚至各種硬件因素：網(wǎng)絡(luò)性能、服務(wù)器的性能、操作系統(tǒng)的性能，甚至網(wǎng)卡、交換機(jī)等。這篇文章主要講到如何改善SQL語(yǔ)句，還將有另一篇討論如何改善索引。
如何改善SQL語(yǔ)句的一些原則：

1. 按需索取字段，跟“SELECT *”說(shuō)拜拜
字段的提取一定要按照“用多少提多少”的原則，避免使用“SELECT *”這樣的操作。做了這樣一個(gè)實(shí)驗(yàn)，表tblA有1000萬(wàn)數(shù)據(jù)：

由此看來(lái)，我們每少提取一個(gè)字段，數(shù)據(jù)的提取速度就會(huì)有相應(yīng)的提升。但提升的速度還要看您舍棄的字段的大小來(lái)判斷。
另外，關(guān)于“SELECT *“的問(wèn)題，可以參考這篇文章：
http://www.cnblogs.com:80/goodspeed/archive/2007/07/20/index_coverage.html

 

2. 字段名和表名要寫規(guī)范，注意大小寫
這一點(diǎn)要多注意，如果大小寫寫錯(cuò)的話，雖然SQL仍然能正常執(zhí)行，但數(shù)據(jù)庫(kù)系統(tǒng)會(huì)花一定的開(kāi)銷和時(shí)間先要把您寫的規(guī)范成正確的，然后再執(zhí)行SQL。寫對(duì)的話，這個(gè)時(shí)間就省了。
正常的：    select top 10 dteTransaction, txtSystem_id from tblTransactionSystem
不小心的：select top 10 dtetransaction, txtsystem_id from tbltransactionsystem

3. 適當(dāng)使用過(guò)渡表
把表的一個(gè)子集進(jìn)行排序并創(chuàng)建臨時(shí)表，有時(shí)能加速查詢。它有助于避免多重排序操作，而且在其他方面還能簡(jiǎn)化優(yōu)化器的工作。例如：

  如果這個(gè)查詢要被執(zhí)行多次而不止一次，可以把所有未付款的客戶找出來(lái)放在一個(gè)臨時(shí)文件中，并按客戶的名字進(jìn)行排序：    

  然后以下面的方式在臨時(shí)表中查詢：     

臨時(shí)表中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁盤I/O，所以查詢工作量可以得到大幅減少。注意：過(guò)渡臨時(shí)表創(chuàng)建后不會(huì)反映主表的修改。在主表中數(shù)據(jù)頻繁修改的情況下，注意不要丟失數(shù)據(jù)。

4. 別在where條件中做函數(shù)計(jì)算
這樣做的后果是將在每個(gè)行上進(jìn)行運(yùn)算，這將導(dǎo)致該列的索引失效而觸發(fā)全表掃描。如下SQL：

可以改成

這樣會(huì)使用針對(duì)dteCreated的索引，提高查詢效率。

5. IN（NOT IN）操作符與EXISTS（NOT EXISTS）操作符
有時(shí)候會(huì)將一列和一系列值相比較。最簡(jiǎn)單的辦法就是在where子句中使用子查詢。在where子句中可以使用兩種方式的子查詢。如下：
第一種方式使用IN操作符：

 

第二種方式使用EXIST操作符：

 

用IN寫出來(lái)的SQL的優(yōu)點(diǎn)是比較容易寫及清晰易懂，這比較適合現(xiàn)代軟件開(kāi)發(fā)的風(fēng)格。但是用IN的SQL性能總是比較低的，而第二種格式要遠(yuǎn)比第一種格式的效率高。從SQL執(zhí)行的步驟來(lái)分析用IN的SQL與不用IN的SQL有以下區(qū)別：
SQL試圖將其轉(zhuǎn)換成多個(gè)表的連接，如果轉(zhuǎn)換不成功則先執(zhí)行IN里面的子查詢，再查詢外層的表記錄，如果轉(zhuǎn)換成功則直接采用多個(gè)表的連接方式查詢。由此可見(jiàn)用IN的SQL至少多了一個(gè)轉(zhuǎn)換的過(guò)程。一般的SQL都可以轉(zhuǎn)換成功，但對(duì)于含有分組統(tǒng)計(jì)等方面的SQL就不能轉(zhuǎn)換了。
第二種格式中，子查詢以’select 1’開(kāi)始。運(yùn)用EXISTS子句不管子查詢從表中抽取什么數(shù)據(jù)它只查看where子句。這樣優(yōu)化器就不必遍歷整個(gè)表而僅根據(jù)索引就可完成工作（這里假定在where語(yǔ)句中使用的列存在索引）。相對(duì)于IN子句來(lái)說(shuō)，EXISTS使用相連子查詢，構(gòu)造起來(lái)要比IN子查詢困難一些。
通過(guò)使用EXIST，數(shù)據(jù)庫(kù)系統(tǒng)會(huì)首先檢查主查詢，然后運(yùn)行子查詢直到它找到第一個(gè)匹配項(xiàng)，這就節(jié)省了時(shí)間。數(shù)據(jù)庫(kù)系統(tǒng)在執(zhí)行IN子查詢時(shí)，首先執(zhí)行子查詢，并將獲得的結(jié)果列表存放在一個(gè)加了索引的臨時(shí)表中。在執(zhí)行子查詢之前，系統(tǒng)先將主查詢掛起，待子查詢執(zhí)行完畢，存放在臨時(shí)表中以后再執(zhí)行主查詢。這也就是使用EXISTS比使用IN通常查詢速度快的原因。
同時(shí)應(yīng)盡可能使用NOT EXISTS來(lái)代替NOT IN，盡管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS要比NOT IN查詢效率更高。

6. IS NULL 或 IS NOT NULL操作（判斷字段是否為空）
不能用null作索引，任何包含null值的列都將不會(huì)被包含在索引中，因?yàn)锽樹(shù)索引是不索引空值的。即使索引有多列這樣的情況下，只要這些列中有一列含有null，該列就會(huì)從索引中排除。也就是說(shuō)如果某列存在空值，即使對(duì)該列建索引也不會(huì)提高性能。
任何在where子句中使用is null或is not null的語(yǔ)句優(yōu)化器是不允許使用索引的。
推薦方案：用其它相同功能的操作運(yùn)算代替，如a is not null 改為 a>0 或a>’等。另外還設(shè)置字段不允許為空，而用一個(gè)缺省值代替空值，如一個(gè)datetime字段，可以將默認(rèn)時(shí)間設(shè)為“1900-01-01”。

7. > 及 < 操作符（大于或小于操作符）
       大于或小于操作符一般情況下是不用調(diào)整的，因?yàn)樗兴饕蜁?huì)采用索引查找，但有的情況下可以對(duì)它進(jìn)行優(yōu)化，如一個(gè)表有100萬(wàn)記錄，一個(gè)數(shù)值型字段A，30 萬(wàn)記錄的A=0，30萬(wàn)記錄的A=1，39萬(wàn)記錄的A=2，1萬(wàn)記錄的A=3。那么執(zhí)行A>2與A>=3的效果就有很大的區(qū)別了，因?yàn)?A>2時(shí)sql會(huì)先找出為2的記錄索引再進(jìn)行比較，而A>=3時(shí)sql則直接找到=3的記錄索引。可結(jié)合非聚集索引一起考慮。

8. LIKE操作符
LIKE 操作符可以應(yīng)用通配符查詢，里面的通配符組合可能達(dá)到幾乎是任意的查詢，但是如果用得不好則會(huì)產(chǎn)生性能上的問(wèn)題，如LIKE ‘%5400%’ 這種查詢不會(huì)引用索引，而LIKE ‘X5400%’則會(huì)引用范圍索引。因?yàn)樗饕臄[放是依據(jù)字段值升序或降序排列，like'%*'這種用法，不能利用有序的數(shù)據(jù)結(jié)構(gòu)，利用二分法查找數(shù)據(jù)。一個(gè)實(shí)際例子：用YW_YHJBQK表中營(yíng)業(yè)編號(hào)后面的戶標(biāo)識(shí)號(hào)可來(lái)查詢營(yíng)業(yè)編號(hào) YY_BH LIKE ‘%5400%’ 這個(gè)條件會(huì)產(chǎn)生全表掃描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 則會(huì)利用YY_BH的索引進(jìn)行兩個(gè)范圍的查詢，性能肯定大大提高。

9. 查詢條件中的適當(dāng)與不適當(dāng)
查詢參數(shù)可以包含一下操作：=、<、>、>=、<=、BETWEEN、部分like。其中，like當(dāng)這樣使用時(shí)會(huì)用到索引：like '*%',但like'%*'就用不到索引。
不適當(dāng)?shù)牟樵儏?shù)有：NOT 、!= 、<>、 !>、 !< 、NOT EXISTS、 NOT IN 、NOT LIKE等，還有一些不當(dāng)?shù)挠梅ǎ纾簩?duì)數(shù)據(jù)進(jìn)行計(jì)算，負(fù)向查詢、等號(hào)左邊使用函數(shù)、使用OR。上述語(yǔ)法都不用不上索引，降低程序的效率。

10. 慎用DELETE

一般在存儲(chǔ)過(guò)程中或多或少都會(huì)實(shí)現(xiàn)一些刪除數(shù)據(jù)的邏輯。對(duì)小數(shù)量的表來(lái)說(shuō)，問(wèn)題倒是不大。但對(duì)于大數(shù)據(jù)量的表來(lái)說(shuō)，采用delete刪除數(shù)據(jù)會(huì)對(duì)儲(chǔ)存過(guò)程的性能產(chǎn)生一定的影響。因?yàn)閐elete采用的是全表逐條掃描的方式進(jìn)行，是一種事務(wù)性操作，會(huì)計(jì)入SQL Server的事務(wù)日志中。不但增加了運(yùn)行時(shí)間，同時(shí)也頻繁寫入LOG文件，導(dǎo)致LOG文件過(guò)大，過(guò)分消耗磁盤空間。所以，可以用truncate操作代替delete，truncate并不會(huì)計(jì)入事務(wù)日志中，同時(shí)也是不帶條件的刪除，執(zhí)行速度很快。又或者直接drop掉表重新創(chuàng)建，有時(shí)都會(huì)比delete來(lái)得快。

PS: 第10點(diǎn)引出的兩種清空SQL Server日志文件的方法

一種方法：清空日志。

1．打開(kāi)查詢分析器，輸入命令DUMP TRANSACTION 數(shù)據(jù)庫(kù)名 WITH NO_LOG

2.再打開(kāi)企業(yè)管理器--右鍵你要壓縮的數(shù)據(jù)庫(kù)--所有任務(wù)--收縮數(shù)據(jù)庫(kù)--收縮文件--選擇日志文件--在收縮方式里選擇收縮至XXM,這里會(huì)給出一個(gè)允許收縮到的最小M數(shù),直接輸入這個(gè)數(shù),確定就可以了。

另一種方法有一定的風(fēng)險(xiǎn)性，因?yàn)镾QL SERVER的日志文件不是即時(shí)寫入數(shù)據(jù)庫(kù)主文件的，如處理不當(dāng)，會(huì)造成數(shù)據(jù)的損失。

1: 刪除LOG

分離數(shù)據(jù)庫(kù) 企業(yè)管理器－＞服務(wù)器－＞數(shù)據(jù)庫(kù)－＞右鍵－＞分離數(shù)據(jù)庫(kù)

2：刪除LOG文件

附加數(shù)據(jù)庫(kù) 企業(yè)管理器－＞服務(wù)器－＞數(shù)據(jù)庫(kù)－＞右鍵－＞附加數(shù)據(jù)庫(kù)

此法生成新的LOG，大小只有500多K。

                                                                                                    THE END