提高SQL Server資料庫效率常用方法

< DOCTYPE html PUBLIC -WCDTD XHTML StrictEN httpwwwworgTRxhtmlDTDxhtml-strictdtd>

在現在網際網路應用程式開發過程中，常常會發現查詢或者操作資料速度慢。其原因很多，常見如下幾種：

1、沒有索引或者沒有用到索引(這是資料庫設計的缺陷)

2、I/O吞吐量小，形成了瓶頸效應。

3、記憶體不足

4、網絡速度慢

5、查詢出的資料量過大（可以采用多次查詢）

6、鎖或者死鎖(這也是程式設計的缺陷)

7、傳回了不必要的行和列

8、查詢語句不好，沒有優化

      資料庫要高效運作，首先要保證資料庫設計的正确性，非特殊需要不要違反三大範式原則；然後再來考慮資料庫性能及效率的優化工作。日常工作中，我們經常用到的一些優化方法如下：

1、把資料、日志、索引放到不同的I/O裝置上，增加讀取速度。資料量（尺寸）越大，提高I/O越重要。

2、更新硬體。

3、根據查詢條件，建立索引，優化索引、優化通路方式，限制結果集的資料量。索引應該盡量小，使用位元組數小的列建索引好，不要對有

     限的幾個值的字段建單一索引。

4、查詢耗時和字段值總長度成正比，是以資料庫設計的時候可變長字段不能用CHAR類型，而是VARCHAR。有相當一部份開發人員喜歡可變長字元串也用CHAR，然後補空格。

5、重建索引DBCC REINDEX，DBCC INDEXDEFRAG，收縮資料和日志 DBCC SHRINKDB，DBCC SHRINKFILE。設定自動收縮日志。對于大的資料庫不要設定資料庫自動增長，它會降低伺服器的性能。

6、在查詢Select語句中用Where字句限制傳回的行數，避免表掃描。如果傳回不必要的資料，浪費了伺服器的I/O資源，加重了網絡的負擔降低性能。如果表很大，在表掃描的期間将表鎖住，禁止其他的聯接通路表，後果嚴重。

7、盡可能不使用光标，它占用大量的資源。如果需要row-by-row地執行，盡量采用非光标技術，如：在用戶端循環，用臨時表，Table變量，用子查詢，用Case語句等等。

8、用Profiler來跟蹤查詢，得到查詢所需的時間，找出SQL的問題所在；用索引優化器優化索引。

9、注意UNion和UNion all 的差別。盡量使用UNION all。

10、注意使用DISTINCT，在沒有必要時不要用，它同UNION一樣會使查詢變慢。

11、查詢時不要傳回不需要的行、列

12、用select top 100 / 10 Percent 來限制使用者傳回的行數或者SET ROWCOUNT來限制操作的行。

13、使用查詢分析器，檢視SQL語句的查詢計劃和評估分析是否是優化的SQL。一般的20%的代碼占據了80%的資源，我們優化的重點是這些慢的地方。

14、如果使用了IN或者OR等時發現查詢沒有走索引，使用顯示申明指定索引： Select * FROM tablename (INDEX = IX_Title) Where sex IN ('男'，'女')

15、資料庫有一個原則是代碼離資料越近越好，是以優先選擇Default，依次為規則、觸發器、限制Constraint（限制如外健主健CheckUNIQUE……，資料類型的最大長度等等都是限制）、存儲過程。這樣不僅維護工作小，編寫程式品質高，并且執行的速度快。

16、如果要插入大的二進制值到Image列，使用存儲過程，千萬不要用内嵌Insert來插入。因為應用程式首先将二進制值轉換成字元串（尺寸是它的兩倍），伺服器受到字元後又将他轉換成二進制值。存儲過

     程就沒有這些動作。方法：Create procedure p_insert as insert into table(Fimage) values (@image)， 在前台調用這個存儲過程傳入二進制參數，這樣處理速度明顯改善。

17、Between在某些時候比IN 速度更快，Between能夠更快地根據索引找到範圍。用查詢優化器可見到差别。 select * from chineseresume where title in ('男'，'女')和Select * from chineseresume where between '男' and '女' 是一樣的功能。由于in會在比較多次，是以有時會慢些。

18、不要在程式中使用沒有作用的事務處理。

19、用OR的字句可以分解成多個查詢，并且通過UNION 連接配接多個查詢。他們的速度隻同是否使用索引有關，如果查詢需要用到聯合索引，用UNION all執行的效率更高。多個OR的字句沒有用到索引，改寫成UNION的形式再試圖與索引比對。一個關鍵的問題是否用到索引。

20、盡量少用視圖，它的效率低。對視圖操作比直接對表操作慢，可以用stored procedure來代替它。特别的是不要用視圖嵌套，嵌套視圖增加了尋找原始資料的難度。

21、沒有必要時不要用DISTINCT和ORDER BY，這些動作可以改在客戶

端執行。它們增加了額外的開銷。這同UNION 和UNION ALL一樣的道理。

22、在IN後面值的清單中，将出現最頻繁的值放在最前面，出現得最少的放在最後面，減少判斷的次數。

23、一次更新多條記錄比分多次更新每次一條快，就是說批處理好。

24、盡量将資料的處理工作放在伺服器上，減少網絡的開銷，如使用存儲過程。存儲過程是編譯好、優化過、并且被組織到一個執行規劃裡、且存儲在資料庫中的SQL語句，是控制流語言的集合，速度當然快。

25、通過SQL Server Performance Monitor監視相應硬體的負載 Memory: Page Faults / sec計數器如果該值偶爾走高，表明當時有線程競争記憶體。如果持續很高，則記憶體可能是瓶頸。

本文轉自 netcorner 部落格園部落格，原文連結： http://www.cnblogs.com/netcorner/archive/2007/10/20/2912278.html ，如需轉載請自行聯系原作者