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