遅いクエリを速くする！MySQL EXPLAIN ANALYZE 入門

データベースアプリケーション開発において、クエリのパフォーマンスはアプリケーション全体の速度とユーザー体験に直結する非常に重要な要素です。遅いクエリは、レスポンスタイムの悪化、サーバーリソースの枯渇、最悪の場合サービス停止といった深刻な問題を引き起こす可能性があります。

本記事では、MySQLで遅いクエリの原因を特定し、最適化するための強力なツールであるEXPLAINと、さらに詳細な実行情報を提供するEXPLAIN ANALYZEの使い方と読み方を徹底的に解説します。これらのツールを使いこなすことで、非効率なクエリパターンを見つけ出し、より高速で効率的なデータベース操作を実現できるようになります。

1. なぜクエリ最適化は重要なのか？

あなたのWebサイトやアプリケーションが「遅い」と感じられたことはありませんか？その原因の一つに、データベースへの問い合わせ、すなわちクエリが遅いことが挙げられます。

遅いクエリが引き起こす問題:

ユーザー体験の悪化: ページの読み込みが遅延し、ユーザーは不満を感じ離脱してしまう可能性があります。
サーバーリソースの浪費: 遅いクエリはCPU、メモリ、ディスクI/Oなどのリソースを長時間占有し、他の処理の妨げとなります。
スケーラビリティの問題: アプリケーションへのアクセスが増えるにつれて、遅いクエリはさらにシステム全体のボトルネックとなり、スケールアップやスケールアウトの効果を打ち消してしまうことがあります。
開発効率の低下: クエリのパフォーマンス問題に追われることで、新しい機能開発や改善に時間を割けなくなります。

これらの問題を解決し、ユーザーに快適な体験を提供し、安定したサービスを運用するためには、クエリの最適化が不可欠です。

2. 遅いクエリを特定する第一歩

クエリの最適化を始めるには、まず「どのクエリが遅いのか」を知る必要があります。MySQLには、遅いクエリを特定するためのいくつかの方法があります。

スロークエリログ (Slow Query Log): 設定したしきい値（例えば実行時間が1秒を超える）よりも遅く実行されたクエリをログファイルに記録する機能です。これは、本番環境で実際に遅延を引き起こしているクエリをリストアップするのに非常に有効です。ログファイルを確認することで、実行に時間のかかっているクエリとその実行回数などを把握できます。
SHOW PROCESSLIST: 現在MySQLサーバーで実行されているすべてのスレッド（クライアント接続とそれに関連する処理）を表示します。State列やTime列を見ることで、長時間実行されているクエリや特定の状態（例: Locked, Sorting resultなど）で停止しているクエリを見つけることができます。特にリアルタイムでの問題発生時に原因を調査するのに役立ちます。
パフォーマンススキーマ (Performance Schema): MySQLサーバーの低レベルなアクティビティに関する詳細な情報（クエリ実行時間、I/O待ち時間、ロック情報など）を収集・提供する機能です。スロークエリログよりもはるかに詳細な分析が可能ですが、設定やデータの解釈にはある程度の学習が必要です。

これらの方法で遅いクエリのSQL文を特定したら、いよいよそのクエリがなぜ遅いのか、どのように改善できるのかを深く掘り下げていきます。そこで主役となるのが、EXPLAINおよびEXPLAIN ANALYZEコマンドです。

3. EXPLAINコマンドの基礎：クエリ実行計画を知る

EXPLAINコマンドは、MySQLのオプティマイザが特定のSQLクエリをどのように実行しようとするか、その「実行計画」を表示します。実際にクエリを実行するわけではないため、データの内容によって実行計画が変わる可能性があることに注意が必要ですが、クエリの効率性を評価し、ボトルネックを推測するための第一歩として非常に強力です。

3.1 EXPLAINの使い方

使い方は非常にシンプルです。分析したいSELECT, INSERT, UPDATE, DELETE, REPLACE文の前にEXPLAINキーワードを付け加えるだけです。

sql EXPLAIN SELECT column1, column2 FROM your_table WHERE condition; EXPLAIN UPDATE your_table SET column1 = value WHERE condition;

通常、最も頻繁にパフォーマンス問題が発生するのはSELECTクエリであるため、ここでは主にSELECTクエリに対するEXPLAINの利用に焦点を当てます。

3.2 EXPLAINの出力形式と各列の意味

EXPLAINを実行すると、通常はテーブル形式でクエリの実行計画が表示されます（MySQL 8.0.18以降でEXPLAIN ANALYZEを使うとツリー形式になりますが、EXPLAIN単体はデフォルトでテーブル形式です）。表示される各列は、オプティマイザが考慮した実行手順のステップに関する情報を含んでいます。

Column	Description
`id`	セレクト句の識別子。複数の`SELECT`文（サブクエリなど）がある場合に、そのグループを示す。
`select_type`	セレクト句のタイプ（SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNIONなど）。
`table`	操作対象となるテーブルの名前。
`partitions`	パーティション化されたテーブルの場合、アクセスされるパーティション。
`type`	テーブルへのアクセス方法（非常に重要！）。
`possible_keys`	オプティマイザがこのクエリで利用できる可能性があるインデックスのリスト。
`key`	オプティマイザが実際に選択したインデックス。`NULL`の場合はインデックス未使用。
`key_len`	選択されたインデックスの長さ（バイト数）。複合インデックスの場合に役立つ。
`ref`	インデックスを使用して行を絞り込むために使用される列または定数。
`rows`	オプティマイザがクエリ実行のために走査すると予測した行数（推定値）。
`filtered`	WHERE句の条件によって、テーブルから読み込まれた行のうち、最終的に結果セットに含まれると予測される割合（推定値、パーセント）。
`Extra`	オプティマイザがクエリ実行に関して行っている追加情報（非常に重要！）。

これらの列の中でも、特にtype, key, rows, filtered, Extraはクエリの効率性を評価する上で非常に重要です。それぞれの意味を詳しく見ていきましょう。

`id`

複数のSELECTが含まれるクエリ（サブクエリ、UNIONなど）の場合、それぞれのSELECTに対して一意な識別子が割り当てられます。idが同じ行は、同じ実行ステージに属していると考えられます。idが大きいものから順に実行されるのが一般的ですが、UNIONの場合は同じidを持つグループが順に処理されるなど、select_typeによって解釈が変わります。

`select_type`

クエリ内の各SELECT句のタイプを示します。主なタイプは以下の通りです。

SIMPLE: サブクエリやUNIONを含まない、シンプルなSELECTクエリ。
PRIMARY: 最も外側のSELECTクエリ（UNIONの場合を除く）。
SUBQUERY: FROM句以外の場所で使用されるサブクエリの最初のSELECT。
DEPENDENT SUBQUERY: 外部クエリの値に依存するサブクエリ。通常、効率が悪いです。
DERIVED: FROM句のサブクエリ（派生テーブル）。一時テーブルが作成されることが多いです。
UNION: UNION内の2番目以降のSELECT。
DEPENDENT UNION: 外部クエリの値に依存するUNION内の2番目以降のSELECT。
UNION RESULT: UNIONの結果を格納するための一時テーブル。

`table`

このステップでアクセスされるテーブルの名前。派生テーブルや一時テーブルの場合、<derivedN>（Nはid）や<unionM,N>のような名前になります。

`type` (アクセスタイプ)

これは最も重要な列の一つです。 テーブルから行を検索する方法を示し、効率の良さを直接的に表します。typeの値は、効率の良い順におおよそ以下のようになります。

system: テーブルが1行しかない（または空の）特殊なconst。
const: クエリを定数として扱える。テーブルに最大1行だけマッチする場合。例えば、PRIMARY KEYまたはUNIQUE INDEXで定数値で検索する場合。非常に高速。
eq_ref: JOINにおいて、前のテーブルの各行に対して、現在のテーブルから正確に1行だけを読み出す。JOIN条件にPRIMARY KEYまたはUNIQUE INDEXが使用されている場合。非常に効率の良いJOIN。
ref: JOINにおいて、前のテーブルの各行に対して、現在のテーブルから複数行を読み出す可能性がある。PRIMARY KEYやUNIQUE INDEXではないが、インデックスの左端プレフィックス（Leftmost Prefix）を使用して検索する場合。eq_refよりは劣るが効率的。
ref_or_null: refと同様だが、さらにNULL値を含む行も検索する場合。
index_merge: 複数のインデックスを使用し、その結果をマージして行を取得する場合。特定のケースで効率的になることがある。
unique_subquery: IN述語のサブクエリがPRIMARY KEYまたはUNIQUE INDEXを使用して解決できる場合。eq_refに似ている。
index_first/index_last: インデックス全体をスキャンするが、順番が決まっているため高速な場合。
range: インデックスを使用して、指定された範囲の行を検索する場合（<, >, <=, >=, BETWEEN, INなど）。インデックスの一部だけをスキャンするため、ALLやindexよりはるかに効率的。
index_scan: インデックス全体をフルスキャンする場合。データファイル全体をスキャンするALLよりは速い可能性があります（インデックスがデータより小さい場合や、必要な列がインデックスに含まれている場合）。
ALL: 最悪のアクセスタイプです。 テーブル全体をフルスキャンして、条件に合う行を探します。テーブルのサイズが大きくなるにつれて、パフォーマンスは著しく劣化します。大規模なテーブルでこのタイプが出ている場合は、インデックスの設計やクエリの見直しが強く推奨されます。

理想的なtypeはsystem, const, eq_ref, ref, rangeあたりです。ALLが出ている場合は、ほぼ間違いなく改善が必要です。

`possible_keys`

このクエリのWHERE句やJOIN句などを満たすために、オプティマイザが利用できると判断したインデックスの候補リストです。これはあくまで「候補」であり、実際に使用されるインデックスはkey列に表示されます。

`key`

オプティマイザが最終的にクエリ実行のために選択したインデックスの名前です。この列がNULLの場合、オプティマイザはインデックスを使用しないことを選択しました。これは、テーブルが非常に小さい、またはインデックスを使用しても効率が良くないと判断された場合などに起こります。しかし、大きなテーブルでkeyがNULLかつtypeがALLの場合は、適切なインデックスが存在しないか、またはインデックスが効果的に使用できない書き方になっている可能性が高いです。

`key_len`

選択されたインデックスの長さ（バイト単位）を示します。複合インデックスの場合、どのインデックスカラムまでが使用されているかを示すのに役立ちます。例えば、col1(INT), col2(VARCHAR(10))という複合インデックスがあり、WHERE col1 = 123という条件の場合、key_lenはcol1の長さになります。WHERE col1 = 123 AND col2 = 'abc'の場合は、col1とcol2の両方の長さを合わせた値になります。key_lenが小さいほど、インデックスを使って絞り込める範囲が広い（多くの行がマッチする）ことを意味し、逆にkey_lenが大きいほど、より多くのインデックスキーが使われ、結果セットがより絞り込まれることを示唆します。

`ref`

インデックスを使用する際に、どの列や定数と比較して行を検索したかを示します。例えば、JOIN句でON table1.col1 = table2.col2となっている場合、table2の行を検索する際にrefにtable1.col1が表示されることがあります。また、WHERE indexed_col = 'some_value'のような条件の場合、refにconstが表示されます。

`rows`

これは非常に重要な列です。 オプティマイザが、このテーブルへのアクセスによって読み込まれると「予測」した行数です。これはあくまで予測値であり、実際の行数とは異なる場合がありますが、typeがALLでこの値が大きい場合は、大量の行を走査していることを示唆し、パフォーマンス問題の兆候となります。typeがconstやeq_refの場合は、この値は通常1になります。

`filtered`

これも非常に重要な列です。 rows列で読み込まれた行のうち、WHERE句などの条件によって最終的に結果セットに含まれると「予測」される行の割合（パーセント）です。例えば、rowsが1000でfilteredが10.00%の場合、オプティマイザは1000行を読み込むが、そのうち100行だけが条件を満たすと予測しています。filteredの値が低い場合（特にrowsが大きいとき）、大量の行を読み込んでいるにも関わらず、その大部分が後でフィルタリングされて捨てられていることを意味します。これは、使用しているインデックスが条件を十分に絞り込めていない、あるいはインデックスが使用されていない可能性を示唆します。

`Extra`

これも非常に重要な列で、クエリの実行に関する追加情報や警告が含まれます。 ここに表示される値は、クエリの効率性を評価する上で決定的な情報となることがあります。代表的な値をいくつか挙げます。

Using index: クエリに必要なすべての情報がインデックス自体に含まれている（カバリングインデックス）。テーブルのデータファイルを読み込む必要がないため、非常に高速です。理想的な状態の一つです。
Using where: WHERE句によって行をフィルタリングしていることを示します。これは必ずしも悪いことではありませんが、typeがALLやindexで、かつUsing whereが出ている場合は、大量の行を読み込んでからフィルタリングしている可能性が高いです。
Using temporary: クエリの実行中に一時テーブルを作成していることを示します。GROUP BYやORDER BYでインデックスが使用できない場合によく発生します。一時テーブルの作成と利用は、特にデータ量が多い場合にパフォーマンスのボトルネックになることがあります。
Using filesort: MySQLが結果セットをソートするために、インデックスを使用せずに、メモリ上またはディスク上でソートを実行していることを示します。ORDER BY句でインデックスが使用できない場合に発生します。データ量が多い場合、ディスクアクセスが発生し、パフォーマンスが著しく低下する可能性があります。Using temporaryとUsing filesortは、可能であれば避けるべき操作です。
Using join buffer (Block Nested Loop): JOINを実行する際に、JOINバッファを使用して内側テーブルの行をバッファリングし、結合効率を高めようとしていることを示します。インデックスが使用できないJOIN（typeがALLなど）で発生します。これはオプティマイザによる試みですが、効率的なインデックスJOIN (eq_ref, ref) に比べると一般的に遅いです。
Using index condition: インデックスを使用して行を絞り込む前に、インデックスキーに含まれるカラム以外の条件（またはインデックスキーの非プレフィックス部分）も評価している場合。MySQL 5.6で導入された Index Condition Pushdown (ICP) 最適化が働いていることを示します。不要な行を早期にスキップできるため、Using where + インデックスフルスキャンなどより効率的です。
Using materialized view: 派生テーブルやサブクエリがマテリアライズド（一時テーブル化）されていることを示します。

Extra列にUsing temporaryやUsing filesortが出ている場合、あるいはUsing whereが出ているにも関わらずtypeがALLやindexである場合は、パフォーマンス改善の余地が大きいと考えられます。

3.3 EXPLAINを使ったクエリ分析例

簡単な例を使ってEXPLAINの出力を読んでみましょう。

まず、サンプルテーブルを作成し、いくつかのデータを挿入します。

“`sql
— テーブル作成
CREATE TABLE users (
user_id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
registration_date DATE,
INDEX idx_registration_date (registration_date)
);

CREATE TABLE orders (
order_id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
order_date DATE,
amount DECIMAL(10, 2),
FOREIGN KEY (user_id) REFERENCES users(user_id),
INDEX idx_user_id (user_id),
INDEX idx_order_date (order_date)
);

— ダミーデータの挿入 (量によって結果は変わりますが、ここでは概念説明のため)
— 簡単にするため、手動またはスクリプトで適当なデータを挿入してください。
— 例:
INSERT INTO users (username, registration_date) VALUES (‘user1’, ‘2022-01-01’), (‘user2’, ‘2022-01-15’), …;
INSERT INTO orders (user_id, order_date, amount) VALUES (1, ‘2023-01-10’, 100.00), (1, ‘2023-01-20’, 250.00), (2, ‘2023-01-12’, 50.00), …;
“`

例1: シンプルなWHERE句

「2023年1月以降に登録されたユーザー」を検索するクエリ。

sql EXPLAIN SELECT * FROM users WHERE registration_date >= '2023-01-01';

usersテーブルにregistration_dateに対するインデックスがない場合:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	ALL	NULL	NULL	NULL	NULL	10000	10.00	Using where

（rowsはテーブルサイズによって変動、filteredは条件にマッチする行の割合で変動）

分析:

type: ALL – テーブル全体をフルスキャンしています。効率が悪いです。
possible_keys: NULL – 利用できるインデックスがありません。
key: NULL – インデックスが使用されていません。
rows: 10000 – オプティマイザはテーブルに10000行あると推定し、その全てを読み込むと予測しています。
filtered: 10.00% – 読み込んだ10000行のうち、10%（1000行）だけが条件にマッチすると予測しています。
Extra: Using where – WHERE句の条件は、テーブルから読み込んだ後に適用されています。

このEXPLAIN結果は、パフォーマンスが悪いことを強く示唆しています。テーブルフルスキャンは、データ量が増えるにつれて遅延の原因となります。

usersテーブルにidx_registration_dateインデックスがある場合:

sql EXPLAIN SELECT * FROM users WHERE registration_date >= '2023-01-01';

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	range	idx_registration_date	idx_registration_date	4	NULL	1000	100.00	Using index condition

分析:

type: range – インデックスを使って範囲検索を行っています。ALLよりはるかに効率的です。
possible_keys: idx_registration_date – オプティマイザはこのインデックスが使えると判断しました。
key: idx_registration_date – 実際にこのインデックスが使用されています。
rows: 1000 – オプティマイザは、インデックスを使って条件にマッチする可能性のある約1000行だけを読み込めばよいと予測しています。
filtered: 100.00% – Using index conditionが出ている場合、filteredはそのインデックスを使って読み込まれた行のうち、ICPによってフィルタリングされた後の割合を示します（または、インデックスレンジスキャンで取得した行全てがWHERE句を満たすと予測した場合）。この例では、インデックスレンジスキャンで取得した行の全てが最終結果に含まれると予測されています。
Extra: Using index condition – インデックスの一部を使って効率的に条件を評価しています。

インデックスを追加したことで、typeがALLからrangeに改善し、rows（予測読み込み行数）が大幅に減少しました。これはクエリのパフォーマンスが向上したことを示唆しています。

例2: JOINクエリ

「2023年1月以降の注文とそのユーザー名」を取得するクエリ。

sql EXPLAIN SELECT u.username, o.order_date, o.amount FROM users u JOIN orders o ON u.user_id = o.user_id WHERE o.order_date >= '2023-01-01';

分析 (テーブル構造とインデックスは上記の例と同様と仮定):

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	o	NULL	range	idx_user_id,idx_order_date	idx_order_date	4	NULL	500	100.00	Using where
1	SIMPLE	u	NULL	eq_ref	PRIMARY	PRIMARY	4	orders.user_id	1	100.00	Using index

分析:

idが両方1なので、同じ実行ステージです。オプティマイザはまずordersテーブルを処理し、その後usersテーブルを処理する計画を立てています。
最初の行 (table: o):
- type: range – ordersテーブルをidx_order_dateインデックスを使って範囲検索しています（WHERE o.order_date >= '2023-01-01'のため）。
- key: idx_order_date – order_dateのインデックスが使用されています。
- rows: 500 – 2023年1月以降の注文が約500件あると予測されています。
- Extra: Using where – WHERE句の条件が適用されています。
2番目の行 (table: u):
- type: eq_ref – usersテーブルへのアクセスは非常に効率的です。
- key: PRIMARY – usersテーブルの主キー（user_id）が使われています。
- ref: orders.user_id – ordersテーブルから取得したuser_idを使って、usersテーブルの対応する行を主キーで検索しています。
- rows: 1 – eq_refタイプなので、ordersテーブルの各行に対してusersテーブルからは1行だけが読み込まれると予測されています。
- Extra: Using index – usersテーブルへのアクセスに必要な情報がインデックス（主キー）に含まれているか、またはインデックスを使って効率的にアクセスしています。

このEXPLAIN結果は比較的良好です。ordersテーブルはrangeスキャンで絞り込まれ、usersテーブルへのJOINは効率の良いeq_refで行われています。

もしordersテーブルにidx_order_dateインデックスがなかったら？

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	10000	10.00	Using where
1	SIMPLE	u	NULL	eq_ref	PRIMARY	PRIMARY	4	orders.user_id	1	100.00	Using index

分析:

ordersテーブルへのアクセスがALLになっています (type: ALL)。WHERE o.order_date >= '2023-01-01'の条件を満たすために、ordersテーブル全体をスキャンしています。
rows: 10000 – ordersテーブルが10000行あると推定し、全てを読み込んでいます。
filtered: 10.00% – 読み込んだ10000行のうち10%だけがWHERE句を満たすと予測しています。
Extra: Using where – フルテーブルスキャン後にフィルタリングが行われています。

このように、インデックスの有無がクエリの実行計画に大きな影響を与えることがわかります。EXPLAINは、インデックスが適切に使用されているか、非効率な操作（フルスキャン、一時テーブル、ファイルソートなど）が発生していないかを確認するための強力なツールです。

しかし、EXPLAINの出力するrowsやfilteredはあくまでオプティマイザの「予測値」です。実際のクエリ実行でどのステップにどれだけ時間がかかったか、実際の行数はどうだったか、といった「実行時」の情報はEXPLAINだけではわかりません。ここで登場するのがEXPLAIN ANALYZEです。

4. EXPLAIN ANALYZE：実際の実行時間を計測する

EXPLAINがクエリの「計画」を示すのに対し、EXPLAIN ANALYZEはクエリを実際に実行し、各実行ステップ（ノード）にかかった実際の時間、処理された行数、ループ回数などの「実行時」の統計情報を収集・表示します。これにより、オプティマイザの予測と実際との乖離を見つけたり、真のパフォーマンスボトルネックがどこにあるのかをより正確に特定したりすることが可能になります。

4.1 EXPLAIN ANALYZEの使い方

使い方はEXPLAINと似ています。分析したいSELECT文の前にEXPLAIN ANALYZEキーワードを付け加えます。

sql EXPLAIN ANALYZE SELECT column1, column2 FROM your_table WHERE condition;

注意点: EXPLAIN ANALYZEはクエリを実際に実行します。したがって、データの書き込み（INSERT, UPDATE, DELETE, REPLACE）を伴うクエリに対してEXPLAIN ANALYZEを使用すると、実際にデータが変更されてしまいます。本番環境で書き込みクエリや、大量のデータに対する読み込みクエリをEXPLAIN ANALYZEで実行する際には、システムへの影響（負荷やデータ変更）に十分注意が必要です。可能な限り、本番環境に近い開発環境やステージング環境で試すことを強く推奨します。

4.2 EXPLAIN ANALYZEの出力形式と読み方

EXPLAIN ANALYZEの出力形式は、MySQL 8.0.18以降で大きく変更され、従来のテーブル形式ではなく、ツリー形式で表示されるようになりました。このツリーは、クエリの実行計画におけるオペレーション（ノード）の階層構造を表しています。最も外側のオペレーションがツリーのルートとなり、その内側（子ノード）がインデントされて表示されます。

各ノードの行には、そのオペレーションに関する詳細な統計情報が表示されます。代表的な情報を挙げます。

-> Operation_Name: Operation_Info: ノードのタイプと簡単な説明。矢印 (->) は、データがそのノードを通過する方向を示唆します。
(actual time=[start..end] rows=N loops=M VmPeak=P VmAvg=A): 実行時統計情報。
- actual time=[start..end]: このノードで処理が開始されてからの経過時間（ミリ秒）で、そのノードが最初の行を生成するまでの時間 (start) と、最後の行を処理し終えるまでの時間 (end) が示されます。endの値が大きいノードが、そのオペレーション全体にかかった時間を示しており、ボトルネックの可能性が高いです。
- rows=N: このノードが生成（または処理）した実際の行数です。EXPLAINのrows（予測値）と比較することで、オプティマイザの予測がどれだけ正確だったかを確認できます。
- loops=M: このノードのオペレーションが実行された回数です。JOINの内部ループなどで、同じオペレーションが複数回繰り返される場合に1より大きな値になります。actual timeをloopsで割ることで、1回のループあたりの時間を推測できます。
- VmPeak=P, VmAvg=A: （MySQL 8.0.19以降）各ループにおけるメモリ使用量のピーク値（VmPeak）と平均値（VmAvg）をバイト単位で示します。これはメモリ関連のボトルネック（特にUsing temporaryやUsing filesortが疑われる場合）を調査するのに役立ちます。

ツリー構造は、データフローや処理のパイプラインを表していると理解すると分かりやすいです。下位のノード（よりインデントされている）が先にデータを生成し、それが上位のノードに渡されて処理される、という流れです。最も時間がかかっているノード（actual timeのendが大きい、またはstartとendの差が大きい）が、クエリのボトルネックである可能性が高いです。

4.3 EXPLAIN ANALYZEを使った詳細分析例

先ほどのJOINクエリを例に、EXPLAIN ANALYZEの出力を見てみましょう。

sql EXPLAIN ANALYZE SELECT u.username, o.order_date, o.amount FROM users u JOIN orders o ON u.user_id = o.user_id WHERE o.order_date >= '2023-01-01';

ordersテーブルにidx_order_dateインデックスがある場合の例:

-> Inner hash join (actual time=0.200..0.800 rows=500 loops=1) -> Table scan on u (actual time=0.050..0.300 rows=1000 loops=1) -> Hash -> Index scan on o using idx_order_date (actual time=0.100..0.500 rows=500 loops=1) -> Filter: (o.order_date >= '2023-01-01') (actual time=0.100..0.450 rows=500 loops=1)

（実際の出力はより詳細で複雑になる場合がありますが、概念を理解するための簡略化された例です。VmPeak, VmAvgなどは省略しています。）

分析:

このツリーは、内側ハッシュJOIN (Inner hash join) を実行していることを示しています。JOINの片側はusersテーブル (Table scan on u)、もう片側はordersテーブル (Index scan on o using idx_order_date) です。

-> Inner hash join:
- actual time=0.200..0.800: JOIN全体の処理にかかった時間は約0.8ミリ秒です。最初の行は0.2ミリ秒後に出力され始め、全ての処理が完了するのに0.8ミリ秒かかったことを示します。
- rows=500: JOINの結果として500行が生成されました。
- loops=1: JOIN処理全体は1回実行されました。
-> Table scan on u: (usersテーブルへのアクセス)
- actual time=0.050..0.300: usersテーブルを走査するのにかかった時間は約0.3ミリ秒です。
- rows=1000: usersテーブルから1000行を読み込みました（テーブル全体をスキャンしていると予測）。
- loops=1: usersテーブルのスキャンは1回行われました。
-> Hash: （ハッシュテーブル構築）
- -> Index scan on o using idx_order_date: (ordersテーブルへのアクセス)
  - actual time=0.100..0.500: ordersテーブルをidx_order_dateインデックスを使ってスキャンするのに約0.5ミリ秒かかっています。
  - rows=500: このインデックススキャンで500行が取得されました。
  - loops=1: インデックススキャンは1回行われました。
  - -> Filter: (o.order_date >= '2023-01-01'):
    - actual time=0.100..0.450: フィルタリング処理にかかった時間は約0.45ミリ秒です。
    - rows=500: フィルタリングを通過した行数は500行です。

解釈:

まず、ordersテーブルに対してorder_dateインデックスを使ったスキャンが行われ、WHERE句の条件を満たす500行が取得されます（Index scan on oとFilterノード）。時間は約0.5ミリ秒。
次に、usersテーブルがフルスキャンされます（Table scan on uノード）。時間は約0.3ミリ秒。
これらの結果を使って、内側ハッシュJOINが実行されます（Inner hash joinノード）。JOIN処理自体に時間がかかっていることがわかります。

この例では、ordersテーブルはインデックスで絞り込めていますが、usersテーブルはフルスキャンされています。もしusersテーブルが非常に大きい場合、このフルスキャンがボトルネックになる可能性があります。ただし、JOINタイプがInner hash joinであることから、オプティマイザはordersの絞り込まれた結果（500行）を使ってハッシュテーブルを構築し、usersテーブルをスキャンしながらハッシュテーブルをルックアップするという計画を立てたと考えられます。この場合、usersテーブルのフルスキャンはJOINの内部ループの一部であり、必ずしもインデックスがないから遅いとは限りません。JOINタイプやデータ量、利用可能なインデックスによって最適な実行計画は変わります。

重要なのは、各ノードのactual timeを見ることで、実際にどこで時間がかかっているかを把握できる点です。もしTable scan on uのactual timeがJOIN全体の時間の大部分を占めているなら、usersテーブルへのアクセス方法（例: usersテーブルを先に絞り込む、JOIN条件に使用されるusersの列にインデックスを張るなど）を見直す必要があるかもしれません。

例3: ORDER BY と Filesort

「2023年以降に登録されたユーザーを、登録日が新しい順に並べる」クエリ。

sql EXPLAIN ANALYZE SELECT user_id, username, registration_date FROM users WHERE registration_date >= '2023-01-01' ORDER BY registration_date DESC;

idx_registration_dateインデックスがあり、ORDER BYにも同じカラムを使用している場合:

-> Sort: <sort_info> (actual time=0.500..0.600 rows=1000 loops=1) -> Index scan on users using idx_registration_date (actual time=0.100..0.400 rows=1000 loops=1) -> Filter: (users.registration_date >= '2023-01-01') (actual time=0.100..0.350 rows=1000 loops=1)

分析:

-> Sort: <sort_info>: ソート処理のノード。
-> Index scan on users using idx_registration_date: usersテーブルをidx_registration_dateインデックスを使ってスキャンしています。
- actual time: インデックススキャンに約0.4ミリ秒。フィルタリングに約0.35ミリ秒。
- rows=1000: 1000行を処理。
-> Sort:
- actual time=0.500..0.600: ソート処理自体は約0.1ミリ秒。
- rows=1000: 1000行をソート。

この例では、インデックスがWHERE句とORDER BY句の両方に利用されています。インデックスは元々ソートされた状態でデータを保持しているため、オプティマイザはインデックスを読み込む順序を調整するだけでソート済みデータを得られると判断した可能性があります。EXPLAIN出力のExtraにはUsing index for sortingまたは単にUsing indexと表示されるはずです。EXPLAIN ANALYZEでは、明示的なFilesortノードが表示されないことが、インデックスによるソートが行われた（またはソートが不要だった）ことを示唆します。

もしORDER BY句のカラムにインデックスがなかったり、インデックスが適切に使えなかったりする場合:

sql -- 例: registration_dateにインデックスがあるが、別のカラムでソート EXPLAIN ANALYZE SELECT user_id, username, registration_date FROM users WHERE registration_date >= '2023-01-01' ORDER BY username; -- usernameにはインデックスがないと仮定

-> Sort: <sort_info> (actual time=1.000..2.500 rows=1000 loops=1) -> Table scan on users (actual time=0.100..0.500 rows=10000 loops=1) -> Filter: (users.registration_date >= '2023-01-01') (actual time=0.100..0.450 rows=1000 loops=1)

分析:

-> Table scan on users: まずusersテーブルをフルスキャンして、WHERE句を満たす1000行を抽出しています。actual timeがそれなりにかかっています。
-> Sort: その後、抽出された1000行に対してソート処理が行われています。
- actual time=1.000..2.500: ソート処理に約1.5秒かかっています。これは大きな時間であり、ボトルネックの可能性が高いです。
- EXPLAIN出力では、このノードに対応する行のExtraにUsing filesortが表示されるはずです。

この例では、registration_dateにインデックスがあっても、ORDER BY usernameという条件を満たすためには、インデックスの順序を利用できず、結果セット全体（WHERE句で絞り込まれた後の1000行）をメモリ上またはディスク上でソートする必要が生じています。これがUsing filesortであり、EXPLAIN ANALYZEの出力ではソートノードのactual timeが大きいことで現れます。このソート処理が、クエリ全体のボトルネックになっている可能性が高いです。

このように、EXPLAIN ANALYZEを使うことで、EXPLAINだけでは「Using filesortが出るな」と推測するだけだったのが、「ソート処理に実際に1.5秒かかっている。これがボトルネックだ」と明確に特定できるようになります。

5. EXPLAIN ANALYZEの分析結果に基づく最適化の実践

EXPLAINとEXPLAIN ANALYZEを使ってクエリの実行計画を分析し、ボトルネックを特定できたら、いよいよ具体的な最適化を行います。分析結果から示唆される問題点（テーブルフルスキャン、非効率なJOIN、ファイルソート、一時テーブルなど）に対して、適切な対策を講じます。

5.1 インデックスの最適化

最も一般的で効果的な最適化手法は、インデックスの適切な利用です。

必要な列にインデックスを追加: WHERE句、JOIN句、ORDER BY句、GROUP BY句で使用される列は、インデックスの候補です。EXPLAINでtypeがALLになっている場合や、keyがNULLの場合は、関連する列にインデックスを追加することを検討します。
複合インデックスの利用: 複数の列が組み合わせて条件指定される場合（例: WHERE col1 = A AND col2 = B）、これらの列を含む複合インデックスを検討します。複合インデックスは、その定義順（Leftmost Prefix）に沿って使用されます。EXPLAINのkey_lenを見て、複合インデックスのどこまでが使われているかを確認しましょう。ORDER BYやGROUP BYでも複合インデックスが利用できる場合があります。
カバリングインデックス (Covering Index): クエリがSELECTするすべての列と、WHERE句などで条件指定する列が、すべて単一のインデックスに含まれている場合、MySQLはテーブルのデータファイル自体を読み込むことなく、インデックスだけを使ってクエリを完了できます。EXPLAINのExtra列にUsing indexと表示されるのが特徴です。これは非常に高速なアクセス方法です。クエリで取得する列を必要最小限に絞り込むことも、カバリングインデックスの利用可能性を高めます。
インデックスの削除: 使用されていないインデックスは、データの書き込み（INSERT, UPDATE, DELETE）時にオーバーヘッドとなるため、削除を検討します。EXPLAINのpossible_keysやkeyに全く表示されないインデックスは未使用の可能性が高いですが、必ずしも全てのクエリでテストできているとは限らないため、スロークエリログやパフォーマンススキーマなどの情報も参考に慎重に判断します。
統計情報の更新: オプティマイザはテーブルやインデックスの統計情報（行数、値の分布など）を元に実行計画を立てます。データが大きく変動した場合は、ANALYZE TABLE table_name;を実行して統計情報を最新に保つことが重要です。オプティマイザの予測（EXPLAINのrows, filtered）と実際（EXPLAIN ANALYZEのrows）に大きな乖離がある場合、統計情報が古いことが原因の一つである可能性があります。

5.2 クエリの書き換え

SQLクエリ自体の構造や書き方を見直すことで、オプティマイザがより効率的な実行計画を選択できるようになる場合があります。

SARGableな条件にする: WHERE句の条件式は、インデックスが利用しやすい形（SARGable – Search Argument-able）にするべきです。例えば、WHERE FUNCTION(indexed_column) = valueのような書き方はインデックスを利用できません (WHERE DATE(order_date) = '2023-01-01'の例)。代わりに、WHERE indexed_column >= '2023-01-01' AND indexed_column < '2023-01-02'のように書き換えることを検討します。
ワイルドカード検索: LIKE '%string'やLIKE '_string'のような前方または中間にワイルドカードがあるLIKE検索は、通常インデックスを利用できません（ALLまたはindexスキャンになりがち）。全文検索機能（FULLTEXTインデックス）や、検索エンジン（Elasticsearchなど）の利用を検討します。
JOINの見直し:
- 不要なJOINをなくす。
- STRAIGHT_JOINヒント句を使って、テーブルのJOIN順序を明示的に指定し、オプティマイザの選択とは異なる、より効率的な順序を試す。
- LEFT JOINが意図せず大量の行を生成していないか確認する。
サブクエリの見直し: 相関サブクエリ（DEPENDENT SUBQUERY）は効率が悪いことが多いです。可能な場合は、JOINや派生テーブル（DERIVED）に書き換えることを検討します。
LIMIT句の活用: 大量の結果セットを生成するが、実際に必要なのは先頭の数件だけ、という場合は必ずLIMIT句を使用します。LIMITは多くの場合、クエリの早期終了を可能にし、パフォーマンスを大幅に改善します。ただし、ORDER BYと組み合わせる場合、ソート対象の行数が多くなるとLIMITの効果が薄れることもあります。EXPLAIN ANALYZEでソート処理のactual timeを確認しましょう。
SELECT * を避ける: 必要のない列まで取得すると、I/Oリソースを無駄に消費したり、カバリングインデックスが利用できなくなったりする可能性があります。必要な列だけを明示的に指定するようにしましょう。
一時テーブルやファイルソートを避ける: EXPLAINやEXPLAIN ANALYZEでUsing temporaryやUsing filesortが表示された場合、その原因となっているGROUP BYやORDER BY句に対してインデックスが利用できないか検討します。複合インデックスの順序がGROUP BYやORDER BYの列順と一致しているか確認します。

5.3 データベース設計の見直し

根本的なパフォーマンス問題は、データベース設計自体に起因することもあります。

正規化/非正規化の検討: 過度な正規化はJOINを複雑にし、クエリが遅くなることがあります。読み取り性能が非常に重要で、書き込み頻度が低いテーブルでは、意図的に非正規化（冗長な列を追加する）を行うことで、JOINなしで必要な情報を取得できるようにし、クエリを単純化できる場合があります。ただし、データの整合性維持のコストが増加するため、慎重な検討が必要です。
データ型の最適化: カラムのデータ型を適切に選択することは、ストレージ容量の削減だけでなく、インデックスの効率や比較処理の速度にも影響します。必要以上に大きなデータ型（例: INTで十分なのにBIGINTを使う、VARCHAR(255)で十分なのにVARCHAR(5000)を使う）は避けるべきです。
パーティショニング: 非常に大きなテーブルの場合、特定の基準（例: 日付範囲）でデータを分割するパーティショニングが有効な場合があります。これにより、クエリが必要なパーティションのみを走査すればよくなるため、パフォーマンスが向上する可能性があります。EXPLAINのpartitions列でアクセスされるパーティションを確認できます。

6. EXPLAIN ANALYZE 利用時の注意点

EXPLAIN ANALYZEは非常に強力なツールですが、利用にあたってはいくつか注意点があります。

本番環境での実行リスク: 前述の通り、EXPLAIN ANALYZEはクエリを実際に実行します。特に書き込みクエリや、大量のデータにアクセスする読み込みクエリを本番環境で実行すると、サーバーに大きな負荷をかけ、既存の処理に悪影響を与えたり、最悪の場合システム障害を引き起こしたりする可能性があります。検証は必ず開発環境やステージング環境で行うようにしましょう。どうしても本番環境で実行する必要がある場合は、メンテナンス時間帯に行う、レプリカスレーブで実行するなど、影響を最小限に抑える工夫が必要です。
分析結果の解釈: EXPLAIN ANALYZEのツリー形式の出力は詳細な情報を含んでいますが、その解釈にはある程度の経験と、MySQLの内部動作（特にクエリ実行モデル、JOINアルゴリズムなど）に関する知識が必要です。複雑なクエリの場合、出力が非常に長大になり、ボトルネックの特定が難しいこともあります。
予測と実際の違い: EXPLAINで見たオプティマイザの「予測」（rows, filteredなど）と、EXPLAIN ANALYZEで見た「実際」（rows, actual time）が大きく異なる場合があります。これは、オプティマイザが古い統計情報に基づいて判断した、データ分布に関する誤解、あるいは特定の条件下での性能劣化など、様々な原因が考えられます。乖離が大きい場合は、統計情報の更新を試みたり、オプティマイザのヒント句を使って異なる実行計画を強制してみたりするなどの追加調査が必要になることがあります。
環境による違い: 開発環境と本番環境では、データ量、データ分布、サーバーのリソース（CPU、メモリ、ディスク）、MySQLの設定、同時実行されるクエリなど、様々な要素が異なります。開発環境で最適に見えたクエリでも、本番環境ではパフォーマンスが出ないということも起こり得ます。可能な限り本番環境に近い環境で検証することが理想です。
ツールの一つとして捉える: EXPLAIN ANALYZEはクエリパフォーマンス分析のための主要なツールですが、これだけで全てが解決するわけではありません。スロークエリログで問題クエリを特定し、SHOW PROCESSLISTでリアルタイムの状態を確認し、パフォーマンススキーマで詳細なメトリクスを収集し、さらにサーバーやアプリケーションの監視ツールからの情報と組み合わせることで、より総合的にパフォーマンス問題を診断し、解決することができます。

7. まとめ

MySQLのクエリ最適化は、アプリケーションのパフォーマンスを向上させるための継続的かつ重要なプロセスです。EXPLAINとEXPLAIN ANALYZEは、このプロセスにおいて中心的な役割を果たす強力なツールです。

EXPLAINは、クエリ実行前にオプティマイザの「予測」した実行計画を確認し、インデックスの使用状況や非効率な操作（フルスキャン、一時テーブル、ファイルソートの可能性）を推測するのに役立ちます。
EXPLAIN ANALYZEは、クエリを実際に実行し、各ステップにかかった実際の時間や処理した行数などの「実行時」統計情報を提供します。これにより、真のボトルネックを特定し、オプティマイザの予測と実際との乖離を把握できます。

これらのツールで得られた分析結果に基づいて、インデックスの追加・変更、クエリの書き換え、あるいはデータベース設計の見直しといった具体的な最適化手法を適用します。

クエリ最適化に終わりはありません。アプリケーションの進化やデータ量の増加に伴って、かつては効率的だったクエリがボトルネックになることもあります。定期的にスロークエリログを確認し、パフォーマンス問題が発生した際にはEXPLAINやEXPLAIN ANALYZEを活用して原因を分析する習慣をつけることが、高速で安定したアプリケーション運用への鍵となります。

本記事が、あなたがMySQLの遅いクエリ問題に立ち向かうための一助となれば幸いです。

8. 参考資料

MySQL 8.0 Reference Manual: EXPLAIN Statement
- https://dev.mysql.com/doc/refman/8.0/en/explain.html
MySQL 8.0 Reference Manual: EXPLAIN ANALYZE
- https://dev.mysql.com/doc/refman/8.0/en/explain-analyze.html
MySQL 8.0 Reference Manual: Optimizing SQL Statements
- https://dev.mysql.com/doc/refman/8.0/en/sql-optimization.html

上記で約5000語程度の記事になるように記述しました。内容としては、EXPLAINとEXPLAIN ANALYZEの基本的な概念、使い方、出力の読み方（特に重要な項目）、そして簡単な例とそれに基づいた分析、さらに最適化手法までを網羅しています。EXPLAIN ANALYZEのツリー形式出力の表現はテキストベースで表現できる範囲に限定しましたが、重要な統計情報は盛り込みました。