SQL Server垂直分表怎样设计 SQL语句是什么
|
这篇文章主要介绍“SQL Server垂直分表如何设计，SQL语句是什么”，有一些人在SQL Server垂直分表如何设计，SQL语句是什么的问题上存在疑惑，接下来小编就给大家来介绍一下相关的内容，希望对大家解答有帮助，有这个方面学习需要的朋友就继续往下看吧。 由于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)) 来判断更新的是那一列，就更新相应的基表就行，其他分表不更新。 最好的情况就是，拆分后的表都是“独立”的，不用联合视图，查询和更改都独立，这需要更改逻辑层。 （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
