MySQL分库分表落地实战：从ShardingSphere配置到跨片查询优化全流程

MySQL分库分表何时从可选项变成必选项

单表数据量超过5000万行或单库写入TPS超过3000时，MySQL的B+Tree索引性能开始显著衰减——即使索引设计合理，查询延迟也会从毫秒级退化到百毫秒级。这个阈值不是绝对值，与硬件配置和查询模式强相关，但作为架构决策的参考基线足够有效。当应用层优化（索引、缓存、读写分离）已经无法满足延迟和吞吐要求时，分库分表就从可选项变成必选项。

分库分表的核心挑战不是数据拆分本身，而是拆分后带来的分布式事务、跨片查询、数据迁移和扩容问题。本文以ShardingSphere-JDBC 5.x为例，覆盖从分片策略设计到跨片查询优化的完整落地流程。

分片策略设计：分片键选择与数据均匀性验证

分片键是整个分库分表方案的基础，选择错误的分片键比不分片更糟糕。分片键的选择标准：高频查询条件覆盖、数据分布均匀、写入热点分散。

以订单系统为例，user_id是订单表最合理的分片键——绝大部分查询都带user_id条件，且用户间订单量分布相对均匀。反之，如果用order_date做分片键，虽然写入均匀但按用户查询会扫全部分片。

-- 分片前的数据均匀性验证
SELECT 
    MOD(user_id, 4) AS shard_idx,
    COUNT(*) AS row_count,
    COUNT(*) * 100.0 / (SELECT COUNT(*) FROM orders) AS pct
FROM orders
GROUP BY MOD(user_id, 4)
ORDER BY shard_idx;

-- 期望：每个分区的pct在20%-30%之间
-- 如果某个分区超过40%，说明分布不均

ShardingSphere的分片配置：

# application-sharding.yml
dataSources:
  ds_0:
    url: jdbc:mysql://mysql-0:3306/order_db_0
    username: root
    password: ${DB_PASSWORD}
  ds_1:
    url: jdbc:mysql://mysql-1:3306/order_db_1
    username: root
    password: ${DB_PASSWORD}

rules:
  - !SHARDING
    tables:
      orders:
        actualDataNodes: ds_${0..1}.orders_${0..3}
        tableStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: orders_mod
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: orders_db_mod
        keyGenerateStrategy:
            column: id
            keyGeneratorName: snowflake
    
    shardingAlgorithms:
      orders_mod:
        type: MOD
        props:
          sharding-count: 4
      orders_db_mod:
        type: MOD
        props:
          sharding-count: 2
    
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
        props:
          worker-id: 1

这套配置将orders表拆分为2个库x4张表=8个物理表。user_id % 2决定落在哪个库，user_id % 4决定落在库内哪张表。

跨片查询优化：从全分片扫描到精准路由

分片后最大的性能陷阱是查询条件不含分片键，导致全分片扫描。一条原本1毫秒的查询可能变成8毫秒（扫描8个分片）。优化策略按优先级排列：

策略1：确保高频查询携带分片键——成本最低效果最好的方案。对于”按订单号查订单”这类查询，在生成订单号时将分片信息编码进订单号：

// 生成带分片路由信息的订单号
// 格式：年份(2位) + 分片序号(2位) + 雪花ID(16位)
public String generateOrderId(long userId, long snowflakeId) {
    int shardIdx = (int) (userId % 4);
    return String.format("%02d%02d%016d", 
        Year.now().getValue() % 100, shardIdx, snowflakeId);
}

// 从订单号提取分片信息
public int extractShardIdx(String orderId) {
    return Integer.parseInt(orderId.substring(2, 4));
}

在ShardingSphere中配置HintManager强制路由：

public Order findById(String orderId) {
    int shardIdx = extractShardIdx(orderId);
    try (HintManager hintManager = HintManager.getInstance()) {
        hintManager.addTableShardingValue("orders", shardIdx);
        return orderMapper.selectById(orderId);
    }
}

策略2：建立宽表索引（冗余表）——对于无法携带分片键的查询场景，建立一张按查询维度分片的冗余表。例如需要按merchant_id查订单，但主表按user_id分片，则创建merchant_order_index表按merchant_id分片，只存储查询必需的字段。

rules:
  - !SHARDING
    tables:
      merchant_order_index:
        actualDataNodes: ds_${0..1}.merchant_order_idx_${0..3}
        tableStrategy:
          standard:
            shardingColumn: merchant_id
            shardingAlgorithmName: merchant_mod

冗余表通过Binlog同步或应用双写维护一致性。Binlog同步对业务侵入小，推荐使用Canal或Debezium监听主表变更，异步写入冗余表。

策略3：查询结果归并优化——对于必须跨分片的排序和分页查询，用游标分页替代偏移分页：

-- 传统偏移分页（跨分片性能差）
SELECT * FROM orders WHERE user_id = ? 
ORDER BY create_time DESC LIMIT 10000, 20;

-- 游标分页（基于上一页最后一条记录）
SELECT * FROM orders 
WHERE user_id = ? AND create_time < '上一页最后一条的时间'
ORDER BY create_time DESC LIMIT 20;

分布式事务方案选择与性能权衡

分库分表后，跨库事务从本地事务升级为分布式事务。ShardingSphere支持三种分布式事务模式：

LOCAL模式：不支持跨库事务，各库独立提交。性能最高但一致性最弱，适用于对一致性要求不高的场景。

XA模式：两阶段提交，强一致性。性能约为LOCAL模式的40%-60%，适用于资金操作等强一致性场景：

@Transactional
@ShardingTransactionType(TransactionType.XA)
public void transferOrder(TransferRequest request) {
    accountMapper.deduct(request.getFromUserId(), request.getAmount());
    orderMapper.create(request.toOrder());
}

BASE模式（Seata AT）：最终一致性，通过Undo Log实现补偿。性能约为XA模式的2倍，但在异常场景下需要人工介入补偿。

生产环境推荐默认使用BASE模式，仅在资金和库存操作中使用XA模式。事务模式在ShardingSphere中可以按方法粒度切换。

数据迁移与扩容方案

分库分表方案上线后，扩容（如从2库扩到4库）是绕不开的问题。推荐双写+Binlog校验的迁移方案：

1. 部署新分片集群（4库），配置双写路由
2. 启动全量数据同步：用DataX或自研脚本将旧分片数据迁移到新分片
3. 启动增量数据同步：用Canal监听旧集群Binlog，同步到新集群
4. 数据校验：对比新旧集群各分片的数据行数和校验和
5. 切换读流量到新集群，验证无误后切换写流量
6. 下线旧集群，清理双写代码

推荐使用CRC32校验和逐表比对，每10万行抽样一行做全字段比对，确保数据一致性达到99.99%以上。

分库分表不是银弹，它引入的复杂度需要在架构评审中充分评估。但当单表性能已经触及天花板时，系统化的分片策略加上完善的数据迁移方案，是实现数据库水平扩展最务实的路径。