【Laravel×MySQL】SQLの実行計画を見てみる〜クエリチューニングはじめの一歩〜

大量または複雑なクエリを発行している結果、データの読み込みが遅い場合、
クエリを見直す必要と思いますが、Laravalではどう確認していくかについてまとめました。
なお、DBはMySQLの想定で説明します。

Laravelではありますが、後半部分はMySQLの仕様の説明部分となるため、Railsなど他のフレームワークや他のDBであっても概ね参考になると思います。

クエリの速度を確認できる方法、クエリの実行計画を確認する方法を順に説明していきます。

Laravelでクエリの速度を確認
1. getQueryLogを使う方法
2. ddd()を使う方法
クエリの実行計画を確認
1. EXPLAIN文を使う
2. SQLの実行計画をチェック
まとめ

Laravelでクエリの速度を確認

2種類あるので説明します。
例えとして、UserNotificationテーブルから特定のユーザー分(user_idカラム)のデータを新しい分から2つ取得するクエリを発行してみるとします。

getQueryLogを使う方法

クエリログを有効化させるenableQueryLogと、クエリを取得できるgetQueryLogを使います。
DBファサードをuseするのを忘れないでください。

use Illuminate\Support\Facades\DB;

\DB::enableQueryLog();
$notifications = UserNotification::where("user_id",$user->id)->orderBy("created_at","DESC")->limit(2)->get();
dd(\DB::getQueryLog());

dd()で止めて、クエリと実行時間を確認することができます。

ddd()を使う方法

Lravel6以降で有効ですが、ddd()を使ってみる方法もあります。

$notifications = UserNotification::where("user_id",$user->id)->orderBy("created_at","DESC")->limit(2)->get();
ddd($notifications);

こちらでもブラウザのDebugタブからクエリを確認できます。

ただし、クエリの時間は方法別に違いがあるなと思いました。
純粋なクエリの時間は、getQueryLogを使う方が信憑性あるのかな・・・と思っています。（もし詳しい方がいらしたら指摘してもらえれば助かります）

このように時間を測定してみましょう。
後で改善した時にbefore/afterで比較ができると思います。

クエリの実行計画を確認

EXPLAIN文を使う

次に、クエリの実行計画を確認します。実行計画を確認することで、インデックスがきちんと使われているかなどの情報を調査でき、クエリチューニングへのヒントを得ることができます。

MySQLではEXPLAINを使うことで、確認ができます。

まずはやってみましょう。
Laravel8以降であれば、予め用意がされており、explain()を使うことで確認することができます。（便利！）

$notifications = UserNotification::where("user_id",$user->id)->orderBy("created_at","DESC")->limit(2)->explain();//explainメソッド使う
dd($notifications);

こうすると、実行計画がブラウザで表示されます

もしくはMySQLの文法として、SELECT文の前にEXPLAINを使うことで確認することができます。
クライアントツールからSQL文を書いてみます。

下に実行結果が表示されていますね。

SQLの実行計画をチェック

実行計画を表示させて、ではどこを確認するか…となると思います。
いろんな記事を読んだのですが、全てが重要というわけでなく次の項目を確認します。
「type」「possible_keys」「key」「 rows」「Extra」
それぞれ解説します。

type・・・レコードへのアクセス方法。refであればインデックスを使っていることになるのでOKです。constも問題なし。
ALLとなっていたらフルテーブルスキャンとなっており、indexではフルインデックススキャンとなり、改善余地ありです。

possible_keys・・・使われる可能性のあるインデックスのリストです。ここから効率の良いものがkeyに選ばれるようになります。

key・・・実際に使用されているインデックスがここに表示されます。

rows・・・テーブルから取得される行の見積もりで、ここが多いと時間がかかる原因となります。インデックスを貼ると減るはずです。

Extra・・・Using indexの場合、インデックスだけを用いて解決している場合で望ましい状況。Using filesortだとインデックスを使いソートをしなくてすむ方法を考える。

これらの情報をヒントに、SQLを改善させていくようにしましょう。