sqlserver动态分区方案例子

sqlserver动态分区方案例子

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

 

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

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

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

 

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

 

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

查看复制打印?

1. CREATE TABLE [dbo].[users](  
2.  [id] [bigint] NOT NULL,  
3.  [district] [varchar](12) NULL,  
4.  [tm] [date] NULL,  
5.  [name] [varchar](16) NULL,  
6.  CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED   
7. (  
8.  [id] ASC  
9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
10. ) ON [PRIMARY]  
11. GO  

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

 

5、插入一批测试数据

查看复制打印?

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

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

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

查看复制打印?

1. USE [Test]  
2. GO  
3.   
4. SET ANSI_NULLS ON  
5. GO  
6. SET QUOTED_IDENTIFIER ON  
7. GO  
8. create procedure [dbo].[proc_show_store] as   
9. begin  
10.   
11.   
12.   
13. SELECT OBJECT_NAME(p.object_id) AS ObjectName,  
14.       i.name                   AS IndexName,  
15.       p.index_id               AS IndexID,  
16.       ds.name                  AS PartitionScheme,     
17.       p.partition_number       AS PartitionNumber,  
18.       fg.name                  AS FileGroupName,  
19.       prv_left.value           AS LowerBoundaryValue,  
20.       prv_right.value          AS UpperBoundaryValue,  
21.       CASE pf.boundary_value_on_right  
22.             WHEN 1 THEN 'RIGHT'  
23.             ELSE 'LEFT' END    AS Range,  
24.       p.rows AS Rows  
25. FROM sys.partitions                  AS p  
26. JOIN sys.indexes                     AS i  
27.       ON i.object_id = p.object_id  
28.       AND i.index_id = p.index_id  
29. JOIN sys.data_spaces                 AS ds  
30.       ON ds.data_space_id = i.data_space_id  
31. JOIN sys.partition_schemes           AS ps  
32.       ON ps.data_space_id = ds.data_space_id  
33. JOIN sys.partition_functions         AS pf  
34.       ON pf.function_id = ps.function_id  
35. JOIN sys.destination_data_spaces     AS dds2  
36.       ON dds2.partition_scheme_id = ps.data_space_id   
37.       AND dds2.destination_id = p.partition_number  
38. JOIN sys.filegroups                  AS fg  
39.       ON fg.data_space_id = dds2.data_space_id  
40. LEFT JOIN sys.partition_range_values AS prv_left  
41.       ON ps.function_id = prv_left.function_id  
42.       AND prv_left.boundary_id = p.partition_number - 1  
43. LEFT JOIN sys.partition_range_values AS prv_right  
44.       ON ps.function_id = prv_right.function_id  
45.       AND prv_right.boundary_id = p.partition_number   
46. WHERE  
47.       OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0  
48.        -- and  ds.name is not null   
49.      
50.          
51.        -- 普通表的存储信息  
52. UNION ALL  
53. SELECT  
54.       OBJECT_NAME(p.object_id)    AS ObjectName,  
55.       i.name                      AS IndexName,  
56.       p.index_id                  AS IndexID,  
57.       NULL                        AS PartitionScheme,  
58.       p.partition_number          AS PartitionNumber,  
59.       fg.name                     AS FileGroupName,    
60.       NULL                        AS LowerBoundaryValue,  
61.       NULL                        AS UpperBoundaryValue,  
62.       NULL                        AS Boundary,   
63.       p.rows                      AS Rows  
64. FROM sys.partitions     AS p  
65. JOIN sys.indexes        AS i  
66.       ON i.object_id = p.object_id  
67.       AND i.index_id = p.index_id  
68. JOIN sys.data_spaces    AS ds  
69.       ON ds.data_space_id = i.data_space_id  
70. JOIN sys.filegroups           AS fg  
71.       ON fg.data_space_id = i.data_space_id  
72. WHERE  
73.       OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0  
74. ORDER BY  
75.       ObjectName,  
76.       IndexID,  
77.       PartitionNumber  
78.         
79.       end  
80.         

 

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

查看复制打印?

1. USE [Test]  
2. GO  
3.   
4. SET ANSI_NULLS ON  
5. GO  
6.   
7. SET QUOTED_IDENTIFIER ON  
8. GO  
9.   
10. create procedure [dbo].[proc_show_store_fq] as   
11. begin  
12.   
13. SELECT OBJECT_NAME(p.object_id) AS ObjectName,  
14.       i.name                   AS IndexName,  
15.       p.index_id               AS IndexID,  
16.       ds.name                  AS PartitionScheme,     
17.       p.partition_number       AS PartitionNumber,  
18.       fg.name                  AS FileGroupName,  
19.       prv_left.value           AS LowerBoundaryValue,  
20.       prv_right.value          AS UpperBoundaryValue,  
21.       CASE pf.boundary_value_on_right  
22.             WHEN 1 THEN 'RIGHT'  
23.             ELSE 'LEFT' END    AS Range,  
24.       p.rows AS Rows  
25. FROM sys.partitions                  AS p  
26. JOIN sys.indexes                     AS i  
27.       ON i.object_id = p.object_id  
28.       AND i.index_id = p.index_id  
29. JOIN sys.data_spaces                 AS ds  
30.       ON ds.data_space_id = i.data_space_id  
31. JOIN sys.partition_schemes           AS ps  
32.       ON ps.data_space_id = ds.data_space_id  
33. JOIN sys.partition_functions         AS pf  
34.       ON pf.function_id = ps.function_id  
35. JOIN sys.destination_data_spaces     AS dds2  
36.       ON dds2.partition_scheme_id = ps.data_space_id   
37.       AND dds2.destination_id = p.partition_number  
38. JOIN sys.filegroups                  AS fg  
39.       ON fg.data_space_id = dds2.data_space_id  
40. LEFT JOIN sys.partition_range_values AS prv_left  
41.       ON ps.function_id = prv_left.function_id  
42.       AND prv_left.boundary_id = p.partition_number - 1  
43. LEFT JOIN sys.partition_range_values AS prv_right  
44.       ON ps.function_id = prv_right.function_id  
45.       AND prv_right.boundary_id = p.partition_number   
46. WHERE  
47.       OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0  
48.         and  ds.name is not null  order by p.object_id   
49.      
50.        /*  
51.        -- 普通表的存储信息  
52. UNION ALL  
53. SELECT  
54.       OBJECT_NAME(p.object_id)    AS ObjectName,  
55.       i.name                      AS IndexName,  
56.       p.index_id                  AS IndexID,  
57.       NULL                        AS PartitionScheme,  
58.       p.partition_number          AS PartitionNumber,  
59.       fg.name                     AS FileGroupName,    
60.       NULL                        AS LowerBoundaryValue,  
61.       NULL                        AS UpperBoundaryValue,  
62.       NULL                        AS Boundary,   
63.       p.rows                      AS Rows  
64. FROM sys.partitions     AS p  
65. JOIN sys.indexes        AS i  
66.       ON i.object_id = p.object_id  
67.       AND i.index_id = p.index_id  
68. JOIN sys.data_spaces    AS ds  
69.       ON ds.data_space_id = i.data_space_id  
70. JOIN sys.filegroups           AS fg  
71.       ON fg.data_space_id = i.data_space_id  
72. WHERE  
73.       OBJECTPROPERTY(p.object_id, 'ISMSShipped') = 0  
74. ORDER BY  
75.       ObjectName,  
76.       IndexID,  
77.       PartitionNumber  
78.         
79.       */  
80.       end  
81.               
82. GO  

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

执行存储过程

查看复制打印?

1. USE [Test]  
2. GO  
3.   
4. DECLARE @return_value int  
5.   
6. EXEC    @return_value = [dbo].[proc_show_store]  
7.   
8. 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、查看分区后的存储情况

查看复制打印?

1. DECLARE @return_value int  
2. EXEC    @return_value = [dbo].[proc_show_store_fq]  
3. 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、动态分区存储过程

查看复制打印?

1. GO  
2. SET ANSI_NULLS ON  
3. GO  
4. SET QUOTED_IDENTIFIER ON  
5. GO  
6. -- =============================================  
7. -- Author:      jiangfl  
8. -- Create date: 2015-8-19  
9. -- Description: 动态分区  
10.   
11. -- =============================================  
12. create PROCEDURE  [dbo].[proc_create_table_partition]  
13.     (@now datetime)  
14. AS  
15. BEGIN   
16.   
17.     --文件组名称  
18.     declare @fileGroupName varchar(32);  
19.     --文件名称  
20.     declare @fileName varchar(512);  
21.     -- 保存201608这样的日期  
22.     declare @yyyymm varchar(8);  
23.     -- 保存 2015-09-01 这样的日期  
24.     declare @yyyy_mm_dd varchar(10);  
25.       
26.       
27.     set @yyyymm='_'+convert(varchar(6),@now,112);  
28.       
29.     set @yyyy_mm_dd= convert(varchar(7),@now,120)+'-01';  
30.       
31.       
32.     --定义文件组名称  
33.     set @fileGroupName='fileGroup'+@yyyymm;  
34.       
35.       
36.     --判断组是否已经创建  
37.     if not  exists(select 1 from sys.filegroups where name=@fileGroupName)   
38.     begin  
39.         --创建文件组，因为alter database 这些是不支持事务的，所以只能通过条件来控制  
40.         exec('alter database Test add filegroup '+@fileGroupName);  
41.           
42.     end;  
43.       
44.     --print @yyyymm;  
45.     --创建文件，并分追加到组中  
46.     if not  exists(select 1 from sys.database_files where name='file'+@yyyymm) begin  
47.   
48.         --查询主文件目录  
49.         select @fileName=substring(physical_name,1,LEN(physical_name)-4) from master.sys.master_files where type_desc='ROWS' and name='Test';  
50.         
51.           
52.         --完整的文件名称  
53.          set @fileName=@fileName+@yyyymm+'.ndf';  
54.           
55.         --创建文件  
56.          exec('alter database Test add file (name=N''file'+@yyyymm+''',filename=N'''+@fileName+''',size=10Mb,filegrowth=2mb) to filegroup '+@fileGroupName);  
57.       
58.       
59.         --修改分区方案(方案依赖函数，所以先修改方案)  
60.          exec('ALTER PARTITION SCHEME sm_fq_tm  NEXT USED '+@fileGroupName);  
61.           
62.           
63.         --修改分区函数，添加分区  
64.          ALTER PARTITION FUNCTION fun_fq_tm() SPLIT RANGE(N''+@yyyy_mm_dd);  
65.       
66.     end  
67.       
68. END  

存储过程说明

1、根据传递进来的时间（月份）判断是否已经创建过分区了，如果创建过分区，就不再创建。 
2、创建文件组 
3、创建数据库文件，并绑定到文件组中 
4、重新修改分区方案，将新创建的文件组绑定到方案中 
5、重修修改分区函数，将添加一个边界

 

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

查看复制打印?

1. begin  
2. declare @now datetime;  
3. set @now= convert(date,'2017-10-1')  
4. --动态分区  
5. exec dbo.proc_create_table_partition  @now;  
6. end;  

我们添加一个10月份的新分区，执行完成后，可以查看到数据库中多了数据库文件，文件组

 

 

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

查看复制打印?

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

 

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

查看复制打印?

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