分库分表策略

简介

当单表数据量达到千万甚至亿级别时，数据库的查询性能和写入能力会遇到瓶颈。分库分表是解决大数据量存储和访问问题的核心策略，通过将数据分散到多个数据库或表中，实现水平扩展。本文将介绍垂直与水平分片策略、分片键选择、分布式 ID 生成方案以及常用中间件的实践。

特点

• 1.水平与垂直分片 — 两种基本的数据分片策略
• 2.分片键设计 — 选择合适的数据分片依据
• 3.分布式 ID — 保证全局唯一性的 ID 生成方案
• 4.中间件方案 — ShardingSphere、MyCat 等中间件的实践

水平分片与垂直分片

垂直分片

-- 垂直分库：按照业务模块将表拆分到不同的数据库
-- 适合：不同业务模块之间耦合度低的系统

-- 原始单库结构
-- | 数据库     | 表                              |
-- |-----------|--------------------------------|
-- | myapp_db  | users, orders, products, logs   |

-- 垂直分库后
-- | 数据库          | 表                    | 说明        |
-- |----------------|----------------------|-------------|
-- | user_db        | users, user_profiles | 用户中心     |
-- | order_db       | orders, order_items  | 订单中心     |
-- | product_db     | products, categories | 商品中心     |
-- | log_db         | access_logs, audit_logs | 日志中心  |

-- 垂直分表：将一张大表按列拆分为多张表
-- 将不常用的字段拆分到扩展表

-- 原始表
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    password_hash VARCHAR(200),
    avatar_url VARCHAR(500),           -- 不常用
    bio TEXT,                          -- 不常用
    preferences JSON,                  -- 不常用
    login_count INT,                   -- 不常用
    last_login_at DATETIME,            -- 不常用
    created_at DATETIME,
    updated_at DATETIME
);

-- 拆分后：基础表（高频访问）
CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    password_hash VARCHAR(200),
    created_at DATETIME,
    updated_at DATETIME
);

-- 拆分后：扩展表（低频访问）
CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY,
    avatar_url VARCHAR(500),
    bio TEXT,
    preferences JSON,
    login_count INT,
    last_login_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

水平分片

-- 水平分表：将同一张表的数据按某种规则分散到多张结构相同的表
-- 适合：单表数据量过大的场景

-- 示例：订单表按用户 ID 取模分表
-- 分为 4 张表：orders_0, orders_1, orders_2, orders_3
-- 分表规则：table_index = user_id % 4

CREATE TABLE orders_0 (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at DATETIME NOT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_order_no (order_no)
);

CREATE TABLE orders_1 (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),
    created_at DATETIME NOT NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_order_no (order_no)
);

CREATE TABLE orders_2 (LIKE orders_0);
CREATE TABLE orders_3 (LIKE orders_0);

-- 数据路由示例（应用层实现）
-- user_id = 10035 -> 10035 % 4 = 3 -> orders_3
-- user_id = 10012 -> 10012 % 4 = 0 -> orders_0

-- 水平分库分表
-- 将数据分散到多个数据库实例
-- | 库         | 表                    |
-- |-----------|----------------------|
-- | db_0      | orders_0, orders_1   |
-- | db_1      | orders_2, orders_3   |
-- | db_2      | orders_4, orders_5   |
-- | db_3      | orders_6, orders_7   |

-- 路由规则：
-- db_index = user_id % 4
-- table_index = (user_id / 4) % 2

分片策略对比

-- 常见分片策略对比
-- | 策略          | 规则              | 优点              | 缺点              | 适用场景        |
-- |--------------|------------------|-------------------|-------------------|----------------|
-- | 取模分片       | id % N           | 数据分布均匀       | 扩容困难（需迁移）   | 用户维度查询    |
-- | 范围分片       | id 1-1M -> t1    | 扩容容易           | 热点数据不均匀      | 自增 ID、时间   |
-- | Hash 分片     | hash(key) % N    | 分布均匀           | 扩容需 rehash      | 随机分布场景    |
-- | 一致性 Hash    | 一致性 Hash 环     | 扩容迁移数据少      | 实现复杂           | 动态扩容场景    |
-- | 按日期分片     | 按月/按天          | 易管理、易归档      | 可能热点不均        | 日志、流水类    |
-- | 枚举分片       | 按地区/类型        | 业务语义清晰        | 需要维护映射关系     | 地区分片       |

-- 按日期分表示例（日志表）
CREATE TABLE access_logs_2026_01 (LIKE access_logs_template);
CREATE TABLE access_logs_2026_02 (LIKE access_logs_template);
CREATE TABLE access_logs_2026_03 (LIKE access_logs_template);
CREATE TABLE access_logs_2026_04 (LIKE access_logs_template);

-- 查询时根据日期范围路由到对应表
-- SELECT * FROM access_logs_2026_04 WHERE path = '/api/orders'

分片键选择

分片键设计原则

-- 分片键（Sharding Key）决定了数据如何分布到各个分片

-- 1. 分片键选择标准
-- 高选择性：分片键的值分布均匀，避免数据倾斜
-- 高频查询：分片键是大多数查询的过滤条件
-- 不可变：分片键的值不应被更新
-- 业务关联：相同业务逻辑的数据应该落在同一分片

-- 2. 电商系统分片键选择示例

-- 订单表：使用 user_id 作为分片键
-- 原因：大部分查询都是按用户查订单
-- SELECT * FROM orders WHERE user_id = ? AND ...
-- 分片键: user_id

-- 用户表：使用 id 作为分片键
-- 原因：大部分查询都是按用户 ID 查询
-- SELECT * FROM users WHERE id = ?
-- 分片键: id

-- 商品表：使用 id 作为分片键
-- 原因：商品查询通常按商品 ID
-- SELECT * FROM products WHERE id = ?
-- 分片键: id

-- 订单项表：使用 order_id 关联的分片键
-- 原因：订单项通常与订单一起查询
-- 需要保证订单和订单项在同一个分片
-- 分片键: user_id（与订单表保持一致）

-- 3. 非分片键查询的处理

-- 问题：按 order_no 查询订单，但分片键是 user_id
-- SELECT * FROM orders WHERE order_no = 'ORD20260412001'
-- 不知道数据在哪个分片

-- 方案一：全表扫描（不推荐）
-- 在所有分片上执行查询，合并结果

-- 方案二：建立映射表
CREATE TABLE order_mapping (
    order_no VARCHAR(32) PRIMARY KEY,
    user_id BIGINT NOT NULL,
    db_index INT NOT NULL,
    table_index INT NOT NULL,
    INDEX idx_order_no (order_no)
);

-- 先查映射表获取分片位置
SELECT db_index, table_index FROM order_mapping WHERE order_no = 'ORD20260412001';
-- 再去对应分片查询数据
SELECT * FROM orders_3 WHERE order_no = 'ORD20260412001';

-- 方案三：基因法（将分片信息编码到业务 ID 中）
-- order_no 中包含 user_id 的分片信息
-- 例如：ORD + 分片标记 + 时间 + 序列号
-- ORD032026041200001 -> 分片标记 03 -> 路由到分片 3

分布式 ID 生成

雪花算法（Snowflake）

-- 雪花算法生成 64 位唯一 ID
-- 结构（共 64 bit）
-- | 部分        | 位数 | 说明                          |
-- |------------|------|-------------------------------|
-- | 符号位      | 1    | 固定为 0                       |
-- | 时间戳      | 41   | 毫秒级时间（可用约 69 年）       |
-- | 机器 ID     | 10   | 最多 1024 台机器               |
-- | 序列号      | 12   | 每毫秒最多 4096 个 ID          |

-- 优点：有序递增、高性能、不依赖数据库
-- 每秒可生成约 400 万个 ID

-- C# 实现示例
/*
public class SnowflakeIdGenerator
{
    private readonly long _machineId;
    private readonly long _epoch = new DateTime(2026, 1, 1).Ticks / 10000;

    private long _sequence = 0;
    private long _lastTimestamp = -1;
    private readonly object _lock = new();

    // 机器 ID 占 10 位
    private const int MachineIdBits = 10;
    private const long MaxMachineId = (1L << MachineIdBits) - 1;

    // 序列号占 12 位
    private const int SequenceBits = 12;
    private const long MaxSequence = (1L << SequenceBits) - 1;

    // 位移
    private const int TimestampShift = MachineIdBits + SequenceBits; // 22
    private const int MachineIdShift = SequenceBits;                  // 12

    public SnowflakeIdGenerator(long machineId)
    {
        if (machineId < 0 || machineId > MaxMachineId)
            throw new ArgumentException($"机器 ID 必须在 0-{MaxMachineId} 之间");
        _machineId = machineId;
    }

    public long NextId()
    {
        lock (_lock)
        {
            var timestamp = GetCurrentTimestamp();

            if (timestamp < _lastTimestamp)
                throw new InvalidOperationException("时钟回拨");

            if (timestamp == _lastTimestamp)
            {
                _sequence = (_sequence + 1) & MaxSequence;
                if (_sequence == 0)
                    timestamp = WaitNextMillis(_lastTimestamp);
            }
            else
            {
                _sequence = 0;
            }

            _lastTimestamp = timestamp;

            return ((timestamp - _epoch) << TimestampShift)
                 | (_machineId << MachineIdShift)
                 | _sequence;
        }
    }

    private long GetCurrentTimestamp() =>
        DateTimeOffset.UtcNow.ToUnixTimeMilliseconds();

    private long WaitNextMillis(long lastTimestamp)
    {
        var timestamp = GetCurrentTimestamp();
        while (timestamp <= lastTimestamp)
        {
            Thread.Sleep(1);
            timestamp = GetCurrentTimestamp();
        }
        return timestamp;
    }
}
*/

-- 其他分布式 ID 方案对比
-- | 方案            | 有序  | 性能      | 依赖      | 适用场景          |
-- |----------------|------|----------|----------|-----------------|
-- | 雪花算法         | 趋势递增 | 极高     | 无        | 通用场景          |
-- | UUID           | 无序   | 高       | 无        | 不需要排序的场景    |
-- | 数据库自增        | 严格递增 | 低       | 数据库     | 低并发场景         |
-- | Redis INCR     | 严格递增 | 高       | Redis     | 需要严格递增的场景   |
-- | 号段模式          | 趋势递增 | 高       | 数据库     | 业务 ID 生成      |

分库分表中间件

ShardingSphere 实践

# Apache ShardingSphere 配置示例
# 分库分表规则配置（YAML 格式）

# dataSources 配置数据源
dataSources:
  ds_0:
    url: jdbc:mysql://192.168.1.100:3306/db_0
    username: root
    password: password
  ds_1:
    url: jdbc:mysql://192.168.1.101:3306/db_1
    username: root
    password: password

# 分片规则
rules:
  - !SHARDING
    tables:
      orders:
        actualDataNodes: ds_${0..1}.orders_${0..3}
        tableStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: orders_table_mod
        databaseStrategy:
          standard:
            shardingColumn: user_id
            shardingAlgorithmName: orders_db_mod
        keyGenerateStrategy:
          column: id
          keyGeneratorName: snowflake

    shardingAlgorithms:
      orders_table_mod:
        type: MOD
        props:
          sharding-count: 4
      orders_db_mod:
        type: MOD
        props:
          sharding-count: 2

    keyGenerators:
      snowflake:
        type: SNOWFLAKE
        props:
          worker-id: 1

-- ShardingSphere 使用示例
-- 应用层无感知，直接写标准 SQL

-- 插入数据（自动路由到对应分片）
INSERT INTO orders (order_no, user_id, total_amount, status, created_at)
VALUES ('ORD20260412001', 10035, 299.00, 'paid', '2026-04-12 10:00:00');
-- 自动路由：user_id=10035 -> db_1.orders_3

-- 按分片键查询（单分片查询）
SELECT * FROM orders WHERE user_id = 10035;
-- 只查询 db_1.orders_3

-- 非分片键查询（全分片广播）
SELECT * FROM orders WHERE order_no = 'ORD20260412001';
-- 查询所有分片，合并结果

-- 范围查询
SELECT * FROM orders
WHERE user_id BETWEEN 10000 AND 10100
ORDER BY created_at DESC LIMIT 20;
-- 路由到相关分片

-- 分页查询
SELECT * FROM orders WHERE user_id = 10035
ORDER BY created_at DESC LIMIT 10 OFFSET 20;

-- 聚合查询
SELECT status, COUNT(*) AS cnt, SUM(total_amount) AS total
FROM orders
WHERE created_at >= '2026-04-01'
GROUP BY status;
-- 所有分片执行后合并结果

跨分片查询处理

-- 跨分片 JOIN 处理

-- 1. 绑定表（Binding Tables）
-- 订单表和订单项表使用相同的分片策略
-- 保证相同 user_id 的订单和订单项在同一个分片
-- 配置绑定关系后，JOIN 查询不会走笛卡尔积

-- ShardingSphere 配置
-- bindingTables:
--   - orders, order_items

-- 这样以下查询可以正确路由到同一分片
SELECT o.*, oi.product_name
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 10035;

-- 2. 广播表（Broadcast Table）
-- 小表需要在所有分片中保存完整副本
-- 如：字典表、配置表、地区表

-- ShardingSphere 配置
-- broadcastTables:
--   - regions
--   - product_categories

-- 3. 跨分片排序分页
-- 问题：每个分片各自排序后，合并结果可能不正确
-- 解决：中间件自动处理（先取各分片 TOP N，内存中合并排序）
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 10 OFFSET 100;
-- 中间件会从每个分片取 110 条，内存合并后取第 101-110 条

-- 4. 全局唯一索引
-- 分片后每个分片的自增 ID 不再全局唯一
-- 解决方案：
-- a) 使用 Snowflake 生成分布式 ID
-- b) 使用号段模式分配 ID 范围
-- c) 中间件自动生成（如 ShardingSphere 的 keyGenerateStrategy）

优点

• 1.突破单机瓶颈 — 通过分片分散数据和负载，支持无限水平扩展
• 2.提升查询性能 — 每个分片数据量更小，查询更快
• 3.提高可用性 — 单个分片故障不影响整体服务
• 4.灵活扩展 — 可以根据业务增长动态增加分片

缺点

• 1.跨分片查询复杂 — 非分片键查询需要扫描所有分片，性能较差
• 2.分布式事务困难 — 跨分片事务的一致性保证非常复杂
• 3.运维成本高 — 分片管理、数据迁移、扩容操作复杂
• 4.开发复杂度增加 — 需要处理数据路由、结果合并等逻辑

总结

分库分表是解决大数据量存储和访问问题的有效手段。垂直分片按照业务模块拆分，适合系统解耦；水平分片按照数据行拆分，适合单表数据量过大的场景。分片键的选择直接决定了系统的查询性能和扩展能力，需要综合考虑业务查询模式和数据分布。在技术选型上，ShardingSphere 等中间件可以大大降低分库分表的开发和运维成本，但仍需要团队具备分布式数据库的管理经验。

关键知识点

• 数据库主题一定要同时看数据模型、读写模式和执行代价。
• 很多性能问题不是 SQL 语法问题，而是索引、统计信息、事务和数据分布问题。
• 高可用、备份、迁移和治理与查询优化同样重要。
• 先把数据模型、访问模式和执行代价绑定起来理解。

项目落地视角

• 所有优化前后都保留执行计划、样本 SQL 和关键指标对比。
• 上线前准备回滚脚本、备份点和校验方案。
• 把连接池、锁等待、慢查询和容量增长纳入日常巡检。
• 保留执行计划、样本 SQL、索引定义和优化前后指标。

常见误区

• 脱离真实数据分布讨论索引或分片。
• 只看单条 SQL，不看整条业务链路的事务和锁。
• 把测试环境结论直接等同于生产环境结论。
• 脱离真实数据分布设计索引。

进阶路线

• 继续向执行计划、存储引擎、复制机制和数据治理层深入。
• 把主题与 ORM、缓存、消息队列和归档策略联动起来思考。
• 沉淀成数据库设计规范、SQL 审核规则和变更流程。
• 继续深入存储引擎、复制机制、归档与冷热分层治理。

适用场景

• 当你准备把《分库分表策略》真正落到项目里时，最适合先在一个独立模块或最小样例里验证关键路径。
• 适合数据建模、查询优化、事务控制、高可用和迁移治理。
• 当系统开始遇到慢查询、锁冲突、热点数据或容量增长时，这类主题价值最高。

落地建议

• 先分析真实查询模式、数据量级和写入特征，再决定索引或分片策略。
• 所有优化结论都结合执行计划、样本数据和监控指标验证。
• 高风险操作前准备备份、回滚脚本与校验 SQL。

排错清单

• 先确认瓶颈在 CPU、I/O、锁等待、网络还是 SQL 本身。
• 检查执行计划是否走错索引、是否发生排序或全表扫描。
• 排查长事务、隐式类型转换、统计信息过期和参数嗅探。

复盘问题

• 如果把《分库分表策略》放进你的当前项目，最先要验证的输入、输出和失败路径分别是什么？
• 《分库分表策略》最容易在什么规模、什么边界条件下暴露问题？你会用什么指标或日志去确认？
• 相比默认实现或替代方案，采用《分库分表策略》最大的收益和代价分别是什么？

延伸阅读

• 连接池优化
• 索引原理与设计
• 数据迁移实战
• 数据库迁移策略