SQL文(SELECT文)のチューニング

実行計画の取得

下記のようにSELECT文のまえにEXPLAINをつけると、クエリの実行計画を取得することができます。

mysql> EXPLAIN SELECT * FROM  log;
+----+-------------+----------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | log   | ALL  | NULL          | NULL | NULL    | NULL | 46497 |       |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------+
1 row in set (0.09 sec)

MySQLでは、SQL文はオプティマイザによって解析・最適化されてからストレージエンジンAPIに渡されて処理されます。
実行計画は、具体的にはオプティマイザがSQL文を最適化するときの情報であり、使用されるインデックスやテーブル結合や順序などの情報です。

EXPLAINの見方

EXPLAINによって取得したSELECT文の実行計画の見方は以下のとおりです。

id/select_type

idとselect_typeとセットで考えるといい。

select_typeはクエリの種類を表すものであり、ツリーの構造にそのまま反映される。クエリの種類とはJOIN、サブクエリ、UNIONおよびそれらの組み合わせで、select_typeの内容もその組み合わせから導き出されたもの。

table

アクセス対象となるテーブルの名前。

type

テーブルに対してどのような方法でアクセスするかどうか。
以下、効率がいい順。

漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!!ならびにMySQL 徹底入門 第2版より、大部分を引用

このフィールドの値がALLやindex,rangeの場合は要注意。

possible_keys

オプティマイザが実際に使用可能なインデックスの候補として挙げたキーの一覧。
この項目がNULLの場合は、利用可能なインデックスがないという状態。

key

オプティマイザによって実際に使用すると決められたキー。
どのインデックスも選ばれなかった場合は、NULLとなる。

key_len

使用されるキーの長さ。インデックスの走査はキーが短いほうが高速になる。
keyがNULLの場合はこのフィールドもNULLになる。

ref

テーブルからレコードをSELECTするために、どのカラムや定数がキーと比較されているかを示す。

rows

テーブルから検査する必要があると思われるレコードの数(※ただし、推測値であり、正確な値とは限らない)。

Extra

追加情報。"Using ～"など様々な情報が出力される場合があるが、その大雑把な意味は下表のとおり。

漢(オトコ)のコンピュータ道: MySQLのEXPLAINを徹底解説!!より、大部分を引用

確認が必要なSQL文

EXPLAINの結果が以下のSQL文は最適化する余地があると思われるので、注意してみてください。

• typeがALL,index,rangeとなっていとなるSQL(特にALLの場合はテーブルのフルスキャンが発生する可能性がある)
• possible_keys, key, key_len, ref がすべてNULLのSQL(インデックスがまったく使用されていない)
• Extraが "Using filesort", "Using temporary"となるSQL

スロークエリログに出力されているSQL文も確認が必要です。

• プライマリーキーは必ず定義しデータ型も極力小さいサイズにする。例えば、BIGINTよりもINT UNSIGNEDなどを利用して桁数を極力少なくするなど。
• WHERE句では極力プライマリーキーを用いて条件指定を行うこと。
• ORDER BY に指定するカラムは極力プライマリーキーを設定したカラムにすること。

MySQLによるタフなサイトの作り方より、大部分を引用