數(shù)據(jù)倉庫中事實表的水平分區(qū)
對于大型數(shù)據(jù)庫來說，其事實表的數(shù)據(jù)記錄數(shù)量往往會在很短的時間內(nèi)迅猛地增長。有時候，難以對這樣的龐大的數(shù)據(jù)集進行有效的管理，甚至SQL查詢的性能和速度都會受到不利的影響。數(shù)據(jù)庫分區(qū)技術就應運而生了。對事實表進行水平分區(qū)，有利于改善大型數(shù)據(jù)庫的查詢和讀寫性能，并簡化數(shù)據(jù)庫管理。
表的水平分區(qū)是將表的行劃分為多個不重疊的單元。水平分區(qū)表中的每一個分區(qū)單元都是通過對某個屬性列的值進行邊界范圍界定的，例如日期、地理區(qū)劃、客戶名等等。(注：你可以考慮將每個分區(qū)單元存儲在不同的硬盤，以提高查詢性能)。雖然對于數(shù)據(jù)庫管理員和數(shù)據(jù)庫開發(fā)人員來說，對表進行水平分區(qū)并不是什么難事，不過在實際操作之前還是需要進行一定的規(guī)劃，因為你正在處理的可是超大量的數(shù)據(jù)。下面，我們會和大家探討一下什么時候應當對一個事實表進行水平分區(qū)，而進行水平分區(qū)的原因又是什么。此外，我們還會介紹如何使用SQL Server 2005的內(nèi)置功能創(chuàng)建分區(qū)函數(shù)、分區(qū)方案和分區(qū)表。
為什么要對事實表進行水平分區(qū)?
大型表格(例如，具有億萬行的表)的管理難度很大，主要是因為表的規(guī)模太大，無論你對其進行任何操作都要花費大量的時間(例如，重新生成索引操作)。在一個事務數(shù)據(jù)庫中，關聯(lián)表(例如，涉及多對多關系的表)通常是擁有最多行數(shù)的表。在維度建模過程中，一個事實表相當于一個關聯(lián)表。就像事務數(shù)據(jù)庫中的關聯(lián)表一樣，事實表往往擁有比其相關維度更多的行。
分區(qū)可以將這些超大型的表分割成便于管理的小單元。如果你用于維護數(shù)據(jù)庫的時間在緊縮，而需要處理的數(shù)據(jù)量卻在不斷的增長，你就可以先對表進行分區(qū)，再按照分區(qū)來執(zhí)行備份和恢復操作以及更新表統(tǒng)計信息等維護任務，而不是對整個表進行這些操作。SQL Server 2005將一個分區(qū)表的所有分區(qū)看作一個邏輯實體，而且在終端用戶面前，這些分區(qū)仍然以一個整體表的形式出現(xiàn)。
對表進行水平分區(qū)有以下原因：
◆可以更好地控制將每個分區(qū)放在存儲器的哪些位置，并利用多讀寫頭來實現(xiàn)快速查詢。
◆可以按分區(qū)來進行備份和恢復操作，索引的重新生成和重新組織也可以按照分區(qū)來執(zhí)行，而且可以對索引本身進行分區(qū)。
◆可以直接通過一個包含分區(qū)列或索引列的WHERE語句進行查詢。
◆由于將鎖限制在分區(qū)，你可以減少鎖升級和鎖管理的開銷。
◆如果多個分區(qū)在同一個文件組，那么合并和分割分區(qū)就非常容易。
接下來的問題是什么表適合進行水平分區(qū)呢?適合進行水平分區(qū)的表包括包含數(shù)據(jù)量非常大的表、預計在近期內(nèi)會數(shù)據(jù)量將會猛增的表、以及能夠根據(jù)某種業(yè)務屬性值(例如，財政年度)來直觀分割的表。這些表都必須包含一個能用來將行分割成獨立不重疊單元的非空屬性列，例如由銷售時間構成的列。
如果你的數(shù)據(jù)庫包含了一個大型表格，對這個表格進行查詢和更新操作時，執(zhí)行的性能都沒能達到你的預期要求，那么你就可以考慮對表進行分區(qū)以提高查詢性能。SQL Server 2005能夠識別分區(qū)，也就是說，如果包含了涉及分區(qū)列或索引列(該索引也是分區(qū)索引)的WHERE語句的查詢運行很慢時，查詢策略只會訪問相關的分區(qū)，這樣查詢可以在小范圍記錄里進行。該功能可以顯著提高查詢性能。
#p#
創(chuàng)建分區(qū)函數(shù)
要對表進行分區(qū)，首先需要創(chuàng)建由一個指定的分區(qū)列和一系列范圍邊界值構成的分區(qū)函數(shù)。執(zhí)行下面的例子中的指令為SALESFact表創(chuàng)建了分區(qū)函數(shù)：
CREATE PARTITION FUNCTION MyDateRangePF (datetime)
AS RANGE LEFT FOR VALUES (‘1/01/2003’, ‘1/01/2005’, ‘1/01/2007)
MyDateRangePF是分區(qū)函數(shù)的名稱。在本例的環(huán)境中，分區(qū)列通常是datetime數(shù)據(jù)類型的列，例如本例中為Date_of_Event列。將datetime記錄分離為不重疊的組很簡單。例如，如果業(yè)務規(guī)則和已知的業(yè)務查詢都表明按照銷售事件發(fā)生的日期來分割表是合理的話，那么你就可以將數(shù)據(jù)分割成兩年一組，就像上面的分區(qū)函數(shù)命令中所示。
(datetime)指定了分區(qū)列的數(shù)據(jù)類型，而RANGE LEFT規(guī)定了FOR VALUES所定義的邊界日期屬于每個邊界值間隔的哪一側(cè)。在范圍分區(qū)中，如果要分為四個分區(qū)，就定義三個邊界值，設置邊界值有兩種方法：RANGE LEFT或RANGE RIGHT。RANGE LEFT指定每個值為每個分區(qū)的上邊界，而RANGE RIGHT則指定每個值為下一個分區(qū)的下邊界。例如，上面的RANGE LEFT分區(qū)函數(shù)將數(shù)據(jù)分為四個分區(qū)，每個分區(qū)的取值范圍如下表所示。如果用RANGE RIGHT來替代上述命令的RANGE LEFT，而使用相同的邊界值，那么其分區(qū)取值范圍會發(fā)生變化，見下表。
從上面的表格可以看出，如果銷售事件發(fā)生的日期是2005年1月1日，那么對于RANGE LEFT分區(qū)函數(shù)，你會在第二個分區(qū)中找到這條記錄，而對于RANGE RIGHT分區(qū)函數(shù)，則要在第三個分區(qū)中找到該記錄。為了數(shù)據(jù)的一致性，也為了方便查詢，建議對所有創(chuàng)建的分區(qū)表都統(tǒng)一選擇其中一種方法設置邊界值。
每一個分區(qū)的取值范圍都在FOR VALUES語句明確規(guī)定了其邊界值。注意，如果你使用datetime數(shù)據(jù)類型作為邊界值，則必須為日期時間設定一個國際標準，特別是對在不同的時區(qū)都有分部的企業(yè)來說更是如此。SQL Server將美國英語作為該會話的默認語言，如果實際使用的是其他語言，必須創(chuàng)建用戶自定義函數(shù)將不同的日期格式轉(zhuǎn)換為美國英語格式，并在FOR VALUES子句中引用該自定義函數(shù)。
創(chuàng)建分區(qū)方案
現(xiàn)在分區(qū)函數(shù)已經(jīng)創(chuàng)建完畢，接下來要創(chuàng)建一個分區(qū)方案。分區(qū)方案可以將以分區(qū)表或已分區(qū)索引的分區(qū)映射到不同的文件組，可以使用以下命令創(chuàng)建分區(qū)方案：
CREATE PARTITION SCHEME MyPartitionScheme
AS MyDateRangePF
TO (MyFilegroup1, MyFilegroup2, MyFilegroup3, MyFilegroup4, MyFilegroup5)
MyPartitionScheme是分區(qū)方案的名稱，而MyDateRangePF則是使用該分區(qū)方案的分區(qū)函數(shù)的名稱。該命令將分區(qū)函數(shù)所創(chuàng)建的分區(qū)映射到指定的一個或多個文件組。在上面的例子中，對分區(qū)按照Date_of_Event列使用分區(qū)函數(shù)MyDateRangePF的表的分區(qū)會按照下表所示分配到各個文件組：
上面的CREATE PARTITION FUNCTION命令包含了三個邊界值和四個分區(qū)。不管分區(qū)是按照RANGE RIGHT還是RANGE LEFT創(chuàng)建的，分區(qū)數(shù)都比邊界值數(shù)大1，每個表最多可以有1000個分區(qū)。那么為什么在本例中，分區(qū)數(shù)只有四個，卻設置了五個文件組而不是四個呢?在SQL Server中，當分區(qū)數(shù)少于文件組數(shù)的時候，會將***個沒有分配分區(qū)的文件組標記為NEXT USED，作為候選待用文件組，如本例中的MyFilegroup5。下面我們來看看在分區(qū)方案中，這個NEXT USED文件組是如何使用的而其重要性又有多大。
CREATE PARTITION FUNCTION命令中，***一個邊界值為1/01/2007，所以Date_of_Event列的值大于等于1/01/2007的行記錄都會被存儲到第四個分區(qū)(P4)中。當2009年1月1日來臨的時候，你可能會創(chuàng)建一個新的分區(qū)來維持你之前所建立的分區(qū)計劃。如果你使用不包含NEXT USED文件組的原始CREATE PARTITION SCHEME命令創(chuàng)建新分區(qū)，你將無法將現(xiàn)有的P4劃分成P4(保留2007年到2008年底數(shù)據(jù)的分區(qū))和P5(存儲2009年1月1日及以后數(shù)據(jù)的分區(qū))兩個分區(qū)。如果你的分區(qū)計劃需要定期創(chuàng)建的新分區(qū)來保存新數(shù)據(jù)(如本例)，那么你就要保證在你的CREATE PARTITION SCHEME命令中包含了NEXT USED文件組。你不需要為這個文件組分配一個分區(qū)。相反，你可以將多個分區(qū)映射到一個文件組，設置可以將所有的分區(qū)都映射到一個文件組。不過，你無法將一個分區(qū)映射到多個不同的文件組。
創(chuàng)建分區(qū)方案可能是數(shù)據(jù)庫分區(qū)過程中最重要的一個步驟。在將來，你有可能需要將兩個相鄰分區(qū)的數(shù)據(jù)合并到一個分區(qū)里，也可能要為現(xiàn)有的分區(qū)增加一個邊界值，還可能需要將數(shù)據(jù)從一個密集的分區(qū)移動到一個空分區(qū)中。要執(zhí)行這些操作，你都需要事先做好規(guī)劃，并創(chuàng)建分區(qū)方案來支持這些操作。
#p#
創(chuàng)建分區(qū)表
創(chuàng)建分區(qū)表和創(chuàng)建普通表的區(qū)別不大，你只需要在ON子句中引用分區(qū)方案的名稱就可以了，見以下命令：
CREATE TABLE SALESFact
(SalesFact_KEY bigint identity
(1, 1) primary not clustered NOT NULL,
Date_of_Event datetime NOT NULL)
ON MyPartitionScheme (Date_of_Event)
通過指定分區(qū)方案的名稱，你可以看出這個表是一個分區(qū)表。當然，在創(chuàng)建該表之前，分區(qū)方案和分區(qū)函數(shù)必須已經(jīng)存在于數(shù)據(jù)庫當中。
你可以將多個分區(qū)的數(shù)據(jù)合并到一個分區(qū)里。不過，你每次只能合并兩個相鄰的分區(qū)，所以如果你想要將一個含有多個分區(qū)的表合并成不分區(qū)的表，你就需要重復很多次合并的步驟。運行以下命令可以將兩個表合并：
ALTER PARTITION FUNCTION
MyPartitionFunction()
MERGE RANGE (‘1/01/2003’)
上面的命令將會把***個分區(qū)(P1)合并到第二個分區(qū)(P2)，也就是說，合并后P2分區(qū)將包含Date_of_Event值在1/01/05以前的所有行記錄。在數(shù)據(jù)庫內(nèi)部(例如，在sys.partitions系統(tǒng)表中)，將會記錄對分區(qū)所做的修改。原來的P1和P2將變成P1，原來的P3變?yōu)镻2，原來的P4變?yōu)镻3。建議在合并任何分區(qū)之前，先畫出數(shù)據(jù)庫圖表目錄，因為如果你在執(zhí)行這些合并操作時不夠小心的話，有可能需要花費很多時間才能理清其中關系。
輕松管理超大型數(shù)據(jù)庫表
SQL Server 2005能夠?qū)⒁粋€表水平分割成不重疊的單元，并將每個單元分配到一個獨立的分區(qū)中，使我們能夠輕松管理超大型的事實表。SQL Server 2005 的Enterprise版和Developer版都能夠識別分區(qū)，這樣寫操作就只需訪問相關分區(qū)的行，因此運行的速度比要訪問整個表的內(nèi)容來得更快。
【編輯推薦】
