MSSQL性能詳解

近期因工作需要，希望比較全面的總結下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代替。 //更多 http://www.52mvc.com/tags.aspx


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


E、 自增字段要慎用，不利于數據遷移。


6、 數據庫物理存儲和環境的設計


在設計階段，可以對數據庫的物理存儲、操作系統環境、網絡環境進行必要的設計，使得我們的系統在將來能適應比較多的用戶并發和比較大的數據量。


這里需要注意文件組的作用，適用文件組可以有效把IO操作分散到不同的物理硬盤，提高并發能力。


7、 系統設計


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


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


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 NUM2=100


應改為


SELECT ID FROM T WHERE NUM=1002


SELECT ID FROM T WHERE NUM2=NUM1


如果NUM有索引應改為


SELECT ID FROM T WHERE NUM=NUM12


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


發現過這樣的語句：


SELECT 年,月,金額 FROM 結余表 
WHERE 100年+月=2007100+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用量以及IO數據，從而分析語句的效率。


C、               可以用WINDOWS的系統性能檢測器，關注CPU、IO參數


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


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


試運行和維護階段是在實際的環境下運行系統，發現的問題范圍更廣，可能涉及操作系統、網絡以及多用戶并發環境出現的問題，其優化也擴展到操作系統、網絡以及數據庫物理存儲的優化。


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


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、 “盡可能的使用 VARCHARNVARCHAR 代替 CHARNCHAR ，因為首先變長字段存儲空間小，可以節省存儲空間，其次對于查詢來說，在一個相對較小的字段內搜索效率顯然要高些。”


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


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