關系數據庫性能的優化研究

段艷明，肖輝輝

(河池學院 計算機與信息科學系，廣西 宜州 546300)

關系數據庫性能的優化研究

段艷明，肖輝輝

(河池學院 計算機與信息科學系，廣西 宜州 546300)

數據庫管理是數據管理的重要部分，是計算機學科的重要分支。數據庫性能的好壞直接決定著數據庫應用系統的運行效率。在闡述影響數據庫性能的主要因素的基礎上，結合實際探討了優化數據庫性能的一些策略，分析和比較了不同策略可能對數據庫性能的具體影響。

關系數據庫;數據庫性能;優化

0 引言

數據是目前最重要的信息載體之一，而數據庫則是實現數據存儲和應用的手段，數據庫的作用越來越受到重視。如何更好地優化數據庫的性能，使之發揮快捷而有效的作用，是數據庫應用的更高要求。目前，大部分應用系統的數據庫主要是關系數據庫，其性能優化問題一直是數據庫應用的研究重點。數據庫性能優化是指進行有目的地調整組件結構和改善組件性能，增加數據庫的吞吐量，減少數據庫操作的處理時間。本文主要從數據庫結構、數據庫訪問、數據庫查詢和數據庫測試等方面對數據庫性能進行優化。

1 數據庫結構的優化

要提高數據庫的性能，最關鍵的是要有一個好的數據庫結構，一個好的數據庫結構的設計能為優化數據庫性能打下良好的基礎。因此，提高數據庫應用系統的性能首先應從數據庫結構的優化設計開始。

1.1 基本表的規范化和反規范化

數據庫系統中的數據都是以基本表的形式存儲，基本表的結構設計在很大程度上決定著數據庫系統的性能。

1.1.1 規范化

規范化的基本思想就是逐步消除數據依賴中不合適的部分，使各關系模式達到某種程度的“分離”。規范化能消除數據冗余，避免一定的更新異常，提高數據庫的完整性，從而提高數據庫的可維護性和可靠性，但是，規范化也會把一個全局關系分解為多個規范化關系，會導致連接操作效率低，從而影響整個關系數據庫的查詢效率。因此，要綜合各種因素適當地規范化數據庫基本表。

在數據庫設計中，應該盡量使基本表達到第三范式模式［1］?；诘谌妒降臄祿旎颈碓O計具有很多優點:

①能消除數據冗余、節省磁盤存儲空間;

②能良好的限制數據完整性，使數據容易維護、移植和更新;

③數據的可逆性好，在連接查詢或者合并表時不遺漏、不重復;

④消除了數據冗余列，使查詢時每個數據頁存儲的數據行增多，這樣能有效地減少邏輯I/O和物理I/O;

⑤在運行大多數事務時性能好;

⑥物理設計的機動性和擴展性較大，能滿足日益增長的用戶需求。

1.1.2 反規范化

一般數據庫設計時要滿足規范化，但是否數據庫基本表的規范化程度越高越好呢?規范化越高，產生的關系就越多，過多的關系會導致表之間的連接操作頻繁，而表之間的連接操作性能較低，進而直接影響到查詢的速度［2］。所以對于查詢操作較多的應用，系統就需要根據實際情況運用反規范化對數據庫基本表進行設計，通過反規范來提高查詢的性能，進而提高整個數據庫的性能。因此，適當降低數據庫規范化程度可以簡化復雜處理進而提高數據庫的性能。

根據數據庫在性能方面的不同，運用不同的方法進行反規范化。以下反規范化設計經實踐驗證能提高數據庫的性能。

①在數據庫基本表中加入重復屬性(列)來表示許多四路或更多路合并的關系。

②存儲常用的計算字段到數據庫實體中。

如某一個項目的工資管理系統中有工資表，其字段為:員工編號、基本工資、崗位津貼、補發工資、扣發工資等，而實發工資(基本工資+崗位津貼+補發工資－扣發工資)是員工經常需要在查詢和報表中用到的，在表中的記錄量也很大，這時有必要把實發工資作為一個獨立的字段加入到表中。這里可以采用觸發器來保持客戶端的數據一致性。

③重新定義實體來減少外部屬性數據或行數據的開支。一般是把基本表按元組或屬性分割成多個表，這樣能分開頻繁被訪問的數據和較少被訪問的數據，以及頻繁被訪問的歷史數據和較少被訪問的歷史數據。

1.2 建立正確的索引

索引是數據庫應用系統設計和開發中的一個重要的方面，使用索引能夠快速找到某個特定數據塊，它可以大大減少找到數據行所需的I/O操作。因此，索引的建立及其正確與否對應用系統的性能有著至關重要的影響。合理使用索引文件，對于改善數據查詢速度有著舉足輕重的作用，可以很大程度地改善數據庫應用程序的性能。

一般地，設計索引時應遵循如下原則:

①避免在經常被更新的列上建立索引;

②不宜在同一張表上過多建立索引，一張表上的索引過多會影響該表的更新性能，因為一旦發生更新行為，該表所有的索引都必須作相應的調整，這將會延誤運行時間;

③對小型表不必建立索引，因為對于小表來說直接掃描往往更快而且維護費用低;

④在經常要做查詢的列上建立相應的索引，尤其是在where語句中涉及的列;

⑤在經常做連接的列上分別建立相應的索引，這樣會提高連接的速度;

⑦建立簇索引，在經常做order by或group by操作且更新很少的列上建立簇索引;

⑧在重復值非常大的列上建立位圖索引，而不是建立普通索引，如果在這樣的列建立普通索引，其查詢效率不高，反而增加了索引的維護代價。

1.3 SQL語句的優化

數據庫應用系統是通過SQL語句來對數據庫中的數據進行操作。因此，SQL語句的優化對數據庫系統性能的提高起著決定性的作用。通過實踐編程證明，以下措施能在很大程度上優化SQL語句。

(1)SQL語句的結構盡量簡單。在查詢時，不要過多地使用通配符“*”，要用到幾列就選擇幾列。如語句“SELECT*FROM student”，應該改寫為“SELECT Name，Age FROM student”。另外，在可能的情況下盡量限制結果集的行數，因為有些情況下用戶不需要那么多的數據。如可用語句“SELECT TOP 100 Name，Grade FROM student”來限制從student表中獲得前100名學生的“Name”和“Grade”。

(2)用EXISTS代替IN，用NOT EXIST代替NOT IN。EXISTS遠比IN的效率高，使用NOT EXIST會使查詢添加限制條件，由此減少全表掃描次數，從而加快查詢的速度以達到提高數據庫運行效率。

(3)盡量避免在帶WHERE的子句中對字段進行函數或表達式的操作，這將導致數據庫引擎放棄索引而進行全表掃描。如語句“SELECT Name from student WHERE average(Grade)=‘90’”，可以在表中增加一個平均分的字段“avg”，SELECT語句再改為“SELECT Name from student WHERE avg=‘90’”。

(4)用WHERE代替HAVING。HAVING子句會導致全表掃描后再選擇，而WHERE子句則是在掃描表的同時就進行了選擇，大大提高了查詢效率。但是當HAVING子句用于聚集函數時不能由WHERE代替。

(5)避免使用!=或＜＞、IS NULL或IS NOT NULL、IN或NOT IN等操作符，因為這些操作符會使系統無法使用索引，即優化器將無法通過索引來確定將要命中的行數，而只能直接掃描表中的所有行，導致查詢效率低。例如語句“SELECT Number form student WHERE Number!=‘2009%’”。

(6)盡量不使用游標。如果在不必要的情況下使用游標，會占用空間，導致增加系統資源。若某些必須使用游標的情況，可以將符合條件的數據行存入臨時表中，再對臨時表定義游標進行操作，可使數據庫效率得到明顯提高。

2 數據庫連接優化

2.1 避免重復連接數據庫

重復連接數據庫的時間，要比在數據庫中執行一條命令的時間長得多。因此，數據庫連接后保持其連接狀態，不要用完就關閉連接，應盡量避免重復連接數據庫［3］。若應用程序中所有操作都重復建立數據庫連接，將嚴重影響應用程序性能。

2.2 選擇合適高效的數據庫連接方法

應用程序連接數據庫的方法有多種，我們要針對不同的數據庫系統選擇恰當的數據庫連接方法。應用程序與關系數據庫的連接一般采用ODBC(Open Database Connectivity)、DAO(Data Access Objects)、ADO(ActiveX Data Objects)和OLE DB(Object L inking and Embedding)等進行數據庫連接。當然，不同的應用程序有各自不同的連接數據庫的方式，像用JAVA連接數據庫主要有兩種方式，一是用JDBC－ODBC橋來連接，二是用連接池來連接。對于Web應用系統，在Web和數據庫進行連接時，每一次Web請求都要建立一次數據庫連接，既費時又浪費了系統的內存資源。一般可以采用數據庫連接池技術來減少數據庫連接。數據庫連接池的基本思想就是為數據庫連接建立一個“緩沖池”，然后預先在緩沖池中建立一定數量的連接，當應用程序需要訪問數據庫時，只需從“緩沖池”中取出一個連接，用完之后再放回去。同時，我們可以通過規定連接池中最大連接數來防止應用系統無限制地與數據庫連接。更為重要的是我們可以通過連接池的管理機制監視數據庫的連接數量、使用情況，為系統開發、測試及性能調整提供依據［4－5］。

3 數據庫查詢優化

在各種數據庫的操作中，查詢操作所占的比重最大。因此，查詢操作的優化將在很大程度上決定著整個數據庫系統的性能優化。查詢優化的重點環節是使數據庫服務器盡量少從磁盤中讀數據，以及盡量減少全表掃描的操作［6］。主要可以從下面幾點來優化查詢。

3.1 消除對大型表行數據的順序存取操作

雖然在前面基本表的優化中已經建立了索引，但某些WHERE子句依然會強迫優化器使用順序存取。例如語句“SELECT*FROM student WHERE(stud_no=200910 AND stud_grade ＞90)OR stud_grad=95”。我們可以用并集來避免順序存取，即實現利用索引路徑處理查詢。如上面語句修改成“SELECT*FROM student WHERE stud_no=200910 AND stud_grade ＞90 UNION SELECT ＊FROM student WHERE stud_grad=95”。

3.2 使用視圖加速查詢操作

把基本表的一個子集進行排序并創建視圖能加速查詢。視圖中的行要比主表中的行少的多，而且其物理順序就是所要得到的順序。這樣，減少了磁盤I/O，避免多重排序操作和簡化優化器工作，查詢的效率可

以得到大幅提高。

3.3 強制查詢優化器使用某個索引

在某種情況下，我們可以強制查詢優化器來使用特定的索引。例如語句“SELECT* FROM student WHERE stud_no=10 AND stud_age＞20”，可以修改成“SELECT*FROM student(INDEX=IX_NoID)WHERE stud_no=10 AND stud_age＞20”。這樣，查詢優化器會強行利用索引IX_NoID來執行查詢。

3.4 含有OR和IN的WHERE子句

含有不連續連接詞(OR和IN)的WHERE子句的性能不太好。這種情況下，我們可以先使優化器生成1個工作表，包含每個可能匹配的標志符，優化器再把這些標志符(頁號和行號)看成指向基本表中相匹配的行的“動態索引”。這樣，優化器只需掃描工作表，獲得每一個行標志符，再從基本表中取得相應的行數據。

3.5 存儲衍生數據

對一些涉及到大量重復性計算的過程而言，當重復計算過程得到的結果相同，或當計算牽扯多行數據需額外的磁盤I/O開銷，或當計算復雜需要大量的CPU時間時，就考慮存儲計算結果。

①當對表中或多行進行重復計算時，可以通過在表內增加列來存儲結果;

②當按類對表進行重復計算時，可以通過增加新表存儲相關結果。

當然，在表內增加列或增加新表有利于加快訪問速度，但造成了數據冗余，違反了第三范式理論，這會增加維護數據完整性的代價，同時必須用觸發器立即更新，或利用存儲過程或應用代碼批量更新，以維護數據的完整性。所以，存儲衍生數據在一定程度上優化了數據庫性能，同時也在一定程度上造成了一些額外開銷，我們在設計數據庫時要根據實際情況利用存儲衍生數據這種優化數據庫基本表的方法。

上面是一些基本的提高查詢速度的措施，但在更多的情況下，往往需要反復試驗比較來得到最佳優化方案。最好的方法是測試，比較實現相同功能的SQL語句執行的時間。

4 數據庫測試優化

在進行了數據庫結構、數據庫訪問、數據庫查詢操作等方面優化之后，我們再對數據庫進行測試其優化程度。數據庫測試進行的好壞直接體現了全面的優化工作，一般從以下幾個方面來優化數據庫的測試。

4.1 用大量的數據進行測試

數據庫系統使用一段時間后，數據庫的性能一般會發生變化，而且數據庫中如果數據量很少，是比較不出來的。這時可以把實現相同功能的多條SQL語句放到查詢分析器上，查看對性能影響最大的所利用的索引和表掃描次數來比較性能優化程度。

4.2 對足夠多的用戶進行并發測試

大多數情況下，一個測試用戶不能真實地反映數據庫系統在真實使用情況下的負載情況。必須測試在多個并發用戶的情況下，數據庫系統是否會引起死鎖(deadlock)以及性能下降等問題。例如當兩個模塊以同樣的方式向基本表中插入記錄，并查詢該表的數據的操作。當這兩個模塊同時被訪問，會破壞數據完整性。只有經過多個用戶的同時操作測試，才可能發現數據不一致的問題。

4.3 選擇合適的測試軟件進行測試

對數據庫進行測試的工具有多種，若能利用好測試工具將加快測試速度和提高測試效率。目前，軟件BF(Benchmark Factory for Databases)能很好地完成數據庫系統的測試，BF內置的標準測試腳本AS3AP可用于結構化查詢語言(SQL)關系型數據庫的測試。BF通過一臺獨立的控制臺控制多臺服務器充當的客戶端來產生數據庫測試壓力。

5 結束語

數據庫性能優化是一項復雜的工程，各種因素相互影響，彼此矛盾［7］。數據庫性能的優化是一個系統工程，應貫穿于數據庫系統的整個生命周期。一個好的數據庫必須從數據庫的結構設計開始，再從數據庫的連接、數據庫的查詢和數據庫的測試等方面進行優化。同時，影響數據庫性能的因素和策略多且不固定，在不同的系統不同的環境中，決定數據庫性能的因素和策略都會變。因此，沒有一個通用的數據庫性能優化的方案，我們必須不斷通過實驗和測試來盡量找到合適的數據庫性能優化策略。

［1］薩師煊，王珊.數據庫系統概論(第四版)［M］.北京:高等教育出版社，2007.

［2］葉忠杰，石建軍，戎成.優化查詢的數據庫非規范化設計［J］.浙江交通職業技術學院學報，2008，(3).

［3］王澤.一般關系數據庫多表智能連接算法及應用［J］.計算機工程與設計，2007，(23).

［4］周彩蘭，陳才賢.基于Java的Web數據庫連接池高效管理策略［J］.武漢理工大學學報(信息與管理工程版)，2004，26(5):38－41.

［5］鄒雯奇，戚宇林.數據庫連接池技術在WEB系統開發中的應用［J］.微計算機信息，2006，(15):185－186.

［6］柳佳剛，劉高嵩.數據庫查詢性能優化的探討［J］.福建電腦，2005，(9).

［7］范孝良，杜亞維.ERP環境下關系數據庫的性能優化［J］.東北電力技術，2005，(5):50－52.

［8］樊新華.關系數據庫的查詢優化技術［J］.計算機與數字工程，2009，(12).

A Research into Optimization to Relational Database Performance

DUAN Yan-ming，XIAO Hui-hui
(Department of Computer and Information Science，Hechi University，Yizhou，Guangxi 546300，China)

Database management is both an important part of the data management，and an important branch of computer science.The database performance directly decides the operation efficiency of the database application system.This paper expounds the main factors that affect database performance，discusses some strategies for optimizing the database performance on the basis of reality，and analyses and compares the influences of different strategies on the specific database performance.

relational database;database performance;optimization

TP311.1

A

1672－9021(2011)02－0052－05

段艷明(1978－)，女，江西永新人，河池學院計算機與信息科學系講師，主要研究方向:計算機應用技術、數據庫技術、人工智能。

2010－10－08

［責任編輯 劉景平］