EXPLAINを使ってクエリ解析

MySQLのクエリ解析をするまでの手順備忘録

EXPLAINを使ったクエリ解析の手順を浅くサクッと解説します。

EXPLAINとは

公式では以下のように説明されています。
にないｒま

クエリー実行計画 (つまり、MySQL がクエリーをどのように実行するかの説明) を取得するために使用されます。

要はEXPLAINを使うことでクエリがどれくらい効率的かを確認することができるということです。

EXPLAINによる解析

前準備

以下のような人を一覧管理するuserテーブルがあるとします。
カラムはidとnameの2つだけで超シンプル。
また、テスト用データとしてこのテーブルに3万件ほどデータを入れている状態となります。

mysql> show columns from user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(10) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+

このテーブルのインデックスを見てみるとプライマリーキーであるidが追加されていることが分かります。

mysql> show index from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user  |          0 | PRIMARY  |            1 | id          | A         |       10051 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

この状況でインデックスがはられていないカラムnameに対してSELECT文を実行してみます。
クエリ自体は取得したレコードを降順にして上位100件を出力する単純なものとなります。

mysql> SELECT * FROM user ORDER BY name DESC LIMIT 100;
+------+----------+
| id   | name     |
+------+----------+
| 9999 | Test9999 |
| 9998 | Test9998 |
| 9997 | Test9997 |
以下省略

まぁ値は取得できしたね。

クエリ解析

ただ上記クエリが効率的にどうなのかはこれだけでは分かりません。
そこで出てくるのがEXPLAINになります。
先ほどのSELECT文にEXPLAINを付けて実行してみましょう。

mysql> EXPLAIN SELECT * FROM user ORDER BY name DESC LIMIT 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 29694 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

また色々と出力されました。
各カラムがどういったことを表しているのかはこちらの公式を参照してください。

ここでは例としてrowsに着目します。ここにはテーブルから取得される行数の見積もりが表示されます。

今回のクエリは100件のデータを取得できれば良いのに対し、実際には3万件近くのデータが取得されていることが分かります。
これでは非効率と言わざるを得ません。

では、次にuserテーブルのnameカラムに対してインデックスを作りましょう。

mysql> ALTER TABLE user ADD INDEX name_index (name);
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from user;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| user  |          0 | PRIMARY    |            1 | id          | A         |       10051 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| user  |          1 | name_index |            1 | name        | A         |       10000 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

この状態でもう一度EXPLAINをかけていきます。

mysql> EXPLAIN SELECT * FROM user ORDER BY name DESC LIMIT 100;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | name_index | 43      | NULL |  100 |   100.00 | Backward index scan; Using index |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.01 sec)

ここでまた、rowsに着目してください。

取得見積もり件数が100件のみとなっており、最低限のアクセスに抑えることができています。

インデックス作成前と後の違い

EXPLAINによって出力された結果をもう少し見ていきましょう。

インデックス	type	key	rows
無	ALL	NULL	29694
有	index	name_index	100.00

インデックス作成前であればtypeは「ALL」、keyは「NULL」となっていました。
これはインデックスが使われずにフルテーブルスキャンが行われていることになります。
テーブルからの行の取得を可能にするインデックスを追加することで、ALL を回避できます。

インデックス作成後であればtypeは「index」、keyは「name_index」となっています。
これらより正しくインデックスが使用され効率的にクエリが実行されていることが分かります。

まとめ

以前書いた記事（スロークエリの見つけ方）と今回のEXPLAINを組み合わせることによりクエリの最適化ができるようになります。
深掘ればもっと細かくクエリに関する情報を確認できるみたいですが、気になる方は公式サイト等をぜひ一読してみてください。