SQL Server: 如何建立 Partition Table

一. 前言

因為客戶有一個上幾百萬筆資料的 Table, 需評估 Partition Table 的可行性, 在評估的過程中, 順便將建立 Partition Table 的過程, 記錄下來.

本文主要係以參考文件 1.. 及 2.. 作為主要的實作依據, 也謝謝參考文件所列各篇文章的作者.

整個實作過程, 包含以下幾個步驟:

1.. 建立資料庫及 FileGroup, 並切換至新建的資料庫

2.. 建立非分割的資料表, 以供對照之用

3.. 建立輔助函式 Function (from 參考文件 2.. 及 8..)

4.. 建立分割函式 ( Partition Function )

5.. 建立分割配置 ( Partition Scheme )

6.. 建立分割資料表/索引及建立測試資料 (Partition Table)

7.. 驗證測試資料

8.. 新年度怎麼辦?

9.. 驗證測試資料

註: Partition Table 的功能只有 Enterprise / Developer / Evaluation 版本才有提供.

二. 實作過程

1.. 建立資料庫及 FileGroup, 並切換至新建的資料庫

-- ====================================================
-- 1.. 建立 資料庫 及 FileGroup, 並切換至新建的資料庫
-- ====================================================
-- 建立 資料庫 及 FileGroup
CREATE DATABASE [MyDBTest] ON
PRIMARY ( NAME = N'MyDBTest', FILENAME = N'C:\Temp\MyDBTest.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [FG1] ( NAME = N'MyDBTest2010', FILENAME = N'C:\Temp\MyDBTest2010.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [FG2] ( NAME = N'MyDBTest2011', FILENAME = N'C:\Temp\MyDBTest2011.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [FG3] ( NAME = N'MyDBTest2012', FILENAME = N'C:\Temp\MyDBTest2012.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [FG4] ( NAME = N'MyDBTest2013', FILENAME = N'C:\Temp\MyDBTest2013.ndf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON ( NAME = N'MyDBTest_log', FILENAME = N'C:\Temp\MyDBTest_log.ldf' , SIZE = 1024KB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%)
GO

-- 切換資料庫至 MyDBTest
USE MyDBTest
GO

執行結果如下:

2.. 建立非分割的資料表, 以供對照之用

-- Orders
DROP TABLES Orders
GO

CREATE TABLE Orders(
    [OrderDate] [datetime] NOT NULL,
    [OrderSeq] [int] NOT NULL,
    [CustomerID] [nvarchar](20) NOT NULL,
    [EmployeeID] [int] NOT NULL,
    [ShipName] [nvarchar](40) NULL,
    [ShipAddress] [nvarchar](60) NULL,
) ON [PRIMARY]
GO

ALTER TABLE Orders
ADD CONSTRAINT PK_Orders PRIMARY KEY (OrderDate, OrderSeq)
GO

-- 建立測試資料
INSERT Orders VALUES('2010-10-05',1,'IBM'  ,1 ,'IBM'  ,'台北市大安區');
INSERT Orders VALUES('2011-08-31',1,'Microsoft' ,2 ,'MS'  ,'台北市信義區');
INSERT Orders VALUES('2012-06-20',1,'Oracle' ,3 ,'ORACLE' ,'台北市南港區');
INSERT Orders VALUES('2013-04-15',1,'Tableau' ,4 ,'TB'  ,'台北市內湖區');
GO

-- 查詢出來看一下
SELECT * 
FROM Orders;
GO

執行結果如下:

3.. 建立輔助函式 Function (from 參考文件 2.. 及 8..)

-- ----------------------
-- 建立 PartitionInfo 函式  (from 參考文件 2..)
-- ----------------------
-- 目的: 列出傳入 Table名稱 的各 Partition 的切割臨界值 / 所佔Size / 資料筆數 
-- 呼叫範例:
-- SELECT * FROM PartitionInfo('dbo.Orders');
-- SELECT * FROM PartitionInfo('dbo.OrdersP');
DROP FUNCTION PartitionInfo
GO

CREATE FUNCTION PartitionInfo( @tablename sysname ) RETURNS table
AS RETURN
 SELECT 
 OBJECT_NAME(p.object_id) as TableName
 ,p.partition_number as PartitionNumber
 ,prv_left.value as LowerBoundary
 ,prv_right.value as  UpperBoundary
 ,ps.name as PartitionScheme
 ,pf.name as PartitionFunction
 ,fg.name as FileGroupName
 ,CAST(p.used_page_count * 8.0 / 1024 AS NUMERIC(18,2)) AS UsedPages_MB
 ,p.row_count as Rows
 FROM  sys.dm_db_partition_stats p
 INNER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
 INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
 INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
 INNER JOIN sys.destination_data_spaces dds ON dds.partition_scheme_id = ps.data_space_id AND dds.destination_id = p.partition_number
 INNER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
 LEFT  JOIN sys.partition_range_values prv_right ON prv_right.function_id = ps.function_id AND prv_right.boundary_id = p.partition_number
 LEFT  JOIN sys.partition_range_values prv_left  ON prv_left.function_id = ps.function_id AND prv_left.boundary_id = p.partition_number - 1
 WHERE 
 p.object_id = OBJECT_ID(@tablename) and p.index_id < 2
GO

-- ----------------------
-- 建立輔助函式 IndexInfo (from 參考文件 8..)
-- ----------------------
-- 目的: 列出傳入 Table名稱 各個 Index 所在的 File Group 及資料筆數
-- 呼叫範例:
-- SELECT * FROM IndexInfo('Orders');
-- SELECT * FROM IndexInfo('OrdersP');
DROP FUNCTION IndexInfo
GO

CREATE FUNCTION IndexInfo( @tablename sysname ) RETURNS table
AS RETURN
 SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name
 ,t.name AS table_name
 ,i.index_id
 ,i.name AS index_name
 ,p.partition_number
 ,fg.name AS filegroup_name
 ,FORMAT(p.rows, '#,###') AS rows
 FROM sys.tables t
 INNER JOIN sys.indexes i ON t.object_id = i.object_id
 INNER JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
 LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
 LEFT OUTER JOIN sys.destination_data_spaces dds ON ps.data_space_id=dds.partition_scheme_id AND p.partition_number=dds.destination_id
 INNER JOIN sys.filegroups fg ON COALESCE(dds.data_space_id, i.data_space_id)=fg.data_space_id
 WHERE t.name = @tablename
GO

執行結果如下:

4.. 建立分割函式 ( Partition Function )

目的: 建立邏輯上資料分割的規則

DROP PARTITION FUNCTION P_Func
GO

CREATE PARTITION FUNCTION P_Func(datetime)
AS RANGE RIGHT
FOR VALUES ('2011/01/01', '2012/01/01', '2013/01/01');
GO
/*
這個範例使用三個 datetime 型別的值來間隔分割區。
       2011/1/1    2012/1/1    2013/1/1 
   r1     ↓    r2    ↓    r3    ↓    r4
----------。----------。----------。----------
RIGHT 數字表示間隔值本身包含在右邊區間，所以四個區間的範圍如下：
 
PartitionNumber Partition Range
=============== ===================================
1                              range1 < 2011/01/01
2               2011/01/01 <=  range2 < 2012/01/01
3               2012/01/01 <=  range3 < 2013/01/01
4               2013/01/01 <=  range4 
*/

執行結果如下:

注意: 在 datetime 資料型別採用 RANGE RIGHT的原因.

如果 datetime 純粹只有日期的部份, 不會出問題,

如果含有時間, 就可能出現以下的狀況, 假定分割函式改為 RANGE LEFT:

AS RANGE LEFT

FOR VALUES ('2010/12/31', '2011/12/31', '2012/12/31');

這個範例使用三個 datetime 型別的值來間隔分割區。

2010/12/31 2011/12/31 2012/12/31

r1 ↓ r2 ↓ r3 ↓ r4

----------。----------。----------。----------

LEFT 數字表示間隔值本身包含在左邊區間，所以四個區間的範圍如下：

PartitionNumber Partition Range

=============== ===================================

1 range1 <= 2010/12/31

2 2010/12/31 < range2 <= 2011/12/31

3 2011/13/31 < range3 <= 2012/12/31

4 2012/12/31 < range4

如果資料是 2010/12/31 13:30 會落在那裡呢, 答案是 range2, 而不是 range1 , 這點要特別注恴

5.. 建立分割配置 ( Partition Scheme )

目的: 邏輯資料切割與實體檔案群組的對應

-- 四個分割都使用不同的檔案群組
DROP PARTITION SCHEME P_Scheme
GO

CREATE PARTITION SCHEME P_Scheme
AS PARTITION P_Func TO (FG1, FG2, FG3, FG4);
GO

執行結果如下:

6.. 建立分割資料表/索引及建立測試資料 (Partition Table)

-- OrdersP
DROP TABLE OrdersP
GO

CREATE TABLE OrdersP(
    [OrderDate] [datetime] NOT NULL,
    [OrderSeq] [int] NOT NULL,
    [CustomerID] [nvarchar](20) NOT NULL,
    [EmployeeID] [int] NOT NULL,
    [ShipName] [nvarchar](40) NULL,
    [ShipAddress] [nvarchar](60) NULL,
) ON P_Scheme(OrderDate)
GO

ALTER TABLE OrdersP
ADD CONSTRAINT PK_OrdersP PRIMARY KEY (OrderDate, OrderSeq)
GO

-- Partitioned Non-Clustered INDEX, 欄位與 PKEY 相同, 主要是在驗證其所在的 FileGroup
CREATE UNIQUE NONCLUSTERED INDEX [UK_OrdersP_ON_P_Scheme] 
ON [OrdersP] ( [OrderDate] ASC, [OrderSeq] ASC ) 
ON [P_Scheme]([OrderDate])
GO

-- Non-Partitioned Non-Clustered INDEX, 欄位與 PKEY 相同, 主要是在驗證其所在的 FileGroup
CREATE UNIQUE NONCLUSTERED INDEX [UK_OrdersP_ON_Primary] 
ON [OrdersP] ( [OrderDate] ASC, [OrderSeq] ASC ) 
ON [Primary]
GO


-- 建立測試資料
INSERT OrdersP VALUES('2010-10-05',1,'IBM'  ,1 ,'IBM'  ,'台北市大安區');
INSERT OrdersP VALUES('2011-08-31',1,'Microsoft' ,2 ,'MS'  ,'台北市信義區');
INSERT OrdersP VALUES('2012-06-20',1,'Oracle' ,3 ,'ORACLE' ,'台北市南港區');
INSERT OrdersP VALUES('2013-04-15',1,'Tableau' ,4 ,'TB'  ,'台北市內湖區');
GO

-- 查詢出來看一下
SELECT * 
FROM OrdersP;
GO

執行結果如下:

注意: 建立分割資料表 Unique Index 的注意事項

Technet: https://technet.microsoft.com/en-us/library/ms187526(v=sql.105).aspx

* When partitioning a unique index (clustered or nonclustered), the partitioning column

must be chosen from among those used in the unique index key.

* If it is not possible for the partitioning column to be included in the unique key,

you must use a DML trigger instead to enforce uniqueness.

* 建立 Unique Index 時, 分割資料表的欄位, 必須為 Unique Index 欄位裡的一部份; 不然就要寫 trigger 去強制 unique

7.. 驗證測試資料

-- OrdersP
SELECT YEAR(OrderDate) as YYYY , COUNT(1) 
FROM OrdersP
GROUP BY YEAR(OrderDate)
GO
-- PartitionInfo(...)
SELECT * FROM PartitionInfo('dbo.OrdersP');
SELECT * FROM PartitionInfo('dbo.Orders');
GO
-- IndexInfo(...)
SELECT * FROM IndexInfo('OrdersP');
SELECT * FROM IndexInfo('Orders');
GO

執行結果如下:

OrdersP 有 Partition, Orders 沒有 Partition

OrdersP 的 Index 中, 除了原來指定要放在 PRIMARY 的 UK_OrdersP_ON_Primary 之外, 其它都分別放在 4 個指定的 FileGroup

Orders 的 Index, 則完全都是在 PRIMARY 這個 FileGroup

8.. 新年度怎麼辦?

隨著時間的推進, 快要跨年了, 要怎麼辦? 共計以下4個步驟

(1) 在資料庫新增檔案群組

(2) 加入一個檔案到 FG5 檔案群組

(3) 建立新的分割區

(4) 加入一筆資料試試

-- ------------------------------
-- 新增分割區, 以存放新年度的資料
-- ------------------------------

--在資料庫新增檔案群組
ALTER DATABASE MyDBTest
ADD FILEGROUP FG5;
GO
 
--加入一個檔案到 FG5 檔案群組
ALTER DATABASE MyDBTest 
ADD FILE 
(
    NAME = N'MyDBTest2014',
    FILENAME = 'C:\Temp\MyDBTest2014.ndf',
    SIZE = 5120KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1024KB
 
)
TO FILEGROUP FG5;
GO

-- 建立新的分割區
ALTER PARTITION SCHEME P_Scheme NEXT USED [FG5];                --指定下個新分割區要使用的檔案群組
ALTER PARTITION FUNCTION P_Func() SPLIT RANGE ('2014/01/01');   --新分割區
GO

-- 加入資料
INSERT OrdersP VALUES('2014-04-15',1,'SYSCOM' ,3 ,'SYSCOM'  ,'台北市萬華區');
GO

執行結果如下:

9.. 驗證測試資料

-- OrdersP
SELECT YEAR(OrderDate) as YYYY , COUNT(1) 
FROM OrdersP
GROUP BY YEAR(OrderDate)
GO
-- PartitionInfo(...)
SELECT * FROM PartitionInfo('dbo.OrdersP');
SELECT * FROM PartitionInfo('dbo.Orders');
GO
-- IndexInfo(...)
SELECT * FROM IndexInfo('OrdersP');
SELECT * FROM IndexInfo('Orders');
GO

執行結果如下:

OrdersP 加入了一個的區段 ( >= 2014/01/01)

OrdersP 的 Index 中, PK_OrdersP 與 UK_OrdersP_ON_P_Scheme 都增加了一個檔案群組 (FG5) 可以使用

三. 參考文件:

1.. VITO の學習筆記, 效能調校(5)-使用資料表分割

==> 這篇是以 TSQL 進行 Partition Table 的建置, 比較淺顯易懂

2.. 五餅二魚工作室, [SQL]Partition Table 的實作筆記

==> 這篇是以 TSQL 進行 Partition Table 的建置, 採用比較多的 SQL 語法, 並自建一個檢測 Function: PartitionInfo( @tablename sysname ) returns table

3.. tolarku (黃昏的甘蔗), 『SQL』分割資料表 Partition Table

==> 這篇是以 SSMS (SQL Server Management Studio) 進行操作, 以建立 Partition Table

4.. MSDN, Create Partitioned Tables and Indexes

==> 基本觀念及操作, 但承襲 MSDN 的傳統, 很多文字, 沒有圖

5.. MSDN, CREATE PARTITION FUNCTION

==> 邏輯上的資料切割, 例如: 一個 integer 欄位, 以 1, 100, 100 作區分; 共有 4 個區間; x <=1, 1< x <=100, <100 x <= 1000, x > 1000

6.. MSDN, CREATE PARTITION SCHEME

==> 邏輯資料切割與實體檔案群組的對應, 例如:

x <=1 放在 filegroup1
1 < x <=100 放在 filegroup2
100 < x <=1000 放在 filegroup3,
x >1000 放在 filegroup4

==> 而 filegroupN 會對應到實際作業系統上的檔案

7.. MSDN, Table and Index Organization

==> 有圖, 比較容易看

==> 說明 Table 及 Index 的結構, 包括:

Clustered Table: tables that have a clustered index
Heap Table: tables that have not a clustered index
Clustered Index: implemented as a B-tree index structure that supports fast retrieval of the rows, based on their clustered index key values.
NonClustered Index: have a B-tree index structure similar to the one in clustered indexes. The difference is that nonclustered indexes do not affect the order of the data rows.

==> sys.partition 這個 Table 裡的 index_id 欄位值的說明

A heap has a row in sys.partitions with index_id = 0
A clustered index on a table or a view has a row in sys.partitions with index_id = 1
Each nonclustered index created for a table or a view has a row in sys.partitions with index_id > 1

8.. Strate SQL, Determining Filegroup For A Table

==> 將各個 Table 的 Index 所在的 FileGroup 列示出來

傑士伯的IT學習之路

2016年10月20日星期四