mssql性能优化［转］（教你写出高质量的SQL语句）（二）
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有效。
轉載于:https://www.cnblogs.com/zxj159/articles/2438770.html
總結
以上是生活随笔為你收集整理的mssql性能优化［转］（教你写出高质量的SQL语句）（二）的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 按姓氏笔画排序
- 下一篇： Fluent NHibernate之旅