1. 概述

1.1. 原因

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

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

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

📎阿里巴巴规范手册（嵩山版）.pdf

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

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

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

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. 拆分原则

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

2.2. 垂直分库

2.2.1. 定义

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

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

垂直分表但没有垂直分库

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

2.2.2. 特点

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

2.3. 水平分表&水平分库

2.3.1. 定义

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

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

2.3.2. 拆分规则

Hash取模

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

优点：数据均匀。

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

rang方式

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

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

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

地域/时间方式

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

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

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表为广播表不进行分表在三个数据库数据一致。

【MySQL系列笔记】分库分表