MySQL大数据优化分表(水平拆分和垂直拆分)

MySQL大数据优化分表(水平拆分和垂直拆分)

  • 在我们业务中,在并发很高的IO下,往往是需要水平分表(分库)或者是垂直分表(分库)。

1,水平拆分:

例:QQ的登录表。假设QQ的用户有100亿,如果只有一张表,每个用户登录的时候数据库都要从这100亿中查找,会很慢很慢。如果将这一张表分成100份,每张表有1亿条,就小了很多,比如qq0,qq1,qq1...qq99表。 用户登录的时候,可以将用户的id%100,那么会得到0-99的数,查询表的时候,将表名qq跟取模的数连接起来,就构建了表名。比如123456789用户,取模的89,那么就到qq89表查询,查询的时间将会大大缩短。 这就是水平分割。

比如我们业务中会将一个突变表信息表分为10个表,id%10(id mod 10),看id的尾数落到哪个数字上,就存入类似以_n结尾的数据表中(n表示0-9的数字)

2,垂直拆分:

垂直分割指的是:表的记录并不多,但是字段却很长,表占用空间很大,检索表的时候需要执行大量的IO,严重降低了性能。这时需要把大的字段拆分到另一个表,并且该表与原表是一对一的关系。 例如学生答题表tt:有如下字段: Id name 分数 题目 回答 其中题目和回答是比较大的字段,id name 分数比较小。 如果我们只想查询id为8的学生的分数:select 分数 from tt where id = 8;虽然知识查询分数,但是题目和回答这两个大字段也是要被扫描的,很消耗性能。但是我们只关心分数,并不想查询题目和回答。这就可以使用垂直拆分。我们可以把题目单独放到一张表中,通过id与tt表建立一对一的关系,同样将回答单独放到一张表中。这样我们插叙tt中的分数的时候就不会扫描题目和回答了。

详细介绍水平分表之取模算法:

假设一个场景,APP需要手机号登录或者注册,而一张用户表中已经有千万级的数据,单表的IO读写已经很吃力,需要迫切的分表,那么该如何分表,以什么形式去分

分表方法:

  • 1.取模算法
  • 2.哈希算法
  • 3.范围分表(时间划分,数值划分)

本文针对水平分表的取模算法来分析:

1.取模实际上就是对被除数进行取余的操作 A%B=余数 2.利用用户手机尾号进行求余的操作 3.假如我们水平分表5张表,那么求余公式则为尾号/5=余数 4.这个余数就是我们的表名,进而得出该用户信息应该在哪张表 5.业务直接去以余数命名的该表查询信息

(1)原始业务图解:

(2)将要实现的:

(3)开始实践:

我们将用户表设计为5张表 从user_0....user_4;

表结构如下:

字段name即为手机号

了解了负载均衡与消息队列,这次将请求还是与之前的结合起来

producer项目里创建controller

public function AddUser():void {
        $data['name'] = time();
        $json = json_encode($data);
        dispatch(new UserJob($json))->onQueue('User');
    }
`

将当前时间戳模拟为用户手机号,并推入到RabbitMQ

consumer项目的队列消费者进程开始消费

public function handle(){
        $data = json_decode($this->data,true);
        $table = 'User_'.$data['name']%5;
        $tableObj = $this->tableObj($table);
        $tableObj->name = $data['name'];
        $tableObj->save();
    }

    /**
     * 通过反射返回Model对象
     * @param $class_name
     * @return object
     * @author mjShu
     * @throws \ReflectionException
     */
    private function tableObj($class_name){
        $class_name = 'App\Http\Model\\'.$class_name;
        $class = new \ReflectionClass($class_name);
        $obj = $class->newInstance();
        return $obj;
    }

在handle方法里,我们通过对producer生产的数据中的模拟手机号进行求余 得到余数,通过字符串拼接得到表名,表名等于User_余数

进而通过PHP的反射机制实例化Model类,将其保存进表

启动AB测试(详细AB测试的操作请跳转:https://blog.ekanshu.com.cn/article/106)

ab -c 100 -n 2000 http://192.168.2.101/user

RabbitMQ消费后通过Lumen队列插入数据库

启动horizon,应该如图

等RabbitMQ ACK确认完毕后,我们来查看下表: 由此可见,我们的数据均是是被水平均匀的拆分到5张表中,怎么样,是不是很简单呢,原理就是通过求余来确定数据的落表地点,进而直接查询该表,均匀打散了数据,并且有规律可循,避免了单表的压力过大,相同理论,该分表方法也可以运用到其他方面,本文只是做一下探讨,实际上求余的操作,都是由中间件完成,不应该放在业务中,因为太繁琐,比如查询某个日期的数据,必须每张表都要执行查询最终合并结果集返回

问题汇总

(1)分表数量又增加了怎么办啊?取模规则改变的话,以前的数据怎么取到呢? 一,迁移 二,业务判断

(2)通过userid去查询的时候怎么落在表上?userid的自增长跟表名一致可以解决吗? 你需要保存一张映射表,以确保通过取余的数可以直接找到表名

(3)如果是汇总统计是不是就麻烦了 是的,但类似MyCat这样的分布式中间件也是这样的,每个表都会搜索返回,再归总

胡梦旭博客
请先登录后发表评论
  • latest comments
  • 总共0条评论