Mysql数据库数据拆分之分库分表总结

Mysql数据库分库和分表方式,涉及到mysql数据库相关知识,对mysql数据库分库分表相关知识感兴趣的朋友一起学习吧

 

分库

场景: 分表能够解决单表数据量过大带来的查询效率下降的问题,但是,却无法给数据库的并发处理能力带来质的提升。面对高并发的读写访问,当数据库master服务器无法承载写操作压力时,不管如何扩展slave服务器,此时都没有意义了。因此,我们必须换一种思路,对数据库进行拆分,从而提高数据库写入能力,这就是所谓的分库!与分表策略相似,分库可以采用通过一个关键字取模的方式,来对数据访问进行路由。

水平分库

水平分库: 把同一个表的数据按一定规则拆到不同的数据库中,每个库可以放在不同的服务器上。

场景: 对用户表进行水平分库(DB1,DB2,DB3),根据 用户ID%3 进行判断用户记录写在哪个库上。具体参考 分库分表 的案例

 

垂直分库

垂直分库: 按照业务将表进行分类,分布到不同的数据库上面,每个库可以放在不同的服务器上,它的核心理念是专库专用。

按照功能分库

按照功能进行分库。常见的分成6大库:

  • 用户类库: 用于保存了用户的相关信息。例如:db_user,db_system,db_company等。
  • 业务类库: 用于保存主要业务的信息。比如主要业务是笑话,用这个库保存笑话业务。例如:db_joke,db_temp_joke等。
  • 内存类库: 主要用Mysql的内存引擎。前台的数据从内存库中查找,速度快。例如:heap。
  • 图片类库: 主要保存图片的索引以及关联。例如:db_img_index,db_img_res。
  • 日志类库: 记录点击,刷新,登录等日志信息。例如:db_log_click,db_log_fresh,db_log_login。
  • 统计类库: 对业务的统计,比如点击量,刷新量等等。例如:db_stat。

安装城市站分库

如果业务遍布全国,在按照功能分库库,每一个城市复制一份一模一样的库,只是库后缀都是城市名称。

比如: db_log_click_bj,db_log_click_tj,db_log_click_sh;

 

分表

场景: 对于大型的互联网应用来说,数据库单表的记录行数可能达到千万级甚至是亿级,并且数据库面临着极高的并发访问。采用Master-Slave复制模式的MySQL架构,只能够对数据库的读进行扩展,而对数据库的写入操作还是集中在Master上,并且单个Master挂载的Slave也不可能无限制多,Slave的数量受到Master能力和负载的限制。因此,需要对数据库的吞吐能力进行进一步的扩展,以满足高并发访问与海量数据存储的需要!

对于访问极为频繁且数据量巨大的单表来说,我们首先要做的就是减少单表的记录条数,以便减少数据查询所需要的时间,提高数据库的吞吐,这就是所谓的分表!

在分表之前,首先需要选择适当的分表策略,使得数据能够较为均衡地分不到多张表中,并且不影响正常的查询!

对于互联网企业来说,大部分数据都是与用户关联的,因此,用户id是最常用的分表字段。因为大部分查询都需要带上用户id,这样既不影响查询,又能够使数据较为均衡地分布到各个表中(当然,有的场景也可能会出现冷热数据分布不均衡的情况)

水平分表

水平分表: 在同一个数据库内,把同一个表的数据按一定规则拆到多个表中。

按照用户或业务的编号分表

对与用户或业务可以按照编号%n,进行分成n表。

例如:笑话表。

tb_joke_01,tb_joke_02,tb_joke_03,tb_joke_04…

按照日期分表

对于日志或统计类等的表。可以按照年,月,日,周分表。

例如:点击量统计。

tb_click_stat_201601,tb_click_stat_201602,tb_click_stat_201603

 

垂直分表

垂直分表: 将一个表按照字段分成多表,每个表存储其中一部分字段。

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

  • 把不常用的字段单独放在一张表;
  • 把text,blob等大字段拆分出来放在附表中;
  • 经常组合查询的列放在一张表中;

场景: 用户在浏览商品列表时,只有对某商品感兴趣时才会查看该商品的详细描述。因此,商品信息中商品描述字段访问频次较低,且该字段存储占用空间较大,访问单个数据IO时间较长;商品信息中商品名称、商品图片、商品价格等其他字段数据访问频次较高。

由于这两种数据的特性不一样,因此他考虑将商品信息表拆分如下:

将访问频次低的商品描述信息单独存放在一张表中,访问频次较高的商品基本信息单独放在一张表中。

原有商品表

idgoods_namegoods_pricegoods_imggoods_desc
主键id商品名称商品价格商品图片商品描述

经过垂直分表拆分后


商品表:ta_goods_info

idgoods_namegoods_pricegoods_img
主键id商品名称商品价格商品图片

商品描述表: ta_goods_describe

idgoods_idgoods_desc
主键id商品id商品描述

 

分库分表

场景: 有时数据库可能既面临着高并发访问的压力,又需要面对海量数据的存储问题,这时需要对数据库既采用分表策略,又采用分库策略,以便同时扩展系统的并发处理能力,以及提升单表的查询性能,这就是所谓的分库分表。

分库分表的策略比前面的仅分库或者仅分表的策略要更为复杂,一种分库分表的路由策略如下:

  • 中间变量 = user_id % (分库数量 * 每个库的表数量)
  • 库 = 取整数 (中间变量 / 每个库的表数量)
  • 表 = 中间变量 % 每个库的表数量

以上分库分表策略为 水平分库水平分表策略。 采用user_id作为路由字段,首先使用user_id 对库数量*每个库表的数量取模,得到一个中间变量;然后使用中间变量除以每个库表的数量,取整,便得到对应的库;而中间变量对每个库表的数量取模,即得到对应的表。

分库分表策略详细过程如下:

假设将原来的单库单表order拆分成256个库,每个库包含1024个表,那么按照前面所提到的路由策略,对于user_id=262145 的访问,路由的计算过程如下:

  • 中间变量 = 262145 % (256 * 1024) = 1
  • 库 = 取整 (1/1024) = 0
  • 表 = 1 % 1024 = 1

这就意味着,对于user_id=262145 的订单记录的查询和修改,将被路由到第0个库的第1个order_1表中执行!!!

 

跨库Join的几种解决思路

在拆分之前,系统中很多列表和详情页所需的数据是可以通过sql join来完成的。而拆分后,数据库可能是分布式在不同实例和不同的主机上,join将变得非常麻烦。而且基于架构规范,性能,安全性等方面考虑,一般是禁止跨库join的。那该怎么办呢?首先要考虑下垂直分库的设计问题,如果可以调整,那就优先调整。如果无法调整的情况,下面笔者将结合以往的实际经验,总结几种常见的解决思路,并分析其适用场景。

全局表

所谓全局表,就是有可能系统中所有模块都可能会依赖到的一些表。比较类似我们理解的“数据字典”。 为了避免跨库join查询,我们可以将这类表在其他每个数据库中均保存一份。同时,这类数据通常也很少发生修改(甚至几乎不会),所以也不用太担心“一致性”问题。

字段冗余

这是一种典型的反范式设计,在互联网行业中比较常见,通常是为了性能来避免join查询。

举个电商业务中很简单的场景:

“订单表”中保存“卖家Id”的同时,将卖家的“Name”字段也冗余,这样查询订单详情的时候就不需要再去查询“卖家用户表”。

字段冗余能带来便利,是一种“空间换时间”的体现。但其适用场景也比较有限,比较适合依赖字段较少的情况。最复杂的还是数据一致性问题,这点很难保证,可以借助数据库中的触发器或者在业务代码层面去保证。当然,也需要结合实际业务场景来看一致性的要求。就像上面例子,如果卖家修改了Name之后,是否需要在订单信息中同步更新呢?

数据同步

定时A库中的tab_a表和B库中tbl_b有关联,可以定时将指定的表做同步。当然,同步本来会对数据库带来一定的影响,需要性能影响和数据时效性中取得一个平衡。这样来避免复杂的跨库查询。笔者曾经在项目中是通过ETL工具来实施的。

系统层组装

在系统层面,通过调用不同模块的组件或者服务,获取到数据并进行字段拼装。说起来很容易,但实践起来可真没有这么简单,尤其是数据库设计上存在问题但又无法轻易调整的时候。

 

Mysql数据库常用架构

核心:一主多从,读写分离。

 

MySQL 有没有做分库分表?怎么设计的?

Why?
当一张表的数据达到几千万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。

mysql中有一种机制是表锁定和行锁定,是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作,必须等我对表操作完才行。行锁定也一样,别的sql必须等我对这条数据操作完了,才能对这条数据进行操作。

 

When?(什么时候需要分表?)
单表行数超过500万行或者单表容量超过2GB,才推荐进行分库分表。说明:如果预计三年后的数据量根本达不到这个级别,请不要在创建表时就分库分表。

反例:某业务三年总数据量才2万行,却分成1024张表,问:你为什么这么设计?答:分1024张表,不是标配吗?

 

How?(分库分表有几种策略)
垂直拆分 or 水平拆分

拆分中间件,详细可以参考:

  • Sharding-sphere,前身是sharding-jdbc;当当的分库分表中间件
  • TDDL:jar,Taobao Distribute Data Layer;
  • Mycat:中间件。
    注:工具的利弊,请自行调研,官网和社区优先。
  • 按照userId纬度拆分,安琪拉见过的常见的有,根据 userId % 64 取模拆0~63编号的64张表,
  • 固定位拆,取userId 指定二位,例如倒数2,3位组成00~99 一共100张表的,百库表表。
  • hash: userId hash一下,然后 % 表数;
  • Range: 另外还有按照userId 指定范围拆的,0-1千万一张表,这种用的比较少,容易产生热点。
  • 把不同业务域的表拆成不同库,例如订单相关表、用户信息相关表、营销相关表分开在不同库;
  • 把大字段独立存储到一张表中
  • 把不常用的字段单独拿出来存储到一张表

 

用userId做的分库分表,现在需要用电话号码查询怎么办?

和回表逻辑一样,单独建一个电话号码索引表,存放电话号码和userId,查询时先根据电话号码查询userId,然后再根据userId查询数据。

 

References

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

Jaemon

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值