The NoteBook of EricKong

　　SQL Server數(shù)據(jù)庫(kù)表分區(qū)操作過(guò)程由三個(gè)步驟組成：

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

　　2. 創(chuàng)建分區(qū)架構(gòu)

　　3. 對(duì)表進(jìn)行分區(qū)

　　下面將對(duì)每個(gè)步驟進(jìn)行詳細(xì)介紹。

　　步驟一：創(chuàng)建一個(gè)分區(qū)函數(shù)

　　此分區(qū)函數(shù)用于定義你希望SQL Server如何對(duì)數(shù)據(jù)進(jìn)行分區(qū)的參數(shù)值(how)。這個(gè)操作并不涉及任何表格，只是單純的定義了一項(xiàng)技術(shù)來(lái)分割數(shù)據(jù)。

　　我們可以通過(guò)指定每個(gè)分區(qū)的邊界條件來(lái)定義分區(qū)。例如，假定我們有一份Customers表，其中包含了關(guān)于所有客戶(hù)的信息，以一一對(duì)應(yīng)的客戶(hù)編號(hào)(從1到1,000,000)來(lái)區(qū)分。我們將通過(guò)以下的分區(qū)函數(shù)把這個(gè)表分為四個(gè)大小相同的分區(qū)：　　

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

　　這些邊界值定義了四個(gè)分區(qū)。第一個(gè)分區(qū)包括所有值小于250,000的數(shù)據(jù)，第二個(gè)分區(qū)包括值在250,000到49,999之間的數(shù)據(jù)。第三個(gè)分區(qū)包括值在500,000到7499,999之間的數(shù)據(jù)。所有值大于或等于750,000的數(shù)據(jù)被歸入第四個(gè)分區(qū)。

　　請(qǐng)注意，這里調(diào)用的"RANGE RIGHT"語(yǔ)句表明每個(gè)分區(qū)邊界值是右界。類(lèi)似的，如果使用"RANGE LEFT"語(yǔ)句，則上述第一個(gè)分區(qū)應(yīng)該包括所有值小于或等于250,000的數(shù)據(jù)，第二個(gè)分區(qū)的數(shù)據(jù)值在250,001到500,000之間，以此類(lèi)推。

　　步驟二：創(chuàng)建一個(gè)分區(qū)架構(gòu)

　　一旦給出描述如何分割數(shù)據(jù)的分區(qū)函數(shù)，接著就要?jiǎng)?chuàng)建一個(gè)分區(qū)架構(gòu)，用來(lái)定義分區(qū)位置(where)。創(chuàng)建過(guò)程非常直截了當(dāng)，只要將分區(qū)連接到指定的文件組就行了。例如，如果有四個(gè)文件組，組名從"fg1"到"fg4"，那么以下的分區(qū)架構(gòu)就能達(dá)到想要的效果：　　

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

　　注意，這里將一個(gè)分區(qū)函數(shù)連接到了該分區(qū)架構(gòu)，但并沒(méi)有將分區(qū)架構(gòu)連接到任何數(shù)據(jù)表。這就是可復(fù)用性起作用的地方了。無(wú)論有多少數(shù)據(jù)庫(kù)表，我們都可以使用該分區(qū)架構(gòu)(或僅僅是分區(qū)函數(shù))。

　　步驟三：對(duì)一個(gè)表進(jìn)行分區(qū)

　　定義好一個(gè)分區(qū)架構(gòu)后，就可以著手創(chuàng)建一個(gè)分區(qū)表了。這是整個(gè)分區(qū)操作過(guò)程中最簡(jiǎn)單的一個(gè)步驟。只需要在表創(chuàng)建指令中添加一個(gè)"ON"語(yǔ)句，用來(lái)指定分區(qū)架構(gòu)以及應(yīng)用該架構(gòu)的表列。因?yàn)榉謪^(qū)架構(gòu)已經(jīng)識(shí)別了分區(qū)函數(shù)，所以不需要再指定分區(qū)函數(shù)了。

　　例如，使用以上的分區(qū)架構(gòu)創(chuàng)建一個(gè)客戶(hù)表，可以調(diào)用以下的Transact-SQL指令：　　

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

　　關(guān)于SQL Server的表分區(qū)功能，你知道上述的相關(guān)知識(shí)就足夠了。記住!編寫(xiě)能夠用于多個(gè)表的一般的分區(qū)函數(shù)和分區(qū)架構(gòu)就能夠大大提高可復(fù)用性。

         數(shù)據(jù)庫(kù)性能調(diào)優(yōu)是每一個(gè)優(yōu)秀SQL Server管理員最終的責(zé)任。雖然保證數(shù)據(jù)的安全和可用性是我們的最高的目標(biāo)，但是假如數(shù)據(jù)庫(kù)應(yīng)用程序無(wú)法滿足用戶(hù)的要求，那么DBA們會(huì)因?yàn)樾阅艿拖碌脑O(shè)計(jì)和實(shí)現(xiàn)而受到指責(zé)。SQL Server 2005在數(shù)據(jù)庫(kù)性能方面得到了很多提高，尤其是表分區(qū)的技術(shù)。如果你還沒(méi)不了解表分區(qū)的特征，那么請(qǐng)你花點(diǎn)時(shí)間讀這篇文章。

　　表分區(qū)的概念不是一個(gè)新的概念;只要你當(dāng)過(guò)一段時(shí)間的SQL Server DBA，那么你可能已經(jīng)對(duì)一些頻繁訪問(wèn)的表進(jìn)行過(guò)歸檔，當(dāng)這個(gè)表中的歷史數(shù)據(jù)變的不再經(jīng)常被訪問(wèn)的時(shí)候。比如，假設(shè)你有一個(gè)打印時(shí)間報(bào)表的應(yīng)用，你的報(bào)告很少會(huì)查詢(xún)1995年的數(shù)據(jù)，因?yàn)榻^大部分的預(yù)算規(guī)劃會(huì)基于最近幾年的數(shù)據(jù)。

　　在SQL Server的早期版本中，你可以創(chuàng)建多個(gè)表。每一個(gè)表都具有相同的列結(jié)構(gòu)，用來(lái)保存不同年份的數(shù)據(jù)。這樣，當(dāng)存在著對(duì)歷史數(shù)據(jù)訪問(wèn)的必要的時(shí)候，你可以創(chuàng)建一個(gè)視圖來(lái)對(duì)這些表進(jìn)行查詢(xún)處理。將數(shù)據(jù)保存在多個(gè)表中是很方便的，因?yàn)橄鄬?duì)于查詢(xún)時(shí)掃描整個(gè)大表，掃描小表會(huì)更快。但是這種好處只有在你預(yù)先知道哪些時(shí)間段的數(shù)據(jù)會(huì)被訪問(wèn)。同時(shí)，一旦數(shù)據(jù)過(guò)期，你還需要?jiǎng)?chuàng)建新表并且轉(zhuǎn)移新產(chǎn)生的歷史數(shù)據(jù)。

　　SQL Server 7和SQL Server 2000支持分布式分區(qū)視圖(distributed partitioned views，又稱(chēng)為物化視圖，materialized views).分布式分區(qū)視圖由分布于多臺(tái)服務(wù)器上的、具有相同表結(jié)構(gòu)的表構(gòu)成，而且你還需要為每一個(gè)服務(wù)器增加鏈接服務(wù)器定義(linked server definitions)，最后在其中一臺(tái)服務(wù)器上創(chuàng)建一個(gè)視圖將每臺(tái)服務(wù)器上返回的數(shù)據(jù)合并起來(lái)。這里的設(shè)計(jì)思想是數(shù)據(jù)庫(kù)引擎可以利用多臺(tái)服務(wù)器的處理能力來(lái)滿足查詢(xún)。

　　但是，分布式分區(qū)視圖(DPV)受到很多限制，你可以在SQL Server的在線幫助文檔中閱讀到。雖然DPV在一些情況下能夠提供性能上的提高，但是這種技術(shù)不能被廣泛的應(yīng)用。已經(jīng)被證明它們不能滿足逐步增長(zhǎng)的企業(yè)級(jí)應(yīng)用的要求。何況，DPV的實(shí)現(xiàn)是一個(gè)費(fèi)力的過(guò)程，需要DBA進(jìn)行很多工作。

　　SQL Server 2005開(kāi)始支持表分區(qū)，這種技術(shù)允許所有的表分區(qū)都保存在同一臺(tái)服務(wù)器上。每一個(gè)表分區(qū)都和在某個(gè)文件組(filegroup)中的單個(gè)文件關(guān)聯(lián)。同樣的一個(gè)文件/文件組可以容納多個(gè)分區(qū)表。

　　在這種設(shè)計(jì)架構(gòu)下，數(shù)據(jù)庫(kù)引擎能夠判定查詢(xún)過(guò)程中應(yīng)該訪問(wèn)哪個(gè)分區(qū)，而不用掃描整個(gè)表。如果查詢(xún)需要的數(shù)據(jù)行分散在多個(gè)分區(qū)中，SQL Server使用多個(gè)處理器對(duì)多個(gè)分區(qū)進(jìn)行并行查詢(xún)。你可以為在創(chuàng)建表的時(shí)候就定義分區(qū)的索引。 對(duì)小索引的搜索或者掃描要比掃描整個(gè)表或者一張大表上的索引要快很多。因此，當(dāng)對(duì)大表進(jìn)行查詢(xún)，表分區(qū)可以產(chǎn)生相當(dāng)大的性能提升。

　　現(xiàn)在讓我們通過(guò)一個(gè)簡(jiǎn)單的例子來(lái)了解表分區(qū)是如何發(fā)揮作用的。在這篇文章中，我不想深入到分區(qū)的語(yǔ)法細(xì)節(jié)當(dāng)中，這些你可以在SQL Server的在線幫助文檔中找到。下面的例子基于存儲(chǔ)著一個(gè)時(shí)間報(bào)表系統(tǒng)的數(shù)據(jù)的數(shù)據(jù)倉(cāng)庫(kù)。除了默認(rèn)的文件組，我另外創(chuàng)建了7個(gè)文件組，每一個(gè)文件組僅包含一個(gè)文件，這個(gè)文件將存儲(chǔ)由分區(qū)函數(shù)定義的一部分?jǐn)?shù)據(jù)。

　　為了測(cè)試表分區(qū)的性能提升，我向這個(gè)分區(qū)表中插入了一千五百萬(wàn)行，同時(shí)向另外一個(gè)具有相同表結(jié)構(gòu)、但是沒(méi)有進(jìn)行分區(qū)的表插入了同樣的數(shù)據(jù)。對(duì)分區(qū)表執(zhí)行的INSERT語(yǔ)句運(yùn)行的更快一些。甚至在我的內(nèi)存不到1G的筆記本電腦上，對(duì)分區(qū)表的INSERT語(yǔ)句比不分區(qū)的表的INSERT語(yǔ)句要快上三倍。當(dāng)然，查詢(xún)的執(zhí)行時(shí)間依據(jù)硬件資源的差異而所有變化，但是你還是能夠在你的環(huán)境中感到不同程度的提升。

　　我將檢查更深入了一步，通過(guò)分別檢查同一條返回所有行的、簡(jiǎn)單SELECT語(yǔ)句在分區(qū)表和非分區(qū)表上的執(zhí)行計(jì)劃，返回的數(shù)據(jù)范圍通過(guò)WHERE語(yǔ)句來(lái)指定。同一條語(yǔ)句在這兩個(gè)不同的表上有不同的執(zhí)行計(jì)劃。對(duì)于分區(qū)表的查詢(xún)顯示出一個(gè)嵌套的循環(huán)和索引的掃描。從本質(zhì)上來(lái)說(shuō)，SQL Server將兩個(gè)分區(qū)視為獨(dú)立的表，因此使用一個(gè)嵌套循環(huán)將它們連接起來(lái)。對(duì)非分區(qū)的表的同一個(gè)查詢(xún)則使用索引掃描來(lái)返回同樣的列。當(dāng)你使用同樣的分區(qū)策略創(chuàng)建多個(gè)表，同時(shí)在查詢(xún)中連接這些表，那么性能上的提升會(huì)更加明顯

你可以使用下面的查詢(xún)來(lái)了解每一個(gè)分區(qū)中的行的個(gè)數(shù)：

SELECT $PARTITION.TimeEntryDateRangePFN(time_entry_date) AS Partition,  COUNT(*) AS [COUNT] FROM fact_time_entry  GROUP BY $PARTITION.TimeEntryDateRangePFN(time_entry_date)  ORDER BY Partition

　　表分區(qū)對(duì)交易環(huán)境和數(shù)據(jù)倉(cāng)庫(kù)環(huán)境來(lái)說(shuō)，都是一個(gè)重要的特征。數(shù)據(jù)倉(cāng)庫(kù)用戶(hù)最主要的抱怨是移動(dòng)事實(shí)表(fact table)會(huì)花費(fèi)太多時(shí)間。當(dāng)裝載數(shù)據(jù)到事實(shí)表的時(shí)候，用戶(hù)查詢(xún)(立方體處理查詢(xún))的性能會(huì)明顯下降，甚至是完全無(wú)法成功。因此，裝載大量的數(shù)據(jù)到事實(shí)表的時(shí)候常常需要停機(jī)。如果使用表分區(qū)，就不再出現(xiàn)這樣的情況——確切的講，你一眨眼的工夫就可以移動(dòng)事實(shí)表。為了演示這是如何生效的，我使用上面例子中相同的分區(qū)函數(shù)和表結(jié)構(gòu)來(lái)創(chuàng)建一個(gè)新的表，這個(gè)表叫做fact_time_entry2。表的主鍵從五千萬(wàn)開(kāi)始，這樣fact_time_entry2就不會(huì)包含表fact_time_entry中已經(jīng)有的數(shù)據(jù)。

　　現(xiàn)在我把2007年的數(shù)據(jù)移動(dòng)到這張fact_time_entry2中。同時(shí)讓我們假設(shè)fact_time_entry表中包含著2007年之前的數(shù)據(jù)。在fact_time_entry2表完成數(shù)據(jù)的轉(zhuǎn)移，我執(zhí)行下面的語(yǔ)句：

ALTER TABLE fact_time_entry2  SWITCH PARTITION 8 TO fact_time_entry PARTITION 8

　　這條語(yǔ)句將編號(hào)為8的分區(qū)，這個(gè)分區(qū)恰好包含著2007年的數(shù)據(jù)，從fact_time_entry2移動(dòng)到了fact_time_entry表中，在我的筆記本電腦上，這個(gè)過(guò)程只花費(fèi)了3毫秒。在這短短的3毫秒中，我的事實(shí)表就增加了五百萬(wàn)條記錄!的確，我需要在交換分區(qū)之前，將數(shù)據(jù)移動(dòng)到中間表，但是我的用戶(hù)不需要擔(dān)心——事實(shí)表隨時(shí)都可以查詢(xún)!在這幕后，實(shí)際上沒(méi)有數(shù)據(jù)移動(dòng)——只是兩張表的元數(shù)據(jù)發(fā)生了變化。

　　我可以使用類(lèi)似的查詢(xún)刪除事實(shí)表中不在需要的數(shù)據(jù)。例如，假設(shè)我們決定我們不再關(guān)心2004年的記錄。下面的語(yǔ)句可以將這些記錄轉(zhuǎn)移到我們創(chuàng)建的工作表中：

ALTER TABLE fact_time_entry  SWITCH PARTITION 2 TO fact_time_entry2 PARTITION 2

　　這樣的語(yǔ)句依舊在毫秒級(jí)內(nèi)完成了。現(xiàn)在，我可以刪除fact_time_entry2或者將它移到其他的服務(wù)器上。我的事實(shí)表不會(huì)包含2004年的任何記錄。這個(gè)分區(qū)還是需要在目的表中存在，而且它必須是空的。你不能將分區(qū)轉(zhuǎn)移到一個(gè)包含重復(fù)數(shù)據(jù)的表中。源表和目的表的分區(qū)必須一致，同時(shí)被轉(zhuǎn)移的數(shù)據(jù)必須在同一個(gè)文件組中。即使受到這么多的限制，轉(zhuǎn)換分區(qū)和無(wú)需停機(jī)就可以移動(dòng)數(shù)據(jù)表的功能必將讓數(shù)據(jù)倉(cāng)庫(kù)的實(shí)現(xiàn)變的前所未有的輕松。

SQL Server 表分區(qū)(partitioned table/Data Partitioning)
Partitioned Table
        可伸縮性性是數(shù)據(jù)庫(kù)管理系統(tǒng)的一個(gè)很重要的方面，在SQL Server 2005中可伸縮性方面提供了表分區(qū)功能。
        其實(shí)對(duì)于有關(guān)系弄數(shù)據(jù)庫(kù)產(chǎn)品來(lái)說(shuō)，對(duì)表、數(shù)據(jù)庫(kù)和服務(wù)器進(jìn)行數(shù)據(jù)分區(qū)的從而提供大數(shù)據(jù)量的支持并不是什么新鮮事，但 SQL Server 2005 提供了一個(gè)新的體系結(jié)構(gòu)功能，用于對(duì)數(shù)據(jù)庫(kù)中的文件組進(jìn)行表分區(qū)。水平分區(qū)可根據(jù)分區(qū)架構(gòu)，將一個(gè)表劃分為幾個(gè)較小的分組。表分區(qū)功能是針對(duì)超大型數(shù)據(jù)庫(kù)（從數(shù)百吉字節(jié)到數(shù)千吉字節(jié)或更大）而設(shè)計(jì)的。超大型數(shù)據(jù)庫(kù) (VLDB) 查詢(xún)性能通過(guò)分區(qū)得到了改善。通過(guò)對(duì)廣大分區(qū)列值進(jìn)行分區(qū)，可以對(duì)數(shù)據(jù)的子集進(jìn)行管理，并將其快速、高效地重新分配給其他表。
        設(shè)想一個(gè)大致的電子交易網(wǎng)站，有一個(gè)表存儲(chǔ)了此網(wǎng)站的歷史交易數(shù)據(jù)，這此數(shù)據(jù)量可能有上億條，在以前的SQL Server版本中存儲(chǔ)在一個(gè)表中不管對(duì)于查詢(xún)性能還是維護(hù)都是件麻煩事，下面我們來(lái)看一下在SQL Server2005怎么提高性能和可管理性：
-- 創(chuàng)建要使用的測(cè)試數(shù)據(jù)庫(kù)，Demo
USE [master]
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'DEMO')
DROP DATABASE [DEMO]
CREATE DATABASE [DEMO]
--由于表分區(qū)使用使用新的體系結(jié)構(gòu)，使用文件組來(lái)進(jìn)行表分區(qū)，所以我們創(chuàng)建將要用到的6個(gè)文件組，來(lái)存儲(chǔ)6個(gè)時(shí)間段的交易數(shù)據(jù)[<2000],[ 2001], [2002], [2003], [2004], [>2005]
ALTER DATABASE Demo ADD FILEGROUP YEARFG1;
ALTER DATABASE Demo ADD FILEGROUP YEARFG2;
ALTER DATABASE Demo ADD FILEGROUP YEARFG3;
ALTER DATABASE Demo ADD FILEGROUP YEARFG4;
ALTER DATABASE Demo ADD FILEGROUP YEARFG5; 
ALTER DATABASE Demo ADD FILEGROUP YEARFG6; 
-- 下面為這些文件組添加文件來(lái)進(jìn)行物理的數(shù)據(jù)存儲(chǔ)
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF1', FILENAME = 'C:"ADVWORKSF1.NDF') TO FILEGROUP YEARFG1;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF2', FILENAME = 'C:"ADVWORKSF2.NDF') TO FILEGROUP YEARFG2;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF3', FILENAME = 'C:"ADVWORKSF3.NDF') TO FILEGROUP YEARFG3;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF4', FILENAME = 'C:"ADVWORKSF4.NDF') TO FILEGROUP YEARFG4;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF5', FILENAME = 'C:"ADVWORKSF5.NDF') TO FILEGROUP YEARFG5;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF6', FILENAME = 'C:"ADVWORKSF6.NDF') TO FILEGROUP YEARFG6;
-- HERE WE ASSOCIATE THE PARTITION FUNCTION TO 
-- THE CREATED FILEGROUP VIA A PARTITIONING SCHEME
USE DEMO;
GO
-------------------------------------------------------
-- 創(chuàng)建分區(qū)函數(shù)
-------------------------------------------------------
CREATE PARTITION FUNCTION YEARPF(datetime)
AS 
RANGE LEFT FOR VALUES ('01/01/2000'
,'01/01/2001'
,'01/01/2002'
,'01/01/2003'
,'01/01/2004')
-------------------------------------------------------
-- 創(chuàng)建分區(qū)架構(gòu)
-------------------------------------------------------
CREATE PARTITION SCHEME YEARPS
AS PARTITION YEARPF TO (YEARFG1, YEARFG2,YEARFG3,YEARFG4,YEARFG5,YEARFG6)
-- 創(chuàng)建使用此Schema的表
CREATE TABLE PARTITIONEDORDERS
(
ID INT NOT NULL IDENTITY(1,1),
DUEDATE DATETIME NOT NULL,
) ON YEARPS(DUEDATE)
--為此表填充數(shù)據(jù)
declare @DT datetime
SELECT @DT = '1999-01-01'
--start looping, stop at ending date
WHILE (@DT <= '2005-12-21')
BEGIN
    INSERT INTO PARTITIONEDORDERS VALUES(@DT) 
   SET @DT=dateadd(yy,1,@DT)
END
      -- 現(xiàn)在我們可以看一下我們剛才插入的行都分布在哪個(gè)Partition 
SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS
      --我們可以看一下我們現(xiàn)在PARTITIONEDORDERS表的數(shù)據(jù)存儲(chǔ)在哪此partition中，以及在這些分區(qū)中數(shù)據(jù)量的分布
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')
--
        --現(xiàn)在我們?cè)O(shè)想一下，如果我們隨著時(shí)間的流逝，現(xiàn)在已經(jīng)到了2005年，按照我們先前的設(shè)定，我們想再想入一個(gè)分區(qū)，這時(shí)是不是重新創(chuàng)建表分區(qū)架構(gòu)然后重新把數(shù)據(jù)導(dǎo)放到新的分區(qū)架構(gòu)呢，答案是完全不用。下面我們就看如果新加一個(gè)分區(qū)。
       --更改分區(qū)架構(gòu)定義語(yǔ)言，讓下一個(gè)分區(qū)使用和現(xiàn)在已經(jīng)存在的分區(qū)YEARFG6分區(qū)中，這樣此分區(qū)就存儲(chǔ)了兩段partition的數(shù)據(jù)。
ALTER PARTITION SCHEME YEARPS
NEXT USED YEARFG6;
       --更改分區(qū)函數(shù)
ALTER PARTITION FUNCTION YEARPF()
SPLIT RANGE ('01/01/2005') 
       --現(xiàn)在我們可以看一下我們剛才插入的行都分布在哪個(gè)Partition？
SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS
      --我們可以看一下我們現(xiàn)在PARTITIONEDORDERS表的數(shù)據(jù)存儲(chǔ)在哪此partition中，以及在這些分區(qū)中