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が効かない場合はこちら */