LoginSignup
200
167

More than 1 year has passed since last update.

@mpyw(mpyw)

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

Last updated at Posted at 2020-06-28

はじめに

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

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

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

取得結果が複数になる 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() に遜色無い感じで使えるライブラリを ２ つ実装しました！

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

コメントを持つ投稿を 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 ライフを送りましょう！！！

200
167
8

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
200
167