好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

SQLServer表分区整理

1. 前言 SQL Server 2005开始支持表 分区 ,这种技术允许所有的表 分区 都保存在同一台服务器上。每一个表 分区 都和在某个文件组(filegroup)中的单个文件关联。同样的一个文件/文件组可以容纳多个 分区 表。在这种设计架构下,数据库引擎能够判定查询过程中

1. 前言

SQL Server 2005开始支持表 分区 ,这种技术允许所有的表 分区 都保存在同一台服务器上。每一个表 分区 都和在某个文件组(filegroup)中的单个文件关联。同样的一个文件/文件组可以容纳多个 分区 表。在这种设计架构下,数据库引擎能够判定查询过程中应该访问哪个 分区 ,而不用扫描整个表。如果查询需要的数据行分散在多个 分区 中,SQL Server使用多个处理器对多个 分区 进行并行查询。你可以为在创建表的时候就定义 分区 的索引。对小索引的搜索或者扫描要比扫描整个表或者一张大表上的索引要快很多。因此,当对大表进行查询,表 分区 可以产生相当大的性能提升通过分别检查同一条返回所有行的、简单SELECT语句在 分区 表和非 分区 表上的执行计划,返回的数据范围通过WHERE语句来指定。同一条语句在这两个不同的表上有不同的执行计划。对于 分区 表的查询显示出一个嵌套的循环和索引的扫描。从本质上来说,SQL Server将两个 分区 视为独立的表,因此使用一个嵌套循环将它们连接起来。对非 分区 的表的同一个查询则使用索引扫描来返回同样的列。当你使用同样的 分区 策略创建多个表,同时在查询中连接这些表,那么性能上的提升会更加明显。

2. 分区 三步曲

SQL Server数据库表 分区 操作过程由三个步骤组成

2.1. 创建 分区 函数

2.1.1. 创建文件组, 一般文件组个数 = 分区 值个数 +1

alter database [mydatabase] -- 创建文件组 1
add filegroup [fg_tb_partition_id_1]
go
alter database [mydatabase] -- 创建文件组 2
add filegroup [fg_tb_partition_id_2]
go
alter database [mydatabase] -- 创建文件组 3
add filegroup [fg_tb_partition_id_3]
go


2.1.2. 为数据库创建文件

一个文件不能属于两个文件组,一个文件组可以包含多个文件,可以同时指定初始化大小及

增长大小。

alter database [mydatabase]
add file
(name=N 'fg_tb_partition_id_1_data' ,
filename=N 'D:\dbbackup\fg_tb_partition_id_1_data.ndf' ,
size =30mb,filegrowth= 10 %)
to filegroup [fg_tb_partition_id_1]
go
alter database [mydatabase]
add file
(name=N 'fg_tb_partition_id_2_data' ,
filename=N 'D:\dbbackup\fg_tb_partition_id_2_data.ndf' ,
size =30mb,filegrowth= 10 %)
to filegroup [fg_tb_partition_id_2]
go
alter database [mydatabase]
add file
(name=N 'fg_tb_partition_id_3_data' ,
filename=N 'D:\dbbackup\fg_tb_partition_id_3_data.ndf' ,
size =30mb,filegrowth= 10 %)
to filegroup [fg_tb_partition_id_3]
go

2.1.3. 创建 分区 函数

此 分区 函数用于定义你希望 SQL Server如何对数据进行 分区 的参数值(how)。这个操作并不涉及任何表格,只是单纯的定义了一项技术来分割数据.

create partition function
fun_tb_partition_id( int ) as
range right
for values ( 10000 , 20000 )

注意:

这里使用了右 分区 则表示 分区 取值范围为

属于第一 分区

〉 =10000 And 属于第二 分区

〉 =20000 属于第三 分区

2.2. 创建 分区 架构

一旦给出描述如何分割数据的 分区 函数,接着就要创建一个 分区 架构,用来定义 分区 位置 (where)。

create partition scheme
sch_tb_partition_id as
partition fun_tb_partition_id
to ([fg_tb_partition_id_1],[fg_tb_partition_id_2],[fg_tb_partition_id_3])

2.3. 对表进行 分区

定义好一个 分区 架构后,就可以着手创建一个 分区 表了。只需要在表创建指令中添加一个

"ON"语句,用来指定 分区 架构以及应用该架构的表列。因为 分区 架构已经识别了 分区 函数,

所以不需要再指定 分区 函数了。

create table [dbo].[tb_partition1](
[id] [ int ] identity ( 1 , 1 ) not null ,
[username] as 'name' + ltrim ([id]),
[age] [ int ] null constraint [df_ tb_partition1_age] default (( 0 )),
constraint [pk_tb_partition1] primary key clustered
(
[id] asc
) with ( pad_index = on , fillfactor = 100 ) on [sch_tb_partition_id](id)
) on [sch_tb_partition_id]([id])
go
create table [dbo].[tb_partition2](
[id] [ int ] identity ( 1 , 1 ) not null ,
[username] as 'name' + ltrim ([id]),
[age] [ int ] null constraint [df_tb_partition2_age] default (( 0 )),
constraint [pk_tb_partition2] primary key clustered
(
[id] asc
) with ( pad_index = on , fillfactor = 100 ) on [sch_tb_partition_id](id)
) on [sch_tb_partition_id]([id])

2.4. 填充测试数据,并进行合并与删除操作

2.4.1. 填充数据

insert tb_partition1 default values
go 30005

2.4.2. 查看数据 分区 状况

select
$partition.fun_tb_partition_id(id) as partition_num,
min (id) as min_value,
max (id) as max_value,
count ( 1 ) as record_num
from [dbo].[tb_partition1]
group by $partition.fun_tb_partition_id(id)
order by $partition.fun_tb_partition_id(id)

2.4.3. 切换 分区

alter table [dbo].[tb_partition1]
switch partition 1 to [dbo].[tb_partition2] partition 1
-- 查看结果
select * from [dbo].[tb_partition1]
select * from [dbo].[tb_partition2]

2.4.4. 修改 分区 架构和 分区 函数

alter partition scheme [sch_tb_partition_id]
next used [fg_tb_partition_id_1]
go
alter partition function [fun_tb_partition_id] ()
split range( 15000 )

3. 表 分区 注意事项

3.1. 表 分区 边界值问题

使用 left和right时候需要注意,特别是时间分割上,通常使用以0.000最可靠,这种分割需要使用right如果使用left需要设置为23:59:59.997。

3.2. 分区 值第一个值

符合这个值之前的值会被分配到第一个 分区 中,使用 left和right的区别就是这个 分区 值是被分配到第一个 分区 还是第二个 分区 。

3.3. 通常情况会以 ID( 自增 ) 或时间字段作为 分区 字段

这样的好处就是容易区分历史数据库,而且对 分区 操作隔离也是最明显的。

3.4. 索引 分区

对 聚集索引 进行 分区 时,聚集键必需包含 分区 依据列。

对于 非唯一的聚集索引 进行 分区 时,如果未在聚集索引键中指定 分区 依据列,默认情况下 SQLServer 将在聚集索引键列表中添加 分区 依据列。如果 聚集索引是唯一 的,则必需明确指定聚集索引键包含 分区 依据列。

对 唯一的非聚集索引 进行 分区 时,索引键必需包含 分区 依据列,对 非唯一的非聚集索引 进行 分区 ,默认情况下 SQLServer将 分区 依据列添加为索引的非键列(包含性列),以确保索引与基表对齐。

3.5. 删除 分区

删除的这个边界值属于哪个 分区 就会删除这个 分区 ,再向临近 (以这个边界值为临界点的两个 分区 )的 分区 合并。

3.6. 索引对齐

索引对齐 :如果你想让数据分开到不同的文件可以使用两个不同的 分区 方案,使用同一 分区 函数。

存储位置对齐 :数据和索引位于同一文件中

4. 动态生成 分区 脚本

-- 分区 脚本

-- 定义变量
declare @databasename nvarchar ( 50 ) -- 数据库名称
declare @tablename nvarchar ( 50 ) -- 表名称
declare @columnname nvarchar ( 50 ) -- 字段名称
declare @partnumber int -- 需要分多少个区
declare @location nvarchar ( 50 ) -- 保存 分区 文件的路径
declare @size nvarchar ( 50 ) -- 分区 初始化大小
declare @filegrowth nvarchar ( 50 ) -- 分区 文件增量
declare @funvalue datetime -- 分区 分段值
declare @i int
declare @partnumberstr nvarchar ( 50 )
declare @sql nvarchar ( max )
-- 变量赋值
set @databasename = 'mydatabase'
set @tablename = 'table_name'
set @columnname = 'id'
set @partnumber = 4
set @location = 'e:\database\'
set @size = '30mb'
set @filegrowth = '10%'
set @funvalue = '20120101'
--1. 创建文件组
set @i = 1
while @i begin
set @partnumberstr = right ( '0' + convert ( nvarchar ,@i), 2 )
set @sql = 'alter database [' +@databasename + ']
add filegroup [fg_ '+@tablename+' _ '+@columnname+' _ '+@partnumberstr+' ] '
print @sql + char ( 13 )
set @i=@i+ 1
end
--2. 创建文件
set @i = 1
while @i begin
set @partnumberstr = right ( '0' + convert ( nvarchar ,@i), 2 )
set @sql = 'alter database [' +@databasename + ']
add file
(name = n '' fg_ '+@tablename+' _ '+@columnname+' _ '+@partnumberstr+' _data '' ,filename = n '''+@location+' fg_ '+@tablename+' _ '+@columnname+' _ '+@partnumberstr+' _data.ndf '' , size = '+@size+' , filegrowth = '+@filegrowth+' )
to filegroup [fg_ '+@tablename+' _ '+@columnname+' _ '+@partnumberstr+' ]; '
print @sql + char ( 13 )
set @i=@i+ 1
end
--3. 创建 分区 函数
declare @funvaluestr nvarchar ( max )
set @i = 1
set @funvaluestr = ''
while @i begin
set @funvaluestr = @funvaluestr + '''' + convert ( varchar ( 10 ), dateadd ( year ,@i,@funvalue), 120 )+ ' 0.000' + ''','
set @i=@i+ 1
end
set @funvaluestr = substring (@funvaluestr, 1 , len (@funvaluestr)- 1 )
set @sql = 'create partition function
fun_ '+@tablename+' _ '+@columnname+' ( int ) as
range right
for values ( '+@funvaluestr+' ) '
print @sql + char ( 13 )
--4. 创建 分区 方案
declare @filegroupstr nvarchar ( max )
set @i = 1
set @filegroupstr = ''
while @i begin
set @partnumberstr = right ( '0' + convert ( nvarchar ,@i), 2 )
set @filegroupstr = @filegroupstr + '[fg_' +@tablename+ '_' +@columnname+ '_' +@partnumberstr+ '],'
set @i=@i+ 1
end
set @filegroupstr = substring (@filegroupstr, 1 , len (@filegroupstr)- 1 )
set @sql = 'create partition scheme
sch_ '+@tablename+' _ '+@columnname+' as
partition fun_ '+@tablename+' _ '+@columnname+'
to ( '+@filegroupstr+' ) '
print @sql + char ( 13 )
--5. 分区 函数的记录数
set @sql = 'select $partition.fun_' +@tablename+ '_' +@columnname+ '(' +@columnname+ ') as partition_num,
min ( '+@columnname+' ) as min_value, max ( '+@columnname+' ) as max_value, count ( 1 ) as record_num
from dbo. '+@tablename+'
group by $partition.fun_ '+@tablename+' _ '+@columnname+' ( '+@columnname+' )
order by $partition.fun_ '+@tablename+' _ '+@columnname+' ( '+@columnname+' ); '
print @sql + char ( 13 )

查看更多关于SQLServer表分区整理的详细内容...

  阅读:47次