【MySQL系列笔记】分库分表

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
简介: 分库分表是一种数据库架构设计的方法,用于解决大规模数据存储和处理的问题。分库分表可以简单理解为原来一个表存储数据现在改为通过多个数据库及多个表去存储,这就相当于原来一台服务器提供服务现在改成多台服务器组成集群共同提供服务。

1. 概述

1.1. 原因

分库分表是一种数据库架构设计的方法,用于解决大规模数据存储和处理的问题。

分库分表可以简单理解为原来一个表存储数据现在改为通过多个数据库及多个表去存储,这就相当于原来一台服务器提供服务现在改成多台服务器组成集群共同提供服务。

随着订单数据的增加,当MySQL单表存储数据达到一定量时其存储及查询性能会下降,在《阿里巴巴规范手册》中提到MySQL单表行数超过 500 万行或者单表容量超过 2GB时建议进行分库分表。

📎阿里巴巴规范手册(嵩山版).pdf

这里说的500 万行或单表容量超过 2GB并不是定律,只是根据生产经验而言。

1.2. 为什么需要分库分表呢?

  1. 数据存储容量:随着业务数据量增加,单个数据库可能会面临存储容量不足的问题
  2. 数据查询效率:单表数据量过大,查询效率变得较低。
  3. 满足高并发场景:单个数据库无法满足高并发需求,通过数据分散到多个数据库,减轻单个数据库的负载,提高系统的并发处理能力和响应速度。
  4. 提高系统的可用性和容错性:当某个数据库发生故障时,可以快速切换。

1.3. 单表达到千万级别,性能会下降?

我们知道为了提高表的查询性能会增加索引,MySQL索引底层是B+Tree,达到千万级别,B+Tree的高度会增高,查询会明显变慢。

MySQL在使用索引时会将索引加入内存,如果数据量非常大从磁盘去查询索引就会产生很多磁盘IO,从而影响性能,这些和表的设计及服务器的硬件配置都有关。

1.4. 分库分表导致问题点

1.4.1. 事务问题

分库分表后,假设两个表在不同的数据库,那么本地事务已经无效啦,需要使用分布式事务了。

1.4.2. 跨库关联

跨节点Join的问题:解决这一问题可以分两次查询实现。

1.4.3. 排序问题

跨节点的count,order by,group by以及聚合函数等问题:可以分别在各个节点上得到结果后在应用程序端进行合并。

1.4.4. 分页问题

  • 方案1:在个节点查到对应结果后,在代码端汇聚再分页。
  • 方案2:把分页交给前端,前端传来pageSize和pageNo,在各个数据库节点都执行分页,然后汇聚总数量前端。这样缺点就是会造成空查,如果分页需要排序,也不好搞。

1.4.5. 分布式ID

据库被切分后,不能再依赖数据库自身的主键生成机制啦,最简单可以考虑UUID,或者使用雪花算法生成分布式ID。

2. 分库分表方式

分库分表包括分库和分表两个部分,在生产中通常包括:垂直分库、水平分库、垂直分表、水平分表四种方式。

2.1. 垂直分表

2.1.1. 定义

商品信息中商品描述字段访问频次较低,且该字段存储占用空间较大,访问单个数据IO时间较长;商品信息中商品名称、商品图片、商品价格等 其他字段数据访问频次较高。 由于这两种数据的特性不一样,因此考虑将将访问频次低的商品描述信息存放在一张表中,访问频次高的商品信息放在另一张表中。商品信息表拆分为商品表,商品详情表。

垂直分表是将一个表按照字段分成多表,每个表存储其中一部分字段,比如按冷热字段进行拆分。

垂直拆表的好处:可以减少IO开销,充分发挥热门数据的作用。(冷热分离)

2.1.2. 拆分原则

  1. 把不常用的字段单独放在一张表。
  2. 把text,blob等大字段拆分出来放在附表中,比如文章的内容。
  3. 经常组合查询的列放在一张表中。

2.2. 垂直分库

2.2.1. 定义

垂直分库是指按照业务(DDD领域模型)将表进行分类,分布到不同的数据库上面,每个库可以放在不同的服务器上,它的核心理念是专库专用,微服务架构下通常会对数据库进行垂直拆分,不同业务数据放在单独的数据库中。(专库专用)

垂直分库的好处:将原来一个单数据库的压力分担到不同的数据库,可以很好应对高并发场景。

垂直分表但没有垂直分库

库内垂直分表只解决了单一表数据量过大的问题,但没有将表分布到不同的服务器上,因此每个表还是竞争同一个物理机的CPU、内存、网络IO、磁盘。

2.2.2. 特点

  1. 解决业务层面的耦合,业务清晰 。
  2. 能对不同业务的数据进行分级管理、维护、监控、扩展等 。
  3. 高并发场景下,垂直分库一定程度的提升IO、降低单机硬件资源的瓶颈。

2.3. 水平分表&水平分库

2.3.1. 定义

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

水平分表是在同一个数据库内,把同一个表的数据按一定规则拆到多个表中。其目的也是为解决单表数据量大的问题。

2.3.2. 拆分规则

  1. Hash取模

以订单分库分表为例,将全局唯一的订单号就数据库的个数进行取模(取余),将数据均分到不同的数据库/表。计算表达式为:db_订单号%3, 比如:10号订单会存入到db_1数据库,11号订单存储到db_2数据库。

优点:数据均匀。

缺点:扩容时需要重新哈希,数据库之间需要迁移数据。

  1. rang方式

主键递增,按照一定的数值范围进行拆分。比如0到500万到db_1数据库,500万到1000万到db_2数据库。

优点:方便后面数据库拓展

缺点:存在数据热点问题,导致单个数据库压力过大。

  1. 地域/时间方式

按照地域/时间划分,根据订单的创建时间,以及下单区域划分。

缺点:数据分布不均,存在一线城市数据量倍数于其他地区。

2.3.3. 总结

一般来说,在系统设计阶段就应该根据业务耦合松紧来确定垂直分库,垂直分表方案,在数据量及访问压力不是特别大的情况,首先考虑缓存、读写分离、索引技术等方案。若数据量极大,且持续增长,再考虑水平分库水平分表方案。

分库方案:设计三个数据库,根据用户id哈希,分库表达式为:db_用户id % 3

参考历史经验,前期设计三个数据库,每个数据库使用主从结构部署,可以支撑项目几年左右的运行,虽然哈希存在数据迁移问题,在很长一段时间也不用考虑这个问题

分表方案:根据订单范围分表,0---500万落到table_0,500万---1000万落到table_1,依次类推。

3. ShardingSphere

Apache ShardingSphere 是一款分布式的数据库生态系统,前身是ShardingJDBC,可以将任意数据库转换为分布式数据库,并通过数据分片、弹性伸缩、加密等能力对原有数据库进行增强。

所以数据分片是应对海量数据存储与计算的有效手段。ShardingSphere 基于底层数据库提供分布式数据库解决方案,可以水平扩展计算和存储。使用ShardingSphere 的数据分片功能即可实现分库分表。

3.1. 步骤

3.1.1. 创建数据库

订单数据库分为三个库 :jzo2o-orders-0、jzo2o-orders-1、jzo2o-orders-2

下边分别向三个数据库导入:jzo2o-orders-sharding.sql。

每个数据库对orders、biz_snapshot、orders_serve进行分表(暂分3个表),其它表为广播表(即在每个数据库都存在且数据是完整的),如下图:

3.1.2. 添加依赖

在jzo2o-framework下的jzo2o-shardingsphere-jdbc中添加了具体shardingsphere的依赖,在orders-base工程引入jzo2o-shardingsphere-jdbc的依赖即可,如下:

<dependency>
  <groupId>com.jzo2o</groupId>
  <artifactId>jzo2o-shardingsphere-jdbc</artifactId>
</dependency>

3.1.3. 本地配置文件

在orders-base工程的resources下创建文件shardingsphere-jdbc-dev.yml(可直接拷贝项目源码目录下的shardingsphere-jdbc-dev.yml)。

配置文件如下:

jzo2o-orders-0、jzo2o-orders-1、jzo2o-orders-2表示三个数据源对应三个订单数据库。

每个数据库中对orders、orders_serve、biz_snapshot进行分表。

详细如下:

dataSources:
  jzo2o-orders-0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://192.168.101.68:3306/jzo2o-orders-0?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
    username: root
    password: mysql
  jzo2o-orders-1:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://192.168.101.68:3306/jzo2o-orders-1?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
    username: root
    password: mysql
  jzo2o-orders-2:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://192.168.101.68:3306/jzo2o-orders-2?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&serverTimezone=Asia/Shanghai
    username: root
    password: mysql
rules:
  - !TRANSACTION
    defaultType: BASE
  - !SHARDING
    tables:
      orders:
        #由数据源名 + 表名组成(参考 Inline 语法规则)
        actualDataNodes: jzo2o-orders-${0..2}.orders_${0..2}
        #分表策略
        tableStrategy:
          standard:
            #分片列名称
            shardingColumn: id
            # 分片算法名称
            shardingAlgorithmName: orders_table_inline
        #分库策略
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: orders_database_inline
      orders_serve:
        actualDataNodes: jzo2o-orders-${0..2}.orders_serve_${0..2}
        tableStrategy:
          standard:
            shardingColumn: id
            shardingAlgorithmName: orders_serve_table_inline
        databaseStrategy:
          standard:
            shardingColumn: serve_provider_id
            shardingAlgorithmName: orders_serve_database_inline
      biz_snapshot:
        actualDataNodes: jzo2o-orders-${0..2}.biz_snapshot_${0..2}
        tableStrategy:
          standard:
            shardingColumn: biz_id
            shardingAlgorithmName: biz_snapshot_table_inline
        databaseStrategy:
          standard:
            shardingColumn: db_shard_id
            shardingAlgorithmName: biz_snapshot_database_inline
    shardingAlgorithms:
      # 订单-分库算法
      orders_database_inline:
        type: INLINE
        props:
          # 分库算法表达式
          algorithm-expression: jzo2o-orders-${user_id % 3}
          # 分库支持范围查询
          allow-range-query-with-inline-sharding: true
      # 订单-分表算法
      orders_table_inline:
        type: INLINE
        props:
          # 分表算法表达式
          algorithm-expression: orders_${(int)Math.floor(id % 10000000000 / 15000000)}
          # 允许范围查询
          allow-range-query-with-inline-sharding: true
      # 服务单-分库算法
      orders_serve_database_inline:
        type: INLINE
        props:
          # 分库算法表达式
          algorithm-expression: jzo2o-orders-${serve_provider_id % 3}
          # 允许范围查询
          allow-range-query-with-inline-sharding: true
      # 服务单-分表算法
      orders_serve_table_inline:
        type: INLINE
        props:
          # 允许范围查询
          algorithm-expression: orders_serve_${(int)Math.floor(id % 10000000000 / 15000000)}
          # 允许范围查询
          allow-range-query-with-inline-sharding: true
      # 快照-分库算法
      biz_snapshot_database_inline:
        type: INLINE
        props:
          # 分库算法表达式
          algorithm-expression: jzo2o-orders-${db_shard_id % 3}
          # 允许范围查询
          allow-range-query-with-inline-sharding: true
      # 快照-分表算法
      biz_snapshot_table_inline:
        type: INLINE
        props:
          # 允许范围查询
          algorithm-expression: biz_snapshot_${(int)Math.floor((Long.valueOf(biz_id))  % 10000000000 / 15000000)}
          # 允许范围查询
          allow-range-query-with-inline-sharding: true
    # id生成器
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
  - !BROADCAST
    tables:
      - breach_record
      - orders_canceled
      - orders_refund
      - orders_dispatch
      - orders_seize
      - serve_provider_sync
      - state_persister
      - orders_dispatch_receive
      - undo_log
      - history_orders_sync
      - history_orders_serve_sync
props:
  sql-show: true

配置项说明参考官方文档:ShardingSphere数据分片

dataSources:数据源

jzo2o-orders-x:与actualDataNodes对应。

下边以orders表为例说明分库分表策略:

分库键:user_id

分库表达式:jzo2o-orders-${user_id % 3}

根据用户id计算落到哪个数据库

分表键:id

分表表达式:orders_${(int)Math.floor(id % 10000000000 / 15000000)}

按1500万为单位进行分表,比如:订单号2311020000000000019,为19位,表达式的值为19,匹配表orders_0,如果表达式的值大于1500万小于3000万匹配表orders_1。

tables:
    orders:
      #由数据源名 + 表名组成(参考 Inline 语法规则)
      actualDataNodes: jzo2o-orders-${0..2}.orders_${0..2}
      tableStrategy:#分表策略
        standard:
          shardingColumn: id #分片列名称
          shardingAlgorithmName: orders_table_inline  # 分片算法名称
      databaseStrategy:#分库策略
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: orders_database_inline
   shardingAlgorithms:
    # 订单-分库算法
    orders_database_inline:
      type: INLINE
      props:
        # 分库算法表达式
        algorithm-expression: jzo2o-orders-${user_id % 3}
        # 分库支持范围查询
        allow-range-query-with-inline-sharding: true
    # 订单-分表算法
    orders_table_inline:
      type: INLINE
      props:
        # 分表算法表达式
        algorithm-expression: orders_${(int)Math.floor(id % 10000000000 / 15000000)}
        # 允许范围查询
        allow-range-query-with-inline-sharding: true

!BROADCAST:指定广播表

广播表在 jzo2o-orders-0、jzo2o-orders-1、jzo2o-orders-2 每个数据库的数据一致。

3.1.4. Nacos配置数据源

进入nacos在jzo2o-orders-manager.yaml中配置数据源使用ShardingSphereDriver:

spring:
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:shardingsphere-jdbc-${spring.profiles.active}.yml


完整配置如下:

spring:
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:shardingsphere-jdbc-${spring.profiles.active}.yml
mybatis-plus:
  configuration:
    default-enum-type-handler: com.baomidou.mybatisplus.core.handlers.MybatisEnumTypeHandler
  page:
    max-limit: 1000
  global-config:
    field-strategy: 0
    db-config:
      logic-delete-field: isDeleted
      id-type: assign_id
xxl-job:
  port: 9998
canal:
    enable: true
    sync:
        application-name: ${spring.application.name}
    rabbit-mq:
        routing-keys: canal-mq-jzo2o-orders-manager
        exchange: exchange.canal-jzo2o
        queue: canal-mq-jzo2o-orders-manager
rabbit-mq:
    enable: true
jzo2o:
  trade:
    aliEnterpriseId: xxx
    wechatEnterpriseId: xxx
  job:
    autoEvaluateCount: 100
  openPay: true

3.1.5. 状态机分库分表

由于对状态机进行了分库分表,需要修改创建订单方法中启动状态机代码:

使用start(Long dbShardId, String bizId, T bizSnapshot) 方法启动状态机,传入分片键user_id。

com.jzo2o.orders.manager.service.impl.OrdersCreateServiceImpl#add

@Transactional(rollbackFor = Exception.class)
public void add(Orders orders) {
    ....
    //状态机启动
    orderStateMachine.start(orders.getUserId(), 
                            String.valueOf(orders.getId()), 
                            orderSnapshotDTO);
}

支付成功调用状态机变更状态方法:

使用:changeStatus(Long dbShardId, String bizId, StatusChangeEvent statusChangeEventEnum, T bizSnapshot)变更状态, 传入分片键user_id

com.jzo2o.orders.manager.service.impl.OrdersCreateServiceImpl#paySuccess

@Transactional(rollbackFor = Exception.class)
public void paySuccess(TradeStatusMsg tradeStatusMsg) {
    ....
    orderStateMachine.changeStatus(orders.getUserId(), 
                                   String.valueOf(orders.getId()), 
                                   OrderStatusChangeEventEnum.PAYED, 
                                   orderSnapshotDTO);
}

3.1.6. 测试

测试流程:

下单、支付、取消订单

预期结果:

  • 下单成功:根据用户id分库,写入jzo2o-orders-x的其中一个数据库,根据订单号分表,写入orders_x其中一个订单表。
  • 状态机:根据用户id分库,写入jzo2o-orders-x的其中一个数据库,根据biz_id字段分表,写入biz_snapshot_x中一个状态表机。state_persister为广播表不进行分表在三个数据库数据一致。
  • 支付及取消订单业务操作正常。
  • orders_canceled表为广播表不进行分表在三个数据库数据一致。


相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
1月前
|
关系型数据库 MySQL 数据库
【MySQL实战笔记】 06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?-01
【4月更文挑战第17天】MySQL的锁分为全局锁、表级锁和行锁。全局锁用于全库备份,可能导致业务暂停或主从延迟。不加锁备份会导致逻辑不一致。推荐使用`FTWRL`而非`readonly=true`因后者可能影响其他逻辑且异常处理不同。表级锁如`lock tables`限制读写并限定操作对象,常用于并发控制。元数据锁(MDL)在访问表时自动加锁,确保读写正确性。
82 31
|
1月前
|
关系型数据库 MySQL
【MySQL实战笔记】07 | 行锁功过:怎么减少行锁对性能的影响?-01
【4月更文挑战第18天】MySQL的InnoDB引擎支持行锁,而MyISAM只支持表锁。行锁在事务开始时添加,事务结束时释放,遵循两阶段锁协议。为减少锁冲突影响并发,应将可能导致最大冲突的锁操作放在事务最后。例如,在电影票交易中,应将更新影院账户余额的操作安排在事务末尾,以缩短锁住关键行的时间,提高系统并发性能。
25 4
|
30天前
|
NoSQL 关系型数据库 MySQL
实时计算 Flink版操作报错之同步MySQL分库分表500张表报连接超时,是什么原因
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
1月前
|
SQL 存储 关系型数据库
Mysql优化提高笔记整理,来自于一位鹅厂大佬的笔记,阿里P7亲自教你
Mysql优化提高笔记整理,来自于一位鹅厂大佬的笔记,阿里P7亲自教你
|
1月前
|
存储 SQL 关系型数据库
MySQL万字超详细笔记❗❗❗
MySQL万字超详细笔记❗❗❗
92 1
MySQL万字超详细笔记❗❗❗
|
9天前
|
算法 Java 数据库连接
【分库分表】基于mysql+shardingSphere的分库分表技术
【分库分表】基于mysql+shardingSphere的分库分表技术
16 0
|
1月前
|
SQL 关系型数据库 MySQL
【MySQL系列笔记】隔离锁
MySQL 中的锁分为三种粒度:全局锁、表级锁和行级锁。全局锁用于锁定整个数据库,例如在进行全库逻辑备份时使用。表级锁分为表锁,元数据锁和意向锁;表锁又分为读锁(共享锁)和写锁(排他锁),用于锁定单表数据。元数据锁(MDL)在 DML 操作时自动加锁,确保事务的正确性;意向锁为了避免DML在执行时,加的行锁与表锁的冲突。行级锁分为记录锁、间隙锁和临键锁。记录锁锁定单行记录,间隙锁锁定索引记录之间的间隙,防止事务间隙插入,临键锁结合了记录锁和间隙锁,防止幻读并锁定数据和前一个间隙。
219 5
【MySQL系列笔记】隔离锁
|
1月前
|
存储 SQL 关系型数据库
【MySQL系列笔记】索引
MySQL的索引是一种数据结构,用于加快数据库查询操作的速度。它们通过在表中的一个或多个列上创建索引,可以快速定位到符合特定条件的行,从而提高查询效率。MySQL支持多种类型的索引,包括B树索引、哈希索引和全文索引。B树索引是最常用的索引类型,能够高效地支持范围查询和排序操作。创建和使用索引需要根据具体的查询需求和数据特点进行优化,避免过度索引或不正确索引导致的性能下降。此外,索引的维护也需要考虑到对数据库性能和存储空间的影响。因此,合理地创建、管理和使用索引是提高MySQL数据库性能的重要手段。
241 2
【MySQL系列笔记】索引
|
1月前
|
SQL 关系型数据库 MySQL
【MySQL系列笔记】MySQL总结
MySQL 是一种关系型数据库,说到关系,那么就离不开表与表之间的关系,而最能体现这种关系的其实就是我们接下来需要介绍的主角 SQL,SQL 的全称是 Structure Query Language ,结构化的查询语言,它是一种针对表关联关系所设计的一门语言,也就是说,学好 MySQL,SQL 是基础和重中之重。SQL 不只是 MySQL 中特有的一门语言,大多数关系型数据库都支持这门语言。
257 8
|
1月前
|
SQL 关系型数据库 MySQL
【MySQL系列笔记】常用SQL
常用SQL分为三种类型,分别为DDL,DML和DQL;这三种类型的SQL语句分别用于管理数据库结构、操作数据、以及查询数据,是数据库操作中最常用的语句类型。 在后面学习的多表联查中,SQL是分析业务后业务后能否实现的基础,以及后面如何书写动态SQL,以及完成级联查询的关键。
221 6

相关产品

  • 云数据库 RDS MySQL 版