SQL Server 分區表 處理海量資料

你是不是也在苦苦尋求最佳化自己的SQL Server資料庫的方法?如果你的資料庫裡有不少非常大的表格，資料分割函數能夠幫到你很大的忙……

　　【IT專家網獨家】你是不是也在苦苦尋求最佳化自己的SQL Server資料庫的方法?如果你的資料庫裡有不少非常大的表格，資料分割函數能夠幫到你很大的忙，因為它可以把這些大表格分割成獨立檔案組。這個技術可以讓你把資料分布在不同的物理磁碟中，並通過調節它們的並行效能來最佳化你的查詢效能。

　　對SQL Server資料表進行分區的過程分為三個步驟：

　　1)建立分區函數

　　2)建立資料分割配置

　　3)對錶格進行分區

　　第一個步驟：建立分區函數

　　分區函數定義[u]how[/u]，即你想要SQL Server如何對資料進行分區。這裡就不以某一個表格作為例子，而是總體概括分割資料的技術。

　　分區是通過指定每個分區的分割界線實現的。例如，假定我們有一個Customers表格，裡麵包含了企業所有的客戶的資訊，客戶資訊以唯一的客戶號進行辨識，客戶號從1到1000000。我們可以運用以下的分區函數(這裡稱之為customer_Partfunc)把這個表格平均分為四個分區：

CREATE PARTITION FUNCTION customer_partfunc (int) 　　AS RANGE RIGHT 　　FOR VALUES (250000, 500000, 750000)

　　這些分割界線指定了四個分區。第一個分區包含所有值小於250000的記錄。第二個分區包含所有值在250000和499999之間的記錄。而第三個分區包含所有值在500000和749999之間的記錄。其他所有大於或等於750000的記錄都包含在第四個分區裡。

　　注意這個例子中使用了“RANGE RIGHT”從句。這說明分界值是在分區的右邊。同樣，如果使用的是“RANGE LEFT”從句，那麼第一個分區就會包含所有值小於或等於250000的記錄;第二個分區就會包含所有值在250001和500000之間的記錄，如此類推。

　　第二個步驟：建立資料分割配置

　　一旦建立完定義如何對資料進行分區的分區函數之後，下一步就是建立一個資料分割配置，定義[u]where[/u]，即你想在哪裡對資料進行分區。這是一個很直接明了的過程，例如，如果我有四個檔案組，名稱分別從“fg1”到“fg4”，那麼就可以使用以下資料分割配置：

CREATE PARTITION SCHEME customer_partscheme 　　AS PARTITION customer_partfunc 　　TO (fg1, fg2, fg3, fg4)

　　注意我們現在把一個分區函數串連到了資料分割配置，但是我們還沒有把資料分割配置串連到任何具體的資料庫表格。這就是重複使用功能發揮功能的時候。我們可以通過這個功能把資料分割配置(或者只是分區函數)用於資料庫表格的任何資料上。

　　第三個步驟：對錶格進行分區

　　建立好資料分割配置之後，就可以開始對錶格進行分區了。這是最簡單的一個步驟，只需要在表格建立語句中添加“ON”從句，指定表格資料分割配置和要應用該資料分割配置的表列。你不需要指定分區函數，因為資料分割配置已經定義了分區函數。

　　舉個例子，假設你想要用上述的資料分割配置來建立一個客戶表格，你需要使用以下Transact-SQL語句：

CREATE TABLE customers (FirstName nvarchar(40), LastName nvarchar(40), CustomerNumber int) 　　ON customer_partscheme (CustomerNumber)

超大型資料庫的大小常常達到數百GB，有時甚至要用TB來計算。而單表的資料量往往會達到上億的記錄，並且記錄數會隨著時間而增長。這不但影響著資料庫的運行效率，也增大資料庫的維護難度。除了表的資料量外，對錶不同的訪問模式也可能會影響效能和可用性。這些問題都可以通過對大表進行合理分區得到很大的改善。當表和索引變得非常大時，分區可以將資料分為更小、更容易管理的部分來提高系統的運行效率。如果系統有多個CPU或是多個磁碟子系統，可以通過並行操作獲得更好的效能。所以對大表進行分區是處理海量資料的一種十分高效的方法。本文通過一個具體執行個體，介紹如何建立和修改分區表，以及如何查看分區表。

1    SQL Server 2005

　　<strong class="kgb" onmouseover="isShowAds = false;isShowAds2 = false;isShowGg = true;InTextAds_GgLayer="SQL_20Server";KeyGate_ads.ShowGgAds(this,"SQL_20Server",event)" style="border-right: 0px; padding-right: 0px; border-top: 0px; padding-left: 0px; font-weight: normal; padding-bottom: 0px; margin: 0px; border-left: 0px; cursor: hand; color: #0000ff; padding-top: 0px; border-bottom: 0px; text-decoration: underline" onclick="javascript:window.open("http://pagead2.googlesyndication.com/pagead/iclk?sa=l&ai=BDTQhftmkSOC3NI3-vAOPvKjiB7bmnEG21sHaBcCNtwHAuAIQARgBIKy8kQooFDgAUMKCtur9_____wFgnaHfgdgFsgEPd3d3LmNuYmxvZ3MuY29tyAEB2gE7aHR0cDovL3d3dy5jbmJsb2dzLmNvbS8xMzU5MC9hcmNoaXZlLzIwMDcvMDcvMDkvODEwNzcwLmh0bWypAsAaeNpXkIM-yAKAi_ADqAMB6APXAugDjAPoAw3oAwXoAwOIBAGQBAGYBAA&num=1&adurl=http://www.ebackup.com.cn/shujukupro.asp&client=ca-pub-1681215984289622");GgKwClickStat("SQL Server","www.ebackup.com.cn","afc","2000072864");" onmouseout="isShowGg = false;InTextAds_GgLayer="SQL_20Server"">SQL Server 2005是微軟在推出SQL Server 2000後時隔五年推出的一個<strong class="kgb" onmouseover="isShowAds = false;isShowAds2 = false;isShowGg = true;InTextAds_GgLayer="_u6570_u636E_u5E93";KeyGate_ads.ShowGgAds(this,"_u6570_u636E_u5E93",event)" style="border-right: 0px; padding-right: 0px; border-top: 0px; padding-left: 0px; font-weight: normal; padding-bottom: 0px; margin: 0px; border-left: 0px; cursor: hand; color: #0000ff; padding-top: 0px; border-bottom: 0px; text-decoration: underline" onclick="javascript:window.open("http://pagead2.googlesyndication.com/pagead/iclk?sa=l&ai=BDTQhftmkSOC3NI3-vAOPvKjiB7bmnEG21sHaBcCNtwHAuAIQARgBIKy8kQooFDgAUMKCtur9_____wFgnaHfgdgFsgEPd3d3LmNuYmxvZ3MuY29tyAEB2gE7aHR0cDovL3d3dy5jbmJsb2dzLmNvbS8xMzU5MC9hcmNoaXZlLzIwMDcvMDcvMDkvODEwNzcwLmh0bWypAsAaeNpXkIM-yAKAi_ADqAMB6APXAugDjAPoAw3oAwXoAwOIBAGQBAGYBAA&num=1&adurl=http://www.ebackup.com.cn/shujukupro.asp&client=ca-pub-1681215984289622");GgKwClickStat("資料庫","www.ebackup.com.cn","afc","2000072864");" onmouseout="isShowGg = false;InTextAds_GgLayer="_u6570_u636E_u5E93"">資料庫平台，它的<strong class="kgb" onmouseover="isShowAds = false;isShowAds2 = false;isShowGg = true;InTextAds_GgLayer="_u6570_u636E";KeyGate_ads.ShowGgAds(this,"_u6570_u636E",event)" style="border-right: 0px; padding-right: 0px; border-top: 0px; padding-left: 0px; font-weight: normal; padding-bottom: 0px; margin: 0px; border-left: 0px; cursor: hand; color: #0000ff; padding-top: 0px; border-bottom: 0px; text-decoration: underline" onclick="javascript:window.open("http://pagead2.googlesyndication.com/pagead/iclk?sa=l&ai=BDTQhftmkSOC3NI3-vAOPvKjiB7bmnEG21sHaBcCNtwHAuAIQARgBIKy8kQooFDgAUMKCtur9_____wFgnaHfgdgFsgEPd3d3LmNuYmxvZ3MuY29tyAEB2gE7aHR0cDovL3d3dy5jbmJsb2dzLmNvbS8xMzU5MC9hcmNoaXZlLzIwMDcvMDcvMDkvODEwNzcwLmh0bWypAsAaeNpXkIM-yAKAi_ADqAMB6APXAugDjAPoAw3oAwXoAwOIBAGQBAGYBAA&num=1&adurl=http://www.ebackup.com.cn/shujukupro.asp&client=ca-pub-1681215984289622");GgKwClickStat("資料","www.ebackup.com.cn","afc","2000072864");" onmouseout="isShowGg = false;InTextAds_GgLayer="_u6570_u636E"">資料庫引擎為關係型資料和結構化資料提供了更安全可靠的儲存功能，使<strong class="kgb" onmouseover="isShowAds = false;isShowAds2 = false;isShowGg = true;InTextAds_GgLayer="_u7528_u6237";KeyGate_ads.ShowGgAds(this,"_u7528_u6237",event)" style="border-right: 0px; padding-right: 0px; border-top: 0px; padding-left: 0px; font-weight: normal; padding-bottom: 0px; margin: 0px; border-left: 0px; cursor: hand; color: #0000ff; padding-top: 0px; border-bottom: 0px; text-decoration: underline" onclick="javascript:window.open("http://pagead2.googlesyndication.com/pagead/iclk?sa=l&ai=BVtzvftmkSOC3NI3-vAOPvKjiB7TW-0247cDxBsCNtwGgnAEQAxgDIKy8kQooFDgAUIXNkPz______wFgnaHfgdgFsgEPd3d3LmNuYmxvZ3MuY29tyAEB2gE7aHR0cDovL3d3dy5jbmJsb2dzLmNvbS8xMzU5MC9hcmNoaXZlLzIwMDcvMDcvMDkvODEwNzcwLmh0bWyAAgGoAwHoA9cC6AOMA-gDDegDBegDA4gEAZAEAZgEAA&num=3&adurl=http://www.macau.com/index.php%3Foption%3Dcom_casinos%26Itemid%3D182%26task%3Dshow_details%26id%3D10%26lang%3Ds_chinese&client=ca-pub-1681215984289622");GgKwClickStat("使用者","www.macau.com","afc","2000072864");" onmouseout="isShowGg = false;InTextAds_GgLayer="_u7528_u6237"">使用者可以構建和管理用於業務的高可用和高效能的資料應用程式。此外SQL Server 2005結合了分析、報表、整合和通知功能。這使企業可以構建和部署經濟有效BI<strong class="kgb" onmouseover="isShowAds = false;isShowAds2 = false;isShowGg = true;InTextAds_GgLayer="_u89E3_u51B3_u65B9_u6848";KeyGate_ads.ShowGgAds(this,"_u89E3_u51B3_u65B9_u6848",event)" style="border-right: 0px; padding-right: 0px; border-top: 0px; padding-left: 0px; font-weight: normal; padding-bottom: 0px; margin: 0px; border-left: 0px; cursor: hand; color: #0000ff; padding-top: 0px; border-bottom: 0px; text-decoration: underline" onclick="javascript:window.open("http://pagead2.googlesyndication.com/pagead/iclk?sa=l&ai=BtJXaftmkSOC3NI3-vAOPvKjiB9XjqmXFz8myBMCNtwGgnAEQBhgGIKy8kQooFDgAUPCpzdT6_____wFgnaHfgdgFoAGj2vX-A7IBD3d3dy5jbmJsb2dzLmNvbcgBAdoBO2h0dHA6Ly93d3cuY25ibG9ncy5jb20vMTM1OTAvYXJjaGl2ZS8yMDA3LzA3LzA5LzgxMDc3MC5odG1sgAIBqAMB6APXAugDjAPoAw3oAwXoAwOIBAGQBAGYBAA&num=6&adurl=http://www.edong.com/v8/delicatedserver/idcadd.php&client=ca-pub-1681215984289622");GgKwClickStat("解決方案","www.edong.com","afc","2000072864");" onmouseout="isShowGg = false;InTextAds_GgLayer="_u89E3_u51B3_u65B9_u6848"">解決方案，協助團隊通過計分卡、Dashboard、Web Services和行動裝置將資料應用推向業務的各個領域。無論是開發人員、資料庫管理員、資訊工作者還是決策者，SQL Server 2005都可以提供出創新的解決方案，並可從資料中獲得更多的益處。

　　它所帶來的新特性，如T-SQL的增強、資料分區、服務代理和與.Net Framework的整合等，在易管理性、可用性、延展性和安全性等方面都有很大的增強。

2    表分區的具體實現方法

　　表分區分為水平資料分割和垂直資料分割。水平資料分割將表分為多個表。每個表包含的列數相同，但是行更少。例如，可以將一個包含十億行的表水平資料分割成 12 個表，每個小表表示特定年份內一個月的資料。任何需要特定月份資料的查詢只需引用相應月份的表。而垂直資料分割則是將原始表分成多個只包含較少列的表。水平資料分割是最常用分區方式，本文以水平資料分割來介紹具體實現方法。

　　水平資料分割常用的方法是根據時期和使用對資料進行水平資料分割。例如本文例子，一個簡訊發送記錄表包含最近一年的資料，但是只定期訪問本季度的資料。在這種情況下，可考慮將資料分成四個區，每個區只包含一個季度的資料。

2.1     建立檔案組

　　建立分區表先要建立檔案組，而建立多個檔案組主要是為了獲得好的 I/O 平衡。一般情況下，檔案組數最好與分區數相同，並且這些檔案組通常位於不同的磁碟上。每個檔案組可以由一個或多個檔案構成，而每個分區必須映射到一個檔案組。一個檔案組可以由多個分區使用。為了更好地管理資料（例如，為了獲得更精確的備份控制），對分區表應進行設計，以便只有相關資料或邏輯分組的資料位元於同一個檔案組中。使用 ALTER DATABASE，添加邏輯檔案組名：

ALTER DATABASE [DeanDB] ADD FILEGROUP [FG1]

DeanDB為資料庫名稱，FG1檔案組名。建立檔案組後，再使用 ALTER DATABASE 將檔案添加到該檔案組中：

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

　　類似的建立四個檔案和檔案組，並把每一個儲存資料的檔案放在不同的磁碟機裡。

2.2     建立分區函數

　　建立分區表必須先確定分區的功能機制，表進行分區的標準是通過分區函數來決定的。建立資料分區函數有RANGE “LEFT | / RIGHT”兩種選擇。代表每個邊界值在局部的哪一邊。例如存在四個分區，則定義三個邊界點值，並指定每個值是第一個分區的上邊界 (LEFT) 還是第二個分區的下邊界 (RIGHT)[1]。代碼如下：

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

2.3     建立資料分割配置

　　建立分區函數後，必須將其與資料分割配置相關聯，以便將分區指向至特定的檔案組。就是定義實際存放資料的媒體與各資料區塊的對應關係。多個資料表可以共用相同的資料分區函數，一般不共用相同的資料資料分割配置。可以通過不同的資料分割配置，使用相同的分區函數，使不同的資料表有相同的分區條件，但存放在不同的媒介上。建立資料分割配置的代碼如下：

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

2.4     建立分區表

　　建立好分區函數和資料分割配置後，就可以建立分區表了。分區表是通過定義分區索引值和資料分割配置相聯絡的。插入記錄時，SQL SERVER會根據分區索引值的不同，通過分區函數的定義將資料放到相應的分區。從而把分區函數、資料分割配置和分區表三者有機的結合起來。建立分區表的代碼如下：

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     查看分區表資訊

　　系統運行一段時間或者把以前的資料匯入分區表後，我們需要查看資料的具體儲存情況，即每個分區存取的記錄數，那些記錄存取在那個分區等。我們可以通過$partition.SendSMSPF來查看，代碼如下：

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]

在查詢分析器裡執行以上指令碼，結果1所示：

圖1　分區表資訊

2.6     維護分區

　　分區的維護主要設計分區的添加、減少、合并和在分區間轉換。可以通過ALTER PARTITION FUNCTION的選項SPLIT，MERGE和ALTER TABLE的選項SWITCH來實現。SPLIT會多增加一個分區，而MEGRE會合并或者減少分區，SWITCH則是邏輯地在組間轉換分區。

3    效能對比

　　我們對2650萬資料，儲存空間佔用約4G的單表進行效能對比，測試環境為IBM365，CPU 至強2.7G*2、記憶體 16G、硬碟 136G*2，系統平台為Windows 2003 SP1+SQL Server 2005 SP1。測試結果如表1：

表1：分區和未分區效能對比表(單位：毫秒)

測試專案          分區            未分區

1             16546             61466

2                13                33

3             20140             61546

4             17140             61000

說明：

1：根據時間檢索某一天記錄所耗時間

2：單條記錄插入所耗時間

3：根據時間刪除某一天記錄所耗時間

4：統計每月的記錄數所需時間

　　從表1可以看出，對分區表進行操作比未分區的表要快，這是因為對分區表的操作採用了CPU和I/O的並行操作，檢索資料的資料量也變小了，定位元據所耗時間變短。

4    結束語

　　對海量資料的處理一直是一個令人頭痛的問題。分離的技術是所有設計者們首先考慮的問題，不管是分離應用程式功能還是分離資料訪問，如果加以了<strong class="kgb" onmouseover="isShowAds = false;isShowAds2 = false;isShowGg = true;InTextAds_GgLayer="_u5408_u7406";KeyGate_ads.ShowGgAds(this,"_u5408_u7406",event)" style="border-right: 0px; padding-right: 0px; border-top: 0px; padding-left: 0px; font-weight: normal; padding-bottom: 0px; margin: 0px; border-left: 0px; cursor: hand; color: #0000ff; padding-top: 0px; border-bottom: 0px; text-decoration: underline" onclick="javascript:window.open("http://pagead2.googlesyndication.com/pagead/iclk?sa=l&ai=BAuhPftmkSOC3NI3-vAOPvKjiB6iiile4tqzABMCNtwHQyhYQAhgCIKy8kQooFDgAUK7V_ZADYJ2h34HYBaABnJmw-wOyAQ93d3cuY25ibG9ncy5jb23IAQHaATtodHRwOi8vd3d3LmNuYmxvZ3MuY29tLzEzNTkwL2FyY2hpdmUvMjAwNy8wNy8wOS84MTA3NzAuaHRtbIACAakCwBp42leQgz7IApru0wSoAwHoA9cC6AOMA-gDDegDBegDA4gEAZAEAZgEAA&num=2&adurl=http://www.bestengine.com.cn&client=ca-pub-1681215984289622");GgKwClickStat("合理","www.bestengine.com.cn","afc","2000072864");" onmouseout="isShowGg = false;InTextAds_GgLayer="_u5408_u7406"">合理規劃，都能十分有效解決大資料表的運行效率低和維護成本高等問題。SQL Server 2005新增的表資料分割函數，可以對資料進行合理分區，當使用者在訪問部分資料時，SQL Server最佳化引擎可以根據資料的實體存放，找出最佳的執行方案，而不至於大海撈針。