【MySQL】MySQLの実行計画について【EXPLAIN】

#DB
writtdden by ま。

こんにちは、ま。です

MySQLに限った話ではないのですが、SQLを実行する際に気を付けるべきことの一つ、実行計画の確認について今回は記事にしてみようと思います。

実行計画の確認の仕方【EXPLAIN】

今回利用するSQLはこちらです

EXPLAIN SELECT ......

実行した結果はこんな感じで表示されます。(MySQL Workbench利用の画像)

次項で内容について説明していきます。

EXPLAIN実行結果のカラム

ここからは上記で実行した結果のカラムの内容について説明していきます。

項目名 内容
id 識別子。サブクエリとかがあると2行分以上出てくる。
select_type SELECTがどんな形式か。いろいろあるので公式見てほしいけど、これ覚えといたほうがいいものだけ挙げます。
SIMPL:サブクエリとか使わないシンプルなもの
PRIMARY:もっとも外側の SELECT。最終的に実行結果になるやつ。
SUBQUERY:サブクエリ。
table 参照しているテーブル。
partitions 一致するパーティション。説明すると長いけど要するに同じテーブルだけど別のファイルにデータを残すような設定したときに対象となったファイル。
type 結合型(JOIN方式)。これも公式見てほしい。
const:一致するレコード(結果)が最大で 1 つ。Primary指定してるときとか。
ref:前のテーブルの結果から絞り込まれている。
ALL:フルスキャン。基本的にはNG。
possible_keys 選択可能なインデックス。大事。
key 実際に選択されたインデックス。possible_keysの中でどれが使われたか。
kry_len 選択されたキーの長さ。ざっくりいうとバイト数。小さい方が検索が早いらしい。
ref インデックスと比較されるカラム。定数の場合はconst、JOINしている場合は相手側のテーブルで検索条件で利用されるカラムが表示。
rows 調査される行の見積もり。要はテーブルの行数。
filtered 何%業を読み込んだか。条件次第で減る。
Extra 追加情報。インデックス使ってたらUsing indexとか出る。

参考(公式ドキュメント):8.8.2 EXPLAIN 出力フォーマット

何を見るのか

ここからは、これで何を見るのかについてです。

いろいろ情報として出てるし、他にもわかることはあるのですが、
EXPLAINを行うことで重要なのは、

・利用できるインデックスあるか。またインデックスを利用したか。

・JOIN時にフルスキャンしていないか。

がわかることです。

インデックスの詳しい説明と作り方はまた別途ブログ化したいのですが、
ざっくり説明すると、よく使う検索対象のカラムは索引にして調べやすくしようというものです。
索引を使うことで、通常よりも高速で検索が可能になります。

そして、JOIN時のフルスキャンも基本的には不適切です。
JOINをするようにテーブルを分ける理由はいろいろありますが、主なというかほとんどの場合は基準のテーブルに対し、複数のレコードを作成したい場合です。そのため必然的にレコード数が多くなってきます。
ただでさえレコード数が多いのに、結合というSQL的には重い作業をしたうえで、テーブル全部確認するのはあんまりよくないよね。っていう考え方ですね。

それを回避するためにインデックスを貼ることでスキャンするスピードや、そもそもスキャンしなくていい行を作ろうといったところが基本の考え方になります。

話がインデックスにそれてしまいましたが、インデックスを作ってそれが本当に適応できてるのかを判定するのがEXPLAINとなるわけです!

まとめ

実行計画の見方は

EXPLAIN SELECT ......

見るべきところは

・利用できるインデックスあるか。またインデックスを利用したか。

・JOIN時にフルスキャンしていないか。

ここを抑えればこの記事では、大丈夫かと思います。

単純に欲しい結果が得られるSQLを書けるのは基本として、
一歩踏み出したところで、JOINやサブクエリを利用したときにコンパクトに書けるのかも大事です。
さらに踏み込んで、実行計画を気にして高速にするところまでできたらかなり上級者だと思います!

大規模システム作るときなど、いやでも気にする必要がある場合もありますけどね(笑)

文中でも記載しましたが、インデックスの貼り方を記事にしたいと思います!

MySQL関連記事

[SQL]別名を付けた(AS句)カラムをWHERE句で使えなかった話。(解決方法記載あり。)

【MySQL】SQLの基本構文と稀に使うサンプル ~重複削除・Indexなど~ インデックスの貼り方だけは書いてあります(笑)

【MySQL】WorkbenchでUPDATEが実行されないときの対応備忘録

Favorite