sqlserver统计表大小(SqlServer 垂直分表减少程序改动)
sqlserver统计表大小
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 垂直分表内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
- sqlserver2000显示无服务器(SQL SERVER 2000 9003错误的解决方法只适用于SQL2000)
- sql server提供的聚合函数(SQLServer行列互转实现思路聚合函数)
- sqlserver常用的字符串函数(SqlServer 获取字符串中小写字母的sql语句)
- sqlserver限制windows身份登录(解决sql server 数据库,sa用户被锁定的问题)
- sqlserver创建带参数的存储过程(SQLServer存储过程实现单条件分页)
- sqlserver分页查询sql语句(sql server实现分页的方法实例分析)
- SQLServer数据库中开启CDC导致事务日志空间被占满的原因(SQLServer数据库中开启CDC导致事务日志空间被占满的原因)
- sqlserver复制表结构及数据到新表(SQL SERVER 表与表之间 字段一对多sql语句写法)
- sqlserver备份还原地址(SqlServer高版本数据备份还原到低版本)
- sqlserver表空间占用率(SQL Server获取磁盘空间使用情况)
- sqlserver2014怎么重新激活(解决Windows 10家庭版安装SQL Server 2014出现.net 3.5失败问题)
- sqlserver常用流控语句(SQL Server实现自动循环归档分区数据脚本详解)
- sqlserver 数据量需要多少内存(SQL Server在AlwaysOn中使用内存表的“踩坑”记录)
- 怎么写存储过程sql server(SqlServer存储过程实现及拼接sql的注意点)
- navicat如何连接sql数据库(Navicat 连接SQLServer数据库图文步骤)
- sqlserver拒绝访问怎么办(SQL server服务显示远程过程调用失败的解决方法)
- 一窗通办政务服务小品剧本(一窗通办政务服务小品剧本)
- 刘韬涛丁子贺小品《根治低头族》台词剧本(刘韬涛丁子贺小品根治低头族台词剧本)
- 看完《夺冠》,黄渤的演技我实在夸不起来,彭昱畅反令人惊喜(黄渤的演技我实在夸不起来)
- 黄渤泪目 我的痴呆父亲,我内心永远的痛(黄渤泪目我的痴呆父亲)
- 蒜苔和鱿鱼尾巴一起炒,味道特别棒,又脆又嫩,有滋又有味(蒜苔和鱿鱼尾巴一起炒)
- 鱿鱼炒蒜苔不是黑暗料理,这样做清香扑鼻,鲜美脆嫩,开胃又下饭(鱿鱼炒蒜苔不是黑暗料理)
热门推荐
- dedecms目录说明(DedeCms获取当前页面URL地址的实现方法)
- sqlserver数据库如何设置循环日志(sqlserver 实现收缩数据库日志操作)
- dedecms悬浮导航(DEDECMS开启绝对地址PC端跳转到静态手机端实现方法)
- ubuntu如何卸载clion(Ubuntu16.04安装clion全过程及步骤详解)
- sqlserver表空间占用率(SQL Server获取磁盘空间使用情况)
- jquery隐藏动画教程(jquery插件实现鼠标隐藏)
- python 简单算法(python实现爬山算法的思路详解)
- elementui自定义坐标(element-ui 上传图片后标注坐标点)
- python飞机大战游戏背景(python实现飞机大战游戏)
- 织梦dedecms是干啥的(织梦dede去掉后台登陆界面广告的方法)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9