•  

sqlserver动态分区方案例子

2017-12-02

当我们存储的数据量比较大时,比如超过千万,上亿级别时单纯的使用索引可能效果不明显了,此时我们可以考虑采用数据库分区来解决性能瓶颈问题。下面是我在工作中创建的一个动态分区思路,分区可以是固定分区,后面再扩展到动态分区。


1、检查你的数据库,看是否支持分区。

sqlserver数据库分区技术在2005版本就已经添加了,在这之前的版本你就别想了,赶紧升级数据库吧,如果你的数据库是2005以上的,那也不一定支持分区,因为sqlserver包含了很多选择,比如标准版,家庭版,开发版等等,有些是不支持分区的。但企业版肯定是支持分区的。

这里可以查看sqlserver数据库历史版本特性的介绍。


2、我们随便创建一个数据库,我这里是Test


3、创建一个测试表,加入这个表的数据量比较大,全国人口13亿多呢,你说大不大!!

CREATE TABLE [dbo].[users](
 [id] [bigint] NOT NULL,
 [district] [varchar](12) NULL,
 [tm] [date] NULL,
 [name] [varchar](16) NULL,
 CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED 
(
 [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

4、将表的主键调整为非聚集索引,因为在创建分区的表上,不能创建聚集索引




5、插入一批测试数据
-- 先初始化一批数据
insert into users(id,district,tm,name) values(1,'深圳',CONVERT(date,'2017-07-02'),'张永辉');
insert into users(id,district,tm,name) values(2,'深圳',CONVERT(date,'2017-08-02'),'蒋福龙');
insert into users(id,district,tm,name) values(3,'深圳',CONVERT(date,'2017-08-03'),'魏吉海');
insert into users(id,district,tm,name) values(4,'深圳',CONVERT(date,'2017-09-10'),'向其星');
go


6、我们创建两个存储过程用来检测分析数据

1)普通存储查询-存储过程

USE [Test]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[proc_show_store] as 
begin



SELECT OBJECT_NAME(p.object_id) AS ObjectName,
      i.name                   AS IndexName,
      p.index_id               AS IndexID,
      ds.name                  AS PartitionScheme,   
      p.partition_number       AS PartitionNumber,
      fg.name                  AS FileGroupName,
      prv_left.value           AS LowerBoundaryValue,
      prv_right.value          AS UpperBoundaryValue,
      CASE pf.boundary_value_on_right
            WHEN 1 THEN 'RIGHT'
            ELSE 'LEFT' END    AS Range,
      p.rows AS Rows
FROM sys.partitions                  AS p
JOIN sys.indexes                     AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces                 AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes           AS ps
      ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions         AS pf
      ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces     AS dds2
      ON dds2.partition_scheme_id = ps.data_space_id 
      AND dds2.destination_id = p.partition_number
JOIN sys.filegroups                  AS fg
      ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number 
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
       -- and  ds.name is not null 
   
       
       -- 普通表的存储信息
UNION ALL
SELECT
      OBJECT_NAME(p.object_id)    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,
      NULL                        AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName,  
      NULL                        AS LowerBoundaryValue,
      NULL                        AS UpperBoundaryValue,
      NULL                        AS Boundary, 
      p.rows                      AS Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
      ON fg.data_space_id = i.data_space_id
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber
      
      end
      


2)分区存储查询-存储过程

USE [Test]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[proc_show_store_fq] as 
begin

SELECT OBJECT_NAME(p.object_id) AS ObjectName,
      i.name                   AS IndexName,
      p.index_id               AS IndexID,
      ds.name                  AS PartitionScheme,   
      p.partition_number       AS PartitionNumber,
      fg.name                  AS FileGroupName,
      prv_left.value           AS LowerBoundaryValue,
      prv_right.value          AS UpperBoundaryValue,
      CASE pf.boundary_value_on_right
            WHEN 1 THEN 'RIGHT'
            ELSE 'LEFT' END    AS Range,
      p.rows AS Rows
FROM sys.partitions                  AS p
JOIN sys.indexes                     AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces                 AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes           AS ps
      ON ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions         AS pf
      ON pf.function_id = ps.function_id
JOIN sys.destination_data_spaces     AS dds2
      ON dds2.partition_scheme_id = ps.data_space_id 
      AND dds2.destination_id = p.partition_number
JOIN sys.filegroups                  AS fg
      ON fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left
      ON ps.function_id = prv_left.function_id
      AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right
      ON ps.function_id = prv_right.function_id
      AND prv_right.boundary_id = p.partition_number 
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
        and  ds.name is not null  order by p.object_id 
   
       /*
       -- 普通表的存储信息
UNION ALL
SELECT
      OBJECT_NAME(p.object_id)    AS ObjectName,
      i.name                      AS IndexName,
      p.index_id                  AS IndexID,
      NULL                        AS PartitionScheme,
      p.partition_number          AS PartitionNumber,
      fg.name                     AS FileGroupName,  
      NULL                        AS LowerBoundaryValue,
      NULL                        AS UpperBoundaryValue,
      NULL                        AS Boundary, 
      p.rows                      AS Rows
FROM sys.partitions     AS p
JOIN sys.indexes        AS i
      ON i.object_id = p.object_id
      AND i.index_id = p.index_id
JOIN sys.data_spaces    AS ds
      ON ds.data_space_id = i.data_space_id
JOIN sys.filegroups           AS fg
      ON fg.data_space_id = i.data_space_id
WHERE
      OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0
ORDER BY
      ObjectName,
      IndexID,
      PartitionNumber
      
      */
      end
            
GO

7、查看未分区时存储情况

执行存储过程

USE [Test]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[proc_show_store]

GO


这里可以看到 在users表中存储了4条数据,而这4条数据是存储在primary(默认)分组里面的。


此时我们来创建固定分区

分区思路:我们根据时间(tm)来进行分区,做两个固定分区(2017年8月和9月)


8、创建分区文件组


9、创建数据库文件


10、通过向导创建分区

1)选择要分区的表,右键选择存储》创建分区


2)下一步


3)选择分区列(我们根据tm进行分区,你可以根据你自己的实际业务选择)


4)选择分区函数(第一次,就创建吧,名字随便取一个)



5)选择分区方案(第一次就创建吧,名字随便取一个)


6)映射分区

这里主要是制定分区的规则,什么样的数据存储在哪个分区里面,就是通过这里配置的


说明:

设置边界:通过设置边界可以将数据快捷分配,比如选择的是时间的话,按年,按月等操作
范围:主要讲的是包含还是不包含的意思

    左边界:文件组包含这个边界值(tm如2017/8/1)的值
    右边界:文件组不包含这个边界值(tm如2017/8/1)的值
预计存储空间:设置边界后,点击【预计存储空间】按钮,可以对数据进行一个估算,但不一定是准确值,因此这里还没有进行真正的分区,users表里面现在有4条数据,而【行计数】,累计却有6条,因此不一定准啊,但我们不用管它。


7)点击下一步,我们直接立即运行



8)再次确认要执行的操作


9)点击完成,执行创建分区

此时我们的分区就算创建完成了

11、查看分区后的存储情况

DECLARE	@return_value int
EXEC	@return_value = [dbo].[proc_show_store_fq]
GO

到这里,我们成功的创建好了固定分区

存储结果说明

ObjectName:users表
PartitionScheme:分区方案sm_fq_tm
lowerBoundaryValue:分区的开始边界值
UpperBoundaryValue:分区的结束边界值
FileGroupName:分区的存储组
    primary:默认文件组,存储了1条数据,如果后面再插入数据,数据不符合其他分组条件的,都会存储在这个区里面
    filesGroup_201708:我们自己定义的分区,我们命名分区的名字和分配存储有点词不达意哈。。不要介意!!这里按名字应该准备存储8月份的数据,可实际存储的是7-1到8-1日的数据,说白了,实际存储的是7月份的数据。
rows:实际存储的数据


12、动态分区

可能你也想到了,数据是实时变化的,以后会有更多的数据存储进来,而我们的分区方案是固定的,这样会导致一个问题,假如插入的数据正好是7和8月份的数据,那么可以正常分配到分区gileGroup_xxx里面去,而假如插入的是2018年或者2000年的数据,那么全部到primary分区里面了,这样会导致primary分区数据非常大。

难道我们事先在数据库里面先创建好10年,或者是100年甚至更久的分区方案??

如果你真这么做,也不是不可以,至少这样也是可行的,但我提出另外一种思路,我们来做一个动态分区

主要思路是这样

创建一个定时器每天执行一次,获取当前日期的月份,在数据库中查询是否有创建了分区,如果没有创建,则自动创建一个分区


13、动态分区存储过程

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		jiangfl
-- Create date: 2015-8-19
-- Description:	动态分区

-- =============================================
create PROCEDURE  [dbo].[proc_create_table_partition]
	(@now datetime)
AS
BEGIN 

	--文件组名称
	declare @fileGroupName varchar(32);
	--文件名称
	declare @fileName varchar(512);
	-- 保存201608这样的日期
	declare @yyyymm varchar(8);
	-- 保存 2015-09-01 这样的日期
	declare @yyyy_mm_dd varchar(10);
	
	
	set @yyyymm='_'+convert(varchar(6),@now,112);
	
	set @yyyy_mm_dd= convert(varchar(7),@now,120)+'-01';
	
	
	--定义文件组名称
	set @fileGroupName='fileGroup'+@yyyymm;
	
	
	--判断组是否已经创建
	if not  exists(select 1 from sys.filegroups where name=@fileGroupName) 
	begin
		--创建文件组,因为alter database 这些是不支持事务的,所以只能通过条件来控制
		exec('alter database Test add filegroup '+@fileGroupName);
		
	end;
	
	--print @yyyymm;
	--创建文件,并分追加到组中
	if not  exists(select 1 from sys.database_files where name='file'+@yyyymm) begin

		--查询主文件目录
		select @fileName=substring(physical_name,1,LEN(physical_name)-4) from master.sys.master_files where type_desc='ROWS' and name='Test';
	  
		
		--完整的文件名称
		 set @fileName=@fileName+@yyyymm+'.ndf';
		
		--创建文件
		 exec('alter database Test add file (name=N''file'+@yyyymm+''',filename=N'''+@fileName+''',size=10Mb,filegrowth=2mb) to filegroup '+@fileGroupName);
	
	
		--修改分区方案(方案依赖函数,所以先修改方案)
		 exec('ALTER PARTITION SCHEME sm_fq_tm  NEXT USED '+@fileGroupName);
		
		
		--修改分区函数,添加分区
		 ALTER PARTITION FUNCTION fun_fq_tm() SPLIT RANGE(N''+@yyyy_mm_dd);
	
	end
	
END
存储过程说明
1、根据传递进来的时间(月份)判断是否已经创建过分区了,如果创建过分区,就不再创建。
2、创建文件组
3、创建数据库文件,并绑定到文件组中
4、重新修改分区方案,将新创建的文件组绑定到方案中
5、重修修改分区函数,将添加一个边界


14、执行存储过程创建创建新分区

begin
declare @now datetime;
set @now= convert(date,'2017-10-1')
--动态分区
exec dbo.proc_create_table_partition  @now;
end;
我们添加一个10月份的新分区,执行完成后,可以查看到数据库中多了数据库文件,文件组



执行前面创建好的分区存储过程也可以查到分区情况,此时我们成功调整了分区。

DECLARE	@return_value int
EXEC	@return_value = [dbo].[proc_show_store_fq]
GO


15、在执行计划中创建定时器执行,每天执行一次,这样就完成了动态分区效果了

begin
declare @now datetime;
--每个月1号执行的时候,添加下一个月的分区
set @now=DATEADD (MONTH , 1, getdate())
--动态分区
exec dbo.proc_create_table_partition  @now;
end;

(完)

申明:本文受法律保护,未经作者同意不得用于商业用途,如转载请说明出处!
评论