Sql Server单表分区

Sql Server单表分区

由于数据中单表数据过千万，影响插入查询等效率。最简单的方式就是把表分区，这样不用改动代码，只需要在数据库中几步就可以操作好，省时省力。

我拿我们数据库中的一个日志表为例子，是用时间进行分区的。这个表Sys_AccessLog中的数据有 三千多万条。

首先第一步，创建分组。这里我是创建了9个组。

alter database [ODC] add filegroup Sys_AccessLog_1
alter database [ODC] add filegroup Sys_AccessLog_2
alter database [ODC] add filegroup Sys_AccessLog_3
alter database [ODC] add filegroup Sys_AccessLog_4
alter database [ODC] add filegroup Sys_AccessLog_5
alter database [ODC] add filegroup Sys_AccessLog_6
alter database [ODC] add filegroup Sys_AccessLog_7
alter database [ODC] add filegroup Sys_AccessLog_8
alter database [ODC] add filegroup Sys_AccessLog_9 

第二步，指定分组的文件位置。如下图。

--D:\ODC\ODC_Subarea_Data\Sys_AccessLog  这个文件夹需要提前建好
alter database ODC add file 
(name=N'Sys_AccessLog_1',filename=N'D:\ODC\ODC_Subarea_Data\Sys_AccessLog\Sys_AccessLog_1.ndf')  
to filegroup Sys_AccessLog_1
alter database ODC add file 
(name=N'Sys_AccessLog_2',filename=N'D:\ODC\ODC_Subarea_Data\Sys_AccessLog\Sys_AccessLog_2.ndf')
to filegroup Sys_AccessLog_2
alter database ODC add file 
(name=N'Sys_AccessLog_3',filename=N'D:\ODC\ODC_Subarea_Data\Sys_AccessLog\Sys_AccessLog_3.ndf')
to filegroup Sys_AccessLog_3
alter database ODC add file 
(name=N'Sys_AccessLog_4',filename=N'D:\ODC\ODC_Subarea_Data\Sys_AccessLog\Sys_AccessLog_4.ndf')
to filegroup Sys_AccessLog_4
alter database ODC add file 
(name=N'Sys_AccessLog_5',filename=N'D:\ODC\ODC_Subarea_Data\Sys_AccessLog\Sys_AccessLog_5.ndf')
to filegroup Sys_AccessLog_5
alter database ODC add file 
(name=N'Sys_AccessLog_6',filename=N'D:\ODC\ODC_Subarea_Data\Sys_AccessLog\Sys_AccessLog_6.ndf')
to filegroup Sys_AccessLog_6
alter database ODC add file 
(name=N'Sys_AccessLog_7',filename=N'D:\ODC\ODC_Subarea_Data\Sys_AccessLog\Sys_AccessLog_7.ndf')
to filegroup Sys_AccessLog_7
alter database ODC add file 
(name=N'Sys_AccessLog_8',filename=N'D:\ODC\ODC_Subarea_Data\Sys_AccessLog\Sys_AccessLog_8.ndf')
to filegroup Sys_AccessLog_8
alter database ODC add file 
(name=N'Sys_AccessLog_9',filename=N'D:\ODC\ODC_Subarea_Data\Sys_AccessLog\Sys_AccessLog_9.ndf')
to filegroup Sys_AccessLog_9

第三步，创建分区函数，这里我是以时间为例。

--这里的意思是，2019-01-01之前的数据是一个时间段，2019-01-01到2019-12-31是一个时间段，以此类推。
--RIGHT FOR VALUES 代表2019-01-01到2019-12-31 是包含2019-01-01的数据， LEFT FOR VALUES则不包含2019-01-01的数据
CREATE PARTITION FUNCTION [Function_Sys_AccessLog](datetime) AS RANGE RIGHT FOR VALUES 
(N'2019-01-01T00:00:00', N'2020-01-01T00:00:00', N'2021-01-01T00:00:00', N'2022-01-01T00:00:00', N'2023-01-01T00:00:00'
, N'2024-01-01T00:00:00', N'2025-01-01T00:00:00', N'2026-01-01T00:00:00')

第四步，创建分区方案，分区方案依赖分区函数。

--Sys_AccessLog_1 放的是2019-01-01之前的数据
--Sys_AccessLog_2放的是2019-01-01到2019-12-31的数据 以此类推
CREATE PARTITION SCHEME [Function_Sys_AccessLog] 
AS PARTITION [Function_Sys_AccessLog] 
TO ([Sys_AccessLog_1], [Sys_AccessLog_2], [Sys_AccessLog_3]
, [Sys_AccessLog_4], [Sys_AccessLog_5], [Sys_AccessLog_6]
, [Sys_AccessLog_7], [Sys_AccessLog_8]
, [Sys_AccessLog_9])

第五步，创建分区索引。

这里有两种方式，一种是直接用sql语句，一种是操作sqlserver，然后它帮助你生成sql语句，然后你在执行。以下是SQL server帮我生成的语句。

USE [ODC]
GO
BEGIN TRANSACTION

--如果你要分的表 有主键，要先删除主键
ALTER TABLE [dbo].[Sys_AccessLog] DROP CONSTRAINT [PK_Sys_Log]
--用你要分区的字段作为主键
CREATE CLUSTERED INDEX [ClusteredIndex_on_Function_Sys_AccessLog_CreateTime] ON [dbo].[Sys_AccessLog]
(
	[CreateTime] desc 
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [Function_Sys_AccessLog]([CreateTime])
--下面语句是 之前在表中加的索引，需要在表分的每个区下都加上索引
CREATE NONCLUSTERED INDEX [NonClusteredIndex-ServiceType-CreateTime-UserKeyId] ON [dbo].[Sys_AccessLog]
(
	[UserKeyId] ASC,
	[CreateTime] DESC,
	[serviceType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [Function_Sys_AccessLog]([CreateTime])
COMMIT TRANSACTION

最后展示效果，如下图：

这样一个表就分区好了。

如何删除分区表以及分区文件

--1.清空表
truncate table [dbo].Sys_AccessLog

2.删除分区函数，分区方案，如下图位置，直接右击删除

3.删除分区组
右击数据库->属性->文件组-> 找到对应的分组->删除

--4.清空分区文件
DBCC SHRINKFILE (Sys_AccessLog_9, EMPTYFILE);
--5.删除分区文件
ALTER DATABASE ODC remove FILE Sys_AccessLog_9

这样就可以删除了