您好,登录后才能下订单哦!
这篇文章给大家介绍如何使用SQL SERVER存储过程实现历史数据迁移方式,内容非常详细,感兴趣的小伙伴们可以参考借鉴,希望对大家能有所帮助。
简单直白地说:就是将一些创建时间比较久而且不常用的历史数据,存储到另一个地方(可以是另一个数据,也可以是另一个表),一般历史数据迁移的数据,都是不会更改了的数据,后续只可能需要查询统计而已。
减少在用数据库的数量,因为数据量越大,数据库操作数据(包括:查询、排序等等)的时间也就越长,当一个表的数据达到上千万级以上,再来个多条件多表查询的时候,是会有响应速度慢的可能。(因为不同开发人员写的逻辑,不可能保障每个SQL都是高效率执行的SQL)
所以及时迁移走一些历史数据,是对整个系统性能的提升是有一定好处的。
最简单的情况,就是你感觉程序有变慢的趋势,那就可以开始考虑历史数据迁移了。
原则上,小企业服务器不多,硬件配置也不是很高的情况下,单表500万以上的数据,最好就开始慢慢迁移,别动不动就等到几千万的数据才开始迁移。
根据产生数据量的大小判断,一般保持1年左右的业务数据即可,一年前的历史数据都迁入历史上数据库。如果每天产生的数据量实在太大的话,一般就需要考虑自动分表存储,当然如果没做这个的话,可以在不影响日常业务的情况下,实时在用业务数据库只保留最近3-6个月的数据。
1)、第一次迁移创建一个一模一样的表结构(只要第一次迁移前创建即可)
2)、按照数据的创建时间排序,把最早的数据N条数据查出,同时插入到历史数据表中。
insert into ... select from
3)、检测插入数据的准确性,一定要保证是N条没错。之后删除在用的业务数据库。
4)、当迁移数据中途有误时,终止程序,但不能删除在用数据库,需要开发人员核对数据。
5)、根据迁移的对性能的影响,N不能太多,最多一次5W到10W条吧(根据服务器的性能配置,推荐一次迁移1W至5W条数据影响较小)。如果要迁移大量数据,可以考虑分批执行。
5、数据迁移存储过程代码示例
代码如下:(无需过多解释,很简单的代码,一看就懂)
USE [Tyingsoft.GLPS] GO /****** Object: StoredProcedure [dbo].[TY_SP_ApiRequestToHis] Script Date: 2021-09-16 15:35:55 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Last Edit date: -- Description: <接口请求记录表GLPS_APIREQUEST数据迁移> -- ============================================= ALTER PROCEDURE [dbo].[TY_SP_ApiRequestToHis] -- Add the parameters for the stored procedure here @PreCountN int = 2000 --每次执行的条数N AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @tableDataCount int; --迁移前的数据条数 declare @tableDataCountHis int; --迁移前历史数据库的数据条数 declare @tableDataCount2 int; --迁移后的数据条数 declare @tableDataCount2His int; --迁移后历史数据库的数据条数 declare @maxCreateTime datetime; --取N条数据中最大的创建时间 declare @maxCreateTimeHis datetime; --历史数据库中的最大创建时间 declare @beginTime datetime; --开始执行时间 declare @endTime datetime; --执行完成时间 declare @execTimeMS int; --执行时间(毫秒数) --中间步骤debugger耗时使用 declare @tmpBeginTime datetime; --(临时)开始执行时间 declare @tmpEndTime datetime; --(临时)执行完成时间 declare @tmpExecTimeMS int; --(临时)执行时间(毫秒数) select @beginTime = getdate(); --迁移前:先查询数据条数 select @tableDataCount = count(1) from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST; select @tableDataCountHis =count(1) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST; print '【接口请求记录表(GLPS_APIREQUEST)数据迁移】开始执行时间:' + convert(nvarchar(50),@beginTime,20); print '本次计划迁移数据条数:'+ cast( @PreCountN as nvarchar(20)); --创建一个临时公用表达式 (表中最早创建的N条数据) with topNRecord (FCREATETIME) as ( select top (@PreCountN) FCREATETIME from GLPS_APIREQUEST order by FCREATETIME ) --取N条数据中最大的创建时间 select @maxCreateTime =max(FCREATETIME) from topNRecord print '对应迁移数据FCREATETIME为:'+ convert(nvarchar(50),@maxCreateTime,21); --日期转化为字符串格式:yyyy-MM-dd HH:mm:ss.fff select @tmpBeginTime = GETDATE(); --中间步骤开始计时 --第一步:将N条数数据写入到历史数据库 insert into [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST select * from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST where FCREATETIME <=@maxCreateTime select @tmpEndTime = GETDATE(); --中间步骤计时结束 print '数据迁移,插入耗时(毫秒):' +cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20)); --第二步:对比历史数据库的数据 select @maxCreateTimeHis=max(FCREATETIME) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST if @maxCreateTime = @maxCreateTimeHis begin select @tmpBeginTime = GETDATE(); --中间步骤开始计时 --第三步:执行完以后,再删除数据 delete from GLPS_APIREQUEST where FCREATETIME <=@maxCreateTime print '迁移后删除数据条数:' + cast( @@ROWCOUNT as nvarchar(50)); select @tmpEndTime = GETDATE(); --中间步骤计时结束 print '数据迁移,删除耗时(毫秒):' +cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20)); end else print '迁移后,日期校验错误,未删除数据!!!' --迁移后:再查询数据条数 select @tableDataCount2 = count(1) from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST; select @tableDataCount2His =count(1) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST; print '迁移前GLPS_APIREQUEST的数据条数:' + cast(@tableDataCount as nvarchar(20)) + ',迁移后数据条数:'+cast(@tableDataCount2 as nvarchar(20)) + ',差额:'+cast((@tableDataCount2-@tableDataCount) as nvarchar(20)); print '迁移前His.GLPS_APIREQUEST的数据条数:' + cast(@tableDataCountHis as nvarchar(20)) +',迁移后数据条数:'+cast(@tableDataCount2His as nvarchar(20)) + ',差额:'+cast((@tableDataCount2His-@tableDataCountHis) as nvarchar(20)); print '注意:正式环境一直有数据变动,所以会有一定的偏差!'; select @endTime = GETDATE(); print '总耗时(毫秒):' +cast( datediff(millisecond,@beginTime,@endTime) as nvarchar(20)); END
我们在测试数据库中来简单执行下试试效果:
此方式是采用 insert into ... select from 的方式进行数据迁移。这个思路由于是最简单的数据迁移逻辑,仅适用于小数据量的情况(一般表数据低于500万),当数据量大于500万之后千万别用此方法,因为此时的insert into ... select from 会执行很慢,有很大可能会影响正式环境的运行。
还有就是此方法,由于是SQL直接访问数据库,所以要求当前业务库和历史数据都能访问(也就是同一个数据库实例),如果异地不同的数据库也没办法处理。
所以此方法仅适用于简单的历史数据迁移场景,使用前提有限,适合小项目使用。
关于如何使用SQL SERVER存储过程实现历史数据迁移方式就分享到这里了,希望以上内容可以对大家有一定的帮助,可以学到更多知识。如果觉得文章不错,可以把它分享出去让更多的人看到。
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。