【Paddy】如何將物理表分割成動態數據表與靜態數據表 - SQLServer2012－IT工程師數位筆記本

文章出處

前言　　

　　一般來說，物理表的增、刪、改、查都受到數據量的制約，進而影響了性能。

　　很多情況下，你所負責的業務關鍵表中，每日變動的數據庫與不變動的數據量比較，相差非常大。

　　這里我們將變動的數據稱為動態數據，不變動的數據稱為靜態數據。

　　舉個例子，1張1000W的表，每日動態數據只有1W條，999W條的數據都為靜態。往往select或者重復改變的數據都在動態數據中。比如訂單表。

　　所以，如果將動態數據庫從表中剝離出來，分割兩張表，一張動態數據表，一張靜態數據表，從數據量的角度來看，性能是不是就會自然提高了？

　　下圖為一張現實表和一張理想化的結構（藍色為靜態數據：1000W、黃色為動態數據：100W）：

　　希望動態數據和靜態數據分割開

　　想法再好，也比不上現實的殘酷。很多朋友可能都想到這種架構，但實現起來問題非常多：

　　1.　　如何將動態數據自動從靜態數據中剝離？如何維護？

　　2.　　程序代碼改動量不可預計。尤其核心表的使用遍布各地，分割成兩張表后，所有代碼基本都要重構。

　　3.　　表分區是否可以達到同樣目的，NO~因為表分區無法100%區分動態與靜態，而且分區列的使用決定了效率。

　　下面開始，進入本次主題，如何在SQL Server中達到動態數據、靜態數據分割；兩表數據如何自動維護；程序統一訪問邏輯表名，零維護成本。

正文

　實現方法：利用視圖 + 視圖觸發器

　　沒錯，沒有了~就是靠這兩貨，就解決了，下面我們來看看邏輯圖（畫的不好，見諒）。

最后，讓我們來看看具體事例

1.　　創建demo數據庫，和動態表(TB_A_NEW)、歷史表(TB_A_OLD)

CREATE DATABASE demo
GO
ALTER DATABASE demo ADD FILEGROUP HekatonFG CONTAINS MEMORY_OPTIMIZED_DATA;
GO
ALTER DATABASE demo ADD FILE( NAME = 'Memory', FILENAME ='C:\Memory.ndf') TO FILEGROUP [HekatonFG];
GO
CREATE TABLE TB_A_OLD
(
	id INT IDENTITY(1,1) PRIMARY KEY,
　　　　 NAME NVARCHAR(36)
GO
CREATE TABLE TB_A_NEW
  (
     id   INT IDENTITY(1, 1) PRIMARY KEY,
     NAME NVARCHAR(36)
  )
GO
INSERT INTO TB_A_OLD
SELECT Newid()
GO 10

這個時候的兩張表的數據如下：

然后，我們為了保證動態表的新增數據的自增值是靜態表的當前標識值+1，這樣兩表記錄合并才能銜接。

DECLARE @IDENT BIGINT

SELECT @IDENT = Ident_current('TB_A_OLD') + 1

DBCC checkident('TB_A_NEW', reseed, @IDENT)

創建視圖（View_TB_A）

CREATE VIEW [dbo].[View_TB_A]
AS
  SELECT *
  FROM   TB_A_NEW
  UNION ALL
  SELECT old.*
  FROM   TB_A_OLD old
         LEFT JOIN TB_A_NEW new
                ON old.id = new.id
  WHERE  new.NAME IS NULL

GO

創建視圖觸發器（Insert、Update）

USE demo

GO

CREATE TRIGGER [dbo].[TR_Insert]
ON [dbo].[View_TB_A]
INSTEAD OF INSERT
AS
  BEGIN
      SET NOCOUNT ON

      INSERT INTO TB_A_NEW
                  (NAME)
      SELECT NAME
      FROM   inserted
  END

GO

USE demo

GO

CREATE TRIGGER [dbo].[TR_Update]
ON [dbo].[View_TB_A]
INSTEAD OF UPDATE
AS
  BEGIN
      SET NOCOUNT ON

      --1. 判斷update中，包含的記錄是否都在new表中
      DECLARE @count BIGINT

      SELECT @count = Count(1)
      FROM   deleted

      UPDATE TB_A_NEW
      SET    NAME = b.NAME
      FROM   TB_A_NEW a,
             inserted b
      WHERE  a.id = b.id

      IF @@ROWCOUNT < @count
        BEGIN
            SET IDENTITY_INSERT TB_A_NEW ON

            INSERT INTO TB_A_NEW
                        (id,
                         NAME)
            SELECT i.id,
                   i.NAME
            FROM   inserted i
                   LEFT JOIN TB_A_NEW n
                          ON i.id = n.id
            WHERE  n.NAME IS NULL
        END
  END

GO

下面，我們來測試下SELECT、UPDATE、INSERT

Select