sqlserver动态分区方案例子

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

 

1、检查你的数据库&＃xff0c;看是否支持分区。

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

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

 

2、我们随便创建一个数据库&＃xff0c;我这里是Test

 

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

查看复制打印?

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 &＃61; OFF, STATISTICS_NORECOMPUTE &＃61; OFF, IGNORE_DUP_KEY &＃61; OFF, ALLOW_ROW_LOCKS &＃61; ON, ALLOW_PAGE_LOCKS &＃61; ON) ON [PRIMARY]  
10. ) ON [PRIMARY]  
11. GO  

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

 

5、插入一批测试数据

查看复制打印?

1. -- 先初始化一批数据  
2. insert into users(id,district,tm,name) values(1,&＃39;深圳&＃39;,CONVERT(date,&＃39;2017-07-02&＃39;),&＃39;张永辉&＃39;);  
3. insert into users(id,district,tm,name) values(2,&＃39;深圳&＃39;,CONVERT(date,&＃39;2017-08-02&＃39;),&＃39;蒋福龙&＃39;);  
4. insert into users(id,district,tm,name) values(3,&＃39;深圳&＃39;,CONVERT(date,&＃39;2017-08-03&＃39;),&＃39;魏吉海&＃39;);  
5. insert into users(id,district,tm,name) values(4,&＃39;深圳&＃39;,CONVERT(date,&＃39;2017-09-10&＃39;),&＃39;向其星&＃39;);  
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 &＃39;RIGHT&＃39;  
23.             ELSE &＃39;LEFT&＃39; 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 &＃61; p.object_id  
28.       AND i.index_id &＃61; p.index_id  
29. JOIN sys.data_spaces                 AS ds  
30.       ON ds.data_space_id &＃61; i.data_space_id  
31. JOIN sys.partition_schemes           AS ps  
32.       ON ps.data_space_id &＃61; ds.data_space_id  
33. JOIN sys.partition_functions         AS pf  
34.       ON pf.function_id &＃61; ps.function_id  
35. JOIN sys.destination_data_spaces     AS dds2  
36.       ON dds2.partition_scheme_id &＃61; ps.data_space_id   
37.       AND dds2.destination_id &＃61; p.partition_number  
38. JOIN sys.filegroups                  AS fg  
39.       ON fg.data_space_id &＃61; dds2.data_space_id  
40. LEFT JOIN sys.partition_range_values AS prv_left  
41.       ON ps.function_id &＃61; prv_left.function_id  
42.       AND prv_left.boundary_id &＃61; p.partition_number - 1  
43. LEFT JOIN sys.partition_range_values AS prv_right  
44.       ON ps.function_id &＃61; prv_right.function_id  
45.       AND prv_right.boundary_id &＃61; p.partition_number   
46. WHERE  
47.       OBJECTPROPERTY(p.object_id, &＃39;ISMSShipped&＃39;) &＃61; 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 &＃61; p.object_id  
67.       AND i.index_id &＃61; p.index_id  
68. JOIN sys.data_spaces    AS ds  
69.       ON ds.data_space_id &＃61; i.data_space_id  
70. JOIN sys.filegroups           AS fg  
71.       ON fg.data_space_id &＃61; i.data_space_id  
72. WHERE  
73.       OBJECTPROPERTY(p.object_id, &＃39;ISMSShipped&＃39;) &＃61; 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 &＃39;RIGHT&＃39;  
23.             ELSE &＃39;LEFT&＃39; 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 &＃61; p.object_id  
28.       AND i.index_id &＃61; p.index_id  
29. JOIN sys.data_spaces                 AS ds  
30.       ON ds.data_space_id &＃61; i.data_space_id  
31. JOIN sys.partition_schemes           AS ps  
32.       ON ps.data_space_id &＃61; ds.data_space_id  
33. JOIN sys.partition_functions         AS pf  
34.       ON pf.function_id &＃61; ps.function_id  
35. JOIN sys.destination_data_spaces     AS dds2  
36.       ON dds2.partition_scheme_id &＃61; ps.data_space_id   
37.       AND dds2.destination_id &＃61; p.partition_number  
38. JOIN sys.filegroups                  AS fg  
39.       ON fg.data_space_id &＃61; dds2.data_space_id  
40. LEFT JOIN sys.partition_range_values AS prv_left  
41.       ON ps.function_id &＃61; prv_left.function_id  
42.       AND prv_left.boundary_id &＃61; p.partition_number - 1  
43. LEFT JOIN sys.partition_range_values AS prv_right  
44.       ON ps.function_id &＃61; prv_right.function_id  
45.       AND prv_right.boundary_id &＃61; p.partition_number   
46. WHERE  
47.       OBJECTPROPERTY(p.object_id, &＃39;ISMSShipped&＃39;) &＃61; 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 &＃61; p.object_id  
67.       AND i.index_id &＃61; p.index_id  
68. JOIN sys.data_spaces    AS ds  
69.       ON ds.data_space_id &＃61; i.data_space_id  
70. JOIN sys.filegroups           AS fg  
71.       ON fg.data_space_id &＃61; i.data_space_id  
72. WHERE  
73.       OBJECTPROPERTY(p.object_id, &＃39;ISMSShipped&＃39;) &＃61; 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 &＃64;return_value int  
5.   
6. EXEC    &＃64;return_value &＃61; [dbo].[proc_show_store]  
7.   
8. GO  

 

这里可以看到 在users表中存储了4条数据&＃xff0c;而这4条数据是存储在primary&＃xff08;默认&＃xff09;分组里面的。

 

此时我们来创建固定分区

分区思路&＃xff1a;我们根据时间(tm)来进行分区&＃xff0c;做两个固定分区&＃xff08;2017年8月和9月&＃xff09;

 

8、创建分区文件组

 

9、创建数据库文件

 

10、通过向导创建分区

1&＃xff09;选择要分区的表&＃xff0c;右键选择存储》创建分区

 

2&＃xff09;下一步

3&＃xff09;选择分区列&＃xff08;我们根据tm进行分区&＃xff0c;你可以根据你自己的实际业务选择&＃xff09;

4&＃xff09;选择分区函数&＃xff08;第一次&＃xff0c;就创建吧&＃xff0c;名字随便取一个&＃xff09;

5)选择分区方案&＃xff08;第一次就创建吧&＃xff0c;名字随便取一个&＃xff09;


6)映射分区

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

说明&＃xff1a;

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

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

 

7)点击下一步&＃xff0c;我们直接立即运行

 

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

 

9&＃xff09;点击完成&＃xff0c;执行创建分区

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

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

查看复制打印?

1. DECLARE &＃64;return_value int  
2. EXEC    &＃64;return_value &＃61; [dbo].[proc_show_store_fq]  
3. GO  

到这里&＃xff0c;我们成功的创建好了固定分区

存储结果说明

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

 

12、动态分区

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

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

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

主要思路是这样

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

 

13、动态分区存储过程

查看复制打印?

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

存储过程说明

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

 

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

查看复制打印?

1. begin  
2. declare &＃64;now datetime;  
3. set &＃64;now&＃61; convert(date,&＃39;2017-10-1&＃39;)  
4. --动态分区  
5. exec dbo.proc_create_table_partition  &＃64;now;  
6. end;  

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

 

 

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

查看复制打印?

1. DECLARE &＃64;return_value int  
2. EXEC    &＃64;return_value &＃61; [dbo].[proc_show_store_fq]  
3. GO  

 

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

查看复制打印?

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