分库分表实战及中间件（一）

ゝ一世哀愁。 2022-11-12 01:48 199阅读 0赞

# 分库分表实战及中间件 #

# 背景描述 #

在项目中，使用单库单个mysql去存储数据，其中我们某个表的数据量目前是3000w 、某个表由于客户一些创建的数据几乎每天增量数据是几十万，而且每个客户相对对应的数据增量也不仅相同。考虑到之后到某个节点时间数据可能会达到上限1个亿。  
我们都知道mysql单表数据量是3000w-5000w左右，如果增量再多，单表的查询效率会变慢。

对此我们需要将其分开存储，也就是需要考虑分库分表的时候了。那我们如何进行分库分表操作呢？

## 为什么要分表 ##

上面也说了，由于单表的数据量在未来会达到一个亿级别甚至更高，单表查询效率会变慢。

这儿是单表查询数据量会变大。故此我们采用分表操作，业务并不需要分库操作。

即`单表数据量太大`

在之后的查询、插入、更新操作都会变慢，在加字段、加索引、机器迁移都会产生高负载，影响服务。

### 对于分表我们怎么选择？ ###

分表分为水平分表和垂直分表

#### 水平分表 ####

针对数据量巨大的单张表（比如订单表），按照规则把一张表的数据切分到多张表里面去。但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。

缺点是：并没有解决单个数据库并发以及IO的提升，如需要提升可增加机器配置，或者进行水平分库又分表。  
好处就不用多说了。

###### 水平分表规则 ######

*  RANGE

`时间`：按照年、月、日去切分。例如order\_2020、order\_202005、order\_20200501

`地域`：按照省或市去切分。例如order\_beijing、order\_shanghai、order\_chengdu

`大小`：从0到1000000一个表。例如1000001-2000000放一个表，每100万放一个表HASH

用户ID取模不同的业务使用的切分规则是不一样，就上面提到的切分规则，举例如下：

站内信

`用户维度`：用户只能看到发送给自己的消息，其他用户是不可见的，这种情况下是按照用户ID hash分库，在用户查看历史记录翻页查询时，所有的查询请求都在同一个库内用户表

`范围法`：以用户ID为划分依据，将数据水平切分到两个数据库实例，如：1到1000W在一张表，1000W到2000W在一张表，这种情况会出现单表的负载较高

按照用户ID HASH尽量保证用户数据均衡分到数据库中如果在登录场景下，用户输入手机号和验证码进行登录，这种情况下，登录时是  
不是需要扫描所有分库的信息？

`最终方案`：用户信息采用ID做切分处理，同时存储用户ID和手机号的映射的关系表（新增一个关系表），关系表采用手机号进行切分。可以通过关系表根据手机号查询到对应的ID，再定位用户信息。

流水表

`时间维度`：可以根据每天新增的流水来判断，选择按照年份分库，还是按照月份分库，甚至也可以按照日期分库

##### 垂直分表 #####

表中字段太多且包含大字段的时候，在查询时对数据库的IO、内存会受到影响，同时更新数据时，产生的binlog文件会很大，MySQL在主从同步时也会有延迟的风险。

大白话即一个表中有很多字段假如50个字段，平时我们经常使用的有5-10个字段，对于其他字段万年几乎使用一次，对此我们可以通过主键关联将其拆到另一张表中。这就是分表可以理解为`类似`于我们将单体服务拆分成多个微服务。

缺点是：如果拆分不好的话，多出一张表，对于其他业务，需要关联使用，增加额外表关联操作。  
好处就不用多说了。

对于目前这儿的业务而言，目前采用分表操作。

## 分析 ##

根据我们目前的业务（具体是做什么的，由于工作的原因，目前无法透露)，由于我们的数据量对于订单表和某个表以下都简称C表，尤其是C表数据量在未来可遇见的情况下，数量会达到一个量。

所以这儿我们采用了对此C表进行分表操作，简而言之根据每个客户对应一个表进行分表操作。

简单的来说，A,B,C,D,E,F…M客户，分表之前产生的数据都在C表中。  
在我们分表之后A,B,C,D,E,F等表分表对应C1,C2,C3,C4等表。

理论上来说可以每个客户对应一个表C，但实际的情况是，每个客户产生的数据不是相同的，有的客户一天甚至几w数据，有的客户甚至以后个月才1W数据。

故此，在分表之前需要对客户数据进行分析，可以通过一些sql分析查询，假设统计分析之后数据如下

对于ABCE等客户可以每个对应一个表C1,C2,C3,C4对于EHI我们可以让其对应表C5 , 其实对于F也可以对应一张表(可以根据自己的业务进行调整)

也就是如下图

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70]

## 实现 ##

以上是简单的对业务分表的分析，然后才能对齐进行分表。

我们使用了客户(这儿根据客户id)进行分表，然后我们开始选择技术栈，对于市面上的分表中间件而言，有`MyCat` 和`Sharding-JDBC`  
首先我们不使用中间件而言如何进行分表操作呢。

对于程序而言，在查询的时候根据不同的id去不同的C表(C1,C2,C3,C4等)中进行查询，新增更新删除也如此。  
就需要我们手动的去指定响应的路由规则即根据id去找到相应的表C；

对此手动路由可以看一下代码

Map<Integer,String> map =  new HashMap<>();
            map.put(1,"C1");
            map.put(2,"C2");
            map.put(3,"C3");
            map.put(4,"C4");
            map.put(5,"C5");
            map.put(6,"C6");
            map.put(7,"C6");
            
            
            //获取表C
            map.get(1);

在代码中进行JDBC操作时候，我们手动的获取响应的表，然后通过替换符在sql中将C表进行替换即可。基本不影响sql操作包括join连表。

SELECT
            s.a,
            s.b,
        FROM
            ${tableName} ss

但对于查询所有客户数据而言，因为分表的缘故，所有的数据都不在一个表中。对此我们可能需要将其数据都查询出来，然后将其组合起来。在sql或者java代码中都可以操作。

在Sql中，可以通过`union`关键字将其所有结果都关联起来查询返回，在业务代码中可以通过for循环等操作将其查询组合起来。

这样也存在`缺点`：

`sql`中sql长度变长，编译sql时间长，全部查询需要更改sql  
`业务代码中`需要编写代码，需要组合查询，额外查询次数变多（单表中查询需要一次），这儿也可以通过保存一份所有的id数据表C。

## 最后 ##

在上线前需要迁移数据，将不同id对应的数据迁移到不同的C表中。

## 总结 ##

#### 在代码中手动去路由分表 ####

优点：简单，不需要引入其他中间件，复杂sql也能自己去实现（sharding-jdbc某些sql不支持）。

缺点也是显而易见的，代码改动大，成本大，几乎所有需要用到的C表都需要手动根据路由去获取C表，然后替换。后续业务发生变化，还需要改动代码。

#### 分表优缺点 ####

优点：单表数据分散存储在不同的表中，提高查询速度。  
缺点：没有解决单个数据的IO以及连接数以及并发。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODM2MTM0Nw_size_16_color_FFFFFF_t_70]: /images/20221022/e8064b57d267472abbe2e495d6b58ecf.png