MySQL 12 亿行单表分库分表实战:ShardingSphere + 基因法 + 不停机迁移

2024 年我们的订单库:MySQL 8.0 单库单表,t_order 表 12 亿行、单表 1.2TB,索引都进不了内存,写入 TPS 到 3000 就到顶,大查询动辄分钟级,DDL 加字段要停机几小时。投了一个半月做分库分表改造,用 ShardingSphere 拆成 16 库 × 64 表,写入 TPS 提升到 5w,查询 P99 从分钟级降到 50ms。本文复盘 MySQL 分库分表的完整实战,覆盖分片键选择、路由规则、全局 ID、平滑迁移、跨片查询、扩容。

问题背景

数据库:MySQL 8.0,单库
核心表:
- t_order:12 亿行,1.2TB
- t_order_item:36 亿行,3.5TB
- t_payment:12 亿行,800GB

瓶颈:
- 单表 12 亿行,二级索引 80GB,buffer pool 装不下
- 写入 TPS 3000 到顶(单机磁盘 IO + 锁竞争)
- count / 范围查询:分钟级
- ALTER TABLE 加字段:锁表 + 几小时(pt-osc 也要很久)
- 备份:全库备份 8 小时,恢复更久
- 主从延迟:大事务一来就飙到分钟级

业务约束:
- 不能停机(7×24 交易)
- 90% 查询带 user_id 或 order_no
- 10% 是运营后台的范围/聚合查询

目标:
- 写入 TPS 5w+
- 单表行数控制在 2000w 内
- 在线 DDL 秒级(单表小了)
- 平滑迁移,业务无感

方案 1:分片键选择

# 分片键(sharding key)是分库分表的灵魂,选错了整个方案废掉

候选分片键分析:
1. user_id(用户 ID)
   - 优:90% 查询带 user_id(查"我的订单")
   - 优:同用户数据在一个库,事务不跨片
   - 缺:大客户数据倾斜(头部商家订单特别多)

2. order_no(订单号)
   - 优:订单详情查询直接定位
   - 缺:查"用户的订单列表"要扫所有片

3. order_id(自增主键)
   - 缺:新数据全压在最后一个片,热点严重

决策:用 user_id 作分片键
- 大部分查询模式匹配
- 配合"基因法"让 order_no 也能直接路由

# 基因法:order_no 里嵌入 user_id 的分片基因
# order_no = 时间戳 + user_id 后 N 位 + 序列号
# 这样用 order_no 查询时,提取基因位 = user_id 的分片位
# 不带 user_id 也能精确路由,不用全片扫描

分片数量规划:
- 目标单表 2000w 行,12 亿 / 2000w = 60 张表 → 取 64(2 的幂)
- 16 个库 × 4 表 = 64 表
- 16 库分布在 4 个 MySQL 实例(每实例 4 库)
- 预留扩容:实例可拆,库不动

方案 2:ShardingSphere 配置

# ShardingSphere-JDBC 配置(sharding.yaml)
dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    jdbcUrl: jdbc:mysql://mysql-0:3306/order_db
    username: app
    password: ${DB_PASSWORD}
    maximumPoolSize: 50
  ds_1:
    jdbcUrl: jdbc:mysql://mysql-1:3306/order_db
    # ... ds_2, ds_3

rules:
- !SHARDING
  tables:
    t_order:
      actualDataNodes: ds_${0..3}.t_order_${0..15}   # 4 库 × 16 表 = 64
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: db_hash_mod
      tableStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: table_hash_mod
      keyGenerateStrategy:
        column: id
        keyGeneratorName: snowflake

    t_order_item:
      actualDataNodes: ds_${0..3}.t_order_item_${0..15}
      databaseStrategy:
        standard:
          shardingColumn: user_id          # 与 t_order 同分片键
          shardingAlgorithmName: db_hash_mod
      tableStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: table_hash_mod

  # 绑定表:t_order 和 t_order_item 同分片规则,JOIN 不跨片
  bindingTables:
    - t_order, t_order_item

  # 广播表:小字典表每库一份
  broadcastTables:
    - t_region_dict

  shardingAlgorithms:
    db_hash_mod:
      type: HASH_MOD
      props:
        sharding-count: 4
    table_hash_mod:
      type: HASH_MOD
      props:
        sharding-count: 16

  keyGenerators:
    snowflake:
      type: SNOWFLAKE

props:
  sql-show: false                          # 生产关闭(调试可开)
  max-connections-size-per-query: 4

方案 3:全局唯一 ID(Snowflake)

// 分库分表后不能用 MySQL 自增(各表独立自增会冲突)
// 用 Snowflake:64 bit = 1 符号 + 41 时间戳 + 10 机器 + 12 序列

public class SnowflakeIdGenerator {
    private final long epoch = 1700000000000L;   // 起始时间戳
    private final long workerIdBits = 10L;
    private final long sequenceBits = 12L;

    private final long maxWorkerId = ~(-1L << workerIdBits);    // 1023
    private final long maxSequence = ~(-1L << sequenceBits);    // 4095

    private final long workerId;
    private long sequence = 0L;
    private long lastTimestamp = -1L;

    public SnowflakeIdGenerator(long workerId) {
        if (workerId < 0 || workerId > maxWorkerId) {
            throw new IllegalArgumentException("workerId out of range");
        }
        this.workerId = workerId;
    }

    public synchronized long nextId() {
        long now = System.currentTimeMillis();

        if (now < lastTimestamp) {
            // 时钟回拨:小幅等待,大幅抛异常
            long offset = lastTimestamp - now;
            if (offset <= 5) {
                try { wait(offset << 1); } catch (InterruptedException e) {}
                now = System.currentTimeMillis();
                if (now < lastTimestamp) throw new RuntimeException("clock moved backwards");
            } else {
                throw new RuntimeException("clock moved backwards " + offset + "ms");
            }
        }

        if (now == lastTimestamp) {
            sequence = (sequence + 1) & maxSequence;
            if (sequence == 0) {
                // 当前毫秒序列用尽,等下一毫秒
                while ((now = System.currentTimeMillis()) <= lastTimestamp) {}
            }
        } else {
            sequence = 0L;
        }

        lastTimestamp = now;

        return ((now - epoch) << (workerIdBits + sequenceBits))
             | (workerId << sequenceBits)
             | sequence;
    }
}

// workerId 分配:用 Redis / ZooKeeper / 配置中心,保证全局唯一
// 也可用 ShardingSphere 内置 SNOWFLAKE,或美团 Leaf / 百度 UidGenerator

// order_no 生成(带分片基因)
public String genOrderNo(long userId) {
    long id = idGenerator.nextId();
    // 末尾嵌入 user_id 的分片基因(后 6 位)
    long gene = userId % 64;
    return String.format("%d%02d", id, gene);
}

方案 4:平滑数据迁移

# 12 亿行不停机迁移,经典"双写 + 校验 + 切换"四阶段

# === 阶段 1:存量同步 ===
# 用 DataX / ShardingSphere-Scaling 全量迁老库 → 新分片库
$ python datax.py order_migrate.json
# - 按 user_id 范围分批,边读边按分片规则写入
# - 12 亿行,32 并发,约 6 小时

# === 阶段 2:增量同步 ===
# Canal 订阅老库 binlog,实时同步到新分片库
# 保证全量迁移期间的变更不丢

canal -> MQ -> 消费者(按分片规则写新库)

# === 阶段 3:双写 ===
# 业务代码改造:写操作同时写老库 + 新库
public void createOrder(Order order) {
    legacyOrderDao.insert(order);          // 写老库(主)
    try {
        shardingOrderDao.insert(order);    // 写新库(影子,异步/降级)
    } catch (Exception e) {
        log.error("shadow write failed", e);
        // 不影响主流程,靠 binlog 增量补
    }
}

# === 阶段 4:数据校验 ===
# 全量比对老库 vs 新库(count + 抽样字段级)
$ java -jar data-checker.jar --table t_order --sample 0.01
校验结果:
  老库总数:1,203,456,789
  新库总数:1,203,456,789      ✓
  抽样 1200w 行字段比对:差异 0   ✓

# === 灰度切换 ===
# 读流量按比例切到新库:1% → 10% → 50% → 100%
# 用配置中心动态控制,有问题秒回滚
if (grayConfig.useShardingRead(userId)) {
    return shardingOrderDao.query(...);    // 新库
} else {
    return legacyOrderDao.query(...);      // 老库
}

# 写流量全切新库后,老库保留 2 周观察,再下线

方案 5:跨片查询处理

// 分库分表后,不带分片键的查询会扫所有片,要专门处理

// 1. 带分片键的查询:直接路由(快)
// SELECT * FROM t_order WHERE user_id = 12345
//   → 路由到 ds_1.t_order_9,单表查询

// 2. order_no 查询:基因法路由
// order_no 末尾 2 位是分片基因 → 直接定位

// 3. 运营后台范围/聚合查询:走异构索引
//    把订单数据同步一份到 ES,后台查询走 ES
@EventListener
public void onOrderChanged(OrderChangedEvent e) {
    elasticsearchClient.index(toOrderDoc(e.getOrder()));
}
// 后台:多条件筛选、聚合、报表 → ES
// ES 数据不分片,天然支持任意维度查询

// 4. 全局 count:维护计数器(Redis)
// 不要 SELECT count(*),分库分表后要扫 64 表
redisTemplate.opsForValue().increment("order:count:total");

// 5. 分页:禁止深翻页
// 不好:跨片 LIMIT 100000, 20 → 每片都要取 100020 行再归并
// 好:用 order_no/id 游标翻页
// SELECT * FROM t_order WHERE user_id=? AND id < ?lastId ORDER BY id DESC LIMIT 20

// 6. 跨片事务:尽量避免
//   - 同 user_id 的操作天然不跨片(分片键设计的好处)
//   - 实在跨片:用 Seata AT 模式 或 最终一致性(MQ)
@GlobalTransactional
public void transfer(long fromUser, long toUser, BigDecimal amount) {
    // 涉及两个用户 → 跨片 → Seata 分布式事务
}

方案 6:后续扩容

# 分片数选 2 的幂(64),为了扩容时数据迁移量最小

# 方案 A:实例扩容(库数不变,迁库)
# 现状:4 实例 × 4 库 = 16 库
# 扩容:8 实例 × 2 库 = 16 库
# 操作:把每个实例的一半库迁到新实例
#   - 库不拆,整库迁移(mysqldump / xtrabackup)
#   - 业务只改 ds 配置的连接地址
#   - 不涉及数据重新分片,简单安全

# 方案 B:翻倍分表(2 倍扩容)
# hash_mod 64 → hash_mod 128
# user_id % 64 = N 的数据,扩容后落在 N 或 N+64
# 即:每张表的数据对半拆,一半留下一半迁走
# 迁移量 = 50%(比一致性哈希差,但简单)

# 推荐:一开始就规划足够大(64/128 表),用方案 A 扩实例
#   - 数据量再大,加实例摊负载,不动分片规则
#   - 64 表 × 单表 2000w = 12.8 亿,撑很久
#   - 真到天花板,再走方案 B 翻倍

# 一致性哈希:扩容只迁 1/N 数据,但范围查询/管理复杂
# 大多数业务用 hash_mod + 预留足够分片就够了

# 容量预警
- alert: ShardTableTooLarge
  expr: mysql_table_rows{table=~"t_order_.*"} > 18000000
  annotations:
    summary: "分表 {{ $labels.table }} 接近 2000w,考虑扩容"

优化效果

指标                  优化前         优化后
=========================================================
单表行数              12 亿          ~1900w(64 分表)
单表大小              1.2TB          ~20GB
写入 TPS              3000           5w+
点查 P99(带分片键)  200ms-1s       50ms
范围查询              分钟级         ES 异构索引 200ms
ALTER TABLE 加字段    锁表几小时     单表秒级
全库备份              8 小时         单库 30min(可并行)
主从延迟              大事务飙分钟级 稳定 < 1s

业务影响:
- 交易写入能力 16x,大促不再卡
- DDL 变更窗口从"凌晨停机"变成"随时在线"
- 单实例故障只影响 1/4 用户(爆炸半径缩小)
- 运营报表走 ES,不再拖垮交易库

迁移过程:
- 全程不停机,业务无感
- 双写期发现并修复 3 处数据不一致
- 灰度切换 1%→100% 用了 1 周
- 老库观察 2 周后下线

避坑清单

1. 分片键选高频查询条件(订单选 user_id),决定整个方案成败
2. 分片数选 2 的幂(64/128),为扩容留余地,一次规划到位
3. order_no 用基因法嵌入分片位,无 user_id 也能精确路由
4. 全局 ID 用 Snowflake,处理时钟回拨;不能用 MySQL 自增
5. t_order/t_order_item 设 bindingTables,JOIN 不跨片
6. 迁移走"全量 + 增量 + 双写 + 校验 + 灰度"五步,不停机
7. 不带分片键的查询走 ES 异构索引,别全片扫描
8. 禁止深翻页,用游标(WHERE id < lastId)翻页
9. 跨片事务尽量避免,实在要用上 Seata 或最终一致性
10. 分表行数监控告警,接近 2000w 提前规划扩容

总结

MySQL 分库分表是单库到天花板后的必经之路,但它是把双刃剑,改造复杂、运维变重,不到万不得已不要做。最大的认知改变:分片键的选择决定一切,选对了 90% 查询都能精确路由、又快又简单;选错了大量查询变成全片扫描,比不分片还慢。订单业务选 user_id 是经过验证的最优解,因为绝大多数查询都是"查某个用户的订单"。最被低估的是基因法,order_no 里嵌入 user_id 的分片位,让"按订单号查"这个高频场景也能精确路由,不用退化成全片扫描,这个小技巧解决了分片键单一带来的最大痛点。最容易踩的坑是想当然地用 MySQL 自增主键,分库分表后各表独立自增必然冲突,必须换 Snowflake 这类全局 ID,而 Snowflake 又要处理时钟回拨问题。最后,平滑迁移是最考验工程能力的环节,"全量同步 + binlog 增量 + 双写 + 数据校验 + 灰度切换"五步缺一不可,数据校验阶段我们就发现了 3 处不一致 —— 12 亿行的迁移,没有校验环节就是在赌博。

—— 别看了 · 2026