SQL Server2005 表分區三步曲

2019-11-03 08:36:10

字體：大中小

來源：轉載

供稿：網友

前言

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-->tb_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')

上一篇： SQL2005中的事務與鎖定（七）

下一篇： MySQL處理同時存在order by 和 limit 語句時出現的bug