【SQL Server 最佳化效能的幾個方面】

              SQL Server 最佳化效能的幾個方面

 

(一).資料庫的設計

   可以參看最近論壇上出現一個精華帖http://topic.csdn.net/u/20100415/10/a377d835-acbd-4815-8bcb-b367f88ac8b5.html?92227
   資料庫設計包含實體設計和邏輯設計:
   實體設計上可以通過使用RAID硬體架構。
   簡單說下使用原則：
a.RAID0可以用在唯讀資料庫資料表，或者是經過複製過來的資料庫上，如果你對資料丟失不敏感的話也可以使用，總之這個level下是高效能無冗餘；
b.RAID 5 與RAID1 不同之處就是多了同位，所有的同位的資訊會遍布各個磁碟，效能上要比RAID1高些，但是一旦發生磁碟I/O失敗，就會造成效能急劇下降，同時這種方法也在RAID0 與RAID1間折了中，是比較通用的做法。
c.RAID 10 就是RAID0 與 RAID1的組合，它提供了高效能，高可用性，效能上要比RAID5好，特別適合大量寫入的應用程式，但是就是成本比較高無論是多少塊磁碟你都是將損失一半的磁碟儲存

  邏輯設計上可以通過使用資料庫的一些組件比如表、約束等，在這裡首先提下檔案組的使用。當你經費不足,無法購買一個完整的RAID系統的時候,你可以考慮檔案組。
  檔案和檔案組體繫結構一些小節：http://blog.csdn.net/feixianxxx/archive/2010/01/28/5267290.aspx
  它的原理就是多硬碟同時讀取，減少磁碟空間爭奪，提高讀取效率。

 

我在這再稍微說幾點：

a.範式和資料表的分割和合并
  我們在設計資料庫的時候一般會追求規範，一般會至少達到3NF.在這之後，也許會因為表中某些經常用於儲存查詢的欄位而把它們從表中分割，否則大量使用者同時大量訪問一個熱門表,效率會變差。

  當然有時候也會進行合并表的設計，這樣可以用少量的冗餘換來減少過多串連的運算。
  具體關於這個設計表到什麼程度，是否需要分割和合并都是因業務需求和系統承載力而定的,可以參看更多這方面的書籍。

 

b.主鍵與外鍵
  作為表與表之間的聯絡，最直接的就是通過主外鍵進行聯絡。主鍵對於一個表來說是非常重要的，它能在表中作為行的唯一標識存在，要求：唯一性,不可為NULL和最小性及容易擷取。

  其中最小性指的是作為主鍵一定要窄。如果索引值過大，由於經常存取，它會讓資料庫系統變得沒有效率，而且它需要維護。一般使用1-4位元組的欄位作為主鍵。它在作為叢集索引時也要保持欄位的長度適宜，否則索引過長。

  定義了外鍵的約束不需要索引，但是由於串連運算的需要，我們通常需要在建立完外鍵後手動加上必要的索引。

 

c.表欄位的取捨
  盡量讓你的表欄位類型“適可而止”，即在符合一般需求長度的前提下，最多稍微增加長度，不要過多浪費。欄位儲存直接體現在頁中。行長越短,一頁就可以儲存越多的記錄行。
  當我們從硬碟中取資料的時候以頁為單位，一頁中包含的記錄越多，代表放入緩衝區的記錄就越多，輸送量就越大。
  1.如果欄位的內容比較固定 比如性別 社會安全號碼碼 手機號碼等 就是用定長的 char 或者nchar
  2.如果欄位的內容變化幅度比較大 則介意使用 varchar或者nvarchar
  3.一般不要讓欄位可以為NULL，這樣會帶來一些處理上不必要的消耗，可以使用預設值代替它
  4.儘可能的使用約束來維護資料完整性，不要過分依賴觸發器或者預存程序來維護,切忌有些簡單的記錄規則不要用前端程式碼判斷。

  5.大型欄位如nvarchar(max)，char(max)，binary(max)，text等欄位應該切割到獨立表，並用主鍵跟本來的源表相互關聯，因為這些資料類型會造成訪問的緩慢，修改等操作的鎖定，而我們在查詢的時候又用不到這些欄位一般

  6.表欄位不要太多，一般一行的長度不要過長，這樣會引起每頁儲存的記錄太少，有必要的時候可以切割有些欄位，將一個表分成2表 甚至更多表格儲存體。
 
d.日常維護計劃安排
  資料維護計劃,比如備份,重建索引,複製資料等批處理消耗資源的操作盡量在系統閒置時候進行。

 

e.前端程式對基礎資料表的存取
  盡量讓程式通過據庫中的預存程序，視圖，函數來存取資料，不要讓其直接在基礎資料表上進行操作。

 

f.將線上分析和線上事務的訪問分開

2者的區別很大，具體可以參考MSDN。簡單來說前者適合查詢，後者多用於插入更新刪除以及部分查詢。

 

g.做好歸檔的計劃

將什麼歸檔 歸檔周期是什麼 歸檔後如何抽取查詢 歸檔的安全性等問題都需要考慮。

 

(二).資料庫查詢的最佳化（T-SQL最佳化）
  這個內容太大了，就挑著點來說
 
1.盡量多的使用查詢參數（SARG）
  它的一般格式：欄位 部分的運算子 《常數或者變數》
  這裡的部分運算子包括：= > < >= <= BETWEEN AND 還有LIKE 後面不是以%開頭的。

  非SARG的文法，索引一般不起作用。下面列舉一些違反SARG文法的做法：

  a.對資料欄位進行了運算
    我直接舉例子(測試過的)： where col_1+col_2='ab' 是不可以用到索引的 ;
                   where col1='a' adn col_2='b'則可以用到欄位上的索引;
                   where col_3+1=4 不可以用到；where col_3=4-1 可以用到

  b.不要對欄位使用逆運算子
    不要對欄位使用某些逆運算子,比如   not in not like  (2000的書上說<> != !>等也是會屏蔽索引的 我在08上是可以使用索引的 歡迎有2000環境的朋友測試)
     例子（測試過的）:where id not like '1%' 或者where id not in(1,2,3) 都是不能用到索引的
 
 c.不要對欄位使用函數
   使用比如 substring left datediff 等等函數 但是你可以巧妙的用SARG方式來替代某些函數的功能
   比如 left(col,2)='av' 可以用 col like 'av%' 或者 abs(col-1)>100 可以用 col>101 or col<-99

 

 d.不要使用OR運算子
   使用了OR運算子，多個條件中的欄位只要有一個沒有合適的索引，其他欄位的索引都失去索引效果，都將整表掃描。（注意索引掃描和表掃描效率差不多）
 
 但是注意在where中使用非SARG也不一定就用不到索引。有時候包含非SARG的條件 但是還是會對SARG部分進行索引利用的。

 

2.表之間的聯結

參考：http://blog.csdn.net/happyflystone/archive/2009/01/15/3786158.aspx

 

 

3.大量資料插入
 
 a.在進行大容量資料插入的時候，可以通過BCP BULK INSERT選項盡量屏蔽表上的觸發器、約束,甚至刪除表上索引，這樣可以讓插入操作快很多。
   如果你擔心資料的不規則，可以先將資料匯入到一個暫存資料表，然後再資料庫裡面過濾掉不規則資料後，再次由暫存資料表插入目標表。
 b.同樣的大容量插入，推薦使用BULK INSERT。因為通常情況下，它比BCP要快。
 c.使用BCP 或者 BULK INSERT插入時候，採用表鎖定而不是預設的記錄鎖。
 d.如伺服器多核，可以盡量讓多用戶端並行對錶插入。
 e.如果插入的資料需要轉換，不要使用DTS等工具直接對記錄的轉化,可以使用先插入暫存資料表，在暫存資料表內轉化後進行第二次匯入。

 

4.一些其他注意事項

1.進行少進行排序操作。DISTINCT、Order By、Union 等關鍵詞 只能在需要的時候才用；

2.大批處理資料時，沒有LOG操作一定比逐條且經由2此寫入的動作快。SELECT INTO 比 INSERT SELECT 快，TRUNCATE TABLE 比DELETE TABLE（不含WHERE ）快；

3.在05及以上的版本引用資料庫物件時，最好使用兩節名稱格式。因為SQL SERVER對單節名稱對象首先在目前使用者的預設架構下尋找對象名字，如果找不到，再去DBO下尋找。

4.注意聯結操作和子查詢操作的選擇。有時候2者的結果一樣，雖然語句結構不一樣，但是產生的執行計畫是一樣的。當然，更多時候會出現不一致的執行計畫，儘管結果是相同的，所以要注意不同語句的調試；

5.查詢提示：注意，一般情況下，不需要使用HINT進行查詢，因為會導致最佳化器不對查詢進行最佳化；一般的HINT只會用於測試瞭解一些查詢方式，索引，鎖用法對查詢的影響；
 

（三）索引的合理設計
     這又是很大的一塊內容，也不是三言兩語能說完的，介於內容的量和本人水平以及實踐太少，就稍微寫點：

       設計索引的準備工作：
  1.瞭解資料庫的性質。看它是OLTP還是OLAP。如果是前者，由於頻繁修改資料，索引建立索引要謹慎；對於後者，可以建立很多索引，以加快搜尋速度；
  2.瞭解哪些查詢是常用的查詢；
  3.瞭解索引的選項，從這個點提高索引效能；
  4.確定索引儲存位置：如將非叢集索引和資料存放於不同磁碟有利於提高效能
      建立索引的一些準則：
  1.對於經常變動的表，即經常進行插入、更新和刪除操作的表，不能建立太多的索引；反之，則可以建立比較多的索引，這樣有利於查詢最佳化工具的選擇；
  2.索引列務必盡量保持較小的寬度；
  3.對於小型表，即含資料量比較少的表，可以不建立索引，因為索引的使用有時候還不如進行表掃描來的快；
  4.當視圖包含彙總運算，大型表串連的時候可以使用索引檢視表來顯著提高效能，注意這裡的表不能經常變更；
  4.選擇重複性少的列做索引列，重複性大的列 比如性別列這樣的列不如不建索引；
  5.使用合理的填滿因數；
  6.為經常用於查詢中的謂詞和串連條件的和分組的排序的列加上索引；
  7.複合式索引(覆蓋索引)，即由多個列組成的索引，當查詢所需要的列都包含其中的時候可以顯著提高效能，因為它只需要索引頁的內容就夠了。我們要注意在這些索引列中
    要將經常使用的列放在盡量靠前，列的排序為從最不重複道最重複的列；這裡還有一個索引叫包含性列索引，它是一個特殊的非叢集索引，它可以包含一些非鍵
    列，將這些列加到索引頁的分葉層級，這同樣可以在查詢所需要的列都包含其中的時候可以顯著提高效能，這2種列 合成為 多列索引；

     我提供幾篇關於索引的文章：
     http://www.cnblogs.com/bhtfg538/(裡面有幾篇)
     http://hi.baidu.com/dusongw/blog/item/4090493d6ec0cdee3d6d97a6.html

 

（四）前端應用程式設計
      應用程式作為資料的控制使用端，它將決定何時使用資料，如何使用資料，得來的資料結果又如何處理。它將直接影響伺服器端的活動，對整個效能非常關鍵。
      下面說幾點應用程式設計的注意點：
1.減少網路流量
   你可以通過使用預存程序，甚至可以使用 SET NOCOUNT 設定來禁用一些影響行數。還有就是要儘可能少的返回資料量，什麼意思呢？就是說,如果你的程式只需要表
   中的某些資料，你就不要返回整個表的資料，然後再篩選，直接通過條件在伺服器篩選好後再傳過來。

2.限定鎖定逾時
   不要讓你的查詢無限運行，應該用適當的API設定查詢逾時。

3.直到必要的時候才使用遊標

4.事務要儘可能的短

5.確保將應用程式設計為可避免死結。

 

（五）其他的一些措施：
 1.最佳化伺服器效能
      伺服器配置選項一般都會有預設的自動調整，你可以通過實際情況，在確定有利於你的系統最佳化的前提下修改某些選項。
      比如可以修改以下幾個方面：
      SQL Server 記憶體;I/0子系統；Windows Server選項。

2.強化硬體裝置
      加記憶體、加硬碟容量、升級RAID等等

3.設計聯盟資料庫伺服器
      對於大型的系統，往往需要多個伺服器平衡各層的處理負荷。

    

 

 

                                           推薦：海爺寫的SQL SERVER效能最佳化綜述http://blog.csdn.net/Haiwer/archive/2008/08/25/2826881.aspx
                                           本文參考：效能調優（胡百敬）