EXPLAIN ANALYZEでSQLパフォーマンスを改善｜インデックス最適化の実践ガイド

Posted at 2026-03-13

はじめにインデックスとは？？

インデックスは、DBの検索を速くするための「目次」です。
本の目次を見れば、知りたい内容がどのページにあるかすぐに分かりますよね。
データベースのインデックスも同じで、特定の値を持つ行がテーブルのどこにあるかを
素早く見つけられるようにします。

インデックスがない場合との違い

インデックスがない場合、データベースはテーブル全体を順番にスキャンします（フルテーブルスキャン）。
100万件のテーブルから1件を見つけるのに、最悪100万件すべてをチェックする必要があります。

一方、インデックスがある場合、該当する行に直接アクセスできるため、
検索速度が向上します。同じ100万件のテーブルでも、数ミリ秒で結果を返せます。

インデックスの仕組み

データベースのインデックスは、代表的な構造としてB-treeが使われます。
B-treeは、辞書の索引のように階層的に値を整理します。
検索の流れ（例：ID=500を探す）

ルートノード：検索の起点。「500はどの範囲にあるか」を判断
中間ノード：範囲をさらに絞り込む
リーフノード：最終的に「500」の位置を見つけ、実際のデータ行へのポインタを取得
この階層構造により、大量のデータでも少ない比較回数で目的の行を見つけられます。
また、リーフノードは値の順番に並んでいるため、範囲検索（BETWEEN, >, < など）も効率的です。

どのような場合にインデックスを設定する？

インデックスを設定するかどうかは、以下の観点で判断します。

1. クエリのパターン
以下の条件で頻繁に検索・結合・ソートされるカラムは、インデックスの候補になります：

WHERE 句で使われるカラム
- 例：WHERE user_id = 123 や WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31'
JOIN の結合キー
- 例：INNER JOIN users ON orders.user_id = users.id
ORDER BY で使われるカラム
- 例：ORDER BY created_at DESC

2. データ件数とアクセス頻度
件数だけでなく、クエリの実行頻度も考慮します：

データ件数	実行頻度	判断
数千〜1万件	低頻度	フルスキャンでも問題ないことが多い
数千〜1万件	高頻度	インデックス検討の余地あり
10万件超	-	条件次第でインデックス必要性が上がる
100万件超	-	主要クエリはほぼ必須で検討

3. 副作用とのバランス
インデックスには副作用もあるため、以下の点も考慮します：

INSERT/UPDATE/DELETE が多いテーブルでは、インデックス追加で書き込みが遅くなる可能性
読み取りが多く書き込みが少ないテーブルは、インデックス追加のメリットが大きい

対象クエリ（例）

explain select
    `orders`.`user_id`,
    `orders`.`id` as `order_id`,
    `products`.`product_name`,
    `orders`.`created_at`,
    `users`.`user_name`
from orders FORCE INDEX (orders_user_id_created_at_index)
         inner join `order_items`
                    on `orders`.`id` = `order_items`.`order_id`
                        and `orders`.`user_id` = `order_items`.`user_id`
                        and `order_items`.`item_type` = 'premium'
         inner join `users`
                    on `orders`.`user_id` = `users`.`id`
         inner join `products`
                    on `order_items`.`product_id` = `products`.`id`
                        and `products`.`category` = 'electronics'
where
    `orders`.`user_id` in (1, 2, 3, 4, 5)
  and `orders`.`created_at` between '2025-12-01 00:00:00' and '2025-12-31 23:59:59';

実測例: time = 5.38ms

EXPLAIN結果の見方

このクエリのEXPLAIN結果は以下の通りです：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	orders	range	orders_user_id_created_at_index	orders_user_id_created_at_index	10		5300	100	Using where; Using index
1	SIMPLE	users	eq_ref	PRIMARY	PRIMARY	4	sample_db.orders.user_id	1	100
1	SIMPLE	order_items	eq_ref	PRIMARY	PRIMARY	12	sample_db.orders.user_id,sample_db.orders.id,const	1	100	Using index
1	SIMPLE	products	eq_ref	PRIMARY	PRIMARY	4	sample_db.order_items.product_id	1	10	Using where

重要なカラムと見方

1. type（最重要）
どの方法でテーブルにアクセスしているかを示します。性能に大きく影響します。

性能の順序（良い順）
eq_ref > ref > range > ALL

type	意味	性能	このクエリでの例
`eq_ref`	主キーまたはユニークキーでの等価検索	最速	`users`, `order_items`, `products`
`range`	範囲検索（`BETWEEN`, `IN`, `>` など）	良好	`orders`テーブル
`ALL`	全件走査（フルスキャン）	最悪（要注意）	-

見方のポイント

ALLが出ていたら要注意（インデックスが使われていない可能性）
eq_refやref、rangeならインデックスが有効活用されている

2. key（最重要）
実際に使われたインデックス名です。NULLの場合はインデックスが使われていません。

テーブル	key	意味
orders	`orders_user_id_created_at_index`	指定したインデックスが使われている
users	`PRIMARY`	主キーを使用
order_items	`PRIMARY`	主キーを使用
products	`PRIMARY`	主キーを使用

見方のポイント

keyがNULLなら、インデックスが使われていない（typeがALLの可能性大）
想定したインデックス名が表示されているか確認

3. rows（重要）
読み取る見込み件数です。大きいほど処理が重くなります。

テーブル	rows	評価
orders	5300	やや多い（`user_id IN (...)`と`created_at BETWEEN`の条件による）
users	1	最適
order_items	1	最適
products	1	最適

見方のポイント

rowsが数千〜数万件を超える場合は、インデックスの見直しを検討
JOINの場合は、結合される側のrowsが小さいほど効率的

4. Extra（参考情報）
追加の処理情報です。typeやkeyほど直接的ではありませんが、最適化のヒントになります。

Extra	意味	評価	このクエリでの例
`Using index`	インデックスだけで処理（カバリングインデックス）	最適	`orders`, `order_items`
`Using where`	WHERE条件でフィルタリング	正常	`orders`, `products`
`Using join buffer`	JOINバッファを使用（インデックスが不十分な可能性）	注意が必要	-

見方のポイント

Using indexが出ていれば良い：テーブルにアクセスせず、インデックスだけでデータを取得できている（カバリングインデックス）
- 例：ordersテーブルでUsing indexが出ている = user_idとcreated_atがインデックスに含まれているため、テーブルを読まずに済んでいる
Using whereは正常：WHERE条件でフィルタリングしているだけなので、問題なし
Using join bufferが出ていたら要確認：JOINのインデックスが不十分で、メモリ上で結合処理をしている可能性がある

重要度の比較

typeやkey：最重要（まずここを確認）
rows：重要（処理量の目安）
Extra：参考情報（最適化のヒント）

5. その他のカラム（参考程度）

possible_keys：使用可能なインデックスの候補（実際に使われたとは限らない）
key_len：使用したインデックスの長さ
ref：結合に使われたカラムや定数
filtered：WHERE条件でフィルタリングされた割合（%）

EXPLAIN ANALYZE について

EXPLAINは実行計画を見るだけですが、EXPLAIN ANALYZEは実際にクエリを実行して、実測値を取得できます。

EXPLAIN と EXPLAIN ANALYZE の違い

項目	EXPLAIN	EXPLAIN ANALYZE
クエリ実行	実行しない（計画のみ）	実際に実行する
実行時間	見積もりのみ	実際の実行時間を表示
処理行数	見積もり（`rows`）	実際の処理行数を表示
使用場面	実行計画の確認	実測値での検証

使い方

EXPLAIN ANALYZE
SELECT ...;

出力例（実際の結果）

-> Nested loop inner join  (cost=12500 rows=850) (actual time=5.2..38.5 rows=850 loops=1)
    -> Nested loop inner join  (cost=3200 rows=4250) (actual time=2.1..15.2 rows=4250 loops=1)
        -> Nested loop inner join  (cost=1800 rows=5300) (actual time=0.8..8.5 rows=5300 loops=1)
            -> Nested loop inner join  (cost=1200 rows=5300) (actual time=0.16..5.2 rows=5300 loops=1)
                -> Filter: ((orders.user_id in (1,2,3,4,5)) and (orders.created_at between '2025-12-01 00:00:00' and '2025-12-31 23:59:59'))  
                    (cost=1065 rows=5300) (actual time=0.147..3.91 rows=5300 loops=1)
                    -> Covering index range scan on orders using orders_user_id_created_at_index  
                        (cost=1065 rows=5300) (actual time=0.141..2.46 rows=5300 loops=1)
                -> Single-row index lookup on users using PRIMARY (id=orders.user_id)  
                    (cost=0.25 rows=1) (actual time=275e-6..309e-6 rows=1 loops=5300)
            -> Single-row covering index lookup on order_items using PRIMARY (order_id=orders.id, user_id=orders.user_id, item_type='premium')  
                (cost=0.25 rows=1) (actual time=0.0005..0.0006 rows=0.8 loops=5300)
        -> Filter: (products.category = 'electronics')  
            (cost=0.25 rows=0.2) (actual time=0.0012..0.0012 rows=0.8 loops=4250)
            -> Single-row index lookup on products using PRIMARY (id=order_items.product_id)  
                (cost=0.25 rows=1) (actual time=0.0010..0.0011 rows=0.8 loops=4250)

基本的な読み方

EXPLAIN ANALYZEの出力は階層構造になっており、インデント（字下げ）で実行順序を示します。
最もインデントが深い（右側）から実行が始まり、上へ向かって処理が進みます。

1. まず見るべき3つの数値

各処理には以下の情報が表示されます：

(actual time=0.141..2.46 rows=5300 loops=1)
     ↑            ↑        ↑        ↑
     │            │        │        └─ この処理が何回実行されたか
     │            │        └────────── 1回のループで処理された行数
     │            └────────────────── 全行を取得するまでの時間（ミリ秒）
     └────────────────────────────── 最初の行を取得するまでの時間（ミリ秒）

`actual time`の2つの値の違い

actual time=0.141..2.46は、最初の行取得時間..全行取得時間を表します。

1つ目の値（0.141）：最初の1行を取得するまでの時間（ミリ秒）
- クエリが結果を返し始めるまでの時間
- ユーザーが最初の結果を見るまでの時間
- 応答性の指標（小さいほど良い）
2つ目の値（2.46）：すべての行を取得し終わるまでの時間（ミリ秒）
- クエリが完全に終了するまでの時間
- 全データを処理し終わるまでの時間
- 全体の処理時間（小さいほど良い）

2. `rows`と`loops`の関係

rows × loops = 実際に処理された総行数

例：

rows=5300 loops=1 → 5300 × 1 = 5300行処理
rows=1 loops=5300 → 1 × 5300 = 5300行処理（5300回ループして、毎回1行ずつ取得）
rows=3457 loops=174 → 3457 × 174 = 約601,518行処理（174回ループして、毎回平均3457行ずつ取得）

実際の大量ループの例

実際のクエリでは、以下のような大量ループが発生することがあります：

-> Covering index lookup on content_user using PRIMARY
    (actual time=0.67..3.72 rows=3457 loops=174)

これは：

174回ループして、毎回平均3457行ずつ取得
総行数：3457 × 174 = 約601,518行

さらに：

-> Single-row index lookup on active_users using PRIMARY
    (actual time=0.00266..0.0027 rows=0.993 loops=601527)

これは：

601,527回ループして、毎回平均0.993行（ほぼ1行）ずつ取得
総行数：0.993 × 601,527 = 約597,000行

`loops`が大きい場合の注意点

loopsの値が大きいほど、その処理が何度も繰り返されているため：

ボトルネックになりやすい
インデックスが正しく使われているか確認が必要
JOINの条件やインデックスの見直しを検討

3. `loops`と`rows`を減らす方法

loopsやrowsが大きいと処理が重くなるため、以下の方法で減らすことができます。

rowsを減らす方法

rowsは1回のループで処理される行数です。減らすには：

WHERE条件で絞り込む
- より具体的な条件を追加する
- 例：user_id IN (1,2,3,4,5) → user_id = 1（より絞り込む）
適切なインデックスを設定する
- WHERE句やJOINの条件にインデックスがあるか確認
- 複合インデックスを検討（例：(user_id, created_at)）
不要なJOINを減らす
- 必要のないテーブルとのJOINを削除
- 必要なカラムだけをSELECTする

loopsを減らす方法

loopsは処理が何回実行されるかです。減らすには：

JOINの順序を最適化する
- 行数が少ないテーブルからJOINする
- 例：1000件のテーブル → 100万件のテーブルの順でJOIN
インデックスを適切に設定する
- JOINの結合キーにインデックスがあるか確認
- 主キーやユニークキーでのJOINはloopsが少なくなる
クエリの書き方を変える
- INの代わりにEXISTSを使う（場合によっては効果的）
- サブクエリを最適化する
データの絞り込みを早い段階で行う
- WHERE条件をJOINの前に適用できるようにする
- 例：WHERE orders.user_id IN (...)を先に適用してからJOIN

実際の改善例

改善前：

-> Single-row index lookup on products using PRIMARY
    (rows=1 loops=4250)

→ 4250回ループしている

改善後（インデックス追加やJOIN順序の最適化）：

-> Single-row index lookup on products using PRIMARY
    (rows=1 loops=850)

→ 850回ループに減らせた（約5分の1）

4. 実行順序の読み方（実際の例）

実際の出力を見ると、以下の順序で実行されています：

1. Covering index range scan on orders
   → 5300件を取得（loops=1）
   → orders_user_id_created_at_indexを使って、user_id IN (1,2,3,4,5)とcreated_at BETWEENの条件に合う行を検索

2. Single-row index lookup on users
   → 5300回実行（loops=5300）
   → 各orders行に対して、usersテーブルを1回ずつ検索（主キーで検索）

3. Single-row covering index lookup on order_items
   → 5300回実行（loops=5300）
   → 各orders行に対して、order_itemsテーブルを1回ずつ検索
   → item_type='premium'の条件でフィルタリング

4. Single-row index lookup on products
   → 4250回実行（loops=4250）
   → 各order_items行に対して、productsテーブルを1回ずつ検索
   → category='electronics'の条件でフィルタリング

5. ボトルネックの見つけ方

loopsの値が大きい処理がボトルネックになりやすいです。

この例では：

loops=5300：usersとorder_itemsの検索が5300回実行されている
loops=4250：productsの検索が4250回実行されている
これらの処理が繰り返し実行されているため、インデックスが正しく使われているか確認が必要

6. 見積もりと実測の比較

この例では：

rows=5300（見積もり）vs rows=5300（実測）
見積もりと実測が一致している = EXPLAINの見積もりが正確
大きく異なる場合は、統計情報の更新（ANALYZE TABLE）を検討

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

EXPLAIN ANALYZEでSQLパフォーマンスを改善｜インデックス最適化の実践ガイド

はじめにインデックスとは？？

インデックスがない場合との違い

インデックスの仕組み

どのような場合にインデックスを設定する？

対象クエリ（例）

EXPLAIN結果の見方

重要なカラムと見方

EXPLAIN ANALYZE について

基本的な読み方

1. まず見るべき3つの数値

actual timeの2つの値の違い

2. rowsとloopsの関係

実際の大量ループの例

loopsが大きい場合の注意点

3. loopsとrowsを減らす方法

4. 実行順序の読み方（実際の例）

5. ボトルネックの見つけ方

6. 見積もりと実測の比較

`actual time`の2つの値の違い

2. `rows`と`loops`の関係

`loops`が大きい場合の注意点

3. `loops`と`rows`を減らす方法