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

sql server 专栏收录该内容
130 篇文章 0 订阅

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)    失败。请高手赐教。

  • 0
    点赞
  • 1
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

打赏
文章很值,打赏犒劳作者一下
相关推荐
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页

打赏

左直拳

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值