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] addfile (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] addfile (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] addfile (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
createpartitionfunction fun_tb_partition_id(int) as rangeright forvalues(10000,20000)
注意:
这里使用了右分区则表示分区取值范围为
属于第一分区
〉=10000 And 属于第二分区
〉=20000属于第三分区
2.2.创建分区架构
一旦给出描述如何分割数据的分区函数,接着就要创建一个分区架构,用来定义分区位置(where)。
createpartition 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"语句,用来指定分区架构以及应用该架构的表列。因为分区架构已经识别了分区函数,
所以不需要再指定分区函数了。
createtable [dbo].[tb_partition1]( [id] [int] identity(1,1) notnull, [username] as'name'+ltrim([id]), [age] [int] nullconstraint [df_ tb_partition1_age] default ((0)), constraint [pk_tb_partition1] primarykey clustered ( [id] asc )with( pad_index = on, fillfactor = 100) on [sch_tb_partition_id](id) ) on [sch_tb_partition_id]([id]) go createtable [dbo].[tb_partition2]( [id] [int] identity(1,1) notnull, [username] as'name'+ltrim([id]), [age] [int] nullconstraint [df_tb_partition2_age] default ((0)), constraint [pk_tb_partition2] primarykey 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 defaultvalues go30005
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] groupby $partition.fun_tb_partition_id(id) orderby $partition.fun_tb_partition_id(id)
2.4.3.切换分区
altertable [dbo].[tb_partition1] switch partition1to [dbo].[tb_partition2] partition1 --查看结果 select * from [dbo].[tb_partition1] select * from [dbo].[tb_partition2]
2.4.4.修改分区架构和分区函数
alterpartition scheme [sch_tb_partition_id] next used [fg_tb_partition_id_1] go alterpartitionfunction [fun_tb_partition_id]() split range(15000)
--定义变量 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 +'] addfile (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 rangeright forvalues('+@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+' groupby $partition.fun_'+@tablename+'_'+@columnname+'('+@columnname+') orderby $partition.fun_'+@tablename+'_'+@columnname+'('+@columnname+');' print @sql + char(13)
Kenyataan:
Kandungan artikel ini disumbangkan secara sukarela oleh netizen, dan hak cipta adalah milik pengarang asal. Laman web ini tidak memikul tanggungjawab undang-undang yang sepadan. Jika anda menemui sebarang kandungan yang disyaki plagiarisme atau pelanggaran, sila hubungi admin@php.cn