SQL分表,分库分区,读写分离和同步
来源:互联网 发布:淘宝电热水龙头 编辑:程序博客网 时间:2024/05/17 08:13
一、分表
分表分为垂直分表和水平分表
1.垂直分表
垂直分表 相对很少见到和用到,因为这可能是数据库设计上的问题了。如果数据库中一张表有部分字段几乎从不不更改但经常查询,而部分字段的数据频繁更改,这种设计放到同一个表中就不合理了,相互影响太大了。在已存在改情况的表的时候,可以考虑按列拆分表,即垂直拆分。
源表设计结构:
-- 源表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]ASSELECT [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 CASCADEGO如果之前是对单个表或者视图操作,拆分之后逻辑层改动可能很多,为保持改动最小,可以用联合视图操作。怎么连接表依个人情况而定。
-- 拆分后使用联合视图(INNER JOIN 也可以)ALTER VIEW [dbo].[VDemoTab]ASSELECT 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 触发器:
-- insert 触发器CREATE TRIGGER [dbo].[tgr_VDemoTab_insert]ON [dbo].[VDemoTab] INSTEAD OF INSERTASBEGIN 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;ENDGO
- update 触发器:
-- update 触发器CREATE TRIGGER [dbo].[tgr_VDemoTab_update] ON [dbo].[VDemoTab] INSTEAD OF UPDATE ASBEGIN 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] ENDGO
- delete 触发器:
-- delete 触发器CREATE TRIGGER [dbo].[tgr_VDemoTab_delete] ON [dbo].[VDemoTab] INSTEAD OF DELETE ASBEGIN DELETE FROM [dbo].[DemoTab001] WHERE [Guid] IN (SELECT [Guid] FROM deleted)ENDGO设计基本就完成了,现在进行测试。
INSERT INTO [dbo].[VDemoTab]([Guid],[UserName],[Password],[UserAccount],[Amount])SELECT NEWID(),'user01','pw01','account01',100UNION ALLSELECT NEWID(),'user02','pw02','account02',99UNION ALLSELECT NEWID(),'user03','pw03','account03',0GO 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)) 来判断更新的是那一列,就更新相应的基表就行,其他分表不更新。
- 最好的情况就是,拆分后的表都是“独立”的,不用联合视图,查询和更改都独立,这需要更改逻辑层。
2.水平分表
根据其中一列中的数据值范围,在各个成员表之间对数据进行分表。每个成员表的数据范围都在为分表依据列指定的 CHECK 约束中定义。然后定义一个视图,以使用 UNION ALL 将选定的所有成员表组合成单个结果集。引用该视图的 SELECT 语句为分表依据列指定搜索条件后,查询优化器将使用 CHECK 约束定义确定哪个成员表包含相应行。
SqlServer 分区视图实现水平分表
二、表分区(即分库)
1.水平分区
- 什么是表分区
一般情况下,我们建立数据库表时,表数据都存放在一个文件里。
但是如果是分区表的话,表数据就会按照你指定的规则分放到不同的文件里,把一个大的数据文件拆分为多个小文件,还可以把这些小文件放在不同的磁盘下由多个cpu进行处理。这样文件的大小随着拆分而减小,还得到硬件系统的加强,自然对我们操作数据是大大有利的。
所以大数据量的数据表,对分区的需要还是必要的,因为它可以提高select效率,还可以对历史数据经行区分存档等。但是数据量少的数据就不要凑这个热闹啦,因为表分区会对数据库产生不必要的开销,除啦性能还会增加实现对象的管理费用和复杂性。
但是如果是分区表的话,表数据就会按照你指定的规则分放到不同的文件里,把一个大的数据文件拆分为多个小文件,还可以把这些小文件放在不同的磁盘下由多个cpu进行处理。这样文件的大小随着拆分而减小,还得到硬件系统的加强,自然对我们操作数据是大大有利的。
所以大数据量的数据表,对分区的需要还是必要的,因为它可以提高select效率,还可以对历史数据经行区分存档等。但是数据量少的数据就不要凑这个热闹啦,因为表分区会对数据库产生不必要的开销,除啦性能还会增加实现对象的管理费用和复杂性。
SQL Server表分区
SQL Server 分区表
2.垂直分区(也叫垂直分库)
垂直分库就是根据业务需求来分库,比如教育系列的,可以分为资讯,课程,用户(学生,学校)三个数据库。比如电商的可以分为订单,商品,用户(商家,消费者)三个数据库。
三、数据库读写分离与数据同步
在生产环境中我们经常会遇到这种情况:
前端的oltp业务很繁忙,但是需要对这些运营数据进行olap,为了不影响前端正常业务,所以需要将数据库进行读写分离。
这里我将几种可以用来进行读写分离的方案总结一下,这里并不考虑数据库是否高可用,只针对读写分离场景,方案本身并无优劣可言,只看是否适合业务使用场景,所以只把几个方案的特点罗列出来,遇到具体的问题时按自己需求和环境综合考虑后再进行取舍
前端的oltp业务很繁忙,但是需要对这些运营数据进行olap,为了不影响前端正常业务,所以需要将数据库进行读写分离。
这里我将几种可以用来进行读写分离的方案总结一下,这里并不考虑数据库是否高可用,只针对读写分离场景,方案本身并无优劣可言,只看是否适合业务使用场景,所以只把几个方案的特点罗列出来,遇到具体的问题时按自己需求和环境综合考虑后再进行取舍
在高安全模式下对主库
性能有一定影响
log shipping(日志传送)否是(只读)N库否UNC方式可访问 副本库在做resotre时会断开已连接用户连接/可能影响常规日志备份发布订阅(事务复制)是是(读写,但写可能会产生数据不一致)N表(查询)是域/非域在主库上有大量DML操作时,对分发服务器会有一定影响,且订阅数据库可能有数据同步延迟always on是是(只读)4(sql 2012)8(sql 2014)库否域非域环境无法使用
SQL Server 2012中的AlwaysOn尝试
阅读全文
0 0
- SQL分表,分库分区,读写分离和同步
- 数据库分区分表以及读写分离
- 数据库分区分表以及读写分离
- 数据库分区分表以及读写分离
- 数据库分区分表以及读写分离
- 数据库分区分表以及读写分离
- 数据库读写分离和垂直分库、水平分表
- 数据库读写分离和垂直分库、水平分表
- 数据库读写分离和垂直分库、水平分表
- 数据库分表、分库及读写分离的基本概念
- mysql 数据库分库/分表/读写分离
- 数据库读写分离与垂直分库水平分表
- 分表和分区
- MySql 分区 分库 分表
- mysql分库、分表、分区
- MySql 分区 分库 分表
- mysql分区和分表
- Mysql 分区和分表
- spring学习(二):资源访问利器
- 屏幕像素缩放比 dpr 的那些事儿
- hive Permission denied: user=root, access=WRITE, inode="/user":hdfs:supergroup:drwxr-xr-x
- 电子病历源代码
- vue组件1-全局注册
- SQL分表,分库分区,读写分离和同步
- 使用token和SpringMVC来实现安全的RESTFul接口
- 微信热修复tinker初探
- LearnOpenGL #01 Hello Window
- Window-history对象
- codeforces 570-C. Replacement(预处理+规律)
- Dubbo详细介绍与安装使用过程
- 安卓开发中Linux常用命令大全
- 概率公式小结