使用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、數據遷移存儲過程代碼示例

代碼如下:(無需過多解釋,很簡單的代碼,一看就懂)

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存儲過程數據遷移內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: