SqlServer 垂直分表(减少程序改动)

由于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 垂直分表内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！

SqlServer 垂直分表(减少程序改动)

性能怎么样呢？

相关推荐