【Laravel】whereHasで発行されるSQLの実行計画を調査

この記事を書こうと思ったきっかけはこちらの記事です。

Qiita

EloquentのwhereHasメソッドは辛い – Qiita やりたいこと 1対NあるいはN対NをEloquentのリレーションシップで表現(hasManyとか)し、それを用いて、子テーブル側の特定カラムに条件マッチしたレコードを取得する。 …

上記記事でwhereHasメソッドを使ってDBからデータを取得した際に発生しうることが書かれています。

MySQLにおいてパフォーマンス劣化の元凶として目の敵にされているDEPENDENT SUBQUERY がガッツリ発生するのだ。

https://qiita.com/hisash/items/40532808e61ed3210a6a

実務でwhereHasをネストして2回使っているところがあるのですが、この記事を読んで少し気になった（というか怖くなった）ので実際に調査してみたので、その結果とか周辺の知識についてまとめておきます。

まず、whereHasメソッドって？

whereHasはLaravelが採用しているEloquent（ORM）のメソッドで、リレーション先のテーブルのカラムでデータ取得等の条件を指定する時に使います。

例えば、以下のようなテーブル構造だとして

table_aのデータを取得するときに、

table_cのcolumn_2が○○の値であるtable_aのデータだけ取得したい

というケースでwhereHasが使えます。（以下のようなコードになります）

TableA::whereHas('tableB', function (Builder $query) {
    $query->whereHas('tableC', function (Builder $query) {
        $query->where('column2', 'テスト');
})->get();

whereHasの中でwhereHasを使っています。

table_a、table_bに対応するモデルでそれぞれtable_b、table_cにリレーションメソッド（このケースではbelongsTo）を定義している前提です。

DEPENDENT SUBQUERY

初めの記事で問題視されていたDEPENDENT SUBQUERYって何？ということで少し調べました。

そもそもサブクエリって何？というところですが、以下の記事にこのように書かれています。

入れ子になって書かれているSQL文における中に書いてある方のSQL文のこと

https://wa3.i-3-i.info/word17573.html

サブクエリ【SQL】-「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典

例えばこんなSQL。

SELECT * FROM (SELECT * FROM table_a WHERE column_1 = 1000);

これの（）の中のSELECT * FROM table_a WHERE column_1 = 1000がサブクエリです。

ということでDEPENDENT SUBQUERYに戻ります。

これは相関サブクエリと呼ばれる類のサブクエリで、サブクエリにおいて外部クエリのカラムを参照しているサブクエリのことです。

と言ってもよくわからないと感じる人がいるかもしれませんが、車輪の再発明になるのも嫌（というかめんどくさい）なので、「相関サブクエリは普通のサブクエリとどう違うのか？」を具体例を交えて解説している記事があってとてもわかりやすかったので貼っておきます。

Qiita

【SQL】相関サブクエリ入門 – Qiita 相関サブクエリとはサブクエリの一種であり、外側のクエリの値をサブクエリ内で使用する。 相関サブクエリを使用したSQLはややこしくて読みにくい場合が多いが、基本形を…

この記事を読めば「サブクエリにおいて外部クエリのカラムを参照しているサブクエリ」という言葉の意味をちゃんと理解できると思います。

SQL実行計画の見方

LaravelのwhereHasメソッドを使ったデータ取得処理でどのようなSQLが発行されているかを確認する方法は

とかがあると思います。

clockwork、debugbarのGitHubリポジトリはそれぞれこちらです。

GitHub

GitHub – itsgoingd/clockwork: Clockwork – php dev tools in your browser – server-side component Clockwork – php dev tools in your browser – server-side component – GitHub – itsgoingd/clockwork: Clockwork – php dev tools in your browser – server-side compon…

GitHub

GitHub – barryvdh/laravel-debugbar: Debugbar for Laravel (Integrates PHP Debug Bar) Debugbar for Laravel (Integrates PHP Debug Bar). Contribute to barryvdh/laravel-debugbar development by creating an account on GitHub.

実務のコードベースで書くことはできないので、発行されたSQLはこの記事では割愛し、sample_sqlという名前にさせてください。（甘えです）

SQLの実行計画の表示はexplainを使います。（最後に;がないとエラーになります）

explain sample_sql;

実行時にERROR 1046 (3D000): No database selectedが出た場合は、

してあげてください。

実行結果の出力もここでは割愛しますが、

の項目を持った表形式で色々な情報が出力されます。

出力のselect_typeがDEPENDENT SUBQUERYになっているものは相関サブクエリということになります。

僕が書いていたwhereHasでは全てSIMPLEで、これは単純なSELECT文でありサブクエリは発生していないという判定だったので良かったです。

SQLの実行計画の出力結果の見方はこちらの記事がとてもわかりやすいです。

Qiita

MySQLのEXPLAIN（実行プラン）まとめ – Qiita MySQLのEXPLAIN（実行プラン）について、まとめます。 EXPLAINは、クエリがどのように実行されるかを確認できます。 例えば、昨日まで1sだったクエリが、今日は10sかかる…

冒頭では「whereHasを使ったらDEPENDENT SUBQUERYが発生する！」という内容でしたが、テーブル間のリレーションが1対多の多→1（belongsTo）の場合は相関サブクエリが発生しないようです。

ここまで記事を書いてきて

という内容になりそうですが、こちらの記事に以下の記載がありました。

クラスメソッド発「やってみた」系…

MySQL のサブクエリって、ほんとに遅いの？ | DevelopersIO こんにちは、みかみです。 今日のクラスメソッドのAWSおみくじ、RDS（t1.micro）でしたー！（昨日は Aurora！＠＠ｖ はじめに 前職＠アプリ開発時、MySQL のクエリチューニ…

・MySQL 5.5 まで、サブクエリはやっぱり遅い。。

・MySQL 5.6 からはそんなに遅くなくなった！

MySQL のサブクエリって、ほんとに遅いの？

結論、MySQL5.6以降はサブクエリがそこまで（曖昧）遅くないようです。

僕はMySQL8.0を使っていてかつ、そもそもサブクエリが発生していなかったので問題なかったですが、そこまで気にすることでもないケースもそれなりにあります。

この辺は実際にどれくらい処理が重いかを目視で確認して対策するかどうかを決めるのが良さそうです。

最後に

この記事をまとめます。

Laravelの学習にオススメの書籍や教材はこちらにまとめています。

あわせて読みたい

【現役エンジニア厳選】Laravel学習にオススメの書籍・教材（レベル別） プログラミング言語PHPのフレームワークであるLaravelを学習するための書籍・教材の中で、現役Laravelエンジニアの僕がオススメするものをご紹介します。 Laravelを勉強…

SQLが苦手な人はこれで勉強するのがオススメです。

スッキリわかるSQL入門 第3版 ドリル256問付き! (スッキリわかる入門シリーズ)

その他の参考記事

漢(オトコ)のコンピュータ道

漢(オトコ)のコンピュータ道: なぜMySQLのサブクエリは遅いのか。 ちょっと硬派なコンピュータフリークのBlogです。