SQL SERVER定期转移海量数据方案

SQL SERVER定期转移海量数据方案

【关键词】
1、    分区切换
2、    BCP
3、    BULK INSERT
4、    Insert

【背景】
有个表比较巨大，每天新增约500万条记录。只保留最新7天数据，每天定期移走过期记录，归并到历史库。采用INSERT，DELETE的方法，消耗时间越来越长，平均达到45分钟，期间该表和历史库根本无法访问。

【方案】
1、    采用分区切换，快速清除旧数据
将表按日进行分区，每日一个区。
比如今天是2010年8月8日，有
--分区函数
CREATE PARTITION FUNCTION [CrPFN](smalldatetime) AS RANGE LEFT FOR VALUES (N'2010-08-01T00:00:00.000', N'2010-08-02T00:00:00.000', N'2010-08-03T00:00:00.000', N'2010-08-04T00:00:00.000', N'2010-08-05T00:00:00.000', N'2010-08-06T00:00:00.000', N'2010-08-07T00:00:00.000', N'2010-08-08T00:00:00.000', N'2010-08-09T00:00:00.000')
GO
--分区方案
CREATE PARTITION SCHEME [crSCM] AS PARTITION [CrPFN] TO ([cr0], [cr1], [cr2], [cr3], [cr4], [cr5], [cr6], [cr7], [cr8], [cr9])
GO

 

巨大表应用此分区方案，数据按天划分，分布于[cr0]……[cr9]这10个文件组。另外，按照巨大表相同的结构，分别在这10个文件组中建立一个不分区的表：[Temp_0]……[Temp_9]，每个文件组上有一个。

 

分区切换步骤：
1）    分区切换，将最老分区数据切换到同一文件组的单表（秒杀，几百万条记录瞬时完成，不用1秒）
@day7ago    SMALLDATETIME --当前日期的7天前
@day2later SMALLDATETIME --当前日期的2天后
ALTER TABLE [BigTable] SWITCH PARTITION $PARTITION.CrPFN(@day7ago) TO [Temp_0];

2）    将最老分区与次老分区合并，也就是8天前分区与7天前分区合并。由于8天前分区的数据已经切换掉，为空，所以合并也瞬时完成。
ALTER PARTITION FUNCTION CrPFN() MERGE RANGE (CONVERT(VARCHAR(10),@day7ago,120));

3）    修改分区方案，将原先最老分区对应的文件组插入到分区队列末尾，以重新使用。
ALTER PARTITION SCHEME [CrSCM]    NEXT USED [cr0];

4）    拆分最新日期的分区，边界值推进到当前日期的2天后
    ALTER PARTITION FUNCTION CrPFN() SPLIT RANGE (CONVERT(VARCHAR(10),@day2later,120));
因为最新日期所在分区还没有数据（因为是未来日期），所以拆分起来也是瞬间完成。这也正是我不只设置7个分区，而是10个分区，永远将边界值往未来移两天的原因。

5）    将移到单表的数据归并入历史库
如何归并，是下面一个话题。

6）    清空单表
TRUNCATE TABLE [Temp_0];
总结：
1）    永远只有10个分区，循环使用。最老的分区被合并出局后，马上又插到分区队列末尾。
2）    关键是如何找出分区位于哪个文件组，然后才能知道应该切换给哪个单表，才能放在作业里自动执行。这个方法详见拙作：

查看分区在哪个文件组

3）    分区表与单表务必对齐。

首先是结构一定要一致。比如字段类型、是否可以为null，等等。

其次是索引。分区表有什么索引，单表也要有什么索引。

注意分区表里面的索引，如果要应用本文说的方法，无论聚集索引还是非聚集索引，必须全部都是分区的。其分区方案对应的分区函数应该跟表的分区函数同为一个，分区方案可以不同。

分区表和单表，包括索引，是否为压缩也必须一致。

2、    bcp + BULK INSERT，将数据导入历史库
用bcp将数据库导出到文本文件，然后用BULK INSERT。
    DECLARE @shell VARCHAR(500);
    SET @shell = 'bcp [myDb].dbo.[' + @Table + '] out c:/data.txt -T -n';
    EXEC xp_cmdshell @shell;
    BULK INSERT [History].dbo.[Table1] FROM 'c:/data.txt';
EXEC xp_cmdshell 'del c:/data.txt';

总结：
1）    bcp导出很快。400百万条记录20秒内完成。
2）    BULK INSERT号称导入数据很快，但在我这里却没有见到效果。如果目标表是空表，400百万条数据可在1分半钟内导入成功。但我的历史表超过5亿条记录，里面两个索引，超过20分钟不见反应，比直接INSERT还要慢（INSERT大约17分钟），我失去了耐心，直接按取消了。
3）    其实BULK INSERT运行的这20分钟里，已经插入了100多万条，但这样的速度比直接用INSERT还要慢，让人无法接受。我估计是索引的缘故，如果将索引全部删掉，应该会快吧？但重建的时间会更长。究竟这个BULK INSERT是个什么机制，在这里会比INSERT更慢？有个网友说它会在tempDB里处理，然后再插进源表，但我在运行期间，观察了一下系统的tempDB，发现它的文件没有变化。
4）    失败。请高手赐教。