SQL Server 2008性能優化研究

陳蘇蓉,朱曉輝

摘要:后臺數據庫系統的性能優劣決定了軟件系統的整體運行效率。為了提高系統的運行效率,需要對數據庫系統進行必要的調整和優化。分析了和比較了不同方案對數據庫性能的影響。提出了數據庫優化的一般性的準則,并結合實際項目的測試數據證明之。

關鍵詞:數據庫設計;性能優化;T-SQL優化

中圖分類號:TP311文獻標識碼:A 文章編號:1009-3044(2009)34-9837-02

Research of Optimizing Performance of SQL Server 2008

CHEN Su-rong, ZHU Xiao-hui

(College of Computer Science and Technology, Nantong University, Nantong 226019, China)

Abstract: The efficiency of the software depend on the performance of database. In order to improve the system performance, we should adjust and optimize the database. The article analyze the impact of different features for the database performance optimizing and propose general rules of performance optimizing for database and make several test on it.

Key words: database design; performance optimizing; T-SQL optimizing

對管理信息系統而言,對后臺數據庫數據的存取速度往往決定了整個系統的運行效率[1],尤其是對一個要處理上百萬、上千萬數據的大型數據庫系統而言更是如此。本文從數據庫設計、T-SQL編寫、應用程序優化,及硬件配置等方面,分析了SQL Server 2008數據庫性能優化問題,并給出優化的一般性準則。

1 數據庫設計

數據庫設計包括邏輯設計和物理設計兩個部分[2]。數據庫邏輯設計又包含業務需求和數據建模。優秀的數據庫邏輯設計方案可以有效去除冗余數據、提高系統數據吞吐率、保證數據完整性,并能清楚地表達數據元素間的關系。數據庫物理設計包括將邏輯設計映射到物理媒體,利用可用的硬件和軟件功能來提高系統對數據庫的訪問速度。

1.1 邏輯設計

數據庫設計過程包括:現實世界→需求分析→概念設計→邏輯設計→物理設計[5]。

概念設計是利用數據模型進行概念數據庫的模式設計,一般用E-R圖來進行描述。邏輯設計是把概念設計得到的概念數據庫模式變為邏輯數據模式,常用到函數依賴、范式、關系分解等多種技術。好的邏輯設計可以為優化數據庫性能打下良好的基礎。在SQL Server 2008中進行邏輯設計時一般應遵循如下幾個原則:

1) 所有邏輯表結構應盡量滿足第一、第二、第三范式[5],從而有效減少數據冗余,提高查詢性能并減少因數據冗余導致的數據不一致性錯誤。

2) 特殊情況下可以突破前三范式的規范化要求,采用空間換取時間的辦法,利用適當的冗余數據來提高數據庫的查詢速度[6]。例如筆者在開發一個銷售系統時,銷售表中已有“銷售單價”、“返利單價”、“銷售數量”三個字段,通過這三個字段可以自動計算出“銷售總額”及“返利總額”兩個信息,但在進行邏輯設計時特意增加了“銷售總額”、“返利總額”兩個冗余字段,以方便進行高效率的匯總統計以及提升按銷售總額或返利總額的排序效率。同時采用SQL2008中的觸發器技術來根據前面三個字段的數據自動生成這“銷售總額”和“返利總額”兩個數據,保證了數據的一致性和完整性。

3) 充分利用SQL2008數據庫的現有的規則功能來自動維護數據庫的一致性和完整性。避免未滿足完整性規則的“臟數據”進入數據庫。常用的技術有:

a) 多使用Check約束,確保數據的有效性;b) 給字段設置Default和Not Null 約束,保證字段值的確定性;c) 建立Foreign Key約束,確保外鍵參照完整性[7];d) 建Primary Key約束,確保紀錄唯一性。

4) 建立合理的索引,索引是數據庫中重要的數據結構,對需要排序和查找的列上建立適當的索引,可以大大減少數據庫對表進行物理讀取的次數,提高運行效率。從另一方面來說,盡管索引可以快速獲取數據,但它們也同時減慢數據的update和delete操作并需要更多的額外空間來存放索引表,因此,必須設計出合適的索引。一般來說建立索引應注意以下幾點:

a) 對于查詢中很少涉及的列或重復值比較多的列,不要建立索引;b) 數據量較小的表一般無需建立索引;c) 在經常進行連接,但沒有指定為外鍵的列上建立索引;d) 經常出現在where子句、order by子句或group by子句后面的字段上建立索引。

對同時出現在where子句、order by子句或group by子句的多個字段上創建復合索引,并且復合索引的次序相同于子句中這些字段的排列次序。

1.2 物理設計

1) 把數據庫事務日志文件和主文件存放到不同的物理磁盤,提高數據存取時I/O并發性[13]。

2) 當某些表中數據量特別大,而且訪問非常頻繁時,可以考慮采用將這些表放置在不同的物理磁盤上以提高讀寫時的磁盤的并發性。

3) 對數據庫進行索引優化,利用索引可以避免表掃描,并減少因查詢而造成的I/O開銷,從而提高系統性能.可以用SQL Server2008中查詢分析器來對索引進行優化和調整。

2 T-SQL編寫

經過優化的T-SQL語句可以極大提高數據的存取效率,以下是常用的優化技巧。

1) 對于SELECT語句必須顯式定義所有需要返回的列,避免使用星號。通過明確指定需要返回的數據列可以大大減少數據庫返回的數據量,減少對磁盤的I/O操作,提高性能。

2) 在WHERE子句中避免使用導致表掃描的語句,比如: OR, <>, != , ! ,<, >, IS NULL, NOT,NOT IN, NOT LIKE 和LIKE等,因為這些操作很難利用已有索引[11]。

3) 避免使用NOT IN,可以采用IN,EXISTS,NOT EXISTS和LEFT JOIN 加空值判斷。

4) 如果WHERE條件語句有多個AND條件,請確保至少有一個列有索引,如果沒有,可以建立多列復合索引。

5) 盡可能避免在WHERE條件語句中使用函數計算。

對一些復雜的多個SQL語句,優先考慮使用存儲過程,并避免在事務中進行賦值和復雜計算。存儲過程是被預先編譯好并被放在數據庫內的,因此可以有效減少編譯語句所花的時間。 同時,編譯好的存儲過程會進入緩存,所以對于經常執行的存儲過程,除了第一次執行外,后續執行的速度會有明顯提高。存儲過程也可以更好的利用服務器內存,尤其對處理中間數據量不大的情況,存儲過程中可以利用存放在內存的表變量來保存臨時數據集。

3 應用程序優化

1) 應用程序應盡量避免在循環語句中編寫對數據庫存取的代碼,應改用WHERE條件子句一次性從數據庫中獲取所有紀錄的方式來替換。在分布式應用環境中,前臺系統與后臺數據庫系統間一般采用斷開式的連接請求的形式,即前臺系統向數據庫發出連接請求→數據庫響應請求→前臺系統發送SQL語句→后臺數據庫執行SQL語句→斷開數據庫連接→執行結束。因此,在循環中進行數據庫存取將導致數據庫頻繁的進行建立連接、斷開連接的操作而影響整個系統的性能。

2) 盡可能在應用程序中完成各種計算,從而減少在數據庫級別的運行和操作。

3) 在應用程序中把需要進行多個SQL語句進行聯合查詢,并且中間會產生較大數據量的操作整合到存儲過程中,這樣可以大大減少網絡的數據流量,提高系統運行效率[14]。

4 硬件配置

對于大型的數據庫系統,數據庫服務器的硬件配置對數據庫系統的運行效率有著巨大的影響,硬件配置的升級往往能帶來運行效率的大幅提升。

1) 選用高I/O性能的磁盤驅動器。數據庫中的數據存放在物理磁盤上,數據庫系統需要頻繁對物理磁盤進行讀取并向用戶返回數據,因此磁盤的I/O性能對數據庫的效率具有很大影響,尤其是在大量并發訪問的情況下,影響尤其明顯[6]。

2) 優先考慮使用多CPU或多核CPU的服務器系統。多個CPU或多核CPU可以并發執行多個數據庫操作線程,因此可以大大提高數據庫的并發能力。

3) 增加服務器內存,大容量的內存可以把整個數據庫全部加載到內存中,從而大大減少對物理磁盤的I/O請求,因此可以大大加快對數據庫的查詢速度。筆者做過一個測試,在一臺雙核2.2GHz的CPU、1GB內存、7200轉/分磁盤的機器上有一個產品數據庫。共有50萬個產品,執行一個分頁存儲過程獲取最后20條產品紀錄的時間是7秒。給機器增加了1G內存后,獲取最后20條紀錄的時間只有1秒。主要原因就是系統把整個產品表都緩存到了內存,查詢速度大大加快。

5 結束語

總之,數據庫性能的優化是一個整體工程,應考慮到方方面面。每一個小的調整,都可能會對系統性能產生很大影響。另外,影響關系數據庫性能的因素錯綜復雜,因此優化策略必須根據實際情況進行不斷調整和測試。以上列舉了影響數據庫系統性能的一些共性問題。在實際應用中,必須根據實際情況進行適當的折衷和平衡。

參考文獻:

[1] 車爭,夏巨諶,胡國安. 基于SQL Server 的數據庫與應用程序的優化[J]. 計算機輔助工程,2002,(4):14-18.

[2] 劉云生. 現代數據庫技術[M].北京:國防工業出版社,2001.

[3] Molina H G, Salem K. Main Memory Database Systems: An Overview[J]. IEEE Transactions on Knowledge and Data Engineering,1992, 4(6): 509-516.

[4] 李春葆,曾平. 數據庫原理與應用[M]. 北京:清華大學出版社,2005.

[5] 薩師煊,王珊.數據庫系統概論[M].3版.北京:高等教育出版社,2000.

[6] 錢文波,謝金寶.SQLSerVer數據庫性能優化技術[J].微型機與應用,1999,18(3):7-9,22.

[7] 趙穎,沈金龍.基于SQLSerVer的應用程序優化[J].電子工程師,20(X),26(5):7-9.

[8] Scott W. Ambler ,Mapping Objects To Relational Databases[M] . AmbySoft2 Inc. ,Februrayr 1999.

[9] 尹大成,周津,朱明.電信網管數據模型三種數據庫實現性能測試報告[P].中興通訊技術報告.20031041.

[10] Johnny Olsson ,WM2data ,Allan R. Lassen ,Ramb ll. Experiences from Ob2ject2relational Programming in Oracle8,http:∥www.cit.dk/COT/reports/reports/Case4/062v1.4/cot2420621.4.pdf.

[11] 尹萍,SQL Server 數據庫性能優化[J].計算機應用與軟件,2005,(3):52-54.

[12] ROB P, CORONEL C. DataBase System Design, Realization and Management[M]. 北京:清華大學出版社,2005.

[13] 王宏志, 李建中, 駱吉洲, 等. 海量關系數據庫的壓縮存儲與查詢策略[EB/OL]. http://dev.csdn.net/develop/article/82/82593.shtm,2005.

[14] 車爭,夏巨諶. 基于SQL Server 的數據庫與應用程序的優化[J].計算機輔助工程, 2002,(4):14-18.