欢迎您的光临,本博所发布之文章皆为作者亲测通过,如有错误,欢迎通过各种方式指正。

文摘  MySQL数据库的水平分割和垂直分割

MySQL 本站 1071 0评论

目前很多互联网系统都存在单表数据量过大的问题,这就降低了查询速度,影响了客户体验。为了提高查询速度,我们可以优化sql语句,优化表结构和索引,不过对那些百万级千万级的数据库表,即便是优化过后,查询速度还是满足不了要求。这时候我们就可以通过分表降低单次查询数据量,从而提高查询速度,一般分表的方式有两种:水平拆分和垂直拆分,两者各有利弊,适用于不同的情况。


1.水平分割


按记录进分分割,不同的记录可以分开保存,每个子表的列数相同。


水平分割通常在下面的情况下使用:

A、表很大,分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,提高查询速度。

B、表中的数据本来就有独立性,例如表中分别记录各个地区的数据或不同时期的数据,特别是有些数据常用,而另外一些数据不常用。

C、需要把数据存放到多个介质上。


通常情况下,我们使用取模的方式来进行表的拆分;比如一张有400W的用户表users,为提高其查询效率我们把其分成4张表users1,users2,users3,users4 

通过用ID取模的方法把数据分散到四张表内Id%4+1 = [1,2,3,4] 

然后查询,更新,删除也是通过取模的方法来查询。


例:QQ的登录表。假设QQ的用户有100亿,如果只有一张表,每个用户登录的时候数据库都要从这100亿中查找,会很慢很慢。如果将这一张表分成100份,每张表有1亿条,就小了很多,比如qq0,qq1,qq1…qq99表。


用户登录的时候,可以将用户的id%100,那么会得到0-99的数,查询表的时候,将表名qq跟取模的数连接起来,就构建了表名。比如123456789用户,取模的89,那么就到qq89表查询,查询的时间将会大大缩短。


另外部分业务逻辑也可以通过地区,年份等字段来进行归档拆分;进行拆分后的表,只能满足部分查询的高效查询需求,这时我们就要在产品策划上,从界面上约束用户查询行为。比如我们是按年来进行归档拆分的,这个时候在页面设计上就约束用户必须要先选择年,然后才能进行查询;在做分析或者统计时,由于是自己人的需求,多点等待其实是没关系的,并且并发很低,这个时候可以用union把所有表都组合成一张视图来进行查询,然后再进行查询。


水平拆分的优点: 

--表关联基本能够在数据库端全部完成; 

--不会存在某些超大型数据量和高负载的表遇到瓶颈的问题; 

--应用程序端整体架构改动相对较少; 

--事务处理相对简单; 

--只要切分规则能够定义好,基本上较难遇到扩展性限制;


水平切分的缺点: 

--切分规则相对更为复杂,很难抽象出一个能够满足整个数据库的切分规则; 

--后期数据的维护难度有所增加,人为手工定位数据更困难; 

--应用系统各模块耦合度较高,可能会对后面数据的迁移拆分造成一定的困难。


2.垂直分割


按列进行分割,即把一条记录分开多个地方保存,每个子表的行数相同。

把主码和一些列放到一个表,然后把主码和另外的列放到另一个表中。如果一个表中某些列常用,而另外一些列不常用,则可以采用垂直分割,另外垂直分割可以使得数据行变小,一个数据页就能存放更多的数据,在查询时就会减少I/O 次数。其缺点是需要管理冗余列,查询所有数据需要join操作。 


例如有表T1

id  name  qty

--------------

1  p1    10

2  p2    20

3  p3    30

4  p4    40

......

......

垂直分割就是按列进行分割,即把一条记录分开多个地方保存,每个子表的行数相同。

例如表T1,可以把id和name放到数据文件p1,把qty放到数据文件p2。

水平分割就是按记录进分分割,不同的记录可以分开保存,每个子表的列数相同。

像表T1,可以把id为单数的放到数据文件P1,双数的放到数据文件P2。

表散列与水平分割相似,但没有水平分割那样的明显分割界限,它由哈希函数和键值决定一条记录的保存文件,这样是为了IO更加均衡。


通常我们按以下原则进行垂直拆分: 

1,把不常用的字段单独放在一张表;, 

2,把text,blob等大字段拆分出来放在附表中; 

3,经常组合查询的列放在一张表中;


例如学生答题表tt:有如下字段: 

Id name 分数 题目 回答 

其中题目和回答是比较大的字段,id name 分数比较小。


如果我们只想查询id为8的学生的分数:select 分数 from tt where id = 8;虽然知识查询分数,但是题目和回答这两个大字段也是要被扫描的,很消耗性能。但是我们只关心分数,并不想查询题目和回答。这就可以使用垂直分割。我们可以把题目单独放到一张表中,通过id与tt表建立一对一的关系,同样将回答单独放到一张表中。这样我们插叙tt中的分数的时候就不会扫描题目和回答了。


垂直切分的优点 

--数据库的拆分简单明了,拆分规则明确; 

--应用程序模块清晰明确,整合容易; 

--数据维护方便易行,容易定位;


垂直切分的缺点 

--部分表关联无法在数据库级别完成,需要在程序中完成; 

--对于访问极其频繁且数据量超大的表仍然存在性能平静,不一定能满足要求; 

--事务处理相对更为复杂; 

--切分达到一定程度之后,扩展性会遇到限制; 

--过读切分可能会带来系统过渡复杂而难以维护。


3.拆分的处理难点

 

两张方式共同缺点:

1. 引入分布式事务的问题。

2. 跨节点Join 的问题。

3. 跨节点合并排序分页问题。

 

针对数据源管理,目前主要有两种思路:

A. 客户端模式,在每个应用程序模块中配置管理自己需要的一个(或者多个)数据源,直接访问各个 数据库,在模块内完成数据的整合。 

优点:相对简单,无性能损耗。   

缺点:不够通用,数据库连接的处理复杂,对业务不够透明,处理复杂。

B. 通过中间代理层来统一管理所有的数据源,后端数据库集群对前端应用程序透明;   

优点:通用,对应用透明,改造少。   

缺点:实现难度大,有二次转发性能损失。

 

4.拆分原则


1.尽量不拆分,架构是进化而来,不是一蹴而就。(SOA)

2.最大可能的找到最合适的切分维度。

3.由于数据库中间件对数据Join 实现的优劣难以把握,而且实现高性能难度极大,业务读取  尽量少使用多表Join -尽量通过数据冗余,分组避免数据垮库多表join。

4.尽量避免分布式事务。

5.单表拆分到数据1000万以内。

 

5.切分方案


范围、枚举、时间、取模、哈希、指定等

 

6.案例分析


场景一:

建立一个历史his系统,将公司的一些历史个人游戏数据保存到这个his系统中,主要是写入,还有部分查询,读写比约为1:4;由于是所有数据的历史存取,所以并发要求比较高; 

分析:

历史数据

写多都少

越近日期查询越频繁?

什么业务数据?用户游戏数据

有没有大规模分析查询?

数据量多大?

保留多久?

机器资源有多少?

 

方案1:按照日期每月一个分片

带来的问题:1.数据热点问题(压力不均匀)

 

方案2:按照用户取模,  --by Jerome 就这个比较合适了

带来的问题:后续扩容困难

 

方案3:按用户ID范围分片(1-1000万=分片1,xxx)

带来的问题:用户活跃度无法掌握,可能存在热点问题

 

场景二:

建立一个商城订单系统,保存用户订单信息。

 

分析:

电商系统

一号店或京东类?淘宝或天猫?

实时性要求高

存在瞬时压力

基本不存在大规模分析

数据规模?

机器资源有多少?

维度?商品?用户?商户?

 

方案1:按照用户取模,

带来的问题:后续扩容困难

 

方案2:按用户ID范围分片(1-1000万=分片1,xxx)

带来的问题:用户活跃度无法掌握,可能存在热点问题

 

方案3:按省份地区或者商户取模

数据分配不一定均匀

 

场景3:

上海公积金,养老金,社保系统

 

分析:

社保系统

实时性要求不高

不存在瞬时压力

大规模分析?

数据规模大

数据重要不可丢失

偏于查询?

 

方案1:按照用户取模,

带来的问题:后续扩容困难

 

方案2:按用户ID范围分片(1-1000万=分片1,xxx)

带来的问题:用户活跃度无法掌握,可能存在热点问题

 

方案3:按省份区县地区枚举

数据分配不一定均匀


转载请注明: ITTXX.CN--分享互联网 » MySQL数据库的水平分割和垂直分割

最后更新:2018-11-06 17:23:55

赞 (0) or 分享 ()
游客 发表我的评论   换个身份
取消评论

表情
(0)个小伙伴在吐槽