EXPLAINを使おう

これはMynet Advent Calendar18日目の記事です。

今回はWEBシステムのボトルネックになりやすいクエリを最適化する際に使う EXPLAIN の見方について、江澤がお送りします。

EXPLAINとは何か

EXPLAINとは、RDBMSオプティマイザからクエリを実行する際、最適な実行方法を説明する実行計画を出力するコマンド

EXPLAINの実行計画出力の見方(今回はMYSQL)

EXPLAIN実行例

mysql> explain select count(ZipCode) from zipcode;
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows   | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | zipcode | index | NULL          | PRIMARY | 21      | NULL | 128363 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+

id

クエリ内のSELECTの連番。

select_type

項目 内容
SIMPLE UNIONやサブクエリを使用しない単純なSELECT
PRIMARY 外部クエリ(サブクエリを含む場合に、外部クエリと呼ばれる)
UNION UNION内の2つめ以降のSELECT
DEPENDENT UNION UNION内の2つめ以降のSELECT、相関関係
UNION RESULT UNIONの結果
SUBQUERY 相関関係のないサブクエリ
DEPENDENT SUBQUERY 相関関係のあるサブクエリ
DERIVED 派生テーブルSELECT(FROM句内のサブクエリ)
MATERIALIZED 実体化されたサブクエリ
UNCACHEABLE SUBQUERY 結果をキャッシュできず、実行の度に結果が変わる可能性があるサブクエリ
UNCACHEABLE UNION UNCACHEABLE SUBQUERYに属する UNION内の2つめ以降のSELECT

table

アクセスする対象のテーブル。

<unionM,N> と表示される場合はselect_typeが「UNION」で、idが「M」およびidが「N」に由来するテーブルであることを示す。

<subqueryN> と表示される場合はselect_typeが「SUBQUERY」で、idが「N」に由来するテーブルであることを示す。

<derivedN> と表示される場合はselect_typeが「DERIVED」で、idが「N」に由来するテーブルであることを示す。

type(レコードアクセスタイプ)

項目 内容
system 1行しかない。constの特殊なケース
const PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。非常に高速
eq_ref PRIMARY KEYまたはUNIQUE KEY利用時のアクセス。結合時最速
ref PRIARY KEYまたはUNIQUE KEYが非利用時のアクセス。N行ある場合に使用
fulltext FULLTEXTインデックス利用時アクセス。お目にかかることはあまり無い
ref_or_null refにNULL値を含む行の追加検索時のアクセス。IS NULL使用時など
index_merge インデックスマージ最適化
unique_subquery IN句のサブクエリを効率化のためrefに置き換える
index_subquery unique_subqueryの一意でないversion
range インデックスを用いた範囲検索
index インデックスフルスキャン。やむを得ない状況で無ければ最適化したい
ALL フルテーブルスキャン。インデックスが使用されていない。最適化必須

possible_keys

オプティマイザがテーブルのアクセスに利用可能なインデックスの候補として挙げたキー。 NULLの場合は、関連するインデックスがない。

key

オプティマイザによって選択されたキー。

key_len

選択されたキーの長さ。キー長が短い方が高速。

ref

検索条件で、keyと比較されている値やカラムの種類。 定数の場合はconst。 結合時は結合する相手側のテーブルの検索条件として利用されているカラムを表示。

rows

クエリを実行するために調査するレコード数。 InnoDBテーブルの場合、推定値であり、正確ではない。

Extra

Extraは項目が多すぎるので良く見るものを抜粋。

項目 内容
Using filesort 結合時、インデックスを用いてソートがされていない(改善出来るのであれば出ないようにしたい)
Using index インデックスツリーの情報のみを使用している
Using index for group-by MIN()、MAX()またはGROUP BYまたはDISTINCTクエリを使用している際、インデックスが効率的に使われている
Using temporary 実行時、結果を保持する一時テーブルを作成(改善出来るのであれば出ないようにしたい)
Using where 結合時や重複排除時にテンポラリテーブルを作成する必要がある

あとがき

次回があれば実際にテーブルとデータを用意し、サンプルを用いて解説したい←最近join、unionしないDB設計が多いので忘れてきている。復習の為

勘違いなどがあれば御指摘を賜りたく←日本語的にも技術的にも拙い

重複でなんとなくDISTINCTを使用する前にGROUP BYを使おう←これが言いたかった、30秒もかかるクエリなんて心臓に悪いので実行したくない