【Laravel】 Eloquent の has() や whereHas() が遅い？なら速くしてやるぜ

はじめに

2021/10/21 追記: Postgres は MySQL と異なり，相関サブクエリの JOIN 最適化もカバーしています。それゆえ，主にこの記事の内容は MySQL を想定したものとなっております。 （情報提供: @KentarouTakeda）

2022/12/22 追記: MySQL もバージョン 8.0.16 以降では Postgres に似た最適化が入るようになりました。そのため，殆どの場合ではもはや has() whereHas() を素直に使うだけで十分になっています: 【ガチ検証】Eloquent whereHas() はもう遅くないよ！

Laravel は「リレーション先が存在するか」という制約条件を has() whereHas() メソッドで表現できます。

• Eloquent: Relationships - Laravel - The PHP Framework For Web Artisans
• Eloquent：リレーション 7.x Laravel

取得結果が複数になる HasMany と，取得結果が単一になるHasOne BelongsTo の， 2 通りのパターンを考えましょう。

例

以下のようなモデル定義があるとします。

class Post extends Model
{
    use SoftDeletes;

    public function comments()
    {
        return $this->hasMany(Comment::class);
    }
}

class Comment extends Model
{
    use SoftDeletes;

    public function post()
    {
        return $this->belongsTo(Post::class);
    }
}

HasMany の場合

$posts = Post::query()
    ->has('comments')
    ->limit(5)
    ->get();

select * from `posts`
where exists (
  select * from `comments`
  where `posts`.`id` = `comments`.`post_id`
    and `comments`.`deleted_at` is null
)
and `posts`.`deleted_at` is null
limit 5

posts.id がサブクエリの中で参照されていて，これは 1 行ごとに変化する値であるため， MySQL は 1 行ごとにサブクエリを実行 します。これは 相関サブクエリ と呼ばれ，場合によっては著しいパフォーマンスの低下を引き起こします。

但し，相関サブクエリがいつでも悪者になるというわけではありません。

• メインクエリのみで十分に絞り込めている場合，相関サブクエリを素直に使ってもいい
• メインクエリのみで絞り込みが不足している場合，相関サブクエリは避けたほうが無難

サブクエリを繰り返し実行したとしても，メインクエリのみの条件で絞り込みが十分に行われていれば，サブクエリの実行回数が削減されるため，問題ないと言えるケースもあるでしょう。以降では，メインクエリのみの条件で十分に絞り込めないケースを見ていきます。

相関サブクエリを JOIN に変換する

まずは誰もが思いつきそうな JOIN を試してみましょう。

$posts = Post::query()
    ->join('comments', function (JoinClause $join) {
        $join->on('posts.id', '=', 'comments.post_id');
        $join->whereNull('comments.deleted_at');
    })
    ->limit(5)
    ->select('posts.*') // 不要なカラムが含まれないように posts.* のみに絞り込む
    ->get();

select `posts`.* from `posts`
inner join `comments`
        on `posts`.`id` = `comments`.`post_id`
       and `comments`.`deleted_at` is null
where `posts`.`deleted_at` is null
limit 5

シンプルな JOIN クエリになりました！

但し，これは期待したように動作しません。なぜならば， HasMany の相手を JOIN すると 自分自身のレコードも増えてしまう からです。

期待する結果

+--------+-------------+
| Post 1 | Comment 1-1 |
|        | Comment 1-2 |
|        | Comment 1-3 |
|        | Comment 1-4 |
|        | Comment 1-5 |
+--------+-------------+
| Post 2 | Comment 2-1 |
+--------+-------------+

実際の結果

+--------+-------------+
| Post 1 | Comment 1-1 |
+--------+-------------+
| Post 1 | Comment 1-2 |
+--------+-------------+
| Post 1 | Comment 1-3 |
+--------+-------------+
| Post 1 | Comment 1-4 |
+--------+-------------+
| Post 1 | Comment 1-5 |
+--------+-------------+
| Post 2 | Comment 2-1 |
+--------+-------------+

対策としては DISTINCT が使えます。

$posts = Post::query()
    ->join('comments', function (JoinClause $join) {
        $join->on('posts.id', '=', 'comments.post_id');
        $join->whereNull('comments.deleted_at');
    })
    ->limit(5)
    ->select('posts.*') // 不要なカラムが含まれないように posts.* のみに絞り込む
    ->distinct()
    ->get();

select distinct `posts`.* from `posts`
inner join `comments`
        on `posts`.`id` = `comments`.`post_id`
       and `comments`.`deleted_at` is null
where `posts`.`deleted_at` is null
limit 5

一応これで解決はできます。但し， DISTINCT + LIMIT の組み合わせはテンポラリテーブルの生成などによって LIMIT の性能を悪化させてしまう 場合があり，銀の弾丸とは言えません。論理削除など モデルのグローバルスコープの機能も使えない ので，自分でそれらの制約条件を付与する必要がある点も懸念点です。

相関サブクエリを通常のサブクエリに変換する

以下のようにすると，相関サブクエリを通常のサブクエリに変換することができます。 whereIn() は第 2 引数にサブクエリを取ることができます。

$posts = Post::query()
    ->whereIn('posts.id', Comment::query()->select('comments.post_id'))
    ->limit(5)
    ->get();

select * from `posts`
where `posts`.`id` in (
  select `comments`.`id` from `comments`
   where `comments`.`deleted_at` is null
)
and `posts`.`deleted_at` is null
limit 5

comments テーブルに対するクエリから posts のカラム参照が消えて，相関サブクエリが普通のサブクエリになりました！またこちらの方法では DISTINCT を使わずに済んでいます。グローバルスコープが自動で付与されている点もポイントが高いです。

実際の実行計画としては JOIN とほぼ同等になる可能性が高いですが， DISTINCT を使用していないためテンポラリテーブル問題は解決できるでしょう。 JOIN よりはこちらを優先して使っていきたいところです。

• MySQL 5.5 の場合はサブクエリの最適化が効かない可能性があるので， JOIN を使ったほうが無難でしょう。
• MySQL 5.6~5.7 でも UPDATE DELETE に対しては最適化が効かないので，その際も JOIN を使いましょう。
• MySQL 8.x の場合はサブクエリ統一で問題なさそうです。

HasOne BelongsTo の場合

相関サブクエリを JOIN に変換する

HasMany は芳しくない結果になってしまいましたが， 取得結果が単一になれば何の問題もありません。躊躇なく JOIN しちゃって OK です。

$comments = Comment::query()
    ->join('posts', function (JoinClause $join) {
        $join->on('comments.post_id', '=', 'posts.id');
        $join->whereNull('posts.deleted_at');
    })
    ->limit(5)
    ->select('comments.*') // 不要なカラムが含まれないように comments.* のみに絞り込む
    ->get();

select `comments`.* from `comments`
inner join `posts`
        on `comments`.`post_id` = `posts`.`id`
       and `posts`.`deleted_at` is null
where `comments`.`deleted_at` is null
limit 5

相関サブクエリを通常のサブクエリに変換する

こちらの方法でもいけます！サブクエリは万能でいいなぁ〜

$posts = Post::query()
    ->whereIn('posts.id', Comment::query()->select('comments.post_id'))
    ->limit(5)
    ->get();

select * from `posts`
where `posts`.`id` in (
  select `comments`.`post_id` from `comments`
   where `comments`.`deleted_at` is null
)
and `posts`.`deleted_at` is null
limit 5

ライブラリを作りました

こんなもん手作業で書いてたら保守性が終わるので， Laravel 標準の has() whereHas() に遜色無い感じで使えるライブラリを ２ つ実装しました！

• mpyw/eloquent-has-by-non-dependent-subquery: Convert has() and whereHas() constraints to non-dependent subqueries.
• mpyw/eloquent-has-by-join: Convert has() and whereHas() constraints to join() ones for single-result relations.

できることに差はありますが，基本的にはサブクエリ版のほうが上位互換だと思ってください。どっちもインストールしておくのもありです。

コメントを持つ投稿を 5件 取得

$posts = Post::query()
    ->hasByNonDependentSubquery('comments')
    ->limit(5)
    ->get();

コメント先の投稿が削除されていないコメントを 5 件取得

$comments = Comment::query()
    ->hasByNonDependentSubquery('posts')
    ->limit(5)
    ->get();

自分のコメントで，かつコメント対象の投稿を作成したユーザの名前が John であるものを 5 件取得

$comments = Auth::user()
    ->comments()
    ->hasByNonDependentSubquery(
        'posts.author',
        null,
        fn (BelongsTo $q) => $q->where('name', 'John')
    )
    ->limit(5)
    ->get();

自分のコメントで，かつコメント対象の投稿を作成したユーザの名前が John であるものを 5 件取得，さらに投稿が論理削除されているものも含める

$comments = Auth::user()
    ->comments()
    ->hasByNonDependentSubquery(
        'posts.author',
        fn (BelongsTo $q) => $q->withTrashed(),
        fn (BelongsTo $q) => $q->where('name', 'John')
    )
    ->limit(5)
    ->get();

これで最高の Eloquent ライフを送りましょう！！！