王 佳，王 智 森

(1.大連工業(yè)大學(xué) 實驗儀器中心，遼寧 大連 116034；2.大連工業(yè)大學(xué) 信息科學(xué)與工程學(xué)院，遼寧 大連 116034)

0 引 言

隨著大數(shù)據(jù)時代和云計算時代的到來，海量數(shù)據(jù)存儲和管理是人們亟待解決的問題，大型數(shù)據(jù)庫的存儲量通常都到達(dá)數(shù)百GB，有的甚至達(dá)到TB級[1]。其中數(shù)據(jù)庫中單表的數(shù)據(jù)記錄往往也達(dá)到上億條，而且記錄的條數(shù)會隨著時間不斷增長。這不但影響了數(shù)據(jù)庫的運行效率，同時增大了數(shù)據(jù)庫的維護(hù)難度。為了提高對大數(shù)據(jù)表的查詢和管理效率，傳統(tǒng)的關(guān)系型數(shù)據(jù)庫引入表分區(qū)的方法[2]。本文以SQL Server 2008為后臺數(shù)據(jù)庫，以某監(jiān)測系統(tǒng)為例對表分區(qū)方法做了介紹，并通過實驗驗證了表分區(qū)的優(yōu)化效果。

1 表分區(qū)

表分區(qū)是指在數(shù)據(jù)庫中將大表在物理上分成多個小表存儲，而在邏輯上仍然是一張表的分區(qū)方法。即表分區(qū)對程序員是透明的，開發(fā)人員不需要關(guān)心表在物理上是怎樣存儲的。在SQL Server中表分區(qū)分為水平分區(qū)和垂直分區(qū)，Oracle數(shù)據(jù)庫中還包括散列分區(qū)等不同的表分區(qū)方式[3]。其中水平分區(qū)是指將一個表分為多個表，每個表包含的列數(shù)相同，但是行數(shù)更少。垂直分區(qū)是指將表分為包含更少列的表，而各表的行數(shù)相同的分區(qū)方法。

通過對大表做分區(qū)，當(dāng)查詢某一分區(qū)中的對象時，僅需要掃描對象所在分區(qū)，這無形中提高了對數(shù)據(jù)的檢索效率。例如當(dāng)對一個數(shù)據(jù)記錄數(shù)達(dá)上億條的數(shù)據(jù)表按月進(jìn)行分區(qū)后，對特定月份的查詢，只需要掃描一個上千萬的表，大大縮小了查詢范圍。圖1為表分區(qū)的示意圖，如果系統(tǒng)是多CPU或多磁盤系統(tǒng)，將不同的分區(qū)放置在不同的磁盤上，通過并行操作提高了系統(tǒng)響應(yīng)性能，同時能使I／O均衡，提高系統(tǒng)吞吐量?？梢砸苑謪^(qū)為單位對數(shù)據(jù)做移動和備份操作，某一分區(qū)出現(xiàn)故障，不會影響其他的分區(qū)，只需對故障分區(qū)作恢復(fù)即可，使數(shù)據(jù)的維護(hù)和管理更加方便。

圖1 表分區(qū)示意圖Fig.1 Schematic plan of partitioned table

2 表分區(qū)的實現(xiàn)方法

不同的數(shù)據(jù)庫實現(xiàn)分區(qū)表的方法各不相同，本節(jié)以SQL Server 2008為例說明了分區(qū)表的實現(xiàn)過程。

2.1 SQL Server數(shù)據(jù)庫數(shù)據(jù)文件構(gòu)成

SQL Server數(shù)據(jù)庫中包含3種類型的文件：

(1)主數(shù)據(jù)文件：每個數(shù)據(jù)庫中都有一個主數(shù)據(jù)文件，主要用于存儲與系統(tǒng)相關(guān)的信息，還可以跟蹤數(shù)據(jù)庫中的其他文件。通常擴(kuò)展名為.mdf。

(2)輔助數(shù)據(jù)文件：一個數(shù)據(jù)庫中有0到多個輔助數(shù)據(jù)文件，只用于存儲用戶數(shù)據(jù)。文件擴(kuò)展名.ndf。

(3)日志文件：每個數(shù)據(jù)庫中至少有一個日志文件，其包含恢復(fù)數(shù)據(jù)庫中所有事務(wù)所需的信息。日志文件任何不屬于文件組，單獨管理其文件擴(kuò)展名.idf。

出于分配和管理的目的將數(shù)據(jù)庫文件分為多個組，其中包含主數(shù)據(jù)文件的文件組被稱為主文件組，一個數(shù)據(jù)庫中只有一個主文件組，除了主文件組外數(shù)據(jù)庫中還可以有一個或多個用戶定義的文件組，以上結(jié)構(gòu)可以參考圖2。

圖2 數(shù)據(jù)庫的文件構(gòu)成Fig.2 Files in a database

2.2 表分區(qū)的實現(xiàn)

創(chuàng)建分區(qū)依次按如下幾個步驟操作：

(1)首先要對表分區(qū)有一個整體的規(guī)劃，確定分區(qū)字段、分區(qū)個數(shù)等。

(2)創(chuàng)建分區(qū)函數(shù)

分區(qū)函數(shù)確定分區(qū)表要使用的鍵(字段)，以及各分區(qū)的邊界?？梢哉f分區(qū)函數(shù)確定了分區(qū)表的邏輯架構(gòu)，使用T－SQL語句創(chuàng)建分區(qū)函數(shù)：

CREATE FUNCTION Partition＿Function－Name(ParameterType)AS RANGE [LEFT／RIGHT]FOR VALUES([boundary＿value[，…..n]])。

分區(qū)函數(shù)雖然也是用戶自定義的函數(shù)，但是它不同于普通的用戶自定義的函數(shù)，分區(qū)函數(shù)只用于分區(qū)表的創(chuàng)建。對于分區(qū)表中分區(qū)邊界的歸屬問題，可見參考文獻(xiàn)[4]。

(3)創(chuàng)建分區(qū)架構(gòu)(分區(qū)方案)

分區(qū)架構(gòu)將每個分區(qū)映射到具體的文件組中，規(guī)定了分區(qū)表的物理結(jié)構(gòu)實現(xiàn)邏輯結(jié)構(gòu)到物理結(jié)構(gòu)的映射，生成分區(qū)架構(gòu)的語句：

CREATE PARTITION SCHEME partition＿scheme＿name AS PARTITION partition＿name TO (file＿group＿name，…)。

這里可以選擇將所有的分區(qū)都映射到一個文件組中，也可以將多個分區(qū)映射到一個文件組中，最好是將不同分區(qū)表映射到不同的文件組中，這樣可以達(dá)到I／O的均衡。分區(qū)函數(shù)和分區(qū)架構(gòu)是分區(qū)表要依賴的兩個對象，這兩個對象只用于分區(qū)表和分區(qū)索引。它們之間的依賴關(guān)系如圖3所示[4]。

圖3 分區(qū)表依賴關(guān)系Fig.3 Partitioned table dependencies

(4)創(chuàng)建分區(qū)表或索引

要在創(chuàng)建對象時指定要使用的分區(qū)方案，就可以創(chuàng)建分區(qū)表或分區(qū)索引了，使用T－SQL語句：

CREATE TABLE table＿name(columns，...)on partition＿scheme＿name(partition＿key)。

3 分區(qū)的管理

使用表分區(qū)方便了數(shù)據(jù)的維護(hù)和管理，對大表而言可以以表分區(qū)為單位操作表中的數(shù)據(jù)。本節(jié)介紹了2種基本分區(qū)表操作。對分區(qū)的操作，是通過改變分區(qū)函數(shù)實現(xiàn)的，分區(qū)函數(shù)改變后，改變通過分區(qū)架構(gòu)傳遞到分區(qū)表中。

(1)拆分分區(qū)－添加分區(qū)，使用對應(yīng)的 T－SQL語句：

ALTER PARTITION FUNCTION function＿name()SPLIT RANGE(rangeValue)。

(2)合并分區(qū)－刪除分區(qū)，合并現(xiàn)有的分區(qū)中的兩個，對應(yīng)的T－SQL語句：

ALTER PARTITION FUNCTION function＿name()MERGE RANGE(rangeValue)。

對分區(qū)的拆分和合并只需要修改分區(qū)函數(shù)，但是新的分區(qū)要映射到哪個文件組上，需要由分區(qū)架構(gòu)通過NEXT USED指定T－SQL語句：

ALTER PARTITION SCHEME partition＿scheme＿name NEXT USED filegroup＿name。

分區(qū)的拆分與合并主要用于數(shù)據(jù)的轉(zhuǎn)移備份。SQL Server通過一種基于元數(shù)據(jù)的操作把數(shù)據(jù)從一個分區(qū)移到另一個分區(qū)，在這個移動過程中數(shù)據(jù)本身不需要移動。數(shù)據(jù)移動在很短的時間完成，對系統(tǒng)產(chǎn)生的影響很小。用戶可以對分區(qū)數(shù)據(jù)做如下操作：①將一張表作為一個分區(qū)轉(zhuǎn)移到另一個已存在的分區(qū)表中；②將一個分區(qū)從一張分區(qū)表轉(zhuǎn)移到另一張分區(qū)表上；③重新指派一個分區(qū)以形成一張單表[5]。

4 查詢效率提高的驗證實驗

本節(jié)結(jié)合具體的應(yīng)用系統(tǒng)，對系統(tǒng)的中大表做了分區(qū)優(yōu)化。在某監(jiān)控系統(tǒng)中，系統(tǒng)采集的監(jiān)控數(shù)據(jù)存儲在SampleData表中，其表結(jié)構(gòu)如表1所示。

表1 SampleData表結(jié)構(gòu)Tab.1 SampleData table

系統(tǒng)的數(shù)據(jù)采集頻率高，時間長，為加快查詢速度決定將SampleData表做分區(qū)處理，以SampleTime字段為分區(qū)字段，每個月的數(shù)據(jù)作為一個分區(qū)。

分區(qū)函數(shù)：

CREATE PARTITION FUNCTION pf(datetime)RANGE RIGHT AS RANGE FOR(‘20100601 00：00：00’，‘20100701 00：00：00’，….，‘20110401 00：00：00’)。

分區(qū)架構(gòu)：

CREATE PARTITION SCHEME ps AS PARTITION pf TO([PRIMARY]，F(xiàn)G1，F(xiàn)G2，F(xiàn)G3)。

生成分區(qū)表：

CREATE TABLE SampleData(DataID，TrackID，DataValue，SampleTime)on ps(SampleTime)。

表的分區(qū)如圖4所示：

圖4 SampleData表分區(qū)示意圖Fig.4 The partitioned table of SampleData

本文模擬系統(tǒng)數(shù)據(jù)，分別做了2組對比測試。

(1)4個分區(qū)的分區(qū)表，分區(qū)信息如表2所示。

表2 分區(qū)表信息一Tab.2 Partitioned table info one

做了8組查詢對比，對比試驗的結(jié)果如表3所示。

表3 查詢時間對比一Tab.3 Query time comparison one

從表3中每組對比試驗的查詢時間可知，分區(qū)表中的查詢速度明顯快于未分區(qū)表。

(2)4個分區(qū)的分區(qū)表，數(shù)據(jù)總量10G，分區(qū)信息如表4所示。

表4 分區(qū)表信息二Tab.4 Partitioned table info two

分別查詢1、2、3、6、13、15d的數(shù)據(jù)量，查詢所用時間如表5所示。

表5 查詢時間對比二Tab.5 Query time comparison two

根據(jù)每次查詢的數(shù)據(jù)總量與相應(yīng)的查詢時間比可以得到圖5。

圖5 不同數(shù)據(jù)總量單位時間查詢量Fig.5 Query data amount per unit of different data

通過對圖5的分析可得，對表做分區(qū)后單位時間的查詢效率有顯著的提高。隨著查詢數(shù)據(jù)量的增加，分區(qū)后查詢效率也隨之增加，但是當(dāng)數(shù)據(jù)量到達(dá)一定量時，查詢效率突然下降，主要是由于硬件條件的限制導(dǎo)致的。

常年采集數(shù)據(jù)的監(jiān)測系統(tǒng)會產(chǎn)生大量數(shù)據(jù)。大多數(shù)情況下，只需要對最近3年的數(shù)據(jù)進(jìn)行查詢，所以對數(shù)據(jù)庫按照時間進(jìn)行分區(qū)操作，隨著時間的增加，利用表分區(qū)的拆分、合并和移動，始終保持3年的數(shù)據(jù)。同時表分區(qū)處理數(shù)據(jù)拆分、合并和移動操作只是元數(shù)據(jù)的操作，避免了數(shù)據(jù)被移動到只讀表或從只讀表中刪除而造成的時間延遲、空間占用。

5 結(jié) 論

表分區(qū)方法作為數(shù)據(jù)庫中處理海量數(shù)據(jù)的一種重要方法，提高了大表的查找和管理效率。文章介紹了分區(qū)表的原理和實現(xiàn)方法，并進(jìn)行了對比測試。測試結(jié)果表明，使用分區(qū)表的方法能夠減小數(shù)據(jù)查找的范圍提高查詢速率，同時能夠均衡I／O，提高系統(tǒng)數(shù)據(jù)即時吞吐量。

[1]IBM：積極推進(jìn)“大 數(shù) 據(jù)”時代 革 新 [J].硅 谷，2011(22)：116－117.

[2]Microsoft Developer Network.Partitioned table and index[EB／OL].

[2013－03－11].http：／／msdn.microsoft.com／en－us／library／ms190787.aspx.

[3]蔣勇.ORACLE數(shù)據(jù)庫分區(qū)技術(shù)及其應(yīng)用[J].科技信息，2011(29)：49－50.

[4]TALMAGE R.Partitioned table and index strategies using SQL Server 2008[EB／OL].

[2013－03－11].http：／／download.microsoft.com／download／D／B／D／DBDE7972－1EB9－470A－BA18－58849DB3EB3B／Part－TableAndIndexStrat.docx.

[5]DELANEY K.Microsoft SQL Server 2005技術(shù)內(nèi)幕：存儲引擎[M].北京：電子工業(yè)出版社，2007：203－205.