一、项目背景与目标

大型电商或教务系统订单量持续增长，单库单表面临性能瓶颈。系统要求：

• 分库分表：用户维度水平分库，订单按时间动态分表

• 读写分离：提高读性能和系统可用性

• 分布式事务：保证跨库业务强一致性

• 多表关联：订单与订单详情联合查询

• 动态分表：按月自动创建新表并动态路由

• SQL性能调优：索引设计、分页优化、热点数据治理

---

二、整体架构设计

客户端 --> Spring Boot 应用（集成ShardingSphere-JDBC）
                     |
      ---------------------------------------
      |                                     |
读写分离读库（MySQL主从架构）       分库分表写库（多个MySQL实例）
                     |
      分布式事务（Seata）

---

三、关键技术栈

技术	作用
Spring Boot	快速搭建应用
ShardingSphere-JDBC	分库分表与读写分离中间件
MyBatis-Plus	ORM框架，简化数据库操作
Seata	分布式事务解决方案
MySQL 主从复制	读写分离数据库架构
HikariCP	连接池优化

---

四、数据库设计

1. 库表结构

• 分库：order_db_0, order_db_1，通过 user_id % 2 路由

• 分表：每库内按月分表，表名形如 order_202507

• 订单详情表 order_item 不分表，存放订单商品明细

-- 订单表示例（分表）
CREATE TABLE `order_202507` (
  `order_id` BIGINT NOT NULL,
  `user_id` BIGINT NOT NULL,
  `status` VARCHAR(20),
  `amount` DECIMAL(10,2),
  `create_time` DATETIME,
  PRIMARY KEY (`order_id`),
  KEY idx_user_time (`user_id`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 订单详情表
CREATE TABLE `order_item` (
  `item_id` BIGINT PRIMARY KEY AUTO_INCREMENT,
  `order_id` BIGINT,
  `product_id` BIGINT,
  `quantity` INT,
  `price` DECIMAL(10,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

---

五、核心代码示范

1. 自定义分片算法实现（动态按月分表）

@Slf4j
public class DynamicMonthTableShardingAlgorithm implements PreciseShardingAlgorithm<Date> {

    @Override
    public String doSharding(Collection<String> availableTables, PreciseShardingValue<Date> shardingValue) {
        Date date = shardingValue.getValue();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
        String suffix = sdf.format(date);
        // 动态匹配或抛异常
        for (String tableName : availableTables) {
            if (tableName.endsWith(suffix)) {
                return tableName;
            }
        }
        // 如果没有对应表，抛异常或者动态创建表（需要额外逻辑支持）
        throw new UnsupportedOperationException("没有对应的分表: " + suffix);
    }
}

---

2. ShardingSphere配置片段（application.yml）

spring:
  shardingsphere:
    datasource:
      names: order_db_0, order_db_1
      order_db_0:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://localhost:3306/order_db_0
        username: root
        password: 123456
      order_db_1:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://localhost:3306/order_db_1
        username: root
        password: 123456

    rules:
      sharding:
        tables:
          order:
            actual-data-nodes: order_db_$->{0..1}.order_$->{202501..202507}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: db-inline
            table-strategy:
              standard:
                sharding-column: create_time
                sharding-algorithm-name: dynamic-month-algorithm
          order_item:
            actual-data-nodes: order_db_0.order_item

        sharding-algorithms:
          db-inline:
            type: INLINE
            props:
              algorithm-expression: order_db_${user_id % 2}
          dynamic-month-algorithm:
            type: CLASS_BASED
            props:
              algorithmClassName: com.example.sharding.DynamicMonthTableShardingAlgorithm

    readwrite-splitting:
      data-sources:
        order_ds:
          primary-data-source-name: order_db_0
          replica-data-source-names: order_db_0_slave, order_db_1_slave

    props:
      sql-show: true
      executor-size: 10

---

3. 分布式事务示例（Seata配置与使用）

• 添加依赖：

<dependency>
  <groupId>io.seata</groupId>
  <artifactId>seata-spring-boot-starter</artifactId>
  <version>1.5.2</version>
</dependency>

• 启用分布式事务

@Service
@Transactional
@GlobalTransactional(name = "order-create-transaction", rollbackFor = Exception.class)
public class OrderService {

    @Autowired
    private OrderMapper orderMapper;

    @Autowired
    private OrderItemMapper orderItemMapper;

    public void createOrder(Order order, List<OrderItem> items) {
        orderMapper.insert(order);
        for (OrderItem item : items) {
            item.setOrderId(order.getOrderId());
            orderItemMapper.insert(item);
        }
    }
}

• 配置seata-server，保证跨库事务ACID。

---

4. 复杂SQL示例（多表联合分页查询）

@Select("SELECT o.*, oi.product_id, oi.quantity, oi.price " +
        "FROM order_${month} o " +
        "LEFT JOIN order_item oi ON o.order_id = oi.order_id " +
        "WHERE o.user_id = #{userId} " +
        "ORDER BY o.create_time DESC " +
        "LIMIT #{offset}, #{pageSize}")
List<OrderWithItems> selectOrdersWithItems(@Param("userId") Long userId,
                                          @Param("month") String month,
                                          @Param("offset") int offset,
                                          @Param("pageSize") int pageSize);

---

六、性能调优与注意事项

• 分片键选取要均匀分布，避免单库热点

• SQL必须携带分片键，否则全库扫描

• 动态建表和路由需要额外管理表生命周期（例如按月定时建新表）

• 读写分离配置合理，避免主库压力过大

• 索引设计：重点索引分片键、查询字段及关联字段

• 慢查询分析配合Explain进行SQL优化

---

七、常见踩坑总结

问题	解决方案
SQL无分片键导致全库扫描	必须WHERE条件包含分片键
分片算法返回空，找不到目标表	确认actual-data-nodes和算法逻辑对应
读写分离主从延迟导致数据不一致	设置合理的同步延迟，读写事务使用主库
分布式事务异常	Seata配置检查，确保TC Server可用
动态新表未创建导致路由失败	需实现自动建表及刷新路由策略逻辑

---

八、总结

• ShardingSphere-JDBC强大灵活，结合Spring Boot快速构建高性能分库分表系统

• 结合读写分离和分布式事务，实现高可用、高一致性的复杂业务需求

• 实践中要重视分片键设计、动态路由管理和SQL优化，保障系统稳定与性能