Sql Server系列：分割槽表操作

1. 分割槽表簡介

　　分割槽表在邏輯上是一個表，而物理上是多個表。從使用者角度來看，分割槽表和普通表是一樣的。使用分割槽表的主要目的是為改善大型表以及具有多個訪問模式的表的可伸縮性和可管理性。

　　分割槽表是把資料按設定的標準劃分成區域儲存在不同的檔案組中，使用分割槽可以快速而有效管理和訪問資料子集。

1.1> 適合做分割槽表的情況

　　◊ 資料庫中某個表的資料很多，在查詢資料時會明顯感覺到速度很慢，這個時候需要考慮分割槽表；

　　◊ 資料是分段的，如以年份為分隔的資料，對於當年的資料經常進行增刪改查操作，而對於往年的資料幾乎不做操作或只做查詢操作，這種情況可以使用分割槽表。對資料的操作如果只涉及一部分資料而不是全部資料的情況可以考慮分割槽表，如果一張表的資料經常使用且不管年份之類的因素經常對其增刪改查操作則最好不要分割槽。

1.2> 分割槽表的優點

　　◊ 分割槽表可以從物理上將一個大表分成幾個小表，但是從邏輯上來看還是一個大表。

　　◊ 對於具有多個CPU的系統，分割槽可以對錶的操作通過並行的方式進行，可以提升訪問效能。

2. 建立分割槽表步驟

　　建立分割槽表的步驟分為5步：

　　（1）建立資料庫檔案組

　　（2）建立資料庫檔案

　　（3）建立分割槽函式

　　（4）建立分割槽方案

　　（5）建立分割槽表

2.1> 建立資料庫檔案組

　　新建示例資料庫Northwind，建立資料庫檔案組和檔案，新增檔案組。

　　

2.2> 建立資料庫檔案

　　建立資料檔案，併為資料檔案分配檔案組。

　　

　　完成建立後的資料庫檔案資訊

　　

　　通過SQL Server Profiler可以看到具體的建立資料庫的指令碼如下：

CREATE DATABASE [Northwind]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Northwind', FILENAME = N'F:\Database\Northwind\Northwind.mdf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [SECTION2010] 
( NAME = N'Northwind_Data_2010', FILENAME = N'F:\Database\Northwind\Northwind_Data_2010.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [SECTION2011] 
( NAME = N'Northwind_Data_2011', FILENAME = N'F:\Database\Northwind\Northwind_Data_2011.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [SECTION2012] 
( NAME = N'Northwind_Data_2012', FILENAME = N'F:\Database\Northwind\Northwind_Data_2012.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [SECTION2013] 
( NAME = N'Northwind_Data_2013', FILENAME = N'F:\Database\Northwind\Northwind_Data_2013.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB ), 
 FILEGROUP [SECTION2014] 
( NAME = N'Northwind_Data_2014', FILENAME = N'F:\Database\Northwind\Northwind_Data_2014.ndf' , SIZE = 5120KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Northwind_log', FILENAME = N'F:\Database\Northwind\Northwind_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)

　　檢視資料庫檔案組SQL語句：

2.3> 建立分割槽函式

　　建立分割槽函式Transact-SQL語法：

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )
AS RANGE [ LEFT | RIGHT ] 
FOR VALUES ( [ boundary_value [ ,...n ] ] ) 
[ ; ]

　　引數：　　

　　partition_function_name：分割槽函式的名稱。 分割槽函式名稱在資料庫內必須唯一，並且符合識別符號的規則。

　　input_parameter_type：用於分割槽的列的資料型別。 當用作分割槽列時，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、別名資料型別或 CLR 使用者定義資料型別外，所有資料型別均有效。

　　boundary_value：為使用 partition_function_name 的已分割槽表或索引的每個分割槽指定邊界值。 如果 boundary_value 為空，則分割槽函式使 partition_function_name 將整個表或索引對映到單個分割槽。 只能使用 CREATE TABLE 或 CREATE INDEX 語句中指定的一個分割槽列。

　　LEFT | RIGHT 指定當間隔值由 資料庫引擎 按升序從左到右排序時，boundary_value [ ,...n ] 屬於每個邊界值間隔的哪一側（左側還是右側）。 如果未指定，則預設值為 LEFT。

　　示例：建立將用於Order表的分割槽函式

CREATE PARTITION FUNCTION Function_DateTime ( DATETIME )
AS RANGE RIGHT
FOR VALUES('2011-01-01', '2012-01-01', '2013-01-01', '2014-01-01')

　　完成建立分割槽函式之後，可以通過以下SQL語句檢視已建立的分割槽函式情況。

SELECT * FROM sys.partition_functions

2.4> 建立分割槽方案

　　分割槽方案的作用是將分割槽函式生成的分割槽對映到檔案組中去，分割槽方案是讓SQL Server將已分割槽的資料放在哪個檔案組中。

　　在當前資料庫中建立一個將已分割槽表或已分割槽索引的分割槽對映到檔案組的方案。 已分割槽表或已分割槽索引的分割槽的個數和域在分割槽函式中確定。 必須首先在 CREATE PARTITION FUNCTION 語句中建立分割槽函式，然後才能建立分割槽方案。

　　建立分割槽方案的Transact-SQL語法：

CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] )
[ ; ]

　　引數：

　　partition_scheme_name：分割槽方案的名稱。 分割槽方案名稱在資料庫中必須是唯一的，並且符合識別符號規則。

　　partition_function_name：使用分割槽方案的分割槽函式的名稱。 分割槽函式所建立的分割槽將對映到在分割槽方案中指定的檔案組。 partition_function_name 必須已經存在於資料庫中。 單個分割槽不能同時包含 FILESTREAM 和非 FILESTREAM 檔案組。

　　ALL：指定所有分割槽都對映到在 file_group_name 中提供的檔案組，或對映到主檔案組（如果指定了 [PRIMARY]。 如果指定了 ALL，則只能指定一個 file_group_name。

　　file_group_name | [ PRIMARY ] [ ,...n]：指定用來持有由 partition_function_name 指定的分割槽的檔案組的名稱。 file_group_name 必須已經存在於資料庫中。
　　如果指定了 [PRIMARY]，則分割槽將儲存於主檔案組中。 如果指定了 ALL，則只能指定一個 file_group_name。 分割槽分配到檔案組的順序是從分割槽 1 開始，按檔案組在 [,...n] 中列出的順序進行分配。 在 [,...n] 中，可以多次指定同一個 file_group_name。 如果 n 不足以擁有在 partition_function_name 中指定的分割槽數，則 CREATE PARTITION SCHEME 將失敗，並返回錯誤。
　　如果 partition_function_name 生成的分割槽數少於檔案組數，則第一個未分配的檔案組將標記為 NEXT USED，並且出現顯示命名 NEXT USED 檔案組的資訊。 如果指定了 ALL，則單獨的 file_group_name 將為該 partition_function_name 保持它的 NEXT USED 屬性。 如果在 ALTER PARTITION FUNCTION 語句中建立了一個分割槽，則 NEXT USED 檔案組將再接收一個分割槽。 若要再建立一個未分配的檔案組來擁有新的分割槽，請使用 ALTER PARTITION SCHEME。
　　在 file_group_name[ 1,...n] 中指定主檔案組時，必須像在 [PRIMARY] 中那樣分隔 PRIMARY，因為它是關鍵字。

 

　　示例：建立將用於Order表的分割槽方案

CREATE PARTITION SCHEME Scheme_DateTime
AS PARTITION Function_DateTime
TO ( SECTION2010, SECTION2011, SECTION2012, SECTION2013, SECTION2014 )

　　分割槽函式和分割槽方案建立之後，可以在資料庫的【儲存】中檢視：

　　通過可以通過以下SQL語句檢視已建立的分割槽方案：

SELECT * FROM sys.partition_schemes

2.5> 建立分割槽表

CREATE TABLE [Order]
(
    OrderID INT IDENTITY(1,1) NOT NULL,
    UserID INT NOT NULL,
    TotalAmount DECIMAL(18,2) NULL,
    OrderDate DATETIME NOT NULL
) ON Scheme_DateTime ( OrderDate )

　　這裡需要注意分割槽表不能再建立聚集索引，因為聚集索引可以將記錄在物理上順序儲存，而分割槽表是將資料儲存在不同的表中，這兩個概念是衝突的，所以在建立分割槽表時不能再建立聚集索引。

　　完成Order表建立之後，檢視錶的屬性，可以看到Order表已經是分割槽表。

3. 操作分割槽表

3.1> Insert資料

USE [Northwind]
GO

INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (1 ,10.00 ,'2009-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (1 ,20.50 ,'2009-12-31');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (2 ,40.00 ,'2010-01-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (3 ,40.00 ,'2010-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (4 ,50.00 ,'2011-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (5 ,60.00 ,'2012-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (5 ,60.00 ,'2012-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (6 ,70.00 ,'2013-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (10 ,90.00 ,'2014-10-20');
INSERT INTO [dbo].[Order] ([UserID],[TotalAmount] ,[OrderDate]) VALUES (9 ,100.00 ,'2015-10-20');
GO

3.2> 查詢資料所在物理分割槽表

　　在分割槽表中使用一般的SELECT語句無法知道資料是分別存放在哪幾個不同的物理表中，若要知道資料分別存放的物理表，可以使用$PARTITION函式，該函式可以呼叫分割槽函式並返回資料所在物理分割槽的編號。

　　$PARTITION的語法：$PARTITION.分割槽函式名(表示式)

SELECT $PARTITION.Function_DateTime('2010-01-01')

　　查詢結果分割槽函式返回為1，說明2010-01-01的資料會存放在第1個物理分割槽表中。

　　使用$PARTITION函式可以具體知道每個物理分割槽表中存放了哪些記錄。

　　檢視物理分割槽表中存放的記錄：

SELECT * FROM [Order] WHERE $PARTITION.Function_DateTime(OrderDate) = 1

SELECT $PARTITION.Function_DateTime(OrderDate) AS 分割槽編號, COUNT(1) AS 記錄數 
FROM [Order]
GROUP BY $PARTITION.Function_DateTime(OrderDate)

3.3> 修改分割槽表資料

UPDATE dbo.[Order] SET OrderDate='2015-01-01' WHERE OrderID = 3

4. 將普通錶轉換為分割槽表

　　一般的普通表都是在主鍵上建聚集索引，記錄的物理儲存位置由主鍵決定。

　　示例：建立一個Product普通表

CREATE TABLE Product
(
    ProductID INT IDENTITY(1,1) NOT NULL,
    ProductName VARCHAR(100) NOT NULL,
    UnitPrice DECIMAL(18,2) NULL,
    CreateDate DATETIME NOT NULL,
    CONSTRAINT PK_Product PRIMARY KEY CLUSTERED (ProductID)
)

USE [Northwind]
GO

INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to SQL' ,10 ,'2012-01-01');
INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to XML' ,10 ,'2012-12-01');
INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to Object' ,10 ,'2013-02-01');
INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to ADO.NET' ,10 ,'2014-01-02');
INSERT INTO [dbo].[Product] ([ProductName],[UnitPrice],[CreateDate]) VALUES ('LINQ to Entity' ,10 ,'2015-01-01');

GO

　　檢視錶Product的屬性：

　　檢視錶Product的索引，可以看到PK_Product為聚集索引。

　　將普通錶轉換為分割槽表的操作是先在普通表上刪除聚集索引，在建立一個新的聚集索引，在該聚集索引中使用分割槽方案。

　　在SQL Server中，主鍵欄位上預設建立聚集索引，刪除主鍵的聚集索引。

ALTER TABLE Product DROP CONSTRAINT PK_Product

　　重新建立主鍵非聚集索引

ALTER TABLE Product ADD CONSTRAINT PK_Product PRIMARY KEY NONCLUSTERED (ProductID ASC)

　　重新建立後的主鍵：

　　建立使用分割槽方案的聚集索引：

CREATE CLUSTERED INDEX IX_CreateDate ON Product ( CreateDate )
ON Scheme_DateTime ( CreateDate )

　　調整後的Product表屬性：

　　調整後Product表記錄的物理儲存情況：

5. 刪除（合併）一個分割槽表

　　刪除2012-01-01的分割槽，修改分割槽函式：

ALTER PARTITION FUNCTION Function_DateTime() MERGE RANGE ('2012-01-01')

　　在修改了分割槽函式之後，與之關聯的分割槽方案也將同時自動調整。在執行了上面合併分割槽的函式之後，檢視分割槽方案的Create指令碼。

CREATE PARTITION SCHEME [Scheme_DateTime] AS PARTITION [Function_DateTime] TO ([SECTION2010], [SECTION2011], [SECTION2013], [SECTION2014])

　　合併分割槽之後，被合併的分割槽記錄也將被重新分配物理儲存位置。

6. 新增分割槽

　　分割槽方案中指定的檔案組個數比分割槽函式中指定的邊界數大1，為分割槽方案指定一個可用的檔案組時，該分割槽方案並沒有立刻使用這個檔案組，只是將檔案組先備用著，等修改了分割槽函式之後分割槽方案才會使用這個檔案組。如果分割槽函式沒有更改，分割槽方案中的檔案組個數也不會更改。

 　　新增分割槽所需要使用到的檔案組可以使用之前合併分割槽之後沒有再使用的SECTION2012，也可以新建檔案組。

ALTER DATABASE [Northwind] ADD FILEGROUP [SECTION2015]

ALTER DATABASE [Northwind] ADD FILE 
( 
    NAME = N'Northwind_Data_2015', 
    FILENAME = N'F:\Database\Northwind\Northwind_Data_2015.ndf' , 
    SIZE = 5120KB , 
    FILEGROWTH = 1024KB 
) TO FILEGROUP [SECTION2015]

　　為分割槽方案指定一個可用的檔案組：

ALTER PARTITION SCHEME Scheme_DateTime NEXT USED [SECTION2015]

　　修改分割槽函式，新增分割槽：

ALTER PARTITION FUNCTION Function_DateTime() SPLIT RANGE('2015-01-01')

　　檢視新增分割槽後的資料物理儲存：