MySQLのEXPLAINの実行例 | VPSサーバーでWebサイト公開　備忘録　~Linux、MySQLからAJAXまで

MySQLのEXPLAINの実行例と解析方法についてまとめました。

※目次をクリックすると目次の下部にコンテンツが表示されます。

１．プライマリキーの単純検索
２．プライマリキーの範囲検索
３．キー未設定カラムの範囲検索
４．複数のカラムを範囲検索
５．likeを使って前方、後方、部分一致
６．REGEXP句で正規表現を使って検索
７．”Using index”について
８．where句ではインデックスキーに式や関数を指定しない

プライマリキーの単純検索

１）実行例
　

mysql> EXPLAIN SELECT id,name from example where id = "13_88248460" \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: example
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 38
          ref: const
         rows: 1
        Extra: NULL
1 row in set (0.00 sec)

　
２）各項目の説明
　
・id
　クエリのID（テーブルのIDではないので注意）
　
・select_type
　クエリの種類を表す。
　SIMPLE：単純な SELECT (UNION やサブクエリーを使用しない)。
　
・table
　対象のテーブル
　
・type
　レコードアクセスタイプ。どのようにテーブルにアクセスされるかを示す。
　const：テーブルに、一致するレコードが最大で 1 つあり、クエリーの開始時に読み取られる。レコードが 1 つしかないため、このレコードのカラムの値はオプティマイザによって定数と見なされる。const テーブルは、1 回しか読み取られないため、非常に高速。const は PRIMARY KEY/UNIQUE キーを定数と比較する場合に使用される。
　
・possible_keys
　利用可能なキー。
　
・key
　MySQL が実際に使用を決定したキー (インデックス)。
　
・key_len
　選択されたキーの長さ（バイト）
　
・rows
　行数の概算見積もり。
　
・Extra
　オプティマイザヒント。

プライマリキーの範囲検索

１）実行例
　

mysql> EXPLAIN SELECT id FROM example WHERE id BETWEEN "13_000000000" AND "13_999999999" \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: example
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 38
          ref: NULL
         rows: 5737
        Extra: Using where; Using index

　
２）単純検索の結果との相違
　
①typeが”const”から”range”に変りました。
　
※type
　レコードアクセスタイプ。どのようにテーブルにアクセスされるかを示す。
　
●const
　
・一致するレコードが最大で1つあり、クエリーの開始時に読み取られる。
・レコードが1つしかないため、このレコードのカラムの値はオプティマイザによって定数と見なされる。
・テーブルは1回しか読み取られないため、非常に高速。
・PRIMARY KEY/UNIQUEキーを定数と比較する場合に使用される。
　
●range
　
・インデックスを使用して、一定の範囲にあるレコードのみが取り出される。
・keyカラムに使用されるインデックスが示される。
・key_len_には使用される最長のインデックス部分が記載される。
・この型ではrefカラムがNULLになる。
・rangeは、=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、IN()を使用してキーカラムを定数と比較する場合に使用できる。
　
②Extra（MySQLでどのようにクエリーが解決されるかに関する追加情報）がNULLだったのが”Using where; Using index”と記述されています。
　
●Using index
　
・インデックスツリーの情報のみを使用してカラム情報がテーブルから取り出され、実際の行を読み取るその後の検索を実行する必要がないことを示す。
・この方針は、クエリーで単一のインデックスの構成部分であるカラムのみが使用される場合に使用できる。
　
●Using where
　
・次のテーブルとの一致が調べられるレコードまたはクライアントに送信されるレコードの限定にWHERE節が使用されることを示す。
・この情報がなく、Extraの値がUsing whereではなく、テーブルの型がALLまたはindexである場合はクエリーが正常に実行されないことがある(テーブルのすべてのレコードの取得や検査を意図していない場合)。

キー未設定カラムの範囲検索

１）キー未設定カラムを範囲検索した場合の実行例
　
mysql> EXPLAIN SELECT count(*) FROM example WHERE build <= 2010 \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: example
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11474
        Extra: Using where

カラムにキーが設定されていないため、typeがALLになっています。フルテーブルスキャンが実行されています。
　
●キー追加
　
mysql> ALTER TABLE example ADD INDEX (build);
　
２）キー設定後のEXPLAIN実行例（プライマリキー以外の範囲検索）
　
mysql> EXPLAIN SELECT count(*) FROM example WHERE build <= 2010 \G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: example
         type: range
possible_keys: build
          key: build
      key_len: 5
          ref: NULL
         rows: 5737
        Extra: Using where; Using index

typeがrangeに変ってインデックスが使用されています。

複数のカラムを範囲検索

１）実行例
　

mysql> EXPLAIN SELECT count(*) FROM example WHERE build <= 2010 AND size < 15 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: example
         type: range
possible_keys: build,size
          key: size
      key_len: 5
          ref: NULL
         rows: 122
        Extra: Using index condition; Using where
1 row in set (0.03 sec)

mysql> EXPLAIN SELECT count(*) FROM example WHERE build <= 1990 AND size < 30 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: example
         type: range
possible_keys: build,size
          key: build
      key_len: 5
          ref: NULL
         rows: 1842
        Extra: Using index condition; Using where

buildとsizeの二つのカラムにインデックスが指定されていてキーとして使用可能となっています。実際にどちらのキーを使用するかは、指定した検索条件を考慮し、選択してくれるようです。

likeを使って前方、後方、部分一致

１）実行例
　

○前方一致
mysql> EXPLAIN SELECT count(*) FROM b_example1 WHERE address like '東京都千代田区%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b_example1
         type: range
possible_keys: address
          key: address
      key_len: 243
          ref: NULL
         rows: 64
        Extra: Using where; Using index
　
○部分一致
mysql> EXPLAIN SELECT count(*) FROM b_example1 WHERE address like '%中央%' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b_example1
         type: index
possible_keys: NULL
          key: address
      key_len: 243
          ref: NULL
         rows: 11474
        Extra: Using where; Using index
　
○後方一致
mysql> EXPLAIN SELECT count(*) FROM b_example1 WHERE address like '%中央町２丁目' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b_example1
         type: index
possible_keys: NULL
          key: address
      key_len: 243
          ref: NULL
         rows: 11474
        Extra: Using where; Using index

　
①前方一致
　
type: rangeとなっていて、インデックスを使用して、一定の範囲にあるレコードのみが取り出されています。
　
②部分一致、後方一致
　
type: index、possible_keys: NULLとなっていてインデックスが使用されていないことが分かります。
　
※typeのindexは下記意味になります。
　
インデックスツリーのみがスキャンされる点を除いてALL（フルスキャン）と同じである。一般にインデックスファイルはデータファイルより小さいため、通常は ALL より高速である。

REGEXP句で正規表現を使って検索

１）実行例
　

mysql> EXPLAIN SELECT address FROM example WHERE address REGEXP '.*月島(１|1|２|2)丁目' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: example
         type: index
possible_keys: NULL
          key: address
      key_len: 243
          ref: NULL
         rows: 11474
        Extra: Using where; Using index

mysql> EXPLAIN SELECT count(id) FROM example WHERE address REGEXP '^東京都(千代田区|港区).*' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: example
         type: index
possible_keys: NULL
          key: address
      key_len: 243
          ref: NULL
         rows: 11474
        Extra: Using where; Using index

先頭の文字列、後方の文字列の如何に関わらずtype: index、possible_keys: NULLとなっていてインデックスが使用されていないことが分かります。
　
※typeのindexは下記意味になります。
　
インデックスツリーのみがスキャンされる点を除いてALL（フルスキャン）と同じである。一般にインデックスファイルはデータファイルより小さいため、通常は ALL より高速である。

"Using index"について

・インデックスツリーの情報のみを使用してカラム情報がテーブルから取り出され、実際の行を読み取った後に検索を実行する必要がないことを示す。
　
クエリーでインデックスの構成部分であるカラムのみが使用される場合に表示される。
　
例１）Using indexが表示される例
　
where句でインデックスを使って条件を指定し、インデックスで指定したカラムのみ表示。

mysql> explain select id_eki,build from sample where id_eki = '2164020' and build = '2010' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sample
         type: index_merge
possible_keys: build,id_eki
          key: id_eki,build
      key_len: 33,5
          ref: NULL
         rows: 3
        Extra: Using intersect(id_eki,build); Using where; Using index
1 row in set (0.00 sec)

例２）インデックスが使用されていない場合
　
カラムにキー項目を指定していても前方後方一致などでインデックスが使用されない条件を指定するとUsing indexとはならない。

mysql> explain select id_eki,build from sample where id_eki like '%210%' and build = '2010' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sample
         type: ref
possible_keys: build
          key: build
      key_len: 5
          ref: const
         rows: 201
        Extra: Using where
1 row in set (0.00 sec)

例３）すべてのカラムを表示する場合
　
インデックスが使用されていてもインデックス以外のカラムを表示しているのでUsing indexとならない。

mysql> explain select * from sample where id_eki = '2164020' and build = '2010' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sample
         type: index_merge
possible_keys: build,id_eki
          key: id_eki,build
      key_len: 33,5
          ref: NULL
         rows: 3
        Extra: Using intersect(id_eki,build); Using where
1 row in set (0.00 sec)

where句ではインデックスキーに式や関数を指定しない

・MySQLでは、クエリーでカラムが式の一部であったり関数内で使用されていると通常インデックスが使用されない。
　
・下記のような単純な式を使ったクエリーでも問題となってしまうので注意する。
SELECT id FROM member WHERE id + 1 = 5;
　
・関数を使用した例
select id, created from Sample where to_days(current_date) - to_days(created) <= 7;

関連記事の目次

MySQL

インストール、基本設定、文字コード、仕組み

インデックス

状態確認、調査

バックアップ、リストア、メンテナンス

パフォーマンス、チューニング

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル