- July 3, 2007 5:09 PM
- EXPLAIN
テーブルは、EXPLAIN
によって表示される順序で読み取られる。出力結果の内容は以下の通
| 項目 | 意味 |
| id | クエリ内部において SELECT が使用する連続した ID |
| select_type | SELECT の種類 (サブクエリ、UNION など) |
| type | 使用する結合型 |
| key | 使用するインデックス |
| key_len | 使用したキーの長さ |
| ref | 検索に使用するフィールド |
| rows | クエリの各ステップを実行するために確認が必要な行の数
rows の積は、クエリを実行するために確認する必要がある行の総数 |
| Extra | テーブルの結合方法に関する追加情報 |
- EXTRA
- EXTRAにあるとうれしい値
- Using index: 最適。インデックスツリーの探索で処理を完了できる。
- Using where: 適切。これが欠けている場合は、テーブルの全ての行が参照される。
- Distinct: DISTINCTを指定した場合？前のテーブルの組み合わせごとに1行が特定される場合。
- Not exists: LEFT JOIN利用時に一部の余分な行の探索を省略できた場合？
- 例：t2のidにNOT NULLが設定された時の、以下のSQL文SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.idWHERE t2.id IS NULL;
- EXTRAにあるとうれしくない値
- Using filesort: インデックスを作成していないカラムでソートしている場合などに発生。
- Using temporary: 一時テーブルが必要。ORDER BYとGROUP BYで指定しているカラムが異なる場合など。
- Range checked for each record: JOINに使用しているインデックスのサイズが違う場合など。
- TYPE
| TYPE | 意味 |
| system | テーブルがMyISAMで、行を一つしか持たない場合 |
| const | テーブルに、一致する行が一行のみの場合 |
| eq_ref | 前のテーブルの組み合わせごとに一行が、このテーブルから読み取ら |
| ref / ref_or_null | 前のテーブルの組み合わせごとに複数行が、このテーブルから読み取 |
| unique_subquery | サブクエリの結果が一意のインデックスでカバーされる場合 |
| index_subquery | サブクエリの結果が、一意で無いインデックスを利用している場合 |
| range | 指定した範囲内の行だけ読み取る場合 |
| index | インデックスツリー全体がスキャンされる場合 |
| all | テーブル全体がスキャンされる場合 |
※望ましい順序
- system
テーブルが行を一つしか持たない場合
*************************** 1. row *************************** Table: t3 Create Table: CREATE TABLE `t3` ( `a` int(11) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> select * from t3; +---+ | a | +---+ | 0 | +---+ 1 row
in set (0.00 sec) mysql> explain select * from t3\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t3 type: system possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 Extra: 1 row in set (0.00 sec) |
但しInnoDBでは行数の情報を保持していないので
*************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `a` int(11) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> select * from t2; +---+ | a | +---+ | 0 | +---+ 1 row
in set (0.00 sec) mysql> explain select * from t2 where a=0\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index 1 row in set (0.00 sec) |
- const
テーブルに、一致する行が一行のみの場合
一行のみであるため、オプティマイザは定数と見なす
*************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `a` int(11) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> select * from t2; +---+ | a | +---+ | 0 | | 1 | +---+ 2 rows
in set (0.00 sec) mysql> explain select * from t2 where a=0\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t2 type: const possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 Extra: Using index 1 row
in set (0.00 sec) |
- eq_ref
前のテーブルの組み合わせごとに一行が、このテーブルから読み取ら
| t4 | |
| a (PK) | b |
| 1 | 0 |
| 2 | 0 |
| 3 | 1 |
| t2 |
| a (PK) |
| 0 |
| 1 |
| 2 |
*************************** 1. row *************************** Table: t4 Create Table: CREATE TABLE `t4` ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `a` int(11) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> select * from t4; +---+------+ | a | b | +---+------+ | 1 | 0 | | 2 | 0 | | 3 | 1 | +---+------+ 3 rows
in set (0.00 sec) mysql> select * from t2; +---+ | a | +---+ | 0 | | 1 | | 2 | +---+ 3 rows
in set (0.00 sec) mysql> explain select * from t4,t2 where t4.b=t2.a\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t4 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t2 type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: ona.t4.b rows: 1 Extra: Using index 2 rows
in set (0.00 sec) |
- ref / ref_or_null
前のテーブルの組み合わせごとに複数行が、このテーブルから読み取
NULL値を含む場合がref_or_null
ref
*************************** 1. row *************************** Table: t4 Create Table: CREATE TABLE `t4` ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `a` int(11) default NULL, KEY `idx` (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> select * from t4; +---+------+ | a | b | +---+------+ | 1 | 0 | | 2 | 0 | | 3 | 1 | +---+------+ 3 rows
in set (0.00 sec) mysql> select * from t1; +------+ | a | +------+ | 0 | | 0 | | 1 | +------+ 3 rows
in set (0.00 sec) mysql> explain select * from t4,t1 where t4.b=t1.a\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t4 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 3 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: t1 type: ref possible_keys: idx key: idx key_len: 5 ref: ona.t4.b rows: 1 Extra: Using where; Using index 2 rows in set (0.00 sec) |
- unique_subquery
サブクエリの結果が一意のインデックスでカバーされる場合
*************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `a` int(11) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> show create table t5\G *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL, PRIMARY KEY (`a`), KEY `idx` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> select * from t2; +---+ | a | +---+ | 0 | | 1 | | 2 | +---+ 3 rows
in set (0.00 sec) mysql> select * from t5; +---+------+ | a | b | +---+------+ | 6 | NULL | | 1 | 0 | | 2 | 0 | | 3 | 1 | | 4 | 2 | | 5 | 2 | +---+------+ 6 rows
in set (0.00 sec) mysql> explain select * from t2 where a in(select a from t5 where b=t2.a)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t2 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 3 Extra: Using where; Using index *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t5 type: unique_subquery possible_keys: PRIMARY,idx key: PRIMARY key_len: 4 ref: func rows: 1 Extra: Using index; Using where 2 rows in set (0.00 sec) |
- index_subquery
サブクエリの結果が、一意で無いインデックスを利用している場合
*************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `a` int(11) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> show create table t5\G *************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL, PRIMARY KEY (`a`), KEY `idx` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> select * from t2; +---+ | a | +---+ | 0 | | 1 | | 2 | +---+ 3 rows
in set (0.00 sec) mysql> select * from t5; +---+------+ | a | b | +---+------+ | 6 | NULL | | 1 | 0 | | 2 | 0 | | 3 | 1 | | 4 | 2 | | 5 | 2 | +---+------+ 6 rows
in set (0.00 sec) mysql> explain select * from t2 where a in(select b from t5 where b<4)\G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: t2 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 3 Extra: Using where; Using index *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: t5 type: index_subquery possible_keys: idx key: idx key_len: 5 ref: func rows: 1 Extra: Using index; Using where 2 rows in set (0.00 sec) |
- range
指定した範囲内の行だけ読み取る場合
*************************** 1. row *************************** Table: t5 Create Table: CREATE TABLE `t5` ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL, PRIMARY KEY (`a`), KEY `idx` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> select * from t5; +---+------+ | a | b | +---+------+ | 6 | NULL | | 1 | 0 | | 2 | 0 | | 3 | 1 | | 4 | 2 | | 5 | 2 | +---+------+ 6 rows
in set (0.00 sec) mysql> explain select * from t5 where a between 2 and 5\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t5 type: range possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 3 Extra: Using where 1 row in set (0.00 sec) |
- index
インデックスツリー全体がスキャンされる場合
*************************** 1. row *************************** Table: t4 Create Table: CREATE TABLE `t4` ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> select * from t4; +---+------+ | a | b | +---+------+ | 1 | 0 | | 2 | 0 | | 3 | 1 | | 4 | NULL | +---+------+ 4 rows
in set (0.01 sec) mysql> explain select a from t4\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t4 type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 4 Extra: Using index 1 row in set (0.00 sec) |
- all
テーブル全体がスキャンされる場合
*************************** 1. row *************************** Table: t4 Create Table: CREATE TABLE `t4` ( `a` int(11) NOT NULL auto_increment, `b` int(11) default NULL, PRIMARY KEY (`a`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row
in set (0.00 sec) mysql> select * from t4; +---+------+ | a | b | +---+------+ | 1 | 0 | | 2 | 0 | | 3 | 1 | | 4 | NULL | +---+------+ 4 rows
in set (0.01 sec) mysql> explain select b from t4\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: t4 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: 1 row in set (0.00 sec) |