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秒もかかるクエリなんて心臓に悪いので実行したくない