給 Eloquent 的 whereHas 加個 where in 的優化

huiren發表於2018-06-26
優化

原文地址：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));
    }
}

補充：評論區 @overtrue 提供了用 macro 的方式，更優雅，支援場景更好。可以直接拿去用。感謝 overtrue！

補充二：經常想不起來看帖子，留個微信，需要可以交流 ZDNocFpGOXhZWGx2YTNWblptZHhOM2t5TWc9PQ==

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章