你的位置:首页 > 数据库

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


前言  

 

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

 

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

 

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

 

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

 

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

 

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

  

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

  

  

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

  

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

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

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

 

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

 

正文

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

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

  

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

 

1.  创建demo数据库,和动态表(TB_A_NEW)、历史表(TB_A_OLD)

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

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

 

然后,我们为了保证动态表的新增数据的自增值是静态表的当前标识值+1,这样两表记录合并才能衔接。

DECLARE @IDENT BIGINTSELECT @IDENT = Ident_current('TB_A_OLD') + 1DBCC 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 NULLGO

创建视图触发器(Insert、Update)

USE demoGOCREATE TRIGGER [dbo].[TR_Insert]ON [dbo].[View_TB_A]INSTEAD OF INSERTAS BEGIN   SET NOCOUNT ON   INSERT INTO TB_A_NEW         (NAME)   SELECT NAME   FROM  inserted ENDGOUSE demoGOCREATE TRIGGER [dbo].[TR_Update]ON [dbo].[View_TB_A]INSTEAD OF UPDATEAS 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 ENDGO

下面,我们来测试下SELECT、UPDATE、INSERT

Select

SELECT的结果就是刚才的插入10条记录,通过!

 

Insert

INSERT到视图中,会在TB_A_NEW表中新增一条记录,TB_A_OLD并没有任何变化。也就是自动分配到了动态表中,通过!

 

Update

然后我们继续更新id=2的记录,看看结果如何

最后,我们继续查询视图VIEW_TB_A,看看最终返回的结果。

看到最终结果11条,并且之前通过视图的更新,插入都返回了正确结果,通过!

 

至此,测试结束,通过视图和视图触发器,实现了统一名称访问,底册物理表分割动态表和静态表,测试过程并不包含delete,感兴趣的童鞋稍微琢磨下就出来了。

 

如果需要合并动态表与静态表,只需将id相同记录,更新到静态表,静态表不存在的id对应动态数据,插入到静态表中即可。

本次内容主意在实现方法,具体性能提升测试,日后再做说明,简单说下,就是触发器的逻辑处理,放在了物理表插入数据之前。将数据分类存储。所以逻辑处理开销会增加,但是物理存储分割。在数据量影响性能明显的场景中会非常适用。

如果你的数据库是SQL Server 2014,那么将静态和动态表定义到内存表中,动态数据又剥离了,具体提升多少这里不做猜测,有环境的朋友可以试试哦~

 

另:

  利用以上方法,也可以解决肖桑(大菠萝)和笑东风提到的的主键int修改为bigint型问题

  (http://www.cnblogs.com/TeyGao/p/4463389.html)

      如果每日一次合并动态表与静态表数据,动态数据表就是当日的差异数据哦,扩展使用非常方便。

 

好~就到这里,再见吧~