MySQL大数据优化分表（水平拆分和垂直拆分）

小胡
2017-02-06 14:00:32
MySQL
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张表中，怎么样,是不是很简单呢，原理就是通过求余来确定数据的落表地点，进而直接查询该表，均匀打散了数据，并且有规律可循，避免了单表的压力过大，相同理论，该分表方法也可以运用到其他方面，本文只是做一下探讨，实际上求余的操作，都是由中间件完成，不应该放在业务中，因为太繁琐，比如查询某个日期的数据，必须每张表都要执行查询最终合并结果集返回