SQL SERVER效能優化綜述

近期因工作需要，希望比較全面的總結下SQL SERVER資料庫效能優化相關的注意事項，在網上搜尋了一下,發現很多文章,有的都列出了上百條,但是仔細看發現，有很多似是而非或者過時(可能對SQL SERVER6.5以前的版本或者ORACLE是適用的)的資訊，只好自己根據以前的經驗和測試結果進行總結了。

　　我始終認為，一個系統的效能的提高，不單單是試執行或者維護階段的效能調優的任務，也不單單是開發階段的事情，而是在整個軟體生命週期都需要注意，進行有效工作才能達到的。所以我希望按照軟體生命週期的不同階段來總結資料庫效能優化相關的注意事項。

　　一、 分析階段

　　一般來說，在系統分析階段往往有太多需要關注的地方，系統各種功能性、可用性、可靠性、安全性需求往往吸引了我們大部分的注意力，但是，我們必須注意，效能是很重要的非功能性需求，必須根據系統的特點確定其實時性需求、響應時間的需求、硬體的配置等。最好能有各種需求的量化的指標。

　　另一方面，在分析階段應該根據各種需求區分出系統的型別，大的方面，區分是OLTP(聯機事務處理系統)和OLAP(聯機分析處理系統)。

　　二、 設計階段

　　設計階段可以說是以後系統效能的關鍵階段，在這個階段，有一個關係到以後幾乎所有效能調優的過程—資料庫設計。

　　在資料庫設計完成後，可以進行初步的索引設計，好的索引設計可以指導編碼階段寫出高效率的程式碼，為整個系統的效能打下良好的基礎。

　　以下是效能要求設計階段需要注意的：

　　1、 資料庫邏輯設計的規範化

　　資料庫邏輯設計的規範化就是我們一般所說的正規化，我們可以這樣來簡單理解正規化：

　　第1規範：沒有重複的組或多值的列，這是資料庫設計的最低要求。

　　第2規範: 每個非關鍵欄位必須依賴於主關鍵字，不能依賴於一個組合式主關鍵字的某些組成部分。消除部分依賴，大部分情況下，資料庫設計都應該達到第二正規化。

　　第3規範: 一個非關鍵欄位不能依賴於另一個非關鍵欄位。消除傳遞依賴，達到第三正規化應該是系統中大部分表的要求，除非一些特殊作用的表。

　　更高的正規化要求這裡就不再作介紹了，個人認為，如果全部達到第二正規化，大部分達到第三正規化，系統會產生較少的列和較多的表，因而減少了資料冗餘，也利於效能的提高。

　　2、 合理的冗餘

　　完全按照規範化設計的系統幾乎是不可能的，除非系統特別的小，在規範化設計後，有計劃地加入冗餘是必要的。

　　冗餘可以是冗餘資料庫、冗餘表或者冗餘欄位，不同粒度的冗餘可以起到不同的作用。

　　冗餘可以是為了程式設計方便而增加，也可以是為了效能的提高而增加。從效能角度來說，冗餘資料庫可以分散資料庫壓力，冗餘表可以分散資料量大的表的併發壓力，也可以加快特殊查詢的速度，冗餘欄位可以有效減少資料庫表的連線，提高效率。

　　3、 主鍵的設計

　　主鍵是必要的，SQL SERVER的主鍵同時是一個唯一索引，而且在實際應用中，我們往往選擇最小的鍵組合作為主鍵，所以主鍵往往適合作為表的聚集索引。聚集索引對查詢的影響是比較大的，這個在下面索引的敘述。

　　在有多個鍵的表，主鍵的選擇也比較重要，一般選擇總的長度小的鍵，小的鍵的比較速度快，同時小的鍵可以使主鍵的B樹結構的層次更少。

　　主鍵的選擇還要注意組合主鍵的欄位次序，對於組合主鍵來說，不同的欄位次序的主鍵的效能差別可能會很大，一般應該選擇重複率低、單獨或者組合查詢可能性大的欄位放在前面。

　　4、 外來鍵的設計

　　外來鍵作為資料庫物件，很多人認為麻煩而不用，實際上，外來鍵在大部分情況下是很有用的，理由是：

　　外來鍵是最高效的一致性維護方法，資料庫的一致性要求，依次可以用外來鍵、CHECK約束、規則約束、觸發器、客戶端程式，一般認為，離資料越近的方法效率越高。

　　謹慎使用級聯刪除和級聯更新，級聯刪除和級聯更新作為SQL SERVER 2000當年的新功能，在2005作了保留，應該有其可用之處。我這裡說的謹慎，是因為級聯刪除和級聯更新有些突破了傳統的關於外來鍵的定義，功能有點太過強大，使用前必須確定自己已經把握好其功能範圍，否則，級聯刪除和級聯更新可能讓你的資料莫名其妙的被修改或者丟失。從效能看級聯刪除和級聯更新是比其他方法更高效的方法。

　　5、 欄位的設計

　　欄位是資料庫最基本的單位，其設計對效能的影響是很大的。需要注意如下：

　　A、資料型別儘量用數字型，數字型的比較比字元型的快很多。

　　B、 資料型別儘量小，這裡的儘量小是指在滿足可以預見的未來需求的前提下的。

　　C、 儘量不要允許NULL，除非必要，可以用NOT NULL+DEFAULT代替。

　　D、少用TEXT和IMAGE，二進位制欄位的讀寫是比較慢的，而且，讀取的方法也不多，大部分情況下最好不用。

　　E、 自增欄位要慎用，不利於資料遷移。

　　6、 資料庫物理儲存和環境的設計

　　在設計階段，可以對資料庫的物理儲存、作業系統環境、網路環境進行必要的設計，使得我們的系統在將來能適應比較多的使用者併發和比較大的資料量。

　　這裡需要注意檔案組的作用，適用檔案組可以有效把I/O操作分散到不同的物理硬碟，提高併發能力。

　　7、 系統設計

　　整個系統的設計特別是系統結構設計對效能是有很大影響的，對於一般的OLTP系統，可以選擇C/S結構、三層的C/S結構等，不同的系統結構其效能的關鍵也有所不同。

　　系統設計階段應該歸納一些業務邏輯放在資料庫程式設計實現，資料庫程式設計包括資料庫儲存過程、觸發器和函式。用資料庫程式設計實現業務邏輯的好處是減少網路流量並可更充分利用資料庫的預編譯和快取功能。

　　8、 索引的設計

　　在設計階段，可以根據功能和效能的需求進行初步的索引設計，這裡需要根據預計的資料量和查詢來設計索引，可能與將來實際使用的時候會有所區別。

　　關於索引的選擇，應改主意：

　　A、 根據資料量決定哪些表需要增加索引，資料量小的可以只有主鍵。

　　B、 根據使用頻率決定哪些欄位需要建立索引，選擇經常作為連線條件、篩選條件、聚合查詢、排序的欄位作為索引的候選欄位。

　　C、 把經常一起出現的欄位組合在一起，組成組合索引，組合索引的欄位順序與主鍵一樣，也需要把最常用的欄位放在前面，把重複率低的欄位放在前面。

　　D、 一個表不要加太多索引，因為索引影響插入和更新的速度。

　　三、 編碼階段

　　編碼階段是本文的重點，因為在設計確定的情況下，編碼的質量幾乎決定了整個系統的質量。

　　編碼階段首先是需要所有程式設計師有效能意識，也就是在實現功能同時有考慮效能的思想，資料庫是能進行集合運算的工具，我們應該儘量的利用這個工具，所謂集合運算實際是批量運算，就是儘量減少在客戶端進行大資料量的迴圈操作，而用SQL語句或者儲存過程代替。關於思想和意識，很難說得很清楚，需要在程式設計過程中來體會。

　　下面羅列一些程式設計階段需要注意的事項：

　　1、 只返回需要的資料

　　返回資料到客戶端至少需要資料庫提取資料、網路傳輸資料、客戶端接收資料以及客戶端處理資料等環節，如果返回不需要的資料，就會增加伺服器、網路和客戶端的無效勞動，其害處是顯而易見的，避免這類事件需要注意：

　　A、橫向來看，不要寫SELECT *的語句，而是選擇你需要的欄位。

　　B、 縱向來看，合理寫WHERE子句，不要寫沒有WHERE的SQL語句。

　　C、 注意SELECT INTO後的WHERE子句，因為SELECT INTO把資料插入到臨時表，這個過程會鎖定一些系統表，如果這個WHERE子句返回的資料過多或者速度太慢，會造成系統表長期鎖定，諸塞其他程式。

　　D、對於聚合查詢，可以用HAVING子句進一步限定返回的行。

　　2、 儘量少做重複的工作

　　這一點和上一點的目的是一樣的，就是儘量減少無效工作，但是這一點的側重點在客戶端程式，需要注意的如下：

　　A、 控制同一語句的多次執行，特別是一些基礎資料的多次執行是很多程式設計師很少注意的。

　　B、 減少多次的資料轉換，也許需要資料轉換是設計的問題，但是減少次數是程式設計師可以做到的。

　　C、 杜絕不必要的子查詢和連線表，子查詢在執行計劃一般解釋成外連線，多餘的連線錶帶來額外的開銷。

　　D、 合併對同一表同一條件的多次UPDATE，比如

　　UPDATE EMPLOYEE SET FNAME=’HAIWER’ WHERE EMP_ID=’ VPA30890F’

　　UPDATE EMPLOYEE SET LNAME=’YANG’ WHERE EMP_ID=’ VPA30890F’

　　這兩個語句應該合併成以下一個語句

　　UPDATE EMPLOYEE SET FNAME=’HAIWER’,LNAME=’YANG’

　　WHERE EMP_ID=’ VPA30890F’

　　E、 UPDATE操作不要拆成DELETE操作+INSERT操作的形式，雖然功能相同，但是效能差別是很大的。

　　F、 不要寫一些沒有意義的查詢，比如

　　SELECT * FROM EMPLOYEE WHERE 1=2

　　3、 注意事務和鎖

　　事務是資料庫應用中和重要的工具，它有原子性、一致性、隔離性、永續性這四個屬性，很多操作我們都需要利用事務來保證資料的正確性。在使用事務中我們需要做到儘量避免死鎖、儘量減少阻塞。具體以下方面需要特別注意：

　　A、事務操作過程要儘量小，能拆分的事務要拆分開來。

　　B、 事務操作過程不應該有互動，因為互動等待的時候，事務並未結束，可能鎖定了很多資源。

　　C、 事務操作過程要按同一順序訪問物件。

　　D、提高事務中每個語句的效率，利用索引和其他方法提高每個語句的效率可以有效地減少整個事務的執行時間。

　　E、 儘量不要指定鎖型別和索引，SQL SERVER允許我們自己指定語句使用的鎖型別和索引，但是一般情況下，SQL SERVER優化器選擇的鎖型別和索引是在當前資料量和查詢條件下是最優的，我們指定的可能只是在目前情況下更有，但是資料量和資料分佈在將來是會變化的。

　　F、 查詢時可以用較低的隔離級別，特別是報表查詢的時候，可以選擇最低的隔離級別(未提交讀)。

　　4、 注意臨時表和表變數的用法

　　在複雜系統中，臨時表和表變數很難避免，關於臨時表和表變數的用法，需要注意：

　　A、如果語句很複雜，連線太多，可以考慮用臨時表和表變數分步完成。

　　B、 如果需要多次用到一個大表的同一部分資料，考慮用臨時表和表變數暫存這部分資料。

　　C、 如果需要綜合多個表的資料，形成一個結果，可以考慮用臨時表和表變數分步彙總這多個表的資料。

　　D、其他情況下，應該控制臨時表和表變數的使用。

　　E、 關於臨時表和表變數的選擇，很多說法是表變數在記憶體，速度快，應該首選表變數，但是在實際使用中發現，這個選擇主要考慮需要放在臨時表的資料量，在資料量較多的情況下，臨時表的速度反而更快。

　　F、 關於臨時表產生使用SELECT INTO和CREATE TABLE + INSERT INTO的選擇，我們做過測試，一般情況下，SELECT INTO會比CREATE TABLE + INSERT INTO的方法快很多，但是SELECT INTO會鎖定TEMPDB的系統表SYSOBJECTS、SYSINDEXES、SYSCOLUMNS，在多使用者併發環境下，容易阻塞其他程式，所以我的建議是，在併發系統中，儘量使用CREATE TABLE + INSERT INTO，而大資料量的單個語句使用中，使用SELECT INTO。

　　G、 注意排序規則，用CREATE TABLE建立的臨時表，如果不指定欄位的排序規則，會選擇TEMPDB的預設排序規則，而不是當前資料庫的排序規則。如果當前資料庫的排序規則和TEMPDB的排序規則不同，連線的時候就會出現排序規則的衝突錯誤。一般可以在CREATE TABLE建立臨時表時指定欄位的排序規則為DATABASE_DEFAULT來避免上述問題。

　　5、 子查詢的用法

　　子查詢是一個 SELECT 查詢，它巢狀在 SELECT、INSERT、UPDATE、DELETE 語句或其它子查詢中。任何允許使用表示式的地方都可以使用子查詢。

　　子查詢可以使我們的程式設計靈活多樣，可以用來實現一些特殊的功能。但是在效能上，往往一個不合適的子查詢用法會形成一個效能瓶頸。

　　如果子查詢的條件中使用了其外層的表的欄位，這種子查詢就叫作相關子查詢。相關子查詢可以用IN、NOT IN、EXISTS、NOT EXISTS引入。

　　關於相關子查詢，應該注意：

　　A、NOT IN、NOT EXISTS的相關子查詢可以改用LEFT JOIN代替寫法。比如：

　　SELECT PUB_NAME

　　FROM PUBLISHERS

　　WHERE PUB_ID NOT IN

　　(SELECT PUB_ID

　　FROM TITLES

　　WHERE TYPE = 'BUSINESS')

　　可以改寫成：

　　SELECT A.PUB_NAME

　　FROM PUBLISHERS A LEFT JOIN TITLES B

　　ON B.TYPE = 'BUSINESS' AND

　　A.PUB_ID=B. PUB_ID

　　WHERE B.PUB_ID IS NULL

　　SELECT TITLE

　　FROM TITLES

　　WHERE NOT EXISTS

　　(SELECT TITLE_ID

　　FROM SALES

　　WHERE TITLE_ID = TITLES.TITLE_ID)

　　可以改寫成：

　　SELECT TITLE

　　FROM TITLES LEFT JOIN SALES

　　ON SALES.TITLE_ID = TITLES.TITLE_ID

　　WHERE SALES.TITLE_ID IS NULL

　　B、 如果保證子查詢沒有重複 ，IN、EXISTS的相關子查詢可以用INNER JOIN 代替。比如：

　　SELECT PUB_NAME

　　FROM PUBLISHERS

　　WHERE PUB_ID IN

　　(SELECT PUB_ID

　　FROM TITLES

　　WHERE TYPE = 'BUSINESS')

　　可以改寫成：

　　SELECT DISTINCT A.PUB_NAME

　　FROM PUBLISHERS A INNER JOIN TITLES B

　　ON B.TYPE = 'BUSINESS' AND

　　A.PUB_ID=B. PUB_ID

　　C、 IN的相關子查詢用EXISTS代替，比如

　　SELECT PUB_NAME

　　FROM PUBLISHERS

　　WHERE PUB_ID IN

　　(SELECT PUB_ID

　　FROM TITLES

　　WHERE TYPE = 'BUSINESS')

　　可以用下面語句代替：

　　SELECT PUB_NAME

　　FROM PUBLISHERS

　　WHERE EXISTS

　　(SELECT 1

　　FROM TITLES

　　WHERE TYPE = 'BUSINESS' AND

　　PUB_ID= PUBLISHERS.PUB_ID)

　　D、不要用COUNT(*)的子查詢判斷是否存在記錄，最好用LEFT JOIN或者EXISTS，比如有人寫這樣的語句：

　　SELECT JOB_DESC FROM JOBS

　　WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)=0

　　應該改成：

　　SELECT JOBS.JOB_DESC FROM JOBS LEFT JOIN EMPLOYEE

　　ON EMPLOYEE.JOB_ID=JOBS.JOB_ID

　　WHERE EMPLOYEE.EMP_ID IS NULL

　　SELECT JOB_DESC FROM JOBS

　　WHERE (SELECT COUNT(*) FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)<>0

　　應該改成：

　　SELECT JOB_DESC FROM JOBS

　　WHERE EXISTS (SELECT 1 FROM EMPLOYEE WHERE JOB_ID=JOBS.JOB_ID)

　　6、 慎用遊標

　　資料庫一般的操作是集合操作，也就是對由WHERE子句和選擇列確定的結果集作集合操作，遊標是提供的一個非集合操作的途徑。一般情況下，遊標實現的功能往往相當於客戶端的一個迴圈實現的功能，所以，大部分情況下，我們把遊標功能搬到客戶端。

　　遊標是把結果集放在伺服器記憶體，並通過迴圈一條一條處理記錄，對資料庫資源(特別是記憶體和鎖資源)的消耗是非常大的，所以，我們應該只有在沒有其他方法的情況下才使用遊標。

　　另外，我們可以用SQL SERVER的一些特性來代替遊標，達到提高速度的目的。

　　A、字串連線的例子

　　這是論壇經常有的例子，就是把一個表符合條件的記錄的某個字串欄位連線成一個變數。比如需要把JOB_ID=10的EMPLOYEE的FNAME連線在一起，用逗號連線，可能最容易想到的是用遊標：

　　DECLARE @NAME VARCHAR(20)

　　DECLARE @NAME VARCHAR(1000)

　　DECLARE NAME_CURSOR CURSOR FOR

　　SELECT FNAME FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID

　　OPEN NAME_CURSOR

　　FETCH NEXT FROM RNAME_CURSOR INTO @NAME

　　WHILE @@FETCH_STATUS = 0

　　BEGIN

　　SET @NAMES = ISNULL(@NAMES+’,’,’’)+@NAME

　　FETCH NEXT FROM NAME_CURSOR INTO @NAME

　　END

　　CLOSE NAME_CURSOR

　　DEALLOCATE NAME_CURSOR

　　可以如下修改，功能相同：

　　DECLARE @NAME VARCHAR(1000)

　　SELECT @NAMES = ISNULL(@NAMES+’,’,’’)+FNAME

　　FROM EMPLOYEE WHERE JOB_ID=10 ORDER BY EMP_ID

　　B、 用CASE WHEN 實現轉換的例子

　　很多使用遊標的原因是因為有些處理需要根據記錄的各種情況需要作不同的處理，實際上這種情況，我們可以用CASE WHEN語句進行必要的判斷處理，而且CASE WHEN是可以巢狀的。比如:

　　表結構:

　　CREATE TABLE 料件表(

　　料號 VARCHAR(30),

　　名稱 VARCHAR(100),

　　主單位 VARCHAR(20),

　　單位1 VARCHAR(20),

　　單位1引數 NUMERIC(18,4),

　　單位2 VARCHAR(20),

　　單位2引數 NUMERIC(18,4)

　　)

　　GO

　　CREATE TABLE 入庫表(

　　時間 DATETIME,

　　料號 VARCHAR(30),

　　單位 INT,

　　入庫數量 NUMERIC(18,4),

　　損壞數量 NUMERIC(18,4)

　　)

　　GO

　　其中，單位欄位可以是0，1，2，分別代表主單位、單位1、單位2，很多計算需要統一單位，統一單位可以用遊標實現：

　　DECLARE @料號 VARCHAR(30),

　　@單位 INT,

　　@引數 NUMERIC(18,4),

　　DECLARE CUR CURSOR FOR

　　SELECT 料號,單位 FROM 入庫表 WHERE 單位 <>0

　　OPEN CUR

　　FETCH NEXT FROM CUR INTO @料號,@單位

　　WHILE @@FETCH_STATUS<>-1

　　BEGIN

　　IF @單位=1

　　BEGIN

　　SET @引數=(SELECT 單位1引數 FROM 料件表 WHERE 料號 =@料號)

　　UPDATE 入庫表 SET 數量=數量*@引數,損壞數量=損壞數量*@引數,單位=1 WHERE CURRENT OF CUR

　　END

　　IF @單位=2

　　BEGIN

　　SET @引數=(SELECT 單位1引數 FROM 料件表 WHERE 料號 =@料號)

　　UPDATE 入庫表 SET 數量=數量*@引數,損壞數量=損壞數量*@引數,單位=1 WHERE CURRENT OF CUR

　　END

　　FETCH NEXT FROM CUR INTO @料號,@單位

　　END

　　CLOSE CUR

　　DEALLOCATE CUR

　　可以改寫成：

　　UPDATE A SET

　　數量=CASE A.單位 WHEN 1 THEN A.數量*B. 單位1引數

　　WHEN 2 THEN A.數量*B. 單位2引數

　　ELSE A.數量

　　END,

　　損壞數量= CASE A.單位 WHEN 1 THEN A. 損壞數量*B. 單位1引數

　　WHEN 2 THEN A. 損壞數量*B. 單位2引數

　　ELSE A. 損壞數量

　　END,

　　單位=1

　　FROM入庫表 A, 料件表 B

　　WHERE A.單位<>1 AND

　　A.料號=B.料號

　　C、 變數參與的UPDATE語句的例子

　　SQL ERVER的語句比較靈活，變數參與的UPDATE語句可以實現一些遊標一樣的功能，比如：

　　在

　　SELECT A,B,C,CAST(NULL AS INT) AS 序號

　　INTO #T

　　FROM 表

　　ORDER BY A ,NEWID()

　　產生臨時表後，已經按照A欄位排序，但是在A相同的情況下是亂序的，這時如果需要更改序號欄位為按照A欄位分組的記錄序號，就只有遊標和變數參與的UPDATE語句可以實現了，這個變數參與的UPDATE語句如下：

　　DECLARE @A INT

　　DECLARE @序號 INT

　　UPDATE #T SET

　　@序號=CASE WHEN A=@A THEN @序號+1 ELSE 1 END,

　　@A=A,

　　序號=@序號

　　D、如果必須使用遊標，注意選擇遊標的型別，如果只是迴圈取資料，那就應該用只進遊標(選項FAST_FORWARD)，一般只需要靜態遊標(選項STATIC)。

　　E、 注意動態遊標的不確定性，動態遊標查詢的記錄集資料如果被修改，會自動重新整理遊標，這樣使得動態遊標有了不確定性，因為在多使用者環境下，如果其他程式或者本身更改了紀錄，就可能重新整理遊標的記錄集。

　　7、 儘量使用索引

　　建立索引後，並不是每個查詢都會使用索引，在使用索引的情況下，索引的使用效率也會有很大的差別。只要我們在查詢語句中沒有強制指定索引，索引的選擇和使用方法是SQLSERVER的優化器自動作的選擇，而它選擇的根據是查詢語句的條件以及相關表的統計資訊，這就要求我們在寫SQL語句的時候儘量使得優化器可以使用索引。

　　為了使得優化器能高效使用索引，寫語句的時候應該注意：

　　A、不要對索引欄位進行運算，而要想辦法做變換，比如

　　SELECT ID FROM T WHERE NUM/2=100

　　應改為:

　　SELECT ID FROM T WHERE NUM=100*2

　　SELECT ID FROM T WHERE NUM/2=NUM1

　　如果NUM有索引應改為:

　　SELECT ID FROM T WHERE NUM=NUM1*2

　　如果NUM1有索引則不應該改。

　　發現過這樣的語句：

　　SELECT 年,月,金額 FROM 結餘表

　　WHERE 100*年+月=2007*100+10

　　應該改為：

　　SELECT 年,月,金額 FROM 結餘表

　　WHERE 年=2007 AND

　　月=10

　　B、 不要對索引欄位進行格式轉換

　　日期欄位的例子：

　　WHERE CONVERT(VARCHAR(10), 日期欄位,120)=’2008-08-15’

　　應該改為

　　WHERE日期欄位〉=’2008-08-15’ AND 日期欄位

　　ISNULL轉換的例子：

　　WHERE ISNULL(欄位,’’)<>’’應改為:WHERE欄位<>’’

　　WHERE ISNULL(欄位,’’)=’’不應修改

　　WHERE ISNULL(欄位,’F’) =’T’應改為: WHERE欄位=’T’

　　WHERE ISNULL(欄位,’F’)<>’T’不應修改

　　C、 不要對索引欄位使用函式

　　WHERE LEFT(NAME, 3)='ABC' 或者WHERE SUBSTRING(NAME,1, 3)='ABC'

　　應改為:

　　WHERE NAME LIKE 'ABC%'

　　日期查詢的例子：

　　WHERE DATEDIFF(DAY, 日期,'2005-11-30')=0應改為:WHERE 日期 >='2005-11-30' AND 日期

　　WHERE DATEDIFF(DAY, 日期,'2005-11-30')>0應改為:WHERE 日期

　　WHERE DATEDIFF(DAY, 日期,'2005-11-30')>=0應改為:WHERE 日期

　　WHERE DATEDIFF(DAY, 日期,'2005-11-30')<0應改為:WHERE 日期>='2005-12-01‘

　　WHERE DATEDIFF(DAY, 日期,'2005-11-30')<=0應改為:WHERE 日期>='2005-11-30‘

　　D、不要對索引欄位進行多欄位連線

　　比如：

　　WHERE FAME+ ’.’+LNAME=‘HAIWEI.YANG’

　　應改為:

　　WHERE FNAME=‘HAIWEI’ AND LNAME=‘YANG’

　　8、 注意連線條件的寫法

　　多表連線的連線條件對索引的選擇有著重要的意義，所以我們在寫連線條件條件的時候需要特別的注意。

　　A、多表連線的時候，連線條件必須寫全，寧可重複，不要缺漏。

　　B、 連線條件儘量使用聚集索引

　　C、 注意ON部分條件和WHERE部分條件的區別

　　9、 其他需要注意的地方

　　經驗表明，問題發現的越早解決的成本越低，很多效能問題可以在編碼階段就發現，為了提早發現效能問題，需要注意：

　　A、程式設計師注意、關心各表的資料量。

　　B、 編碼過程和單元測試過程儘量用資料量較大的資料庫測試，最好能用實際資料測試。

　　C、 每個SQL語句儘量簡單

　　D、不要頻繁更新有觸發器的表的資料

　　E、 注意資料庫函式的限制以及其效能

　　10、 學會分辯SQL語句的優劣

　　自己分辨SQL語句的優劣非常重要，只有自己能分辨優劣才能寫出高效的語句。

　　A、 檢視SQL語句的執行計劃，可以在查詢分析其使用CTRL+L圖形化的顯示執行計劃，一般應該注意百分比最大的幾個圖形的屬性，把滑鼠移動到其上面會顯示這個圖形的屬性，需要注意預計成本的資料，也要注意其標題，一般都是CLUSTERED INDEX SEEK 、INDEX SEEK 、CLUSTERED INDEX SCAN 、INDEX SCAN 、TABLE SCAN等，其中出現SCAN說明語句有優化的餘地。也可以用語句

　　SET SHOWPLAN_ALL ON

　　要執行的語句

　　SET SHOWPLAN_ALL OFF

　　檢視執行計劃的文字詳細資訊。

　　B、 用事件探查器跟蹤系統的執行，可疑跟蹤到執行的語句，以及所用的時間，CPU用量以及I/O資料，從而分析語句的效率。

　　C、 可以用WINDOWS的系統效能檢測器，關注CPU、I/O引數

　　四、 測試、試執行、維護階段

　　測試的主要任務是發現並修改系統的問題，其中效能問題也是一個重要的方面。重點應該放在發現有效能問題的地方，並進行必要的優化。主要進行語句優化、索引優化等。

　　試執行和維護階段是在實際的環境下執行系統，發現的問題範圍更廣，可能涉及作業系統、網路以及多使用者併發環境出現的問題，其優化也擴充套件到作業系統、網路以及資料庫物理儲存的優化。

　　這個階段的優花方法在這裡不再展開，只說明下索引維護的方法：

　　A、 可以用DBCC DBREINDEX語句或者SQL SERVER維護計劃設定定時進行索引重建，索引重建的目的是提高索引的效能。

　　B、 可以用語句UPDATE STATISTICS或者SQL SERVER維護計劃設定定時進行索引統計資訊的更新，其目的是使得統計資訊更能反映實際情況，從而使得優化器選擇更合適的索引。

　　C、 可以用DBCC CHECKDB或者DBCC CHECKTABLE語句檢查資料庫表和索引是否有問題，這兩個語句也能修復一般的問題。

　　D、

　　五、 網上資料中一些說法的個人不同意見

　　1、 “應儘量避免在 WHERE 子句中對欄位進行 NULL 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：

　　SELECT ID FROM T WHERE NUM IS NULL

　　可以在NUM上設定預設值0，確保表中NUM列沒有NULL值，然後這樣查詢：

　　SELECT ID FROM T WHERE NUM=0”

　　個人意見：經過測試，IS NULL也是可以用INDEX SEEK查詢的，0和NULL是不同概念的，以上說法的兩個查詢的意義和記錄數是不同的。

　　2、 “應儘量避免在 WHERE 子句中使用!=或<>操作符，否則將引擎放棄使用索引而進行全表掃描。”

　　個人意見：經過測試，<>也是可以用INDEX SEEK查詢的。

　　3、 “應儘量避免在 WHERE 子句中使用 OR 來連線條件，否則將導致引擎放棄使用索引而進行全表掃描，如：

　　SELECT ID FROM T WHERE NUM=10 OR NUM=20

　　可以這樣查詢：

　　SELECT ID FROM T WHERE NUM=10

　　UNION ALL

　　SELECT ID FROM T WHERE NUM=20”

　　個人意見：主要對全表掃描的說法不贊同。

　　4、 “IN 和 NOT IN 也要慎用，否則會導致全表掃描，如：

　　SELECT ID FROM T WHERE NUM IN(1,2,3)

　　對於連續的數值，能用 BETWEEN 就不要用 IN 了：

　　SELECT ID FROM T WHERE NUM BETWEEN 1 AND 3”

　　個人意見：主要對全表掃描的說法不贊同。

　　5、 “如果在 WHERE 子句中使用引數，也會導致全表掃描。因為SQL只有在執行時才會解析區域性變數，但優化程式不能將訪問計劃的選擇推遲到執行時;它必須在編譯時進行選擇。然而，如果在編譯時建立訪問計劃，變數的值還是未知的，因而無法作為索引選擇的輸入項。如下面語句將進行全表掃描：

　　SELECT ID FROM T WHERE NUM=@NUM

　　可以改為強制查詢使用索引：

　　SELECT ID FROM T WITH(INDEX(索引名)) WHERE NUM=@NUM”

　　個人意見：關於區域性變數的解釋比較奇怪，使用引數如果會影響效能，那儲存過程就該校除了，我堅持我上面對於強制索引的看法。

　　6、 “儘可能的使用 VARCHAR/NVARCHAR 代替 CHAR/NCHAR ，因為首先變長欄位儲存空間小，可以節省儲存空間，其次對於查詢來說，在一個相對較小的欄位內搜尋效率顯然要高些。”

　　個人意見：“在一個相對較小的欄位內搜尋效率顯然要高些”顯然是對的，但是欄位的長短似乎不是由變不變長決定，而是業務本身決定。在SQLSERVER6.5或者之前版本，不定長字串欄位的比較速度比定長的字串欄位的比較速度慢很多，所以對於那些版本，我們都是推薦使用定長欄位儲存一些關鍵欄位。而在2000版本，修改了不定長字串欄位的比較方法，與定長欄位的比較速度差別不大了，這樣為了方便，我們大量使用不定長欄位。

　　7、 關於連線表的順序或者條件的順序的說法，經過測試，在SQL SERVER，這些順序都是不影響效能的，這些說法可能是對ORACLE有效。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-664618/，如需轉載，請註明出處，否則將追究法律責任。