分库分表方案实战：从亿级数据到横向扩展的全流程落地指南

何时需要分库分表

单表数据量超过5000万行后，MySQL的B+树索引深度增加，查询性能出现断崖式下降。线上表现是：简单的主键查询从1ms涨到10ms，范围扫描从百毫秒涨到秒级，DDL操作锁表时间从分钟级涨到小时级。经验法则：单表2000万行以内性能可接受，2000-5000万行需要监控关注，超过5000万行必须考虑分表或归档。

分库分表的两个维度：垂直拆分（按业务模块拆分到不同数据库）和水平拆分（按数据规则拆分到同结构的不同表/库）。垂直拆分解决的是业务耦合问题，水平拆分解决的是单表容量问题。实际工程中两者往往结合使用。

分片键选择与路由算法

分片键的选择决定了数据分布的均匀性和查询的路由效率。错误的选择会导致数据倾斜和跨库查询：

分片键选择原则：高频查询条件字段优先；数据分布均匀的字段优先；尽量避免跨分片JOIN。用户ID、订单ID、租户ID是常见的优质分片键；创建时间、状态字段因为分布不均，不适合做分片键。

路由算法对比：

# 1. 取模路由：数据分布最均匀，但扩容需要rehash
shard_index = user_id % shard_count  # user_id=12345, 4个分片 -> 分片1

# 2. 范围路由：按时间范围分片，适合日志/流水数据，扩容简单
if create_time >= "2026-07-01" and create_time < "2026-08-01":
    shard = "order_202607"

# 3. 一致性哈希：扩缩容只影响相邻节点，适合动态扩容场景
import hashlib
def consistent_hash(key, nodes, virtual_count=150):
    ring = {}
    for node in nodes:
        for i in range(virtual_count):
            vnode = hashlib.md5(f"{node}-{i}".encode()).hexdigest()
            ring[vnode] = node
    # 找到第一个大于等于key哈希值的节点
    key_hash = hashlib.md5(str(key).encode()).hexdigest()
    for vnode in sorted(ring.keys()):
        if vnode >= key_hash:
            return ring[vnode]
    return ring[sorted(ring.keys())[0]]  # 环回首个节点

ShardingSphere-JDBC配置实战

ShardingSphere-JDBC作为客户端分片方案，无需部署独立代理节点，对应用侵入性低。Spring Boot集成配置：

# application-sharding.yml
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://10.0.1.10:3306/order_db0?useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: ${DB_PASSWORD}
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://10.0.1.11:3306/order_db1?useSSL=false&serverTimezone=Asia/Shanghai
        username: root
        password: ${DB_PASSWORD}

    rules:
      sharding:
        tables:
          t_order:
            actual-data-nodes: ds0.t_order_0,ds0.t_order_1,ds1.t_order_0,ds1.t_order_1
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: order-db-mod
            table-strategy:
              standard:
                sharding-column: order_id
                sharding-algorithm-name: order-tbl-mod
        sharding-algorithms:
          order-db-mod:
            type: MOD
            props:
              sharding-count: 2
          order-tbl-mod:
            type: MOD
            props:
              sharding-count: 2
        binding-tables:
          - t_order,t_order_item
        broadcast-tables:
          - t_region  # 广播表：每个库都有全量数据

Java代码中完全屏蔽分片细节，像操作单表一样查询：

@Mapper
public interface OrderMapper {
    // ShardingSphere自动路由到正确的分片
    @Select("SELECT * FROM t_order WHERE user_id = #{userId} AND order_id = #{orderId}")
    Order selectByUserAndOrder(@Param("userId") Long userId, @Param("orderId") Long orderId);

    // 绑定表JOIN避免笛卡尔积
    @Select("SELECT o.*, oi.product_name FROM t_order o " +
            "JOIN t_order_item oi ON o.order_id = oi.order_id " +
            "WHERE o.user_id = #{userId}")
    List<OrderWithItem> selectWithItems(@Param("userId") Long userId);
}

全局ID生成方案

分库分表后数据库自增ID不再唯一。必须引入分布式ID生成方案：

// 基于Snowflake的ID生成器
public class DistributedIdGenerator {
    private final long epoch = 1704067200000L;  // 2024-01-01基准时间
    private final long workerIdBits = 10L;
    private final long sequenceBits = 12L;
    private final long maxWorkerId = ~(-1L << workerIdBits);  // 1024
    private final long sequenceMask = ~(-1L << sequenceBits); // 4095

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

    public DistributedIdGenerator(long workerId) {
        if (workerId > maxWorkerId || workerId < 0) {
            throw new IllegalArgumentException("WorkerID范围: 0-" + maxWorkerId);
        }
        this.workerId = workerId;
    }

    public synchronized long nextId() {
        long timestamp = System.currentTimeMillis() - epoch;
        if (timestamp < lastTimestamp) {
            throw new RuntimeException("时钟回拨，拒绝生成ID");
        }
        if (timestamp == lastTimestamp) {
            sequence = (sequence + 1) & sequenceMask;
            if (sequence == 0) {
                timestamp = tilNextMillis(lastTimestamp);
            }
        } else {
            sequence = 0L;
        }
        lastTimestamp = timestamp;
        return (timestamp << (workerIdBits + sequenceBits))
             | (workerId << sequenceBits)
             | sequence;
    }

    private long tilNextMillis(long lastTimestamp) {
        long timestamp = System.currentTimeMillis() - epoch;
        while (timestamp <= lastTimestamp) {
            timestamp = System.currentTimeMillis() - epoch;
        }
        return timestamp;
    }
}

数据迁移与双写一致性

从单库迁移到分库分表，最大的挑战是迁移过程中保证业务连续性。双写方案是经过验证的路径：

# 数据迁移步骤
# 1. 开启双写：新数据同时写入旧库和新分片
# 2. 历史数据迁移：按分片规则迁移存量数据
# 3. 数据校验：对比旧库和新分片的数据一致性
# 4. 读流量切换：将查询切到新分片
# 5. 停止旧库写入：确认无异常后关闭双写

def verify_migration(source_db, sharded_dbs, table, shard_key):
    cursor = source_db.cursor()
    cursor.execute(f"SELECT {shard_key}, COUNT(*) FROM {table} GROUP BY {shard_key} % 4")
    source_counts = dict(cursor.fetchall())

    for shard_idx, shard_db in enumerate(sharded_dbs):
        shard_cursor = shard_db.cursor()
        shard_cursor.execute(f"SELECT COUNT(*) FROM {table}_{shard_idx}")
        shard_count = shard_cursor.fetchone()[0]
        expected = sum(1 for k in source_counts if k % 4 == shard_idx)
        if shard_count != expected:
            print(f"数据不一致! 分片{shard_idx}: 预期{expected}, 实际{shard_count}")
            return False

    print("数据校验通过")
    return True

分库分表是架构演进的里程碑决策，回退成本极高。在执行前完成容量评估、分片键论证、迁移方案评审，比动手写代码更重要。