王 佳，王 智 森

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

0 引 言

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

1 表分區(qū)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

分區(qū)函數(shù)確定分區(qū)表要使用的鍵(字段)，以及各分區(qū)的邊界?？梢哉f分區(qū)函數(shù)確定了分區(qū)表的邏輯架構(gòu)，使用T－SQL語(yǔ)句創(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)建。對(duì)于分區(qū)表中分區(qū)邊界的歸屬問題，可見參考文獻(xiàn)[4]。

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

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

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

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

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

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

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

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

3 分區(qū)的管理

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

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

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

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

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

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

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

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

4 查詢效率提高的驗(yàn)證實(shí)驗(yàn)

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

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

系統(tǒng)的數(shù)據(jù)采集頻率高，時(shí)間長(zhǎng)，為加快查詢速度決定將SampleData表做分區(qū)處理，以SampleTime字段為分區(qū)字段，每個(gè)月的數(shù)據(jù)作為一個(gè)分區(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組對(duì)比測(cè)試。

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

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

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

表3 查詢時(shí)間對(duì)比一Tab.3 Query time comparison one

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

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

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

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

表5 查詢時(shí)間對(duì)比二Tab.5 Query time comparison two

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

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

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

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

5 結(jié) 論

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

[1]IBM：積極推進(jìn)“大 數(shù) 據(jù)”時(shí)代 革 新 [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ù)庫(kù)分區(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)幕：存儲(chǔ)引擎[M].北京：電子工業(yè)出版社，2007：203－205.