WEBサービス創造記

WEBサービスを作ったり保守したりしてる人のメモブログです。

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

      2012/12/11

実行計画の取得

下記のように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

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

system テーブルに1レコードしかない状態
const マッチするレコードが1件しかない状態。PRIMARY KEYまたはUNIQUEインデックスを利用して検索される
eq_ref JOINにおいてPRIMARY KEYまたはUNIQUEインデックスを利用して1つのレコードを検索している状態。
理想的なJOINの形式
range インデックスを用いた範囲検索
index すべてのレコードのインデックスをスキャンする必要がある状態(フルインデックススキャン)
ALL すべてのレコードを走査する必要がある状態(フルテーブルスキャン)

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

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

possible_keys

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

key

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

key_len

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

ref

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

rows

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

Extra

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

Using where WHERE句に検索条件が指定されており、なおかつインデックスを見ただけではWHERE句の条件を全て適用することが出来ない場合に表示される
Using index クエリがインデックスだけを用いて解決できることを示す。Covering Indexを利用している場合などに表示される。
Using filesort メモリ上のバッファでソート処理ができず、テンポラリファイルが作成されてソート処理がされている場合に出力される。
SQL文にORDER BY句を指定している場合によく表示される。
Using temporary クエリの実行にテンポラリテーブルが必要な場合に表示される。
SQL文がJOINかつORDER BY句を利用している場合やGROUP BY句とORDER BY句で指定しているカラムが異なる場合などで発生することがある。
Using index for group-by MIN()/MAX()がGROUP BY句と併用されているとき、クエリがインデックスだけを用いて解決できることを示す。
Range checked for each record (index map: N) JOINにおいてrangeまたはindex_mergeが利用される場合に表示される。
Not exists LEFT JOINにおいて、左側のテーブルからフェッチされた行にマッチする行が右側のテーブルに存在しない場合、右側のテーブルはNULLとなるが、右側のテーブルがNOT NULLとして定義されたフィールドでJOINされている場合にはマッチしない行を探せば良い・・・ということを示す。

漢(オトコ)のコンピュータ道: 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によるタフなサイトの作り方より、大部分を引用

 - MySQL , , , ,