- 由于sqlserver的设计特殊性,一般大量数据一般都是采用水平分表,而垂直分表只是把text、图片都较大数据放到单独的表中,这样数据设计会更合理,相对于mysql可能要好一点,mssql本来就是一个文件,基本上提升不大,目前来看几十万的数据没有分不分表没有任何影响,对于千万以上数据还是采用水平分表比较好。
复制代码
而 垂直分表 则相对很少见到和用到,因为这大概是数据库计划上的标题了。假如数据库中一张表有部门字段险些从不不更改但经常查询,而部门字段的数据频繁更改,这种计划放到同一个表中就不合理了,相互影响太大了。在已存在改情况的表的时间,可以思量按列拆分表,即垂直拆分。
由于垂直分表的案例比力少,最近因为存在如许的表,以是个人捣鼓了一下。
源表计划布局:- -- 源表
- CREATE TABLE [dbo].[DemoTab](
- [Guid] [uniqueidentifier] NOT NULL,
- [UserName] [nvarchar](30) NOT NULL,
- [Password] [nvarchar](30) NOT NULL,
- [UserAccount] [varchar](30) NOT NULL,
- [Amount] [numeric](18, 4) NULL,
- CONSTRAINT [PK_DemoTab] PRIMARY KEY CLUSTERED ([Guid])
- )
- GO
-
-
- ALTER TABLE [dbo].[DemoTab]
- ADD CONSTRAINT [DF_DemoTab_Guid] DEFAULT (newsequentialid()) FOR [Guid]
- GO
-
- -- 原来是访问视图的(好处就是视图层不变)
- CREATE VIEW [dbo].[VDemoTab]
- AS
- SELECT [Guid],[UserName],[Password],[UserAccount],[Amount]
- FROM [dbo].[DemoTab]
- GO
复制代码
注:拆分后各表的主键都是相同了,而且拆分后的表是规范化的。
如今拆成两张表:
留意选择一张表作为基表,其他表都有与该表的外键。- -- 分表【1】,以该表为"主表",其他拆分出的表为"子表"
- CREATE TABLE [dbo].[DemoTab001](
- [Guid] [uniqueidentifier] NOT NULL,
- [UserName] [nvarchar](30) NOT NULL,
- [Password] [nvarchar](30) NOT NULL,
- CONSTRAINT [PK_DemoTab001] PRIMARY KEY CLUSTERED ([Guid])
- )
- GO
-
- -- 主键默认值可以不需要,因为插入数据前需要确定主键值
- --ALTER TABLE [dbo].[DemoTab001]
- --ADD CONSTRAINT [DF_DemoTab001_Guid] DEFAULT (newsequentialid()) FOR [Guid]
- --GO
-
- -- 分表【2】,"子表"
- CREATE TABLE [dbo].[DemoTab002](
- [Guid] [uniqueidentifier] NOT NULL,
- [UserAccount] [varchar](30) NOT NULL,
- [Amount] [numeric](18, 4) NULL,
- CONSTRAINT [PK_DemoTab002] PRIMARY KEY CLUSTERED ([Guid])
- )
- GO
-
- -- 主键默认值可以不需要,因为插入数据前需要确定主键值
- --ALTER TABLE [dbo].[DemoTab002]
- --ADD CONSTRAINT [DF_DemoTab002_Guid] DEFAULT (newsequentialid()) FOR [Guid]
- --GO
-
-
- -- 若主表变更主键则级联更新或删除(主键通常是不更新的,也可省去 ON UPDATE CASCADE)
- ALTER TABLE [dbo].[DemoTab002]
- ADD CONSTRAINT [FK_DemoTab002_DemoTab001_Guid] FOREIGN KEY ([Guid])
- REFERENCES [DemoTab001]([Guid]) ON UPDATE CASCADE ON DELETE CASCADE
- GO
复制代码
假如之前是对单个表大概视图使用,拆分之后逻辑层改动大概很多,为保持改动最小,可以用连合视图使用。怎么连接表依个人情况而定。- -- 拆分后使用联合视图(INNER JOIN 也可以)
- ALTER VIEW [dbo].[VDemoTab]
- AS
- SELECT T1.[Guid],T1.[UserName],T1.[Password],T2.[UserAccount],T2.[Amount]
- FROM [dbo].[DemoTab001] T1 LEFT JOIN [dbo].[DemoTab002] T2 ON T1.[Guid]=T2.[Guid]
- GO
复制代码
这时标题来了,要对表举行DML使用,insert , update , delete 怎么解决?因为要求主键是分散在多个表而且是相同的!
这时只能用思量触发器来保证同等性了,触发器则界说在视图上,使用的是 INSTEAD OF 范例的触发器。
insert 触发器:
视图 [VDemoTab] 中的 [Guid] 为表 插入时值,在插入触发器中,虚拟表[inserted]的[Guid]是唯一的,以是在触发器中可以同时使用该 [Guid] 插入到多个分表中,保证了多个分表的[Guid]是相同的!- -- insert 触发器
- CREATE TRIGGER [dbo].[tgr_VDemoTab_insert]
- ON [dbo].[VDemoTab]
- INSTEAD OF INSERT
- AS
- BEGIN
- INSERT INTO [dbo].[DemoTab001]([Guid],[UserName],[Password])
- SELECT [Guid],[UserName],[Password] FROM inserted;
-
- INSERT INTO [dbo].[DemoTab002]([Guid],[UserAccount],[Amount])
- SELECT [Guid],[UserAccount],[Amount] FROM inserted;
- END
- GO
复制代码
update 触发器:
同理,更新时涉及虚拟表 deleted 和 inserted,而更新是对视图[VDemoTab]更新的,以是虚拟表inserted包括了全部的字段,以是必要触发器分别更新多个分表。- -- update 触发器
- CREATE TRIGGER [dbo].[tgr_VDemoTab_update]
- ON [dbo].[VDemoTab]
- INSTEAD OF UPDATE
- AS
- BEGIN
- UPDATE T1 SET
- T1.[UserName] = T2.[UserName],
- T1.[Password] = T2.[Password]
- FROM [dbo].[DemoTab001] AS T1, inserted AS T2 WHERE T1.[Guid] = T2.[Guid]
-
- UPDATE T1 SET
- T1.[UserAccount] = T2.[UserAccount],
- T1.[Amount] = T2.[Amount]
- FROM [dbo].[DemoTab002] AS T1, inserted AS T2 WHERE T1.[Guid] = T2.[Guid]
- END
- GO
复制代码
delete 触发器:
删除视图[VDemoTab]记载,涉及多个表则不允许删除,因此只要删除"主表"的记载即可,其他分表都会级联删除。- -- delete 触发器
- CREATE TRIGGER [dbo].[tgr_VDemoTab_delete]
- ON [dbo].[VDemoTab]
- INSTEAD OF DELETE
- AS
- BEGIN
- DELETE FROM [dbo].[DemoTab001]
- WHERE [Guid] IN (SELECT [Guid] FROM deleted)
- END
- GO
复制代码
计划根本就完成了,如今举行测试。- INSERT INTO [dbo].[VDemoTab]([Guid],[UserName],[Password],[UserAccount],[Amount])
- SELECT NEWID(),'user01','pw01','account01',100
- UNION ALL
- SELECT NEWID(),'user02','pw02','account02',99
- UNION ALL
- SELECT NEWID(),'user03','pw03','account03',0
- GO
-
- UPDATE [VDemoTab] SET [Password]='pw',[Amount]='10'
- WHERE [Amount] >=0 AND [Amount]<100 AND [UserName] LIKE '%3'
- GO
-
- DELETE FROM [VDemoTab] WHERE [UserName] = 'user03'
- GO
-
- SELECT * FROM [dbo].[DemoTab001]
- SELECT * FROM [dbo].[DemoTab002]
- SELECT * FROM [dbo].[VDemoTab]
复制代码
根本使用都是正常的!垂直分表完成!
性能怎么样呢?
由于 Guid 作为主键,使用的是 NEWID() 而不是 NEWSEQUENTIALID(),新增记载时聚集索引都大概重新排序较多数据。
分表之后,单个数据页能存储的数据更多了,但是分成多个表中,数据页也增多了,同时 Guid 在每个表都存在,以是查询数据时IO会更多。
对于更新数据,在触发器中是两个表同时更新的,纵然更新此中一个分表,其他分表都会影响。假如分表之后差别时更新,可以在触发器中使用 if(update(col)) 来判断更新的是那一列,就更新相应的基表就行,其他分表不更新。
最好的情况就是,拆分后的表都是“独立”的,不用连合视图,查询和更改都独立,这必要更改逻辑层。
到此这篇关于SqlServer 垂直分表(淘汰步伐改动)的文章就介绍到这了,更多相关SqlServer 垂直分表内容请搜索脚本之家从前的文章或继承欣赏下面的相关文章盼望大家以后多多支持脚本之家! |