分库分表实战及中间件

背景描述

在项目中,使用单库单个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分析查询,假设统计分析之后数据如下

客户 数据量 /月
A 9W
B 10W
C 11W
D 0.2W
E 5W
F 7W
H 0.8W
I 2W

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

也就是如下图

分库分表实战及中间件-LMLPHP

实现

源码:https://www.douban.com/note/797783027/

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

我们使用了客户(这儿根据客户id)进行分表,然后我们开始选择技术栈,对于市面上的分表中间件而言,有MyCatSharding-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以及连接数以及并发。

04-01 05:27