explainした時の重要ポイント（実例つき）

MySQLにて、SQLをexplainした時に表示される項目の大事なポイントをメモ。

ちなみに、本ページに表示されているサンプルSQL用テーブル作成DDLはこちら。
（必須ではないので閉じておく）

CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `row1` int(11) NOT NULL,
  `row2` int(11) NOT NULL,
  `row3` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `inx_row1_row2` (`row1`,`row2`),
  KEY `idx_row3` (`row3`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `test` (`row1`, `row2`, `row3`)
VALUES
	(1, 2, 3),
	(2, 3, 4),
	(3, 4, 5);

ポイントは、サンプルテーブルのセカンダリインデックスは2つあるということ。
1つ目は「row1列、row2列の複合インデックス」
2つ目は「row3列単体のインデックス」

type列

type列に表示された時に注意するべき値

表示される値のうち、以下2つは即修正の必要あり。

ALL

フルテーブルスキャンされているので最高に遅い。
運用してデータ数が増えたら激重になるので、インデックスを作成する必要がある。

mysql> explain select * from test where row2 = 3 \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.00 sec)

例えば、この例だと、row2列から始まっているインデックスが存在していないのでインデックスが使われていない。
row1列+row2列の複合インデックスはあるが、whereにrow1列が使われていないため有効にならず、フルテーブルスキャンされている。

index

一見するとインデックスが使われているような感じがする値だが、実はフルインデックススキャンされているということ。
インデックス全体をスキャンしているので遅い。
インデックスの見直しが必要。

key_len列

インデックスで選択されたキーの長さ（バイト数）が表示される。
長さが想定した値かチェック。

mysql> explain select * from test where row1 > 3 and row2 = 2 \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: range
possible_keys: inx_row1_row2
          key: inx_row1_row2
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

例えば、この例だと、row1列+row2列（どちらもintでNOT NULLなので、それぞれ4バイト）の複合インデックスが使われているので、key_lenが8になるのが正解だが、4になっている場合はrow1列しかインデックスが使用されていないということなので遅くなる可能性あり。

テーブル設計の見直しが必要。
データ型ごとのバイト数はこちらで確認。
http://dev.mysql.com/doc/refman/5.6/en/storage-requirements.html

rows列

検索を行うにあたり、読み取る必要がある推定レコード数が表示される。
値が大きいと、多くのレコードを走査する必要があるということなので、インデックスが使用されているとしても遅くなる。

Extra列

Extra列に表示された時に注意するべき値

表示される値のうち、以下2つは要注意。

Using filesort

ORDER BYに記述されたソート順が、インデックスだけでは満たせず、filesortでソートを行っている場合に表示される。
インデックスはソートされた状態であるため、インデックスが使われるように見直す必要あり。

mysql> explain select * from test where row1 = 1 and row2 = 2 order by row3 \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ref
possible_keys: inx_row1_row2
          key: inx_row1_row2
      key_len: 8
          ref: const,const
         rows: 1
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

例えば、この例だと、row1列+row2列の複合インデックスが使われている。
その2列がorder byで指定されているのであれば、インデックス上でソートされているので全く問題ない。
だが、order byでrow3列が指定されているので、順番が保証されておらず、filesortが必要となっている。

Using temporary

DISTINCTによる重複の排除を行う場合など、クエリの実行結果にテンポラリテーブルが必要な場合に表示される。

mysql> explain select distinct row3 from test where row1 = 1 \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ref
possible_keys: inx_row1_row2,idx_row3
          key: inx_row1_row2
      key_len: 4
          ref: const
         rows: 1
        Extra: Using where; Using temporary
1 row in set (0.00 sec)

Extra列に表示されているとステキな値

逆に、この値が表示されていれば最高。

Using index

SQLがCovering indexの場合に表示される。この状態だとクラスタインデックス経由でデータを取得する必要がないので高速。

mysql> explain select row1, row2 from test where row1 = 3 and row2 = 2 \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ref
possible_keys: inx_row1_row2
          key: inx_row1_row2
      key_len: 8
          ref: const,const
         rows: 1
        Extra: Using index
1 row in set (0.00 sec)

例えば、この例だと、row1列+row2列の複合インデックスが使われている。
select句での指定列もrow1列とrow2列のみなので、複合インデックスのみで検索＆データ取得が完了している。
もし、select句にrow3列もある場合は、複合インデックスのみではrow3列が取得できないので、通常通りクラスタインデックス経由でデータを取得する必要があるため、Using indexは消えることになる。
「SELECT *」が遅いといわれるのはこれが一因。

補足Tips

コマンドラインで実行したexplainは横に長いので、見にくい場合がある。
↓特にこのようにHTMLに貼る時。

mysql> explain select * from test where row1 = 1;
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | ref  | inx_row1_row2 | inx_row1_row2 | 4       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------+
1 row in set (0.00 sec)

その場合は、後ろに \G を付けると縦に表示されて見やすくなる。

mysql> explain select * from test where row1 = 1 \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
         type: ref
possible_keys: inx_row1_row2
          key: inx_row1_row2
      key_len: 4
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)