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

最新推荐文章于 2025-04-07 18:31:44 发布

Framework大林

最新推荐文章于 2025-04-07 18:31:44 发布

阅读量1.5k

点赞数

分类专栏：笔记文章标签： laravel mysql php

本文链接：https://blog.youkuaiyun.com/qq_33974231/article/details/121005211

版权

笔记专栏收录该内容

2 篇文章

订阅专栏

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拼接上了，之后执行的where、limit都将进行自动拼接新sql，而且会走索引，速度大大提高。具体的调用方法如下

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