深入探索：SQL Server历史数据迁移的重要性与目的解析

发布时间：2024-07-10 02:59:06 所属栏目：MsSql教程来源：DaWei

导读： 这篇文章主要介绍“SQLSERVER历史数据迁移的目的和思路是什么”的相关知识，下面会通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“SQLSERVER历史数

这篇文章主要介绍“SQLSERVER历史数据迁移的目的和思路是什么”的相关知识，下面会通过实际案例向大家展示操作过程，操作方法简单快捷，实用性强，希望这篇“SQLSERVER历史数据迁移的目的和思路是什么”文章能帮助大家解决问题。

今天讲下软件开发中最常见的历史数据迁移方式。在讲迁移之前，先简单介绍下几个基本概念。

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(); --中间步骤开始计时

“SQLSERVER历史数据迁移的目的和思路是什么”的内容就介绍到这里了，感谢大家的阅读。

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!