一种简单实用、支持动态扩缩容的分库分表方案

在互联网的业务中，mysql使用很广泛，且是最容易产生性能瓶颈的服务组件，一般稍微有点业务量的toC业务，都需要在系统设计阶段考虑好扩缩容方案，但又不能过度设计造成资源浪费，所以需要有一个灵活的分库分表方案，以适应不同时期的业务需求，本文以激励广告项目的项目为例，介绍一种简单实用的分库分表方案。

为什么分库分表

数据库很容易成为系统性能的一个瓶颈，单机存储容量、IO、CPU处理能力都有限，当单表的数据量达到1000W或100G以后，库表的增删改查操作面临着性能大幅下降的问题。存储容量现在一般容易解决，主要是IO瓶颈和CPU瓶颈，最终都会导致数据库的活跃连接数增加，进而逼近甚至达到数据库可承载活跃连接数的阈值。从业务方来看，就是数据库可用连接少，甚至无连接可用。

1、IO瓶颈

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

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

2、CPU瓶颈

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

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

分库分表方案

1、水平分库

概念：以某个字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。结果：

• 每个库的结构都一样；
• 每个库的数据都不一样，没有交集；
• 所有库的并集是全量数据；

场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。

2、水平分表

概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。

• 每个表的结构都一样；
• 每个表的数据都不一样，没有交集；
• 所有表的并集是全量数据；

场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。

3、垂直分库

概念：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。

• 每个库的结构都不一样；
• 每个库的数据也不一样，没有交集；
• 所有库的并集是全量数据；

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

分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。

4、垂直分表

概念：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

• 每个表的结构都不一样；
• 每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
• 所有表的并集是全量数据；

场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。

分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

分库分表带来的问题

1、联合查询困难

联合查询不仅困难，而且可以说是不可能，因为两个相关联的表可能会分布在不同的数据库，不同的服务器中。

2、需要支持事务

分库分表后，就需要支持分布式事务了。数据库本身为我们提供了事务管理功能，但是分库分表之后就不适用了。如果我们自己编程协调事务，代码方面就又开始了麻烦。

3、跨库join困难

分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。 我们可以使用全局表，所有库都拷贝一份。

4、结果合并麻烦

比如我们购买了商品，订单表可能进行了拆分等等，此时结果合并就比较困难。

5、扩容缩容需要迁移

对于水平分库和水平分表，如果进行扩容或缩容，由于数据拆分条件发生变化，通常需要进行数据迁移，而数据迁移又会影响到在线业务的使用。

分库分表实践

本文主要讨论业务量越来越大需要扩容或大促后需要缩容的场景下，如何对数据进行水平分库、水平分表，才能适应业务在不同时期的性能要求，并最大程度降低切换的代价。

下面以激励广告后台流水数据的分库分表方案为例进行说明。

1、分库方案

激励广告是在福利任务中心给用户提供一种看广告领红包的场景，后台需要记录用户观看广告记录及红包发放记录。

根据产品需求预估，一期需要db存储容量在215G左右。同时，根据产品规划，后续半年会再上2个以上同样量级的流量入口，这样的话，在后续1年内，至少需要215G*3=645G的存储容量。

首先假设不进行分库，mysql实例最大支持扩容到2400G的存储空间，则能支撑11个与当前流量类似的业务场景，在1年内是够用的，但长期来看预留空间还是不够，将来再进行分库的话，涉及大量数据迁移工作，所以我们分库才能解决短期和长期的容量问题。

分库就要考虑分多少个库，首先从业务量级来考虑，单个mysql实例最大能支持每天2750w UV的业务量级(11个当前的流量的业务)，假设再分为10个库，就可以最大支持每天2.75亿UV的业务量级，这个业务量级可以说是互联网业务的天花板了，恐怕到这个量级之前服务就要重构了，所以分10个库是完全够用的。

其次，从mysql实例本身的性能上来看，10个超大型实例可以支持12T的容量和23万的qps，这个量级也足够支撑绝大部分互联网业务了。

另一方面，产品上要求登录用户观看激励广告，后台服务都使用person_id作为主键存储数据，而person_id都是纯数字且是离散分布的，可以使用person_id的尾号来分库，这样也方便RD后期定位问题，不用计算hash。

2、分表方案

由于当前业务特征是激励广告任务是按自然天划分的，所以同一个库的数据，再按照天进行分表，这样就可以解决单表数据量过大的问题，又不影响在线服务，后期进行备份归档时也方便操作。

3、扩缩容方案

如何合理地分库分表不难，难的是如何才能最大限度减少扩容缩容带来的迁移问题。

通常的作法，分库的扩容或缩容是相应地增加、减少db数量，必定会改变路由规则，需要对全量数据按照新的路由规则重新计算，再导入到新的db中，这种方法需要专门开发迁移脚本才行，并且通常需要新旧db双写来达到平滑迁移目的，对原有项目侵入较大，迁移工作量也较大。

为了减少迁移工作量，可以把分好的10个db作为不同的逻辑db放到一个实例上，而项目中会配置10个不同的db连接，后续需要扩容时，可以先扩容实例，这个阶段的扩容对业务是没有影响的。后续如果实例扩容到极限，则可以增加新的实例，比如扩容一倍，则增加1个实例，将其中5个db迁移到新的实例上，由于是整个db迁移，交给dba来操作就行了，项目只用修改其中5个db连接的配置即可。

类似地，若业务量变小需要缩容时，可将某些实例上的db迁移到其他实例上，修改db连接配置即可，也比较方便。

4、总结

通过上面的分析，总结下来，该方案有以下几个主要步骤：

A) 分库分表数量一步到位

也就是在第一次分库分表时，就一次给他分个够。保证在整个项目的生命周期中，这么多的库表是足够使用的，后续扩容缩容不需要调整分库分表的路由规则，并且保证每个库里面有多少个表也是固定的，迁移时可以以库为单位进行迁移。

上面分库方案中的推导过程是正向来算的，其实应该是反向计算，先确定业务的终极目标或5年规划是达到多大的业务量级，比如像上面激励广告的场景，假设最终要支持2.75亿的UV，大约需要23T的容量，由于我们扩容的最终方案是每个db占用一个超大型实例，所以按照超大型实例最大支持2400G的容量来计算，需要10个超大型实例，我们就分为10个库。

至于分表，由于本例是按自然日的流水型数据，所以可以按天分表，相对比较简单

假设这个流水数据用户需要经常查询，且要查询90天内的数据，那么就不能再按照日期进行划分了。比如还是上面的例子，最终每个库是要存储2.3T或2750w记录，而单表最大支持1000W或100G，按照2.3T容量来划分的话，需要分成24个表，所以整个分库分表过程就变为：先基于person_id%10得到分库，再基于person_id%10%24得到分表，如下所示：

B) 以半年内的规划确定mysql实例大小

由于项目一期需要215G的存储空间，半年内需要645G的存储空间，所以一期申请一个中型实例即可满足短期需求，也不会造成浪费，且留有一定的升级空间。

此处建议mysql实例以中型或中小型为标准来申请，若半年内的需求超过了1个中型mysql实例的容量，则建议申请多个中型实例（而不是直接申请大型或超大型），这样预留了mysql实例升级空间，要知道mysql实例升级对业务是透明的，避免了扩缩容的工作量。

C) db为逻辑库，并配置化

上面分库得到的db都是逻辑库，这些db可以在一个mysql实例上，也可以在多个mysql实例上，需要将每个db连接配置化，这样后续扩缩容时不用修改代码，配合dba迁移db后修改配置即完成扩缩容。

D) 执行扩容缩容

执行扩容缩容的过程，就是不断在逻辑db和 mysql实例之间做迁移，然后服务配合改一下配置即可。

若服务不支持停服，若要想达到对用户无感知，难免要修改代码来兼容，一个可行的办法是先将新db设为旧db的备库，然后用灰度放量的方式逐步对不同的用户分别进行迁移，只有命中迁移的用户才切换到新db，最终全量后更新db连接配置即可。

若服务支持短暂停服，就比较简单了，先由dba将原db数据迁移到新db(或者将新db设为旧db的备库)；然后停服，并迁移停服前的增量数据(新db更改为主库)；接着变更db连接配置，重启服务保证配置生效即可，整个过程应该是分钟级的