SQL Server表分区的操作

背景：

     大多数项目开发中都会有几个日志表用于记录用户操作或者数据变更的信息，往往这些表数据数据量比较庞大，每次对这些表数据进行操作都比较费时，这个时候就考虑用表分区对表进行切分到不同物理磁盘进行存储，从而提高运行效率。

 

表分区优点：

      1.性能提升：最大的好处应该是把表数据分割到不同的磁盘存储，充分利用多cpu对数据文件同步处理带来的数据操作效率的提升

      2.数据管理：分区表进行数据备份的时候可以单独备份需要的指定分区文件进行备份，不需要对整个表数据进行备份

      3.可用性：一个分区文件遭到破坏不会影响其他文件的正常使用

 

实战：

     项目中有一个日志表因为每日记录数据量太大（3个月数据2000W）需要只保留最近三个月的数据，这样就要求每月初把3个月前的数据给删掉，同时这个表要进行分页查询和数据汇总，这样就考虑到将这张表进行分区操作，操作数据库是SQL Server2012(只有专业版才支持分区)

     第一步：创建文件组和分组文件

alter database Test add filegroup LoginLog1
alter database Test add filegroup LoginLog2
alter database Test add filegroup LoginLog3
alter database Test add filegroup LoginLog4

  Test是用来测试的数据库名称，我们先创建4个文件组

   接下来创建分组文件

alter database Test add file
 (Name=N'LoginLog1',filename='G:\练习\表分区测试\group\LoginLog1.ndf',size=10mb,maxsize=200Mb,filegrowth=5mb) 
 to filegroup LoginLog1
 alter database Test add file
 (Name=N'LoginLog2',filename='G:\练习\表分区测试\group\LoginLog2.ndf',size=10mb,maxsize=200Mb,filegrowth=5mb) 
 to filegroup LoginLog2
 alter database Test add file
 (Name=N'LoginLog3',filename='G:\练习\表分区测试\group\LoginLog3.ndf',size=10mb,maxsize=200Mb,filegrowth=5mb) 
 to filegroup LoginLog3
  alter database Test add file
 (Name=N'LoginLog4',filename='G:\练习\表分区测试\group\LoginLog4.ndf',size=10mb,maxsize=200Mb,filegrowth=5mb) 
 to filegroup LoginLog4

 

       第二步  创建分区函数

       我们当前用时间作为分区字段，以便于日志表根据添加时间做分区

 create partition function Login_Log_CreateTime (datetime)
as range right for values ('2017-04-01','2017-05-01','2017-06-01')

      这里我们用三个日期把整个时间轴划分为4块：2017-04-01以前的数据、2017-04-01至2017-04-30的数据、2017-05-01至2017-05-31的数据、2017-06-01至2017-06-30的数据

      注意range right的left和right的作用是决定临界点值得归属，一开始我这里用的是left导致分区划分为4、5、6和6月份以后的数据，这样导致我在下次添加新的分区的时候没办法添加2017-07-01的分割点，只能添加>=2017-08-01的时间点。所以这里根据情况自己定义好是Left还是right

 

       第三步   创建分区方案

create partition scheme SchemeLogin_Log_CreateTime
 as partition Login_Log_CreateTime
 to (LoginLog1,LoginLog2,LoginLog3,LoginLog4)

        这里注意如果分区函数是三个分割点，这里就要对应3+1个文件组

       到这里我们的分区方案算是已经搞好了，下面就开始创建测试表并给其指定分区方案

 

 创建测试表，指定添加时间作为分区字段

create Table Login_Log
(
ID int,
Name nvarchar(50),
CreateTime datetime
) on SchemeLogin_Log_CreateTime(createtime) 

 这里有一点不太明白：如果在表中创建主键或唯一索引，则分区依据列必须为该列，所以我们把分区列建在createtime上面意味着建表的时候不能存在主键或者唯一索引。

 如果我用代码添加主键报错：

alter table Login_Log  add constraint PK_Login_Log primary key(ID)

但是我如果对表右击-->设计-->设置主键  这样就可以

 

然后我们插入测试数据

insert Login_Log (id,Name,CreateTime)
values (1,'aa','2017-03-12')
insert Login_Log (id,Name,CreateTime)
values (2,'bb','2017-04-12')
insert Login_Log (id,Name,CreateTime)
values (3,'bb','2017-04-13')
insert Login_Log (id,Name,CreateTime)
values (4,'cc','2017-05-1')
insert Login_Log (id,Name,CreateTime)
values (5,'cc','2017-05-2')
insert Login_Log (id,Name,CreateTime)
values (6,'cc','2017-05-14')
insert Login_Log (id,Name,CreateTime)
values (7,'dd','2017-06-12')
insert Login_Log (id,Name,CreateTime)
values (8,'dd','2017-06-13')
insert Login_Log (id,Name,CreateTime)
values (9,'dd','2017-06-14')
insert Login_Log (id,Name,CreateTime)
values (10,'dd','2017-06-15')

我们现在来看下数据插入情况，下面是查询代码

select convert(varchar(50), ps.name) as partition_scheme,
p.partition_number, 
convert(varchar(10), ds2.name) as filegroup, 
convert(varchar(19), isnull(v.value, ''), 120) as range_boundary, 
str(p.rows, 9) as rows
from sys.indexes i 
join sys.partition_schemes ps on i.data_space_id = ps.data_space_id 
join sys.destination_data_spaces dds
on ps.data_space_id = dds.partition_scheme_id 
join sys.data_spaces ds2 on dds.data_space_id = ds2.data_space_id 
join sys.partitions p on dds.destination_id = p.partition_number
and p.object_id = i.object_id and p.index_id = i.index_id 
join sys.partition_functions pf on ps.function_id = pf.function_id 
LEFT JOIN sys.Partition_Range_values v on pf.function_id = v.function_id
and v.boundary_id = p.partition_number - pf.boundary_value_on_right 
WHERE i.object_id = object_id('Login_Log')    --此处是表名
and i.index_id in (0, 1) 
order by p.partition_number

View Code

查询结果如下

这样我们可以看出正如我们分区所设的那样在4月份以前的数据是1条，存储在了LoginLog1的文件组中，其他数据也按CreateTime时间点存到了对应的文件组，证明我们的分区已经实现了

这里呢目前还没有涉及到索引，后面再慢慢补充吧

 

到这里应该算是我们的测试已经成功了，但是我们还有一个功能点就是定时删除三个月前的旧数据，我们先来梳理下代码，正确的实现是要写存储过程然后用作业实现的，这个后面在完善

假设当前日期到了2017-6-30 23:59:59，这时候我们就要清理3月份的数据啦，并且新建一个7月份的分区点，关键步骤如下：

1.因为3月份数据都存储在第一个分区里面，所以我们只需要把这个分区的数据给清理掉就行了，也不用根据时间点去表里进行过滤删除。

   我们先建立一个跟Login_Log表一样的临时表（同样需要进行表分区）

create Table Login_Log_Temp
(
ID int,
Name nvarchar(50),
CreateTime datetime
) on SchemeLogin_Log_CreateTime(createtime)

  然后我们将第一分区的数据拷贝到临时表并且删除临时表

  --讲需要删除的分区数据插入临时表
 alter table Login_Log switch partition 1 to Login_Log_Temp  PARTITION 1 
 --删除临时表
 drop table Login_Log_Temp

  这是我们再来查询下数据，LoginLog1的数据已经没有了

因为删除了3月份的数据，这样我们就要将分区1和分区2进行分区合并，好腾出来分组给新的分区使用

  --合并分区
   ALTER PARTITION FUNCTION [Login_Log_CreateTime]()  MERGE RANGE ('2017-04-01')

这里可以看出分区1和分区2的数据已经合并到LoginLog1分组里面了，这样LoginLog2分组已经空出来了

接下来建立新的分区

 --修改分区方案，新的分区使用LoginLog2分组文件
 ALTER PARTITION SCHEME SchemeLogin_Log_CreateTime NEXT USED [LoginLog2]  
 
  --添加新的分区
  ALTER PARTITION FUNCTION [Login_Log_CreateTime]()
  SPLIT RANGE('2017-07-01')

我们再来插入一条7月份的数据

insert Login_Log (id,Name,CreateTime)
values (11,'ee','2017-07-01')

OK，我们的新分区创建完成啦

 

另外删除的时候的注意点：

如果想要删除分组呢就要先删除分组中的数据文件，想要删除分区函数就要先删除分区方案，要删除分区方案就要先删除应用的数据表

这里列出来本次例子用到的删除代码

  --删除数据
  drop table Login_Log_Temp
  drop table Login_Log
  drop partition scheme SchemeLogin_Log_CreateTime
  drop partition function Login_Log_CreateTime

  dbcc   shrinkfile(LoginLog1,emptyfile) 
  ALTER DATABASE test REMOVE FILE LoginLog1
  ALTER DATABASE test REMOVE FILEGROUP LoginLog1
  dbcc   shrinkfile(LoginLog2,emptyfile) 
  ALTER DATABASE test REMOVE FILE LoginLog2
  ALTER DATABASE test REMOVE FILEGROUP LoginLog2
  dbcc   shrinkfile(LoginLog3,emptyfile) 
  ALTER DATABASE test REMOVE FILE LoginLog3
  ALTER DATABASE test REMOVE FILEGROUP LoginLog3
  dbcc   shrinkfile(LoginLog4,emptyfile) 
  ALTER DATABASE test REMOVE FILE LoginLog4
  ALTER DATABASE test REMOVE FILEGROUP LoginLog4

 

 

备注：这只是本人用来学习的一个例子，此次记录仅作为一个参考点，有很多不完善的地方需要继续研究

 

转载于:https://www.cnblogs.com/zzs-pedestrian/p/6525390.html