段艷明，肖輝輝

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

關(guān)系數(shù)據(jù)庫性能的優(yōu)化研究

段艷明，肖輝輝

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

數(shù)據(jù)庫管理是數(shù)據(jù)管理的重要部分，是計算機(jī)學(xué)科的重要分支。數(shù)據(jù)庫性能的好壞直接決定著數(shù)據(jù)庫應(yīng)用系統(tǒng)的運(yùn)行效率。在闡述影響數(shù)據(jù)庫性能的主要因素的基礎(chǔ)上，結(jié)合實(shí)際探討了優(yōu)化數(shù)據(jù)庫性能的一些策略，分析和比較了不同策略可能對數(shù)據(jù)庫性能的具體影響。

關(guān)系數(shù)據(jù)庫;數(shù)據(jù)庫性能;優(yōu)化

0 引言

數(shù)據(jù)是目前最重要的信息載體之一，而數(shù)據(jù)庫則是實(shí)現(xiàn)數(shù)據(jù)存儲和應(yīng)用的手段，數(shù)據(jù)庫的作用越來越受到重視。如何更好地優(yōu)化數(shù)據(jù)庫的性能，使之發(fā)揮快捷而有效的作用，是數(shù)據(jù)庫應(yīng)用的更高要求。目前，大部分應(yīng)用系統(tǒng)的數(shù)據(jù)庫主要是關(guān)系數(shù)據(jù)庫，其性能優(yōu)化問題一直是數(shù)據(jù)庫應(yīng)用的研究重點(diǎn)。數(shù)據(jù)庫性能優(yōu)化是指進(jìn)行有目的地調(diào)整組件結(jié)構(gòu)和改善組件性能，增加數(shù)據(jù)庫的吞吐量，減少數(shù)據(jù)庫操作的處理時間。本文主要從數(shù)據(jù)庫結(jié)構(gòu)、數(shù)據(jù)庫訪問、數(shù)據(jù)庫查詢和數(shù)據(jù)庫測試等方面對數(shù)據(jù)庫性能進(jìn)行優(yōu)化。

1 數(shù)據(jù)庫結(jié)構(gòu)的優(yōu)化

要提高數(shù)據(jù)庫的性能，最關(guān)鍵的是要有一個好的數(shù)據(jù)庫結(jié)構(gòu)，一個好的數(shù)據(jù)庫結(jié)構(gòu)的設(shè)計能為優(yōu)化數(shù)據(jù)庫性能打下良好的基礎(chǔ)。因此，提高數(shù)據(jù)庫應(yīng)用系統(tǒng)的性能首先應(yīng)從數(shù)據(jù)庫結(jié)構(gòu)的優(yōu)化設(shè)計開始。

1.1 基本表的規(guī)范化和反規(guī)范化

數(shù)據(jù)庫系統(tǒng)中的數(shù)據(jù)都是以基本表的形式存儲，基本表的結(jié)構(gòu)設(shè)計在很大程度上決定著數(shù)據(jù)庫系統(tǒng)的性能。

1.1.1 規(guī)范化

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

在數(shù)據(jù)庫設(shè)計中，應(yīng)該盡量使基本表達(dá)到第三范式模式［1］?；诘谌妒降臄?shù)據(jù)庫基本表設(shè)計具有很多優(yōu)點(diǎn):

①能消除數(shù)據(jù)冗余、節(jié)省磁盤存儲空間;

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

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

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

⑤在運(yùn)行大多數(shù)事務(wù)時性能好;

⑥物理設(shè)計的機(jī)動性和擴(kuò)展性較大，能滿足日益增長的用戶需求。

1.1.2 反規(guī)范化

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

根據(jù)數(shù)據(jù)庫在性能方面的不同，運(yùn)用不同的方法進(jìn)行反規(guī)范化。以下反規(guī)范化設(shè)計經(jīng)實(shí)踐驗(yàn)證能提高數(shù)據(jù)庫的性能。

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

②存儲常用的計算字段到數(shù)據(jù)庫實(shí)體中。

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

③重新定義實(shí)體來減少外部屬性數(shù)據(jù)或行數(shù)據(jù)的開支。一般是把基本表按元組或?qū)傩苑指畛啥鄠€表，這樣能分開頻繁被訪問的數(shù)據(jù)和較少被訪問的數(shù)據(jù)，以及頻繁被訪問的歷史數(shù)據(jù)和較少被訪問的歷史數(shù)據(jù)。

1.2 建立正確的索引

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

一般地，設(shè)計索引時應(yīng)遵循如下原則:

①避免在經(jīng)常被更新的列上建立索引;

②不宜在同一張表上過多建立索引，一張表上的索引過多會影響該表的更新性能，因?yàn)橐坏┌l(fā)生更新行為，該表所有的索引都必須作相應(yīng)的調(diào)整，這將會延誤運(yùn)行時間;

③對小型表不必建立索引，因?yàn)閷τ谛”韥碚f直接掃描往往更快而且維護(hù)費(fèi)用低;

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

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

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

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

1.3 SQL語句的優(yōu)化

數(shù)據(jù)庫應(yīng)用系統(tǒng)是通過SQL語句來對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行操作。因此，SQL語句的優(yōu)化對數(shù)據(jù)庫系統(tǒng)性能的提高起著決定性的作用。通過實(shí)踐編程證明，以下措施能在很大程度上優(yōu)化SQL語句。

(1)SQL語句的結(jié)構(gòu)盡量簡單。在查詢時，不要過多地使用通配符“*”，要用到幾列就選擇幾列。如語句“SELECT*FROM student”，應(yīng)該改寫為“SELECT Name，Age FROM student”。另外，在可能的情況下盡量限制結(jié)果集的行數(shù)，因?yàn)橛行┣闆r下用戶不需要那么多的數(shù)據(jù)。如可用語句“SELECT TOP 100 Name，Grade FROM student”來限制從student表中獲得前100名學(xué)生的“Name”和“Grade”。

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

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

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

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

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

2 數(shù)據(jù)庫連接優(yōu)化

2.1 避免重復(fù)連接數(shù)據(jù)庫

重復(fù)連接數(shù)據(jù)庫的時間，要比在數(shù)據(jù)庫中執(zhí)行一條命令的時間長得多。因此，數(shù)據(jù)庫連接后保持其連接狀態(tài)，不要用完就關(guān)閉連接，應(yīng)盡量避免重復(fù)連接數(shù)據(jù)庫［3］。若應(yīng)用程序中所有操作都重復(fù)建立數(shù)據(jù)庫連接，將嚴(yán)重影響應(yīng)用程序性能。

2.2 選擇合適高效的數(shù)據(jù)庫連接方法

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

3 數(shù)據(jù)庫查詢優(yōu)化

在各種數(shù)據(jù)庫的操作中，查詢操作所占的比重最大。因此，查詢操作的優(yōu)化將在很大程度上決定著整個數(shù)據(jù)庫系統(tǒng)的性能優(yōu)化。查詢優(yōu)化的重點(diǎn)環(huán)節(jié)是使數(shù)據(jù)庫服務(wù)器盡量少從磁盤中讀數(shù)據(jù)，以及盡量減少全表掃描的操作［6］。主要可以從下面幾點(diǎn)來優(yōu)化查詢。

3.1 消除對大型表行數(shù)據(jù)的順序存取操作

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

3.2 使用視圖加速查詢操作

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

以得到大幅提高。

3.3 強(qiáng)制查詢優(yōu)化器使用某個索引

在某種情況下，我們可以強(qiáng)制查詢優(yōu)化器來使用特定的索引。例如語句“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”。這樣，查詢優(yōu)化器會強(qiáng)行利用索引IX_NoID來執(zhí)行查詢。

3.4 含有OR和IN的WHERE子句

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

3.5 存儲衍生數(shù)據(jù)

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

①當(dāng)對表中或多行進(jìn)行重復(fù)計算時，可以通過在表內(nèi)增加列來存儲結(jié)果;

②當(dāng)按類對表進(jìn)行重復(fù)計算時，可以通過增加新表存儲相關(guān)結(jié)果。

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

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

4 數(shù)據(jù)庫測試優(yōu)化

在進(jìn)行了數(shù)據(jù)庫結(jié)構(gòu)、數(shù)據(jù)庫訪問、數(shù)據(jù)庫查詢操作等方面優(yōu)化之后，我們再對數(shù)據(jù)庫進(jìn)行測試其優(yōu)化程度。數(shù)據(jù)庫測試進(jìn)行的好壞直接體現(xiàn)了全面的優(yōu)化工作，一般從以下幾個方面來優(yōu)化數(shù)據(jù)庫的測試。

4.1 用大量的數(shù)據(jù)進(jìn)行測試

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

4.2 對足夠多的用戶進(jìn)行并發(fā)測試

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

4.3 選擇合適的測試軟件進(jìn)行測試

對數(shù)據(jù)庫進(jìn)行測試的工具有多種，若能利用好測試工具將加快測試速度和提高測試效率。目前，軟件BF(Benchmark Factory for Databases)能很好地完成數(shù)據(jù)庫系統(tǒng)的測試，BF內(nèi)置的標(biāo)準(zhǔn)測試腳本AS3AP可用于結(jié)構(gòu)化查詢語言(SQL)關(guān)系型數(shù)據(jù)庫的測試。BF通過一臺獨(dú)立的控制臺控制多臺服務(wù)器充當(dāng)?shù)目蛻舳藖懋a(chǎn)生數(shù)據(jù)庫測試壓力。

5 結(jié)束語

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

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

［2］葉忠杰，石建軍，戎成.優(yōu)化查詢的數(shù)據(jù)庫非規(guī)范化設(shè)計［J］.浙江交通職業(yè)技術(shù)學(xué)院學(xué)報，2008，(3).

［3］王澤.一般關(guān)系數(shù)據(jù)庫多表智能連接算法及應(yīng)用［J］.計算機(jī)工程與設(shè)計，2007，(23).

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

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

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

［7］范孝良，杜亞維.ERP環(huán)境下關(guān)系數(shù)據(jù)庫的性能優(yōu)化［J］.東北電力技術(shù)，2005，(5):50－52.

［8］樊新華.關(guān)系數(shù)據(jù)庫的查詢優(yōu)化技術(shù)［J］.計算機(jī)與數(shù)字工程，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－)，女，江西永新人，河池學(xué)院計算機(jī)與信息科學(xué)系講師，主要研究方向:計算機(jī)應(yīng)用技術(shù)、數(shù)據(jù)庫技術(shù)、人工智能。

2010－10－08

［責(zé)任編輯 劉景平］