【MySQL】遅いselect文の原因を調査する【explainの読み方】

ExplainでSQLのボトルネックを調査する

→select文の前に「explain」をつけて実行してみる

explain select * from tableName;

実行結果の読み方

select_type

サブクエリの場合

値	説明
PRIMARY	外部のクエリ
SUBQUERY	相関関係のないサブクエリ
DEPENDENT SUBQUERY	相関関係のあるサブクエリ
UNCACHEABLE SUBQUERY	実行のたびに結果が変わるかもしれないサブクエリ
DERIVERD	from句で使われるサブクエリ

外部クエリ→サブクエリの順序で実行されるが、DERIVERDのときだけはサブクエリ→外部クエリと実行順所が逆になる。

UNIONの場合

値	説明
PRIMARY	unionのときに最初にフェッチされるテーブル
UNION	２番目以降にフェッチされるテーブル
UNION RESULT	UNIONの実行結果
DEPENDENT UNION	DEPENDENT SUBQUERYがUNIONになっている場合
UNCACHEABLE UNION	UNCACHEABLE SUBQUERYがUNIONになっている場合

UNOINは先頭から順に処理されていくので、処理順序がわかりやすい・・・はず。

type

対象のテーブルに対してのアクセス方法。
致命的なものは見れば一発でわかる・・・らしい。

値	説明
ALL	フルスキャンなので一番重い。改善必須
index	フルインデックススキャン。インデックスをALL検索するのでこれまた重い。改善必須
ref	インデックスを使った検索
range	インデックスを使った範囲検索。
eq_ref	joinのときにPKやユニークキーを使う場合。joinのときはこの値になれば最速。
const	join以外でPKやユニークキーを使う場合。最速。

key

テーブルにアクセスするために使ったindexを示す。
ちなみに、possible_keysは「使える候補」で、実際につかったかどうかはkeyの値を見る。

key_len

選択されたキーの長さ。
インデックスのキー長が短いほうが高速になるので、迷ったら長さの短いほうにインデックスをつけるといい。

rows

selectの取得件数の見積もり。
ざっくりとしたものなので、where句次第ではもっと少ない件数が返ってくることもある。

extra

ほかに使用している条件などがあれば、ここに出力される。

チューニング方法の例

type=null かつ key=null の場合

→インデックスが張られてない＆テーブルのフルスキャンが行われている。
where条件で使うカラムにインデックスを張る。
もしくは、インデックスが張られたカラムをwhere条件の中に追加する。

extra=Using filesort もしくは extra=temporary

→order byに指定しているカラムにインデックスを張る。
Javaだと、Listで取得してCollection.sort()もできるので、Java側にソート処理を入れるものアリかもしれない。
「extra=temporary」はjoinしているときに出現するので、joinしているテーブルのどこかにインデックスを張ればよい。

インデックスを張ってあるはずなのにtype=all

→複合インデックスが使われてない。
複合インデックスが張ってあるカラムを、すべてwhere条件に使用する。
インデックスと同じ順序でwhere条件を組み立てること！

possible_keysに出力があるのにkey=nullで使われてない

→MySQLの仕様が原因かもしれないので、SQLそのものを作り変える。
例）インデックスが張ってあるカラムをwhere条件に使用しているのに、テーブル検索が実施されている
MySQLが「インデックス検索よりもテーブル検索が早い」と判断してしまうことがあるようです。
SQLのつくりを変えるなどの、大きな変更が必要になる。
（仕様や設計を見直すことも視野に入れた方がよい）

その他やってみるといいかもしれない方法

joinのときにインデックスを使うような記述を追加する

SELECT * FROM tableA aa LEFT JOIN tableB bb USE INDEX(index名)
SELECT * FROM tableA aa LEFT JOIN tableB bb FORCE INDEX(index名) /* USEが効かない場合はこちら */