给 Eloquent 的 whereHas 加个 where in 的优化

原文地址:https://www.jianshu.com/p/ec2a93755e10

用 Laravel 很久了,whereHas 简直是连表大杀器,本来需要写大量 SQL 的查询用 whereHas 都可以很快的实现。不过在一些场景里,遇到了严重的性能问题。

我们有个A表,大约是百万级数据,与之关联的有个B表,大约万级数据。在做关联查询的时候我们自然使用 A::whereHas('b', function(){...})

后来发现了许多慢查询,仔细一看发现,Laravel 的 whereHas 在生成 SQL 的时候会使用 select * from A where exists ( select * from b where ... ) 。当我们的左表远远大于右表时,A 表就成了性能瓶颈。

最直接的方法当然是拆成两条 SQL,但是嫌麻烦,还得一条条优化。再加上我们很多 SQL 都是靠各种工具生成,所以改起来也挺麻烦。

于是就考虑加了个 whereHasIn 的方法,接口参数跟 whereHas 一致,只不过在生成 SQL 的时候会生成 select * from A where A.id in (select id from B)。这样就不需要改什么 SQL 了,只要在调用 A::whereHas() 的地方加两个字符变成 A::whereHasIn() 就搞定了。在实际中,我们这条查询的耗时从几秒一下降低到了20毫秒。

下面是一个实现的 demo,暂时只支持 一对多的情况。如果大家有什么更好的想法,一起讨论讨论。

<?php

use Illuminate\Database\Eloquent\Relations;

abstract class AbstractModel
{
    /**
     * whereHas 的 where in 实现
     *
     * @param \Illuminate\Database\Eloquent\Builder $builder
     * @param string $relationName
     * @param callable $callable
     * @return Builder
     *
     * @throws Exception
     */
    public function scopeWhereHasIn($builder, $relationName, callable $callable)
    {
        $relationNames = explode('.', $relationName);
        $nextRelation = implode('.', array_slice($relationNames, 1));

        $method = $relationNames[0];
        /** @var Relations\BelongsTo|Relations\HasOne $relation */
        $relation = Relations\Relation::noConstraints(function () use ($method) {
            return $this->$method();
        });

        /** @var Builder $in */
        $in = $relation->getQuery()->whereHasIn($nextRelation, $callable);

        if ($relation instanceof Relations\BelongsTo) {
            return $builder->whereIn($relation->getForeignKey(), $in->select($relation->getOwnerKey()));
        } elseif ($relation instanceof Relations\HasOne) {
            return $builder->whereIn($this->getKeyName(), $in->select($relation->getForeignKeyName()));
        }

        throw new Exception(__METHOD__ . " 不支持 " . get_class($relation));
    }
}
本帖由 leo 于 8个月前 加精
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L02 从零构建论坛系统》
以构建论坛项目 LaraBBS 为线索,展开对 Laravel 框架的全面学习。应用程序架构思路贴近 Laravel 框架的设计哲学。
讨论数量: 23
overtrue

可以考虑使用 macro 特性来增加该方法:

use \Illuminate\Database\Query\Builder;
.
.
.
Builder::macro('whereHasIn', function($builder, $relationName, callable $callable)
    {
        $relationNames = explode('.', $relationName);
        $nextRelation = implode('.', array_slice($relationNames, 1));

        $method = $relationNames[0];
        /** @var Relations\BelongsTo|Relations\HasOne $relation */
        $relation = Relations\Relation::noConstraints(function () use ($method) {
            return $this->$method();
        });

        /** @var Builder $in */
        $in = $relation->getQuery()->whereHasIn($nextRelation, $callable);

        if ($relation instanceof Relations\BelongsTo) {
            return $builder->whereIn($relation->getForeignKey(), $in->select($relation->getOwnerKey()));
        } elseif ($relation instanceof Relations\HasOne) {
            return $builder->whereIn($this->getKeyName(), $in->select($relation->getForeignKeyName()));
        }

        throw new Exception(__METHOD__ . " 不支持 " . get_class($relation));
    });

你可以将上面这段代码写到 AppServiceProvider 中

这样你就不需要修改原有的模型类了,也不需要创建 Model 基类。

6个月前

这个原始的SQL语句还真没注意过

8个月前

@rshu 都是坑出来的 ?

8个月前
nff93

可以向官方提PR

8个月前

虽然知道有这个性能问题,但是一直没有去改造……感谢分享! :stuck_out_tongue_winking_eye: :+1:

8个月前

file

报错了 SQLSTATE[42S22]: Column not found: 1054 Unknown column 'has_in' in 'where clause' (SQL: select * from tags where has_in = tagsname)

8个月前

改成 join会不会更好点?

7个月前

@linzi 看报错是 tag 没有加这个方法。这个函数我是加在 baseModel 里的。

7个月前

@luffyzhao 这种场景下,性能都差不太多。对 laravel 封装的 join 用的不多,改的时候也没想太多。

7个月前

@linzi 咦,这个 tagsname 是个 relation 么?

7个月前

@huiren 是的 用whereHas可以 用whereHasIn 就报错啦

7个月前
ALMAS

把这个方法Macro到Builder更好用觉得,不用到处添加trait,不过还没试试是否可行。

7个月前

@ALMAS Laravel 提供了 scope 功能来做这个事,其实也相当于定制版的 macro 了 :joy:

7个月前

@linzi 执行过程可以参考 \Illuminate\Database\Eloquent\Builder::__call ,优先找 scope 方法,找不到再找 Query。

所以这个错误应该是 scope 没命中上。

方便的话可以把更详细的错误私信给我

7个月前

这个文件是添加到哪里呢 ,楼主能写详细点吗

6个月前
overtrue

可以考虑使用 macro 特性来增加该方法:

use \Illuminate\Database\Query\Builder;
.
.
.
Builder::macro('whereHasIn', function($builder, $relationName, callable $callable)
    {
        $relationNames = explode('.', $relationName);
        $nextRelation = implode('.', array_slice($relationNames, 1));

        $method = $relationNames[0];
        /** @var Relations\BelongsTo|Relations\HasOne $relation */
        $relation = Relations\Relation::noConstraints(function () use ($method) {
            return $this->$method();
        });

        /** @var Builder $in */
        $in = $relation->getQuery()->whereHasIn($nextRelation, $callable);

        if ($relation instanceof Relations\BelongsTo) {
            return $builder->whereIn($relation->getForeignKey(), $in->select($relation->getOwnerKey()));
        } elseif ($relation instanceof Relations\HasOne) {
            return $builder->whereIn($this->getKeyName(), $in->select($relation->getForeignKeyName()));
        }

        throw new Exception(__METHOD__ . " 不支持 " . get_class($relation));
    });

你可以将上面这段代码写到 AppServiceProvider 中

这样你就不需要修改原有的模型类了,也不需要创建 Model 基类。

6个月前
overtrue

@linzi 参考我的回复

6个月前

return Doctor::whereHas('user',function($query){
$query->select('id');
})->toSql();

直接 select id 会比你们那个快吗@huiren @overtrue

6个月前

@linzi 感觉上区别不大,这个问题的核心是 where exists 会扫左表。跟右表怎么处理关系不大。不过你可以试试。

6个月前

没有看懂scopewhereHasIn这个函数,以及它要怎么调用?

3周前

@阳光的小mi 不好意思最近比较忙没空上来。

scope 是 eloquent 提供的一个类似于 plugin 的功能。能把方法集成到 builder 里。

直接 $query->whereHasIn(...) 就行了。

具体的 scope 的问题,可以详细看一下文档~

5天前

@huiren 感谢回复,问题已解决。

2天前

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!