SQL Server 表分割槽(partitioned table/Data Partitioning)

可伸縮性性是資料庫管理系統的一個很重要的方面，在SQL Server 2005中可伸縮性方面提供了表分割槽功能。

其實對於有關係弄資料庫產品來說，對錶、資料庫和伺服器進行資料分割槽的從而提供大資料量的支援並不是什麼新鮮事，但 SQL Server 2005 提供了一個新的體系結構功能，用於對資料庫中的檔案組進行表分割槽。水平分割槽可根據分割槽架構，將一個表劃分為幾個較小的分組。表分割槽功能是針對超大型資料庫（從數百吉位元組到數千吉位元組或更大）而設計的。超大型資料庫 (VLDB) 查詢效能通過分割槽得到了改善。通過對廣大分割槽列值進行分割槽，可以對資料的子集進行管理，並將其快速、高效地重新分配給其他表。

設想一個大致的電子交易網站，有一個表儲存了此網站的歷史交易資料，這此資料量可能有上億條，在以前的SQL Server版本中儲存在一個表中不管對於查詢效能還是維護都是件麻煩事，下面我們來看一下在SQL Server2005怎麼提高效能和可管理性：

-- 建立要使用的測試資料庫，Demo

 

USE [master]

IF  EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'DEMO')

DROP DATABASE [DEMO]

 

CREATE DATABASE [DEMO]

--由於表分割槽使用使用新的體系結構，使用檔案組來進行表分割槽，所以我們建立將要用到的6個檔案組，來儲存6個時間段的交易資料[<2000],[ 2001], [2002], [2003], [2004], [>2005]

ALTER DATABASE Demo ADD FILEGROUP YEARFG1;

ALTER DATABASE Demo ADD FILEGROUP YEARFG2;

ALTER DATABASE Demo ADD FILEGROUP YEARFG3;

ALTER DATABASE Demo ADD FILEGROUP YEARFG4;

ALTER DATABASE Demo ADD FILEGROUP YEARFG5;

ALTER DATABASE Demo ADD FILEGROUP YEARFG6;

 

-- 下面為這些檔案組新增檔案來進行物理的資料儲存

ALTER DATABASE Demo ADD FILE (NAME = 'YEARF1', FILENAME = 'C:\ADVWORKSF1.NDF') TO FILEGROUP YEARFG1;

ALTER DATABASE Demo ADD FILE (NAME = 'YEARF2', FILENAME = 'C:\ADVWORKSF2.NDF') TO FILEGROUP YEARFG2;

ALTER DATABASE Demo ADD FILE (NAME = 'YEARF3', FILENAME = 'C:\ADVWORKSF3.NDF') TO FILEGROUP YEARFG3;

ALTER DATABASE Demo ADD FILE (NAME = 'YEARF4', FILENAME = 'C:\ADVWORKSF4.NDF') TO FILEGROUP YEARFG4;

ALTER DATABASE Demo ADD FILE (NAME = 'YEARF5', FILENAME = 'C:\ADVWORKSF5.NDF') TO FILEGROUP YEARFG5;

ALTER DATABASE Demo ADD FILE (NAME = 'YEARF6', FILENAME = 'C:\ADVWORKSF6.NDF') TO FILEGROUP YEARFG6;

-- HERE WE ASSOCIATE THE PARTITION FUNCTION TO

-- THE CREATED FILEGROUP VIA A PARTITIONING SCHEME

USE DEMO;

GO

 

-------------------------------------------------------

-- 建立分割槽函式

-------------------------------------------------------

CREATE PARTITION FUNCTION YEARPF(datetime)

AS

RANGE LEFT FOR VALUES ('01/01/2000'

                                          ,'01/01/2001'

                                          ,'01/01/2002'

                                          ,'01/01/2003'

                                          ,'01/01/2004')

-------------------------------------------------------

-- 建立分割槽架構

-------------------------------------------------------

CREATE PARTITION SCHEME YEARPS

AS PARTITION YEARPF TO (YEARFG1, YEARFG2,YEARFG3,YEARFG4,YEARFG5,YEARFG6)

 

-- 建立使用此Schema的表

CREATE TABLE PARTITIONEDORDERS

(

ID INT NOT NULL IDENTITY(1,1),

DUEDATE DATETIME NOT NULL,

) ON YEARPS(DUEDATE)

 

--為此表填充資料

declare @DT datetime

SELECT @DT = '1999-01-01'

 

--start looping, stop at ending date

WHILE (@DT <= '2005-12-21')

BEGIN

       INSERT INTO PARTITIONEDORDERS VALUES(@DT)

       SET @DT=dateadd(yy,1,@DT)

END

 

-- 現在我們可以看一下我們剛才插入的行都分佈在哪個Partition

SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS

 

--我們可以看一下我們現在PARTITIONEDORDERS表的資料儲存在哪此partition中，以及在這些分割槽中資料量的分佈

SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')

 

--

--現在我們設想一下，如果我們隨著時間的流逝，現在已經到了2005年，按照我們先前的設定，我們想再想入一個分割槽，這時是不是重新建立表分割槽架構然後重新把資料導放到新的分割槽架構呢，答案是完全不用。下面我們就看如果新加一個分割槽。

--更改分割槽架構定義語言，讓下一個分割槽使用和現在已經存在的分割槽YEARFG6分割槽中，這樣此分割槽就儲存了兩段partition的資料。

ALTER PARTITION SCHEME YEARPS

NEXT USED YEARFG6;

 

--更改分割槽函式

ALTER PARTITION FUNCTION YEARPF()

SPLIT RANGE ('01/01/2005') 

 

--現在我們可以看一下我們剛才插入的行都分佈在哪個Partition？

SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS

 

--我們可以看一下我們現在PARTITIONEDORDERS表的資料儲存在哪此partition中，以及在這些分割槽中資料量的分佈

SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS') 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-624446/，如需轉載，請註明出處，否則將追究法律責任。