MySQL分库分表

分片策略

根据业务特性选择水平分片或垂直分片，常见的分片键包括用户ID、时间等。

# ShardingSphere配置
spring:
  shardingsphere:
    datasource:
      names: ds0, ds1
      ds0:
        url: jdbc:mysql://db0:3306/order_db
      ds1:
        url: jdbc:mysql://db1:3306/order_db
    rules:
      sharding:
        tables:
          orders:
            actual-data-nodes: ds$->{0..1}.order_$->{0..3}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: user-mod
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order-mod
        sharding-algorithms:
          user-mod:
            type: MOD
            props:
              sharding-count: 2
          order-mod:
            type: MOD
            props:
              sharding-count: 4

分布式事务

采用Seata的AT模式或TCC模式保证跨库事务一致性。

@Service
public class OrderService {

    @GlobalTransactional
    public void createOrder(OrderDTO order) {
        // 跨库操作：扣减库存 + 创建订单 + 扣减余额
        stockService.deduct(order.getProductId(), order.getQuantity());
        orderMapper.insert(order);
        accountService.deduct(order.getUserId(), order.getTotalAmount());
    }
}

数据迁移

双写迁移方案保证迁移过程中数据不丢失、服务不中断。

@Component
public class DataMigration {

    @Transactional
    public void migrateBatch(int startId, int endId) {
        List<Record> records = sourceMapper.selectRange(startId, endId);
        for (Record record : records) {
            targetMapper.insert(record);
            sourceMapper.updateMigrated(record.getId());
        }
    }
}

读写分离

主库负责写操作，从库负责读操作，通过中间件实现透明化路由。

spring:
  shardingsphere:
    rules:
      readwrite-splitting:
        data-sources:
          ds0:
            write-data-source-name: master
            read-data-source-names: slave0,slave1
            load-balancer-name: round-robin
        load-balancers:
          round-robin:
            type: ROUND_ROBIN