SQL Server 表分区整理

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时候需要注意，特别是时间分割上，通常使用以00:00:00.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)+' 00:00:00.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)