Mysql数据库分库分表实践

2021/4/12 19:25:25

编程Tag： 数据 mysql 分片查询数据库分库分表

本文主要是介绍Mysql数据库分库分表实践，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

目前应用系统的最大瓶颈出现在数据库，主要是基于数据库的逻辑存储结构，和磁盘的物理特性决定了随机读取效率低下，无法靠简单磁盘阵列的扩展或者分布式文件系统，来提升性能。

数据库的瓶颈

不管是IO瓶颈，还是CPU瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是，可用数据库连接少甚至无连接可用。接下来就可以想象了吧（并发量、吞吐量、崩溃）。

IO瓶颈
- 由于关系型数据库的存储结构，不适用于分布式文件系统，往往会出现磁盘读IO瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的IO，降低查询速度 ==> 分库和垂直分表。
- 网络IO瓶颈，请求的数据太多，网络带宽不够 ==> 分库。
CPU瓶颈
- SQL问题，如SQL中包含join，group by，order by，非索引字段条件查询等，增加CPU运算的操作 ==> SQL优化，建立合适的索引，在业务Service层进行业务计算。
- 单表数据量太大，查询时扫描的行太多，SQL效率低，CPU率先出现瓶颈 ==> 水平分表。

分库分表

水平分片

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3VXHQodM-1618219274275)(https://raw.githubusercontent.com/WengyXu/oss/master/uPic/2021-03-15/640-20210315152055836.png)]

概念：

以字段为依据，按照一定策略（hash、range等），将数据拆分到多个分片中。分片多了，io和cpu的压力自然可以成倍缓解。

场景：

水平分表

系统绝对并发量并没有上来，变更不频繁，只是单表的数据量太多，存在冷热数据，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。
水平分库

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

垂直分片

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OIiXhjPU-1618219274276)(https://raw.githubusercontent.com/WengyXu/oss/master/uPic/2021-03-15/640-20210315151958350.png)]

概念：

以表为依据，按照业务归属不同，将数据拆分到不同的分片中。

场景：

垂直分表
- 系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。
- 可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。
垂直分库
- 系统绝对并发量上来了，并且可以抽象出单独的业务模块。
- 到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。我们在微服务拆分的时候，其实已经完成了数据库的垂直拆分

3. 分库分表的基本原则

什么时候需要考虑分库分表

当MySQL单表的记录数达到500W左右时，即要考虑分库分表，来满足业务增长
分片键的选择

分片键的选择，要结合业务来进行，一般在SQL占比最大的语句中，选择分片键。

首先根据需求分析，判断系统请求最大的业务类型，并提供生产环境相应的数据支撑。

如何在生产环境查询sql的执行次数？
```
use performance_schema;

SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC
```
通过该语句可以查询哪类的SQL执行最多
冷热数据分离，大字段分离
将Mysql定位于事务性数据库（OLTP），专注于事务流水操作，发挥关系形数据库的特长
结合业务避免笛卡尔乘积，尽量以小表驱动大表，来进行分库关联
分库分表会大大提高系统设计的复杂度，需要平衡

4. 常见分片策略

基于范围分片
- 时间：按照年、月、日去切分。例如order_2020、order_202005、order_20200501
- 地域：按照省或市去切分。例如order_beijing、order_shanghai、order_chengdu
- 大小：从0到1000000一个表。例如1000001-2000000放一个表，每100万放一个表
优点：新的数据可以落在新的存储节点上，如果集群扩容，数据无需迁移。
缺点：数据热点分布不均，数据冷热不均匀，导致节点负荷不均。
Hash取模分片

整型的Key可直接对设备数量取模，其他类型的字段可以先计算Key的哈希值，然后再对设备数量取模。假设有n台设备，编号为0 ~ n-1，通过Hash(Key) % n就可以确定数据所在的设备编号。该模式也称为离散分片。

优点：实现简单，数据分配比较均匀，不容易出现冷热不均，负荷不均的情况。
缺点：扩容时会产生大量的数据迁移，比如从n台设备扩容到n+1，绝大部分数据需要重新分配和迁移。（有优化方案）
一致性Hash+虚拟节点

通过一致性Hash + 虚拟节点可以在保证数据冷热均匀的基础上，大大减少数据迁移的工作量

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Vvc5u8Wb-1618219274277)(https://raw.githubusercontent.com/WengyXu/oss/master/uPic/2021-03-17/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1dlaXhpYW9odWFp,size_16,color_FFFFFF,t_70-20210317184613098.png)]

扩容后：
Jump Consistent Hash

参考1：https://opensource.actionsky.com/20200213-hash/

参考2：https://opensource.actionsky.com/20201223-dble/

参考3： https://arxiv.org/ftp/arxiv/papers/1406/1406.2294.pdf

参考4：https://opensource.actionsky.com/20190910-dble/
分组法

Hash分片是可以解决数据均匀的问题，范围法`可以解决数据迁移问题，那我们可以不可以两者相结合呢？利用这两者的特性呢？

考虑在hash分片的基础，加上一个Group的概念，组内遵循hash分片，组与组之间，采用范围法

设计方案如图：

因为组内采用了hash分片，这样落在每一个分片的数据是基本平均的，每一个数据库关联不同的分片，而且数据库也可以关联不同分组的分片，这样就可以保证每个数据库的流量平均，并且扩容只需要新增一个组就可以了。
- 数据结构设计：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-urBQUnk7-1618219274281)(https://raw.githubusercontent.com/WengyXu/oss/master/uPic/2021-04-09/%25E6%259C%25AA%25E5%2591%25BD%25E5%2590%258D%25E7%25BB%2598%25E5%259B%25BE.png)]

可以将这个映射关系缓存起来，这样不会影响性能

5. 分库分表后带来的问题

非分区键查询，需要遍历多个库

场景一（一对一的场景）

用户表采用 user_id 哈希取模进行了水平分库，分散了单库的压力，但是这里可能会出现一些问题，一个是说，用户在登录的时候，可能不是根据userid登陆的，可能是根据用户名，手机号之类的来登录的，此时你又没有userid，怎么知道去哪个表里找这个用户的数据判断是否能登录呢？
- 解决方案：冗余双写映射法
  
  创建一张userId 和手机号的映射表（该映射表也可以通过手机号分片），在往用户表插入的时候，同时维护映射表，这样通过手机号和 userId 都可以快速定位到某个分片。这张映射表还可以考虑放在缓存中
  - 冗余会带来一致性问题，跨库双写事务如何处理
    
    详见分布式事务小节
场景二（一对多的场景）

用户表和订单表是一对多的情况，通过订单id查询订单详情流量占比为60%，通过userId查询订单列表的访问量占30%，这时候如果通过订单id分片，则查询用户的订单列表，就需要遍历所有的分片；如果通过userId 分片，在查询订单id的详情时，就需要遍历所有的分片
- 解决方案：基因法（ER分片）
  
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XJVWpsDy-1618219274282)(https://raw.githubusercontent.com/WengyXu/oss/master/uPic/2021-04-01/%25E6%259C%25AA%25E5%2591%25BD%25E5%2590%258D%25E6%2596%2587%25E4%25BB%25B6(1)].png)
场景三（多对多的场景）

典型的场景，用户之间的相互关注，需要查询我的关注和关注我的流量各占50%，一旦数据量爆炸以后，需要分片，如果采用 user_id 分片，则查询“关注我的人”就需要遍历多个分片；如果采用follower_id分片，则查询“我的关注”还是需要遍历多个分片
- 解决方案：异构表冗余
  
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U9OyRkzn-1618219274283)(https://raw.githubusercontent.com/WengyXu/oss/master/uPic/2021-04-02/%25E6%259C%25AA%25E5%2591%25BD%25E5%2590%258D%25E7%25BB%2598%25E5%259B%25BE.png)]
- 数据冗余势必会带来数据一致性的问题
  
  详见分布式事务小节
场景四：复合场景（多分片键组合）

销售中心订单表，包含用户ID(user_id), 订单ID(order_id), 商户ID( merchant_id) , 主要的访问方式有
1. 通过 user_id 查询我的订单 (30%)
2. 通过 order_id 查询订单详情（60%）
3. 通过 merchant_id 查询商户的订单量（10%）
那么在数据量很大的时候，单表无法支撑时，应该如何进行拆分呢？

碰到复杂的问题，可以把他拆解成已知的问题：
- user_id 和 order相当于是一对多的场景，可以采用基因法
- user_id和merchant_id相当于多对多的场景，可以采用异构表冗余法
可以通过两者结合的方法来处理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wk4JCqda-1618219274284)(https://raw.githubusercontent.com/WengyXu/oss/master/uPic/2021-04-07/%25E5%2588%2586%25E5%25BA%2593%25E5%2588%2586%25E8%25A1%25A8.png)]
场景五
- 消息流水表（用户动态，app消息推送, 订单表）：记录数量随着时间的推移而不断增长，数据存在冷热，离当前时间越近的数据访问频度越高，流水表在原有分库的基础上，最好要再按时间分表，这样可以防止随着时间推移出现数据爆炸。
- 在流水表的访问流量中，经常会碰到查询最近1个月，最近1年的数据，如果单单只存储一个月的数据，那么在应用层就需要查询两次，为了减少一次查询，给应用层代理便利，可以适当的冗余。例如：
  1. 消息推送表，按月分表，可以在存储当月数据的同时，冗余上个月的数据，每张表存储2个月的数据，这样在查询最近一个月的数据时，就可以只查询一次，就返回从上个月的今天到现在的所有数据
  2. 订单表分片的策略，按照用户ID的基因分库，按照年分表，每张表存储2年的数据（存储今年的数据并冗余上去年的数据），这样在查询最近一年的数据时，就可以只查询一次。
场景六

有些时候，有一些工具表，比如数据字典，手机归属地表，省份表等，每一个分片都有可能会使用到
- 解决方案：全局表（变化少，并发低，可以考虑 XA 两阶段事务）
  
  像数据字典，省份表，厂商表这些变动不是非常频繁的表可以，考虑采用全局表，在多个库中都包含该表
复杂搜索场景

如在运营管理界面上，有一个用户管理模块，需要对用户按照性别，住址，年龄，职业等各种条件进行各种组合的复杂搜索

解决方案：对用户数据进行binlog监听，把需要搜索的所有字段同步到Elasticsearch中去，建立好搜索的索引
简单的OLAP场景

有些时候我们还会有一些简单报表统计的需求，如：需要统计销量前100的商品

解决方案：单独创建一个报表库，在其中创建中间表，通过订阅变更的消息，来实现增量同步

创建报表库，在报表库中维护一个商品销量表 rpt_sales_volume，当用户下单后，发布消息，报表服务通过消费该消息，更新商品的销量，当数据量很大时可以考虑分表（报表数据的查询一般提供给运营，并发不会特别大）

注意：应该将Mysql定位于事务性数据库（OLTP），专注于事务流水操作，发挥关系形数据库的特点，尽量通过离线或者流式计算的方式来处理OLAP，

分页查询问题

分页需要确认排序字段，在分库中查询出来以后，进行二次组装，需要在每一个分片上先查询出来pageSize*pageNum数量的记录，然后在应用层进行组装（效率比较低）
监听binlog, 将变更发送到消息队列，同步到Elasticsearch中，在ES中进行分页查询

分布式事务

分布式事物的技术方案很多，这里只介绍适合做数据冗余的分布式事务技术

**实时性的要求不是非常高：**通过最终一致性来保证分布式事务
- 方案一：binlog监听
  
  通过Canal监听binlog, 增量同步到Elasticsearch中，为了防止并发过大，以及每一条日志都可以正确同步，保证数据的最终一致性，在Canal和Elasticsearch之间加了一层MQ（一般使用kafka）。通过mq的ack机制保证每一条binlog日志的正常同步，同时给大并发提供缓冲
  - 注意： 在处理同一行数据时，需要保证MQ的有序消费
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-E41ly40r-1618219274285)(https://raw.githubusercontent.com/WengyXu/oss/master/uPic/2021-04-09/image-20210409141524612.png)]
  
  数据对比
  
  为了给数据同步上一个保险，还可以利用有序队列，进行数据比对，我们可以利用有序队列的特性，让其第一条消息堆积十分钟，那么后续消息基本上也会堆积十分钟，然后就可以消费这个消息进行数据拉取，拿到最新的数据进行数据对比，如图所示：
  
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-F8I98ir4-1618219274285)(…/Library/Application%20Support/typora-user-images/image-20210412134218161.png)]
  - 其他同步工具选型
    
    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Zh21Kmen-1618219274286)(https://raw.githubusercontent.com/WengyXu/oss/master/uPic/2021-04-09/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9lbGFzdGljLmJsb2cuY3Nkbi5uZXQ=,size_16,color_FFFFFF,t_70.png)]
- 方案二：本地消息表
  
  本地消息表这个方案最初是 ebay 架构师 Dan Pritchett 在 2008 年发表给 ACM 的文章。该方案中会有消息生产者与消费者两个角色，假设系统 A 是消息生产者，系统 B 是消息消费者，其大致流程如下：
  
  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-itADzoS4-1618219274287)(https://raw.githubusercontent.com/WengyXu/oss/master/uPic/2021-04-08/native-message-20210408172410217.jpg)]
  1. 当系统 A 被其他系统调用发生数据库表更操作，首先会更新数据库的业务表，其次会往相同数据库的消息表中插入一条数据，两个操作发生在同一个事务中
  2. 系统 A 的脚本定期轮询本地消息往 mq 中写入一条消息，如果消息发送失败会进行重试
  3. 系统 B 消费 mq 中的消息，并处理业务逻辑。如果本地事务处理失败，会在继续消费 mq 中的消息进行重试，如果超过重试次数，记录错误记录和日志，人工介入（当错误记录超过100条时，所有事务全部回滚，防止产生大量需要人工处理的数据）
    
    这里没有采用失败后回滚的逻辑，主要还是考虑事务一的数据，一旦入库，后续的事务必须成功，这样不会产生脏数据。这就需要在事务开启时，对需要的资源进行预留和锁定。
  本地消息表实现的条件：
  1. 消费者与生成者的接口都要支持幂等
  2. 生产者需要额外的创建消息表
  3. 事务开启时，对需要的资源进行预留和锁定（分布式锁）
  4. 需要建立错误监控体系，及时人工介入，错误记录要保留完整的数据，方便数据恢复
  容错机制：
  1. 步骤 1 失败时，事务直接回滚
  2. 步骤 2、3 写 mq 与消费 mq 失败会进行重试
  3. 超过失败重试的次数，记录错误记录和日志，人工介入（当错误记录超过100条时，所有事务全部回滚，防止产生大量需要人工处理的数据）
  此方案的核心是将需要分布式处理的任务通过消息日志的方式来异步执行。消息日志可以存储到本地文本、数据库或消息队列，再通过业务规则自动或人工发起重试。人工重试更多的是应用于支付场景，通过对账系统对事后问题的处理。
**实时性，和准确性要求高：**本地消息表+实时查询

在原数据库中插入完以后，先在相同的库添加一条映射记录，然后记录缓存，表示映射表有数据还没有同步，按异步方案进行同步，如果此时已经需要查询该映射记录，则先按映射表的分片表的记录查询出来，并加上缓存中记录的这条映射。
使用支持ACID的分布式数据库（Mysql Cluster）
- 实现原理
  
  分布式数据库实现分布式事务的主流方法还是2PC, 过多副本（Multi-Paxos）,解决了2PC单点，阻塞和数据不一致的问题
  
  如上图所示，当分布式事务提交时，会选择其中的一个数据分片作为协调者在所有数据分片上执行两阶段提交协议。由于所有数据分片都是通过 Paxos 复制日志实现多副本高可用的，当主副本发生宕机后，会由同一数据分片的备副本转换为新的主副本继续提供服务，所以可以认为参与者和协调者都是保证高可用不宕机的（多数派存活），绕开了协调者宕机的问题。
  
  在参与者高可用的实现前提下，可以对协调者进行了“无状态”的优化。在标准的两阶段提交中，协调者要通过记录日志的方法持久化自己的状态，否则如果协调者和参与者同时宕机，协调者恢复后可能会导致事务提交状态不一致。但是如果我们认为参与者不会宕机，那么协调者并不需要写日志记录自己的状态。
  
  所以在第一阶段所有参与者都回复prepare完成以后，即可以反馈事务提交成功，提升了2PC的效率
  由于存在多副本，只要保证在prepare阶段，验证事务执行没有错误，协调者发出commit指令后，就可以乐观的认为，事务执行成功并反馈给事务发起者。相信commit消息会被多数副本收到，多数副本收到消息以后，剩下的就交给他们自己同步
  
  在上图中（绿色部分表示写日志的动作），左侧为标准两阶段提交协议，用户感知到的提交时延是4次写日志耗时以及2次 RPC 的往返耗时；由于少了协调者的写日志耗时以及提前了应答客户端的时机，用户感知到的提交时延是1次写日志耗时以及1次 RPC 的往返耗时。

分片的扩容问题

成倍扩容法
1. 首先添加一个从库，实现和主库同步
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YF4ao5bQ-1618219274287)(…/Library/Application%20Support/typora-user-images/image-20210412150032499.png)]
1. 同步完成后，修改数据源，成倍扩容
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h0qas7eA-1618219274288)(…/Library/Application%20Support/typora-user-images/image-20210412152141828.png)]
时间断+Hash分片

根据时间断来进行分片，（比如在2020-01-01前创建的用户id，取Hash值后，按100取模；2020-01-01至2020-12-31创建的用户id,取Hash值后，按1000取模；）好处：扩容不需要对数据迁移。

6. 最佳实践

可以通过用户手机的归属地进行分库，然后基于用户ID 的Hash值进行分表，其他和用户相关的表（比如订单和内容），打入归属地和用户ID 的基因，进行ER分片。

Sharding-Sphere实践

DBLE实践

Canal, XData + Elasticsearch 实践

这篇关于Mysql数据库分库分表实践的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！