Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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

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

explain select * from tableName;

実行結果の読み方

select_type

サブクエリの場合

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

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

UNIONの場合

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

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

type

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

説明
ALL フルスキャンなので一番重い。改善必須:warning:
index フルインデックススキャン。インデックスをALL検索するのでこれまた重い。改善必須:warning:
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が効かない場合はこちら */
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away