Lachlan✨

菜单

Lachlan
发布于 2025-09-18 / 4 阅读
0

MySQL 分库分表（理论）

#技术

感谢光老师的奉献

参考资料：

  1. https://www.cnblogs.com/luedong/p/15940999.html MySQL 分库分表及其平滑扩容方案

  2. https://zhuanlan.zhihu.com/p/375072815 分库分表：中间件最全方案对比

  3. https://zhuanlan.zhihu.com/p/490261031 MySQL 中间件汇总比较

  4. https://cloud.tencent.com/developer/article/1451045 【干货】MySQL 分库分表及其平滑扩容方案

  5. https://tech.meituan.com/2016/11/18/dianping-order-db-sharding.html 大众点评订单系统分库分表实践

  6. https://developer.aliyun.com/article/949608 使用 ShardingSphere 实操MySQL分库实战

在实际开发中，会遇到核心业务表增长很快，数据量很大，MySQL 写入性能瓶颈的问题，这时需要根据业务的特性考虑分库分表，可以调研下相关的解决方案，主要有两种方案：代码改造（数据库中间件mycat，sharding-sphere）和分布式数据库（实际业务中使用比较多的有 PingCAP TiDB，阿里云 DRDS），可以优先使用分布式数据库方案，虽然成本会有所增加，但对应用程序没有侵入性，同时也可以比较好的支撑业务增长和系统快速迭代，今天就聊这么多，希望对您有所收获。

1. 总述

  1. https://zhuanlan.zhihu.com/p/137368446 MySQL：互联网公司常用分库分表方案汇总！

  2. https://www.cnblogs.com/awan-note/p/13993917.html 亿级大表分库分表实战总结（万字干货，实战复盘）

todo

1.1. 数据库瓶颈

不管是IO瓶颈，还是CPU瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。在业务Service来看就是，可用数据库连接少甚至无连接可用。接下来就可以想象了吧（并发量、吞吐量、崩溃）。

1.1.1. IO瓶颈

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

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

1.1.2. CPU瓶颈

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

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

1.2. 分库分表工具

  1. sharding-sphere：jar，前身是sharding-jdbc；

  2. TDDL：jar，Taobao Distribute Data Layer；

  3. Mycat：中间件。

注：工具的利弊，请自行调研，官网和社区优先。

1.3. 分库分表步骤

根据容量（当前容量和增长量）评估分库或分表个数 -> 选key（均匀）-> 分表规则（hash或range等）-> 执行（一般双写）-> 扩容问题（尽量减少数据的移动）。

系统绝对并发量上来了，并且可以抽象出单独的业务模块，做服务化。

1.4. todo

2. 如何分库分表

  1. https://developer.aliyun.com/article/1608413 一篇文章搞懂MySQL的分库分表，从拆分场景、目标评估、拆分方案、不停机迁移、一致性补偿等方面详细阐述MySQL数据库的分库分表方案

  2. https://www.elibaron.com/db/mysql/mysql-x-sharding-detail.html MySQL - 分表分库详解

分库分表，都有垂直分、水平分的场景。

2.1. 分库

2.1.1. 为什么要分库？

往往是数据库出现性能瓶颈，需要拆分不同的库到不同的数据库实例中去，将量分散出去， 减轻单一数据库实例的负载压力。

2.1.2. 垂直分库

将单一数据库（或实例）中的不同表，拆分到多个数据库（或实例）中去。

2.1.3. 水平分库

水平分库是指，将表的数据量切分到不同的数据库服务器上，每个服务器具有相同的库和表，只是表中的数据集合不一样。它可以有效地缓解单机单库的性能瓶颈和压力。

2.2. 分表

2.2.1. 为什么要分表？

2.2.2. 垂直分表

一张表中的部分字段是常用的，另一部分是不常用的，垂直分表，可以减少一行记录的数据大小，提高单表存储行数（记录数），提高查询效率。

2.2.3. 水平分表

  1. https://blog.csdn.net/agonie201218/article/details/110823552

如果一个表的数据量太大，可以按照某种规则（如hash取模、range），把数据切分到多张表去。一张订单表，按时间range拆分如下：

2.3. 按日期分表（eg 月结账单场景）

对于账务或者计费类系统，每天晚上都会做前一天的日结或日账任务，每月的1号都会做月结或月账任务，任务执行完之后相关表的数据都已静态化了（业务层不需要这些数据），根据业务的特性，可以按月创建表，比如对于账单表 bills，就可以创建按月分表（十月份表bills_202010，202011十一月份表），出完月账任务之后，就可以归档到历史库了，用于数据仓库ETL来做分析报表，确认数据都同步到历史库之后就可以删除这些表释放空间。

2.4. 按地理位置分表

略

2.5. MySQL 分区表（PARTITION）

3. 水平分库分表策略

分库分表策略一般有几种，使用于不同的场景。

3.1. range 范围（按月划分的业务可以，其他不行）

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

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

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

3.2. ID 取模分片

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

3.3. hash 取模

注意这里指的是非一致性哈希。

优点：数据散列均匀。

缺点：扩容后需要迁移几乎全部数据。

3.4. range + hash 取模混合

3.5. 一致性 Hash 取模（推荐）

  1. https://tech.meituan.com/2016/11/18/dianping-order-db-sharding.html 大众点评订单系统分库分表实践

例如一致性哈希 mod 2^n，易于水平扩展。假设 n=2，旧版本是 mod 4，分配到 0-3 中；新版本扩容 n=3，则 mod 8，对于所有的老数据，迁移只需要迁移部分数据，准确来说是一半数据，如下图。操作时，手动取模拷贝到新库（表），老库的值可以在变更完之后择期清除，不影响业务。

优点：数据散列均匀，扩容后只需迁移部分数据。易于水平扩展。

3.6. Snowflake 分片

  1. https://blog.csdn.net/lby0307/article/details/82749884 数据库分库分表——扩容无须数据迁移的分片算法

网上说优点是扩容后无需迁移数据。但是翻了很多资料，发现所谓的无需迁移，就是在分片之前，设置好雪花 ID 的结构，例如 workId，然后在分片策略时，用类似于 hash 的方式直接路由到对应表中。

本质就是 Hash，这样的方式，会有数据倾斜问题，故不采用。

3.6.1. 示例

常见的分库分表方案大都用主键mod一个数（如分为8个库，则 id % 8 根据余数决定落到哪个分片）。此种方案中，如果要拓展数据库将是十分复杂的事情（例如拓展为10个，则代码需要改为 id % 10 之前的旧数据也要做迁移）。我们希望有一种支持自由规划无须数据迁移和修改路由代码的Sharding扩容方案。

方案设计：

在Twitter-Snowflake分库分表算法主键算法本身就带时间戳，我们可以基于它实现无需数据迁移的扩容方案。Snowflake生成的ID是一个64位的Long值，通过主键可以算出两个维度的值：

  1. 时间维度，主键的前42位就是一个以毫秒为单位的时间戳。

  2. 分片维度，对主键做Hash运算（为了得到的hash值尽可能散，使用MurmurHash算法），可以得到一个32位的hash值，我们以它的前16位作为数据库分片值，后16位作为表分片值。

如此一来，只需根据时间戳，指定某一时间戳后使用新的分片规则，即可不迁移数据对数据库进行扩容。

4. 全局ID生成策略

  1. https://cloud.tencent.com/developer/article/1451045 【干货】MySQL 分库分表及其平滑扩容方案

4.1. 自动增长列

优点：数据库自带功能，有序，性能佳。

缺点：单库单表无妨，分库分表时如果没有规划，ID可能重复。解决方案：

4.1.1. 设置自增偏移和步长
## 假设总共有 10 个分表
## 级别可选: SESSION(会话级), GLOBAL(全局)
SET @@SESSION.auto_increment_offset = 1; ## 起始值, 分别取值为 1~10
SET @@SESSION.auto_increment_increment = 10; ## 步长增量

如果采用该方案，在扩容时需要迁移已有数据至新的所属分片。

4.1.2. 全局ID映射表

在全局 Redis 中为每张数据表创建一个 ID 的键，记录该表当前最大 ID；

每次申请 ID 时，都自增 1 并返回给应用；

Redis 要定期持久至全局数据库。

4.2. UUID(128位)

在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的。通常平台会提供生成UUID的API。

UUID 由4个连字号(-)将32个字节长的字符串分隔后生成的字符串，总共36个字节长。形如：550e8400-e29b-41d4-a716-446655440000。

UUID 的计算因子包括：以太网卡地址、纳秒级时间、芯片ID码和许多可能的数字。

UUID 是个标准，其实现有几种，最常用的是微软的 GUID(Globals Unique Identifiers)。

  • 优点：简单，全球唯一；

  • 缺点：存储和传输空间大，无序，性能欠佳。

4.3. COMB(组合)

参考资料：The Cost of GUIDs as Primary Keys

组合 GUID(10字节) 和时间(6字节)，达到有序的效果，提高索引性能。

4.4. Snowflake(雪花) 算法

参考资料：twitter/snowflake，Snowflake 算法详解

Snowflake 是 Twitter 开源的分布式 ID 生成算法，其结果为 long(64bit) 的数值。其特性是各节点无需协调、按时间大致有序、且整个集群各节点单不重复。该数值的默认组成如下(符号位之外的三部分允许个性化调整)：

  • 1bit: 符号位，总是 0(为了保证数值是正数)。

  • 41bit: 毫秒数(可用 69 年)；

  • 10bit: 节点ID(5bit数据中心 + 5bit节点ID，支持 32 * 32 = 1024 个节点)

  • 12bit: 流水号(每个节点每毫秒内支持 4096 个 ID，相当于 409万的 QPS，相同时间内如 ID 遇翻转，则等待至下一毫秒)

参考《三、分布式系统 - 全局唯一ID实现方案》

5. 分库分表引入的问题

5.1. 分布式事务

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

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

5.2. 跨节点 JOIN

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

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

全局表: 一些稳定的共用数据表，在各个数据库中都保存一份；

字段冗余: 一些常用的共用字段，在各个数据表中都保存一份；

应用组装：应用获取数据后再组装。

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

5.3. 跨节点聚合

只能在应用程序端完成。

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

5.4. 节点扩容

  1. https://loufengman.github.io/2020/02/20/MySQL%E5%88%86%E5%BA%93%E5%88%86%E8%A1%A8%E7%A0%94%E7%A9%B6/ MySQL分表研究

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

6. MySQL水平切割的实现方式

6.1. 手动实现

开发者可以在应用程序中根据分库分表规则编写逻辑，将请求路由到对应的数据库或表。

def route_db(user_id):
    return "db_{}".format(user_id % 10)

def route_table(order_id):
    return "order_{}".format(order_id % 100)

6.2. 自动化工具

市面上有许多自动化工具可以帮助实现MySQL水平切割，如：

  1. ShardingSphere：一个开源的数据库中间件，提供分库分表、读写分离、分布式事务等功能。

  2. Mycat：一个开源的数据库中间件，支持分库分表、读写分离等功能。

7. 分布式数据库

7.1. todo

7.2. todo

记录一次MySQL大数据量实例搭建...