MySQLのEXPLAIN解説

EXPLAINとは

MySQLのEXPLAINを使用することで、MySQLがクエリを実行する方法についての実行計画を知ることができます。
この実行計画によってクエリやインデックス最適化を行うことができます。

データ準備

本題に入る前に、今回の解説で利用するテーブルとデータを作成します。
下記のようなcompanyテーブル、employeeテーブル、messageテーブルを準備します。

companyテーブルの作成

mysql> CREATE TABLE `company` (
    ->   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
    ->   `company_name` VARCHAR(50) COMMENT '会社名',
    ->   `created_at` datetime NOT NULL COMMENT '作成日時',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会社';
Query OK, 0 rows affected (0.01 sec)

employeeテーブルの作成

mysql> CREATE TABLE `employee` (
    ->   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
    ->   `name` VARCHAR(20) COMMENT '名前',
    ->   `company_id` INT UNSIGNED NOT NULL COMMENT '会社ID',
    ->   `created_at` datetime NOT NULL COMMENT '作成日時',
    ->   `updated_at` datetime NOT NULL COMMENT '更新日時',
    ->   PRIMARY KEY (`id`)
    ->   KEY `idx_employee_created_at` (`created_at`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='従業員';
Query OK, 0 rows affected (0.01 sec)

messageテーブルの作成

mysql> CREATE TABLE `message` (
    ->   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'id',
    ->   `employee_id` INT UNSIGNED NOT NULL COMMENT '従業員id',
    ->   `message` TEXT COMMENT 'メッセージ',
    ->   `created_at` datetime NOT NULL COMMENT '作成日時',
    ->   `updated_at` datetime NOT NULL COMMENT '更新日時',
    ->   PRIMARY KEY (`id`),
    ->   KEY `idx_message_employee_id` (`employee_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='メッセージ';
Query OK, 0 rows affected (0.00 sec)

companyのデータ準備

mysql> INSERT INTO company VALU (1, '株式会社hoge', NOW()););
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO company VALUES (10, 'fuga株式会社', NOW());
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM company;
+----+------------------+---------------------+
| id | company_name     | created_at          |
+----+------------------+---------------------+
|  1 | 株式会社hoge     | 2021-05-06 16:04:29 |
| 10 | fuga株式会社     | 2021-05-06 16:04:56 |
+----+------------------+---------------------+
2 rows in set (0.01 sec)

employeeのデータ準備

mysql> INSERT INTO employee VALUES (1, '山田', 1, NOW(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO employee VALUES (2, '佐藤', 10, NOW(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT *  FROM employee;
+----+--------+------------+---------------------+---------------------+
| id | name   | company_id | created_at          | updated_at          |
+----+--------+------------+---------------------+---------------------+
|  1 | 山田   |          1 | 2021-05-06 16:07:30 | 2021-05-06 16:07:30 |
|  2 | 佐藤   |         10 | 2021-05-06 16:07:38 | 2021-05-06 16:07:38 |
+----+--------+------------+---------------------+---------------------+
2 rows in set (0.00 sec)

messageのデータ準備

mysql> INSERT INTO message VALUES (1, 1, 'こんにちは', NOW(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO message VALUES (2, 2, '元気ですか', NOW(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO message VALUES (3, 1, 'hello', NOW(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO message VALUES (4, 1, 'good night', NOW(), NOW());
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM message;
+----+-------------+-----------------+---------------------+---------------------+
| id | employee_id | message         | created_at          | updated_at          |
+----+-------------+-----------------+---------------------+---------------------+
|  1 |           1 | こんにちは      | 2021-05-06 16:08:30 | 2021-05-06 16:08:30 |
|  2 |           2 | 元気ですか      | 2021-05-06 16:08:36 | 2021-05-06 16:08:36 |
|  3 |           1 | hello           | 2021-05-06 16:38:59 | 2021-05-06 16:38:59 |
|  4 |           1 | good night      | 2021-05-06 16:39:10 | 2021-05-06 16:39:10 |
+----+-------------+-----------------+---------------------+---------------------+
4 rows in set (0.00 sec)

EXPLAINの実行方法

EXPLAINの実行方法はとても簡単で、下記のように実行計画を見たいクエリの先頭にEXPLAINをつけるだけです。

EXPLAINの実行

--- 下記のクエリの実行計画を確認する
--- SELECT id FROM user WHERE id = 1;
mysql > EXPLAIN SELECT id FROM user WHERE id = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   100   |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+

EXPLAIN結果の見方

EXPLAINの目的・実行方法が分かったところで、EXPLAINの出力結果の意味を解説していきます。

id

idはSELECT識別子で、実行順序を表します。

table

クエリを発行しているテーブル。

select_type

select_typeは、SELECTの種類を表します。
種類は以下の通りです。

SIMPLE

UNIONやサブクエリを使わない単純なSELECT。

SIMPLEの例

mysql> EXPLAIN SELECT * FROM employee WHERE id = 1;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | employee | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

PRIMARY

外部クエリのSELECT。

SUBQUERY

サブクエリ内の最初のSELECT。

PRIMARYとSUBQUERYの例

mysql> EXPLAIN SELECT * FROM employee WHERE id = (SELECT employee_id FROM message where id = 1);
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | employee | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
|  2 | SUBQUERY    | message  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)

UNION

UNION内の2つ目以降のSELECT。

UNION RESULT

UNIONの結果。

UNIONとUNION_RESULTの例

mysql> EXPLAIN SELECT id as employee_id FROM employee WHERE created_at < '2021-05-06 15:20:00' UNION SELECT employee_id FROM message;
+----+--------------+------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
| id | select_type  | table      | partitions | type  | possible_keys           | key                     | key_len | ref  | rows | filtered | Extra                    |
+----+--------------+------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY      | employee   | NULL       | range | idx_employee_created_at | idx_employee_created_at | 5       | NULL |    1 |   100.00 | Using where; Using index |
|  2 | UNION        | message    | NULL       | index | NULL                    | idx_message_employee_id | 4       | NULL |    2 |   100.00 | Using index              |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL                    | NULL                    | NULL    | NULL | NULL |     NULL | Using temporary          |
+----+--------------+------------+------------+-------+-------------------------+-------------------------+---------+------+------+----------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

DEPENDENT UNION

相関関係のあるUNION。
UNION内で相関関係のあるサブクエリが使用された場合、こちらに当てはまります。

DEPENDENT SUBQUERY

相関関係のあるサブクエリ。

DERIVED

FROM内で使用しているサブクエリ。

DEPENDENT DERIVED

別のテーブルに依存する導出テーブル。

MATERIALIZED

実体化されたサブクエリ

UNCACHEABLE SUBQUERY

キャッシュできず、外部クエリの行ごとに実行されるサブクエリ。

UNCACHEABLE UNION

UNCACHEABLE SUBQUERYのうち、UNION内の2つ目以降のSELECT。

partitions

一致するパーティション。

type

テーブルの結合方法。
種類は以下の通りです。

system

テーブルに行が1行しかありません。

const

一致するレコードが最大で1つであり、非常に高速です。
プライマリキーとユニークキーを定数値と比較する時に使用されます。

eq_ref

前のテーブルの行の組み合わせごとに、このテーブルから1行ずつ読み取られる。
結合でインデックスが使用されており、インデックスがプライマリキーまたはユニークキー（NOT NULL）である場合に使用されます。(つまりconstとの違いは、結合でインデックスが使用されていること。)
単一の行の絞り込みが可能であるため、system・constの次に高速で最適なtype。

ref

プライマリキーまたはユニークキー以外のインデックスを用いて等価検索（=または<=>）を行った場合に使用されます。
単一の行の絞り込みはできませんが、使用されているキーが数行の場合は適切な結合型です。

このカラムにfuncが格納されている場合、特定の関数や演算子の結果です。詳細を知りたい場合、EXPLAIN実行後にSHOW WARNINGS;を実行することで確認できます。

fulltext

FULLTEXTインデックスを使用しています。

index_merge

インデックスマージ最適化が行われています。

unique_subquery

INサブクエリでプライマリキーまたはユニークキーをSELECTする場合に使用されます。

index_subquery

INサブクエリでプライマリキーまたはユニークキー以外のカラムをSELECTする場合に使用されます。

range

インデックスを用いて特定の範囲の行を取得する場合に使用されます。
このtypeのkeyカラムは、使用されるインデックスを示し、key_lenカラムには使用された最長のインデックスパートが格納されます。
このtypeのrefカラムはNULLになります。
=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE または IN()の演算子を用いてキーカラムを定数と比較する場合に使用されます。

index

indexは下記の2つの方法に分かれます。

• インデックスがクエリのカバリングインデックスで、インデックスツリーのみが参照される場合に使用されます。このとき、ExtraカラムにはUsing indexが格納されます。
• インデックスがカバリングインデックスでなく、フルテーブルスキャンが行われます。インデックスからの読み取りを使用して、インデックス順でデータ行をルックアップして実行されます。このとき、ExtraカラムにはUses indexが格納されます。

ALL

インデックスが全く使用されていないフルテーブルスキャンの場合に使用されます。
パフォーマンスがかなり悪いのでチューニングの必要があります。

Possible keys

選択可能なインデックスが格納されます。

keys

実際に使用されたインデックスが格納されます。

rows

fetchされる行数の見積もり。
あくまでも見積もりなので、実際にfetchされる行数とは異なります。

filtered

フィルタ処理される行数の割合。
最大値は100で、このときフィルタ処理されなかったことを表し、数字が小さくなるほどフィルタリングされた行が増加したことを示します。

key_len

使用するキーの長さを示します。短いほうが速いです。

ref

実際に使用されたインデックス（keysカラムに格納されたインデックス）と比較される値またはカラム。
値が指定されている場合は、constが格納されます。

Extra

Extraカラムには、実行計画の追加情報が格納されます。
特によく見る種類だけ書いておきます。

Using where

WHERE句で検索条件が指定されており、インデックスだけではその検索条件を適用できない場合に使用されます。

Using filesort

クイックソートが使用されています。

Using index

上でも記載の通り、typeカラムがindexでインデックスがカバリングインデックスの場合に使用されます。

Uses index

上でも記載の通り、typeカラムがindexでインデックスがカバリングインデックスでない場合に使用されます。

参考サイト

EXPLAIN出力フォーマット
MySQLのEXPLAINを徹底解説!!