今天講下軟件開發中最常見的歷史數據遷移方式。在講遷移之前，先簡單介紹下幾個基本概念。
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、數據遷移存儲過程代碼示例
代碼如下：（無需過多解釋，很簡單的代碼，一看就懂）
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
|
USE [Tyingsoft.GLPS]GO/****** Object: StoredProcedure [dbo].[TY_SP_ApiRequestToHis] Script Date: 2021-09-16 15:35:55 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- 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 --每次執行的條數NASBEGIN -- 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存儲過程數據遷移內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://www.cnblogs.com/feitianxinhong/p/15310913.html
