如何使用SQL SERVER存儲(chǔ)過(guò)程實(shí)現(xiàn)歷史數(shù)據(jù)遷移方式

這篇文章給大家介紹如何使用SQL SERVER存儲(chǔ)過(guò)程實(shí)現(xiàn)歷史數(shù)據(jù)遷移方式，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。

1、什么是歷史數(shù)據(jù)遷移？

簡(jiǎn)單直白地說(shuō)：就是將一些創(chuàng)建時(shí)間比較久而且不常用的歷史數(shù)據(jù)，存儲(chǔ)到另一個(gè)地方（可以是另一個(gè)數(shù)據(jù)，也可以是另一個(gè)表），一般歷史數(shù)據(jù)遷移的數(shù)據(jù)，都是不會(huì)更改了的數(shù)據(jù)，后續(xù)只可能需要查詢統(tǒng)計(jì)而已。

2、歷史數(shù)據(jù)遷移的目的

減少在用數(shù)據(jù)庫(kù)的數(shù)量，因?yàn)閿?shù)據(jù)量越大，數(shù)據(jù)庫(kù)操作數(shù)據(jù)（包括：查詢、排序等等）的時(shí)間也就越長(zhǎng)，當(dāng)一個(gè)表的數(shù)據(jù)達(dá)到上千萬(wàn)級(jí)以上，再來(lái)個(gè)多條件多表查詢的時(shí)候，是會(huì)有響應(yīng)速度慢的可能。（因?yàn)椴煌_(kāi)發(fā)人員寫的邏輯，不可能保障每個(gè)SQL都是高效率執(zhí)行的SQL）

所以及時(shí)遷移走一些歷史數(shù)據(jù)，是對(duì)整個(gè)系統(tǒng)性能的提升是有一定好處的。

3、什么時(shí)候需要做歷史數(shù)據(jù)遷移？

最簡(jiǎn)單的情況，就是你感覺(jué)程序有變慢的趨勢(shì)，那就可以開(kāi)始考慮歷史數(shù)據(jù)遷移了。

原則上，小企業(yè)服務(wù)器不多，硬件配置也不是很高的情況下，單表500萬(wàn)以上的數(shù)據(jù)，最好就開(kāi)始慢慢遷移，別動(dòng)不動(dòng)就等到幾千萬(wàn)的數(shù)據(jù)才開(kāi)始遷移。

根據(jù)產(chǎn)生數(shù)據(jù)量的大小判斷，一般保持1年左右的業(yè)務(wù)數(shù)據(jù)即可，一年前的歷史數(shù)據(jù)都遷入歷史上數(shù)據(jù)庫(kù)。如果每天產(chǎn)生的數(shù)據(jù)量實(shí)在太大的話，一般就需要考慮自動(dòng)分表存儲(chǔ)，當(dāng)然如果沒(méi)做這個(gè)的話，可以在不影響日常業(yè)務(wù)的情況下，實(shí)時(shí)在用業(yè)務(wù)數(shù)據(jù)庫(kù)只保留最近3-6個(gè)月的數(shù)據(jù)。

4、數(shù)據(jù)遷移的基本思路

1）、第一次遷移創(chuàng)建一個(gè)一模一樣的表結(jié)構(gòu)（只要第一次遷移前創(chuàng)建即可）

2）、按照數(shù)據(jù)的創(chuàng)建時(shí)間排序，把最早的數(shù)據(jù)N條數(shù)據(jù)查出，同時(shí)插入到歷史數(shù)據(jù)表中。

insert into ... select from

3）、檢測(cè)插入數(shù)據(jù)的準(zhǔn)確性，一定要保證是N條沒(méi)錯(cuò)。之后刪除在用的業(yè)務(wù)數(shù)據(jù)庫(kù)。

4）、當(dāng)遷移數(shù)據(jù)中途有誤時(shí)，終止程序，但不能刪除在用數(shù)據(jù)庫(kù)，需要開(kāi)發(fā)人員核對(duì)數(shù)據(jù)。

5）、根據(jù)遷移的對(duì)性能的影響，N不能太多，最多一次5W到10W條吧（根據(jù)服務(wù)器的性能配置，推薦一次遷移1W至5W條數(shù)據(jù)影響較?。Ｈ绻w移大量數(shù)據(jù)，可以考慮分批執(zhí)行。

5、數(shù)據(jù)遷移存儲(chǔ)過(guò)程代碼示例

代碼如下：（無(wú)需過(guò)多解釋，很簡(jiǎn)單的代碼，一看就懂）

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:    <接口請(qǐng)求記錄表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ù)庫(kù)的數(shù)據(jù)條數(shù)
    declare @tableDataCount2 int;         --遷移后的數(shù)據(jù)條數(shù)
    declare @tableDataCount2His int;      --遷移后歷史數(shù)據(jù)庫(kù)的數(shù)據(jù)條數(shù)
    declare @maxCreateTime datetime;      --取N條數(shù)據(jù)中最大的創(chuàng)建時(shí)間
    declare @maxCreateTimeHis datetime;   --歷史數(shù)據(jù)庫(kù)中的最大創(chuàng)建時(shí)間
    declare @beginTime datetime;          --開(kāi)始執(zhí)行時(shí)間
    declare @endTime datetime;            --執(zhí)行完成時(shí)間
    declare @execTimeMS int;              --執(zhí)行時(shí)間（毫秒數(shù)）

    --中間步驟debugger耗時(shí)使用
    declare @tmpBeginTime datetime;       --(臨時(shí))開(kāi)始執(zhí)行時(shí)間
    declare @tmpEndTime datetime;         --(臨時(shí))執(zhí)行完成時(shí)間
    declare @tmpExecTimeMS int;           --(臨時(shí))執(zhí)行時(shí)間（毫秒數(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 '【接口請(qǐng)求記錄表（GLPS_APIREQUEST）數(shù)據(jù)遷移】開(kāi)始執(zhí)行時(shí)間：' + convert(nvarchar(50),@beginTime,20);
    print '本次計(jì)劃遷移數(shù)據(jù)條數(shù)：'+ cast( @PreCountN as nvarchar(20));

    --創(chuàng)建一個(gè)臨時(shí)公用表達(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)建時(shí)間
    select @maxCreateTime =max(FCREATETIME) from topNRecord

    print '對(duì)應(yīng)遷移數(shù)據(jù)FCREATETIME為：'+ convert(nvarchar(50),@maxCreateTime,21); --日期轉(zhuǎn)化為字符串格式：yyyy-MM-dd HH:mm:ss.fff


    select @tmpBeginTime = GETDATE();  --中間步驟開(kāi)始計(jì)時(shí)

    --第一步：將N條數(shù)數(shù)據(jù)寫入到歷史數(shù)據(jù)庫(kù)
    insert into [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST
    select * from [Tyingsoft.GLPS].dbo.GLPS_APIREQUEST  where  FCREATETIME <=@maxCreateTime

    select @tmpEndTime = GETDATE();   --中間步驟計(jì)時(shí)結(jié)束
    print '數(shù)據(jù)遷移，插入耗時(shí)（毫秒）：' +cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20));


    --第二步：對(duì)比歷史數(shù)據(jù)庫(kù)的數(shù)據(jù)
    select @maxCreateTimeHis=max(FCREATETIME) from [Tyingsoft.GLPS_His].dbo.GLPS_APIREQUEST

    if @maxCreateTime = @maxCreateTimeHis
       begin
            select @tmpBeginTime = GETDATE();  --中間步驟開(kāi)始計(jì)時(shí)

            --第三步：執(zhí)行完以后，再刪除數(shù)據(jù)
            delete from GLPS_APIREQUEST where FCREATETIME <=@maxCreateTime
            print '遷移后刪除數(shù)據(jù)條數(shù)：' + cast( @@ROWCOUNT as nvarchar(50));

            select @tmpEndTime = GETDATE();       --中間步驟計(jì)時(shí)結(jié)束
            print '數(shù)據(jù)遷移，刪除耗時(shí)（毫秒）：' +cast( datediff(millisecond,@tmpBeginTime,@tmpEndTime) as nvarchar(20));
        end
    else
        print '遷移后，日期校驗(yàn)錯(cuò)誤，未刪除數(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ù)變動(dòng)，所以會(huì)有一定的偏差！';


    select @endTime = GETDATE();
    print '總耗時(shí)（毫秒）：' +cast( datediff(millisecond,@beginTime,@endTime) as nvarchar(20));


END

我們?cè)跍y(cè)試數(shù)據(jù)庫(kù)中來(lái)簡(jiǎn)單執(zhí)行下試試效果：

6、使用場(chǎng)景特別說(shuō)明

此方式是采用 insert into ... select from 的方式進(jìn)行數(shù)據(jù)遷移。這個(gè)思路由于是最簡(jiǎn)單的數(shù)據(jù)遷移邏輯，僅適用于小數(shù)據(jù)量的情況（一般表數(shù)據(jù)低于500萬(wàn)），當(dāng)數(shù)據(jù)量大于500萬(wàn)之后千萬(wàn)別用此方法，因?yàn)榇藭r(shí)的insert into ... select from 會(huì)執(zhí)行很慢，有很大可能會(huì)影響正式環(huán)境的運(yùn)行。

還有就是此方法，由于是SQL直接訪問(wèn)數(shù)據(jù)庫(kù)，所以要求當(dāng)前業(yè)務(wù)庫(kù)和歷史數(shù)據(jù)都能訪問(wèn)（也就是同一個(gè)數(shù)據(jù)庫(kù)實(shí)例），如果異地不同的數(shù)據(jù)庫(kù)也沒(méi)辦法處理。

所以此方法僅適用于簡(jiǎn)單的歷史數(shù)據(jù)遷移場(chǎng)景，使用前提有限，適合小項(xiàng)目使用。

關(guān)于如何使用SQL SERVER存儲(chǔ)過(guò)程實(shí)現(xiàn)歷史數(shù)據(jù)遷移方式就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。