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

  • URLをコピーしました!

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

上記記事で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に戻ります。

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

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

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

SQL実行計画の見方

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

  • toSql()を使う
  • clockwork、debugbar等のライブラリを使う

とかがあると思います。

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

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

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

explain sample_sql;

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

  • SHOW databases;でデータベースを一覧表示
  • use データベース名;で使うデータベースを指定

してあげてください。

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

  • id
  • select_type
  • partitions
  • table
  • type
  • possible_keys
  • key
  • key_len
  • ref
  • rows
  • filtered
  • Extra

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

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

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

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

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

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

  • whereHasを使うとDEPENDENT SUBQUERYが発生する可能性がある
  • DEPENDENT SUBQUERY(相関サブクエリ)は遅い
  • リレーションがbelongsToの場合は発生しない

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

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

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

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

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

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

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

最後に

この記事をまとめます。

  • LaravelのwhereHas()を使うとサブクエリが発生する可能性がある
  • サブクエリ(相関サブクエリ等)は処理が遅い
  • けど、MySQL5.6以降はそこまで(曖昧)遅くない
  • SQL実行計画はexplainを使う
  • サブクエリの発行有無は実行計画のselect_typeを見る

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

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

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

その他の参考記事

この記事が気に入ったら
フォローしてね!

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

ブログを開設するなら「SWELL」が絶対オススメ!

コメント

コメントする

目次