SQL Server2005 表分割槽三步曲

--&gt Title  : SQL Server2005 表分割槽三步曲

--&gt Author : wufeng4552

--&gt Date   : 2009-10-26

 

前言

SQL Server 2005開始支援表分割槽，這種技術允許所有的表分割槽都儲存在同一臺伺服器上。每一個表分割槽都和在某個檔案組(filegroup)中的單個檔案關聯。同樣的一個檔案/檔案組可以容納多個分割槽表。在這種設計架構下，資料庫引擎能夠判定查詢過程中應該訪問哪個分割槽，而不用掃描整個表。如果查詢需要的資料行分散在多個分割槽中，SQL Server使用多個處理器對多個分割槽進行並行查詢。你可以為在建立表的時候就定義分割槽的索引。 對小索引的搜尋或者掃描要比掃描整個表或者一張大表上的索引要快很多。因此，當對大表進行查詢，表分割槽可以產生相當大的效能提升

通過分別檢查同一條返回所有行的、簡單SELECT語句在分割槽表和非分割槽表上的執行計劃，返回的資料範圍通過WHERE語句來指定。同一條語句在這兩個不同的表上有不同的執行計劃。對於分割槽表的查詢顯示出一個巢狀的迴圈和索引的掃描。從本質上來說，SQL Server將兩個分割槽視為獨立的表，因此使用一個巢狀迴圈將它們連線起來。對非分割槽的表的同一個查詢則使用索引掃描來返回同樣的列。當你使用同樣的分割槽策略建立多個表，同時在查詢中連線這些表，那麼效能上的提升會更加明顯

分割槽請三思

1.雖然分割槽可以帶來眾多的好處，但是同進也增加了實現物件的管理費用和複雜性。因此在進行分割槽之前要首先仔細的考慮以確定是否應為物件進行分割槽。

2.在確定了為物件進行分割槽後，下一步就要確定分割槽鍵和分割槽數。要確定分割槽資料，應先評估您的資料中是否存在邏輯分組和模式。

3.確定是否應使用多個檔案分組。為了有助於優化效能和維護，應使用檔案組分離資料。檔案組是資料庫資料檔案的邏輯組合，它可以對資料檔案進行管理和分配，以便提高資料庫檔案的併發訪問效率。

分割槽三步曲

SQL Server資料庫表分割槽操作過程由三個步驟組成：

1. 建立分割槽函式

2. 建立分割槽架構

3. 對錶進行分割槽

(一)：建立一個分割槽函式（邏輯結構）

   此分割槽函式用於定義你希望SQL Server如何對資料進行分割槽的引數值(how)。這個操作並不涉及任何表格，只是單純的定義了一項技術來分割資料。

--刪除表

if object_id('tb_partition1')is not null drop table tb_partition1

go

if object_id('tb_partition2')is not null drop table tb_partition2

go

--刪除架構

If exists(Select 1 from sys.partition_schemes where name='my_psch')

    drop partition scheme my_psch

go

--刪除分割槽函式

if exists(select 1 from sys.partition_functions where name='my_pfun')

    drop partition function my_pfun

--建立分割槽函式

create partition function my_Pfun(datetime)

as range left

for values('2007-12-31')

注意:這裡呼叫的"RANGE RIGHT"語句表明每個分割槽邊界值是右界。類似的，如果使用"RANGE LEFT"語句,則上述第一個分割槽應該包括所有值小於或等於'2004-01-01'資料,以此類推.

(二)：建立一個分割槽架構（物理結構）

   一旦給出描述如何分割資料的分割槽函式，接著就要建立一個分割槽架構，用來定義分割槽位置(where)。建立過程非常直截了當，只要將分割槽連線到指定的檔案組就行了。

--建立分割槽架構

go

create partition scheme my_psch

as partition my_pfun

to([Primary],[Primary])

/*
1，建立分割槽函式，分割槽方案是有先後順序的。
2，分割槽函式提供的值的數目n，不能超過 999。所建立的分割槽數等於 n + 1
*/

注意:這裡將一個分割槽函式連線到了該分割槽架構，但並沒有將分割槽架構連線到任何資料表。這就是可複用性起作用的地方了。無論有多少資料庫表,我們都可以使用該分割槽架構(或僅僅是分割槽函式)。

(三)：對一個表進行分割槽

   定義好一個分割槽架構後，就可以著手建立一個分割槽表了。只需要在表建立指令中新增一個"ON"語句，用來指定分割槽架構以及應用該架構的表列。因為分割槽架構已經識別了分割槽函式，所以不需要再指定分割槽函式了。

create table tb_partition1

(id int identity,

 dt datetime,

 [name]as 'Name'+ltrim(ID),

 constraint pk_tbpartition1 primary key clustered(ID,dt)on my_psch(dt)

)on my_psch(dt)

go

create table tb_partition2

(id int identity,

 dt datetime,

 [name]as 'Name'+ltrim(ID),

 constraint pk_tbpartition2 primary key clustered(ID,dt)on my_psch(dt)

)on my_psch(dt)
--為此表填充數

declare @dt datetime

set @dt='2007-01-01'

while @dt<='2009-10-01'

   begin

     insert tb_partition1 select @dt

     set @dt=dateadd(dd,1,@dt)

   end

--查詢表的分割槽狀況 Select * from sys.partitions

Where object_id In

(Select object_id From sys.tables Where name In('tb_partition1','tb_partition2'))

---- 現在我們可以看一下我們剛才插入的行都分佈在哪個Partition
select *,$partition.my_pfun(dt) from tb_partition1

--切換分割槽

--切換前

select * from tb_partition1

select * from tb_partition2

----切換分割槽tb_partition1--&gttb_partition2

Alter Table tb_partition1

Switch Partition 2 To tb_partition2 Partition 2

go

select * from tb_partition1

select * from tb_partition2

修改三步曲
1 新增一個檔案組到資料庫(可選)
2 修改分割槽Scheme
3 修改分割槽函式

--1 新增一個檔案組到資料庫
--2 修改分割槽Scheme
--把大於/12/31的分割槽改分為2個分割槽
--3 修改分割槽函式
Alter Partition Function My_pfun()

Alter Partition Scheme my_psch

Next Used [Primary]

go

Split Range('2008/12/31')

Go

--把小於2008/12/31的兩分割槽合併

Alter Partition Function My_PFun()

Merge Range('2007/12/31')

 

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/wufeng4552/archive/2009/10/26/4728248.aspx

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