大数据之sql server使用分区表

大数据之sql server使用分区表

5012发表于2014-11-07

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

原始表:

CREATE TABLE [dbo].[GamePropLog](
 [LogID] [bigint] IDENTITY(1,1) NOT NULL Primary key,
 [PropID] [bigint] NULL,
 [PropName] [varchar](100) NULL,
 [CharacterID] [bigint] NULL,
 [CharacterName] [varchar](100) NULL,
 [CreateTime] [datetime] NOT NULL
)
以上表是一个游戏的道具日志表每天都有70多万的新增数据。
下面是sql server中使用日期字段按月进行分区的步骤。以上表是一个游戏的道具日志表每天都有70多万的新增数据。下面是sql server中使用日期字段按月进行分区的步骤。

1、创建文件组及文件

创建三个文件组

ALTER DATABASE NKStatisDBLog ADD FILEGROUP LogGame201410
ALTER DATABASE NKStatisDBLog ADD FILEGROUP LogGame201411
ALTER DATABASE NKStatisDBLog ADD FILEGROUP LogGame201412
为文件组分别创建文件
ALTER DATABASE NKStatisDBLog
ADD FILE(
 NAME=N'PropLog',FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NKStatisDBLog_LogGame201410.mdf',SIZE=3MB
 ,FILEGROWTH=5MB
) TO FILEGROUP LogGame201410
ALTER DATABASE NKStatisDBLog
ADD FILE(
 NAME=N'PropLog201411',FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NKStatisDBLog_LogGame201411.mdf',SIZE=3MB
 ,FILEGROWTH=5MB
) TO FILEGROUP LogGame201411
ALTER DATABASE NKStatisDBLog
ADD FILE(
 NAME=N'PropLog201412',FILENAME='D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\NKStatisDBLog_LogGame201412.mdf',SIZE=3MB
 ,FILEGROWTH=5MB
) TO FILEGROUP LogGame201412

2、创建分区函数

CREATE PARTITION FUNCTION pf_LogDateMonth (datetime)
AS RANGE right
FOR VALUES ('2014/11/01', '2014/12/01')
分区函数有两个时间点,分隔出来了三个区域,分别是
<2014/11/01
>=2014/11/01且<2014/12/01
>=2014/12/01

3、创建分区方案

create partition scheme ps_LogDateMonth
as partition pf_LogDateMonth
to(LogGame201410,LogGame201411,LogGame201412)
将三个分区的数据映射到三个文件组里面。

4、使用分区

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

ALTER TABLE GamePropLog DROP constraint PK_GamePropLog
--创建主键,但不设为聚集索引
ALTER TABLE GamePropLog ADD CONSTRAINT PK_GamePropLog PRIMARY KEY NONCLUSTERED
(
 [LogID] ASC
) ON [PRIMARY] 
在重新非聚集主键之后,就可以为表创建一个新的聚集索引,并且在这个聚集索引中使用分区方案,如以下代码所示:
--创建一个新的聚集索引,在该聚集索引中使用分区方案
CREATE CLUSTERED INDEX Idx_GamePropLog_CreateTime ON GamePropLog([CreateTime])
ON partschSale([CreateTime])
情况2:
重新创建表
CREATE TABLE [dbo].[GamePropLogPartion](
 [LogID] [bigint] IDENTITY(1,1),
 [PropID] [bigint] NULL,
 [PropName] [varchar](100) NULL,
 [CharacterID] [bigint] NULL,
 [CharacterName] [varchar](100) NULL,
 [PlayerID] [bigint] NULL,
 [PlayerName] [varchar](100) NULL,
 [CreateTime] [datetime] NOT NULL,
 [ImportTime] [datetime] NOT NULL DEFAULT GETDATE(),
 CONSTRAINT PK_GamePropLogPartion_ID_CreateTime PRIMARY KEY(LogID,CreateTime)
) ON ps_LogDateMonth(CreateTime)
这里感觉是不是将LogID和CreateTime设置为主键是不是有点怪、多余。
注意:这里有个问题,如果是这样会报错:
CREATE TABLE [dbo].[GamePropLogPartion](
 [LogID] [bigint] IDENTITY(1,1) primary key,
 [PropID] [bigint] NULL,
 [PropName] [varchar](100) NULL,
 [CharacterID] [bigint] NULL,
 [CharacterName] [varchar](100) NULL,
 [PlayerID] [bigint] NULL,
 [PlayerName] [varchar](100) NULL,
 [CreateTime] [datetime] NOT NULL,
 [ImportTime] [datetime] NOT NULL DEFAULT GETDATE()
) ON ps_LogDateMonth(CreateTime) 
错误会是“列 'CreateTime' 是索引 'PK__GamePropLogParti__2D47B39A' 的分区依据列。唯一索引的分区依据列必须是索引键的子集”
解决办法:
CREATE TABLE [dbo].[GamePropLogPartion](
 [LogID] [bigint] IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
 [PropID] [bigint] NULL,
 [PropName] [varchar](100) NULL,
 [CharacterID] [bigint] NULL,
 [CharacterName] [varchar](100) NULL,
 [PlayerID] [bigint] NULL,
 [PlayerName] [varchar](100) NULL,
 [GoldCoin] [decimal](10, 2) NULL,
 [LogType] [varchar](50) NOT NULL DEFAULT 0,
 [SourceType] [varchar](50) NULL,
 [ServerName] [varchar](100) NULL,
 [SystemName] [varchar](100) NULL,
 [PartnerCode] [varchar](100) NULL,
 [CreateTime] [datetime] NOT NULL,
 [ImportTime] [datetime] NOT NULL DEFAULT GETDATE()
)
设置LogID为主键时指定为非聚集索引,创建表之后再创建一个聚集索引映射到分区上,如下
CREATE CLUSTERED INDEX Idx_ProLog_CreateTime ON [GamePropLogPartion2](CreateTime) ON ps_LogDateMonth(CreateTime)

5、查看分区数据

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

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

6、添加分区

当我们需要新添加分区的时候,我们需要修改分区方案,比如现在我们到了2015年年初,我们需要为2015年1月的记录准备分区,就需要添加分区:
USE [master]
GO
ALTER DATABASE [NKStatisDBLog ] ADD FILEGROUP [LogGame201501]
GO
ALTER DATABASE [NKStatisDBLog] ADD FILE ( NAME = N'File4', FILENAME = N'G:\data\FG4\File4.ndf' , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [LogGame201501]
GO
我们新建立了一个文件组,然我们同样按照上面的方法,进行修改分区函数和方案:
use NKStatisDBLog
go
alter partition scheme ps_LogDateMonth next used [LogGame201501]
alter partition function pf_LogDateMonth() split range('2015/01/01')
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月的分区到一个分区,我们可以用如下的代码:

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

7、查看元数据

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

select * from sys.partition_functions
select * from sys.partition_range_values
select * from sys.partition_schemes

小编蓝狐