您當前位置：首頁 > 數據庫 > Sqlserver > SQL Server表分區(一):基礎知識和實現方式

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

來源：程序員人生發布時間：2013-10-27 10:43:13 閱讀次數：3637次

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:TestTablePartitionTestTablePartitionDB.mdf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG1]
(
Name='FG1',
FileName='D:TestTablePartitionFG1.ndf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG2]
(
Name='FG2',
FileName='D:TestTablePartitionFG2.ndf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG3]
(
Name='FG3',
FileName='D:TestTablePartitionFG3.ndf',
Size=5,
FileGrowth=1
),
FILEGROUP [FG4]
(
Name='FG4',
FileName='D:TestTablePartitionFG4.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) )