【Paddy】如何将物理表分割成动态数据表与静态数据表

前言　　

　　一般来说，物理表的增、删、改、查都受到数据量的制约，进而影响了性能。

　　很多情况下，你所负责的业务关键表中，每日变动的数据库与不变动的数据量比较，相差非常大。

　　这里我们将变动的数据称为动态数据，不变动的数据称为静态数据。

　　举个例子，1张1000W的表，每日动态数据只有1W条，999W条的数据都为静态。往往select或者重复改变的数据都在动态数据中。比如订单表。

　　所以，如果将动态数据库从表中剥离出来，分割两张表，一张动态数据表，一张静态数据表，从数据量的角度来看，性能是不是就会自然提高了？

　　下图为一张现实表和一张理想化的结构（蓝色为静态数据：1000W、黄色为动态数据：100W）：

　　【Paddy】如何将物理表分割成动态数据表与静态数据表

　　希望动态数据和静态数据分割开

　　想法再好，也比不上现实的残酷。很多朋友可能都想到这种架构，但实现起来问题非常多：

　　1.　　如何将动态数据自动从静态数据中剥离？如何维护？

　　2.　　程序代码改动量不可预计。尤其核心表的使用遍布各地，分割成两张表后，所有代码基本都要重构。

　　3.　　表分区是否可以达到同样目的，NO~因为表分区无法100%区分动态与静态，而且分区列的使用决定了效率。

　　下面开始，进入本次主题，如何在SQL Server中达到动态数据、静态数据分割；两表数据如何自动维护；程序统一访问逻辑表名，零维护成本。

正文

　实现方法：利用视图 + 视图触发器

　　没错，没有了~就是靠这两货，就解决了，下面我们来看看逻辑图（画的不好，见谅）。

　　【Paddy】如何将物理表分割成动态数据表与静态数据表

最后，让我们来看看具体事例

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

这个时候的两张表的数据如下：

【Paddy】如何将物理表分割成动态数据表与静态数据表

然后，我们为了保证动态表的新增数据的自增值是静态表的当前标识值+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