萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server 動態存儲過程按日期分表儲存實戰範例

前言

高流量系統面對海量寫入時,單表容量與索引維護成本急劇上升。按日期動態建表(日分表)是常見應對策略:將資料按天切分到獨立物理表,查詢僅掃描目標日期分區,大幅降低 I/O 與鎖爭用。本文分享一個生產級動態存儲過程範例,展示如何在 T-SQL 中實現「按日期自動建表 → 條件判斷 → 寫入對應分表」的完整邏輯。

一、核心設計思路

  • 命名規範:表名含日期後綴,如 SocketMe20240115、MessSend20240115、SockData20240115,便於識別與維護。
  • 冪等建表:每次寫入前檢查 sysobjects,不存在才 CREATE TABLE,避免重複建表報錯。
  • 動態 SQL:因表名為變數,需用 EXEC(@sql) 執行 DDL/DML。
  • 業務邏輯封裝:存儲過程接收業務參數,內部完成分表路由、去重、關聯寫入,上層應用無感知。

二、完整存儲過程碼(含註解)

ALTER PROC [dbo].[EventInsert]
    @chrTagData   VARCHAR(50),   -- 設備編號
    @intEData     INT,
    @chrJZData    VARCHAR(50),
    @intDYData    INT,
    @intXHData    INT,
    @createdata   DATETIME,
    @chrtype      VARCHAR(1)     -- 事件類型
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 參數防呆
    IF ISNULL(@chrtype, '') = '' RETURN;

    -- 2. 取得事件標題(用於訊息內容)
    DECLARE @chrTitle VARCHAR(1000);
    SELECT @chrTitle = CategoryTitle FROM EventCategory WHERE CategoryID = @chrtype;

    -- 3. 組裝今日分表名稱
    DECLARE @chrDate       VARCHAR(8)  = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), '-', '');
    DECLARE @chrMetabName  VARCHAR(50) = 'SocketMe'  + @chrDate; -- 模擬告警表
    DECLARE @chrSendTabName VARCHAR(50) = 'MessSend'  + @chrDate; -- 訊息彈出表
    DECLARE @chrSockDataTabName VARCHAR(50) = 'SockData' + @chrDate; -- 原始資料表

    -- 4. 動態建表:模擬告警表
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = N'
    IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = ''' + @chrMetabName + ''' AND type = ''U'')
    BEGIN
        CREATE TABLE ' + QUOTENAME(@chrMetabName) + N'(
            SMeID       INT IDENTITY(1,1) PRIMARY KEY,
            tabname     VARCHAR(50),
            TagData     VARCHAR(50),
            TagDataMe   VARCHAR(500),
            Pcount      INT NULL,
            Content     VARCHAR(5000),
            UserID      INT NULL,
            JZData      VARCHAR(50),
            EData       INT,
            DYData      INT,
            XHData      INT,
            Type        VARCHAR(1),
            State       VARCHAR(1),
            IfClose     VARCHAR(1),
            CloseDate   DATETIME,
            CreateDate  DATETIME
        );
    END';
    EXEC (@sql);

    -- 5. 動態建表:訊息彈出表
    SET @sql = N'
    IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = ''' + @chrSendTabName + ''' AND type = ''U'')
    BEGIN
        CREATE TABLE ' + QUOTENAME(@chrSendTabName) + N'(
            MessID      INT IDENTITY(1,1) PRIMARY KEY,
            TabName     VARCHAR(50),
            TabPrID     INT,
            MessTitle   VARCHAR(500),
            TagData     VARCHAR(50),
            TagDataMe   VARCHAR(1000),
            Content     VARCHAR(2000),
            Type        VARCHAR(1),
            CreateDate  DATETIME
        );
    END';
    EXEC (@sql);

    -- 6. 業務邏輯:去重檢查 → 寫入主表 → 取身份值 → 清理舊訊息 → 寫入訊息表
    SET @sql = N'
    IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(@chrMetabName) + N' WHERE TagData = ''' + @chrTagData + ''' AND Type = ''' + @chrtype + ''' AND IfClose = ''0'')
    BEGIN
        -- 寫入主表
        INSERT INTO ' + QUOTENAME(@chrMetabName) + N' (tabname, TagData, TagDataMe, Content, JZData, EData, DYData, XHData, Type, IfClose, CreateDate, State)
        SELECT ''' + @chrMetabName + ''', ''' + @chrTagData + ''', dbo.funTagDataMeget_all(''' + @chrTagData + '''),
               ''' + @chrTitle + N',位置:['' + dbo.funGetEvenAddget(''' + @chrJZData + ''') + '']'',
               ''' + @chrJZData + ''', ' + CAST(@intEData AS VARCHAR(20)) + N', ' + CAST(@intDYData AS VARCHAR(20)) + N', ' + CAST(@intXHData AS VARCHAR(20)) + N',
               ''' + @chrtype + ''', ''0'', GETDATE(), ''0'';

        DECLARE @intSMeID INT = @@IDENTITY;

        -- 清理舊訊息並寫入新訊息
        DELETE ' + QUOTENAME(@chrSendTabName) + N' WHERE TagData = ''' + @chrTagData + ''' AND Type = ''' + @chrtype + ''';
        INSERT INTO ' + QUOTENAME(@chrSendTabName) + N' (TabName, TabPrID, MessTitle, TagData, Content, Type, CreateDate)
        SELECT ''' + @chrMetabName + ''', @intSMeID,
               dbo.funTagDataMeget_all(''' + @chrTagData + ''') + ''' + @chrTitle + ''',
               ''' + @chrTagData + ''',
               dbo.funTagDataMeget_all(''' + @chrTagData + ''') + ''' + @chrTitle + N',位置:['' + dbo.funGetEvenAddget(''' + @chrJZData + ''') + '']'',
               ''' + @chrtype + ''', GETDATE();
    END';
    EXEC (@sql);
END

三、關鍵技術點解析

1. QUOTENAME() 防注入

動態拼接表名時使用 QUOTENAME(@var) 自動加上 [],防止異常字元導致語法錯誤或注入風險。

2. @@IDENTITY 取回自增鍵

主表寫入後立即取得 @@IDENTITY 作為訊息表外鍵,保證關聯正確。

3. 自定義函數復用

範例中呼叫 dbo.funTagDataMeget_all()、dbo.funGetEvenAddget() 封裝複雜格式化邏輯,存儲過程保持精簡。

4. 交易與鎖控制

生產環境建議在外層包裝顯式交易,並視情況加 TABLOCKX / HOLDLOCK 提示,避免並發建表衝突。

四、擴展與維護建議

  • 分表保留策略:配合 Agent Job 定期歸檔或刪除超過保留期(如 90 天)的分表。
  • 統計資訊更新:新分表建立後主動執行 UPDATE STATISTICS,確保優化器有準確基數估計。
  • 查詢層適配:上層查詢改用 UNION ALL 跨分表,或引入分區視圖/資料表分區(Enterprise Edition)實現透明查詢。
  • 監控告警:監控分表數量、單表行數、建表耗時,異常及時通知。

結語

動態分表存儲過程模式將「分表路由、建表、寫入」封裝於資料庫層,應用層無感知,兼具效能與維護性。配合保留策略與監控體系,可支撐億級寫入場景長期穩定運行。

copyright © 萬盛學電腦網 all rights reserved