精华内容
下载资源
问答
  • mysql分库分表方案
    2022-05-22 12:58:26

    分库分表概念

           分库分表就是业务系统将数据写请求分发到master节点,而读请求分发到slave 节点的一种方案,可以大大提高整个数据库集群的性能。但是要注意,分库分表的 一整套逻辑全部是由客户端自行实现的。而对于MySQL集群,数据主从同步是实现 读写分离的一个必要前提条件
    1、分库分表有什么用
         分库分表就是为了解决由于数据量过大而导致数据库性能降低的问题,将原来独立 的数据库拆分成若干数据库组成 ,将数据大表拆分成若干数据表组成,使得单一数据库、单一数据表的数据量变 小,从而达到提升数据库性能的目的。
    例如:微服务架构中,每个服务都分配一个独立的数据库,这就是分库。而对一些 业务日志表,按月拆分成不同的表,这就是分表。
    2、分库分表的方式
        分库分表包含分库和分表 两个部分,而这两个部分可以统称为数据分片,其目的都 是将数据拆分成不同的存储单元。另外,从分拆的角度上,可以分为垂直分片和水平分片。
    垂直分片: 按照业务来对数据进行分片,又称为纵向分片。他的核心理念就是转库专用。在拆分之前,一个数据库由多个数据表组成,每个表对应不同的业务。而拆分之后,则是按照业务将表进行归类,分布到不同的数据库或表中,从而将压力分散至不同的数据库或表。例如,下图将用户表和订单表垂直分片到不同的数据库:

     

          垂直分片往往需要对架构和设计进行调整。通常来讲,是来不及应对业务需求快速 变化的。而且,他也无法真正的解决单点数据库的性能瓶颈。垂直分片可以缓解数据量和访问量带来的问题,但无法根治。如果垂直分片之后,表中的数据量依然超过单节点所能承载的阈值,则需要水平分片来进一步处理。

     水平分片:又称横向分片。相对于垂直分片,它不再将数据根据业务逻辑分类,而是通过某个字段(或某几个字段),根据某种规则将数据分散至多个库或表中, 每个分片仅包含数据的一部分。例如,像下图根据主键机构分片

    常用的分片策略有:
    1:取余\取模 : 优点 均匀存放数据,缺点 扩容非常麻烦
    2:按照范围分片 : 比较好扩容, 数据分布不够均匀
    3:按照时间分片 : 比较容易将热点数据区分出来。
    4:按照枚举值分片 : 例如按地区分片
    5:按照目标字段前缀指定进行分区:自定义业务规则分片

     水平分片从理论上突破了单机数据量处理的瓶颈,并且扩展相对自由,是分库分表 的标准解决方案。般来说,在系统设计阶段就应该根据业务耦合松紧来确定垂直分库,垂直分表方案,在数据量及访问压力不是特别大的情况,首先考虑缓存、读写分离、索引技 术等方案。若数据量极大,且持续增长,再考虑水平分库水平分表方案.

    分库分表的缺点

         虽然数据分片解决了性能、可用性以及单点备份恢复等问题,但是分布式的架构 在获得收益的同时,也引入了非常多新的问题。
    • 事务一致性问题
        原本单机数据库有很好的事务机制能够帮我们保证数据一致性。但是分库分表后, 由于数据分布在不同库甚至不同服务器,不可避免会带来分布式事务问题。
    • 跨节点关联查询问题
        在没有分库时,我们可以进行很容易的进行跨表的关联查询。但是在分库后,表被 分散到了不同的数据库,就无法进行关联查询了。 这时就需要将关联查询拆分成多次查询,然后将获得的结果进行拼装。
    • 跨节点分页、排序函数
         跨节点多库进行查询时,limit分页、order by排序等问题,就变得比较复杂了。需要先在不同的分片节点中将数据进行排序并返回,然后将不同分片返回的结果集进行汇总和再次排序。 这时非常容易出现内存崩溃的问题。
    • 主键避重问题
            在分库分表环境中,由于表中数据同时存在不同数据库中,主键值平时使用的自增长将无用武之地,某个分区数据 库生成的ID无法保证全局唯一。因此需要单独设计全局主键,以避免跨库主键重复问题。
    • 公共表处理
            实际的应用场景中,参数表、数据字典表等都是数据量较小,变动少,而且属于高 频联合查询的依赖表。这一类表一般就需要在每个数据库中都保存一份,并且所有对公共表的操作都要分发到所有的分库去执行。
    • 运维工作量
            面对散乱的分库分表之后的数据,应用开发工程师和数据库管理员对数据库的操作都变得非常繁重。对于每一次数据读写操作,他们都需要知道要往哪个具体的数据 库的分表去操作,这也是其中重要的挑战之一。

    什么时候需要分库分表?

           在阿里巴巴公布的开发手册中,建议MySQL单表记录如果达到500W这个级别, 或者单表容量达到2GB,一般就建议进行分库分表。而考虑到分库分表需要对数据 进行再平衡,所以如果要使用分库分表,就要在系统设计之初就详细考虑好分库分表的方案,这里要分两种情况。
           一般对于用户数据这一类后期增长比较缓慢的数据,一般可以按照三年左右的业务量来预估使用人数,按照标准预设好分库分表的方案。 而对于业务数据这一类增长快速且稳定的数据,一般则需要按照预估量的两倍左右预设分库分表方案。并且由于分库分表的后期扩容是非常麻烦的,所以在进行分库分表时,尽量根据情况,多分一些表。最好是计算一下数据增量,永远不用增加
    更多的表。
            另外,在设计分库分表方案时,要尽量兼顾业务场景和数据分布。在支持业务场景的前提下,尽量保证数据能够分得更均匀。 最后,一旦用到了分库分表,就会表现为对数据查询业务的灵活性有一定的影响,例如如果按userId进行分片,那按age来进行查询,就必然会增加很多麻烦。如果再要进行排序、分页、聚合等操作,很容易就扛不住了。这时候,都要尽量在分库分表的同时,再补充设计一个降级方案,例如将数据转存一份到ES,ES可以实现更灵活的大数据聚合查询。

    常见的分库分表组件

           由于分库分表之后,数据被分散在不同的数据库、服务器。因此,对数据的操作也就无法通过常规方式完成,并且它还带来了一系列的问题。好在,这些问题不是所有都需要我们在应用层面上解决,市面上有很多中间件可供我们选择,我们来了解一下它。
    • shardingsphere
    官网地址: https://shardingsphere.apache.org/document/current/cn/overview/
    Sharding-JDBC是当当网研发的开源分布式数据库中间件,他是一套开源的分布式数据库中间件解决方案组成的生态圈,它由Sharding-JDBC、Sharding-Proxy和 Sharding-Sidecar(计划中)这3款相互独立的产品组成。 他们均提供标准化的数 据分片、分布式事务和 数据库治理功能,可适用于如Java同构、异构语言、容器、云原生等各种多样化的应用场景。
    • mycat
    官网地址: http://www.mycat.org.cn/
    基于阿里开源的Cobar产品而研发,Cobar的稳定性、可靠性、优秀的架构和性能以 及众多成熟的使用案例使得MYCAT一开始就拥有一个很好的起点,站在巨人的肩膀 上,我们能看到更远。业界优秀的开源项目和创新思路被广泛融入到MYCAT的基因 中,使得MYCAT在很多方面都领先于目前其他一些同类的开源项目,甚至超越某些商业产品。MyCAT虽然是从阿里的技术体系中出来的,但是跟阿里其实没什么关系。
    • DBLE
    官网地址: https://opensource.actionsky.com/
    该网站包含几个重要产品。其中分布式中间件可以认为是MyCAT的一个增强版,专注于MySQL的集群化管理。另外还有数据传输组件和分布式事务框架组件可供选择。
    更多相关内容
  • 分库分表方案产品 目前市面上的分库分表中间件相对较多,其中基于代理方式的有MySQL Proxy和Amoeba, 基于Hibernate框架的是Hibernate Shards,基于jdbc的有当当sharding-jdbc, 基于mybatis的类似maven插件式的有...


    名词解释
    库:database;表:table;分库分表:sharding

    1. 数据库架构演变

    刚开始我们只用单机数据库就够了,随后面对越来越多的请求,我们将数据库的写操作和读操作进行分离, 使用多个从库副本(Slaver Replication)负责读,使用主库(Master)负责写, 从库从主库同步更新数据,保持数据一致。架构上就是数据库主从同步。 从库可以水平扩展,所以更多的读请求不成问题。

    但是当用户量级上来后,写请求越来越多,该怎么办?加一个Master是不能解决问题的, 因为数据要保存一致性,写操作需要2个master之间同步,相当于是重复了,而且更加复杂。

    这时就需要用到分库分表(sharding),对写操作进行切分。

    2. 分库分表前的问题

    任何问题都是太大或者太小的问题,我们这里面对的数据量太大的问题。

    用户请求量太大
    因为单服务器TPS,内存,IO都是有限的。

    解决方法:分散请求到多个服务器上; 其实用户请求和执行一个sql查询是本质是一样的,都是请求一个资源,只是用户请求还会经过网关,路由,http服务器等。

    单库太大
    单个数据库处理能力有限;单库所在服务器上磁盘空间不足;

    单库上操作的IO瓶颈 解决方法:切分成更多更小的库

    单表太大
    CRUD都成问题;索引膨胀,查询超时

    解决方法:切分成多个数据集更小的表。

    3. 分库分表的方式方法

    一般就是垂直切分水平切分,这是一种结果集描述的切分方式,是物理空间上的切分。

    我们从面临的问题,开始解决,阐述: 首先是用户请求量太大,我们就堆机器搞定(这不是本文重点)。

    然后是单个库太大,这时我们要看是因为表多而导致数据多,还是因为单张表里面的数据多。

    如果是因为表多而数据多,使用垂直切分,根据业务切分成不同的库。

    如果是因为单张表的数据量太大,这时要用水平切分,即把表的数据按某种规则切分成多张表,甚至多个库上的多张表。

    分库分表的顺序应该是先垂直分,后水平分。 因为垂直分更简单,更符合我们处理现实世界问题的方式。

    垂直拆分
    垂直分表

    也就是“大表拆小表”,基于列字段进行的。一般是表中的字段较多,将不常用的, 数据较大,长度较长(比如text类型字段)的拆分到“扩展表“。一般是针对那种几百列的大表,也避免查询时,数据量太大造成的“跨页”问题。

    3.1 垂直分库

    • 垂直分表

      也就是“大表拆小表”,基于列字段进行的。一般是表中的字段较多,将不常用的, 数据较大,长度较长(比如text类型字段)的拆分到“扩展表“。一般是针对那种几百列的大表,也避免查询时,数据量太大造成的“跨页”问题。

    • 垂直分库针对的是一个系统中的不同业务进行拆分,比如用户User一个库,商品Producet一个库,订单Order一个库。 切分后,要放在多个服务器上,而不是一个服务器上。为什么? 我们想象一下,一个购物网站对外提供服务,会有用户,商品,订单等的CRUD。没拆分之前, 全部都是落到单一的库上的,这会让数据库的单库处理能力成为瓶颈。按垂直分库后,如果还是放在一个数据库服务器上, 随着用户量增大,这会让单个数据库的处理能力成为瓶颈,还有单个服务器的磁盘空间,内存,tps等非常吃紧。 所以我们要拆分到多个服务器上,这样上面的问题都解决了,以后也不会面对单机资源问题。
      在这里插入图片描述

    然而,尽管业务之间已经足够独立了,但是有些业务之间或多或少总会有点联系,如用户,基本上都会和每个业务相关联,况且这种分区方式,也不能解决单张表数据量暴涨的问题,因此为何不试试水平分割呢?

    数据库业务层面的拆分,和服务的“治理”,“降级”机制类似,也能对不同业务的数据分别的进行管理,维护,监控,扩展等。 数据库往往最容易成为应用系统的瓶颈,而数据库本身属于“有状态”的,相对于Web和应用服务器来讲,是比较难实现“横向扩展”的。 数据库的连接资源比较宝贵且单机处理能力也有限,在高并发场景下,垂直分库一定程度上能够突破IO、连接数及单机硬件资源的瓶颈。

    3.2 水平拆分

    在这里插入图片描述
    仅分表 和 分库分表的区别在于 分片是否还在同一个库中,前者在同一个库,后者在不同库

    水平分表

    针对数据量巨大的单张表(比如订单表),按照某种规则(RANGE,HASH取模等),切分到多张表里面去。 但是这些表还是在同一个库中,所以库级别的数据库操作还是有IO瓶颈。不建议采用。

    水平分库分表

    将单张表的数据切分到多个服务器上去,每个服务器具有相应的库与表,只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力,突破IO、连接数、硬件资源等的瓶颈。

    水平分库分表切分规则

    RANGE

    从0到10000一个表,10001到20000一个表;

    HASH取模

    一个商场系统,一般都是将用户,订单作为主表,然后将和它们相关的作为附表,这样不会造成跨库事务之类的问题。 取用户id,然后hash取模,分配到不同的数据库上。

    地理区域

    比如按照华东,华南,华北这样来区分业务,七牛云应该就是如此。
    时间

    按照时间切分,就是将6个月前,甚至一年前的数据切出去放到另外的一张表,因为随着时间流逝,这些表的数据 被查询的概率变小,所以没必要和“热数据”放在一起,这个也是“冷热数据分离”。

    4. 分库分表后面临的问题

    4.1 事务支持

    分库分表后,就成了分布式事务了。

    如果依赖数据库本身的分布式事务管理功能去执行事务,将付出高昂的性能代价; 如果由应用程序去协助控制,形成程序逻辑上的事务,又会造成编程方面的负担。

    4.2 多库结果集合并(group by,order by)

    TODO

    4.3 跨库join

    TODO 分库分表后表之间的关联操作将受到限制,我们无法join位于不同分库的表,也无法join分表粒度不同的表, 结果原本一次查询能够完成的业务,可能需要多次查询才能完成。粗略的解决方法: 全局表:基础数据,所有库都拷贝一份。 字段冗余:这样有些字段就不用join去查询了。 系统层组装:分别查询出所有,然后组装起来,较复杂。

    5. 分库分表方案产品

    目前市面上的分库分表中间件相对较多,其中基于代理方式的有MySQL ProxyAmoeba, 基于Hibernate框架的是Hibernate Shards,基于jdbc的有当当sharding-jdbc, 基于mybatis的类似maven插件式的有蘑菇街的蘑菇街TSharding, 通过重写spring的ibatis template类的Cobar Client

    还有一些大公司的开源产品:
    在这里插入图片描述
    当前主要有两类解决方案:

    • 基于应用程序层面的DDAL(分布式数据库访问层)

      比较典型的就是淘宝半开源的TDDL,当当网开源的Sharding-JDBC等。分布式数据访问层无需硬件投入,技术能力较强的大公司通常会选择自研或参照开源框架进行二次开发和定制。对应用程序的侵入性一般较大,会增加技术成本和复杂度。通常仅支持特定编程语言平台(Java平台的居多),或者仅支持特定的数据库和特定数据访问框架技术(一般支持MySQL数据库,JDBC、MyBatis、Hibernate等框架技术)。

    • 数据库中间件,比较典型的像mycat(在阿里开源的cobar基础上做了很多优化和改进,属于后起之秀,也支持很多新特性),基于Go语言实现kingSharding,比较老牌的Atlas(由360开源)等。这些中间件在互联网企业中大量被使用。另外,MySQL 5.x企业版中官方提供的Fabric组件也号称支持分片技术,不过国内使用的企业较少。

      中间件也可以称为“透明网关”,大名鼎鼎的mysql_proxy大概是该领域的鼻祖(由MySQL官方提供,仅限于实现“读写分离”)。中间件一般实现了特定数据库的网络通信协议,模拟一个真实的数据库服务,屏蔽了后端真实的Server,应用程序通常直接连接中间件即可。而在执行SQL操作时,中间件会按照预先定义分片规则,对SQL语句进行解析、路由,并对结果集做二次计算再最终返回。

    引入数据库中间件的技术成本更低,对应用程序来讲侵入性几乎没有,可以满足大部分的业务。增加了额外的硬件投入和运维成本,同时,中间件自身也存在性能瓶颈和单点故障问题,需要能够保证中间件自身的高可用、可扩展。

    总之,不管是使用分布式数据访问层还是数据库中间件,都会带来一定的成本和复杂度,也会有一定的性能影响。所以,还需读者根据实际情况和业务发展需要慎重考虑和选择。

    参考

    MySQL 分库分表方案,总结的非常好!
    分库分表的几种常见玩法及如何解决跨库查询等问题

    展开全文
  • 超详细的mysql分库分表方案

    千次阅读 多人点赞 2020-12-07 17:39:04
    今天就跟大家讨论下那些年MySQL使用过的分表分库方案分表分库后的问题以及解决方案,希望对您有新的收获哦。 MySQL表大小限制** ** MySQL一般安装部署在Linux操作系统上(例如CentOS 7.4),默认都是InnoDB存储...

    我们都知道,随着业务量的增长,数据量也会随之增加,这个时候就需要关注业务大表,因为大表会影响查询性能,DDL变更时间很长,影响业务的可用性,同时导致从库延迟很大,如果业务做了读写分离,导致用户重复操作产生脏数据,例如重复下单。

    今天就跟大家讨论下那些年MySQL使用过的分表分库的方案,分表分库后的问题以及解决方案,希望对您有新的收获哦。

    MySQL表大小限制

    MySQL一般安装部署在Linux操作系统上(例如CentOS 7.4),默认都是InnoDB存储引擎,且开启了独立表空间选项(参数innodb_file_per_table=1),此时创建一个表 orders 就会自动生成一个数据文件 orders.ibd,文件大小是受操作系统 Block 大小限制的,下面是 ext3 文件系统块大小和最大尺寸的对应关系。

    操作系统块大小最大文件尺寸最大文件系统尺寸
    1KB16GB2TB
    2KB256GB8TB
    4KB2TB16TB
    8KB16TB32TB

    查看操作系统页大小及块大小

    img

    这就说明 MySQL 单表的最大尺寸不能超过 2TB,我们简单来算一下,假设一个表的平均行长度为32KB(InnoDB最大行长度限制65536字节,64KB),那么他最大能存储多少行数据?4 x 1024 x 1024 x 1024 / 32 = 134217728大约 1.4 亿不到。

    对于饿了么,美团那外卖种交易系统的订单表 1.4 亿是很容易达到的,一天平均 2000W 订单,一周就到 1.4 亿了,没法玩了,一般都会采用异地多活的方案,根据用户的位置将数据写到相应的 IDC 数据中心,这其实也是一种高大上的分表方案,不在我们今天讨论范围啦。

    分表方案

    分表的应用场景是单表数据量增长速度过快,影响了业务接口的响应时间,但是 MySQL 实例的负载并不高,这时候只需要分表,不需要分库(拆分实例)。

    我们知道,一个表大小是满足如下公式的:TABLE_SIZE = AVG_ROW_SIZE x ROWS,从这里可以知道表太大,要么是平均行长度太大,也就说表的字段太多,要么是表的记录数太多。这就产生两种不同的分表方案,即切分字段(垂直分表)和切分记录(水平分表)

    垂直分表

    还是以订单表 orders 为例,按照字段进行拆分,这里面需要考虑一个问题,如何拆分字段才能表上的DML性能最大化,常规的方案是冷热分离(将使用频率高字段放到一张表里,剩下使用频繁低的字段放到另一张表里)。

    img

    orders 表通过拆分之后,就变成了 orders01 和 orders02 两张表,在磁盘上就会存储两个数据文件 orders01.ibd 和 orders02.ibd,orders 表最大尺寸就是 4TB 了,拆分完之后,该怎么查询呢?举个例子:

    img

    分析下上面的 SQL,select 后面的列分别位于两张表中(order_id,order_sn在orders01中,source在orders02中),上面的SQL可以查询重写为如下形式。

    img

    如果用了数据库中间件就会自动实现查询重写,例如 mycat,sharding-sphere,不用中间件的话,也可以实现的,就是稍微比较麻烦点,可以搞一个 route 表(主键ID, 原表名,字段名,子表名),每次解析SQL时都需要根据原表名 + 字段名去获取需要的子表,然后再改写 SQL,执行 SQL 返回结果,这种代码改造量太大,而且容易出错,故这种垂直拆分在实际业务中用的不多。

    如果业务表中有必须的 Text 类型来存储数据,这时可以利用垂直拆分来减少表大小,将 text 字段拆分到子表中。

    img

    这样将 text 类型拆分放到子表中之后,原表的平均行长度就变小了,就可以存储更多的数据了。

    水平分表

    水平拆分表就是按照表中的记录进行分片,举个例子,目前订单表 orders 有 2000w 数据,根据业务的增长,估算一年之后会达到1亿,同时参考阿里云 RDS for MySQL 的最佳实践,单表不建议超过 500w,1亿数据分20个子表就够了。

    问题来了,按照什么来拆分呢?主键id还是用户的user_id,按主键ID拆分数据很均匀,通过ID查询 orders 的场景几乎没有,业务访问 orders 大部分场景都是根据 user_id来过滤的,而且 user_id 的唯一性又很高(一个 user_id 对应的 orders 表记录不多,选择性很好),按照 user_id 来作为 Sharding key能满足大部分业务场景,拆分之后每个子表数据也比较均匀。

    img

    这样就将 orders 表拆分成20个子表,对应到InnoDB的存储上就是20个数据文件(orders_0.ibd,orders_1.ibd等),这时候执行SQL语句select order_id, order_sn, source from **orders** where user_id = 1001;就能很快的定位到要查找记录的位置是在orders_1,然后做查询重写,转化为SQL语句select order_id, order_sn, source from **orders_01** where user_id = 1001,这种查询重写功能很多中间件都已经实现了,常用的就是 sharding-sphere 或者 sharding-jdbc 都可以实现。

    按月分表

    对于账务或者计费类系统,每天晚上都会做前一天的日结或日账任务,每月的1号都会做月结或月账任务,任务执行完之后相关表的数据都已静态化了(业务层不需要这些数据),根据业务的特性,可以按月创建表,比如对于账单表 bills,就可以创建按月分表(十月份表bills_202010,202011十一月份表),出完月账任务之后,就可以归档到历史库了,用于数据仓库ETL来做分析报表,确认数据都同步到历史库之后就可以删除这些表释放空间。

    img

    MySQL分区表

    你可能在想,上面的水平分表之后,还要改造代码要能保证 SQL 正确的路由,执行并返回结果,这个调用链路有点长吧,MySQL内部有没有分表的解决方案呢?其实是有的,可以考虑使用 MySQL 的 HASH 分区,常规的 hash 也是基于分区个数取模(%)运算的,跟上面的user_id % 20是一样的,来看一个例子。

    img

    这样就创建了20个分区,对应磁盘上就是20个数据文件(orders#p#p0.ibd一直到orders#p#p19.ibd),来看一下SQL的执行过程。

    img

    从执行计划可以看到,通过分区键user_id过滤,直接可以定位到数据所在的分区 p19(user_id =1019 % 20 = 19,所以在p19分区上),进而去访问p19对应的数据文件 orders#p#p19.ibd 即可获得数据。这种方案的好处就是 MySQL 内部实现 SQL 路由的功能,不用去改造业务代码。

    分库方案

    聊了下分表的方案,那什么时候分库呢?我们知道,MySQL 的高可用架构大多都是一主多从,所有写入操作都发生在 Master 上,随着业务的增长,数据量的增加,很多接口响应时间变得很长,经常出现 Timeout,而且通过升级 MySQL 实例配置已经无法解决问题了,这时候就要分库,通常有两种做法:按业务拆库和按表分库,下面就介绍这两种分库方案啦。

    按业务分库

    举个例子,交易系统 trade 数据库单独部署在一台 RDS 实例,现在交易需求及功能越来越多,订单,价格及库存相关的表增长很快,部分接口的耗时增加,同时有大量的慢查询告警,升级 RDS 配置效果不大,这时候就需要考虑拆分业务,将库存,价格相关的接口独立出来。

    img

    这样按照业务模块拆分之后,相应的 trade 数据库被拆分到了三个 RDS 实例中,数据库的写入能力提升,服务的接口响应时间也变短了,提高了系统的稳定性。

    按表分库

    上面介绍了分表方案,常见的有垂直分表和水平分表(拆分后的子表都在同一个 RDS 实例中存储),对应的分库就是垂直分库和水平分库,这里的分库其实是拆分 RDS 实例,是将拆分后的子表存储在不同的 RDS 实例中,垂直分库实际业务用的很少,就不介绍了,主要介绍下水平分库。

    举个例子,交易数据库的订单表 orders 有2亿多数据,RDS 实例遇到了写入瓶颈,普通的 insert 都需要50ms,时常也会收到 CPU 使用率告警,这时就要考虑分库了。根据业务量增长趋势,计划扩容一台同配置的RDS实例,将订单表 orders 拆分20个子表,每个 RDS 实例10个。

    img这样解决了订单表 orders 太大的问题,查询的时候要先通过分区键 user_id 定位是哪个 RDS 实例,再定位到具体的子表,然后做 DML操作,问题是代码改造的工作量大,而且服务调用链路变长了,对系统的稳定性有一定的影响。其实已经有些数据库中间件实现了分库分表的功能,例如常见的 mycat,阿里云的 DRDS 等。

    分布式数据库

    通过上面的分表和分库方案的介绍,主要会遇到下面三类问题:

    1. MySQL单 Master 的写入性能瓶颈。

    2. 分库分表后的 SQL 解析处理,服务调用链路变长,系统变得不稳定。

    3. 分库分表后动态扩容不好实现,例如开始分了20个表,不影响业务的情况下扩容至50个表不好实现。

    拆分后的问题

    垂直拆分

    1. 跨库Join问题

    在垂直拆分之前,系统中所需的数据是可以通过表 Join 来完成的,而拆分之后,数据库可能分布式在不同 RDS 实例,Join 处理起来比较麻烦,根据 MySQL 开发规范,一般是禁止跨库 Join 的,那该怎么处理呢?

    首先要考虑这种垂直拆分的合理性,如果可以调整,那就优先调整,如果无法调整,根据以往的实际经验,总结几种常见的解决思路。

    • 全局表

    用过 mycat 做分库分表的朋友都清楚,有个全局表的概念,也就是每个 DataNode 上都有一份全量数据,例如一些数据字典表,数据很少修改,可以避免跨库 Join 的性能问题。

    • 数据同步

    对于分布式系统,不同的服务的数据库是分布在不同的 RDS 实例上的,在禁止跨库 Join 的情况下,数据同步是一种解决方案。

    img

    通过数据同步工具将 user 库的 users 表实时同步到trade库中,这样就可以直接在 trade 库做 Join 操作,比较依赖于同步工具的稳定性,如果同步有延迟,就会导致数据不一致,产生脏数据,需要做好风险评估和兜底方案。

    1. 分布式事务问题

    拆分之后,数据分布在不同的 RDS 实例上,对表的 DML 操作就变成了多个子表的 DML 操作,就涉及到分布式事务,也要遵循事务 ACID 特性,同时也会提到两个重要的理论:CAP(Consistency一致性,Availability可用性,Partition tolerance分区容忍性Partitiontolerance)和BASE(Basically Available基本可用, Soft state软状态,Eventually consistent最终一致性),进而产生了解决分布式事务问题不同的方案。

    MySQL XA事务

    MySQL支持分布式事务(XA 事务或者 2PC 两阶段提交),分为两个阶段:Prepare 和 Commit,事务处理过程如下

    img

    如果任何一个 XA Client 否决了此次提交,所有数据库都要求 XA Manager 回滚它们在事务中的信息,优点是可以最大程度保证了数据的强一致,适合对数据强一致要求很高的业务场景;缺点就是实现复杂,牺牲了可用性,对性能影响较大,不适合高并发高性能场景。

    本地消息表

    本地消息表实现方式应该是业界使用最多的,其核心思想是将分布式事务拆分成本地事务进行处理,其基本的设计思想是将远程分布式事务拆分成一系列的本地事务。

    img

    处理过程

    消息生产方:需要额外建一个消息表,并记录消息发送状态,消息表和业务数据要在一个事务里提交,也就是说他们要在一个数据库里面。然后消息会经过 MQ 发送到消息的消费方,如果消息发送失败,会进行重试发送。

    消息消费方:需要处理这个消息,并完成自己的业务逻辑,此时如果本地事务处理成功,表明已经处理成功了,如果处理失败,那么就会重试执行。如果是业务上面的失败,可以给生产方发送一个业务补偿消息,通知生产方进行回滚等操作。

    生产方和消费方定时扫描本地消息表,把还没处理完成的消息或者失败的消息再发送一遍。如果有靠谱的自动对账补账逻辑,这种方案还是非常实用的。

    水平拆分

    1. 分布式全局唯一ID

    MySQL InnoDB的表都是使用自增的主键ID,分库分表之后,数据表分布不同的分片上,如果使用自增 ID 作为主键,就会出现不同分片上的主机 ID 重复现象,可以利用 Snowflake 算法生成唯一ID。

    1. 分片键选择

    选择分片键时,需要先统计该表上的所有的 SQL,尽量选择使用频率且唯一值多的字段作为分片键,既能做到数据均匀分布,又能快速定位到数据位置,例如user_id,order_id等。

    1. 数据扩容

    举个例子,目前交易数据库 trade 中的订单表 orders 已经做了水平分库(位于两个不同RDS实例上),这时发现两个 RDS 写入性能还是不够,需要再扩容一个RDS,同时将 orders 从原来的 20 个子表扩容到 40个(user_id % 40),这就需要迁移数据来实现数据重平衡,既要停机迁移数据,又要修改代码,有点出力不讨好的感觉啦。

    1. 跨库Join问题

    跟垂直拆分中的跨库 Join 问题是一样的。

    1. 跨库排序分页

    在处理order by user_id limit n场景是,当排序字段就是分片字段 user_id 的时候,通过分片键可以很容易定位到具体的分片,而当排序字段非分片字段的时候,例如order by create_time,处理起来就会变得复杂,需要在不同的分片节中将数据进行排序并返回,并将不同分片返回的结果集进行汇总和再次排序,最后再返回给用户。

    1. 跨库函数处理

    在使用max,min,sum,count之类的函数进行统计和计算的时候,需要先在每个分片数据源上执行相应的函数处理,然后将各个结果集进行二次处理,最终再将处理结果返回。

    1. ER分片

    在 RDBMS 系统中,表之间往往存在一些关联的关系,如果可以先确定好关联关系,并将那些存在关联关系的表记录存放在同一个分片上,就能很好地避免跨分片 join 问题。

    1. 非分片键过滤

      大部分业务场景都可以根据分片键来过滤,但是有些场景没有分片键过滤,例如按照状态和时间范围来查询订单表 orders,常见的SQL 这样的。

      img

      这种就很痛苦了,只能全部分片数据扫描一遍,将每个分片的数据Union之后再回复给客户端,这种场景可以考虑创建复合索引(status,create_time)让SQL走索引范围扫描,同时减少返回的数据量,如果是核心业务场景,可以考虑实时实时数仓(例如基于MPP架构的分析型数据库 ADB,分布式列式数据库 Clickhouse),将需要的表实时同步到数仓,然后再做处理,这也是实际业务中常见一种解决方案。

    总结

    上面聊了下 MySQ L的分表方案,分库方案,拆分后的问题以及给出了常用的解决方案,在实际开发中,会遇到核心业务表增长很快,数据量很大,MySQL 写入性能瓶颈的问题,这时需要根据业务的特性考虑分库分表,可以调研下相关的解决方案,主要有两种方案:代码改造(数据库中间件mycat,sharding-sphere)和分布式数据库(实际业务中使用比较多的有 PingCAP TiDB,阿里云 DRDS),可以优先使用分布式数据库方案,虽然成本会有所增加,但对应用程序没有侵入性,同时也可以比较好的支撑业务增长和系统快速迭代,今天就聊这么多,希望对您有所收获。

    展开全文
  • “ 面试中我们经常会碰到的关于分库分表的问题!今天就给大家介绍互联网公司常用 MySQL 分库分表方案!希望对大家的面试有所帮助!数据库瓶颈不管是 IO 瓶颈,还是 CPU 瓶颈,最终都...

    面试中我们经常会碰到的关于分库分表的问题!今天就给大家介绍互联网公司常用 MySQL 分库分表方案!希望对大家的面试有所帮助!

    数据库瓶颈

    不管是 IO 瓶颈,还是 CPU 瓶颈,最终都会导致数据库的活跃连接数增加,进而逼近甚至达到数据库可承载活跃连接数的阈值。

    在业务 Service 来看就是,可用数据库连接少甚至无连接可用。接下来就可以想象了吧(并发量、吞吐量、崩溃)。

    IO 瓶颈

    第一种:磁盘读 IO 瓶颈,热点数据太多,数据库缓存放不下,每次查询时会产生大量的 IO,降低查询速度→分库和垂直分表。

    第二种:网络 IO 瓶颈,请求的数据太多,网络带宽不够→分库。

    CPU 瓶颈

    第一种:SQL 问题,如 SQL 中包含 join,group by,order by,非索引字段条件查询等,增加 CPU 运算的操作→SQL 优化,建立合适的索引,在业务 Service 层进行业务计算。

    第二种:单表数据量太大,查询时扫描的行太多,SQL 效率低,CPU 率先出现瓶颈→水平分表。


    分库分表

    水平分库

    水平分库,如下图:

    概念:以字段为依据,按照一定策略(hash、range等),将一个库中的数据拆分到多个库中。

    结果:

    • 每个库的结构都一样

    • 每个库的数据都不一样,没有交集

    • 所有库的并集是全量数据

    场景:系统绝对并发量上来了,分表难以根本上解决问题,并且还没有明显的业务归属来垂直分库。

    分析:库多了,IO 和 CPU 的压力自然可以成倍缓解。

    水平分表

    水平分表,如下图:

    概念:以字段为依据,按照一定策略(hash、range 等),将一个表中的数据拆分到多个表中。

    结果:

    • 每个表的结构都一样

    • 每个表的数据都不一样,没有交集

    • 所有表的并集是全量数据

    场景:系统绝对并发量并没有上来,只是单表的数据量太多,影响了 SQL 效率,加重了 CPU 负担,以至于成为瓶颈。

    分析:表的数据量少了,单次 SQL 执行效率高,自然减轻了 CPU 的负担。

    垂直分库

    垂直分库,如下图:

    概念:以表为依据,按照业务归属不同,将不同的表拆分到不同的库中。

    结果:

    • 每个库的结构都不一样

    • 每个库的数据也不一样,没有交集

    • 所有库的并集是全量数据

    场景:系统绝对并发量上来了,并且可以抽象出单独的业务模块。

    分析:到这一步,基本上就可以服务化了。例如,随着业务的发展一些公用的配置表、字典表等越来越多,这时可以将这些表拆到单独的库中,甚至可以服务化。

    再有,随着业务的发展孵化出了一套业务模式,这时可以将相关的表拆到单独的库中,甚至可以服务化。

    垂直分表

    垂直分表,如下图:

    概念:以字段为依据,按照字段的活跃性,将表中字段拆到不同的表(主表和扩展表)中。

    结果:

    • 每个表的结构都不一样

    • 每个表的数据也不一样,一般来说,每个表的字段至少有一列交集,一般是主键,用于关联数据

    • 所有表的并集是全量数据

    场景:系统绝对并发量并没有上来,表的记录并不多,但是字段多,并且热点数据和非热点数据在一起,单行数据所需的存储空间较大。

    以至于数据库缓存的数据行减少,查询时会去读磁盘数据产生大量的随机读 IO,产生 IO 瓶颈。

    分析:可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据(可能会冗余经常一起查询的数据)放在一起作为主表,非热点数据放在一起作为扩展表。

    这样更多的热点数据就能被缓存下来,进而减少了随机读 IO。拆了之后,要想获得全部数据就需要关联两个表来取数据。

    但记住,千万别用 join,因为 join 不仅会增加 CPU 负担并且会讲两个表耦合在一起(必须在一个数据库实例上)。

    关联数据,应该在业务 Service 层做文章,分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

    分库分表工具

    常用的分库分表工具如下:

    • sharding-sphere:jar,前身是 sharding-jdbc。

    • TDDL:jar,Taobao Distribute Data Layer。

    • Mycat:中间件。

    • ......


    注:工具的利弊,请自行调研,官网和社区优先。


    分库分表步骤

    根据容量(当前容量和增长量)评估分库或分表个数→选 key(均匀)→分表规则(hash 或 range 等)→执行(一般双写)→扩容问题(尽量减少数据的移动)。

    分库分表问题

    非 partition key 的查询问题

    基于水平分库分表,拆分策略为常用的 hash 法。

    ①端上除了 partition key 只有一个非 partition key 作为条件查询。

    映射法,如下图:

    基因法,如下图:

    注:写入时,基因法生成 user_id,如图。关于 xbit 基因,例如要分 8 张表,23=8,故 x 取 3,即 3bit 基因。

    根据 user_id 查询时可直接取模路由到对应的分库或分表。根据 user_name 查询时,先通过 user_name_code 生成函数生成 user_name_code 再对其取模路由到对应的分库或分表。id 生成常用 Snowflake 算法。

    ②端上除了 partition key 不止一个非 partition key 作为条件查询

    映射法,如下图:

    冗余法,如下图:

    注:按照 order_id 或 buyer_id 查询时路由到 db_o_buyer 库中,按照 seller_id 查询时路由到 db_o_seller 库中。感觉有点本末倒置!有其他好的办法吗?改变技术栈呢?

    ③后台除了 partition key 还有各种非 partition key 组合条件查询

    NoSQL 法,如下图:

    冗余法,如下图:


    非 partition key 跨库跨表分页查询问题

    基于水平分库分表,拆分策略为常用的 hash 法。注:用 NoSQL 法解决(ES 等)。

    扩容问题

    基于水平分库分表,拆分策略为常用的 hash 法。

    ①水平扩容库(升级从库法)

    注:扩容是成倍的。

    ②水平扩容表(双写迁移法)

    步骤如下:

    • 第一步:(同步双写)修改应用配置和代码,加上双写,部署。

    • 第二步:(同步双写)将老库中的老数据复制到新库中。

    • 第三步:(同步双写)以老库为准校对新库中的老数据。

    • 第四步:(同步双写)修改应用配置和代码,去掉双写,部署。

    注:双写是通用方案。


    分库分表总结

    关于分库分表总结如下:

    • 分库分表,首先得知道瓶颈在哪里,然后才能合理地拆分(分库还是分表?水平还是垂直?分几个?)。且不可为了分库分表而拆分。

    • 选 key 很重要,既要考虑到拆分均匀,也要考虑到非 partition key 的查询。

    • 只要能满足需求,拆分规则越简单越好。

    作者:尜尜人物

    编辑:陶家龙

    出处:cnblogs.com/littlecharacter/p/9342129.html

    展开全文
  • MySQL分库分表方案

    2021-01-27 07:38:22
    MySQL分库分表方案问题:什么是最好的切分MySQL表的方式?我想到的有:应用层切分?MySQL代理层切分?提供中心查找分片服务?你们知道任何这方面有趣的项目或者工具吗?回答:最好的切分MySQL的方式就是:除非...
  • MySql分库分表方案

    2020-12-02 16:45:46
    垂直拆分比较简单,也...上图中订单数据达到了4000万,我们也知道mysql单表存储量推荐是百万级,如果不进行处理,mysql单表数据太大,会导致性能变慢。使用方案可以参考数据进行水平拆分。把4000万数据拆分4张表或...
  • 五、分库分表问题 1、非partition key的查询问题 基于水平分库分表,拆分策略为常用的hash法。 端上除了partition key只有一个非partition key作为条件查询 映射法 基因法 注:写入时,基因法生成user_id,如图。...
  • MySQL数据库之分库分表方案

    千次阅读 2022-06-10 16:09:16
    MySQL数据库之分库分表方案
  • 各位小伙伴,在目前企业级开发中采用Mysql做为数据库是一个主流选择,而当数据量比较大的情况下,为了支撑项目的正常快速的运行,我们不得不选择对数据库分库分表操作,本章节就对数据库的分表做一些方案的讲解,...
  • 一、数据库瓶颈不管是IO瓶颈,还是CPU瓶颈,最终都会导致数据库的活跃连接数增加,进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是,可用数据库... 分库和垂直分表。第二种:网络IO瓶颈,请求...
  • 1.如果只是为了分页,可以考虑这种分表,就是表的id是范围性的,且id是连续的,比如第...Mysql分库分表方案 1.为什么要分表: 当一张表的数据达到几千万时,你查询一次所花的时间会变多,如果有联合查询的话,我...
  • MySQL 分库分表方案总结

    千次阅读 2018-12-16 14:39:52
    mysql中有一种机制是表锁定和行锁定,是为了保证数据的完整性。表锁定表示你们都不能对这张表进行操作,必须等我对表操作完才行。行锁定也一样,别的sql必须等我对这条数据操作完了,才能对这条数据进行操作。 2....
  • 【分库、分表】MySQL分库分表方案

    千次阅读 2019-04-19 22:59:11
    一、Mysql分库分表方案 1.为什么要分表: 当一张表的数据达到几千万时,你查询一次所花的时间会变多,如果有联合查询的话,我想有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。 mysql...
  • mysql-分库分表方案

    千次阅读 2019-04-09 19:31:38
    上图中订单数据达到了4000万,我们也知道mysql单表存储量推荐是百万级,如果不进行处理,mysql单表数据太大,会导致性能变慢。使用方案可以参考数据进行水平拆分。把4000万数据拆分4张表或者...
  • mysql分库分表方案

    千次阅读 2018-10-24 16:20:51
    分库分表的几种方式 1、把一个实例中的多个数据库拆分到不同的实例 2、把一个库中的表分离到不同的数据库中   3、对一个库中的相关表进行水平拆分到不同的实例数据库中 如何选择分区键: 1、分区键要能...
  • Mysql分库分表

    2022-04-09 20:23:49
    当前互联网发展速度越来越快,很多应用的用户量也越来越多,很多大的互联网项目的用户量甚至破亿,日活跃用户也在几千万,用户的活动信息一般都记录到了数据库中,那么...本文简单讲解了一下Mysql中的分库分表方案
  • Mysql 分库分表

    千次阅读 2021-11-14 22:30:28
    分库分表方案:只分库、只分表、分库又分表。 垂直拆分:由于表数量多导致的单个库大。将表拆分到多个库中。 水平拆分:由于表记录多导致的单个库大。将表记录拆分到多个表中。 一,垂直拆分 垂直拆分又称为纵向...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 37,611
精华内容 15,044
关键字:

mysql分库分表方案

mysql 订阅