MySQLのExplainを確認する
テーブル設計でこんなものですが〜みたいな出し方やると、割と取り返しつかなくてあれなので、 意識が入らないくらい習慣に叩きこめば問題ないですね。
実践SQL入門を買ったので読み進めようと思ったら、
本書では以降、実行計画の読みやすいPostgreSQLとOracleの実行計画をサンプルとして使います。
と書いてあって、MySQLの実行計画無いの、というか、MySQLの実行計画読み難いの？ となったので、 MySQLの実行計画の読み方について調べてから読み進める事にしました。
本はまだまだ読み終わっていないですが、一旦投下。
内容
ためしにMySQLでExplain
試しに、MySQLのExplainを実行してみるために、実践SQLに書いてあるように数十件の店舗情報が入ったテーブル(shops)を用意した上で、以下のSQLを実行してみます。
mysql> EXPLAIN SELECT * from shops; +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ | 1 | SIMPLE | shops | ALL | NULL | NULL | NULL | NULL | 60 | NULL | +----+-------------+-------+------+---------------+------+---------+------+------+-------+ 1 row in set (0.00 sec)
この時点ではまだ普通のような気がしますが、とりあえず、一番基本的な項目がどこに当たるか、確認します。
参考は、
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html https://dev.mysql.com/doc/refman/5.6/ja/explain.html
です。
操作対象のオブジェクト、操作の対象となるレコード数
操作対象のオブジェクトはtableの列に入れられているようです。 また、操作対象となるレコード数はrowsに入っているようです、これを見た分には。
オブジェクトに対する操作の種類
試しに簡単なものでいくつか叩いてみます。
mysql> EXPLAIN INSERT shops (name, rating, area, created_at, updated_at) VALUES ('xxx', 2, 'yyy', NOW(), NOW()); +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used | +----+-------------+-------+------+---------------+------+---------+------+------+----------------+ 1 row in set (0.00 sec) mysql> EXPLAIN UPDATE shops SET rating = 5; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+ | 1 | SIMPLE | shops | index | NULL | PRIMARY | 4 | NULL | 60 | NULL | +----+-------------+-------+-------+---------------+---------+---------+------+------+-------+ 1 row in set (0.00 sec) mysql> EXPLAIN DELETE FROM shops; +----+-------------+-------+------+---------------+------+---------+------+------+-------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | 60 | Deleting all rows | +----+-------------+-------+------+---------------+------+---------+------+------+-------------------+ 1 row in set (0.00 sec) mysql> EXPLAIN DELETE FROM shops WHERE id = 1; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | shops | range | PRIMARY | PRIMARY | 4 | const | 1 | Using where | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec)
オブジェクトに対する操作の種類、レコード数について誤解していたようでした。
Explainで分かるオブジェクトの操作は基本的に、いまあるオブジェクトをどのように見て、どのように操作対象のオブジェクトを探すか、ということだそうで、 削除処理とか更新処理と言ったことは基本的に含まれていないみたいです。
なので、オブジェクトの操作ではどのようにセレクトをしているか、みたいなことを見る必要があります。
MySQLのSELECTは少し特徴的らしいので、ここで少し詳しく調べてみます。
MySQLのSELECT文の見方の基本
基本的には、まずtypeの行を見て、possible_keysとkeyの列を比べて想定通りにkeyが選択されているかを確認し、typeがindex/ALLであったり、possible_keysとkeyが予想外の列になっていたら、クエリを調整していく流れになる気がします(自分の中で)。
MySQLのSELECTがどのように行われるかを見るために各列を簡単に調べてみます。
id
MySQLのSELECTはツリー構造を作ってから行われる...みたいなことが書いてありましたが、まだよく分かっていません(汗)。
SELECTを実行するときの、順序に関わるみたいです。
select_type
サブクエリが絡むときと、UNIONが絡むときに関係あるみたいです。
サブクエリが絡むとき
相関関係がある(SUBQUERY/DEPENDENT SUBQUERY)か、外部クエリ(サブクエリを含むクエリのこと。サブクエリは内部クエリと呼ばれる事がある)なのか(PRIMARY)、実行するたびに結果が変わるものなのか(キャッシュが効き難い?)(UNCACHEABLE SUBQUERY)、FROM句で用いられているのか(DERIVED)。
UNIONが絡むとき
UNIONされるテーブルの中で一番最初にフェッチされる(PRIMARY)か、2番目以降にフェッチされる(UNION)か。
UNIONの結果なのか(UNION RESULT)、各種サブクエリの結果がUNIONになっている(DEPENDENT UNION, UNCACHEABLE UNION)か。
type
レコードアクセスタイプともよばれるそうです。
たぶん、SELECTでindex使われてるかな、という時は、ここを最初に見ればいいと思います(今のところ、自分はここを見てる...)。
同じ行のtable列の入ってるtableのレコードに対して、どのようにアクセスするかを示しています。
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html から、そのまま引用。
| 意味 | |
|---|---|
| const | PRIMARY KEYまたはUNIQUEインデックスのルックアップによるアクセス。最速。 |
| eq_ref | JOINにおいてPRIARY KEYまたはUNIQUE KEYが利用される時のアクセスタイプ。constと似ているがJOINで用いられるところが違う。 |
| ref | ユニーク（PRIMARY or UNIQUE）でないインデックスを使って等価検索（WHERE key = value）を行った時に使われるアクセスタイプ。 |
| range | インデックスを用いた範囲検索。 |
| index | フルインデックススキャン。インデックス全体をスキャンする必要があるのでとても遅い。 |
| ALL | フルテーブルスキャン。インデックスがまったく利用されていないことを示す。OLTP系の処理では改善必須。 |
indexまたはALLを見かけたらすかさずクエリをチューニングすること。
possible_keys
オプティマイザが、テーブルのアクセスに利用可能なインデックスの候補として挙げたキーの一覧。(こちらから引用)
key
key_len
選択されたキーの長さ。インデックスの走査は、キー長が短い方が高速である。インデックスをつけるカラムを選ぶ時にはそのことを念頭に置いて欲しい。 (こちらから引用)
検索条件で、keyと比較されている値やカラムの種類。定数が指定されている場合はconstと表示される。JOINが実行されている時には、結合する相手側のテーブルで検索条件として利用されているカラムが表示される(こちらから引用).
rows
テーブルからフェッチされる行数の見積もり。あくまで目安であって、正確ではありません。
DERIVEDテーブル(FROM句でサブクエリで作っているテーブル)に関しては、EXPLAINするときに、見積もりのために実際に実行しているので割と正確ですが、その分EXPLAINも時間がかかります。
JOINの場合は、WHEREによる絞り込みがなければ、JOINする全てのテーブルのrowsフィールドの積として考えられます。
ユニークインデックスや主キーを用いた場合は、前のテーブルの行に対して、１つの行がJOINされますが、そうでない場合は、１つの行に対して複数の行がJOINされていき、結合結果のテーブルが巨大化していくことになるみたいです。
Explain可能な命令について
MySQLは5.6.3より前のバージョンだとSELECT文しかExplainできないそうです。
ただ、INSERTにEXPLAINかけても、EXPLAINはINSERT前のSELECTのSQLに対して動いている気がするので、 INSERT前のWHERE句の部分だけ抜き出してSELECTしたら実質同じなような気もしました。
