張 捷

[摘要]設(shè)計一個應(yīng)用系統(tǒng)似乎并不難,但是要想使系統(tǒng)達(dá)到最優(yōu)化的性能并不是一件容易的事。在開發(fā)工具、數(shù)據(jù)庫設(shè)計、應(yīng)用程序的結(jié)構(gòu)、查詢設(shè)計、接口選擇等方面有多種選擇,這取決于特定的應(yīng)用需求以及開發(fā)隊伍的技能。以SQL Server為例,從后臺數(shù)據(jù)庫的角度討論應(yīng)用程序性能優(yōu)化技巧,并且給出一些有益的建議。

[關(guān)鍵詞]SQL Server數(shù)據(jù)庫優(yōu)化高效索引

中圖分類號:TP3文獻(xiàn)標(biāo)識碼:A文章編號:1671-7597(2009)1210114-01

一、使用良好的數(shù)據(jù)庫設(shè)計方案

(一)邏輯數(shù)據(jù)庫規(guī)范化問題。一般來說,邏輯數(shù)據(jù)庫設(shè)計會滿足規(guī)范化的前3級標(biāo)準(zhǔn):第1規(guī)范:沒有重復(fù)的組或多值的列;第2規(guī)范:每個非關(guān)鍵字段必須依賴于主關(guān)鍵字,不能依賴于一個組合式主關(guān)鍵字的某些組成部分;第3規(guī)范:一個非關(guān)鍵字段不能依賴于另一個非關(guān)鍵字段。遵守這些規(guī)則的數(shù)據(jù)庫設(shè)計會產(chǎn)生較少的列和更多的表,因而也就減少了數(shù)據(jù)冗余,也減少了用于存儲數(shù)據(jù)的頁。

(二)生成物理數(shù)據(jù)庫。要想正確選擇基本物理實現(xiàn)策略,必須了解和利用好數(shù)據(jù)庫訪問格式和硬件資源的操作特點,特別是內(nèi)存和磁盤子系統(tǒng)I/O。以下是一些常用技巧:與每個表列相關(guān)的數(shù)據(jù)類型應(yīng)該反映數(shù)據(jù)所需的最小存儲空間,特別是對于被索引的列更是如此。用SQLServer段把一個頻繁使用的大表分割開,并放在多個單獨的智能型磁盤控制器的數(shù)據(jù)庫設(shè)備上,這樣也可以提高性能。因為有多個磁頭在查找,所以數(shù)據(jù)分離也能提高性能。

二、合理使用索引并簡化排序

索引是數(shù)據(jù)庫中重要的數(shù)據(jù)結(jié)構(gòu),它的根本目的就是提高查詢效率。索引的使用要恰到好處,其使用原則如下:在經(jīng)常進(jìn)行連接,但是沒有指定為外鍵的列上建立索引,而不經(jīng)常連接的字段則由優(yōu)化器自動生成索引;在頻繁進(jìn)行排序或分組(即進(jìn)行g(shù)roupby或orderby操作)的列上建立索引;在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立索引,在不同值少的列上不要建立索引。比如在雇員表的“性別”列上只有“男”與“女”兩個不同值,因此就無必要建立索引。如果建立索引不但不會提高查詢效率,反而會嚴(yán)重降低更新速度。如果待排序的列有多個,可以在這些列上建立復(fù)合索引。

在數(shù)據(jù)庫應(yīng)用設(shè)計階段應(yīng)當(dāng)盡量簡化或避免對大型表進(jìn)行重復(fù)的排序。當(dāng)能夠利用索引自動以適當(dāng)?shù)拇涡虍a(chǎn)生輸出時,優(yōu)化器就避免了排序這個步驟。為了避免不必要的排序,就要正確地增建索引,合理地合并數(shù)據(jù)庫表(盡管有時可能影響表的規(guī)范化,但相對于效率的提高是值得的)。如果排序不可避免,那么應(yīng)當(dāng)試圖簡化它,如縮小排序的列的范圍等。

三、設(shè)計高效的查詢

1.如果有獨特的索引,那么帶有“=”操作符的WHERE子句性能最好,其次是封閉的區(qū)間(范圍),再其次是開放的區(qū)間。

2.從數(shù)據(jù)庫訪問的角度看,含有不連續(xù)連接詞(OR和IN)的WHERE子句一般來說性能不會太好。所以,優(yōu)化器可能會采用R策略,這種策略會生成1個工作表,其中含有每個可能匹配的執(zhí)行的標(biāo)識符,優(yōu)化器把這些行標(biāo)志符(頁號和行號)看做是指向1個表中匹配的行的“動態(tài)索引”。優(yōu)化器只需掃描工作表,取出每一個行標(biāo)志符,再從數(shù)據(jù)表中取得相應(yīng)的行,所以R策略的代價是生成工作表。

3.包含NOT、<>、或!=的WHERE子句對于優(yōu)化器的索引選擇來說沒有什么用處。因為這樣的子句是排斥性的,而不是包括性的,所以在掃描整個原來數(shù)據(jù)表之前無法確定子句的選擇性。

4.限制數(shù)據(jù)轉(zhuǎn)換和串操作,優(yōu)化器一般不會根據(jù)WHERE子句中的表達(dá)式和數(shù)據(jù)轉(zhuǎn)換式生成索引選擇。例如:

paycheck * 12>36000 or substring(lastname,1,1)=“L”

如果該表建立了針對paycheck和lastname的索引,就不能利用索引進(jìn)行優(yōu)化,可以改寫上面的條件表達(dá)式為:

paycheck<36000/12 or lastname like “L%”

5.如果沒有包含合并子句的索引,那么優(yōu)化器構(gòu)造1個工作表以存放合并中最小的表中的行。然后再在這個表上構(gòu)造1個分簇索引以完成一個高效的合并。這種作法的代價是工作表的生成和隨后的分族索引的生成,這個過程叫REFORMATTING。所以應(yīng)該注意RAM中或磁盤上的數(shù)據(jù)庫tempdb的大小(除了SELECT INTO語句)。

四、創(chuàng)造良好的SQLServer數(shù)據(jù)庫應(yīng)用環(huán)境

(一)操作系統(tǒng)。操作系統(tǒng)性能的好壞直接影響數(shù)據(jù)庫的使用性能,如果操作系統(tǒng)存在問題,如CPU過載、過度內(nèi)存交換、磁盤I/O瓶頸等,在這種情況下,單純進(jìn)行數(shù)據(jù)庫內(nèi)部性能調(diào)整是不會改善系統(tǒng)性能的。我們可以通過WindowsNT的系統(tǒng)監(jiān)視器(SystemMonitor)來監(jiān)控各種設(shè)備,發(fā)現(xiàn)性能瓶頸。

(二)CPU。一種常見的性能問題就是缺乏處理能力。系統(tǒng)的處理能力是由系統(tǒng)的CPU數(shù)量、類型和速度決定的。如果系統(tǒng)沒有足夠的CPU處理能力,它就不能足夠快地處理事務(wù)以滿足需要。我們可以使用System Monitor確定CPU的使用率,如果以75%或更高的速率長時間運行,就可能碰到了CPU瓶頸問題,這時應(yīng)該升級CPU。而當(dāng)確定需要更強的處理能力,可以添加CPU或者用更快的CPU替換。

(三)內(nèi)存。SQLServer可使用的內(nèi)存量是SQLServer性能最關(guān)鍵因素之一。而內(nèi)存同I/O子系統(tǒng)的關(guān)系也是一個非常重要的因素。例如,在I/O操作頻繁的系統(tǒng)中,SQLServer用來緩存數(shù)據(jù)的可用內(nèi)存越多,必須執(zhí)行的物理I/O也就越少。這是因為數(shù)據(jù)將從數(shù)據(jù)緩存中讀取而不是從磁盤讀取。同樣,內(nèi)存量的不足會引起明顯的磁盤讀寫瓶頸,因為系統(tǒng)緩存能力不足會引起更多的物理磁盤I/O。

(四)I/O子系統(tǒng)。由I/O子系統(tǒng)發(fā)生的瓶頸問題是數(shù)據(jù)庫系統(tǒng)可能遇到的最常見的同硬件有關(guān)的問題。配置很差的I/O子系統(tǒng)引起性能問題的嚴(yán)重程度僅次于編寫很差的SQL語句。I/O子系統(tǒng)問題是這樣產(chǎn)生的,一個磁盤驅(qū)動器能夠執(zhí)行的I/O操作是有限的,一般一個普通的磁盤驅(qū)動器每秒只能處理85次I/O操作,如果磁盤驅(qū)動器超載,到這些磁盤驅(qū)動器的I/O操作就要排隊,SQL的I/O延遲將很長。解決I/O子系統(tǒng)有關(guān)的問題也許是最容易的,多數(shù)情況下,增加磁盤驅(qū)動器就可以解決這個性能問題。

當(dāng)然,影響性能的因素很多,而應(yīng)用又各不相同,找出一個通用的優(yōu)化方案是很困難的,只能是在系統(tǒng)開發(fā)和維護的過程中針對運行的具體情況,不斷加以調(diào)整。

參考文獻(xiàn):

[1]邵遠(yuǎn)山,基于DB2數(shù)據(jù)庫應(yīng)用系統(tǒng)的性能優(yōu)化[D].安徽大學(xué),2004年.

[2]劉博,Oracle數(shù)據(jù)庫性能調(diào)整與優(yōu)化[D].大連理工大學(xué),2007年.

[3]李學(xué)強、羅省賢,基于ORACLE系統(tǒng)的數(shù)據(jù)庫性能優(yōu)化設(shè)計[J].北京印刷學(xué)院學(xué)報,2006年06期.

作者簡介:

張捷,男,湖北省武穴市人,黃岡職業(yè)計算機學(xué)院計算機系教師,軟件設(shè)計師,研究方向:軟件設(shè)計、數(shù)據(jù)庫管理。