超大型數(shù)據(jù)庫(kù)的大小常常達(dá)到數(shù)百GB，有時(shí)甚至要用TB來(lái)計(jì)算。而單表的數(shù)據(jù)量往往會(huì)達(dá)到上億的記錄，并且記錄數(shù)會(huì)隨著時(shí)間而增長(zhǎng)。這不但影響著數(shù)據(jù)庫(kù)的運(yùn)行效率，也增大數(shù)據(jù)庫(kù)的維護(hù)難度。除了表的數(shù)據(jù)量外，對(duì)表不同的訪問(wèn)模式也可能會(huì)影響性能和可用性。這些問(wèn)題都可以通過(guò)對(duì)大表進(jìn)行合理分區(qū)得到很大的改善。當(dāng)表和索引變得非常大時(shí)，分區(qū)可以將數(shù)據(jù)分為更小、更容易管理的部分來(lái)提高系統(tǒng)的運(yùn)行效率。如果系統(tǒng)有多個(gè)CPU或是多個(gè)磁盤(pán)子系統(tǒng)，可以通過(guò)并行操作獲得更好的性能。所以對(duì)大表進(jìn)行分區(qū)是處理海量數(shù)據(jù)的一種十分高效的方法。本文通過(guò)一個(gè)具體實(shí)例，介紹如何創(chuàng)建和修改分區(qū)表，以及如何查看分區(qū)表。

1 SQL Server 2005

SQL Server 2005是微軟在推出SQL Server 2000后時(shí)隔五年推出的一個(gè)數(shù)據(jù)庫(kù)平臺(tái)，它的數(shù)據(jù)庫(kù)引擎為關(guān)系型數(shù)據(jù)和結(jié)構(gòu)化數(shù)據(jù)提供了更安全可靠的存儲(chǔ)功能，使用戶可以構(gòu)建和管理用于業(yè)務(wù)的高可用和高性能的數(shù)據(jù)應(yīng)用程序。此外SQL Server 2005結(jié)合了分析、報(bào)表、集成和通知功能。這使企業(yè)可以構(gòu)建和部署經(jīng)濟(jì)有效的BI解決方案，幫助團(tuán)隊(duì)通過(guò)記分卡、Dashboard、Web Services和移動(dòng)設(shè)備將數(shù)據(jù)應(yīng)用推向業(yè)務(wù)的各個(gè)領(lǐng)域。無(wú)論是開(kāi)發(fā)人員、數(shù)據(jù)庫(kù)管理員、信息工作者還是決策者，SQL Server 2005都可以提供出創(chuàng)新的解決方案，并可從數(shù)據(jù)中獲得更多的益處。

它所帶來(lái)的新特性，如T-SQL的增強(qiáng)、數(shù)據(jù)分區(qū)、服務(wù)代理和與.Net Framework的集成等，在易管理性、可用性、可伸縮性和安全性等方面都有很大的增強(qiáng)。

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

表分區(qū)分為水平分區(qū)和垂直分區(qū)。水平分區(qū)將表分為多個(gè)表。每個(gè)表包含的列數(shù)相同，但是行更少。例如，可以將一個(gè)包含十億行的表水平分區(qū)成 12 個(gè)表，每個(gè)小表表示特定年份內(nèi)一個(gè)月的數(shù)據(jù)。任何需要特定月份數(shù)據(jù)的查詢只需引用相應(yīng)月份的表。而垂直分區(qū)則是將原始表分成多個(gè)只包含較少列的表。水平分區(qū)是最常用分區(qū)方式，本文以水平分區(qū)來(lái)介紹具體實(shí)現(xiàn)方法。

水平分區(qū)常用的方法是根據(jù)時(shí)期和使用對(duì)數(shù)據(jù)進(jìn)行水平分區(qū)。例如本文例子，一個(gè)短信發(fā)送記錄表包含最近一年的數(shù)據(jù)，但是只定期訪問(wèn)本季度的數(shù)據(jù)。在這種情況下，可考慮將數(shù)據(jù)分成四個(gè)區(qū)，每個(gè)區(qū)只包含一個(gè)季度的數(shù)據(jù)。

2.1 創(chuàng)建文件組

建立分區(qū)表先要?jiǎng)?chuàng)建文件組，而創(chuàng)建多個(gè)文件組主要是為了獲得好的 I/O 平衡。一般情況下，文件組數(shù)最好與分區(qū)數(shù)相同，并且這些文件組通常位于不同的磁盤(pán)上。每個(gè)文件組可以由一個(gè)或多個(gè)文件構(gòu)成，而每個(gè)分區(qū)必須映射到一個(gè)文件組。一個(gè)文件組可以由多個(gè)分區(qū)使用。為了更好地管理數(shù)據(jù)(例如，為了獲得更精確的備份控制)，對(duì)分區(qū)表應(yīng)進(jìn)行設(shè)計(jì)，以便只有相關(guān)數(shù)據(jù)或邏輯分組的數(shù)據(jù)位于同一個(gè)文件組中。使用 ALTER DATABASE，添加邏輯文件組名：

ALTER DATABASE [DeanDB] ADD FILEGROUP [FG1]

DeanDB為數(shù)據(jù)庫(kù)名稱(chēng)，F(xiàn)G1文件組名。創(chuàng)建文件組后，再使用 ALTER DATABASE 將文件添加到該文件組中：

ALTER DATABASE [DeanDB] ADD FILE ( NAME = N'FG1', FILENAME = N'C:DeanDataFG1.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG1]

類(lèi)似的建立四個(gè)文件和文件組，并把每一個(gè)存儲(chǔ)數(shù)據(jù)的文件放在不同的磁盤(pán)驅(qū)動(dòng)器里。2.2 創(chuàng)建分區(qū)函數(shù)

創(chuàng)建分區(qū)表必須先確定分區(qū)的功能機(jī)制，表進(jìn)行分區(qū)的標(biāo)準(zhǔn)是通過(guò)分區(qū)函數(shù)來(lái)決定的。創(chuàng)建數(shù)據(jù)分區(qū)函數(shù)有RANGE “LEFT | / RIGHT”兩種選擇。代表每個(gè)邊界值在局部的哪一邊。例如存在四個(gè)分區(qū)，則定義三個(gè)邊界點(diǎn)值，并指定每個(gè)值是第一個(gè)分區(qū)的上邊界 (LEFT) 還是第二個(gè)分區(qū)的下邊界 (RIGHT)[1]。代碼如下：

CREATE PARTITION FUNCTION [SendSMSPF](datetime)　AS RANGE RIGHT FOR VALUES ('20070401', '20070701', '20071001')

2.3 創(chuàng)建分區(qū)方案

創(chuàng)建分區(qū)函數(shù)后，必須將其與分區(qū)方案相關(guān)聯(lián)，以便將分區(qū)指向至特定的文件組。就是定義實(shí)際存放數(shù)據(jù)的媒體與各數(shù)據(jù)塊的對(duì)應(yīng)關(guān)系。多個(gè)數(shù)據(jù)表可以共用相同的數(shù)據(jù)分區(qū)函數(shù)，一般不共用相同的數(shù)據(jù)分區(qū)方案。可以通過(guò)不同的分區(qū)方案，使用相同的分區(qū)函數(shù)，使不同的數(shù)據(jù)表有相同的分區(qū)條件，但存放在不同的媒介上。創(chuàng)建分區(qū)方案的代碼如下：

CREATE PARTITION SCHEME [SendSMSPS] AS PARTITION [SendSMSPF] TO ([FG1], [FG2], [FG3], [FG4])

2.4 創(chuàng)建分區(qū)表

建立好分區(qū)函數(shù)和分區(qū)方案后，就可以創(chuàng)建分區(qū)表了。分區(qū)表是通過(guò)定義分區(qū)鍵值和分區(qū)方案相聯(lián)系的。插入記錄時(shí)，SQL SERVER會(huì)根據(jù)分區(qū)鍵值的不同，通過(guò)分區(qū)函數(shù)的定義將數(shù)據(jù)放到相應(yīng)的分區(qū)。從而把分區(qū)函數(shù)、分區(qū)方案和分區(qū)表三者有機(jī)的結(jié)合起來(lái)。創(chuàng)建分區(qū)表的代碼如下：

CREATE TABLE SendSMSLog

([ID] [int] IDENTITY(1,1) NOT NULL,

[IDNum] [nvarchar](50) NULL,

[SendContent] [text] NULL

[SendDate] [datetime] NOT NULL,

) ON SendSMSPS(SendDate)

2.5 查看分區(qū)表信息

系統(tǒng)運(yùn)行一段時(shí)間或者把以前的數(shù)據(jù)導(dǎo)入分區(qū)表后，我們需要查看數(shù)據(jù)的具體存儲(chǔ)情況，即每個(gè)分區(qū)存取的記錄數(shù)，那些記錄存取在那個(gè)分區(qū)等。我們可以通過(guò)$partition.SendSMSPF來(lái)查看，代碼如下：

SELECT $partition.SendSMSPF(o.SendDate)

AS [Partition Number]

, min(o.SendDate) AS [Min SendDate]

, max(o.SendDate) AS [Max SendDate]

, count(*) AS [Rows In Partition]

FROM dbo.SendSMSLog AS o

GROUP BY $partition.SendSMSPF(o.SendDate)

ORDER BY [Partition Number]

在查詢分析器里執(zhí)行以上腳本，結(jié)果如圖1所示：

圖1　分區(qū)表信息

2.6 維護(hù)分區(qū)

分區(qū)的維護(hù)主要設(shè)計(jì)分區(qū)的添加、減少、合并和在分區(qū)間轉(zhuǎn)換。可以通過(guò)ALTER PARTITION FUNCTION的選項(xiàng)SPLIT，MERGE和ALTER TABLE的選項(xiàng)SWITCH來(lái)實(shí)現(xiàn)。SPLIT會(huì)多增加一個(gè)分區(qū)，而MEGRE會(huì)合并或者減少分區(qū)，SWITCH則是邏輯地在組間轉(zhuǎn)換分區(qū)。

3 性能對(duì)比

我們對(duì)2650萬(wàn)數(shù)據(jù)，存儲(chǔ)空間占用約4G的單表進(jìn)行性能對(duì)比，測(cè)試環(huán)境為IBM365，CPU 至強(qiáng)2.7G*2、內(nèi)存 16G、硬盤(pán) 136G*2，系統(tǒng)平臺(tái)為Windows 2003 SP1+SQL Server 2005 SP1。測(cè)試結(jié)果如表1：

表1：分區(qū)和未分區(qū)性能對(duì)比表(單位：毫秒)

測(cè)試項(xiàng)目 分區(qū) 未分區(qū)

1 16546 61466

2 13 33

3 20140 61546

4 17140 61000

說(shuō)明：

1：根據(jù)時(shí)間檢索某一天記錄所耗時(shí)間

2：?jiǎn)螚l記錄插入所耗時(shí)間

3：根據(jù)時(shí)間刪除某一天記錄所耗時(shí)間

4：統(tǒng)計(jì)每月的記錄數(shù)所需時(shí)間

從表1可以看出，對(duì)分區(qū)表進(jìn)行操作比未分區(qū)的表要快，這是因?yàn)閷?duì)分區(qū)表的操作采用了CPU和I/O的并行操作，檢索數(shù)據(jù)的數(shù)據(jù)量也變小了，定位數(shù)據(jù)所耗時(shí)間變短。

4 結(jié)束語(yǔ)

對(duì)海量數(shù)據(jù)的處理一直是一個(gè)令人頭痛的問(wèn)題。分離的技術(shù)是所有設(shè)計(jì)者們首先考慮的問(wèn)題，不管是分離應(yīng)用程序功能還是分離數(shù)據(jù)訪問(wèn)，如果加以了合理規(guī)劃，都能十分有效的解決大數(shù)據(jù)表的運(yùn)行效率低和維護(hù)成本高等問(wèn)題。SQL Server 2005新增的表分區(qū)功能，可以對(duì)數(shù)據(jù)進(jìn)行合理分區(qū)，當(dāng)用戶在訪問(wèn)部分?jǐn)?shù)據(jù)時(shí)，SQL Server最佳化引擎可以根據(jù)數(shù)據(jù)的實(shí)體存放，找出最佳的執(zhí)行方案，而不至于大海撈針。