吳 綱

(武漢職業(yè)技術(shù)學(xué)院計(jì)算機(jī)學(xué)院,湖北武漢 430074)

在實(shí)際業(yè)務(wù)系統(tǒng)中,歷史數(shù)據(jù)是需要保存的,因?yàn)橐皇切枰M(jìn)行回溯查詢,查看一段時(shí)間內(nèi)或者歷史同期的情況做出性能比較;第二,這也是數(shù)據(jù)挖掘的基礎(chǔ),利于性能優(yōu)化。但是歷史數(shù)據(jù)會(huì)占用大量的空間,本文提到的移動(dòng)基站設(shè)備性能數(shù)據(jù),每個(gè)月的數(shù)據(jù)量大概有200萬(wàn)條,初始系統(tǒng)運(yùn)行近一年后,總記錄量約2500萬(wàn)條、數(shù)據(jù)庫(kù)的大小也有近70GB,頻頻出現(xiàn)查詢歷史數(shù)據(jù)緩慢的情況,并在Brow ser端常常顯示超時(shí)報(bào)警提示。

SQL Server作為一種企業(yè)級(jí)的數(shù)據(jù)庫(kù),提供了豐富的特性,比如分區(qū)視圖、索引視圖、聚集索引、查詢優(yōu)化器等,開發(fā)者可以從多個(gè)角度進(jìn)行性能分析與優(yōu)化。實(shí)踐表明,只要適當(dāng)進(jìn)行優(yōu)化,在普通配置的服務(wù)器上,一億條記錄以內(nèi)的情況,SQL Server數(shù)據(jù)庫(kù)的性能是完全可用的。

1 優(yōu)化方法

本文操作背景是一個(gè)地市級(jí)移動(dòng)基站設(shè)備性能的歷史數(shù)據(jù)庫(kù),經(jīng)常需要對(duì)歷史數(shù)據(jù)進(jìn)行查詢操作。性能數(shù)據(jù)的導(dǎo)入是通過(guò)SQL SERVER DTS(數(shù)據(jù)轉(zhuǎn)換服務(wù))定時(shí)任務(wù)來(lái)進(jìn)行。

1.1 索引與查詢優(yōu)化

索引相當(dāng)于書的目錄或者索引,對(duì)于查詢條件中常用的列一般需要建立索引,當(dāng)根據(jù)索引碼的值搜索數(shù)據(jù)時(shí),索引提供了對(duì)數(shù)據(jù)的快速訪問(wèn)[1]。

(1)主鍵索引

建立數(shù)據(jù)庫(kù)的時(shí)候,一般需要為每張表指定一個(gè)主鍵來(lái)唯一標(biāo)識(shí)表中某一行的屬性或?qū)傩越M,一個(gè)表只能有一個(gè)主鍵,主鍵還常與外鍵構(gòu)成參照完整性約束。

(2)聚集索引與非聚集索引

索引有兩種類型,聚集索引是對(duì)表的物理排序,相當(dāng)于書的目錄,每個(gè)表只能有一個(gè)聚集索引,而非聚集索引則相當(dāng)于書的索引,可以有多個(gè)[2]。

聚集索引有兩個(gè)最大的優(yōu)勢(shì):以最快的速度縮小查詢范圍和以最快的速度進(jìn)行字段排序。聚集索引對(duì)于那些經(jīng)常要搜索范圍值的列特別有效,使用聚集索引找到包含第一個(gè)值的行后,便可以確保包含后續(xù)索引值的行在物理相鄰。例如,如果應(yīng)用程序執(zhí)行的一個(gè)查詢經(jīng)常檢索某一日期范圍內(nèi)的記錄,則使用聚集索引可以迅速找到包含開始日期的行,然后檢索表中所有相鄰的行,直到到達(dá)結(jié)束日期,這樣有助于提高此類查詢的性能[3]。

聚集索引是重要和唯一的,因此要將聚集索引建立在最頻繁使用的、用以縮小查詢范圍的字段上或最頻繁使用的、需要排序的字段上等。查詢特定時(shí)期特定地區(qū)的歷史數(shù)據(jù),自然要對(duì)時(shí)間字段和空間字段進(jìn)行索引,按日期或者地區(qū)來(lái)建立聚集索引是好的選擇,后面的試驗(yàn)結(jié)果印證了這一點(diǎn)。

(3)查詢優(yōu)化器

SQL Server、Oracle這些企業(yè)級(jí)數(shù)據(jù)庫(kù)在執(zhí)行查詢之前,利用查詢優(yōu)化器來(lái)“智能地進(jìn)行分析”,自動(dòng)選擇最優(yōu)的查詢計(jì)劃。查詢優(yōu)化器檢查查詢條件是否滿足SARG約束,滿足稱之為可優(yōu)化的,并可以利用索引快速獲得所需數(shù)據(jù);如果查詢條件表達(dá)式不能滿足SARG的形式,索引這時(shí)候是無(wú)用的。值得提醒的是,雖然SQL查詢優(yōu)化器大部分時(shí)間工作性能好,但也有例外,必要的時(shí)候可以強(qiáng)制使用索引或者查詢計(jì)劃[4]。

(4)分頁(yè)存儲(chǔ)過(guò)程與索引

對(duì)于B/S類應(yīng)用,查詢結(jié)果展示依賴分頁(yè)的存儲(chǔ)過(guò)程,分頁(yè)的一個(gè)前提是分頁(yè)的列應(yīng)該是個(gè)唯一列。上面提到聚集索引是提高性能的關(guān)鍵之一,對(duì)于歷史數(shù)據(jù)聚集索引是日期,而日期是不唯一的,因此不能作為分頁(yè)列,這樣就產(chǎn)生了矛盾。解決的技巧是可以用日期列g(shù)etdate()作為輔助列,配置 UNIQUE約束,從而滿足分頁(yè)的需要,并且將此列作為聚集索引列。

1.2 其它優(yōu)化方法

1.冗余與緩存機(jī)制

根據(jù)空間換取時(shí)間的原則,在數(shù)據(jù)庫(kù)設(shè)計(jì)進(jìn)行優(yōu)化的時(shí)候,可以采用適當(dāng)?shù)娜哂嗷蛘呔彺鏅C(jī)制。索引也可以算是一種特殊的空間換取時(shí)間機(jī)制。

冗余的例子比如對(duì)于一些經(jīng)常要計(jì)算得到的列,可以增加一個(gè)冗余的計(jì)算字段,這樣除了生成的時(shí)候進(jìn)行一次計(jì)算,再查詢的時(shí)候就不需要重新計(jì)算,從而提高查詢速度。

對(duì)于某些性能瓶頸,可以考慮引入緩存或者中間表,緩存典型用法就是中間表,例如要分區(qū)域統(tǒng)計(jì)歷史性能數(shù)據(jù),如直接從歷史數(shù)據(jù)表實(shí)時(shí)進(jìn)行統(tǒng)計(jì)處理的話,需要很長(zhǎng)時(shí)間計(jì)算,可以設(shè)計(jì)一個(gè)統(tǒng)計(jì)結(jié)果中間表保存中間統(tǒng)計(jì)結(jié)果,即分區(qū)統(tǒng)計(jì)結(jié)果的歷史數(shù)據(jù)緩存,這樣進(jìn)行分區(qū)域統(tǒng)計(jì)的時(shí)候就可以直接存取這個(gè)中間表,避免性能問(wèn)題。還可以充分利用DTS/SSIS服務(wù)和SQL Server自動(dòng)化機(jī)制,后臺(tái)不斷的定時(shí)計(jì)算并生成這個(gè)中間結(jié)果。

2.數(shù)據(jù)分片與分區(qū)

除了表模式優(yōu)化、索引、內(nèi)部調(diào)校、冗余處理外,最有效的方法就是“分而治之”。實(shí)際操作中多是下面三種手段及其組合應(yīng)用:

(1)分散(分片)

根據(jù)數(shù)據(jù)的時(shí)間局部性和空間局部性原則,將海量數(shù)據(jù)表Sharding(分片)/分割成多個(gè)表,如按IP地址散列、按時(shí)間切割、按地理范圍等,解決數(shù)據(jù)庫(kù)擴(kuò)展性問(wèn)題,一般有兩種分片方法,即橫向分片和豎向分片。開源數(shù)據(jù)庫(kù)如MySQL廣泛使用數(shù)據(jù)庫(kù)分片技術(shù),用戶可以自己編寫程序來(lái)實(shí)現(xiàn),也可使用一些HSCALE等第三方分片軟件來(lái)實(shí)現(xiàn)。商業(yè)數(shù)據(jù)庫(kù)如Oracle、SQL Server等還內(nèi)置標(biāo)準(zhǔn)化的分片機(jī)制,如分區(qū)視圖[5],例如處理本文提到的基站設(shè)備性能歷史數(shù)據(jù)可以按時(shí)間分區(qū)或者地理分區(qū)。

另外將數(shù)據(jù)庫(kù)分成不同的文件和文件組,充分利用RAID及在多個(gè)驅(qū)動(dòng)器之間分配 I/O,也可以認(rèn)為是一種底層的分片思路,可以提高磁盤操作的尋道時(shí)間和訪問(wèn)速度。

⑵分布

多臺(tái)機(jī)器的分布式存儲(chǔ),著名的如Google的分布式存儲(chǔ)。

(3)分層(級(jí))

多級(jí)存儲(chǔ)訪問(wèn),如內(nèi)存文件系統(tǒng)、Memcache緩存和內(nèi)存數(shù)據(jù)庫(kù)等。

2 測(cè)試與性能分析

2.1 性能分析方法與工具

(1)性能計(jì)數(shù)器分析

在window s xp/2003 server中打開管理工具→性能,添加SQL性能計(jì)數(shù)器,其有很多SQL組,通常關(guān)注的有:用戶連接;鎖請(qǐng)求/秒,如發(fā)現(xiàn)鎖操作總體過(guò)大,應(yīng)該從應(yīng)用層面進(jìn)行分析優(yōu)化;完全掃描/秒,計(jì)數(shù)器指示有多少不使用索引而進(jìn)行的全表掃描,應(yīng)分析SQL查詢語(yǔ)句和數(shù)據(jù)庫(kù)索引的對(duì)應(yīng)關(guān)系,追加必要的索引以減少全表掃描的次數(shù)。

(2)通過(guò)使用SQL事件探查器和查詢分析器等工具對(duì)SQL Server內(nèi)部語(yǔ)句執(zhí)行的性能狀況列出明細(xì)表,并可將CPU占用較高的任務(wù)列出。

(3)通過(guò)SQL查詢分析器分析查詢的執(zhí)行計(jì)劃,找出性能瓶頸的SQL語(yǔ)句,進(jìn)行針對(duì)性優(yōu)化。

2.2 測(cè)試環(huán)境與測(cè)試項(xiàng)目

本文的測(cè)試平臺(tái)為:軟件系統(tǒng)是Windows 2003 server SP2,SQL Server2000 SP4企業(yè)版;硬件平臺(tái)為Xeon 5110,4GB內(nèi)存,1TB SATA硬盤。

(1)查詢時(shí)間計(jì)算

(2)回避查詢緩存的影響

SQL Server占用的內(nèi)存主要由三部分組成:數(shù)據(jù)緩存(Data Buffer)、執(zhí)行緩存(Procedure Cache)以及SQL Server引擎程序(所占用緩存一般相對(duì)變化不大)。查詢緩存會(huì)對(duì)測(cè)試有所影響,因此每次測(cè)試后要清理緩存,保證測(cè)試結(jié)果的客觀。

清除步驟如下:

CHECKPOINT--將當(dāng)前數(shù)據(jù)庫(kù)的全部臟頁(yè)寫入磁盤。

DBCC DROPCLEANBUFFERS--從緩沖池中刪除所有清除緩沖區(qū)。

DBCC FREEPROCCACHE--從過(guò)程緩存中刪除所有元素。

DBCC FREESYSTEMCACHE(＇ALL＇)——從所有緩存中釋放所有未使用的緩存條目

DBCC FLUSHPROCINDB({DBID}):清理指定數(shù)據(jù)庫(kù)實(shí)例中存儲(chǔ)過(guò)程使用的緩存。在測(cè)試時(shí)保證以前的存儲(chǔ)過(guò)程計(jì)劃不會(huì)對(duì)測(cè)試結(jié)果造成負(fù)面影響,可以使用這個(gè)存儲(chǔ)過(guò)程。

值得指出,清理緩存后,應(yīng)緊接著執(zhí)行查詢,因?yàn)镾QL Server會(huì)時(shí)刻自動(dòng)往緩存里讀入最需要的數(shù)據(jù)頁(yè)。

2.3 索引的測(cè)試

測(cè)試分三種情況:

(1)沒(méi)有任何索引;

(2)非聚集索引(在時(shí)間字段上建立一個(gè)非聚集索引);

(3)聚集索引(在時(shí)間字段上建立一個(gè)聚集索引)。

分別運(yùn)行如下測(cè)試樣例,每次測(cè)試前使用前述計(jì)算時(shí)間的算法并且清理緩存。

(1)查詢性能測(cè)試

測(cè)試樣例1:導(dǎo)出某一時(shí)段的歷史數(shù)據(jù),測(cè)試基本歷史數(shù)據(jù)查詢功能。

測(cè)試樣例2:歷史數(shù)據(jù)統(tǒng)計(jì)分析,測(cè)試聚合查詢功能。

測(cè)試結(jié)果數(shù)據(jù)如表1所示,繪制分析圖如圖1所示。

表1 查詢操作測(cè)試數(shù)據(jù)

從圖1容易看出,在實(shí)際測(cè)試中,在數(shù)據(jù)數(shù)量巨大的情況下,使用或不使用索引對(duì)查詢性能影響非常顯著,沒(méi)有使用索引之前,查詢一段歷史記錄需要3 min左右,使用非聚集索引一般就是在6～7 s,而使用聚集索引后基本就在2 s內(nèi),聚集索引比非聚集索引又有一定提升,尤其是使用聚合函數(shù)進(jìn)行統(tǒng)計(jì)分析查詢時(shí),性能提升十分顯著。

圖1 查詢性能圖

(2)Update/Insert/Delete性能測(cè)試

測(cè)試樣例3:測(cè)試Update/Insert/Delete單條記錄的性能。

測(cè)試結(jié)果數(shù)據(jù)如表2所示,繪制分析圖如圖2所示。

表2 Insert/Delete/Update操作測(cè)試數(shù)據(jù)

圖2 增/刪/改性能圖

從圖2看出,插入操作當(dāng)有索引時(shí)反而可能會(huì)降低速度,因?yàn)檫€增加了寫索引操作,而更新和刪除操作首先是要按條件檢索數(shù)據(jù),然后進(jìn)行更新操作,對(duì)于一個(gè)海量數(shù)據(jù)表來(lái)說(shuō),檢索是主要的消耗,因此這種情況下的更新和刪除操作,在有索引的時(shí)候性能有很大提升。

3 結(jié) 語(yǔ)

數(shù)據(jù)庫(kù)的發(fā)展趨勢(shì)是數(shù)據(jù)量越來(lái)越大,開發(fā)數(shù)據(jù)庫(kù)時(shí)對(duì)海量數(shù)據(jù),如歷史數(shù)據(jù)等,進(jìn)行分析和挖掘也越來(lái)越重要,利用SQL Server等企業(yè)級(jí)數(shù)據(jù)庫(kù)提供的特性,使用多種方式組合對(duì)表進(jìn)行優(yōu)化,如本文得出的一個(gè)結(jié)論是聚集索引非常關(guān)鍵,而一個(gè)表只能有一個(gè)聚集索引,應(yīng)該合理應(yīng)用。同時(shí)要善于利用數(shù)據(jù)庫(kù)的工具,如系統(tǒng)性能計(jì)數(shù)器、查詢分析器、SQL事件探查器等,有針對(duì)性地分析和調(diào)校性能。

1 Ryan K.Stephens,Ronald R.Plew.何玉潔等譯.數(shù)據(jù)庫(kù)設(shè)計(jì)[M].北京:機(jī)械工業(yè)出版社,2001,9.

2 Kalen Delaney.聶偉等譯.Microsoft SQ L Server 2005技術(shù)內(nèi)幕:存儲(chǔ)引擎[M].北京:電子工業(yè)出版社,2007,9.

3 胡百敬,姚巧玫,劉承修.SQL Server 2005 Performance Tuning性能調(diào)校[M].北京:電子工業(yè)出版社,2008,6.

4 Robert Vieira.董明等譯.SQL Server 2005高級(jí)程序設(shè)計(jì)[M].北京:人民郵電出版社,2008,4.

5 使用分區(qū)視圖.SQ L Server聯(lián)機(jī)叢書[EB/OL].http://technet.microsoft.com/zh-cn/library/ms190019.aspx