SQL Server大數(shù)據(jù)量數(shù)據(jù)庫性能優(yōu)化初探

吳 綱

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

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

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

1 優(yōu)化方法

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

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

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

(1)主鍵索引

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

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

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

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

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

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

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

(4)分頁存儲過程與索引

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

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

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

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

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

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

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

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

(1)分散(分片)

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

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

⑵分布

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

(3)分層(級)

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

2 測試與性能分析

2.1 性能分析方法與工具

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

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

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

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

2.2 測試環(huán)境與測試項目

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

(1)查詢時間計算

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

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

清除步驟如下:

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

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

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

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

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

值得指出,清理緩存后,應(yīng)緊接著執(zhí)行查詢,因為SQL Server會時刻自動往緩存里讀入最需要的數(shù)據(jù)頁。

2.3 索引的測試

測試分三種情況:

(1)沒有任何索引;

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

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

分別運行如下測試樣例,每次測試前使用前述計算時間的算法并且清理緩存。

(1)查詢性能測試

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

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

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

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

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

圖1 查詢性能圖

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

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

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

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

圖2 增/刪/改性能圖

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

3 結(jié) 語

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

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

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

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

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

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