MySQL 分库分表 + 平滑扩容方案（秒懂+史上最全）

45岁资深老架构师尼恩

于 2025-07-14 09:23:15 发布

阅读量1.4k

点赞数 15

CC 4.0 BY-SA版权

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/crazymakercircle/article/details/149323224

本文原文

MySQL 分库分表及其平滑扩容方案

尼恩说在前面：

在40岁老架构师尼恩的读者交流群(50+)中，最近有小伙伴拿到了一线互联网企业如得物、阿里、滴滴、极兔、有赞、shein 希音、shopee、百度、网易的面试资格，遇到很多很重要的面试题：

你们的DB分了几个集群、几个库、几个表？

如果数据库已经到了写瓶颈怎么办？优化写操作或分库

如果数据库已经到了读瓶颈怎么办？优化读操作或加从库或分库或分表

核心业务或是维护的业务数据库或数据表的数据量、TPS、QPS

是否使用过分区表，按照什么分区的，每个分区数据量是多少

什么时候需要分库分表？

分库分表的时候，是分库还是分表？还是既分库又分表？

分多少库？分多少表？如何计算的？

万一容量预估得不准，预估少了怎么办？

前几天小伙伴面试阿里，遇到了这个问题。但是由于没有回答好，导致面试挂了。

小伙伴面试完了之后，来求助尼恩。那么，遇到这个问题，该如何才能回答得很漂亮，才能让面试官刮目相看、口水直流。

所以，尼恩给大家做一下系统化、体系化的梳理，使得大家内力猛增，可以充分展示一下大家雄厚的 “技术肌肉”，让面试官爱到 “不能自已、口水直流”，然后实现”offer直提”。

当然，这道面试题，以及参考答案，也会收入咱们的《尼恩Java面试宝典》V145版本PDF集群，供后面的小伙伴参考，提升大家的 3高架构、设计、开发水平。

最新《尼恩架构笔记》《尼恩高并发三部曲》《尼恩Java面试宝典》的PDF，请关注本公众号【技术自由圈】获取，后台回复：领电子书

基本思路

起点是：为什么分库分表

如果项目经历里和数据库有关，看起来可以用分区表得内容，就会问：为什么要分库分表？

一：为什么分库分表

（1）IO 瓶颈

第一种：磁盘读 IO 瓶颈，热点数据太多，数据库缓存放不下，每次查询时会产生大量的 IO，降低查询速度 -> 分库和垂直分表。

第二种：网络 IO 瓶颈，请求的数据太多，网络带宽不够 -> 分库。

（2）CPU 瓶颈

第一种：SQL 问题，如 SQL 中包含 join，group by，order by，非索引字段条件查询等，增加 CPU 运算的操作 -> SQL 优化，建立合适的索引，在业务 Service 层进行业务计算。

第二种：单表数据量太大，查询时扫描的行太多，SQL 效率低，CPU 率先出现瓶颈 -> 水平分表。

不管是 IO 瓶颈，还是 CPU 瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。

在业务 Service 来看就是，可用数据库连接少甚至无连接可用。接下来就崩溃。

一句话总结分库分表，那就是数据库本身出现了性能问题，而且这些性能问题已经没办法通过SQL优化、索引优化之类的手段解决了

进一步将分库分表和分区表、读写分离进行对比，从硬件资源、并发、数据量 三个引起性能瓶颈的角度去分析

在分库分表之前优先考虑分区表和读写分离。

因为这两种方案和分库分表比起来都更简单、好维护

如果是数据库本身硬件资源不足，那么不管是分区表还是读写分离都难以解决问题。

比如数据库网络带宽不够了，分区表肯定解决不了；

如果是写操作引发的网络带宽不够，读写分离增加从库也没法解决。

二：分库分表的两个原则

二个原则：

(1) 如果是数据库本身的硬件资源引起的性能瓶颈，就要分数据源，有更多的主从集群

(3) 如果是单表数据量过大、锁竞争等跟表维度相关的资源引发的性能问题，分表就可以了

容量估算

主要依据两点：现有数据和增长趋势

存量数据

是最好处理的，但是不是所有的存量数据都需要进行分库分表，部分不重要的、用不上的、历史悠久的数据，不如直接归档或是放到大数据平台上。真正需要计算的是那些线上继续查询的数据的量

增长趋势

计算难点，需要考虑现有数据增长率和数据增长率的变化趋势，也就是数据量的一阶导数和二阶导数。

主要看公司的规划

数据的增长趋势只需要根据公司的战略规划来就可以。比如说今年公司的目标是业务翻倍，那么就可以认为今年数据的增长率是 100%。

就算公司没有发布这一类的规划，但是产品经理肯定是背着 KPI 的，问一下他们也就知道了。

不过正常来说，一家公司都是有三五年规划的，照着规划来预估容量就可以了。

只需要预估未来三年就可以。但是有些公司很有钱同时害怕扩容，所以一开始可能就留足了余量，所以可以满足数十年的需要。

财大气粗型预估

一开始上来就是32个库，每个库32个表，1024张表：

基本上国内互联网肯定都够用
无论并发支撑还是数据量支撑都没问题

每个库正常承载的写入并发量是1000，那么32个库就可以承载32 * 1000 = 32000的写并发。

每个库承载1500的写并发，32 * 1500 = 48000的写并发，接近5万/s的写并发。
1024张表，假设每个表放500万数据，在MySQL里可以放50亿条数据。

每秒的5万写并发，总共50亿条数据，对于国内大部分互联网公司来说都够。

三：分库分表方案

（1）sever 端方案

sever 端方案，比如tidb 等。

这个不建议说，如果说这个的话，就没后面什么事了。

（2）代理层方式

部署一台代理服务器伪装成 MySQL 服务器，代理服务器负责与真实 MySQL 节点的对接，应用程序只和代理服务器对接。

对应用程序是透明的。

比如 MyCAT，它可以支持 MySQL, SQL Server, Oracle, DB2, PostgreSQL等主流数据库，也支持MongoDB这种新型NoSQL方式的存储，未来还会支持更多类型的存储。

MyCAT 不仅仅可以用作读写分离，以及分表分库、容灾管理，而且可以用于多租户应用开发、云平台基础设施，让你的架构具备很强的适应性和灵活性。

这个不建议说，如果说这个的话，就没后面什么事了。

（3）应用层方式

处于业务层和 JDBC 层中间，是以 JAR 包方式提供给应用调用，对代码有侵入性。

主要方案有：

淘宝网的 TDDL: 已于 2012 年关闭了维护通道，建议不要使用
当当网的 Sharding-JDBC(仍在活跃维护中)

Sharding-JDBC

当当应用框架 ddframe 是从关系型数据库模块 dd-rdb 中分离出来的数据库水平分片框架，实现透明化数据库分库分表访问，实现了 Snowflake 分片算法；

Sharding-JDBC定位为轻量Java框架，使用客户端直连数据库，无需额外部署，无其他依赖，DBA也无需改变原有的运维方式。

Sharding-JDBC分片策略灵活，可支持等号、between、in等多维度分片，也可支持多分片键。

SQL解析功能完善，支持聚合、分组、排序、limit、or等查询，并支持Binding Table以及笛卡尔积表查询。

Sharding-JDBC直接封装JDBC API，可以理解为增强版的JDBC驱动，旧代码迁移成本几乎为零。

可适用于任何基于Java的ORM框架，如JPA、Hibernate、Mybatis、Spring JDBC Template或直接使用JDBC。
可基于任何第三方的数据库连接池，如DBCP、C3P0、 BoneCP、Druid等。
理论上可支持任意实现JDBC规范的数据库。虽然目前仅支持MySQL，但已有支持Oracle、SQLServer等数据库的计划。

咱们基于这个方案去说。

四：分库分表分片键的设计

（1）连续分片

根据特定字段(比如用户ID、订单时间)的范围，值在该区间的，划分到特定节点。

优点：集群扩容后，指定新的范围落在新节点即可，无需进行数据迁移。

缺点：如果按时间划分，数据热点分布不均(历史数冷当前数据热)，导致节点负荷不均。



// ======================== 1. 连续分片（按范围） ========================
class RangeSharding {
    static final long NODE0_MAX = 1000_0000L;
    static final long NODE1_MAX = 2000_0000L;

    int shard(long userId) {
        if (userId <= NODE0_MAX) return 0;
        if (userId <= NODE1_MAX) return 1;
        return 2;
    }
}

（2）ID取模分片

缺点：扩容后需要迁移数据。


// ======================== 2. ID 取模分片 ========================
class ModSharding {
    final int nodeCount;

    ModSharding(int nodeCount) { this.nodeCount = nodeCount; }
    
    int shard(long id) {
        return (int) (id % nodeCount);
    }
}

实现简单直观： 逻辑清晰，计算简单（shard = id % N），易于理解和实现。

缺点：

1、扩容复杂，数据迁移量大：

这是最致命的缺点。当需要增加分片数量（N 变大）时，原有的取模计算规则 (% N) 会失效，绝大多数数据行 ((N_new - N_old) / N_new 比例) 需要根据新的模数 (% N_new) 重新计算并迁移到新的正确分片上。这个过程非常耗时耗力，对业务影响大。

2、范围查询效率低：

对于按分片键进行的范围查询（如 WHERE id BETWEEN 1000 AND 2000），数据很可能分散在多个分片上，需要查询所有相关分片（广播查询或合并查询），效率较低。

（3）一致性Hash算法

优点：扩容后无需迁移数据。



// ======================== 3. 一致性 Hash 分片 ========================
import java.util.*;
class ConsistentHashSharding {
    final int VIRTUAL = 200;                               // 虚拟节点数
    final SortedMap<Integer, Integer> ring = new TreeMap<>();
    final List<Integer> nodes = Arrays.asList(0,1,2);

    ConsistentHashSharding() {
        nodes.forEach(node -> {
            for (int i = 0; i < VIRTUAL; i++) {
                ring.put(hash(node + "#" + i), node);
            }
        });
    }
    int shard(String key) {
        int h = hash(key);
        if (!ring.containsKey(h)) h = ring.ceilingKey(h);
        return ring.get(h);
    }
    int hash(String s) { return s.hashCode() & 0x7fffffff; }
}

优点：

1、扩容缩容数据迁移量小

这是核心优势。当增加或减少节点时，只有环上相邻节点之间的部分数据需要迁移，大部分数据（通常远高于50%）不需要移动。

2、数据分布相对均衡：

通过虚拟节点机制，可以将物理节点映射到环上的多个虚拟点，从而更均匀地分布数据，减少因节点增减或节点性能差异导致的负载不均。

缺点：

**1、实现相对复杂：**

比简单的取模分片要复杂，需要维护哈希环和虚拟节点映射。

（4）Snowflake 分片

首先需要明确：Snowflake 本身是一种分布式ID生成算法，不是直接的分片策略。

Snowflake 是一个高并发黄金组件，内容太多，具体请参考尼恩的文章：

京东面试：600Wqps高并发ID如何设计？时钟回拨如何解决？

但它生成的ID具有内在特性，常被用作分片键，并配合其他策略（主要是取模或一致性哈希）进行分片。

这里分析的是利用 Snowflake ID 作为分片键的优缺点。



// ======================== 4. Snowflake 分片 ========================
class SnowflakeSharding {
    final int nodeBits = 5;                // 节点位宽
    final long NODE_MASK = (1L << nodeBits) - 1;

    /* Snowflake: 41b 时间 + 5b 节点 + 12b 序列 */
    int shard(long snowId) {
        return (int) ((snowId >> 12) & NODE_MASK);
    }
}

优点：
1、全局唯一、趋势递增：

Snowflake ID 在分布式系统内保证唯一性，且整体趋势随时间递增（因为高位是时间戳）。这避免了主键冲突。

2、减少索引碎片：

趋势递增的特性在作为主键时，可以减少数据库索引的碎片化（新数据插入总是在索引末尾）。

缺点：

1、依赖时钟：

Snowflake 算法严重依赖机器时钟。如果发生时钟回拨（服务器时间意外倒退），可能导致生成的ID重复（需要特殊处理机制）或服务暂停。

四：分库分表引入的问题

（1）分布式事务

参见分布式事务的解决方案

由于两阶段/三阶段提交对性能损耗大，可改用事务补偿机制。

（2）跨节点 JOIN

对于单库 JOIN，MySQL 原生就支持；

对于多库，出于性能考虑，不建议使用 MySQL 自带的 JOIN，可以用以下方案避免跨节点 JOIN：

全局表: 一些稳定的共用数据表，在各个数据库中都保存一份；
字段冗余: 一些常用的共用字段，在各个数据表中都保存一份；
应用组装：应用获取数据后再组装。

另外，某个 ID 的用户信息在哪个节点，他的关联数据(比如订单)也在哪个节点，可以避免分布式查询。

跨节点 JOIN 问题如何解决，可以参考尼恩已经发布的异构存储设计文章：

字节面试：百亿级存储，怎么设计？只是分库分表？

100亿级任务调度篇：从0到1, 从入门到 XXLJOB 工业级使用

高并发搜索ES圣经：从0到1, 从入门到 ElasticSearch 工业级使用

超级底层：10WQPS/PB级海量存储HBase/RocksDB，底层LSM结构是什么？

百亿级存储架构： ElasticSearch+HBase 海量存储架构与实现

（3）跨节点聚合

只能在应用程序端完成。

但对于分页查询，每次大量聚合后再分页，性能欠佳。

跨节点 JOIN 问题如何解决，可以参考尼恩已经发布的上面的异构存储设计的文章。

（4）节点扩容和数据迁移

节点扩容后，新的分片规则导致数据所属分片有变，因而需要迁移数据。

：节点扩容和数据迁移

方案1：全迁移 + 停服扩容（简单方案）

如果增加的节点数和扩容操作没有规划，那么绝大部分数据所属的分片都有变化，需要在分片间迁移：

预估迁移耗时，发布停服公告；
停服(用户无法使用服务)，使用事先准备的迁移脚本，进行数据迁移；
修改为新的分片规则；
启动服务器。

最好别这样，有点不太靠谱，既然分库分表，就说明数据量实在太大，这么玩可能玩脱。

从单库单表迁移到分库分表时，数据量并不是很大，单表最大也就两三千万。

写个工具，多弄几台机器并行跑，1小时数据就导完了。

但如果是：3个库+12个表。

跑一段时间，数据量都1亿~2亿了，光是导2亿数据，都要导个几个小时，6点，刚刚导完数据，还要搞后续的修改配置，重启系统，测试验证，大半天才搞完。

方案2：微迁移+ 不停服扩容（简单方案）

采用双倍扩容策略，减少数据迁移。

扩容前每个节点的数据，有一半要迁移至一个新增节点中，对应关系比较简单。

具体操作如下(假设已有 2 个节点 A/B，要双倍扩容至 A/A2/B/B2 这 4 个节点)：

无需停止应用服务器；
新增两个数据库 A2/B2 作为从库，设置主从同步关系为：A=>A2、B=>B2，直至主从数据同步完毕(早期数据可手工同步)；
调整分片规则并使之生效：

原 ID%2=0 => A 改为 ID%4=0 => A, ID%4=2 => A2；

原 ID%2=1 => B 改为 ID%4=1 => B, ID%4=3 => B2。
解除数据库实例的主从同步关系，并使之生效；

此时，四个节点的数据都已完整，只是有冗余(多存了和自己配对的节点的那部分数据)，择机清除即可(过后随时进行，不影响业务)。