用MySQL存下亿级数据——分库分表

游目鱼

已于 2024-08-04 21:37:15 修改

阅读量1k

点赞数 35

分类专栏： mysql 文章标签： mysql 数据库

于 2024-08-04 21:34:31 首次发布

本文链接：https://blog.csdn.net/qq_39039639/article/details/140912426

版权

mysql 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

首先，我们需要明白分库分表要解决什么问题。在如今数据量爆炸的时代，业务的数据记录动不动就是以亿计，单表必定是存不下了。我们先看看大数据的相关技术为什么不能完美解决这个问题：

如果是采用Hive、Spark等离线数仓，无法满足实时返回结果的要求
如果是采用ClickHouse等OLAP类型数据库，无法支持事务，而且通常资源成本远高于MySQL
如果是采用ElasticSearch等搜索引擎，那已经跟数据库没关系了，面向的应用场景完全不同

分库分表在底层数据源不做大的变动的情况下，很好地扩展了MySQL的存储能力。不过，分库分表也不能完美解决数据量的问题，在分成1024张表的时候，假设单表1000万，总共数据量也就刚过100亿，假设数据量过大（超过百亿），仍需要定时对历史数据进行清理，归档到Hive等离线数仓。同时，分库分表还会引入一系列新的问题，这个我们会在下面来讨论。

分库和分表

首先我们需要知道分库和分表解决的问题还不太一致。分库想要解决的问题是并发量过大的问题，不同库在不同的机器上。我们以始为终来思考其意义，就可以得出如下结论：既然最终产生了亿级的数据，那说明每时每刻都在产生大量数据，这个并发量也不是单机数据库所能抗住的，当下分布式解决高并发已经是标准方案，数据库采用集群分布式部署也是顺理成章了。
分表则是真正解决数据量过大的手段，一张表数据量过大相关索引维护成本很高，读写性能都会急剧下降，分多张表存则可以解决这个问题。

如何分？

水平拆分：最常用的拆分方式。简单来说，水平拆分是对数据表行的拆分，把一张行比较多的表拆分为多张表。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。
垂直拆分：简单来说，垂直拆分是对数据表列的拆分，把一张列比较多的表拆分为多张表。举个例子：我们可以将用户信息表中的一些列单独抽出来作为一个表。我更愿意把垂直拆分比作业务拆分，因为这种分法完全可以认为是拆成了两张表，通过某一个唯一业务ID进行关联，至于怎么拆就看业务字段可以怎么归类了。

如何选择分库分表键？

分库分表键的选择与实际的业务场景密切相关，首要原则就是找到数据依赖的主体维度，保证所有的操作都离不开这个主体。比如：

游戏中的主体是玩家，那么玩家ID就可以作为拆分键
学校中的主体是学生，那么学号ID就可以作为拆分键

数据倾斜

但是实际中我们往往还会遇到一个问题——表中有多个可供选择的主体怎么办？一个典型就是订单交易，我们既可以买家ID为分库分表键，又可以卖家ID为分库分表键。这个时候我们就需要考虑数据能否均匀分布在各个表中？如果做不到就意味着存在数据倾斜的问题。往往不同主体带来的数据倾斜问题有大有小，显然，我们应该选择尽可能避免数据倾斜的主体维度作为分库分表键。

还是以订单交易为例，在电商场景中，往往存在头部商家占据大部分流量的问题。以无处不在的“二八原则”作为说明：20%的商家会拥有80%的销量。此时如果我们以卖家ID作为分库分表键，显然就会存在数据倾斜的问题，尤其是细分行业的电商，头部商家屈指可数，几乎可以肯定数据就集中在头部商家所在的几张表里。此时，我们就需要以买家ID作为分库分表键，如果仍需要以卖家ID去查询，则可以同步到离线数仓或者搜索引擎进行处理。

在外卖场景，由于极强的地域属性，即使存在头部商户，配送距离超过10公里的用户也不太可能选择该商户。而且品牌商不像电商中的头部商家只有一个ID，如肯德基、麦当劳有分散各地的店铺，每个店铺的ID都是不一样的，相当于地域的划分自动帮我们实现了数据打散。此时，我们以卖家ID作为分库分表键就没有数据倾斜的问题。

总而言之，实际中分库分表键一定要根据具体的业务场景来选择，存在多个可供选择的主体维度时以不存在数据倾斜为原则进行挑选。不过，假如所有可供选择的主体维度都存在数据倾斜的风险呢？

数据倾斜的加工处理

如果数据倾斜的风险无法通过合理选择分库分表键进行规避，那么我们可以通过对分库分表键进行二次加工。一个常见办法就是利用主体+时间作为分库分表键，比如卖家+订单下单时间。亦或是将多个主体联合作为分库分表键，比如一个卖家的订单也会因为交易类型、支付方式等不同而可以分散到多个表中。

分表路由算法有哪些？

确定了分库分表键，下一步我们就需要落地实现路由算法。常见的路由算法有以下几种：

简单取模。假如有1024张表，而分库分表键也是数值类型，那么取模后我们可以得到库和表。假设只分表不分库，总共1024张表，卖家ID为1025，1025%1024=1，那么卖家1025对应的订单就都存到order_0001表中；假设分库分表，有16个库，每个库64张表，即数据库0存order_[0000-0063]，那么分库分表键的路由计算公式就为：

所在库 = (卖家ID % 1024) / 64
所在表 = (卖家ID % 1024) % 64

哈希取模。假如分库分表键不是数值类型，比如是字符串，我们需要先将其转化成数字，然后再取模，即

所在库 = (Math.abs((long) 卖家名称.hashCode()) % 1024) / 64
所在表 = (Math.abs((long) 卖家名称.hashCode()) % 1024) % 64

一致性哈希。为了避免容量不够扩展表数量时需要重新计算hash值的问题，我们需要一致性哈希来解决这个问题。

分表后主键怎么保持唯一？

分库分表会导致主键的自增机制失效，因为主键自增约束只能保证单表内的单调递增，不能保证全局单调递增。分库分表的主键唯一是一个纯粹的技术问题，平常并不需要人为根据不同业务场景过多调整。由于涉及到的各个算法说来话长，我将在另一篇文章（分库分表的主键怎么设计）里专门讲解。

分库分表引入了哪些新的问题？

跨库事务怎么支持？分库，其实就意味着数据库分散在各个机器，一旦操作的库A在机器1，操作的库B在机器2，那么普通的事务就无法协调机器1、机器2进行回滚。一般解决办法就是采用分布式事务分布式事务浅析。但是，由于分布式事务增加了开发和维护成本，且具有一定侵入性，假如我们通过合理设计分库分表键使得命中的库A、库B都在一台机器上，那么直接利用MySQL的事务机制就行。比如用户个人信息表和用户个人收益明细表，都可以用户ID作为分库分表键，使用相同的分库分表算法可以让它们分到同一个库里，此时我们就可以直接在一个MySQL本地事务中同时更新用户个人信息表和用户个人收益表。
还能进行join操作吗？首先我并不推荐在MySQL中使用join你真的会用join吗？，其次要在分库分表中使用join那就更不推荐了（问自己一个问题：真的需要用MySQL来支持亿级数据的实时统计分析吗）？也许有人会说我并不用于数据分析呀，我就是join一下查出关联的一些额外信息，比如用订单主表和订单明细表用订单ID来join，查出某一个订单ID关联的明细信息。那这就需要你转换思路了，这种查询完全可以在应用层分两次查询，因为某个指定订单ID关联的明细记录数量一定不会太多。另一种办法就是使用成熟的数据库中间件，比如MyCat就支持join，但是这只是中间件帮你做了很多事，你如果不清楚实现细节后面维护和排查问题的成本都很高。
如何进行分页查询？这里又是涉及各种算法的讨论，在此不赘述，我在另一篇文章专门分析过分库分表后怎么进行分页和排序？。但是这里我可以给一个简明的结论：如果容忍数据结果返回不全/重复可以尝试，有条件的还是同步到搜索引擎来提供分页查询功能。

如今互联网上各类文章满天飞，但是大部分要不是寥寥数语，让人过目即忘；要不是过多细枝末节又没有实操，让人不知所云。我将从个人学习和工作经历出发，给大家带来深入浅出的技术解析。我的文章力求简短精悍，尽量结合实战，以便大家在碎片时间即可充分吸收，后续还能学以致用。

欢迎大家关注我的微信公众号，所有文章第一时间更新~