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)