实际应用中MySQL分库分表实践总结原理

互联网系统需要处理大量用户的请求。比如微信日活用户破10亿，海量的用户每天产生海量的数量；美团外卖，每天都是几千万的订单，那这些系统的用户表、订单表、交易流水表等是如何处理呢？

数据量只增不减，历史数据又必须要留存，非常容易成为性能的瓶颈，而要解决这样的数据库瓶颈问题，“读写分离”和缓存往往都不合适，目前比较普遍的方案就是使用NoSQL/NewSQL或者采用分库分表。

使用分库分表时，主要有垂直拆分和水平拆分两种拆分模式，都属于物理空间的拆分。

分库分表方案：只分库、只分表、分库又分表。

垂直拆分：由于表数量多导致的单个库大。将表拆分到多个库中。

水平拆分：由于表记录多导致的单个库大。将表记录拆分到多个表中。

一、传统项目结构

1.1数据库面临的性能瓶颈

① 数据库连接 数据库连接是非常稀少的资源,MySQL数据库默认100个连接，单机最大1500连接。如果一个库里既有用户相关的数据又有商品、订单相关的数据，当海量用户同时操作时,数据库连接就很可能成为瓶颈。

② 数据量 MySQL单库数据量在5000万以内性能比较好，超过阈值后性能会随着数据量的增大而变弱。MySQL单表的数据量是500w-1000w之间性能比较好，超过1000w性能也会下降。

③ 硬件问题 因为单个服务的磁盘空间是有限制的，如果并发压力下所有的请求都访问同一个节点，肯定会对磁盘IO造成非常大的影响。

1.2数据库优化方案

① 参数优化 ② 缓存、索引 ③ 读写分离 ④ 分库分表 （最终方案）

二、数据库拆分方式

1、垂直拆分

垂直拆分又称为纵向拆分，垂直拆分是将表按库进行分离，或者修改表结构按照访问的差异将某些列拆分出去。应用时有垂直分库和垂直分表两种方式，一般谈到的垂直拆分主要指的是垂直分库。

如下图所示，采用垂直分库，将用户表和订单表拆分到不同的数据库中。

垂直分表就是将一张表中不常用的字段拆分到另一张表中，从而保证第一张表中的字段较少，避免出现数据库跨页存储的问题，从而提升查询效率。
解决：一个表中字段过多，还有有些字段经常使用，有些字段不经常使用，或者还有text等字段信息。可以考虑使用垂直分表方案。

按列进行垂直拆分，即把一条记录分开多个地方保存，每个子表的行数相同。把主键和一些列放到一个表，然后把主键和另外的列放到另一个表中。

垂直拆分优点：

拆分后业务清晰，拆分规则明确；

易于数据的维护和扩展；

可以使得行数据变小，一个数据块 (Block) 就能存放更多的数据，在查询时就会减少 I/O 次数；

可以达到最大化利用 Cache 的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起；

便于实现冷热分离的数据表设计模式。

垂直拆分缺点：

主键出现冗余，需要管理冗余列；

会引起表连接 JOIN 操作，可以通过在业务服务器上进行 join 来减少数 据库压力，提高了系统的复杂度；

依然存在单表数据量过大的问题；

事务处理复杂。

2、水平拆分

水平拆分又称为横向拆分。 相对于垂直拆分，它不再将数据根据业务逻辑分类，而是通过某个字段（或某几个字段），根据某种规则将数据分散至多个库或表中，每个表仅包含数据的一部分，如下图所示。

水平分表是将一张含有很多记录数的表水平切分，不同的记录可以分开保存，拆分成几张结构相同的表。如果一张表中的记录数过多，那么会对数据库的读写性能产生较大的影响，虽然此时仍然能够正确地读写，但读写的速度已经到了业务无法忍受的地步，此时就需要使用水平分表来解决这个问题。

水平拆分：解决表中记录过多问题。

垂直拆分：解决表过多或者是表字段过多问题。

水平拆分重点考虑拆分规则：例如范围、时间或Hash算法等。

水平拆分优点：

拆分规则设计好，join 操作基本可以数据库做；

不存在单库大数据，高并发的性能瓶颈；

切分的表的结构相同，应用层改造较少，只需要增加路由规则即可；

提高了系统的稳定性和负载能力。

水平拆分缺点：

拆分规则难以抽象；

跨库Join性能较差；

分片事务的一致性难以解决；

数据扩容的难度和维护量极大。

日常工作中，我们通常会同时使用两种拆分方式，垂直拆分更偏向于产品/业务/功能拆分的过程，在技术上我们更关注水平拆分的方案。

三、分库分表需要解决的问题

3.1分布式事务问题

解决方案： ① 采用补偿事务，例如TCC来解决分布式事务问题。 ② 用记录日志等方式来解决分布式事务问题。

3.2分布式主键ID冲突问题

解决方案： ① 利用Redis的incr命令生成主键。 ② 用UUID生成主键（不建议：字段比较长、不好排序）。 ③ 利用snowake算法生成主键。

3.3跨库join问题

解决方案： ① 将有E-R关系的表存储到一个库中。 ② 对于数据量少的表建成全局表，分布到各个库中 ③ 对于必须跨库join的，最多支持跨两张表的跨库join

四、SNOWFLAKE策略

有些时候我们希望能使用一种简单一些的ID，并且希望ID能够按照时间有序生成，SnowFlake解决了这种需求。SnowFlake是Twitter开源的分布式ID生成算法，结果是一个long型的ID，long型是8个字节，64-bit。其核心思想是：使用41bit作为毫秒数，10bit作为机器的ID（5个bit是数据中心，5个bit的机器ID），12bit作为毫秒内的流水号，最后还有一个符号位，永远是0。如下图所示：

SnowFlake生成的ID整体上按照时间自增排序，并且整个分布式系统内不会产生ID重复，并且效率较高。经测试SnowFlake每秒能够产生26万个ID。缺点是强依赖机器时钟，如果多台机器环境时钟没同步，或时钟回拨，会导致发号重复或者服务会处于不可用状态。因此一些互联网公司也基于上述的方案做了封装，例如百度的uidgenerator（基于SnowFlake）和美团的leaf（基于数据库和SnowFlake）等。

五、扩容方面

当系统用户进入了高速增长期时，即便是对数据进行分库分表，但数据库的容量，还有表的数据量也总会达到天花板。当现有数据库达到承受极限时，就需要增加新服务器节点数量进行横向扩容。

首先来思考一下，横向扩展会有什么技术难度？

数据迁移问题

分片规则改变

数据同步、时间点、数据一致性

遇到上述问题时，我们可以使用以下两种方案：

1、停机扩容

这是一种很多人初期都会使用的方案，尤其是初期只有几台数据库的时候。停机扩容的具体步骤如下：

站点发布一个公告，例如：“为了为广大用户提供更好的服务，本站点将在今晚00:00-2:00之间升级，给您带来不便抱歉"；

时间到了，停止所有对外服务；

新增n个数据库，然后写一个数据迁移程序，将原有x个库的数据导入到最新的y个库中。比如分片规则由%x变为%y；

数据迁移完成，修改数据库服务配置，原来x个库的配置升级为y个库的配置

重启服务，连接新库重新对外提供服务

回滚方案：万一数据迁移失败，需要将配置和数据回滚，改天再挂公告。

优点：简单

缺点：

停止服务，缺乏高可用

程序员压力山大，需要在指定时间完成

如果有问题没有及时测试出来启动了服务，运行后发现问题，数据会丢失一部分，难以回滚。

适用场景：

小型网站

大部分游戏

对高可用要求不高的服务

2、平滑扩容

数据库扩容的过程中，如果想要持续对外提供服务，保证服务的可用性，平滑扩容方案是最好的选择。平滑扩容就是将数据库数量扩容成原来的2倍，比如：由2个数据库扩容到4个数据库，具体步骤如下：

新增2个数据库

配置双主进行数据同步（先测试、后上线）

数据同步完成之后，配置双主双写（同步因为有延迟，如果时时刻刻都有写和更新操作，会存在不准确问题）

数据同步完成后，删除双主同步，修改数据库配置，并重启；

此时已经扩容完成，但此时的数据并没有减少，新增的数据库跟旧的数据库一样多的数据，此时还需要写一个程序，清空数据库中多余的数据，如：

User1去除 uid % 4 = 2的数据；

User3去除 uid % 4 = 0的数据；

User2去除 uid % 4 = 3的数据；

User4去除 uid % 4 = 1的数据；

平滑扩容方案能够实现n库扩2n库的平滑扩容，增加数据库服务能力，降低单库一半的数据量。其核心原理是：成倍扩容，避免数据迁移。

优点：

扩容期间，服务正常进行，保证高可用

相对停机扩容，时间长，项目组压力没那么大，出错率低

扩容期间遇到问题，随时解决，不怕影响线上服务

可以将每个数据库数据量减少一半

适用场景：

• 大型网站
• 对高可用要求高的服务

以上就是本文对MySQL分库分表实践总结，前面博主也有分享，链接:分布式数据库MyCat综合实战:https://www.toutiao.com/article/7049340575182651943/

MyCat实现主从复制及分库分表策略详解:https://www.toutiao.com/article/6970231461404328485/