CSDN老海的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   日期欄位<’2008-08-16’

 

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 日期 <'2005-12-1‘

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

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

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有效。