MySQLでクエリの実行計画を確認するにはEXPLAINを使う。
type・key・rows・Extraの意味を理解すると、インデックスが効いているかどうかを判断できる。

参考: 【MySQL】performance_schemaで重いクエリを特定する

以降の例では、主キーid、一意インデックスidx_email、通常インデックスidx_ageを持つusersテーブル（1,000行）と、user_idに通常インデックスを持つordersテーブル（1,000行）を使う。

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL,
  age INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  UNIQUE KEY idx_email (email),
  KEY idx_age (age)
);

CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  amount INT NOT NULL,
  KEY idx_user_id (user_id)
);

EXPLAINの実行方法

対象のSELECT文の先頭にEXPLAINを付けて実行する。クエリは実行されず、実行計画だけが返る。

mysql> EXPLAIN SELECT * FROM users WHERE id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

出力される列のうち、チューニングでまず見るのはtype・key・rows・Extraの4つである。

id・select_type・table

  • id: SELECT文の識別子。サブクエリやUNIONが絡むと複数行に分かれる
  • select_type: そのSELECTの種類（SIMPLE、PRIMARY、DERIVEDなど）
  • table: アクセス対象のテーブル名

派生テーブルを使うとidとselect_typeの関係が分かりやすい。

mysql> EXPLAIN SELECT * FROM (SELECT status, COUNT(*) AS cnt FROM users GROUP BY status) AS t WHERE cnt > 100;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
|  2 | DERIVED     | users      | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | Using temporary |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

外側のクエリがid=1のPRIMARY、派生テーブルを作る内側のサブクエリがid=2のDERIVEDである。tableの<derived2>は、id=2で作られた派生テーブルを参照している印である。

typeでアクセス方法を判断する

typeはテーブルへのアクセス方法を表し、パフォーマンスの良し悪しに直結する。代表的な値を、良い順に並べると以下の通りである。

type意味
const主キーまたは一意インデックスへの等価条件で、該当行が高々1件に絞られる
eq_refJOINの結合条件が相手テーブルの主キーまたは一意インデックスに一致し、1行だけ返る
ref一意でないインデックスへの等価条件で絞り込む
rangeインデックスを使った範囲検索（BETWEEN、<、INなど）
indexインデックスの全件を走査する
ALLテーブルの全件を走査する（フルテーブルスキャン）

const・eq_ref: 1行に絞り込める

主キーへの等価条件はconstになる。

mysql> EXPLAIN SELECT * FROM users WHERE id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | users | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

JOINで相手テーブルの主キーに結合する場合はeq_refになる。

mysql> EXPLAIN SELECT u.email, o.amount FROM orders o JOIN users u ON u.id = o.user_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+
|  1 | SIMPLE      | o     | NULL       | ALL    | idx_user_id   | NULL    | NULL    | NULL             | 1000 |   100.00 | NULL  |
|  1 | SIMPLE      | u     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | testdb.o.user_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------+

orders側（o）はidx_user_idを使わずALLになっている。ordersにはJOIN条件による絞り込みがなく、全件が結合対象になるためフルスキャンが選ばれている。一方users側（u）は、o.user_idの値ごとに主キーidで1行を引くためeq_refである。

ref・range: インデックスで絞り込む

一意でないインデックスへの等価条件はrefになる。

mysql> EXPLAIN SELECT * FROM orders WHERE user_id = 5;
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | orders | NULL       | ref  | idx_user_id   | idx_user_id | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

範囲検索はrangeになる。

mysql> EXPLAIN SELECT * FROM users WHERE age BETWEEN 20 AND 25;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------+
|  1 | SIMPLE      | users | NULL       | range | idx_age       | idx_age | 4       | NULL |  150 |   100.00 | Using index condition  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+------------------------+

refはrowsが1件で済むのに対し、rangeは条件に該当する複数行を走査するためrowsが150件になっている。

index・ALL: 絞り込めない

WHERE句の条件にインデックスが使えない場合、絞り込みができず全件を走査する。テーブル自体を走査すればALL、インデックスだけを走査すればindexになる。

mysql> EXPLAIN SELECT * FROM users WHERE status = 'active';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

mysql> EXPLAIN SELECT id FROM users;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | index | NULL          | idx_age | 4       | NULL | 1000 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

statusにインデックスがないため、1件目はALLでテーブル全体を走査している。2件目はSELECT idのみでテーブル本体の読み込みが不要なため、より軽いidx_ageのインデックスだけを走査するindexが選ばれている。ALLとindexはいずれも全件走査だが、indexのほうがテーブル本体を読まない分軽い。

possible_keys・key・key_len・ref

  • possible_keys: 使える可能性があるインデックスの候補
  • key: 実際に選ばれたインデックス
  • key_len: 使われたインデックスのバイト数。複合インデックスでどこまでの列が使われたかの目安になる
  • ref: keyと比較される値（constは定数、testdb.o.user_idのようなカラム名は結合相手の列）

possible_keysに候補があってもkeyがNULLの場合、オプティマイザがそのインデックスを使わないと判断している。status = 'active'の例のように該当行が多く、インデックス経由で1行ずつ引くよりテーブルを直接読むほうが速いとオプティマイザが判断した場合などに起きる。

rows・filteredは推定値

  • rows: そのアクセス方法で走査すると見積もられる行数
  • filtered: rowsのうちWHERE条件で絞り込まれずに残ると見積もられる割合（%）

いずれも統計情報に基づく見積もりであり、実際に走査した行数ではない。統計情報が古いと実態とずれるため、ANALYZE TABLEで統計を更新してから確認する。実測値を見たい場合はEXPLAIN ANALYZEを使う。

Extraでよく見る値

  • Using where: ストレージエンジンから返された行をサーバー側のWHERE条件でさらに絞り込んでいる
  • Using index: テーブル本体を読まず、インデックスだけでクエリが完結している（カバリングインデックス）
  • Using index condition: インデックス条件プッシュダウン。インデックス上で条件を評価してから、必要な行だけテーブル本体を読む
  • Using filesort: インデックスの順序でORDER BYをまかなえず、追加のソート処理が発生している
  • Using temporary: GROUP BYやDISTINCTなどのために一時テーブルを作っている

Using filesortとUsing temporaryは、インデックスで対応できていない処理があるサインである。

mysql> EXPLAIN SELECT status, COUNT(*) FROM users GROUP BY status ORDER BY COUNT(*);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+

statusにインデックスがないため、GROUP BYの集計に一時テーブルが必要になり、さらにCOUNT(*)でのソートにも追加処理が必要になっている。

参考

＼第一線のプログラマーの行動原理を学べる！／

プログラマが知るべき97のこと

Amazonで見る