laravel的mysql的分表及分表联查解决方案

wuchangjian2021-10-28 00:56:04编程学习

0X00 问题初现

php的laravel如何实现水平分表?最近手里有个项目,框架使用php的laravel7框架,由于数据量激增,当时为了快速迭代开发的详情表现在一晚上就干出30万行的数据量,主表已经一千两百万的数据。因为这只是用来存储记录用户视频详情,所以决定使用按月进行水平分表。

初期直接使用union all进行联表,导致sql没有走索引,早上六点客户疯狂发消息说生产挂了,赶紧排查发现所有sql都在全表扫描,把SQL服务器打死了,语句如下

select * from (select * from video_time_info_details union all select * from video_time_info_details_10_1) where xxxx

因为xxx条件是在Union之后的,所以不会走索引,所以就挂了,因此对分表这一块进行重构

0x01 重构分表逻辑

1. model插入、更新分表

更新的分表很简单,直接在__construct构造方法里对表新增月数后缀,判断下表是否存在,不存在则创建,存在则直接插入。这里因为属于详情表,调用非常频繁,所以进行缓存,避免每次都查询数据库判断是否存在表,代码如下


class VideoTimeInfoDetails extends Model
{
    use SerializeDate, SplitTableTrait;

    protected $table;

    public function __construct(array $attributes = [])
    {
        parent::__construct($attributes);
        // 手动实现分表,按月分表
        $this->table = 'video_time_info_details_' . date('Y_m') . "_1"; // 现在分表一个月存一次。后缀加_1是为了以后进行按日分表时,可以快速避免按月分表造成的无法联表的问题,避免给以后留坑
        $hasTable = Cache::get('has_' . $this->table, "0"); // 对当前表进行缓存
        if ($hasTable == "0") { // 如果不存在缓存
            if (!Schema::hasTable($this->table)) { // 如果不存在表,则创建表
                DB::update('create table ' . DB::getTablePrefix() . $this->table . ' like ' . DB::getTablePrefix() . 'video_time_info_details');
            }
            Cache::set('has_' . $this->table, "1");
        }
    }
}

这样我们对模型model进行正常的save、update、insert就会自动进行分表

2.model的查询

经过文档研究,准备使用laravel的globalScope对所有目标model进行监听,针对需要进行分表查询的业务流程新增makeUnionQuery方法用于返回model的queryBuilder,并且对sql的表名留下一个特殊的标识,这样我们在我们自己封装的globalScope中监听这个标识,然后把sql和where条件重新拼装,就可以构造带条件的union语句。

(1)对model的处理

对model的boot方法新增一个全局scope,代码如下:

    public static function boot()
    {
        parent::boot();

        static::addGlobalScope(new SplitTableScope); // 该方法在下方
    }

对model新增一个makeUnionQuery方法替换原来的query方法,这样以前的VideoTimeInfoDetail::query()就可以替换为VideoTimeInfoDetail::makeUnionQuery()代码如下:


    /**
     * 构造联表UNION
     */
    public static function makeUnionQuery($startTime,$endTime = null)
    {
        $queryList = static::getSubTablesByMonth($startTime,$endTime); // 要查询的表,该方法在下面
        // 构造联表union
        $queries = collect();
        // 开始循环
        $unionTable = $queries->push("video_time_info_details");
        foreach ($queryList as $suffix) {
            $tempTable = 'video_time_info_details_' . $suffix;
            $hasTable = Cache::get('has_' . $tempTable, "0");
            if ($hasTable == "0") {
                if (!Schema::hasTable($tempTable)) {
                    DB::update('create table ' . DB::getTablePrefix() . $tempTable . ' like ' . DB::getTablePrefix() . 'video_time_info_details');
                }
                Cache::set('has_' . $tempTable, "1");
            }

            if ($hasTable == "1") {
                $queries->push($tempTable);
            }
        }

        $self = new self; // 返回model而不是raw query builder
        $unionTableSql = implode('{SPLIT_TABLE_FLAG}', $unionTable->toArray());

        return $self->setTable(DB::raw("{SPLIT_TABLE}$unionTableSql{SPLIT_TABLE}"));
    }

其中,getSubTablesByMonth方法为封装的用来获取,从制定开始日期到目标结束日期里,根据月数构建表名的方法,代码如下

/**
     * 按月分表
     */
    public static function getSubTablesByMonth($startTime, $endTime = null)
    {
        $endTime = empty($endTime) ? time() : $endTime;
        if ($endTime instanceof \Illuminate\Support\Carbon) {
            $endTime = $endTime->timestamp;
        }
        $now = strtotime(date("Y-m-1", $endTime)); // 当前1号的时间戳
        $indexTime = empty($startTime) ? time() : $startTime;
        if ($indexTime instanceof \Illuminate\Support\Carbon) {
            $indexTime = $indexTime->timestamp;
        }
        $indexTime = strtotime(date("Y-m-1", $indexTime)); // 开始时间的1号时间戳
        $queryList = [];
        while ($indexTime <= $now) {
            $queryList[] = date('Y_m', $indexTime) . "_1";
            $indexTime = strtotime("+1 month", $indexTime);
        }
        $queryList = array_unique($queryList); // 要查询的表
        return $queryList;
    }

接下来,创建类SplitTableScope,实现在执行mysql前对结果进行监听、替换,代码如下


namespace App\Scopes;

use Illuminate\Database\Eloquent\Scope;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\ScopeInterface;
use Illuminate\Support\Facades\DB;

class SplitTableScope implements Scope
{
    /**
     * Apply the scope to a given Eloquent query builder.
     *
     * @param  \Illuminate\Database\Eloquent\Builder  $builder
     * @param  \Illuminate\Database\Eloquent\Model  $model
     * @return void
     */
    public function apply(Builder $builder, Model $model)
    {
        $query = $builder->getQuery();
        $from = $query->from;
        $query->from = null;
        if (strpos($from, "{SPLIT_TABLE}") === false) {
            return;
        }
        $splitFrom = str_replace("{SPLIT_TABLE}", "", $from);
        $splitTables = explode("{SPLIT_TABLE_FLAG}", $splitFrom);

        // 处理where
        $wheres = (array)$query->wheres;

        $columns = empty($query->columns) ? "*" : $query->columns;
        $bindings = empty($query->bindings) ? [] : $query->bindings;
        $myBindings = []; // 记录bindings否则后面会出现?与参数不一致的情况
        // 简单拼一下
        $queries = collect();
        foreach ($splitTables as $table) {
            $tempDb = DB::table($table);
            $tempBindings = [];
            foreach ($wheres as $key => $where) {
                $tempDb->where($where['column'], $where['operator'], $where['value'], $where['boolean']);
                $tempBindings[] = $where['value'];
            }
            $myBindings = array_merge($myBindings, $tempBindings);
            $queries->push($tempDb->select($columns));
        }

        $firstQuery = $queries->shift();

        $queries->each(function ($item, $key) use ($firstQuery) {
            $firstQuery->unionAll($item);
        });

        $bindings = array_merge($myBindings, $bindings);
        $query->bindings = $bindings;
        $query->from = DB::raw("({$firstQuery->toSql()}) as video_time_info_details_all");

    }
}

以上,就对我们原有的sql的from进行了一次替换,并且在原有的基础上将bindings拼接上了,之后执行的wherelimit都将进行自动拼接新sql,而且会走索引,速度大大提高。具体的调用方法如下

VideoTimeInfoDetails::makeUnionQuery($LearnTime->created_at,$LearnTime->updated_at)->where('video_time_info_id',$LearnTime['id'])->orderByDesc('start_play_time')->first();

0x02 生产现状

因为还有一个端是使用的go语言,所以go的分表查询在后面再赘述吧。看下现在生产的表行数量:

1.表数量

在这里插入图片描述
因为是在10月份使用的分表,所以为了防止之前月份出现问题,在创建的时候也会根据时间对之前的日期进行创建

2.主表数量

在这里插入图片描述
1800万的时候进行分表

3.10月份表数量

在这里插入图片描述
也到了400万了,后面还得细分

0x03 结束语

因为这个项目的detail必须存,而且要保存3年,当时没想到数据量会增长的如此之快,早知如此当时该全部存到mongodb之类的日志数据库里了。不过好在甲方爸爸有钞能力,这样也可以顶住压力~

相关文章

MBR平板膜迎战生活污水 可实现达标排放

MBR平板膜迎战生活污水 可实现达标排放

  近年来,农村居民的生活水平有了很大提高。卫生设施的普及导致大量生活污水...

并发编程最新2021年面试题及答案,汇总版

并发编程最新2021年面试题及答案,汇总版

1、堆的作用是什么? 堆是虚拟机所管理的内存中最大的一块,...

基于MATLAB的汽车出入库计时计费系统

汽车出出入库计时计费系统的核心技术就是车牌识别系统。 图一:车牌汉字...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。