给 Eloquent 的 whereHas 加个 where in 的优化
84

原文地址: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 于 2个月前 加精
《L03 构架 API 服务器》
你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程,JWT 概念及使用 和 API 开发相关的进阶知识。
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
讨论数量: 20

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

2个月前

@rshu 都是坑出来的 ?

2个月前

可以向官方提PR

2个月前

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

2个月前

file

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

2个月前

改成 join会不会更好点?

1个月前

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

1个月前

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

1个月前

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

1个月前

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

1个月前
ALMAS

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

1个月前

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

1个月前

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

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

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

1个月前

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

2周前
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 基类。

1周前
overtrue

@linzi 参考我的回复

1周前

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

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

1周前

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

3天前

  • 请注意单词拼写,以及中英文排版,参考此页
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`, 更多语法请见这里 Markdown 语法
  • 支持表情,使用方法请见 Emoji 自动补全来咯,可用的 Emoji 请见 :metal: :point_right: Emoji 列表 :star: :sparkles:
  • 上传图片, 支持拖拽和剪切板黏贴上传, 格式限制 - jpg, png, gif
  • 发布框支持本地存储功能,会在内容变更时保存,「提交」按钮点击时清空
  请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!