使用SQL SERVER存儲過程實現(xiàn)歷史數(shù)據(jù)遷移方式
今天講下軟件開發(fā)中最常見的歷史數(shù)據(jù)遷移方式。在講遷移之前，先簡單介紹下幾個基本概念。
1、什么是歷史數(shù)據(jù)遷移？
簡單直白地說：就是將一些創(chuàng)建時間比較久而且不常用的歷史數(shù)據(jù)，存儲到另一個地方（可以是另一個數(shù)據(jù)，也可以是另一個表），一般歷史數(shù)據(jù)遷移的數(shù)據(jù)，都是不會更改了的數(shù)據(jù)，后續(xù)只可能需要查詢統(tǒng)計而已。
2、歷史數(shù)據(jù)遷移的目的
減少在用數(shù)據(jù)庫的數(shù)量，因為數(shù)據(jù)量越大，數(shù)據(jù)庫操作數(shù)據(jù)（包括：查詢、排序等等）的時間也就越長，當(dāng)一個表的數(shù)據(jù)達(dá)到上千萬級以上，再來個多條件多表查詢的時候，是會有響應(yīng)速度慢的可能。（因為不同開發(fā)人員寫的邏輯，不可能保障每個SQL都是高效率執(zhí)行的SQL）
所以及時遷移走一些歷史數(shù)據(jù)，是對整個系統(tǒng)性能的提升是有一定好處的。
3、什么時候需要做歷史數(shù)據(jù)遷移？
最簡單的情況，就是你感覺程序有變慢的趨勢，那就可以開始考慮歷史數(shù)據(jù)遷移了。
原則上，小企業(yè)服務(wù)器不多，硬件配置也不是很高的情況下，單表500萬以上的數(shù)據(jù)，最好就開始慢慢遷移，別動不動就等到幾千萬的數(shù)據(jù)才開始遷移。
根據(jù)產(chǎn)生數(shù)據(jù)量的大小判斷，一般保持1年左右的業(yè)務(wù)數(shù)據(jù)即可，一年前的歷史數(shù)據(jù)都遷入歷史上數(shù)據(jù)庫。如果每天產(chǎn)生的數(shù)據(jù)量實在太大的話，一般就需要考慮自動分表存儲，當(dāng)然如果沒做這個的話，可以在不影響日常業(yè)務(wù)的情況下，實時在用業(yè)務(wù)數(shù)據(jù)庫只保留最近3-6個月的數(shù)據(jù)。
4、數(shù)據(jù)遷移的基本思路
1）、第一次遷移創(chuàng)建一個一模一樣的表結(jié)構(gòu)（只要第一次遷移前創(chuàng)建即可）
2）、按照數(shù)據(jù)的創(chuàng)建時間排序，把最早的數(shù)據(jù)N條數(shù)據(jù)查出，同時插入到歷史數(shù)據(jù)表中。
insert into ... select from
3）、檢測插入數(shù)據(jù)的準(zhǔn)確性，一定要保證是N條沒錯。之后刪除在用的業(yè)務(wù)數(shù)據(jù)庫。
4）、當(dāng)遷移數(shù)據(jù)中途有誤時，終止程序，但不能刪除在用數(shù)據(jù)庫，需要開發(fā)人員核對數(shù)據(jù)。
5）、根據(jù)遷移的對性能的影響，N不能太多，最多一次5W到10W條吧（根據(jù)服務(wù)器的性能配置，推薦一次遷移1W至5W條數(shù)據(jù)影響較小）。如果要遷移大量數(shù)據(jù)，可以考慮分批執(zhí)行。
5、數(shù)據(jù)遷移存儲過程代碼示例
代碼如下：（無需過多解釋，很簡單的代碼，一看就懂）
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數(shù)據(jù)遷移>
-- =============================================
ALTER PROCEDURE [dbo].[TY_SP_ApiRequestToHis]
-- Add the parameters for the stored procedure here
@PreCountN int = 2000 --每次執(zhí)行的條數(shù)N
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @tableDataCount int; --遷移前的數(shù)據(jù)條數(shù)
declare @tableDataCountHis int; --遷移前歷史數(shù)據(jù)庫的數(shù)據(jù)條數(shù)
declare @tableDataCount2 int; --遷移后的數(shù)據(jù)條數(shù)
declare @tableDataCount2His int; --遷移后歷史數(shù)據(jù)庫的數(shù)據(jù)條數(shù)
declare @maxCreateTime datetime; --取N條數(shù)據(jù)中最大的創(chuàng)建時間
declare @maxCreateTimeHis datetime; --歷史數(shù)據(jù)庫中的最大創(chuàng)建時間
declare @beginTime datetime; --開始執(zhí)行時間
declare @endTime datetime; --執(zhí)行完成時間
declare @execTimeMS int; --執(zhí)行時間（毫秒數(shù)）
--中間步驟debugger耗時使用
declare @tmpBeginTime datetime; --(臨時)開始執(zhí)行時間
declare @tmpEndTime datetime; --(臨時)執(zhí)行完成時間
declare @tmpExecTimeMS int; --(臨時)執(zhí)行時間（毫秒數(shù)）
select @beginTime = getdate();
--遷移前：先查詢數(shù)據(jù)條數(shù)
select @tableDataCount = count(1) from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST;
select @tableDataCountHis =count(1) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST;
print '【接口請求記錄表（GLPS_APIREQUEST）數(shù)據(jù)遷移】開始執(zhí)行時間：' + convert(nvarchar(50),@beginTime,20);
print '本次計劃遷移數(shù)據(jù)條數(shù)：'+ cast( @PreCountN as nvarchar(20));
--創(chuàng)建一個臨時公用表達(dá)式 （表中最早創(chuàng)建的N條數(shù)據(jù)）
with topNRecord (FCREATETIME)
as
(
select top (@PreCountN) FCREATETIME from GLPS_APIREQUEST order by FCREATETIME
)
--取N條數(shù)據(jù)中最大的創(chuàng)建時間
select @maxCreateTime =max(FCREATETIME) from topNRecord
print '對應(yīng)遷移數(shù)據(jù)FCREATETIME為：'+ convert(nvarchar(50),@maxCreateTime,21); --日期轉(zhuǎn)化為字符串格式：yyyy-MM-dd HH:mm:ss.fff
select @tmpBeginTime = GETDATE(); --中間步驟開始計時
--第一步：將N條數(shù)數(shù)據(jù)寫入到歷史數(shù)據(jù)庫
insert into [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST
select * from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST where FCREATETIME <=@maxCreateTime
select @tmpEndTime = GETDATE(); --中間步驟計時結(jié)束
print '數(shù)據(jù)遷移，插入耗時（毫秒）：' +cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20));
--第二步：對比歷史數(shù)據(jù)庫的數(shù)據(jù)
select @maxCreateTimeHis=max(FCREATETIME) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST
if @maxCreateTime = @maxCreateTimeHis
begin
select @tmpBeginTime = GETDATE(); --中間步驟開始計時
--第三步：執(zhí)行完以后，再刪除數(shù)據(jù)
delete from GLPS_APIREQUEST where FCREATETIME <=@maxCreateTime
print '遷移后刪除數(shù)據(jù)條數(shù)：' + cast( @@ROWCOUNT as nvarchar(50));
select @tmpEndTime = GETDATE(); --中間步驟計時結(jié)束
print '數(shù)據(jù)遷移，刪除耗時（毫秒）：' +cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20));
end
else
print '遷移后，日期校驗錯誤，未刪除數(shù)據(jù)?。?！'
--遷移后：再查詢數(shù)據(jù)條數(shù)
select @tableDataCount2 = count(1) from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST;
select @tableDataCount2His =count(1) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST;
print '遷移前GLPS_APIREQUEST的數(shù)據(jù)條數(shù)：' + cast(@tableDataCount as nvarchar(20))
+ ',遷移后數(shù)據(jù)條數(shù)：'+cast(@tableDataCount2 as nvarchar(20))
+ ',差額：'+cast((@tableDataCount2-@tableDataCount) as nvarchar(20));
print '遷移前His.GLPS_APIREQUEST的數(shù)據(jù)條數(shù)：'
+ cast(@tableDataCountHis as nvarchar(20))
+',遷移后數(shù)據(jù)條數(shù)：'+cast(@tableDataCount2His as nvarchar(20))
+ ',差額：'+cast((@tableDataCount2His-@tableDataCountHis) as nvarchar(20));
print '注意：正式環(huán)境一直有數(shù)據(jù)變動，所以會有一定的偏差！';
select @endTime = GETDATE();
print '總耗時（毫秒）：' +cast( datediff(millisecond,@beginTime,@endTime) as nvarchar(20));
END
我們在測試數(shù)據(jù)庫中來簡單執(zhí)行下試試效果：
6、使用場景特別說明
此方式是采用 insert into ... select from 的方式進(jìn)行數(shù)據(jù)遷移。這個思路由于是最簡單的數(shù)據(jù)遷移邏輯，僅適用于小數(shù)據(jù)量的情況（一般表數(shù)據(jù)低于500萬），當(dāng)數(shù)據(jù)量大于500萬之后千萬別用此方法，因為此時的insert into ... select from 會執(zhí)行很慢，有很大可能會影響正式環(huán)境的運行。
還有就是此方法，由于是SQL直接訪問數(shù)據(jù)庫，所以要求當(dāng)前業(yè)務(wù)庫和歷史數(shù)據(jù)都能訪問（也就是同一個數(shù)據(jù)庫實例），如果異地不同的數(shù)據(jù)庫也沒辦法處理。
所以此方法僅適用于簡單的歷史數(shù)據(jù)遷移場景，使用前提有限，適合小項目使用。
對于大數(shù)據(jù)量的（單表1千萬以上的數(shù)據(jù)），又不能影響當(dāng)下業(yè)務(wù)性能指標(biāo)的數(shù)據(jù)遷移方式，請看下篇文章介紹。
到此這篇關(guān)于使用SQL SERVER存儲過程實現(xiàn)歷史數(shù)據(jù)遷移的文章就介紹到這了,更多相關(guān)SQL SERVER存儲過程數(shù)據(jù)遷移內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信