MySQL分库分表实战：ShardingSphere水平拆分与跨库查询解决方案

什么时候需要分库分表

单表数据量超过5000万行或单库超过100GB后，MySQL的B+树索引层级加深，查询延迟显著增加。写入QPS超过单机上限时，主从复制延迟也会成为瓶颈。分库分表是最后的手段，不是第一个方案——在拆分前应先尝试索引优化、读写分离、缓存拦截。

确认需要分库分表的信号：慢查询日志中单表扫描行数持续增长、主库写入延迟超过50ms、单表ibd文件超过50GB、主从延迟经常超过10秒。

ShardingSphere-JDBC配置：水平分表策略

ShardingSphere-JDBC以JAR包形式嵌入应用，对业务代码零侵入。以下是一个订单表按用户ID取模分到4个库、每个库4张表的完整配置。

# application-sharding.yml
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,ds2,ds3
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://10.0.1.1:3306/order_db_0?useSSL=false
        username: root
        password: pwd0
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://10.0.1.2:3306/order_db_1?useSSL=false
        username: root
        password: pwd1

    rules:
      sharding:
        tables:
          t_order:
            actual-data-nodes: ds$->{0..3}.t_order_$->{0..3}
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-table-inline
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-db-inline
            key-generate-strategy:
              column: order_id
              key-generator-name: snowflake

        sharding-algorithms:
          order-db-inline:
            type: INLINE
            props:
              algorithm-expression: ds$->{(user_id % 16).intdiv(4)}
          order-table-inline:
            type: INLINE
            props:
              algorithm-expression: t_order_$->{user_id % 4}

        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1

分片策略的关键设计：用户ID对16取模确定库（4个库），再对4取模确定表（每个库4张表）。取模因子选择业务最高频的查询维度。

跨库查询难题与应对方案

分库分表后最大的挑战是非分片键查询。按user_id分表后，按order_id查询需要扫描所有库所有表。

// 方案1：广播路由（全表扫描，性能最差但最简单）
// ShardingSphere对不带分片键的查询自动走广播路由
// SELECT * FROM t_order WHERE order_id = 123456
// 会被改写为4个库 * 4张表 = 16次查询

// 方案2：建立索引表（异步写，最终一致）
@Mapper
public interface OrderIndexMapper {
    @Insert("INSERT INTO order_index(order_id, user_id) VALUES(#{orderId}, #{userId})")
    void insertIndex(@Param("orderId") Long orderId, @Param("userId") Long userId);

    @Select("SELECT user_id FROM order_index WHERE order_id = #{orderId}")
    Long getUserIdByOrderId(@Param("orderId") Long orderId);
}

@Service
public class OrderQueryService {
    public Order findByOrderId(Long orderId) {
        Long userId = orderIndexMapper.getUserIdByOrderId(orderId);
        if (userId == null) return null;
        return orderMapper.selectByOrderIdAndUserId(orderId, userId);
    }
}

// 方案3：基因法（分片键嵌入order_id）
public class OrderIdGenerator {
    private final SnowflakeIdWorker snowflake = new SnowflakeIdWorker(1, 0);

    public long generateId(long userId) {
        long snowflakeId = snowflake.nextId();
        int shardBits = (int) (userId % 16);
        return (snowflakeId & ~0xF) | shardBits;
    }

    public int extractShard(long orderId) {
        return (int) (orderId & 0xF);
    }
}

基因法的优势是零额外存储、零额外查询，劣势是order_id生成逻辑与分片策略耦合，未来调整分片规则需要重新设计ID方案。

分页查询的坑与解法

跨库分页是分库分表场景下的经典难题。LIMIT 10000, 10在单库是跳过前10000条取10条，但在分库环境下，每个库都要取出10010条数据，在应用层合并排序后再取10条。

// 优化方案1：禁止深分页，限制最大偏移量
// 配置 max-page-size: 5000

// 优化方案2：游标分页替代offset分页
// WHERE create_time > last_time ORDER BY create_time LIMIT 10
// 基于上次查询的最后一条记录继续查

// 优化方案3：使用ShardingSphere的流式归并
spring:
  shardingsphere:
    props:
      max-connections-size-per-query: 5
      kernel-executor-size: 16

数据迁移：从单库到分库的平滑切换

存量数据迁移到分库分表架构需要保证业务不停机。双写+增量同步是业界标准方案。

# 迁移步骤
# 1. 部署ShardingSphere代理层，指向单库（不分片）
# 2. 开启双写：应用同时写单库和分库
# 3. 历史数据全量迁移到分库
# 4. 增量数据通过binlog同步到分库
# 5. 验证数据一致性
# 6. 切换读流量到分库
# 7. 切换写流量到分库
# 8. 下线单库

# 数据一致性校验脚本
import pymysql

def verify_data_consistency():
    source_conn = pymysql.connect(host='10.0.1.0', db='order_db')
    total_diff = 0

    for db_idx in range(4):
        for tbl_idx in range(4):
            target_conn = pymysql.connect(
                host='10.0.1.' + str(db_idx),
                db='order_db_' + str(db_idx)
            )
            cur = target_conn.cursor()
            cur.execute("SELECT COUNT(*) FROM t_order_" + str(tbl_idx))
            shard_total = cur.fetchone()[0]

            src_cur = source_conn.cursor()
            src_cur.execute(
                "SELECT COUNT(*) FROM t_order "
                "WHERE user_id %% 16 = %s" % (db_idx * 4 + tbl_idx)
            )
            source_total = src_cur.fetchone()[0]

            if shard_total != source_total:
                print("不一致: ds%d.t_order_%d 分库=%d 单库=%d" %
                      (db_idx, tbl_idx, shard_total, source_total))
                total_diff += 1

    print("校验完成, 不一致分表数: %d" % total_diff)

verify_data_consistency()

分库分表没有回头路，一旦切换完成很难回退。迁移前务必做好灰度验证、数据备份和回滚预案。灰度期间保持双写，一旦发现问题可以立即切回单库。