深入淺出SQL Server 2008 分區函數和分區表

標籤：style   blog   http   io   color   ar   os   使用   for   

原文:深入淺出SQL Server 2008 分區函數和分區表

    當我們資料量比較大的時候，我們需要將大型表拆分為多個較小的表，則只訪問部門資料的查詢就可以更快的運行，基本原理就是，因為要掃描的資料變的更小。維護任務（例如，重建索引或備份表）也可以更快的運行。

   我們可以再不通過將表物理放置在多個磁碟機上來拆分表的情況下擷取分區。如果將某個表放置在一個物理磁碟機上，將相關表放置在另一個磁碟機上，則可以提高查詢效能，因為當運行涉及表間串連的查詢時，多個磁碟頭同時讀取資料。可以使用SQL Server檔案組來指定放置表的磁碟。

  對於分區的方式，基本就三種方式：硬體分區、水平資料分割、垂直資料分割。相關方案可以參考SQL聯機叢書

  這裡我們介紹分區表的具體實戰方法：

  第一步，首先建立我們要使用的資料庫，最重要的是建立多個檔案組。

  我們先建立立四個目錄，來組成檔案組，一個用來存放主檔案的目錄：Primary

  三個資料檔案目錄：FG1、FG2、FG3

  建立庫： 

create  database  Sales on primary(   name=N‘Sales‘,   filename=N‘G:\data\Primary\Sales.mdf‘,   size=3MB,   maxsize=100MB,   filegrowth=10%),filegroup FG1(  NAME = N‘File1‘,     FILENAME = N‘G:\data\FG1\File1.ndf‘,     SIZE = 1MB,     MAXSIZE = 100MB,     FILEGROWTH = 10% ),FILEGROUP FG2   (     NAME = N‘File2‘,     FILENAME = N‘G:\data\FG2\File2.ndf‘,     SIZE = 1MB,     MAXSIZE = 100MB,  FILEGROWTH = 10%   ),FILEGROUP FG3   (     NAME = N‘File3‘,     FILENAME = N‘G:\data\FG3\File3.ndf‘,     SIZE = 1MB,     MAXSIZE = 100MB,     FILEGROWTH = 10%   )   LOG ON   (     NAME = N‘Sales_Log‘,     FILENAME = N‘G:\data\Primary\Sales_Log.ldf‘,     SIZE = 1MB,     MAXSIZE = 100MB,     FILEGROWTH = 10%)GO

第二步：建立分區函數，目的是用來規範不同資料存放到不同目錄的標準，簡單講就是如何分區。

USE Sales   GOCREATE PARTITION FUNCTION pf_OrderDate (datetime)   AS RANGE RIGHT   FOR VALUES (‘2003/01/01‘, ‘2004/01/01‘) GO

我們建立了一個用於資料類型為datetime的分區函數，按照時間段來劃分
檔案組 分區    取值範圍
FG1    1        (過去某年, 2003/01/01)
FG2    2       [2003/01/01, 2004/01/01)
FG3    3        [2004/01/01,未來某年)

第三步：建立資料分割配置，關聯到分區函數。目的就是我們將已經建立好的分區函數組織成一套方案，簡單點將就是我們在哪裡對資料進行分區。

Use Salesgocreate  partition  scheme ps_OrderDateas partition  pf_OrderDateto(FG2,FG2,FG3)go

很簡單，就是將第二步建立的分區函數應用已經建立的分區組中。
第四步：建立分區表。建立表並將其綁定到資料分割配置上。我們首先建立兩個表，一張原始表另一張用來歸檔資料，儲存歸檔資料。

Use Salesgocreate table Orders(   OrderID int identity(10000,1),   OrderDate datetime  not null,   CustomerID int not null,   constraint  PK_Orders primary key(OrderID,OrderDate))on ps_OrderDate(OrderDate)gocreate table OrdersHistory(   OrderID int identity(10000,1),   OrderDate datetime  not null,   CustomerID int not null,   constraint  PK_OrdersHistory primary key(OrderID,OrderDate))on ps_OrderDate(OrderDate)go

到這裡，通過上面的四步我們已經完整的搭建好了一個帶有分區表的庫，我們來插入一些資料，來測試下我們建立是否好用。

首先，因為是用2003年1月1號作為區分點的，我們先向資料表中寫入2002年的規範資料

USE Sales   GO   INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2002/6/25‘, 1000)   INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2002/8/13‘, 1000)   INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2002/8/25‘, 1000)   INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2002/9/23‘, 1000)GO

同樣我們寫入2003年四條資料

USE Sales   GOINSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2003/6/25‘, 1000)INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2003/8/13‘, 1000)INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2003/8/25‘, 1000)INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (‘2003/9/23‘, 1000)   GO

我們來查看這些資料是否完整錄入：

因為OrdersHistory表我們還沒有歸檔資料，所以為空白。

我們來分條件查詢下：

1、查詢某個分區

這裡我們要用到$partition函數。這個函數在聯機叢書中是這樣解釋的：

用法：為任何指定的分區函數返回分區號，一組分區列值將映射到該分區號中。文法： [ database_name. ] $PARTITION.partition_function_name(expression) 參數：database_name 包含分區函數的資料庫的名稱。partition_function_name 對其應用一組分區列值的任何現有分區函數的名稱。expression 其資料類型必須匹配或可隱式轉換為其對應分區列資料類型的運算式。expression 也可以是當前參與 partition_function_name 的分區列的名稱。傳回型別：int 備忘：$PARTITION 返回從 1 到分區函數的分區數之間的 int 值。$PARTITION 將針對任何有效值返回分區號，無論此值當前是否存在於使用分區函數的分區表或索引中。

我們來查詢分區表Order的第一個分區，代碼如下：

可以看到我們查詢出來的資料全部為2002年的，也就是說在第一分區中我們存入的資料都是小於2003年，按照此推斷2003年的資料，就應該存在第二分區中：

結果如我們所料，我們可以按照這個分區進行分組來查看各個分區的資料行多少，代碼如下：

select $partition.pf_OrderDate(OrderDate) as Patition,COUNT(*) countRow from dbo.Ordersgroup by $partition.pf_OrderDate(OrderDate)

還可以通過$Partition函數獲得一組分區識別欄位值的分區號，例如獲得2002屬於哪個分區，代碼如下：

 2、歸檔資料

假如現在是2003年年初，那麼我們就可以把2002您所有的交易記錄歸檔到我們剛才建立的曆史訂單表HistroryOrder中。代碼如下：

Use Salesgoalter table orders switch partition 1 to ordersHistory partition 1go

現在我們再重新查看這兩張表的資料：

這時候Orders表只剩下2003年的資料，而OdersHistory表中包含了2002年的資料。

簡單點講就是把第一區的資料匯入到另一張分區表的第一區中

當然如果到了2004年年初，我們就可以歸檔2003年的所有交易資料。

Use Salesgoalter table orders switch partition 2 to ordersHistory partition 2go

這裡需要注意的是我們按照區進行資料修改的時候，必須是同一種分區函數下的分區表進行操作，並且分區結構相對應，如果不這樣會報錯，例如：

  3、添加分區

當我們需要新添加分區的時候，我們需要修改資料分割配置，比如現在我們到了2005年年初，我們需要為2005年的交易記錄準備分區，就需要添加分區：

USE [master]GOALTER DATABASE [Sales] ADD FILEGROUP [FG4]GOALTER DATABASE [Sales] ADD FILE ( NAME = N‘File4‘, FILENAME = N‘G:\data\FG4\File4.ndf‘ , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG4]GO

我們建立立了一個檔案組，然我們同樣按照上面的方法，進行修改分區函數和方案：

use Salesgoalter partition scheme ps_OrderDate  next used [FG4]alter partition function  pf_OrderDate() split range(‘2005/01/01‘)go

我們這裡用alter partition Scheme ps_OrderDate Next Used FG4用來指定新分區的資料在那個檔案。這裡Next Used FG4指定的就是我們剛才建立立的第四個檔案組。當然我們可以放在原來已經建立的檔案組，為了防治資料混亂存放我們大部分是建立立檔案組。

alter partition function pf_OrderDate() split range(‘2005/01/01‘)代表我麼建立一個新分區，而這裡split range是建立新分區的關鍵文法。

至此，我們就有了四個分區，此時的區間如下：

檔案組 分區    取值範圍
FG1    1        (過去某年, 2003/01/01)
FG2    2       [2003/01/01, 2004/01/01)
FG3    3        [2004/01/01,2005/01/01]

FG4    4         [2004/01/01,未來某年)

4、刪除分區

刪除分區又稱合并分區，簡單講就是兩個分區的資料進行合并，比如我們想合并2002年的分區和2003年的分區到一個分區，我們可以用如下的代碼：

use Salesgoalter partition function  pf_OrderDate() merge range(‘2003/01/01‘)go

也就是將2003年這個分區點去掉，裡面分區裡面的資料會自動合并到一起。

執行完上面的代碼，此時分區區間如下：
檔案組 分區      取值範圍
Fg2     1        [過去某年, 2004/01/01)
Fg3     2        [2004/01/01, 2005/01/01)
Fg2     3        [2005/01/01, 未來某年)

合并2002和2003年的資料到2003年之後，我們執行如下代碼：

SELECT Sales.$PARTITION.pf_OrderDate(‘2003‘)

你會發現返回的結果是1。而原來返回的是2，原因是2002年以前資料所在的那個分區合并到了2003年這個分區中了。
此時我們執行下面代碼:

SELECT *FROM dbo.OrdersHistory   WHERE $PARTITION.pf_OrderDate(OrderDate) = 2

結果一行資料都沒返回，事實就這樣，因為OrderHistroy表中只儲存了2002和2003年的曆史資料，在沒有合并分區之前，執行上面的代碼肯定會查詢出2003年的資料，但是合并了分區之後，上面代碼實際查詢的是第二個分區中2004年的資料。
不過我們改成如下代碼：

SELECT *FROM dbo.OrdersHistory   WHERE $PARTITION.pf_OrderDate(OrderDate) = 1

便會查詢出8行資料，包括2002年和2003年的資料，因為合并分區後2002年和2003年的資料都成了第1分區的資料了。
5、查看中繼資料

我們可以通過三個系統檢視表來查看我們的分區函數，資料分割配置，邊界值點等。

select * from sys.partition_functions   select * from sys.partition_range_valuesselect * from sys.partition_schemes

 

深入淺出SQL Server 2008 分區函數和分區表