sqlserver动态分区方案例子

骑猪看日落 2024-02-19 18:50 65阅读 0赞

## sqlserver动态分区方案例子 ##

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

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

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

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

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

![1.png][]

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

[查看][Link 2][复制][Link 2][打印][Link 2][?][Link 2]

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、将表的主键调整为非聚集索引，因为在创建分区的表上，不能创建聚集索引**

![18.png][]  
  
![13.png][]

**5、插入一批测试数据**

[查看][Link 2][复制][Link 2][打印][Link 2][?][Link 2]

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)普通存储查询-存储过程**

[查看][Link 2][复制][Link 2][打印][Link 2][?][Link 2]

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)分区存储查询-存储过程**

[查看][Link 2][复制][Link 2][打印][Link 2][?][Link 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、查看未分区时存储情况**

执行存储过程

[查看][Link 2][复制][Link 2][打印][Link 2][?][Link 2]

1.  USE \[Test\]
2.  GO
3.  
4.  DECLARE @return\_value int
5.  
6.  EXEC @return\_value = \[dbo\].\[proc\_show\_store\]
7.  
8.  GO

![2.png][]

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

**此时我们来创建固定分区**

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

**8、创建分区文件组**

![3.png][]

**9、创建数据库文件**

![4.png][]

**10、通过向导创建分区**

**1）****选择要分区的表，右键选择存储》创建分区**

![5.png][]

**2）下一步**

![6.png][]

**3）选择分区列（我们根据tm进行分区，你可以根据你自己的实际业务选择）**

![7.png][]

**4）选择分区函数（第一次，就创建吧，名字随便取一个）**

**![8.png][]**  
  
**5)选择分区方案（第一次就创建吧，名字随便取一个）**  
![9.png][]  
  
**6)映射分区**

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

![10.png][]

说明：

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

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

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

![11.png][]  
  
**8)再次确认要执行的操作**  
![12.png][]

**9）点击完成，执行创建分区**

![14.png][]

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

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

[查看][Link 2][复制][Link 2][打印][Link 2][?][Link 2]

1.  DECLARE @return\_value int
2.  EXEC @return\_value = \[dbo\].\[proc\_show\_store\_fq\]
3.  GO

![15.png][]

到这里，我们成功的创建好了固定分区  
  
**存储结果说明**

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、动态分区存储过程**

[查看][Link 2][复制][Link 2][打印][Link 2][?][Link 2]

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、执行存储过程创建创建新分区**

[查看][Link 2][复制][Link 2][打印][Link 2][?][Link 2]

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月份的新分区，执行完成后，可以查看到数据库中多了数据库文件，文件组

![16.png][]

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

[查看][Link 2][复制][Link 2][打印][Link 2][?][Link 2]

1.  DECLARE @return\_value int
2.  EXEC @return\_value = \[dbo\].\[proc\_show\_store\_fq\]
3.  GO

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

[查看][Link 2][复制][Link 2][打印][Link 2][?][Link 2]

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;

![17.png][]

[Link 1]: http://www.itmayun.com/it/files/1/article/555592734860053/1.html
[1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/832952116a3e48c5ba2bc91d4e98025c.png
[Link 2]: http://www.itmayun.com/it/#
[18.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/402c2fe1c58843d5bf841989cc68f2e9.png
[13.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/ba11bf63d6754c2bbcc05c3dcc023cef.png
[2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/b0b400e82b944956ae0e3b3807bd26fd.png
[3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/80d67eb5f33a41bcb4f642ce0c63b298.png
[4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/e539088adcdc4a8ebf4dca3b1727982b.png
[5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/2dc8b721779e4f4fbf5e18602ebaaa15.png
[6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/8437907d792f4c0ebe53861658487d27.png
[7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/be28909a08a64b3786ed22af9782f197.png
[8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/d4eaf8c6bfa34da2bf12e8e45fceb309.png
[9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/c62fc9739d284283b6d2480fc1609dde.png
[10.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/bbc78a006b57405bad55d756457fb6f9.png
[11.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/840e8844feea4e0c96ae55f04ca545d2.png
[12.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/a09ea5057f5d4e069b63a61775cb4dcc.png
[14.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/1cb9a6a9a38a4401a486c44915a6c462.png
[15.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3af3315ff0dc4f249ae15a5b5319f510.png
[16.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/3c726e718a484e749dc46a4e735e07db.png
[17.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/29/9f098632ea53420b907cca0e876c8bde.png