使用SQL SERVER存儲過程實現歷史數據遷移

2021 年 9 月 19 日
筆記
SQL Server, SQL SERVER資料庫技術相關, 資料庫技術, 數據遷移

今天講下軟體開發中最常見的歷史數據遷移方式。在講遷移之前，先簡單介紹下幾個基本概念。

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 USE [Tyingsoft.GLPS]  
  2 GO
  3 /****** Object:  StoredProcedure [dbo].[TY_SP_ApiRequestToHis]    Script Date: 2021-09-16 15:35:55 ******/
  4 SET ANSI_NULLS ON
  5 GO
  6 SET QUOTED_IDENTIFIER ON
  7 GO
  8 -- =============================================
  9 -- Author:         
 10 -- Create date:    
 11 -- Last Edit date: 
 12 -- Description:    <介面請求記錄表GLPS_APIREQUEST數據遷移>
 13 -- =============================================
 14 ALTER PROCEDURE [dbo].[TY_SP_ApiRequestToHis]
 15     -- Add the parameters for the stored procedure here
 16     @PreCountN int = 2000     --每次執行的條數N
 17 AS
 18 BEGIN
 19     -- SET NOCOUNT ON added to prevent extra result sets from
 20     -- interfering with SELECT statements.
 21     SET NOCOUNT ON;
 22 
 23     declare @tableDataCount int;          --遷移前的數據條數
 24     declare @tableDataCountHis int;       --遷移前歷史資料庫的數據條數
 25     declare @tableDataCount2 int;         --遷移後的數據條數
 26     declare @tableDataCount2His int;      --遷移後歷史資料庫的數據條數
 27     declare @maxCreateTime datetime;      --取N條數據中最大的創建時間
 28     declare @maxCreateTimeHis datetime;   --歷史資料庫中的最大創建時間 
 29     declare @beginTime datetime;          --開始執行時間
 30     declare @endTime datetime;            --執行完成時間
 31     declare @execTimeMS int;              --執行時間（毫秒數）
 32 
 33     --中間步驟debugger耗時使用
 34     declare @tmpBeginTime datetime;       --(臨時)開始執行時間
 35     declare @tmpEndTime datetime;         --(臨時)執行完成時間
 36     declare @tmpExecTimeMS int;           --(臨時)執行時間（毫秒數）
 37 
 38     select @beginTime = getdate();
 39 
 40     --遷移前：先查詢數據條數
 41     select @tableDataCount = count(1) from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST;
 42     select @tableDataCountHis =count(1) from  [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST;
 43 
 44     print '【介面請求記錄表（GLPS_APIREQUEST）數據遷移】開始執行時間：' + convert(nvarchar(50),@beginTime,20);
 45     print '本次計劃遷移數據條數：'+ cast( @PreCountN as nvarchar(20));
 46 
 47     --創建一個臨時公用表達式 （表中最早創建的N條數據）
 48     with topNRecord (FCREATETIME)
 49     as
 50     (
 51         select top (@PreCountN) FCREATETIME from GLPS_APIREQUEST  order by FCREATETIME 
 52     )
 53 
 54     --取N條數據中最大的創建時間
 55     select @maxCreateTime =max(FCREATETIME) from topNRecord
 56     
 57     print '對應遷移數據FCREATETIME為：'+ convert(nvarchar(50),@maxCreateTime,21); --日期轉化為字元串格式：yyyy-MM-dd HH:mm:ss.fff
 58 
 59     
 60     select @tmpBeginTime = GETDATE();  --中間步驟開始計時
 61 
 62     --第一步：將N條數數據寫入到歷史資料庫
 63     insert into [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST
 64     select * from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST  where  FCREATETIME <=@maxCreateTime
 65     
 66     select @tmpEndTime = GETDATE();   --中間步驟計時結束
 67     print '數據遷移，插入耗時（毫秒）：' +cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20));
 68 
 69 
 70     --第二步：對比歷史資料庫的數據
 71     select @maxCreateTimeHis=max(FCREATETIME) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST
 72 
 73     if @maxCreateTime = @maxCreateTimeHis  
 74        begin
 75             select @tmpBeginTime = GETDATE();  --中間步驟開始計時
 76 
 77             --第三步：執行完以後，再刪除數據
 78             delete from GLPS_APIREQUEST where FCREATETIME <=@maxCreateTime 
 79             print '遷移後刪除數據條數：' + cast( @@ROWCOUNT as nvarchar(50));
 80 
 81             select @tmpEndTime = GETDATE();       --中間步驟計時結束
 82             print '數據遷移，刪除耗時（毫秒）：' +cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20));
 83         end
 84     else
 85         print '遷移後，日期校驗錯誤，未刪除數據！！！'
 86 
 87     --遷移後：再查詢數據條數
 88     select @tableDataCount2 = count(1) from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST;
 89     select @tableDataCount2His =count(1) from  [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST;
 90 
 91     print '遷移前GLPS_APIREQUEST的數據條數：' + cast(@tableDataCount as nvarchar(20))
 92      + ',遷移後數據條數：'+cast(@tableDataCount2 as nvarchar(20))
 93      + ',差額：'+cast((@tableDataCount2-@tableDataCount) as nvarchar(20));
 94     
 95     print '遷移前His.GLPS_APIREQUEST的數據條數：' 
 96     + cast(@tableDataCountHis as nvarchar(20))
 97     +',遷移後數據條數：'+cast(@tableDataCount2His as nvarchar(20))
 98     + ',差額：'+cast((@tableDataCount2His-@tableDataCountHis) as nvarchar(20));
 99 
100     print '注意：正式環境一直有數據變動，所以會有一定的偏差！';
101 
102    
103     select @endTime = GETDATE();
104     print '總耗時（毫秒）：' +cast( datediff(millisecond,@beginTime,@endTime) as nvarchar(20));
105     
106 
107 END

我們在測試資料庫中來簡單執行下試試效果：

6、使用場景特別說明

此方式是採用 insert into … select from 的方式進行數據遷移。這個思路由於是最簡單的數據遷移邏輯，僅適用於小數據量的情況（一般表數據低於500萬），當數據量大於500萬之後千萬別用此方法，因為此時的insert into … select from 會執行很慢，有很大可能會影響正式環境的運行。

還有就是此方法，由於是SQL直接訪問資料庫，所以要求當前業務庫和歷史數據都能訪問（也就是同一個資料庫實例），如果異地不同的資料庫也沒辦法處理。

所以此方法僅適用於簡單的歷史數據遷移場景，使用前提有限，適合小項目使用。

對於大數據量的（單表1千萬以上的數據），又不能影響當下業務性能指標的數據遷移方式，請看下篇文章介紹。

Tags: SQL Server SQL SERVER資料庫技術相關資料庫技術數據遷移