【SQL】そのクエリ重くない?実行計画を調べるEXPLAINについてのまとめ

SQL プログラミング

  • Q:色々試行錯誤してやっと動きそうなSQLが書けたんだけど、挙動が重くなりそうで心配。事前に確認できないかな
  • A:書いたSQLの先頭にEXPLAINと追記すると、そのSQLがどんな動きをするか確認することができます。

自分が書いたクエリが重くならないか心配な場合、EXPLAINを文頭に記述することで、実行状況を事前に確認することができます。

例えば、サブクエリを沢山使っていたり、CASE文を重ねて使っていると、それだけで処理が重くなります。ローカル環境で試している時は動いていても、本番環境で試したらDBへのアクセスが重くなって、サーバーの処理が落ちてしまう(Gateway Timeout)ことがあります。

しかし、EXPLAINを活用すれば、データの処理は行わず、実行計画だけを確認してくれるのです。

具体的にどんな記述があるか、調べてみます。

スポンサードサーチ

EXPLAINを実行すると表示される項目

EXPLAIN + 実行しようとしているSQL文を実行すると、例えば以下のような項目が表示されます。

id:実行順序

select_type:クエリの種類。SIMPLEの場合は、サブクエリやUNIONなどを利用していない単純なSELECT文を指します。

table:参照しているテーブル名です。この場合は、hoge,fugaというのがテーブル名になります。

partitions:テーブルのパーティションのことを指します。MySQL5.1以降の機能のようです。データの値を項目ごとに振り分ける場合に現れます。パーティションを参照することで処理が早くなることがあります。

type:結合型 ALLの場合はフルテーブルスキャンとなり、実行が遅くなります。あらかじめインデックスなどを作成しておくと、ALLではない表記になります。refはJOINするテーブルやサブクエリがある場合に見られました。eq_refという表示の場合は、PRIMARY_KEYを用いてのスキャンになるため、より効率が上がるそうです。

possible_keys:利用可能なインデックスを指します

key:MySQLが実際に選択したkeyを指します

key_len:選択されたkeyの長さを指します

ref:インデックスと比較される項目を指します。例えば、hogeテーブルのhogehoge_idという項目をfugaテーブルの検索で使いたい時、hogehoge_idを参照しています。この参照している項目がrefの内容になります。

rows:実際に検索されている行の見積もりです。単純にこの数値が多いほど、検索時間がかかります。数値が減るほど、実行計画が良くなっていることになります。

filtered:例えばwhere以降の条件でフィルタリングされる行の割合です。rows × filtered / 100 が実際にDBで検索される行数になります。例えば、rowsが100でfilteredが3だった場合、検索対象の行数は3行ということになります。(もしかしたら、若干ニュアンスが違うかも知れません)

Extra:追加情報を指します。Using whereはSELECT文にWHEREが付いている場合です。インデックスをDBに付与している場合は、USING Indexという名前がつきます。

今回は簡単な網羅とさせてください。今後、EXPLAINを活用して行った時に、更にわかったことを追記していきたいと思います。

いくつか参考資料を置いておきます。