关于分库分表，你知道多少？

在业务体量还不是很大的时候，单库单表即可满足业务上的需求，随着业务体量的增大，无论是CPU还是IO都可能出现性能瓶颈，由于大量连接达到单库上，导致单库无法承载这些活跃的连接数，这使得我们从Java进程的角度看来就是数据库连接很少或者没有连接可用，最终出现并发、吞吐全面下降甚至是系统崩溃。

所以，笔者整理了这篇分库分表的文章来逐一分析拆解这些问题。

一、关于一些分库分表性能指标的补充

这里我们补充一下IO瓶颈和CPU瓶颈，关于IO瓶颈，即是数据表中存在大量热点数据，大量的请求都需要到数据库进行查询，因为大量的IO请求进来导致数据库连接数不足导致性能瓶颈这就是所谓的IO瓶颈，针对这种情况我们可以考虑根据热点数据类型采取垂直分表或者分库的方式解决。

就像下面这种情况，因为有大量请求专门查询用户名和地址，所以我们采用垂直分表的方式将热点数据拆出来独立维护，解决原有订单表过度冗余的字段使得热点数据体量直接减小，再通过内存中间件加以缓存缓解数据库压力解决IO性能瓶颈：

假如热点数据在垂直分表后，数据量可以减小，那么我们就采取垂直分表结合缓存中间件的方式解决。如果热点数据无法通过垂直分表或者说通过垂直分表后数据规模仍然很大的话，那么我们就必须通过水平分库解决了。

而CPU瓶颈则是因为表关联join或者各种运算例如group by，order by等导致查询效率低下，这种情况如果无法通过索引或者业务代码层面进行计算的方式解决的话，那么就只能通过业务层面并结合水平分表缩小数据体量提升数据聚合效率。

二、分库分表基本概念介绍

1. 为什么不使用MySQL分区

可能也会有读者问到，为什么不采用MySQL分区表呢？ 这里我们需要了解一下MySQL分区表的工作原理，它会将分区的数据表在在物理层面进行分区，但在逻辑上还是一张表，这使得用户在查询的时候对分区是没有感知的。所以说使用MySQL分区会带来以下好处：

在一定的数据量情况下，使用分区键进行查询可以快速定位数据。因为分区会在物理层面进行切分，所以对于需要定期删除分区数据的场景下，MySQL分区是非常方便管理的。

而同样的它也存在如下缺点：

无法创建外键，当然这对于现代开发规范来说这一点没有太大影响。并发量上来了依然存在IO瓶颈。查询时必须带上分区键，否则会对所有分区进行扫描。对分区查询时的优化都是由MySQL优化器自定义，对用户来说是黑盒可控性较差，不如分库分表灵活。2. 分库分表的基本概念

本质上分库分表是3个概念，这里我们都是从水平拓展的维度讨论问题，本质上分库分表中的概念分别对应：

(1) 分库：以8C16G的MySQL实例为例，经过业界压测普遍认为其TPS大约在2500~3000左右，所以当系统的并发量超过这个时候，就很可能出现连接数不足导致服务瘫痪的问题，所以业界就有了水平分库增加更多的数据库连接的同时还能分散系统请求从而提升系统并发度：

(2) 分表：当单表体量超过一定阈值之后，无论数据检索还是修改操作对应的耗时的都会增加，最经典的就是深分页问题，此时我们就可以通过水平分表来缩小单表数据体量以提升数据检索速度。

(3) 分库分表：如果系统是典型高并发、海量数据的场景，也就是上述两种情况的综合体，那么我们就需要通过分库分表这种综合方案来解决问题了。

三、分库分表的两种维度

1. 垂直分库或者水平分库

垂直分库是解耦服务间依赖的常见手段，在传统单体架构时，我们的所有的数据表都在一个数据库中。随着业务体量的增加，为了针对业务进行优化，我们可以将不同业务进行圈表拆分到不同库中，这就是垂直分库，通过垂直分库进行针对性优化，从而针对这些业务孵化出一个业务模式，达到服务化。

因为高并发导致单点数据库无法承载这些连接，所以我们将相同结构的数据表放到不同的数据库，然后用户通过分库算法定位到这些数据进行操作，以减轻数据库的io和cpu压力，这就是典型的水平分库。

2. 垂直分表和水平分表

关于常见的分表技术有垂直分表和水平分表，其中垂直分表主要是优化查询的一种常见手段，从物理角度来说，它就是将一张表垂直进行拆分以实现确保将热点数据与非热点数据进行隔离，确保每次进行查询时缓存行可以尽可能缓存更多的字段，避免到磁盘进行随机IO导致的IO瓶颈。

水平分表则是为了解决大数据存储和查询问题，从物理角度来说它就是将大数据表横切一刀分为无数张小表，然后所有的操作都需要针对体积更小的小表进行操作，从而减小单体查询检索的IO量，提升检索效率：

四、常见的分库分表方案

1. 简介常见的集中分库分表设计方案

为保证分库分表后数据能够被准确的定位并查询到，分表的策略也是很重要的，这里笔者列出几种比较常见的分表方案:

range范围发表法hash取模法range+hash法2. range法分表

rang法实现比较简单，就是针对每个表都指定一个id范围，假设我们现在有3张分表，分表1存储1-500w的数据，分表2存储500w-1000w的数据，分表3存储1000w-1500w的数据。因为每个表范围是固定的，那么我们在进行数据查询时就很方便了，例如我们想查询id为1500的订单详情，直接通过id%500w即定位到分表0。

这种方案在数据查询比较均衡的情况下表现良好，遇到热点问题就比较棘手了，例如双十一淘宝订单都集中在分表3和分表4，这就会导致这两张表单位时间内承载大量查询和操作请求，而其他表却无法去负担这些压力，这也就是我们常说的数据偏斜问题。

3. hash取模法

于是就有一种均摊数据的分表算法即hash取模法，这种算法要求我们尽可能在功能实现前，评估将来的数据量，例如就是5000w，那么我们就设置10张表，每张表500w。后续进行插入操作时我们只需根据自增id值进行取模运算然后均摊存储到不同表即可。例如：我们现在有一条数据得到id为1000，通过1000%10=0，由此可知这条数据就可以存到tb_0表中.

hash分表算法虽然可以均摊数据存储，避免数据热点问题，但是也存在一定的缺点，即查询问题，假如我们现在只有3张分表，id算法为id%3，一旦数据体量增加，我们的分表需要增加到6张，那么规则就需要改变了，很明显这种改动量存在的风险是非常大的。

4. range+hash法

由上可知range法可以很好的进行扩容，而hash法可以完美的均摊存储。所以我们更建议使用range+hash法进行分库分表，通过range法决定当前存储的区域，再结合hash取模法指定这个区域中具体的一张表。

例如: 举个例子，笔者现在根据业务需求对数据表进行拆分得到6张分表：

每张表存储1000w条数据。1个库作为一个range范围，id自增。1个range包含两张张分表，总和2000w数据。

对应规则得到的表名和含义如下：

根据我们上文所说，通过range决定区域，假设我们现在数据id为600w，根据上表前缀可知我们要存储的数据表为tb_0开头的表，因为tb_0开头的表有两张，由此我们再用hash法进行取模，即600w%2=0，由此可知数据最终要存到tb_0_0表。 我们再回过头说说扩容问题，因为我们通过range法决定存储的分表area，所以假设需要增加分表，我们也只需定义一个新的range范围和这个范围的分表算法即可。

就比如，我们现在就需要增加两张分表，那么我们可以直接指定这两张分表区域为3，的id范围是6000w到8000w，因为这个区域还是两张分表，所以算法也是hash%2，简单配置一下即可实现扩容，无需对代码进行改造，可以说这套方案相较于前两者会更出色一些。

五、分库分表涉及的一些涉及问题

1. 分库分表时如何选择分表字段（推荐id）

整体来说分库分表的选用的字段可以有很多种的方案例如：

按照用户id按照时间按照地区

只不过在选择的时候一定要结合业务场景进行设计同时也要考虑到下面这两个问题：

如何保证数据尽可能均匀分布到库表，同时保证保证检索效率？确定字段后，如何在数据检索前明确知晓数据存在的库表？

假设我们的查询都是按照时间维度进行查询，那么我们就使用数据表中带有时间性质的字段作为分表字段，例如我们现在的订单表order分表算法是按月进行分表，在1月创建的数据存放至tb_1，在2月创建的数据存放至tb_2，那么我们雪花id算法作为分布式id生成工具，其原因如下：

雪花算法自增有序，不会导致大量页分裂而导致检索性能下降问题。雪花算法有41bit的空间记录当前时间戳，所以按照我们的分表算法，可以直接通过生成的id定位到日期从而确定库表。

如下所示，我们1.25创建的数据得到的订单id是1882967322877497344，基于高41bit得到时间是1月份所以存入分表1，后续查询时，我们只需要知道对应订单的id即可定位到分表从而利用主键索引检索到数据：

2. 分库分表全局ID生成方案

分库分表势必涉及一些关于全局库表id的设计方案，感兴趣的读者可以参考读者这篇文章：《来聊聊大厂常用的分布式 ID 生成方案》

3. 数据偏斜问题和解决方案

数据偏斜即按照现有分库分表算法出现了某份库表数据远远大于其他表数据，进而导致：

性能瓶颈：因为数据偏斜导致数据分布不均匀，对于分表后的性能表现和分表前并没有很大的提升资源利用不均匀查询效率低下

数据偏斜问题的根因大部分是分表算法设计不好所导致，例如上面提到的range分表法无法针对业务高峰期的id段进行数据均摊，针对该问题我们建议从以下几个角度考虑并选用合适的方案：

如果没有特定的范围查询或者分页查询等需要，仅仅针对特定几条数据的检索，我们可以将分表算法改为hash算法结合取模运算均匀分布数据。如果需要进行特定日期等范围查询的要求，建议在特殊月份做特殊的分表算法，并针对该月份的分表规则进行特殊处理，例如11月份订单是平时的3倍，我们就可以在该月份多部署几个库源和服务，针对该每个服务都有各自的workerId对应一个库源，如下图一个java-service对应一个tb_11_x的库，通过负载均衡算法将订单请求打到不同的服务上以保证数据均匀的落到不同的库表中。

4. 如何解决分库分表后的join问题

水平拓展后可能会导致库表发布到不同的MySQL实例上，这使得原有的单数据源关联查询变为多库源关联：

实际上解决该问题的办法有如下几种:

应用层进行关联，即应用层面分别查询两张表然后将数据关联。通过数据库中间件例如shardingsphere等工具实现，不过shardingsphere的联邦查询还不是很稳定，慎用。将需要关联查询的数据直接冗余到分表上。通过es等搜索引擎统一结构化存储提供外部检索查询。5. 非partition key查询问题(读扩散问题)

问题说明： 进行分表后，对于非partition key的查询就由为的复杂，因为非partition key和partition key没有任何关联如果没有采取任何措施的话，查询效率就会十分低下。最简单的例子就是上文600w那条数据，他记录着一个用户的个人信息，假如我们希望通过用户名name进行查询，又该如何定位到这条数据呢？很明显在没有任何措施的情况下，只能通过逐表遍历查询解决了。

解决方案：

(1) 映射法：对此我们提出第一种解决方案——映射法，即通过建立一张中间表将partition key和非partition key进行关联，以上面的例子，我们想通过name进行查询时，可直接通过映射表带入对应的name，从而得到对应的id，进而根据id得到对应的表即进行查询了。

映射算法也存在一定的缺陷，其一为了查询要同时维护两套表，并且普通索引更新时对应的映射表也得更新，而且一旦数据量逐渐增大时，可能还需要对映射表进行水平拆分，再一次增加的业务实现的复杂度。

(2) Elasticsearch：上述的映射表起始就是一种倒排索引的思想，而ES天生就是做这种事情的，针对当前问题，我们直接集成ES，通过开源工具canal监听MySQL的binlog拿到日志变更，将数据采集到ES中，通过ES近乎实时查询能力即可完美解决上述问题。

(3) 最终方案：这些做法要么会增加维护的困难和复杂度，亦或者需要增加新的中间件，还需要为了考虑可靠性增加更多的硬件资源。所以，如果业务允许的情况下，针对这种大数据存储，我们更建议直接采用TIDB进行数据存储，它是成熟的分布式数据存储数据库，它通过引入range的概念对数据表进行分片，有点类似于range范围分表，且支持普通索引分片类似倒排索引。且其语法和MySQL几乎一样，市面也有很多工具可以辅助完成数据迁移，如果项目允许的话，很明显这套数据库是最干净利落的解决方案了。

6. 分库分表扩容问题

(1) 问题简介：

因为各种原有我们需要对旧有数据表进行扩容，对此数据迁移就是一个很麻烦的问题，有没有什么比较安全且易实现的方案呢？这里笔者为大家推荐两种比较常见的解决方案。

(2) 解决方案：

升级从库：先说说升级从库法，这种方式就是通过升级从库为主库的方式实现数据迁移再改造hash的迁移方式。 举个例子，假设我们现在有两个分库，每个库中有一张分表，对应的分库分表算法即id%2得到库索引，然后将数据存入对应分库的分表中，例如我们现在要存储一个id为600w的数据，通过算法得到值为0，那么这条数据就存入分库0的tb表，对应的我们的从库也跟随db0做数据同步。

当现有主库数据已达到一定体量导致查询性能下降，我们可直接将各自的从库升级为主库，这是第一步。

完成升级从库为主库之后，db0对应的从库变为db2，此时这两个数据的数据表是重复的，因为我们将分表算法修改为id%4，所以我们需要基于这个算法清除冗余数据，即主库0删除id%4=2(这些是升级为主库的db2数据)，db1删除id%4=3(这个是升级为主库的db3的数据)，其余两个从库同理，自此完成算法和数据迁移的升级。

双写扩容：双写扩容是现如今比较常见的方案，步骤为:

设计一套全新的算法的分库分表将新的数据插入到新表中。通过同步双写将新数据插入新老两库。通过异步的方式查询老库的数据全部写到新表中。完成迁移工作后以老库为准核对数据，核对结束后配置关闭双写，后续的数据都写入新库。

这种方案相较于前者更加稳妥，也是笔者较为推荐的一种解决方案。

7. 分页查询

将单表进行水平维度的分库分表之后所导致的库源不一致，传统的limit查询就无法针对整个分布式维度的分页，此时我们不得不借助一些第三方工具类将库源抽象成一个维度进行实现分表查询，我们以sharding-jdbc为例，它的做法就说基于当前查询的页数n，到所有库源中查询前n页的数据并聚合，将分布式库源检索结果聚合成一个维度，然后进行排序从而得到实际上的第二页的数据并返回。

例如，我们的分库分表希望查到第二页的数据，按照sharding-jdbc的做法，它就会将所有库表的前2页的数据查出来，然后进行归并排序得到一个完整维度的前2页的数据，最后再筛选出第二页数据返回给用户：

但是这种做法也存在一个指明的缺陷，即深分页数据的检索，按照这张方案的做法，假设我们查询100w页的数据10条，那么我们就需要归并n表*100w页*10条的数据进行归并排序，这意味着我们的程序的内存很大概率会被打穿。

针对sharding-jdbc，感兴趣的读者可以参考笔者这篇文章，这里面针对跨库表分页查询有着相对详细的介绍：

本质上，跨库表分页查询就是因为散列的数据缺少一个全局视角，针对该问题业界也有一个在业务和性能上相对折中的方案——二次查询法，下面笔者就以一个单表进行水平分库分表后的查询为例演示一下这套方案。

默认情况下，在单库单表的情况下，我们查询第二页的数据4条对应的SQL为：

对应的查询结果如下图所示，即id在5~8这个区间的数据：

在进行水平拆分后，得到两张分表如下所示，接下来笔者就演示一下如何基于二次查询实现相对简单且高效的数据检索：

我们需要对这条SQL进行改造以保证后续步骤能够准确获取全局视角，首先我们假设分表散列均匀，所以均摊一个offset即可两张表对应的SQL为：

于是我们就得到了下面这张图中绿色区间的数据：

基于上述检索到的数据进行排序，我们得到id的最小值为4，基于这个最小值我们进行第二次查询，对应的查询采用范围查询的方式以排序的最小值作为起点，当前表的最大值作为终点：

可以看到分表1数据区间不变，分表2多了一条数据5，此时我们就可以基于这份样本得到数值4在全局视角的offset值：

tb_1中的数值4是offset 2即偏移2得来的数据，这意味着小于4的数值有两个。tb_2中的二次查询后找到大于等于4的区间是通过offset 1得来的，这意味着小于4的数值只有1个。

由此可得数值在全局视角是offset 2+1即offset 3的结果。

基于此结果可知，我们还需要偏移一条数据即可完成offset 4的偏移，因为数值4在全局是offset 3的结果，所以跳过数值4就可以实现offset 4，于是我们可知数值5开始之后4个元素就是全局视角的第二页的数值结果，由此查询出5~8，二次查询法完成：

可以看到，二次查询大体步骤为：

均衡偏移获取候选数据。获得最小数据作为二次查询检索范围。基于二次查询结果获得最小值在全局的偏移量。将二次查询结果排序结合最小值在全局视角的偏移量得出最终得出分表后的分页结果。

这种做法无论是在性能还是实现复杂度都做了较好的折中，算是比较不错的解决方案。

8. 分布式事务

因为水平拓展使得库源可能分布在不同的服务器上，所以系统在进行多表操作的时候无法保证数据操作的ACID，此时我们就必须借助一个第三方工具来统一管理分布式数据源，常见的方案又seata或者rocketMQ。

我们就以seata为例了解一下主流的分布式事务解决思路：

seata首先会针对分布式库源要操作的数据进行前置镜像备份。协调分布式库源执行本地事务。基于分布式库源事务结果判断事务是否提交。如果某个库源事务失败，则通知其他库源一并回滚，反之统一提交。

可以看出针对分库分表后的分布式事务本质上就是通过第三方的工具在逻辑上的统一协调来保证分布式事务的ACID：

9. 在分库分表后的模糊查询

针对大库表进行分库分表分散压力之后，针对下面这种非前缀匹配还是显得有些力不从心(索引失效)：

对于此问题最好的办法就是专业事情让专业的工具解决，我们完全可以通过bin.log订阅分库分表数据将其提交到elasticSearch中，通过其强大的自然语言处理的分词器和倒排索引这种天生为数据检索而生的设计理念来解决模糊搜索问题：

10. 分库分表如何进行库表资源评估确定数据表体量以及TPS针对TPS进行分库基于分库后得到单库的数据体量进行分表

例如我们现在TPS为6000，每日数据体量在2e,按照上述步骤的推算过程为：

按照先常见的硬件配置对MySQL实例(8C32G)的压测，单库TPS基本在2000~3000，按照我们业务体量TPS需求为6000，大体需要3个库，当然我们也可以适当冗余一个库预防流量突增，所以最终我们分配4个库。平均之后单库数据差不多在7000w以内(2e/3)，按照业界标准单条数据1k的情况下单表最好在2000w以内，所以我们单库标准分3张表，还是同样道理冗余1张预防突增的数据，也可以不用，因为我们已经适当冗余了一个数据库。

基于4库3表指定分库分表方案并完成业务落地：