SQL SERVER效能最佳化綜述(很好的總結,不要錯過哦)第1/3頁

一、分析階段
一般來說，在系統分析階段往往有太多需要關注的地方，系統各種功能性、可用性、可靠性、安全性需求往往吸引了我們大部分的注意力，但是，我們必須注意，效能是很重要的非功能性需求，必鬚根據系統的特點確定其即時性需求、回應時間的需求、硬體的配置等。最好能有各種需求的量化的指標。
另一方面，在分析階段應該根據各種需求區分出系統的類型，大的方面，區分是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、
關於暫存資料表和表變數的選擇，很多說法是表變數在記憶體，速度快，應該首選表變數，但是在實際使用中發現，這個選擇主要考慮需要放在暫存資料表的資料量，在資料量較多的情況下，暫存資料表的速度反而更快。