SaaS平台的数据库扩展之路从单库到读写分离再到分库分表的复盘数据库的扩展不是选择题而是填空题——在什么量级用什么方案什么时候该切换答案是由数据量和业务特征填写的。本文复盘一个SaaS平台数据库架构的三次关键演进包含每个阶段的触发条件、技术方案、迁移策略和踩坑记录。一、数据库扩展的阶段模型阶段数据量级触发条件核心架构单库500万行—MySQL单实例读写分离500万~5000万行读QPS3000 或 单库CPU70%一主多从分库分表5000万行单表2000万行 或 写QPS1000ShardingSphere/分片NewSQL亿级跨分片查询成为瓶颈TiDB/CockroachDB二、阶段一→阶段二读写分离2.1 为什么要做读写分离当时系统处于以下状态订单表日增5万行累计接近800万读QPS峰值3200写QPS峰值180主库CPU持续在75%以上报表查询与业务查询共用主库互相影响读写比例约18:1——典型的读多写少场景读写分离是最合适的方案。2.2 实现方案Configuration public class ReadWriteSplittingConfig { Bean public DataSource routingDataSource() { // 主库写 HikariDataSource master new HikariDataSource(); master.setJdbcUrl(jdbc:mysql://master.db.internal:3306/saas); master.setMaximumPoolSize(50); master.setMinimumIdle(10); master.setConnectionTimeout(3000); // 从库1读 HikariDataSource slave1 new HikariDataSource(); slave1.setJdbcUrl(jdbc:mysql://slave1.db.internal:3306/saas); slave1.setMaximumPoolSize(100); slave1.setReadOnly(true); // 从库2读 HikariDataSource slave2 new HikariDataSource(); slave2.setJdbcUrl(jdbc:mysql://slave2.db.internal:3306/saas); slave2.setMaximumPoolSize(100); slave2.setReadOnly(true); // 读写分离路由 MapObject, Object dataSources Map.of( master, master, slave1, slave1, slave2, slave2 ); ReadWriteRoutingDataSource router new ReadWriteRoutingDataSource(); router.setTargetDataSources(dataSources); router.setDefaultTargetDataSource(master); return router; } } public class ReadWriteRoutingDataSource extends AbstractRoutingDataSource { private static final ThreadLocalBoolean READ_ONLY ThreadLocal.withInitial(() - false); private final ListString slaveKeys List.of(slave1, slave2); private final AtomicInteger roundRobin new AtomicInteger(0); Override protected Object determineCurrentLookupKey() { if (READ_ONLY.get()) { // 读操作轮询从库 int idx roundRobin.getAndIncrement() % slaveKeys.size(); return slaveKeys.get(idx); } return master; } public static void setReadOnly(boolean readOnly) { READ_ONLY.set(readOnly); } public static void clear() { READ_ONLY.remove(); } } // AOP自动切换Transactional(readOnlytrue) → 走从库 Aspect Component Order(0) public class ReadOnlyDataSourceAspect { Around(annotation(transactional)) public Object route(ProceedingJoinPoint pjp, Transactional transactional) throws Throwable { try { ReadWriteRoutingDataSource.setReadOnly(transactional.readOnly()); return pjp.proceed(); } finally { ReadWriteRoutingDataSource.clear(); } } } // 主从延迟处理写后立即读必须走主库 Service public class OrderService { Transactional public Order createOrder(CreateOrderRequest req) { Order order orderRepo.save(buildOrder(req)); // 写后立即读强制走主库避免读到旧数据 ReadWriteRoutingDataSource.setReadOnly(false); Order freshOrder orderRepo.findById(order.getId()); return freshOrder; } }2.3 主从延迟的应对策略Component public class ReplicationLagMonitor { private final JdbcTemplate jdbc; /** * 定期检查主从延迟 */ Scheduled(fixedRate 5000) public void monitor() { for (String slave : List.of(slave1, slave2)) { int lagSeconds checkLag(slave); // 记录延迟指标 meterRegistry.gauge(mysql.replication.lag, Tags.of(slave, slave), lagSeconds); if (lagSeconds 10) { // 延迟10秒临时摘除该从库 removeSlaveFromPool(slave); alertService.warn(从库 %s 复制延迟 %d 秒已摘除.formatted( slave, lagSeconds)); } else if (lagSeconds 3) { // 延迟恢复重新加入 addSlaveToPool(slave); } } } private int checkLag(String slave) { String sql SHOW SLAVE STATUS; MapString, Object status jdbc.queryForMap(sql); return (int) status.getOrDefault(Seconds_Behind_Master, 999); } }三、阶段二→阶段三分库分表3.1 分片策略设计当订单表突破2000万行后即使读写分离单表写入也开始出现瓶颈。分库分表势在必行# ShardingSphere 配置 dataSources: ds0: url: jdbc:mysql://shard0.db.internal:3306/saas_order_0 ds1: url: jdbc:mysql://shard1.db.internal:3306/saas_order_1 ds2: url: jdbc:mysql://shard2.db.internal:3306/saas_order_2 ds3: url: jdbc:mysql://shard3.db.internal:3306/saas_order_3 rules: - !SHARDING tables: t_order: # 分库策略tenant_id hash mod 4 actualDataNodes: ds$-{0..3}.t_order_$-{0..15} databaseStrategy: standard: shardingColumn: tenant_id shardingAlgorithmName: tenant_db_sharding # 分表策略order_id hash mod 16单库内16张表 tableStrategy: standard: shardingColumn: order_id shardingAlgorithmName: order_table_sharding t_order_item: # 绑定表与order同分片避免跨库JOIN actualDataNodes: ds$-{0..3}.t_order_item_$-{0..15} databaseStrategy: standard: shardingColumn: tenant_id shardingAlgorithmName: tenant_db_sharding tableStrategy: standard: shardingColumn: order_id shardingAlgorithmName: order_table_sharding shardingAlgorithms: tenant_db_sharding: type: MOD props: sharding-count: 4 order_table_sharding: type: HASH_MOD props: sharding-count: 16 # 广播表每个分片都有完整副本 broadcastTables: - t_config - t_tenant_info3.2 分布式ID生成分库分表后自增ID不再全局唯一需要分布式ID方案Component public class SnowflakeIdGenerator { private final long workerId; private final long datacenterId; private long sequence 0L; private long lastTimestamp -1L; // 雪花算法参数 private static final long EPOCH 1704067200000L; // 2024-01-01 private static final long WORKER_ID_BITS 5L; private static final long DATACENTER_ID_BITS 5L; private static final long SEQUENCE_BITS 12L; private static final long MAX_WORKER_ID ~(-1L WORKER_ID_BITS); private static final long MAX_DATACENTER_ID ~(-1L DATACENTER_ID_BITS); private static final long WORKER_ID_SHIFT SEQUENCE_BITS; private static final long DATACENTER_ID_SHIFT SEQUENCE_BITS WORKER_ID_BITS; private static final long TIMESTAMP_SHIFT SEQUENCE_BITS WORKER_ID_BITS DATACENTER_ID_BITS; /** * 生成分布式唯一ID * 结构[41位时间戳][5位数据中心][5位机器][12位序列号] */ public synchronized long nextId() { long timestamp System.currentTimeMillis(); if (timestamp lastTimestamp) { throw new RuntimeException( 时钟回拨! 上次: lastTimestamp 当前: timestamp); } if (timestamp lastTimestamp) { sequence (sequence 1) ((1 SEQUENCE_BITS) - 1); if (sequence 0) { // 当前毫秒序列号用完等待下一毫秒 timestamp waitNextMillis(lastTimestamp); } } else { sequence 0L; } lastTimestamp timestamp; return ((timestamp - EPOCH) TIMESTAMP_SHIFT) | (datacenterId DATACENTER_ID_SHIFT) | (workerId WORKER_ID_SHIFT) | sequence; } }3.3 平滑迁移策略从单库迁移到分库分表不能停机。平滑迁移三步走Service public class MigrationCoordinator { private final OrderRepository oldRepo; // 旧单库 private final OrderRepository newRepo; // 新分片集群 private final FeatureFlagService featureFlag; /** * 双写模式同时写入新旧两套存储 */ Transactional public Order createOrder(Order order) { // 主写入根据灰度比例决定 if (featureFlag.isEnabled(db_migration_write_new, order.getTenantId())) { order newRepo.save(order); // 主写新库 } else { order oldRepo.save(order); // 主写旧库 } // 异步双写到另一侧 CompletableFuture.runAsync(() - { try { if (featureFlag.isEnabled(db_migration_write_new, order.getTenantId())) { oldRepo.save(order); // 异步补写旧库 } else { newRepo.save(order); // 异步补写新库 } } catch (Exception e) { // 双写失败不影响主流程但需要记录差异 diffRecorder.record(order.getId(), e); } }, migrationExecutor); return order; } /** * 渐进式灰度切换 */ Scheduled(cron 0 0 * * * ?) public void progressiveRollout() { MigrationConfig config configService.getConfig(); // 每小时自动扩大5%灰度比例前提错误率0.1% double currentErrorRate metricsService.getMigrationErrorRate(); if (currentErrorRate 0.001 config.getTrafficPercentage() 100) { int newPercentage Math.min( config.getTrafficPercentage() 5, 100); configService.updateTrafficPercentage(newPercentage); log.info(Migration traffic increased to {}%, newPercentage); } else if (currentErrorRate 0.001) { log.warn(Migration error rate {} exceeds threshold, pausing rollout, currentErrorRate); } } }3.4 迁移后的性能对比指标单库读写分离分库分表4×16数据总量2100万2100万2100万单表平均行数2100万2100万~33万写入QPS上限8508503800查询P99延迟单条180ms45ms12ms查询P99延迟分页3200ms850ms120ms存储空间含索引45GB45GB 45GB×212GB × 4四、特殊场景的处理4.1 跨分片查询的应对分库分表后最大的痛点是跨分片查询Service public class CrossShardQueryService { /** * 策略1应用层聚合适用于简单统计 */ public OrderStats getStats(String tenantId) { // 并行查询所有分片 ListCompletableFutureOrderStats futures IntStream.range(0, 4).mapToObj(shardId - CompletableFuture.supplyAsync(() - orderRepo.getStatsByShard(tenantId, shardId)) ).toList(); // 聚合结果 return futures.stream() .map(CompletableFuture::join) .reduce(new OrderStats(), OrderStats::merge); } /** * 策略2ES异构索引适用于复杂搜索 */ Transactional public void syncToElasticsearch(Order order) { // 通过Canal监听binlog → 异步同步到ES // ES中不存储全量字段仅存储搜索需要的字段 OrderDocument doc OrderDocument.builder() .orderId(order.getId()) .tenantId(order.getTenantId()) .amount(order.getAmount()) .status(order.getStatus()) .createdAt(order.getCreatedAt()) .build(); elasticsearchTemplate.save(doc); } }五、总结数据库扩展的三个核心经验不要在没必要时过度设计。单库阶段能撑到500万行读写分离能撑到2000万行——在触发条件没达到之前过度优化只会增加不必要的复杂度。分片键的选择决定了架构上限。我们选择tenant_id作为分库键order_id作为分表键是因为SaaS场景下99%的查询都携带租户ID。如果选错了分片键后期修正的成本远高于前期设计。迁移方案比架构方案更重要。分库分表的架构方案调研了2周但平滑迁移的方案设计和演练花了6周。双写→灰度→全量三步走每一步都有充分的验证和回滚机制——生产环境的数据迁移容错率是零。