今天讲下软件开发中最常见的历史数据迁移方式。在讲迁移之前，先简单介绍下几个基本概念。
1、什么是历史数据迁移？
简单直白地说：就是将一些创建时间比较久而且不常用的历史数据，存储到另一个地方（可以是另一个数据，也可以是另一个表），一般历史数据迁移的数据，都是不会更改了的数据，后续只可能需要查询统计而已。
2、历史数据迁移的目的
减少在用数据库的数量，因为数据量越大，数据库操作数据（包括：查询、排序等等）的时间也就越长，当一个表的数据达到上千万级以上，再来个多条件多表查询的时候，是会有响应速度慢的可能。（因为不同开发人员写的逻辑，不可能保障每个sql都是高效率执行的sql）
所以及时迁移走一些历史数据，是对整个系统性能的提升是有一定好处的。
3、什么时候需要做历史数据迁移？
最简单的情况，就是你感觉程序有变慢的趋势，那就可以开始考虑历史数据迁移了。
原则上，小企业服务器不多，硬件配置也不是很高的情况下，单表500万以上的数据，最好就开始慢慢迁移，别动不动就等到几千万的数据才开始迁移。
根据产生数据量的大小判断，一般保持1年左右的业务数据即可，一年前的历史数据都迁入历史上数据库。如果每天产生的数据量实在太大的话，一般就需要考虑自动分表存储，当然如果没做这个的话，可以在不影响日常业务的情况下，实时在用业务数据库只保留最近3-6个月的数据。
4、数据迁移的基本思路
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
我们在测试数据库中来简单执行下试试效果：
6、使用场景特别说明
此方式是采用 insert into … select from 的方式进行数据迁移。这个思路由于是最简单的数据迁移逻辑，仅适用于小数据量的情况（一般表数据低于500万），当数据量大于500万之后千万别用此方法，因为此时的insert into … select from 会执行很慢，有很大可能会影响正式环境的运行。
还有就是此方法，由于是sql直接访问数据库，所以要求当前业务库和历史数据都能访问（也就是同一个数据库实例），如果异地不同的数据库也没办法处理。
所以此方法仅适用于简单的历史数据迁移场景，使用前提有限，适合小项目使用。
对于大数据量的（单表1千万以上的数据），又不能影响当下业务性能指标的数据迁移方式，请看下篇文章介绍。
到此这篇关于使用sql server存储过程实现历史数据迁移的文章就介绍到这了,更多相关sql server存储过程数据迁移内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！