登录 注册

数据库分库分表架构设计：从垂直拆分到水平拆分，MySQL分片策略与数据迁移方案

D
dashi100 2025-11-20T21:45:36+08:00
0 0 56

数据库分库分表架构设计：从垂直拆分到水平拆分，MySQL分片策略与数据迁移方案

引言：为何需要分库分表？

随着业务规模的持续增长，单机数据库（如 MySQL）在面对高并发读写、海量数据存储和复杂查询时，逐渐暴露出性能瓶颈。主要体现在：

  • 连接数限制：单实例数据库最大连接数有限（如默认 151），难以支撑大规模并发请求。
  • 磁盘与内存瓶颈：数据量过大导致索引失效、查询变慢，甚至引发全表扫描。
  • 可用性风险：单点故障（SPOF）一旦发生，整个系统不可用。
  • 运维困难：备份恢复耗时长，扩容过程复杂且影响线上服务。

为应对这些挑战，分库分表（Sharding） 成为大型互联网系统中不可或缺的数据库架构设计手段。它通过将数据分散到多个数据库或表中，实现横向扩展能力，提升系统的吞吐量、可用性和可维护性。

本文将深入探讨从垂直拆分到水平拆分的完整演进路径，结合实际案例分析 MySQL 分片策略选择、数据迁移方案设计、分布式事务处理 等关键技术，并提供可落地的代码示例与最佳实践建议。

一、分库分表的核心概念与目标

1.1 什么是分库分表？

分库分表 是一种将原本集中存储于单一数据库中的数据，按照一定规则分布到多个物理数据库（分库）和多个逻辑表（分表）中的架构模式。其核心思想是“把大问题拆成小问题”，从而缓解单点压力。

  • 分库（Database Sharding）：将数据按维度拆分到不同的数据库实例中。
  • 分表（Table Sharding）：在同一数据库内，将一张大表拆分为多个结构相同的子表。

✅ 示例：用户表 user 有 10 亿条记录 → 拆分为 16 个数据库，每个数据库含 16 张表 → 共计 256 张表，每张表约 3800 万行。

1.2 分库分表的目标

目标 说明
提升性能 降低单表/单库负载，提高查询效率
增强可扩展性 支持动态添加节点，水平扩展
提高可用性 避免单点故障，支持故障隔离
便于运维 按模块或业务拆分，利于备份、监控和管理

⚠️ 注意：分库分表并非银弹。它会带来复杂性，需权衡收益与成本。

二、分库分表的两种基本策略：垂直拆分 vs 水平拆分

2.1 垂直拆分（Vertical Sharding）

定义

按业务功能或字段维度将原表拆分成多个独立的表或数据库，每个表只包含部分列或相关业务数据。

应用场景

  • 表字段过多（如用户信息 + 订单信息 + 财务信息）
  • 不同业务模块访问频率差异大
  • 某些字段频繁更新，其他字段很少变动

实际案例：电商系统用户表拆分

原始 user 表结构如下：

CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20),
    avatar_url VARCHAR(255),
    address TEXT,
    created_at DATETIME,
    updated_at DATETIME,
    last_login DATETIME,
    login_count INT,
    balance DECIMAL(10,2),
    credit_score INT,
    is_verified BOOLEAN
);

该表包含用户基本信息、财务信息、登录行为等，数据量大且热点不一。

拆分策略
拆分维度 拆分后表名 包含字段
用户基础信息 user_basic id, username, email, phone, avatar_url, created_at, updated_at
地址信息 user_address id, address
登录日志 user_login_log id, last_login, login_count
财务信息 user_finance id, balance, credit_score, is_verified

✅ 优势：

  • 查询性能提升：高频查询仅涉及 user_basic，无需加载无关字段。
  • 可独立部署：财务模块可部署在专用数据库中，保障安全。
  • 易于归档：历史登录日志可单独归档。
缺点
  • 多表关联查询开销大（JOIN）。
  • 事务一致性更难保证。
  • 需要中间层统一路由（如 MyCat、ShardingSphere）。

代码示例：使用 ShardingSphere 进行垂直拆分配置

# application.yml - ShardingSphere 配置
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,ds2,ds3
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.100:3306/user_basic_db?useSSL=false&serverTimezone=UTC
        username: root
        password: password
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.101:3306/user_address_db?useSSL=false&serverTimezone=UTC
        username: root
        password: password
      ds2:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.102:3306/user_login_db?useSSL=false&serverTimezone=UTC
        username: root
        password: password
      ds3:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.103:3306/user_finance_db?useSSL=false&serverTimezone=UTC
        username: root
        password: password

    rules:
      sharding:
        tables:
          user_basic:
            actual-data-nodes: ds0.user_basic
          user_address:
            actual-data-nodes: ds1.user_address
          user_login_log:
            actual-data-nodes: ds2.user_login_log
          user_finance:
            actual-data-nodes: ds3.user_finance

💡 注：此配置由 ShardingSphere 解析并自动路由请求至对应数据库。

2.2 水平拆分（Horizontal Sharding）

定义

将同一张表的数据根据某个分片键（Shard Key） 按照一定算法分配到多个分表或分库中，各分表结构一致。

应用场景

  • 单表数据量超过千万级，查询缓慢。
  • 高频插入/删除操作，存在锁竞争。
  • 需要支持线性扩展。

分片键选择原则

选择标准 说明
高区分度 如用户 ID、订单号，避免数据倾斜
高频访问 经常用于查询条件
不易变更 一旦确定不宜修改，否则迁移成本高
无业务语义冲突 如不能用“城市”作为分片键（城市可能变化）

✅ 推荐：用户主键 user_id、订单编号 order_id、时间戳等。

常见分片算法

算法 描述 适用场景
一致性哈希 基于哈希值映射，节点增减影响小 分布式缓存、跨机房部署
取模分片 shard_key % N 简单高效，适合固定数量分片
Range 分片 按范围划分（如时间区间） 日志、流水表
表达式分片 自定义表达式（如 id % 4 + 1） 灵活控制
示例：基于用户 ID 的取模分片（4 个库 × 4 个表）

假设我们有 4 个数据库（db_0, db_1, db_2, db_3），每个库下有 4 张表（user_0, user_1, ..., user_3）。

分片规则如下：

// Java 伪代码：计算分片位置
public int getDbIndex(long userId) {
    return (int)(userId % 4); // 0~3
}

public int getTableIndex(long userId) {
    return (int)(userId % 4); // 0~3
}

📌 结果：user_id = 12345 → db_1.user_1

代码示例：使用 ShardingSphere 实现水平分片

# application.yml - ShardingSphere 水平分片配置
spring:
  shardingsphere:
    datasource:
      names: ds0,ds1,ds2,ds3
      ds0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.100:3306/db_0?useSSL=false&serverTimezone=UTC
        username: root
        password: password
      ds1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.101:3306/db_1?useSSL=false&serverTimezone=UTC
        username: root
        password: password
      ds2:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.102:3306/db_2?useSSL=false&serverTimezone=UTC
        username: root
        password: password
      ds3:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.103:3306/db_3?useSSL=false&serverTimezone=UTC
        username: root
        password: password

    rules:
      sharding:
        tables:
          user:
            actual-data-nodes: ds${0..3}.user_${0..3}
            table-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: user-table-inline
            database-strategy:
              standard:
                sharding-column: user_id
                sharding-algorithm-name: user-db-inline

        sharding-algorithms:
          user-table-inline:
            type: INLINE
            props:
              algorithm-expression: user_${user_id % 4}
          user-db-inline:
            type: INLINE
            props:
              algorithm-expression: ds_${user_id % 4}

✅ 使用 INLINE 算法，支持表达式动态生成数据源和表名。

🔄 执行流程：

  1. 用户发起查询 SELECT * FROM user WHERE user_id = 12345
  2. ShardingSphere 根据 user_id % 4 = 1，决定去 ds1.user_1
  3. 代理执行真实 SQL 到目标数据库

三、读写分离架构设计

3.1 为什么需要读写分离？

  • 写操作通常较少但耗时长（事务、锁）。
  • 读操作频繁且并发高。
  • 主库承担读写压力，容易成为瓶颈。

3.2 架构设计

应用层 → 中间件（如 MyCat / ShardingSphere） → Master（主库） + Slave（从库）
                              ↑
                     读请求走从库，写请求走主库

3.3 实现方式

方案一：中间件自动路由（推荐）

以 ShardingSphere 为例，配置读写分离：

# application.yml
spring:
  shardingsphere:
    datasource:
      names: master,slave0,slave1
      master:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.100:3306/db?useSSL=false&serverTimezone=UTC
        username: root
        password: password
      slave0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.101:3306/db?useSSL=false&serverTimezone=UTC
        username: root
        password: password
      slave1:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.1.102:3306/db?useSSL=false&serverTimezone=UTC
        username: root
        password: password

    rules:
      readwrite-splitting:
        data-sources:
          ms:
            write-data-source-name: master
            read-data-source-names:
              - slave0
              - slave1
            load-balancer-name: round-robin

✅ 读请求自动分发到 slave0 和 slave1，采用轮询负载均衡。

方案二：应用层手动控制

@Service
public class UserService {

    @Autowired
    private DataSource masterDataSource;

    @Autowired
    private DataSource slaveDataSource;

    public User findById(Long id) {
        // 读操作 → 使用从库
        try (Connection conn = slaveDataSource.getConnection()) {
            String sql = "SELECT * FROM user WHERE user_id = ?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setLong(1, id);
            ResultSet rs = ps.executeQuery();
            // ...
        }
    }

    public void save(User user) {
        // 写操作 → 使用主库
        try (Connection conn = masterDataSource.getConnection()) {
            String sql = "INSERT INTO user (user_id, name) VALUES (?, ?)";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setLong(1, user.getId());
            ps.setString(2, user.getName());
            ps.executeUpdate();
        }
    }
}

⚠️ 缺点：耦合性强，维护困难。

四、分片策略选择与优化建议

4.1 分片键选择最佳实践

建议 说明
✅ 优先使用自增主键或全局唯一 ID（Snowflake） 避免热点
❌ 避免使用字符串类型（如用户名）作为分片键 哈希值不稳定
❌ 不要使用“城市”、“性别”等低区分度字段 导致数据倾斜
✅ 若业务允许，可引入“虚拟分片键” 如 user_id % 1000 作为逻辑分片键

4.2 分片数量建议

数据量 推荐分片数
< 100 万 不建议分片
100 万 ~ 1000 万 4~8 个分片
1000 万 ~ 1 亿 8~16 个分片
> 1 亿 16~64+ 个分片

🔍 原则：单分片数据量 ≤ 500 万行，避免单表过大。

4.3 分片算法对比

算法 优点 缺点 适用场景
取模分片 简单、均匀 节点扩容时数据重分布严重 小规模系统
一致性哈希 扩容影响小 实现复杂，需额外中间件 分布式缓存、大规模集群
Range 分片 易于按时间分区 时间跨度大时，某些分片过热 日志、订单流水

✅ 推荐：初期用取模，后期升级为一致性哈希或使用开源中间件（如 TiDB、MyCat）。

五、数据迁移方案设计与实施

5.1 迁移背景

当系统从单库发展到分库分表，必须进行数据迁移。常见原因包括：

  • 旧系统无法支撑当前流量
  • 新架构要求分片
  • 数据库版本升级（如从 5.7 → 8.0）

5.2 迁移策略选择

方案 说明 优缺点
双写 + 同步工具 新老系统同时写入，通过工具同步 安全，但开发成本高
在线迁移工具（如 DTS、Canal） 基于 binlog 实时捕获增量 推荐，低侵入
离线全量导出导入 停服后导出再导入 快速，但停机时间长

✅ 推荐组合策略：先全量迁移 + 再增量同步 + 最终切换

5.3 使用 Canal 进行实时数据同步

步骤 1：开启 MySQL Binlog

# my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
binlog-row-image = FULL
expire_logs_days = 7

重启 MySQL 并验证：

SHOW VARIABLES LIKE 'binlog_format';
-- 应返回：ROW

步骤 2：部署 Canal Server

# 下载 Canal
wget https://github.com/alibaba/canal/releases/download/canal-1.1.5/canal.deployer-1.1.5.tar.gz
tar -zxvf canal.deployer-1.1.5.tar.gz

# 修改配置
vim conf/example.instance

# example.instance
destination=example
hostname=192.168.1.100
port=3306
username=root
password=password
databaseName=user_db
tableNames=.*

启动 Canal Server：

sh bin/startup.sh

步骤 3：编写客户端消费数据

public class CanalClient {
    public static void main(String[] args) throws InterruptedException {
        InetSocketAddress address = new InetSocketAddress("192.168.1.100", 11111);
        CanalConnector connector = CanalConnectors.newSingleConnector(address, "example", "", "");

        try {
            connector.connect();
            connector.subscribe(".*\\..*");
            connector.rollback();

            while (true) {
                Message message = connector.get(1000);
                if (message.getEntries().size() == 0) {
                    Thread.sleep(1000);
                    continue;
                }

                for (Entry entry : message.getEntries()) {
                    if (entry.getEntryType() == EntryType.ROWDATA) {
                        RowChange rowChange = RowChange.parseFrom(entry.getStoreValue());
                        EventType eventType = rowChange.getEventType();

                        System.out.println("表名：" + entry.getHeader().getTableName());
                        System.out.println("操作类型：" + eventType);

                        for (RowData rowData : rowChange.getRowDatasList()) {
                            if (eventType == EventType.DELETE) {
                                System.out.println("删除数据：" + rowData.getBeforeColumnsList());
                            } else if (eventType == EventType.INSERT || eventType == EventType.UPDATE) {
                                System.out.println("插入/更新数据：" + rowData.getAfterColumnsList());
                            }
                        }
                    }
                }
            }
        } finally {
            connector.disconnect();
        }
    }
}

✅ 该客户端可将增量数据写入新分片数据库。

六、分布式事务处理

6.1 问题本质

分库分表后，一个业务操作可能涉及多个数据库，传统本地事务无法保证一致性。

例如：用户转账 → 从 A 库扣款，向 B 库加款。

6.2 解决方案

方案 说明 适用场景
两阶段提交（2PC） 强一致性，但性能差，不推荐 金融系统
TCC（Try-Confirm-Cancel） 业务层面补偿，灵活 微服务
消息队列 + 本地事务表 最佳实践，异步最终一致性 通用场景

6.3 消息队列 + 本地事务表方案（推荐）

设计思路

  1. 在本地数据库创建事务日志表。
  2. 执行本地业务 + 写入事务日志。
  3. 发送消息到 MQ。
  4. MQ 消费端处理远程事务。

示例：转账业务实现

-- 本地事务日志表
CREATE TABLE transaction_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    tx_no VARCHAR(64) UNIQUE NOT NULL,
    from_user_id BIGINT NOT NULL,
    to_user_id BIGINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status ENUM('INIT', 'SUCCESS', 'FAIL') DEFAULT 'INIT',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

@Service
public class TransferService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Autowired
    private RabbitTemplate rabbitTemplate;

    @Transactional(rollbackFor = Exception.class)
    public void transfer(Long fromUserId, Long toUserId, BigDecimal amount) {
        String txNo = UUID.randomUUID().toString();

        // 1. 写入本地事务日志
        jdbcTemplate.update(
            "INSERT INTO transaction_log (tx_no, from_user_id, to_user_id, amount, status) VALUES (?, ?, ?, ?, ?)",
            txNo, fromUserId, toUserId, amount, "INIT"
        );

        // 2. 扣款（模拟调用余额服务）
        boolean success = accountService.deduct(fromUserId, amount);
        if (!success) {
            throw new RuntimeException("扣款失败");
        }

        // 3. 发送消息到 MQ
        TransferEvent event = new TransferEvent(txNo, fromUserId, toUserId, amount);
        rabbitTemplate.convertAndSend("transfer.exchange", "transfer.route", event);

        // 4. 本地事务提交
        // 注意：这里不直接抛异常，而是依赖 MQ 消费端补偿
    }
}

消费端补偿逻辑

@RabbitListener(queues = "transfer.queue")
public void handleTransfer(TransferEvent event) {
    String txNo = event.getTxNo();
    try {
        // 1. 查询本地事务日志
        Integer count = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM transaction_log WHERE tx_no = ? AND status = 'INIT'",
            Integer.class, txNo
        );

        if (count == 0) {
            log.info("已处理过，跳过");
            return;
        }

        // 2. 向目标账户加款
        boolean success = accountService.add(event.getToUserId(), event.getAmount());

        if (success) {
            jdbcTemplate.update(
                "UPDATE transaction_log SET status = 'SUCCESS' WHERE tx_no = ?",
                txNo
            );
        } else {
            // 标记失败，后续可人工介入
            jdbcTemplate.update(
                "UPDATE transaction_log SET status = 'FAIL' WHERE tx_no = ?",
                txNo
            );
        }

    } catch (Exception e) {
        log.error("转账失败", e);
        jdbcTemplate.update(
            "UPDATE transaction_log SET status = 'FAIL' WHERE tx_no = ?",
            txNo
        );
    }
}

✅ 优势：解耦、高可用、易于排查。

七、总结与最佳实践

7.1 关键结论

项目 建议
分库分表时机 单表 > 500 万行 或 并发 > 1000 QPS
分片键 选用高区分度、稳定、高频使用的字段
分片数量 8~16 个为宜，避免过度分片
读写分离 必须配置，提升读性能
数据迁移 采用“全量 + 增量”双轨策略
分布式事务 推荐使用消息队列 + 本地事务表
中间件选择 ShardingSphere（Apache）、MyCat（社区版）、TiDB（云原生）

7.2 最佳实践清单

✅ 必做项

  • 使用唯一主键作为分片键
  • 所有查询尽量带上分片键
  • 限制跨分片查询（如 JOIN、GROUP BY）
  • 开启读写分离
  • 部署监控告警（如 Prometheus + Grafana）

❌ 避免事项

  • 使用非主键字段作为分片键
  • 在分片表上执行全表扫描
  • 忽略数据迁移测试
  • 未考虑分片扩容后的数据重分布

结语

数据库分库分表是构建高并发、高可用系统的核心技术之一。从垂直拆分到水平拆分，再到读写分离与分布式事务，每一步都需深思熟虑。合理选择分片策略、利用成熟中间件、制定严谨的数据迁移计划，才能真正释放数据库的潜力。

🌟 技术不是目的，业务价值才是根本。分库分表不是为了“炫技”，而是为了

# mysql # 数据库 # 架构设计 # 数据迁移 # 分库分表

相似文章

    评论 (0)