大数据之sql server使用分区表

sql server数据库每天增量上了万级别、十万级别，如果又有查询统计的需要,一般都会考虑用分区表，好处是每个分区的数据可以放在单独的文件里面，这样当要被查询统计的数据只在一个分区的时候，不用全部文件进行扫描，只对分区所在的文件进行扫描，大大的减少了IO,提高了查询的效率。同时当一个分区的数据坏了不会影响其它分区的数据，数据库备份也可以对单独的分区进行备份。在实际的业务中，常见的就是对日志表或者销售数据表进行分区，因为这类型的数据 一般都比较大。

原始表：

1. CREATE TABLE [dbo].[GamePropLog](
2. [LogID] [bigint] IDENTITY(1,1) NOT NULL Primary key,
3. [PropID] [bigint] NULL,
4. [PropName] [varchar](100) NULL,
5. [CharacterID] [bigint] NULL,
6. [CharacterName] [varchar](100) NULL,
7. [CreateTime] [datetime] NOT NULL
8. )

以上表是一个游戏的道具日志表每天都有70多万的新增数据。
下面是sql server中使用日期字段按月进行分区的步骤。以上表是一个游戏的道具日志表每天都有70多万的新增数据。下面是sql server中使用日期字段按月进行分区的步骤。

1、创建文件组及文件

创建三个文件组

1. ALTER DATABASE NKStatisDBLog ADD FILEGROUP LogGame201410
2. ALTER DATABASE NKStatisDBLog ADD FILEGROUP LogGame201411
3. ALTER DATABASE NKStatisDBLog ADD FILEGROUP LogGame201412

为文件组分别创建文件

1. ALTER DATABASE NKStatisDBLog
2. ADD FILE(
3. NAME=N'PropLog',FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NKStatisDBLog_LogGame201410.mdf',SIZE=3MB
4. ,FILEGROWTH=5MB
5. ) TO FILEGROUP LogGame201410
6. ALTER DATABASE NKStatisDBLog
7. ADD FILE(
8. NAME=N'PropLog201411',FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NKStatisDBLog_LogGame201411.mdf',SIZE=3MB
9. ,FILEGROWTH=5MB
10. ) TO FILEGROUP LogGame201411
11. ALTER DATABASE NKStatisDBLog
12. ADD FILE(
13. NAME=N'PropLog201412',FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NKStatisDBLog_LogGame201412.mdf',SIZE=3MB
14. ,FILEGROWTH=5MB
15. ) TO FILEGROUP LogGame201412

2、创建分区函数

1. CREATE PARTITION FUNCTION pf_LogDateMonth (datetime)
2. AS RANGE right
3. FOR VALUES ('2014/11/01', '2014/12/01')

分区函数有两个时间点，分隔出来了三个区域，分别是
<2014/11/01
>=2014/11/01且<2014/12/01
>=2014/12/01

3、创建分区方案

1. create partition scheme ps_LogDateMonth
2. as partition pf_LogDateMonth
3. to(LogGame201410,LogGame201411,LogGame201412)

将三个分区的数据映射到三个文件组里面。

4、使用分区

使用分区一般有两种情况。
情况1、表已经创建了也有数据了，又不不想删除表重新创建，不想丢数据；
情况2、表不存在，这种情况是最好简单的。
情况1：
先看表是否有聚集索引，我们的表一般都会有一个自增的ID字段为主键，sql server在创建表的时候主键默认就会建一个聚集索引。
如果存在，就要先删除，重写建一个聚集索引。假设聚集索引“PK_GamePropLog”，
--删掉主键

1. ALTER TABLE GamePropLog DROP constraint PK_GamePropLog

--创建主键，但不设为聚集索引

1. ALTER TABLE GamePropLog ADD CONSTRAINT PK_GamePropLog PRIMARY KEY NONCLUSTERED
2. (
3. [LogID] ASC
4. ) ON [PRIMARY]

在重新非聚集主键之后，就可以为表创建一个新的聚集索引，并且在这个聚集索引中使用分区方案，如以下代码所示：
--创建一个新的聚集索引，在该聚集索引中使用分区方案

1. CREATE CLUSTERED INDEX Idx_GamePropLog_CreateTime ON GamePropLog([CreateTime])
2. ON partschSale([CreateTime])

情况2：
重新创建表

1. CREATE TABLE [dbo].[GamePropLogPartion](
2. [LogID] [bigint] IDENTITY(1,1),
3. [PropID] [bigint] NULL,
4. [PropName] [varchar](100) NULL,
5. [CharacterID] [bigint] NULL,
6. [CharacterName] [varchar](100) NULL,
7. [PlayerID] [bigint] NULL,
8. [PlayerName] [varchar](100) NULL,
9. [CreateTime] [datetime] NOT NULL,
10. [ImportTime] [datetime] NOT NULL DEFAULT GETDATE(),
11. CONSTRAINT PK_GamePropLogPartion_ID_CreateTime PRIMARY KEY(LogID,CreateTime)
12. ) ON ps_LogDateMonth(CreateTime)

这里感觉是不是将LogID和CreateTime设置为主键是不是有点怪、多余。
注意：这里有个问题，如果是这样会报错：

1. CREATE TABLE [dbo].[GamePropLogPartion](
2. [LogID] [bigint] IDENTITY(1,1) primary key,
3. [PropID] [bigint] NULL,
4. [PropName] [varchar](100) NULL,
5. [CharacterID] [bigint] NULL,
6. [CharacterName] [varchar](100) NULL,
7. [PlayerID] [bigint] NULL,
8. [PlayerName] [varchar](100) NULL,
9. [CreateTime] [datetime] NOT NULL,
10. [ImportTime] [datetime] NOT NULL DEFAULT GETDATE()
11. ) ON ps_LogDateMonth(CreateTime)

错误会是“列 'CreateTime' 是索引 'PK__GamePropLogParti__2D47B39A' 的分区依据列。唯一索引的分区依据列必须是索引键的子集”
解决办法：

1. CREATE TABLE [dbo].[GamePropLogPartion](
2. [LogID] [bigint] IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
3. [PropID] [bigint] NULL,
4. [PropName] [varchar](100) NULL,
5. [CharacterID] [bigint] NULL,
6. [CharacterName] [varchar](100) NULL,
7. [PlayerID] [bigint] NULL,
8. [PlayerName] [varchar](100) NULL,
9. [GoldCoin] [decimal](10, 2) NULL,
10. [LogType] [varchar](50) NOT NULL DEFAULT 0,
11. [SourceType] [varchar](50) NULL,
12. [ServerName] [varchar](100) NULL,
13. [SystemName] [varchar](100) NULL,
14. [PartnerCode] [varchar](100) NULL,
15. [CreateTime] [datetime] NOT NULL,
16. [ImportTime] [datetime] NOT NULL DEFAULT GETDATE()
17. )

设置LogID为主键时指定为非聚集索引，创建表之后再创建一个聚集索引映射到分区上，如下

1. CREATE CLUSTERED INDEX Idx_ProLog_CreateTime ON [GamePropLogPartion2](CreateTime) ON ps_LogDateMonth(CreateTime)

5、查看分区数据

--统计所有分区表中的记录总数  

1. SELECT $partition.pf_LogDateMonth(CreateTime),COUNT(1) FROM GamePropLog
2. GROUP BY $partition.pf_LogDateMonth(CreateTime)

6、添加分区

当我们需要新添加分区的时候，我们需要修改分区方案，比如现在我们到了2015年年初，我们需要为2015年1月的记录准备分区，就需要添加分区：

1. USE [master]
2. GO
3. ALTER DATABASE [NKStatisDBLog ] ADD FILEGROUP [LogGame201501]
4. GO
5. ALTER DATABASE [NKStatisDBLog] ADD FILE ( NAME = N'File4', FILENAME = N'G:\data\FG4\File4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [LogGame201501]
6. GO

我们新建立了一个文件组，然我们同样按照上面的方法，进行修改分区函数和方案：

1. use NKStatisDBLog
2. go
3. alter partition scheme ps_LogDateMonth next used [LogGame201501]
4. alter partition function pf_LogDateMonth() split range('2015/01/01')
5. go

我们这里用alter partition Scheme pf_LogDateMonth Next Used LogGame201501用来指定新分区的数据在那个文件。这里Next Used LogGame201501指定的就是我们刚才新建立的第四个文件组。当然我们可以放在原来已经建立的文件组，为了防治数据混乱存放我们大部分是新建立文件组。
alter partition function pf_LogDateMonth() split range('2015/01/01')代表我创建一个新分区，而这里split range是创建新分区的关键语法。
至此，我们就有了四个分区，此时的区间如下：
文件组 分区 取值范围
LogGame201410 1 (过去某年, 2014/11/01)
LogGame201411 2 [2014/11/01, 2014/12/01)
LogGame201412 3 [2014/12/01,2015/01/01)
LogGame201501 4 [2015/01/01,未来某年)

6、删除分区

删除分区又称合并分区，简单讲就是两个分区的数据进行合并，比如我们想合并2014年10月的分区和2014年11月的分区到一个分区，我们可以用如下的代码：

1. use NKStatisDBLog
2. go
3. alter partition function pf_LogDateMonth() merge range('2014/10/01')
4. go

也就是将2014/10/01年这个分区点去掉，里面分区里面的数据会自动合并到一起。
执行完上面的代码，此时分区区间如下：
文件组 分区 取值范围
LogGame201411 2 (过去某年, 2014/12/01)
LogGame201412 3 [2014/12/01,2015/01/01)
LogGame201501 4 [2015/01/01,未来某年)

7、查看元数据

我们可以通过三个系统视图来查看我们的分区函数，分区方案，边界值点等。

1. select * from sys.partition_functions
2. select * from sys.partition_range_values
3. select * from sys.partition_schemes