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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
云数据库 RDS MySQL,高可用系列 2核4GB
简介: MySQL 分库分表 + 平滑扩容方案 (秒懂+史上最全)

本文原文

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

尼恩说在前面:

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

  • 你们的DB分了几个集群、几个库、几个表?
  • 如果数据库已经到了写瓶颈怎么办?优化写操作或分库
  • 如果数据库已经到了读瓶颈怎么办?优化读操作或加从库或分库或分表

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

  • 是否使用过分区表,按照什么分区的,每个分区数据量是多少
  • 什么时候需要分库分表?
  • 分库分表的时候,是分库还是分表?还是既分库又分表?
  • 分多少库?分多少表?如何计算的?
  • 万一容量预估得不准,预估少了怎么办?

基本思路

起点是:为什么分库分表

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

一:为什么分库分表

(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 的用户信息在哪个节点,他的关联数据(比如订单)也在哪个节点,可以避免分布式查询。

(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。

  • 解除数据库实例的主从同步关系,并使之生效;

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

2的幂和数据迁移的关系

大厂在容量规划时都是按照2的幂来规划的,比如428,或是8432,而且扩容的时候,也是按照2的幂进行的,基本扩容都选择容量翻倍
原因是:2的特性,在使用哈希取余来进行分库分表的时候,可以使用位运算来计算余数,非常高效。

在扩容的时候,如果扩容为原来的2倍,只需要迁移一半的数据

(3)工业级方案: 微迁移+ 不停服扩容 大厂 的操作流程

由于平台篇幅 限制, 剩下的内容,请参见 本文原文

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

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
相关文章
|
10月前
|
存储 SQL 关系型数据库
Mysql高可用架构方案
本文阐述了Mysql高可用架构方案,介绍了 主从模式,MHA模式,MMM模式,MGR模式 方案的实现方式,没有哪个方案是完美的,开发人员在选择何种方案应用到项目中也没有标准答案,合适的才是最好的。
813 3
Mysql高可用架构方案
|
9月前
|
存储 缓存 关系型数据库
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
MySQL的存储引擎是其核心组件之一,负责数据的存储、索引和检索。不同的存储引擎具有不同的功能和特性,可以根据业务需求 选择合适的引擎。本文详细介绍了MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案。
1629 57
【MySQL进阶篇】存储引擎(MySQL体系结构、InnoDB、MyISAM、Memory区别及特点、存储引擎的选择方案)
|
6月前
|
关系型数据库 MySQL 数据库
|
6月前
|
消息中间件 缓存 NoSQL
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)
|
7月前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
258 9
|
8月前
|
监控 关系型数据库 MySQL
Aurora MySQL负载突增应对策略与优化方案
通过以上策略,企业可以有效应对 Aurora MySQL 的负载突增,确保数据库在高负载情况下依然保持高性能和稳定性。这些优化方案涵盖了从架构设计到具体配置和监控的各个方面,能够全面提升数据库的响应速度和处理能力。在实际应用中,应根据具体的业务需求和负载特征,灵活调整和应用这些优化策略。
154 22
|
8月前
|
Java 关系型数据库 MySQL
MySQL 分库分表方案
本文总结了数据库分库分表的相关概念和实践,针对单张表数据量过大及增长迅速的问题,介绍了垂直和水平切分的方式及其适用场景。文章分析了分库分表后可能面临的事务支持、多库结果集合并、跨库join等问题,并列举了几种常见的开源分库分表中间件。最后强调了不建议水平分库分表的原因,帮助读者在规划时规避潜在问题。
941 20
|
8月前
|
关系型数据库 MySQL 中间件
MySQL 中如何实现分库分表?常见的分库分表策略有哪些?
在MySQL中,分库分表(Sharding)通过将数据分散到多个数据库或表中,以应对大量数据带来的性能和扩展性问题。常见策略包括:哈希分片(分布均匀,查询效率高)、范围分片(适合范围查询)、列表分片(适用于特定值查询)、复合分片(灵活性高)和动态分片(灵活应对负载变化)。每种策略各有优劣,需根据业务需求选择。常用工具如MyCAT、ShardingSphere和TDDL可简化实现过程。
|
9月前
|
SQL 关系型数据库 MySQL
数据库数据恢复—Mysql数据库表记录丢失的数据恢复方案
Mysql数据库故障: Mysql数据库表记录丢失。 Mysql数据库故障表现: 1、Mysql数据库表中无任何数据或只有部分数据。 2、客户端无法查询到完整的信息。
|
21天前
|
安全 关系型数据库 MySQL
MySQL安全最佳实践:保护你的数据库
本文深入探讨了MySQL数据库的安全防护体系,涵盖认证安全、访问控制、网络安全、数据加密、审计监控、备份恢复、操作系统安全、应急响应等多个方面。通过具体配置示例,为企业提供了一套全面的安全实践方案,帮助强化数据库安全,防止数据泄露和未授权访问,保障企业数据资产安全。

推荐镜像

更多