MySQL - EXPLAIN

概要

EXPLAIN 文は、MySQLがSELECT文をどのように実行するかを表示する、クエリ実行計画の解析ツールである。
クエリ最適化の第一歩として、EXPLAIN を使用することで、インデックスの使用状況、テーブルスキャンの有無、結合の順序等を確認できる。

EXPLAIN は、パフォーマンスボトルネックの特定に不可欠であり、フルテーブルスキャン、ファイルソート、一時テーブルの使用等の問題を検出できる。

MySQL 5.6以降では、EXPLAIN の出力形式として、従来の表形式に加えて、JSON形式、TREE形式 (8.0.16以降)、ANALYZE形式 (8.0.18以降) がサポートされている。

EXPLAIN は、SELECT文だけでなく、DELETE文、INSERT文、REPLACE文、UPDATE文に対しても使用できる。
これにより、更新系クエリのパフォーマンスも分析できる。

基本構文

EXPLAIN文の構文

EXPLAIN 文の基本構文を以下に示す。

 EXPLAIN [explain_type] {SELECT文 | DELETE文 | INSERT文 | REPLACE文 | UPDATE文}
 
 explain_type:
    {FORMAT = {TRADITIONAL | JSON | TREE}}
    | {ANALYZE}

下表に、EXPLAIN のバリエーションを示す。

構文	説明
EXPLAIN	デフォルトの表形式 (TRADITIONAL形式) で実行計画を表示する。
EXPLAIN FORMAT=JSON	JSON形式で実行計画を表示する。 コスト情報等の詳細情報を含む。
EXPLAIN FORMAT=TREE	ツリー形式で実行計画を表示する。(MySQL 8.0.16以降)
EXPLAIN ANALYZE	実際にクエリを実行して、推定値と実測値を比較した結果を表示する。(MySQL 8.0.18以降)

EXPLAINの実行方法

基本的な EXPLAIN の実行例を以下に示す。

 # 最もシンプルなEXPLAIN
 EXPLAIN SELECT * FROM users WHERE id = 100;
 
 # 複雑な結合クエリの実行計画
 EXPLAIN
 SELECT u.name, o.order_date, oi.product_name
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    INNER JOIN order_items oi ON o.id = oi.order_id
    WHERE u.status = 'active'
       AND o.order_date >= '2024-01-01';
 
 # サブクエリを含むクエリの実行計画
 EXPLAIN
 SELECT * FROM users
    WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');
 
 # UPDATE文の実行計画
 EXPLAIN UPDATE users SET status = 'inactive' WHERE last_login < '2023-01-01';
 
 # DELETE文の実行計画
 EXPLAIN DELETE FROM logs WHERE created_at < '2024-01-01';

EXPLAIN は実際にクエリを実行しないため、SELECT文以外でも安全に使用できる。
ただし、EXPLAIN ANALYZE は実際にクエリを実行するため、DELETE文やUPDATE文には注意が必要である。

EXPLAIN出力カラム

出力カラム一覧

EXPLAIN の出力には、以下の12個のカラムが含まれる。

EXPLAIN出力カラム一覧

カラム名	説明
id	SELECTの識別子 (クエリ内のSELECT文の実行順序)
select_type	SELECTのタイプ (SIMPLE, PRIMARY, UNION, SUBQUERY等)
table	参照しているテーブル名
partitions	使用されるパーティション (パーティションテーブルの場合)
type	アクセスタイプ (結合タイプ) (system, const, ref, range, index, ALL等)
possible_keys	使用可能なインデックスのリスト
key	実際に使用されたインデックス
key_len	使用されたインデックスのバイト長
ref	インデックスと比較される値またはカラム
rows	検査される行数の推定値
filtered	フィルタリングされる行の割合 (%)
Extra	追加情報 (Using where, Using index, Using filesort等)

これらのカラムを理解することで、クエリのパフォーマンス特性を把握できる。
特に、type、key、rows、Extra は重要な指標である。

id

id カラムは、SELECT文の識別子である。
クエリ内の各SELECT文に対して、一意の番号が割り当てられる。

id の値が同じ行は、同じSELECT文の一部として実行される。
id の値が異なる場合、数値が大きい方が先に実行される。(外側から内側へ)

例を以下に示す。

 EXPLAIN
 SELECT * FROM users
    WHERE id IN (SELECT user_id FROM orders WHERE status = 'completed');

この場合、サブクエリ (orders) の id が大きく、先に実行される。
その後、メインクエリ (users) が実行される。

UNIONクエリの場合、各SELECTに異なる id が割り当てられ、UNIONの結果をマージする行には id が NULL となる。

select_type

select_type カラムは、SELECTのタイプを示す。
主要な値を以下に示す。

select_typeの値

値	説明
SIMPLE	単純なSELECT (サブクエリやUNIONを含まない)
PRIMARY	最も外側のSELECT (サブクエリやUNIONを含む場合)
UNION	UNIONの2番目以降のSELECT
DEPENDENT UNION	UNIONの2番目以降のSELECTで、外側のクエリに依存
UNION RESULT	UNIONの結果を取得する処理
SUBQUERY	サブクエリ内の最初のSELECT
DEPENDENT SUBQUERY	サブクエリ内の最初のSELECTで、外側のクエリに依存
DERIVED	FROM句のサブクエリ (派生テーブル)
MATERIALIZED	マテリアライズされたサブクエリ
UNCACHEABLE SUBQUERY	キャッシュできないサブクエリ (変数を含む等)
UNCACHEABLE UNION	キャッシュできないUNION

SIMPLE は、最もシンプルで高速なクエリである。
DEPENDENT SUBQUERY は、外側のクエリの各行に対してサブクエリが実行されるため、パフォーマンスが低下する可能性がある。

table

table カラムは、その行が参照しているテーブル名を示す。

通常のテーブル名以外に、以下の特殊な値が表示される場合がある。

• <derivedN>

  派生テーブル (FROM句のサブクエリ) で、Nは対応する id の値。

• <unionM,N>

  UNIONの結果で、MとNはUNION対象の id の値。

• <subqueryN>

  マテリアライズされたサブクエリで、Nは対応する id の値。

partitions

partitions カラムは、クエリがアクセスするパーティションを示す。
パーティションテーブルを使用していない場合、このカラムは NULL となる。

パーティションテーブルを使用している場合、アクセスされるパーティション名がカンマ区切りで表示される。
パーティションプルーニング (不要なパーティションのスキップ) が適切に行われているかを確認できる。

例を以下に示す。

 # パーティションテーブルの例
 CREATE TABLE logs (
    id INT,
    created_at DATE,
    message TEXT
 )
 PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
 );
 
 # 特定の年のデータを取得
 EXPLAIN SELECT * FROM logs WHERE created_at >= '2024-01-01';
 # partitionsカラムには 'p2024' のみが表示される

type (アクセスタイプ)

type カラムは、テーブルへのアクセス方法 (結合タイプ) を示す。
パフォーマンスの観点から、最も重要なカラムの1つである。

アクセスタイプを性能の良い順 (上から順) に以下に示す。

アクセスタイプ (性能順)

タイプ	説明	パフォーマンス
system	テーブルに1行のみ (システムテーブル)	最速
const	PRIMARY KEYまたはUNIQUEインデックスで定数と比較 (最大1行)	非常に高速
eq_ref	結合で、各行に対してPRIMARY KEYまたはUNIQUEインデックスを使用 (1行)	非常に高速
ref	非UNIQUE インデックスを使用 (複数行の可能性)	高速
fulltext	FULLTEXTインデックスを使用	高速 (全文検索)
ref_or_null	ref と同様だが、NULLも検索	高速
index_merge	複数のインデックスを使用してマージ	中速
unique_subquery	IN句のサブクエリでUNIQUEインデックスを使用	中速
index_subquery	IN句のサブクエリで非UNIQUEインデックスを使用	中速
range	インデックスを使用した範囲スキャン	中速
index	インデックスフルスキャン (インデックス全体を読む)	遅い
ALL	フルテーブルスキャン (テーブル全体を読む)	最も遅い

理想的には、const、eq_ref、ref、range を目指す。
index や ALL が表示される場合、インデックスの追加または最適化を検討すべきである。

アクセスタイプの例を以下に示す。

 # constの例 (PRIMARY KEYで定数検索)
 EXPLAIN SELECT * FROM users WHERE id = 100;
 # type: const
 
 # refの例 (非UNIQUEインデックスで検索)
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # type: ref (statusにインデックスがある場合)
 
 # rangeの例 (範囲検索)
 EXPLAIN SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
 # type: range (created_atにインデックスがある場合)
 
 # ALLの例 (フルテーブルスキャン)
 EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
 # type: ALL (関数を使用しているためインデックスが使用されない)

possible_keys

possible_keys カラムは、MySQLが使用を検討したインデックスのリストを示す。
実際に使用されたインデックスは key カラムに表示される。

possible_keys が NULL の場合、使用可能なインデックスが存在しないことを示す。
この場合、WHERE句やJOIN条件で使用されているカラムにインデックスを追加することを検討する。

複数のインデックスが候補として表示される場合、MySQLのオプティマイザが最適なインデックスを選択する。
ただし、オプティマイザの選択が最適でない場合もあるため、key カラムと比較して確認する。

key

key カラムは、実際に使用されたインデックス名を示す。
possible_keys の中から、オプティマイザが選択したインデックスである。

key が NULL の場合、インデックスが使用されていないことを示す。
これは、フルテーブルスキャンが発生していることを意味し、パフォーマンス改善の余地がある。

key が PRIMARY の場合、PRIMARY KEYが使用されている。
その他の値は、インデックス名 (CREATE INDEX時に指定した名前) である。

例を以下に示す。

 # インデックスが使用される例
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # possible_keys: idx_status
 # key: idx_status
 
 # インデックスが使用されない例
 EXPLAIN SELECT * FROM users WHERE UPPER(email) = 'ADMIN@EXAMPLE.COM';
 # possible_keys: NULL
 # key: NULL (関数を使用しているためインデックスが使用されない)

key_len

key_len カラムは、使用されたインデックスのバイト長を示す。
複合インデックスの場合、どの部分まで使用されたかを判断できる。

key_len の計算方法を以下に示す。

key_lenの計算 (主要なデータ型)

データ型	バイト長	NULL許容の場合	可変長の場合
INT	4	+1	-
BIGINT	8	+1	-
CHAR(N)	N × 文字セットのバイト数	+1	-
VARCHAR(N)	N × 文字セットのバイト数	+1	+2
DATE	3	+1	-
DATETIME	8	+1	-
TIMESTAMP	4	+1	-

例を以下に示す。

 # 複合インデックスの例
 CREATE INDEX idx_user_status_created ON users(status, created_at);
 
 # statusのみが使用される場合
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # key_len: 50 (VARCHAR(50) の場合、50 + 1(NULL) + 2(可変長) = 53 程度)
 
 # statusとcreated_atの両方が使用される場合
 EXPLAIN SELECT * FROM users WHERE status = 'active' AND created_at > '2024-01-01';
 # key_len: 53 + 8 + 1 = 62 程度 (DATETIMEの場合)

key_len が短い場合、複合インデックスの一部のみが使用されていることを示す。
インデックスの順序を見直すことで、パフォーマンスを改善できる可能性がある。

ref

ref カラムは、インデックスと比較される値またはカラムを示す。

ref の主要な値を以下に示す。

値	説明
const	定数値と比較
データベース名.テーブル名.カラム名	他のテーブルのカラムと比較 (結合)
func	関数の結果と比較
NULL	比較対象なし (rangeスキャン等)

例を以下に示す。

 # constの例
 EXPLAIN SELECT * FROM users WHERE id = 100;
 # ref: const
 
 # 結合の例
 EXPLAIN
 SELECT u.name, o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active';
 # usersのref: const
 # ordersのref: database_name.u.id

rows

rows カラムは、MySQLがクエリ実行時に検査すると推定する行数を示す。
この値は推定値であり、実際の行数とは異なる場合がある。

rows の値が大きい場合、多くの行を検査する必要があることを示す。
インデックスの追加、WHERE句の見直し、クエリの最適化を検討する。

複数のテーブルを結合する場合、rows の値を掛け合わせることで、全体の検査行数を推定できる。
例えば、テーブルAの rows が100、テーブルBの rows が200の場合、推定検査行数は 100 × 200 = 20,000 となる。

例を以下に示す。

 # rowsが小さい例 (インデックスを使用)
 EXPLAIN SELECT * FROM users WHERE id = 100;
 # rows: 1
 
 # rowsが大きい例 (フルテーブルスキャン)
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # rows: 50000 (インデックスがない場合、全行を検査)
 
 # インデックスを追加後
 CREATE INDEX idx_status ON users(status);
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # rows: 5000 (インデックスにより絞り込まれる)

filtered

filtered カラムは、WHERE句の条件によってフィルタリングされる行の割合を示す。
値は0から100のパーセンテージで表示される。

filtered の値が小さい場合、多くの行が条件でフィルタリングされることを示す。
これは、インデックスが適切に使用されていない、または条件が非効率的である可能性を示唆する。

例を以下に示す。

 # filteredが高い例 (条件が効率的)
 EXPLAIN SELECT * FROM users WHERE id = 100;
 # filtered: 100.00 (PRIMARY KEYで完全一致)
 
 # filteredが低い例 (条件が非効率的)
 EXPLAIN SELECT * FROM users WHERE status = 'active' AND email LIKE '%@example.com';
 # filtered: 10.00 (LIKE '%...' は非効率的)

filtered と rows を掛け合わせることで、実際に返される行数の推定値を計算できる。
例えば、rows が1000、filtered が10.00の場合、推定される結果行数は 1000 × 0.10 = 100 となる。

Extra

Extra カラムは、クエリ実行に関する追加情報を示す。
パフォーマンスチューニングにおいて、重要な手がかりとなる。

主要な Extra の値を以下に示す。

主要なExtra値

値	説明	パフォーマンス
Using index	カバリングインデックス (インデックスのみでクエリを満たす)	非常に良い
Using where	WHERE句の条件でフィルタリング	普通
Using index condition	インデックスコンディションプッシュダウン (ICP) を使用	良い
Using filesort	ファイルソートが必要 (ORDER BY用)	悪い
Using temporary	一時テーブルを使用	悪い
Using join buffer	結合バッファを使用	注意
Impossible WHERE	WHERE句が常にFALSE	警告
Select tables optimized away	COUNT(*) や MIN / MAX の最適化	非常に良い
No tables used	FROMがない、またはFROM DUAL	普通
Distinct	DISTINCTの最適化が行われた	良い
Using MRR	Multi-Range Read最適化を使用	良い
FirstMatch	FirstMatch準結合戦略を使用	良い
LooseScan	LooseScan準結合戦略を使用	良い
Start temporary, End temporary	Duplicate Weedout準結合戦略を使用	普通

Using filesort と Using temporary は、パフォーマンス問題の兆候であることが多い。

Using filesort

Using filesort は、ORDER BY句の結果をソートするために、ファイルソート (メモリまたはディスク上でのソート) が必要であることを示す。

ファイルソートを避けるには、ORDER BY句で使用するカラムにインデックスを作成する。

 # Using filesortが発生する例
 EXPLAIN SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
 # Extra: Using where; Using filesort
 
 # インデックスを追加してUsing filesortを回避
 CREATE INDEX idx_status_created ON users(status, created_at);
 EXPLAIN SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
 # Extra: Using where (Using filesortが消える)

Using temporary

Using temporary は、クエリ実行中に一時テーブルが作成されることを示す。
GROUP BY、DISTINCT、UNION、ORDER BY等で一時テーブルが使用される。

一時テーブルを避けるには、インデックスの最適化、クエリの書き換えを検討する。

 # Using temporaryが発生する例
 EXPLAIN SELECT DISTINCT status FROM users ORDER BY created_at;
 # Extra: Using temporary; Using filesort
 
 # インデックスを使用してUsing temporaryを回避
 CREATE INDEX idx_status ON users(status);
 EXPLAIN SELECT status FROM users GROUP BY status;
 # Extra: Using index (一時テーブルが不要)

Using index (カバリングインデックス)

Using indexは、クエリがインデックスのみで満たされることを示す。(カバリングインデックス)
テーブルデータへのアクセスが不要なため、非常に高速である。

 # カバリングインデックスの例
 CREATE INDEX idx_status_email ON users(status, email);
 
 EXPLAIN SELECT status, email FROM users WHERE status = 'active';
 # Extra: Using index (テーブルデータを読まずにインデックスのみで完結)

Using index condition (ICP)

Using index conditionは、インデックスコンディションプッシュダウン (Index Condition Pushdown, ICP) が使用されていることを示す。
ICPは、WHERE句の条件をストレージエンジン層で評価することで、不要なテーブルアクセスを削減する最適化である。

 # ICPが使用される例
 CREATE INDEX idx_name ON users(name);
 
 EXPLAIN SELECT * FROM users WHERE name LIKE 'John%' AND age > 30;
 # Extra: Using index condition

ICPにより、インデックスで絞り込んだ後、テーブルアクセス前にWHERE条件を評価できる。

EXPLAIN FORMAT=JSON

JSON形式の構文

EXPLAIN FORMAT=JSON は、実行計画をJSON形式で出力する。
表形式よりも詳細な情報 (コスト情報、実行順序等) を含む。

 EXPLAIN FORMAT=JSON
 SELECT u.name, o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active';

JSON形式の出力例を以下に示す。(一部抜粋)

 {
   "query_block": {
     "select_id": 1,
     "cost_info": {
       "query_cost": "1234.56"
     },
     "nested_loop": [
       {
         "table": {
           "table_name": "u",
           "access_type": "ref",
           "possible_keys": ["idx_status"],
           "key": "idx_status",
           "used_key_parts": ["status"],
           "key_length": "50",
           "ref": ["const"],
           "rows_examined_per_scan": 5000,
           "rows_produced_per_join": 5000,
           "filtered": "100.00",
           "cost_info": {
             "read_cost": "100.00",
             "eval_cost": "500.00",
             "prefix_cost": "600.00",
             "data_read_per_join": "1M"
           },
           "used_columns": ["id", "name", "status"]
         }
       },
       {
         "table": {
           "table_name": "o",
           "access_type": "ref",
           "possible_keys": ["idx_user_id"],
           "key": "idx_user_id",
           "used_key_parts": ["user_id"],
           "key_length": "4",
           "ref": ["database.u.id"],
           "rows_examined_per_scan": 10,
           "rows_produced_per_join": 50000,
           "filtered": "100.00",
           "cost_info": {
             "read_cost": "500.00",
             "eval_cost": "5000.00",
             "prefix_cost": "6100.00",
             "data_read_per_join": "2M"
           },
           "used_columns": ["id", "user_id", "order_date"]
         }
       }
     ]
   }
 }

主要なJSONフィールド

JSON形式の出力に含まれる主要なフィールドを以下に示す。

フィールド	説明
query_block	クエリブロック全体の情報
cost_info	コスト情報 (クエリ全体のコスト)
nested_loop	ネストループ結合の配列 (テーブルの処理順序)
table_name	テーブル名
access_type	アクセスタイプ (ref, range, ALL等)
possible_keys	使用可能なインデックスの配列
key	実際に使用されたインデックス
used_key_parts	使用されたインデックスの部分 (複合インデックスの場合)
rows_examined_per_scan	スキャンごとに検査される行数
rows_produced_per_join	結合後に生成される行数
filtered	フィルタリング割合
read_cost	読み取りコスト
eval_cost	評価コスト
prefix_cost	累積コスト
used_columns	使用されるカラムの配列

コスト情報の読み方

JSON形式の cost_info には、以下のコスト情報が含まれる。

フィールド	説明
read_cost	データを読み取るためのコスト
eval_cost	行を評価するためのコスト (rows × 0.1)
prefix_cost	このテーブルまでの累積コスト (read_cost + eval_cost + 前のテーブルのコスト)
query_cost	クエリ全体のコスト

コスト値は相対的な値であり、絶対的な実行時間ではない。
異なるクエリのコストを比較することで、どちらが効率的かを判断できる。

コスト値が大きいテーブルは、ボトルネックになっている可能性がある。
インデックスの追加、クエリの書き換えを検討する。

EXPLAIN FORMAT=TREE

TREE形式の構文

EXPLAIN FORMAT=TREE は、実行計画をツリー形式で表示する。(MySQL 8.0.16以降)
クエリの処理フローを視覚的に理解しやすい形式である。

 EXPLAIN FORMAT=TREE
 SELECT u.name, o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active'
    ORDER BY o.order_date DESC;

TREE形式の出力例を以下に示す。

-> Nested loop inner join  (cost=6100.00 rows=50000)
   -> Filter: (u.status = 'active')  (cost=600.00 rows=5000)
      -> Index lookup on u using idx_status (status='active')  (cost=600.00 rows=5000)
   -> Index lookup on o using idx_user_id (user_id=u.id)  (cost=0.25 rows=10)

-> Sort: o.order_date DESC  (cost=6100.00 rows=50000)

TREE形式の読み方

TREE形式は、上から下、内側から外側の順に処理される。
インデント (矢印 ->) が深いほど、内側 (先に実行される) の処理である。

TREE形式の主要な要素を以下に示す。

要素	説明
Nested loop inner join	ネストループ内部結合
Filter	フィルタリング条件
Index lookup	インデックス検索
Table scan	フルテーブルスキャン
Sort	ソート処理
Aggregate	集約処理 (GROUP BY, COUNT等)
Materialize	マテリアライズ (サブクエリの結果を一時テーブルに保存)

コストと推定行数が各ステップに表示されるため、ボトルネックを特定しやすい。

例を以下に示す。

 EXPLAIN FORMAT=TREE
 SELECT status, COUNT(*) AS cnt
    FROM users
    WHERE created_at >= '2024-01-01'
    GROUP BY status;

出力例を以下に示す。

-> Group aggregate: count(0)  (cost=2000.00 rows=10)
   -> Filter: (users.created_at >= '2024-01-01')  (cost=1000.00 rows=5000)
      -> Index range scan on users using idx_created_at  (cost=1000.00 rows=10000)

この例では、以下の順序で処理される。

1. idx_created_at インデックスで範囲スキャンする。(10000行を検査)
2. created_at >= '2024-01-01' でフィルタリング。(5000行に絞り込み)
3. status でグループ化して COUNT(*) を集約。(10グループ)

EXPLAIN ANALYZE

EXPLAIN ANALYZEの構文

EXPLAIN ANALYZE は、クエリを実際に実行して、推定値と実測値を比較した結果を表示する。(MySQL 8.0.18以降)

※重要
EXPLAIN ANALYZE は実際にクエリを実行するため、SELECT文以外 (UPDATE, DELETE等) に使用する場合は注意が必要である。

 EXPLAIN ANALYZE
 SELECT u.name, o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active'
    ORDER BY o.order_date DESC
    LIMIT 100;

EXPLAIN ANALYZE の出力例を以下に示す。

-> Limit: 100 row(s)  (actual time=0.123..5.678 rows=100 loops=1)
   -> Sort: o.order_date DESC, limit input to 100 row(s) per chunk  (actual time=5.567..5.678 rows=100 loops=1)
      -> Nested loop inner join  (cost=6100.00 rows=50000) (actual time=0.089..4.567 rows=45678 loops=1)
         -> Filter: (u.status = 'active')  (cost=600.00 rows=5000) (actual time=0.045..1.234 rows=4876 loops=1)
            -> Index lookup on u using idx_status (status='active')  (cost=600.00 rows=5000) (actual time=0.034..0.987 rows=4876 loops=1)
         -> Index lookup on o using idx_user_id (user_id=u.id)  (cost=0.25 rows=10) (actual time=0.002..0.003 rows=9 loops=4876)

出力の読み方

EXPLAIN ANALYZE の出力には、以下に示す情報が含まれる。

要素	説明
cost=...	推定コスト (オプティマイザの見積もり)
rows=...	推定行数 (オプティマイザの見積もり)
actual time=...	実際の実行時間 (ミリ秒) - 最初の行を取得するまでの時間..最後の行を取得するまでの時間
rows=... (actual)	実際に返された行数
loops=...	処理がループした回数

actual time の形式は 開始時間..終了時間 である。
例えば、actual time=0.045..1.234 は、最初の行を取得するのに 0.045ミリ秒、全ての行を取得するのに 1.234ミリ秒掛かったことを示す。

推定値と実測値の差異

EXPLAIN ANALYZE により、オプティマイザの推定値 (rows, cost) と実測値 (actual rows, actual time) を比較できる。

推定値と実測値に大きな差がある場合、以下の原因が考えられる。

• テーブル統計が古い

  ANALYZE TABLE文を実行して統計を更新する。

• データ分布が偏っている

  ヒストグラム統計を収集する。(MySQL 8.0以降)

• WHERE句の条件が複雑

  オプティマイザが正確に推定できない。

推定値と実測値の差異を解消する方法を以下に示す。

 # テーブル統計を更新
 ANALYZE TABLE users;
 ANALYZE TABLE orders;
 
 # ヒストグラム統計を収集 (MySQL 8.0以降)
 ANALYZE TABLE users UPDATE HISTOGRAM ON status, created_at;
 ANALYZE TABLE orders UPDATE HISTOGRAM ON user_id, order_date;
 
 # 統計更新後に再度EXPLAIN ANALYZEを実行
 EXPLAIN ANALYZE
 SELECT u.name, o.order_date
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active';

推定行数が5000だが実際には50しか返されない場合、インデックスの選択が最適化される可能性がある。

実践的な読み方

確認すべきポイント

EXPLAIN の出力を分析する際、以下に示すポイントを確認する。

これらのポイントを確認することにより、パフォーマンスボトルネックを特定できる。

確認項目	説明
type カラムを確認	ALL (フルテーブルスキャン) や index (インデックスフルスキャン) は要注意。 理想は const、eq_ref、ref、range。
key カラムを確認	NULL の場合、インデックスが使用されていない。インデックスの追加を検討。
rows カラムを確認	大きな値の場合、多くの行を検査している。インデックスで絞り込めないか確認。
Extra カラムを確認	Using filesort、Using temporary はパフォーマンス問題の兆候。 Using index (カバリングインデックス) は理想的。
結合の順序を確認	小さいテーブルを先に処理しているか (ドライビングテーブルの選択)。
フィルタリング割合を確認	filtered が低い場合、多くの行が無駄に処理されている。

よくあるパフォーマンス問題の特定

フルテーブルスキャン

type が ALL の場合、フルテーブルスキャンが発生している。
テーブルの全行を読み取るため、テーブルサイズが大きい場合は非常に遅い。

フルテーブルスキャンが発生する原因を以下に示す。

• WHERE句で使用するカラムにインデックスがない。
• WHERE句で関数を使用している。

  例: WHERE YEAR(created_at) = 2024

• WHERE句で否定条件を使用している。

  例: WHERE status != 'active'

• インデックスが存在するが選択性が低い。(カーディナリティが低い)

対策を以下に示す。

 # 問題: フルテーブルスキャン
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # type: ALL
 # rows: 1000000
 
 # 対策: インデックスを追加
 CREATE INDEX idx_status ON users(status);
 
 # 改善後
 EXPLAIN SELECT * FROM users WHERE status = 'active';
 # type: ref
 # key: idx_status
 # rows: 50000

filesortの発生

Extra に Using filesort が表示される場合、ORDER BY句の結果をソートするために、ファイルソート処理が必要である。

ファイルソートは、メモリ (sort_buffer_size) または ディスク上で行われる。
ソート対象のデータが大きい場合、パフォーマンスが大幅に低下する。

filesortを回避する方法を以下に示す。

• ORDER BY句で使用するカラムにインデックスを作成
• 複合インデックスの順序をORDER BY句と一致させる
• WHERE句とORDER BY句の両方を考慮した複合インデックスを作成

対策を以下に示す。

 # 問題: Using filesort
 EXPLAIN SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
 # Extra: Using where; Using filesort
 
 # 対策: 複合インデックスを作成 (status, created_at)
 CREATE INDEX idx_status_created ON users(status, created_at);
 
 # 改善後
 EXPLAIN SELECT * FROM users WHERE status = 'active' ORDER BY created_at DESC;
 # Extra: Using where
 # (Using filesortが消える)

※注意
複合インデックスの順序は重要である。
WHERE status = 'active' ORDER BY created_at の場合、idx_status_created (status, created_at) が適切である。
逆の順序 idx_created_status (created_at, status) では、filesortは回避できない。

一時テーブルの使用

Extra に Using temporary が表示される場合、一時テーブルが作成される。
一時テーブルは、メモリ (tmp_table_size, max_heap_table_size) または ディスク上に作成される。

一時テーブルが作成される主なケースを以下に示す。

• GROUP BY句とORDER BY句で異なるカラムを使用
• DISTINCTとORDER BY句を併用
• UNION (UNION ALLは一時テーブルを使用しない)
• 派生テーブル (FROM句のサブクエリ)

対策を以下に示す。

 # 問題: Using temporary
 EXPLAIN SELECT DISTINCT status FROM users ORDER BY created_at;
 # Extra: Using temporary; Using filesort
 
 # 対策1: DISTINCTをGROUP BYに書き換え、インデックスを活用
 CREATE INDEX idx_status_created ON users(status, created_at);
 EXPLAIN SELECT status FROM users GROUP BY status ORDER BY MIN(created_at);
 # Extra: Using index (一時テーブルが不要)
 
 # 対策2: サブクエリを結合に書き換え
 # 問題
 EXPLAIN
 SELECT * FROM users
    WHERE id IN (SELECT DISTINCT user_id FROM orders);
 # Extra: Using temporary
 
 # 改善
 EXPLAIN
 SELECT DISTINCT u.* FROM users u
    INNER JOIN orders o ON u.id = o.user_id;
 # (インデックスがあれば一時テーブルを回避できる場合がある)

インデックスが使用されないケース

インデックスが存在するにもかかわらず、使用されない場合がある。

主な原因を以下に示す。

原因	説明	対策
WHERE句で関数を使用	WHERE YEAR(created_at) = 2024 は、created_at のインデックスを使用できない。	WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31' に書き換える。
暗黙的な型変換	WHERE user_id = '100' (user_id がINT型の場合)	正しい型で条件を指定する。 WHERE user_id = 100
LIKE句の前方一致以外	WHERE name LIKE '%Smith' や WHERE name LIKE '%John%' はインデックスを使用できない。	WHERE name LIKE 'Smith%' (前方一致) に書き換える。
OR条件で片方のカラムにインデックスがない	WHERE status = 'active' OR role = 'admin' (role にインデックスがない場合)	両方のカラムにインデックスを作成するか、UNION句に書き換える。
複合インデックスの順序が一致しない	インデックスが idx_abc (a, b, c) の場合、WHERE b = 10 はインデックスを使用できない。	WHERE a = 1 AND b = 10 のように、先頭カラムから順に条件を指定する。

• 関数を使用している例と対策

   # 問題: 関数を使用しているためインデックスが使用されない
   EXPLAIN SELECT * FROM users WHERE YEAR(created_at) = 2024;
   # type: ALL
   # key: NULL
   
   # 対策: 関数を使わずに範囲条件に書き換え
   EXPLAIN SELECT * FROM users
      WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
   # type: range
   # key: idx_created_at
  

  
  

• OR条件の例と対策

   # 問題: OR条件で片方にインデックスがない
   EXPLAIN SELECT * FROM users WHERE status = 'active' OR role = 'admin';
   # type: ALL (インデックスが使用されない)
   
   # 対策1: 両方のカラムにインデックスを作成
   CREATE INDEX idx_status ON users(status);
   CREATE INDEX idx_role ON users(role);
   EXPLAIN SELECT * FROM users WHERE status = 'active' OR role = 'admin';
   # type: index_merge (インデックスマージが使用される)
   # Extra: Using union(idx_status,idx_role)
   
   # 対策2: UNION句に書き換え
   EXPLAIN
   SELECT * FROM users WHERE status = 'active'
   UNION
   SELECT * FROM users WHERE role = 'admin';
  

EXPLAIN結果に基づく最適化の流れ

EXPLAIN の結果を基に、クエリを最適化する一般的な流れを以下に示す。

1. EXPLAIN を実行してクエリの実行計画を確認
   • type、key、rows、Extra を重点的に確認。
2. ボトルネックを特定
   • フルテーブルスキャン (type: ALL)
   • ファイルソート (Extra: Using filesort)
   • 一時テーブル (Extra: Using temporary)
   • インデックス未使用 (key: NULL)
   • 大量の行検査 (rows: 大きな値)
3. インデックスの追加または最適化
   • WHERE句、JOIN句、ORDER BY句で使用するカラムにインデックスを作成。
   • 複合インデックスの順序を最適化。
   • カバリングインデックスを検討。
4. クエリの書き換え
   • 関数を削除または書き換え。
   • サブクエリをJOIN句に書き換え。
   • OR条件をUNION句に書き換え。
   • DISTINCT、GROUP BYの最適化。
5. テーブル統計の更新
   • ANALYZE TABLE文を実行して統計を最新に保つ。
   • ヒストグラム統計を収集 (MySQL 8.0以降)。
6. 再度 EXPLAIN を実行して改善を確認
   • type が改善されているか (ALL → ref / range)。
   • rows が減少しているか。
   • Extra から Using filesort / Using temporary が消えているか。
7. 必要に応じて EXPLAIN ANALYZE で実測値を確認
   • 推定値と実測値の差異を確認。
   • 実行時間を測定。

最適化の例を以下に示す。

 # Step 1: 最初のクエリ
 EXPLAIN
 SELECT u.name, COUNT(*) AS order_count
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active'
       AND o.order_date >= '2024-01-01'
    GROUP BY u.id
    ORDER BY order_count DESC;
 
 # 結果: type: ALL, key: NULL, Extra: Using temporary; Using filesort
 
 # Step 2: インデックスを追加
 CREATE INDEX idx_status ON users(status);
 CREATE INDEX idx_user_order_date ON orders(user_id, order_date);
 
 # Step 3: テーブル統計を更新
 ANALYZE TABLE users;
 ANALYZE TABLE orders;
 
 # Step 4: 再度EXPLAINを実行
 EXPLAIN
 SELECT u.name, COUNT(*) AS order_count
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active'
       AND o.order_date >= '2024-01-01'
    GROUP BY u.id
    ORDER BY order_count DESC;
 
 # 結果: type: ref/range, key: idx_status/idx_user_order_date
 # rowsが大幅に減少、Using temporaryは残るがパフォーマンスは改善
 
 # Step 5: EXPLAIN ANALYZEで実測
 EXPLAIN ANALYZE
 SELECT u.name, COUNT(*) AS order_count
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
    WHERE u.status = 'active'
       AND o.order_date >= '2024-01-01'
    GROUP BY u.id
    ORDER BY order_count DESC;
 
 # 実行時間が大幅に短縮されたことを確認

EXPLAIN を活用することにより、クエリのパフォーマンス問題を体系的に分析・解決できる。

MySQL - SELECTページのパフォーマンス最適化セクションも参照して、さらなる最適化を行うことを推奨する。