数据库分库分表前的容量评估与方案选型

DBA 丢给我一张容量曲线图

2 月底，DBA 在周会上放了张图：订单主库 t_order 已经 4200 万行，数据文件 38G，加上索引一共 51G，按每月 380 万行的增速，年底到 8000 万行，明年年中破亿。他的结论是"该考虑分库分表了"。

主管把这个评估任务派给了我。说实话我当时有点虚，工作第二年，没实际做过拆分。硬着头皮做了一个月，中间方案被架构师挑战了两轮，最后交出去的东西和第一版差别很大。这里把过程记下来。

第一步：先别急着分，把数字摸清楚

我先花了一周采集现状，分了几块：

数据增长和留存

mysql> SELECT
         DATE_FORMAT(create_time, '%Y-%m') AS ym,
         COUNT(*) AS cnt,
         ROUND(SUM(1) / 30) AS per_day
       FROM t_order
       WHERE create_time >= '2018-09-01'
       GROUP BY ym;

+---------+-------+---------+
| ym      | cnt   | per_day |
+---------+-------+---------+
| 2018-09 | 3.1M  |  103K   |
| 2018-12 | 3.6M  |  120K   |
| 2019-02 | 3.8M  |  127K   |
+---------+-------+---------+

再查访问分布，用慢查询日志加业务埋点统计：

这份统计决定了后面所有的设计。它说明一件事：热数据只占全表的不到 15%，剩下 3600 万行基本没人碰。

性能到底差在哪

用 sysbench 在同样的 4 核 16G 测试机上，对不同数据量的表做了对比压测（oltp_read_write，64 线程）：

主键点查几乎没变化，这个结果一开始让我很意外。查了资料才明白：InnoDB 的 B+ 树非叶子节点能存上千个 key，4200 万行、主键是 bigint 的情况下树高也就 3 到 4 层，多一层只是多一次内存里的页查找。所谓"单表超过 2000 万就慢"这个说法，准确的表述应该是"索引树变高 + 缓冲池命中率下降 + 索引维护成本上升"三件事叠加，而不是行数本身有魔法阈值。

真正影响大的是写入 TPS，从 14200 掉到 8200，掉 42%。原因是表空间大了以后，二级索引的插入（尤其是非递增的 idx_user_time）会造成大量随机 IO 和页分裂。

先做能立刻见效的事：归档

基于热数据只占 15% 这个事实，我先推了一个不需要改架构的方案：把 12 个月之前的订单迁到历史库，主表只留近一年（约 4500 万行，而且还是继续涨）。

后来被架构师一票否决了："你迁完之后主表还是 4500 万，明年这个会还得再开一次。"

改成留 6 个月，主表压到 2200 万行。归档脚本用分批删除，避免大事务把 undo 撑爆（这个坑我在 MVCC 那篇里写过）：

-- 一次删 5000 行，循环执行，每批之间 sleep 0.1 秒
DELETE FROM t_order
WHERE create_time < '2018-09-01'
LIMIT 5000;

归档上线后实测：写入 TPS 从 8200 回到 11600，二级索引范围查 P99 从 12.6ms 降到 6.8ms。这一步没动任何架构，花了三天，收益比后面所有方案加起来都高。

所以第一版报告里我写了句后来被反复引用的话：在考虑分库分表之前，先确认你的瓶颈是不是"数据太多"，而不是"索引没建对、慢 SQL 没优化、冷热没分离"。我们慢查询日志里排第一的那条 SQL 是全表扫的统计查询，优化之后整体负载降了 18%。

真要分，先定分片键

分片键是整套方案里唯一一个"选错了要推倒重来"的决策，我在这上面花的时间最多。

候选是 user_id 和 order_no。按 order_no 哈希分，C 端"我的订单列表"这种查询就得广播到所有分片，34% 的流量会变成跨分片查询，直接否决。选 user_id，理由：

• 它是最常见的查询维度，C 端 63% 的请求带 user_id
• 同一个用户的数据在同一个分片，单用户的一致性容易保证
• 数据分布比较均匀（我们有个大商家占了 12% 的订单，但 C 端用户很分散）

选了 user_id 之后，订单详情按 order_no 查（29% 流量）就麻烦了：只有 order_no 不知道在哪个分片。解决办法是把分片基因编码进订单号：

// 订单号 = 时间戳(14位) + 机器位(4位) + 序列(6位) + 分片基因(2位)
// 分片基因 = user_id % 64，固定占最后两位
public String genOrderNo(long userId) {
    long gene = userId & 63;                      // 64 个分片
    return String.format("%s%04d%06d%02d",
            DateTimeFormatter.ofPattern("yyyyMMddHHmmss").format(LocalDateTime.now()),
            workerId, sequence.incrementAndGet() & 999999, gene);
}

这样拿到订单号就能反推出分片：shard = Long.parseLong(orderNo.substring(orderNo.length()-2)) % 64。这个技巧我们叫它"基因法"，代价是订单号变长了两 位，而且分片数定了 64 就不能随便改。

商家端的 18% 流量用 merchant_id 查，这个和 user_id 分片完全不搭。方案是做一张异构索引表：另建一张按 merchant_id 分片的 t_order_merchant_index，只存 merchant_id + order_no + 少量冗余字段，用 Canal 订阅 binlog 同步写入。商家端先查索引表拿到 order_no 列表，再按基因回查主表。多一次查询，但避免了全分片广播。

Sharding-JDBC 还是 MyCat

2019 年年初能选的其实就这两个。我把对比结果整理成了表，交上去评审：

最后选了 Sharding-JDBC（4.0.0-RC1，2018 年底这个项目进了 Apache 孵化器，包名从 io.shardingjdbc 变成了 org.apache.shardingsphere，网上老文档要注意区分）。几个决定性的理由：

• 我们全栈 Java，不需要多语言支持
• 运维同学明确表示不想再接一个中间件，光是给 MyCat 做高可用就要额外的机器和人力
• 压测 TPS 差 14%，这个差距主要来自多一跳网络

它最大的短板是"升级要发应用"，我们 6 个服务都要升。评估下来可以接受，因为分片规则一旦定下来基本不会改。

配置是这么写的：

spring:
  shardingsphere:
    datasource:
      names: ds0, ds1
      ds0: {url: jdbc:mysql://10.0.1.11:3306/order_0, ...}
      ds1: {url: jdbc:mysql://10.0.1.12:3306/order_1, ...}
    sharding:
      tables:
        t_order:
          actual-data-nodes: ds$->{0..1}.t_order_${0..31}
          database-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: ds${user_id % 2}
          table-strategy:
            inline:
              sharding-column: user_id
              algorithm-expression: t_order_${(user_id.intdiv(2)) % 32}
      default-database-strategy:
        inline:
          sharding-column: user_id
          algorithm-expression: ds${user_id % 2}

这里做了个关键设计：一开始就分 32 张逻辑表，但只放在 2 个库里。以后扩容时，只需要把其中 16 张表整体迁到新库，改一下 actual-data-nodes 配置，不用重算哈希、不用全量重分布数据。这是当时架构师给我提的核心建议，我第一版方案是"2 库 8 表，以后扩到 4 库 8 表"，被他一句话问住了："扩到 4 库的时候，user_id % 2 变成 % 4，一半的数据要搬家，你怎么迁？"

跨分片查询怎么收场

分片之后最难受的不是写入，是这三类查询：

• 跨分片分页：ORDER BY create_time LIMIT 10000, 20 在 64 个分片上会变成每个分片取 10020 条再归并，内存里排 64 万条。我们的做法是限制分页深度，超过 100 页就要求加时间范围条件，配合"上一页最后一条的 create_time"做游标分页。
• 跨分片统计：全部挪到离线。对账和报表不再查主库，改成从 Canal 同步到专门的统计库跑。
• 跨库事务：下单要同时改订单表和库存表，这两张表分片键不同。最后用了最大努力通知 + 本地消息表，没有上 XA（XA 性能太差，我们压测下 TPS 只有原来的 1/5，而且当时的 XA 实现在宕机恢复上有很多坑）。

上线路径

整个迁移分了五步，从 3 月初到 5 月底，跨了将近三个月：

1. 归档先行，把数据量压下来（3 天）
2. 双写：新数据同时写老表和新分片表，新表作为影子表，只写不读（2 周）
3. 停写窗口内做全量同步，用 mysqldump --where 按 user_id 分段导出导入，4200 万行花了 4 小时 20 分
4. 增量补偿：比对双写期间的 binlog，我们写了个脚本校验两边主键和金额，发现 37 条不一致，都是同步延迟造成的，重跑一遍就对齐了
5. 灰度切读：先切 1% 用户按 user_id 尾号路由到新表，观察三天，再切 10%、50%，两周后全量

回滚预案是保留双写和老表数据 30 天，路由开关随时能切回去。结果没用上，但这份预案是评审能通过的前提。

小结

• 分库分表是最后的手段，不是第一选择。归档、索引优化、慢 SQL 治理、读写分离、加从库，这些做完再谈拆分。我们光归档就解决了大半问题。
• 分片键的决定要基于真实的查询分布统计，不是拍脑袋。我们统计了 5 个场景的占比，才敢选 user_id。
• 订单号里编码分片基因，是"按 A 分片但要按 B 查"这种矛盾最便宜的解法。其他的维度用异构索引表 + binlog 同步兜。
• 一开始就按最终规模做逻辑分片（我们 32 张表起步），扩容变成"搬库"而不是"重分布"，这个设计省下的工作量比选哪个中间件重要得多。
• Sharding-JDBC 和 MyCat 的选择，本质是"运维复杂度换灵活性"。团队没人愿意接新中间件的时候，客户端分片的缺点就不那么重要了。

这套方案上线三个月，主表稳定在 2200 万行左右，写入 TPS 11600，C 端订单列表 P99 从 12.6ms 降到 4.3ms（数据量小了 + 少了跨分片）。中间踩的另外一个大坑是全局 ID 生成，从数据库号段改成了 Snowflake，那又是另一篇了。

参考

• 官方文档：database-sql
• Baeldung 上的相关实战
• StackOverflow 上的相关讨论