陳蘇蓉,朱曉輝

摘要:后臺數(shù)據(jù)庫系統(tǒng)的性能優(yōu)劣決定了軟件系統(tǒng)的整體運(yùn)行效率。為了提高系統(tǒng)的運(yùn)行效率,需要對數(shù)據(jù)庫系統(tǒng)進(jìn)行必要的調(diào)整和優(yōu)化。分析了和比較了不同方案對數(shù)據(jù)庫性能的影響。提出了數(shù)據(jù)庫優(yōu)化的一般性的準(zhǔn)則,并結(jié)合實(shí)際項(xiàng)目的測試數(shù)據(jù)證明之。

關(guān)鍵詞:數(shù)據(jù)庫設(shè)計(jì);性能優(yōu)化;T-SQL優(yōu)化

中圖分類號:TP311文獻(xiàn)標(biāo)識碼: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

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

1 數(shù)據(jù)庫設(shè)計(jì)

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

1.1 邏輯設(shè)計(jì)

數(shù)據(jù)庫設(shè)計(jì)過程包括:現(xiàn)實(shí)世界→需求分析→概念設(shè)計(jì)→邏輯設(shè)計(jì)→物理設(shè)計(jì)[5]。

概念設(shè)計(jì)是利用數(shù)據(jù)模型進(jìn)行概念數(shù)據(jù)庫的模式設(shè)計(jì),一般用E-R圖來進(jìn)行描述。邏輯設(shè)計(jì)是把概念設(shè)計(jì)得到的概念數(shù)據(jù)庫模式變?yōu)檫壿嫈?shù)據(jù)模式,常用到函數(shù)依賴、范式、關(guān)系分解等多種技術(shù)。好的邏輯設(shè)計(jì)可以為優(yōu)化數(shù)據(jù)庫性能打下良好的基礎(chǔ)。在SQL Server 2008中進(jìn)行邏輯設(shè)計(jì)時(shí)一般應(yīng)遵循如下幾個(gè)原則:

1) 所有邏輯表結(jié)構(gòu)應(yīng)盡量滿足第一、第二、第三范式[5],從而有效減少數(shù)據(jù)冗余,提高查詢性能并減少因數(shù)據(jù)冗余導(dǎo)致的數(shù)據(jù)不一致性錯(cuò)誤。

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

3) 充分利用SQL2008數(shù)據(jù)庫的現(xiàn)有的規(guī)則功能來自動(dòng)維護(hù)數(shù)據(jù)庫的一致性和完整性。避免未滿足完整性規(guī)則的“臟數(shù)據(jù)”進(jìn)入數(shù)據(jù)庫。常用的技術(shù)有:

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

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

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

對同時(shí)出現(xiàn)在where子句、order by子句或group by子句的多個(gè)字段上創(chuàng)建復(fù)合索引,并且復(fù)合索引的次序相同于子句中這些字段的排列次序。

1.2 物理設(shè)計(jì)

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

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

3) 對數(shù)據(jù)庫進(jìn)行索引優(yōu)化,利用索引可以避免表掃描,并減少因查詢而造成的I/O開銷,從而提高系統(tǒng)性能.可以用SQL Server2008中查詢分析器來對索引進(jìn)行優(yōu)化和調(diào)整。

2 T-SQL編寫

經(jīng)過優(yōu)化的T-SQL語句可以極大提高數(shù)據(jù)的存取效率,以下是常用的優(yōu)化技巧。

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

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

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

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

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

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

3 應(yīng)用程序優(yōu)化

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

2) 盡可能在應(yīng)用程序中完成各種計(jì)算,從而減少在數(shù)據(jù)庫級別的運(yùn)行和操作。

3) 在應(yīng)用程序中把需要進(jìn)行多個(gè)SQL語句進(jìn)行聯(lián)合查詢,并且中間會(huì)產(chǎn)生較大數(shù)據(jù)量的操作整合到存儲過程中,這樣可以大大減少網(wǎng)絡(luò)的數(shù)據(jù)流量,提高系統(tǒng)運(yùn)行效率[14]。

4 硬件配置

對于大型的數(shù)據(jù)庫系統(tǒng),數(shù)據(jù)庫服務(wù)器的硬件配置對數(shù)據(jù)庫系統(tǒng)的運(yùn)行效率有著巨大的影響,硬件配置的升級往往能帶來運(yùn)行效率的大幅提升。

1) 選用高I/O性能的磁盤驅(qū)動(dòng)器。數(shù)據(jù)庫中的數(shù)據(jù)存放在物理磁盤上,數(shù)據(jù)庫系統(tǒng)需要頻繁對物理磁盤進(jìn)行讀取并向用戶返回?cái)?shù)據(jù),因此磁盤的I/O性能對數(shù)據(jù)庫的效率具有很大影響,尤其是在大量并發(fā)訪問的情況下,影響尤其明顯[6]。

2) 優(yōu)先考慮使用多CPU或多核CPU的服務(wù)器系統(tǒng)。多個(gè)CPU或多核CPU可以并發(fā)執(zhí)行多個(gè)數(shù)據(jù)庫操作線程,因此可以大大提高數(shù)據(jù)庫的并發(fā)能力。

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

5 結(jié)束語

總之,數(shù)據(jù)庫性能的優(yōu)化是一個(gè)整體工程,應(yīng)考慮到方方面面。每一個(gè)小的調(diào)整,都可能會(huì)對系統(tǒng)性能產(chǎn)生很大影響。另外,影響關(guān)系數(shù)據(jù)庫性能的因素錯(cuò)綜復(fù)雜,因此優(yōu)化策略必須根據(jù)實(shí)際情況進(jìn)行不斷調(diào)整和測試。以上列舉了影響數(shù)據(jù)庫系統(tǒng)性能的一些共性問題。在實(shí)際應(yīng)用中,必須根據(jù)實(shí)際情況進(jìn)行適當(dāng)?shù)恼壑院推胶狻?/p>

參考文獻(xiàn):

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

[2] 劉云生. 現(xiàn)代數(shù)據(jù)庫技術(shù)[M].北京:國防工業(yè)出版社,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] 李春葆,曾平. 數(shù)據(jù)庫原理與應(yīng)用[M]. 北京:清華大學(xué)出版社,2005.

[5] 薩師煊,王珊.數(shù)據(jù)庫系統(tǒng)概論[M].3版.北京:高等教育出版社,2000.

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

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

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

[9] 尹大成,周津,朱明.電信網(wǎng)管數(shù)據(jù)模型三種數(shù)據(jù)庫實(shí)現(xiàn)性能測試報(bào)告[P].中興通訊技術(shù)報(bào)告.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 數(shù)據(jù)庫性能優(yōu)化[J].計(jì)算機(jī)應(yīng)用與軟件,2005,(3):52-54.

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

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

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