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