SQL Server表分區(一):基礎知識和實現方式

1、什麼是表分區？
SQL Server使用三種不同類型的檔案儲存體資料，它們分別是.mdf、.ndf和.ldf。主要資料存放區在
.mdf檔案中，比如表，索引，預存程序等。.ndf檔案也用於儲存這些資料。.ldf檔案用於儲存動作記錄。
表預設儲存在.mdf檔案中。更新表時SQL Server會對錶鎖。那麼其它的操作必須等待正在更新操作完成。
如果一個表很大，那麼無論是查詢操作還是更新操作，效能都會很差。如果將表分別儲存在物理上獨立但邏輯上連續的
分區中，那麼SQL Server可以大幅最佳化查詢操作和更新操作的效能。

2、如何?表分區？
1)確定分區策略，即劃分資料的邏輯規則。比如將整形值小於100的資料放在一個分區上，將大於等於100的資料放在第二個
分區上，等等。
SQL Server中的使用分區函數來實現分區策略：

CREATE PARTITION FUNCTION PF_Order(DATETIME)
AS RANGE RIGHT FOR VALUES
('20090101','20100101','20110101')

2)確定分區儲存架構，即劃分資料的實體儲存體規則。最終分區可以儲存在主檔案組，即.mdf檔案中，也可以儲存在非主檔案組，
即.ndf檔案中。
SQL Server中的使用分區架構來實現分區儲存架構：

CREATE PARTITION SCHEME PS_Order
AS PARTITION PF_Order
TO ([FG1],[FG2],[FG3],[FG4])

3）將表建立在分區儲存架構上，並指定使用哪一列做為劃分資料的依據。注意做為劃分資料依據的列必須在叢集索引中
在更新資料時，SQL Server首先使用列的資料做為參數調用分區函數確定應該將該資料存放區在哪個檔案組（邏輯分區），
然後將資料寫入檔案組對應的物理檔案中。

SQL Server中建立分區表:

CREATE TABLE dbo.[Order]
(
OrderID BIGINT IDENTITY(1,1),
OrderDate DATETIME NOT NULL,
CONSTRAINT PK_Order_OrderID_OrderDate PRIMARY KEY CLUSTERED(OrderID,OrderDate)
)
ON PS_Order(OrderDate)

 

下面是詳細的範例程式碼：

--表分區測試代碼
USE [master]
GO

--建立具有多個檔案組的測試資料庫
IF EXISTS(SELECT 1 FROM sys.databases WHERE [name]=N'TestTablePartitionDB')
BEGIN
    DROP DATABASE [TestTablePartitionDB]
END

CREATE DATABASE [TestTablePartitionDB]
ON PRIMARY
(
Name='PrimaryFG',
FileName='D:\Test\TablePartition\TestTablePartitionDB.mdf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG1]
(
Name='FG1',
FileName='D:\Test\TablePartition\FG1.ndf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG2]
(
Name='FG2',
FileName='D:\Test\TablePartition\FG2.ndf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG3]
(
Name='FG3',
FileName='D:\Test\TablePartition\FG3.ndf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG4]
(
Name='FG4',
FileName='D:\Test\TablePartition\FG4.ndf',
Size=5,
FileGrowth=1
)

USE TestTablePartitionDB
GO

--建立分區函數
CREATE PARTITION FUNCTION PF_Order(DATETIME)
AS RANGE LEFT FOR VALUES
('20090101','20100101','20110101')

--建立分區架構
CREATE PARTITION SCHEME PS_Order
AS PARTITION PF_Order
TO([FG1],[FG2],[FG3],[FG4])

--建立分區表
CREATE TABLE dbo.[Order]
(
OrderID BIGINT IDENTITY(1,1),
OrderDate DATETIME NOT NULL,
CONSTRAINT PK_Order_OrderID_OrderDate PRIMARY KEY CLUSTERED(OrderID,OrderDate)
)
ON PS_Order(OrderDate)

--測試資料的邏輯分區是哪個
SELECT $PARTITION.PF_Order('20090101')

--測試分區表的分區情況
SELECT partition_number,rows
FROM sys.partitions
WHERE [object_id]=object_id(N'dbo.[Order]')

--Drop index MyTable_IXC on MyTable with (Move To [Data Partition Scheme] (ID) )