mysql分库分表

什么是分库分表？简单来说，就是把过大的数据集合，切分成多份，就像一个大西瓜，切开才好下嘴

分表的必要性

数据库数据会随着业务的发展不断增多，数据操作的开销也会越来越大。简单一条count语句，在1000w数据量下，也需要运行几秒甚至超过十秒。

同时，由于物理服务器的资源有限，数据量过大产生的慢查询，最终会拖累整个服务，整体数据处理能力都将遭遇瓶颈。

什么是分库分表

分库分表是解决数据过大问题的良方，目的就是为了缓解数据库的压力，最大限度减轻数据库压力，提高数据处理的效率。

分库是把一个数据库，按实际场景切分多个库，再将数据表分散到多个库中。

分表是把一个数据库中的数据表拆分成多张表，防止单表过大

两者都是为了解决数据过大问题，划分方式一般有垂直划分和水平划分两种。

这里强烈建议在业务设计之初，就要规划好数据库的设计，做好分库分表，如果等业务量上涨之后再来划分，成本会比较昂贵。

分库分表其实是一家，分库只是在分好表的基础上，将表挪移到不同的库，所以我们着重讨论分表。

 

为了方便我们讨论，现在拟定有如下数据表：用户得分表user_score，其描述了用户针对某个问题的解答及得分，数据量达到5000w；试题信息表question，其记录了试题的文本，数据量10w。可以看到，查询的瓶颈主要是存在于user_score表。

user_score

字段	类型	描述
id	bigint(20)	唯一id
user_id	bigint(20)	用户唯一id
name	varchar(64)	名字
score	float	分数
question_id	bigint(20)	试题id，指向试题信息表中的id字段
answer	varchar(4096)	解答内容

question
 

字段	类型	描述
id	bigint(20)	唯一id
content	varchar(4096)	试题内容

垂直划分

将一张表的数据，根据场景切分成多张表，本质是由于前期抽象不足，需要将业务数据进一步拆分。

 

一种思路是将长度比较大、不常用的信息，移到扩展表，拿我们的user_score表来说，其数据量庞大，同时answer字段远大于其他字段，如果是查询用户的排名，按得分展示列表的场景，那么一般点击详情才会去看回答，因此，answer非常适合抽成扩展表。
 

 

解答表answer
 

字段	类型	描述
id	bigint(20)	唯一id
content	varchar(4096)	解答内容

 

用户得分表user_score
 

字段	类型	描述
id	unsigned bigint	唯一id
user_id	bigint	用户唯一id
name	varchar(64)	名字
score	float	分数
question_id	bigint(20)	试题id，指向试题信息表中的id字段
answer_id	bigint(20)	解答id，指向解答表中的id字段

 

水平分表

水平分表本质是将一张大表拆成多个结构相同的子表。

直观来看表结构都是一样的，可以按某个字段来进行业务划分，也可以按照数据量来划分，划分的规则实际就是按某种维度，预判数据量进行拆分。

根据关键字

可以采用关键字进行分表，让相同关键字的数据落在同一张表里。

为了方便理解，还是回到我们的例子，user_score表可以用user_id这个关键字来分表的：使用user_id mod 10，即可把user_score表分为10个小表，小表的名字为user_score_{user_id%10} 这种形式。

这种方案适用于按关键字查询频繁的场景，关键字相同的数据，必须落在同张表，不然要是有列表分页查询，就会很麻烦。

不足之处在于，可能有比较多的大客户落在同一张表，分表数据不均匀。假设在我们的场景，支持代理机构注册一个用户id，多个学生可以使用同一个用户id参加考试，那么某个用户的数据量有可能非常大。

如果user_id 为1和user_id为11的用户占据了80%的数据量，而他们mod 10之后，都会落在user_score_1这张表，那么user_score_1就会出现数据量过大的问题，所以动态调整能力，即调整user_id分表规则的能力，在本方案中或不可缺。

 

根据大小
 

直接根据数据量进行分表，比如每200w记录，就自动生成一张新表。

为了方便理解，还是回到我们的例子，user_score表每200w数据来分表，即头200w的数据，落在user_score_1，后面的200w落在user_score_2，以此类推。

这种方案适用于数据和关键字无关的场景，如单纯的流水记录表，如果是和某个关键字扯上了关系，那么会导致跨表查询，比如查询某个用户的平均考试分数，就会比较麻烦，因为无法知道一个用户横跨了多少个子表。

本方案优点是大小均匀，性能可控。这里建议每个分表不超过500万行数据，这样对数据库造成的压力不会太大；缺点是有比较多的场景限制。

 

根据时间
 

根据时间来进行分表，比如按天、按月、按年。如账单流水表就可以按月分表，或者某些任务处理记录表，都可以按时间来划分。

为了方便理解，还是回到我们的例子，user_score表按每个月来分表，那么2021年1月的数据会落在user_score_202101，2月会落在user_score_202102。这种方案适用于数据和时间成关联，热度也跟时间成关联的场景。

其优点在于思路简单，且很容易清理掉旧数据，整个表能自动变冷。缺点在于业务初期，对业务量的预估，会存在难度：可能一开始月表绰绰有余，后期随着业务量突飞猛进，一个月都有千万条甚至上亿条数据，此时又得进行拆分。另外，用该方案时一定要注意时间分割的节点，会不会造成有相关联的数据出现不一致。

分表的实现方式

分表逻辑一定是在一个公共的，可复用的位置来实现。

一般有两种选择：①在公共包里实现；②在一个中间件服务实现。

公共包实现

本地依赖包，即将分表逻辑写在公共的代码库里，每个需要调用服务的客户方都集成该公共包，就接入了自动分表的能力。

优点在于简单，不引入新的组件，不增加运维难度。缺点是公共包更改后每个客户端都需要更新。在访问数据库的服务较少且完全可控时，可以选择该方案。

中间件实现

可以是服务级别的中间件，有自己独立的进程，通过该进程来调用数据库，这样分表逻辑就是中心化，完全可控的，代理服务就属于这类。

 

这种方式的优点是方便更改、耦合性低、架构清晰。缺点是增加了运维成本。

 

这里给大家推荐一个开源组件——Mycat，它是一个优秀的数据库中间件，其本质就是提供代理服务，对数据库进行访问，提供包括读写分离、分库分表等能力。部署容易，耦合性低，感兴趣的朋友可以了解一下。

 

 

推荐方式

在当前微服务架构下，一个业务所需调用的进程，很容易达到数十个甚至更多，如果使用依赖包模式，在更改了相关逻辑后，所有服务都需要配合进行升级，不仅麻烦还存在遗漏的风险，拖累了微服务架构。

 

选择哪种方式才是最优解？不用问，问就是服务级中间件！

针对现有表做拆分

在设计之初就考虑清楚，最好层层评审，一开始就设计一个长期稳定的分表策略，尽量不要对已有表进行拆分。如果实在不得已，需要对老项目进行拆分，通常要考虑如下几个问题：

 

1. 分表过程中，是否可以停服？
 

2. 如果不停服，怎么保证数据一致性？

针对不同场景，自然有不同的解决方案，这里只讲一种最复杂的情况，即在持续比较大的访问流量下，如何在不停服的情况下进行拆分？

 

通常来说， 可以按如下几个阶段操作：

 

1. 双写读老阶段：通过中间件，对write sql同时进行两次转发，也就是双写，保持新数据一致，同时开始历史数据拷贝。本阶段建议施行一周；

 

 

2. 双写双读阶段：采用灰度策略，一部分流量读老表，一部分流量读新表，读新表的部分在一开始，还可以同时多读一次老表数据，进行比对检查，观察无误后，随着时间慢慢切量到新表。本阶段建议施行至少两周；

 

3. 双写读新阶段：此时基本已经稳定，可以只读新表，为了安全保证，建议还是多双写一段时间，防止有问题遗漏。本阶段建议周期一个月；

 

 

4. 写新读新阶段：此时已经完成了分表的迁移，老表数据可以做个冷备。

 

 

看着很简单的四个步骤，但在业务量已经比较庞大的情况下，每个步骤的实施都会带来很多麻烦，首先为了安全，每一阶段通常需要比较大的流转时间，也就是说可能已经跨越了多个开发版本。其次是会带来短期性能损失——无论是双写，还是读检查，都做了额外的数据请求。在同样的请求量下，服务响应时间至少增大了一倍。