MySQL分库分表实战：ShardingSphere在物流订单系统中的落地方案

关键词：MySQL、ShardingSphere、分库分表、物流订单、Snowflake、分布式事务、Spring Boot

目录

1. 业务背景与挑战
2. 技术选型与整体架构
3. 数据建模与分片策略
4. 环境准备与建表脚本
5. Spring Boot + ShardingSphere-JDBC 集成
6. 分布式主键 & 雪花算法落地
7. 复杂业务 SQL 改写与测试
8. 分布式事务 —— Seata 方案
9. 运维：弹性扩缩容 & 灰度方案
10. 性能压测与最佳实践总结

1. 业务背景与挑战

某头部物流平台日均订单 800W，峰值 2.2KW。单库单表（t_order）在 6 亿数据时：

• 写入 RT P99 > 400 ms（索引维护、锁竞争）。
• 大促期间磁盘 I/O 100%，MySQL QPS 天花板 6K。
• DDL 无法在线执行（GH-OST 时长 > 18 h）。

=> 必须水平拆分；业务特点：

• 查询维度：订单号、用户 ID、运单号。
• 生命周期：订单完结 30 天后迁移冷存，热数据占比 < 20%。

2. 技术选型与整体架构

架构图：

┌──────────────┐      ┌──────────────┐
│  Spring Boot │──────│Sharding-JDBC │
└──────┬───────┘      └──────┬───────┘
       │ 8*主库            │
       │ 8*从库            │
 ┌─────┴─────┐       ┌─────┴─────┐
 │ ds0 ~ ds7 │       │ ck 冷存   │
 └───────────┘       └───────────┘

3. 数据建模与分片策略

3.1 逻辑表结构

CREATE TABLE t_order (
  order_id     BIGINT PRIMARY KEY,
  user_id      BIGINT NOT NULL,
  waybill_no   VARCHAR(32) NOT NULL,
  status       TINYINT NOT NULL,
  created_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_user (user_id),
  INDEX idx_waybill (waybill_no)
) ENGINE=InnoDB;

3.2 分片规则

• 分库键：user_id % 8 → ds${0..7}
• 分表键：order_id & 31 → t_order_${0..31}
• 绑定表：t_order_item、t_order_track 与 t_order 使用相同的 order_id 路由，避免跨分片 JOIN。

4. 环境准备与建表脚本

4.1 一键建库建表（Shell）

for i in {0..7}; do
  mysql -h127.0.0.1 -P3306 -uroot -p123456 -e "
    CREATE DATABASE IF NOT EXISTS logistics_${i};
    USE logistics_${i};
    $(cat create_table.sql | sed 's/t_order/t_order_&/g')
  "
done

create_table.sql 里仅包含 t_order 建表语句，通过 sed 替换生成 32 张分表。

5. Spring Boot + ShardingSphere-JDBC 集成

5.1 Maven 依赖

<dependency>
  <groupId>org.apache.shardingsphere</groupId>
  <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
  <version>5.4.1</version>
</dependency>

5.2 application.yml

spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,...,ds7
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        jdbcUrl: jdbc:mysql://127.0.0.1:3306/logistics_0?useSSL=false
        username: root
        password: 123456
      # ... ds1 ~ ds7 同上
    rules:
      sharding:
        tables:
          t_order:
            actual-data-nodes: ds$->{0..7}.t_order_$->{0..31}
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order-inline
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: user-inline
        binding-tables: t_order,t_order_item,t_order_track
        sharding-algorithms:
          order-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_$->{order_id & 31}
          user-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{user_id % 8}
    props:
      sql-show: true

5.3 读写分离（可选）

spring:
  shardingsphere:
    rules:
      readwrite-splitting:
        data-sources:
          ds0-rw:
            type: Static
            props:
              write-data-source-name: ds0
              read-data-source-names: ds0-slave0,ds0-slave1

6. 分布式主键 & 雪花算法落地

ShardingSphere 已内置 Snowflake，无需额外配置：

@TableId(value = "order_id", type = IdType.ASSIGN_ID) // MyBatis-Plus
private Long orderId;

注意：Snowflake 依赖 worker-id，多实例部署需通过 spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=${HOST_NAME_HASH} 防止重复。

7. 复杂业务 SQL 改写与测试

7.1 分页查询用户最近 30 天订单

Page<OrderDO> page = new Page<>(1, 10);
QueryWrapper<OrderDO> qw = new QueryWrapper<>();
qw.eq("user_id", 123456L)
  .ge("created_time", LocalDateTime.now().minusDays(30))
  .orderByDesc("created_time");

IPage<OrderDO> result = orderMapper.selectPage(page, qw);

• 路由结果：user_id % 8 = 4，只扫 ds4 的 32 张分表，避免全库广播。
• 分页优化：使用 分片键 + 时间索引 覆盖查询，避免 filesort。

7.2 运单号反查订单

运单号非分片键 → 需 基因法 改造：

-- 新增冗余字段 waybill_hash = CRC32(waybill_no) & 31
SELECT * FROM t_order_${waybill_hash} WHERE waybill_no = 'SF123456789';

保证单表定位，99.9% 查询 < 5 ms。

8. 分布式事务 —— Seata 方案

8.1 引入 Seata

<dependency>
  <groupId>com.alibaba.cloud</groupId>
  <artifactId>spring-cloud-starter-alibaba-seata</artifactId>
</dependency>

8.2 全局事务示例

@Service
public class OrderService {

    @GlobalTransactional(name = "create-order", rollbackFor = Exception.class)
    public Long createOrder(CreateOrderDTO dto) {
        // 1. 插入 t_order
        orderMapper.insert(order);
        // 2. 扣减库存 (RPC -> stock-service)
        stockFeign.deduct(dto.getSkuId(), dto.getNum());
        return order.getOrderId();
    }
}

Seata AT 模式对业务零侵入，undo log 存储在分库中，需在每个库建 undo_log 表。

9. 运维：弹性扩缩容 & 灰度方案

10. 性能压测与最佳实践总结

• 压测结果（16C32G * 8 库）：
  • 写入 QPS 8.7W，P99 延迟 18 ms。
  • 查询 QPS 12W，P99 延迟 9 ms。
• 经验清单
  1. 永远带分片键查询，否则走全库广播。
  2. 避免热点账户（user_id 连续），可在业务层加随机扰动。
  3. 定期清理过期分区，减少索引树高度。
  4. 监控 information_schema.INNODB_METRICS，及时发现行锁等待。