MySQL分库分表实战：基于ShardingSphere的水平拆分与跨片查询方案

分库分表场景分析与ShardingSphere架构选型

单表数据量超过5000万行后，MySQL查询性能显著下降，B+树层级增加导致IO放大，写入延迟从毫秒级升至百毫秒级。分库分表是解决单表瓶颈的主流方案，Apache ShardingSphere是当前生态最成熟的中间件，支持Proxy和JDBC两种部署模式。

两种模式对比：Proxy模式独立部署代理服务，应用无侵入但多一跳网络延迟；JDBC模式以JAR包嵌入应用，零网络开销但需修改数据源配置。对延迟敏感的在线交易系统选JDBC模式，对多语言异构系统选Proxy模式。

本文以JDBC模式为例，Maven依赖：

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc-core</artifactId>
    <version>5.5.0</version>
</dependency>

水平分表规则配置与分片算法设计

以订单表为例，按user_id取模分4库8表：

# application-sharding.yml
rules:
  - SHARDING:
      tables:
        t_order:
          actualDataNodes: ds_${0..3}.t_order_${0..7}
          databaseStrategy:
            standard:
              shardingColumn: user_id
              shardingAlgorithmName: order_db_mod
          tableStrategy:
            standard:
              shardingColumn: order_id
              shardingAlgorithmName: order_table_mod
          keyGenerateStrategy:
            column: order_id
            keyGeneratorName: snowflake
      shardingAlgorithms:
        order_db_mod:
          type: MOD
          props:
            sharding-count: 4
        order_table_mod:
          type: MOD
          props:
            sharding-count: 8
      keyGenerators:
        snowflake:
          type: SNOWFLAKE
          props:
            worker-id: 1

分片策略核心逻辑：database_index = user_id % 4，table_index = order_id % 8。分库键和分表键分开设计是为了支持两种查询路径——按用户维度查订单走分库路由，按订单号查详情走分表路由。雪花算法生成分布式主键，避免自增ID跨片冲突。

跨分片查询与结果归并处理

分库分表后最大的挑战是跨片查询。按用户维度查询命中单库单表，但按时间范围或状态筛选会触发全片扫描。ShardingSphere处理流程：将SQL广播到所有分片并行执行，在Proxy层做结果归并。

-- 原始SQL
SELECT * FROM t_order WHERE status = 'PAID' ORDER BY create_time DESC LIMIT 20;

-- ShardingSphere实际执行（4库8表=32个分片）
-- 对每个分片执行: SELECT * FROM t_order_? WHERE status = 'PAID' 
--   ORDER BY create_time DESC LIMIT 20
-- 归并: 32个结果集做归并排序，取前20条

归并排序的内存消耗与分片数乘以LIMIT成正比。32分片乘以20条等于640条数据驻留内存，性能可接受。但LIMIT设为10000时，32乘以10000等于32万条数据归并，内存和延迟都不乐观。优化策略：

-- 添加分片键条件，缩小扫描范围
SELECT * FROM t_order WHERE user_id = 12345 
  AND status = 'PAID' ORDER BY create_time DESC LIMIT 20;
-- 仅路由到ds_1库，单库8表扫描，归并数据量降至160条

分布式事务方案与一致性保障

跨库写操作需要分布式事务保障。ShardingSphere支持三种事务类型：

1. LOCAL事务：每个分片独立提交，不保证跨库一致性。适合分片键相同的写操作（单库事务）。

2. XA事务：两阶段提交，强一致性但性能差，TPS约为LOCAL的1/3。

3. BASE事务：Saga/TCC模式，最终一致性，性能介于LOCAL和XA之间。

# 事务配置
props:
  transaction:
    defaultType: BASE
    managerType: SEGMENT  # 基于Sega的segment模式

# 代码中使用
@ShardingTransactionType(ShardingTransactionType.BASE)
@Transactional
public void createOrderWithItems(Order order, List<OrderItem> items) {
    orderMapper.insert(order);        // 可能落在ds_0
    orderItemMapper.batchInsert(items); // 可能落在ds_2
}

BASE事务下，如果ds_2写入失败，ds_0的订单记录已提交，通过补偿SQL回滚。业务代码需实现补偿接口：

public interface Compensable {
    String getCompensateSQL();
}

SQL查询优化与分库分表避坑指南

分库分表后SQL写法直接影响性能，常见陷阱：

• 不带分片键的查询：全片广播扫描，32分片场景延迟从5ms飙升到200ms以上
• GROUP BY跨分片聚合：每个分片先做局部聚合，再在归并层做全局聚合，结果正确但无法利用索引
• JOIN跨分片关联：ShardingSphere支持跨库绑定表JOIN，但性能差，设计时应将关联数据分到同一库
• DISTINCT去重：归并层无法直接去重，改为在应用层聚合或使用ES辅助查询

监控指标：ShardingSphere暴露的parse_route_time（路由耗时）、execute_time（执行耗时）、merge_time（归并耗时）三项之和构成总延迟。归并耗时占比超过50%说明跨片查询过多，需优化分片键或添加查询维度表。