MySQL 分库分表实战：ShardingSphere 5.x 路由配置与跨片查询优化方案

什么场景需要分库分表

单表数据量超过 5000 万行，或单库 QPS 超过 5000，常规的索引优化和读写分离已经无法满足性能需求。分库分表是解决单库单表容量瓶颈的最终手段，但代价不低——引入分片键、跨片查询、分布式事务等一系列复杂问题。评估是否真的需要分片，比分片方案本身更重要。

典型需要分片的信号：

• 单表数据文件超过 50GB，DDL 变更需要数小时
• 慢查询日志中全表扫描频率上升，即使走了索引性能也持续退化
• 主库写入压力导致复制延迟超过业务容忍阈值
• 数据库连接池耗尽，扩容从库无法缓解写入瓶颈

ShardingSphere 5.x 分片规则配置

ShardingSphere-JDBC 以 JAR 包形式嵌入应用，无需独立部署 Proxy，对运维侵入最小。以订单表为例，按 user_id 取模分 4 库 8 表：

# application.yml
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,ds2,ds3
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://db0:3306/order_db
        username: root
        password: <password>
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://db1:3306/order_db
        username: root
        password: <password>
      ds2:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://db2:3306/order_db
        username: root
        password: <password>
      ds3:
        type: com.zaxxer.hikari.HikariDataSource
        jdbc-url: jdbc:mysql://db3:3306/order_db
        username: root
        password: <password>

    rules:
      sharding:
        tables:
          t_order:
            actual-data-nodes: ds$->{0..3}.t_order_$->{0..1}
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-db-mod
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-tbl-mod
        sharding-algorithms:
          order-db-mod:
            type: MOD
            props:
              sharding-count: 4
          order-tbl-mod:
            type: MOD
            props:
              sharding-count: 2
        key-generators:
          snowflake:
            type: SNOWFLAKE
            props:
              worker-id: 1

分片键选择是架构层面最重要的决策。订单表用 user_id 分片是常见选择，保证同一用户的订单落在同一物理表，避免跨片查询。但运营后台需要按时间范围查全部订单，这时就需要特殊处理。

分片键选择的核心原则

分片键决定了数据分布的均匀性和查询的路由效率。选择原则：

1. 高基数字段优先

低基数字段（如状态字段 status 只有 3 个值）做分片键会导致数据倾斜。user_id、order_id 等唯一或高基数字段才能保证数据均匀分布。

2. 查询覆盖度

80% 的查询条件包含哪个字段，就用哪个字段做分片键。分片键不在查询条件中时，ShardingSphere 只能全片广播查询，性能退化为分片数倍的全表扫描。

3. 避免后续变更

分片键一旦确定，数据迁移成本极高。需要确认业务模型稳定后再做分片。如果一个字段未来可能变（如用户迁移到不同分区），不要用它做分片键。

跨片查询优化方案

跨片查询是分库分表最大的性能杀手。以”查询最近7天的全部订单”为例，如果不带分片键，ShardingSphere 会将 SQL 广播到所有分片执行，结果合并排序后返回。分 4 库 8 表，一次查询变成 8 次查询加内存归并。

方案1：冗余索引表

-- 创建索引表，按时间维度分片
CREATE TABLE t_order_idx_time (
  order_id BIGINT PRIMARY KEY,
  user_id BIGINT,
  created_at DATETIME,
  shard_key INT  -- 记录原分片位置
) SHARD BY created_at;

-- 查询流程：先查索引表定位分片，再精准路由
SELECT shard_key FROM t_order_idx_time
WHERE created_at BETWEEN '2026-07-22' AND '2026-07-29';

-- 再到目标分片拉取完整数据
SELECT * FROM t_order WHERE user_id = ? AND order_id IN (...);

索引表的数据量远小于主表（只存关键字段），写入时同步维护即可。查询时先通过索引表窄化范围，再精准路由，将 8 次查询降到 1-2 次。

方案2：异构索引 + Elasticsearch

将订单关键字段同步到 ES，复杂查询走 ES 拿到 order_id 列表，再回查 MySQL 拿完整数据：

// 查询流程
// 1. ES 搜索获取 order_id 列表
SearchResponse response = client.prepareSearch("order_index")
    .setQuery(QueryBuilders.rangeQuery("created_at")
        .gte("2026-07-22").lte("2026-07-29"))
    .addSort("created_at", SortOrder.DESC)
    .setSize(100)
    .execute()
    .actionGet();

// 2. 从 hits 中提取 order_id，通过 Hint 路由到分片
List<String> orderIds = extractOrderIds(response);
String hintSql = "/* SHARDSPHERE HINT: order_id IN (" +
    String.join(",", orderIds) + ") */ " +
    "SELECT * FROM t_order WHERE order_id IN (" +
    String.join(",", orderIds) + ")";

方案3：广播表

维度表（如地区表、品类表）数据量小但查询频繁，每个分片都存一份完整副本：

rules:
  sharding:
    broadcast-tables:
      - t_region
      - t_category

广播表写入时同步到所有分片，读取时从本地分片获取，避免跨片 JOIN。

分布式主键生成

分片后自增 ID 不再全局唯一。ShardingSphere 内置雪花算法生成分布式 ID：

rules:
  sharding:
    tables:
      t_order:
        key-generate-strategy:
          column: order_id
          key-generator-name: snowflake
    key-generators:
      snowflake:
        type: SNOWFLAKE
        props:
          worker-id: 1    # 每个实例分配唯一 worker-id
          max-vibration-offset: 1
          max-tolerate-time-difference-milliseconds: 10

雪花算法的 worker-id 必须全局唯一。K8s 部署时可用 Pod ordinal 作为 worker-id，或通过 ZooKeeper 自动分配。

数据迁移与扩容

分片扩容（4 库扩到 8 库）是最痛苦的操作。常用方案：

1. 双写 + 历史数据迁移

新写入同时写新旧分片，历史数据用脚本按新规则迁移。验证数据一致后切流量到新分片。

2. 成倍扩容法

4 库扩 8 库时，每个旧库拆成 2 个新库。由于是取模扩容，只需将旧库中一半数据迁移到新库，旧库数据不需要搬迁。具体操作：

# 旧分片规则：user_id % 4
# 新分片规则：user_id % 8
# 映射关系：
# ds0 (0) -> ds0 (0) + ds4 (4)
# ds1 (1) -> ds1 (1) + ds5 (5)
# ds2 (2) -> ds2 (2) + ds6 (6)
# ds3 (3) -> ds3 (3) + ds7 (7)

# 从旧 ds0 迁移 user_id % 8 = 4 的数据到新 ds4
INSERT INTO ds4.t_order SELECT * FROM ds0.t_order WHERE user_id % 8 = 4;
DELETE FROM ds0.t_order WHERE user_id % 8 = 4;

扩容窗口期用 ShardingSphere 的读写分离功能，写入新分片，读取走旧分片，数据迁移完成后一刀切换。

监控与运维

ShardingSphere 提供了 Prometheus metrics 端点，接入监控：

# application.yml 开启监控
spring:
  shardingsphere:
    props:
      proxy-middleware-enabled: true
      check-table-metadata-enabled: false

# 关键指标
# shardingsphere_proxy_request_total       请求总数
# shardingsphere_proxy_request_latency     请求延迟
# shardingsphere_proxy_current_connections 当前连接数
# shardingsphere_proxy_transaction_total   事务数

分库分表后的运维复杂度成倍上升，需要在架构评审时充分评估。如果读写分离和缓存层就能解决问题，不要急于引入分片。分片是最后的武器，一旦引入就没有回头路。