EXPLAINとEXPLAIN ANALYZEの使い方と違いと、アプリケーション開発における注意点まとめ。
自身がはまった点を含め、覚書きも兼ねてまとめました。
MySQLでは、EXPLAIN ANALYZEは8.0.18で追加された機能です。
本記事の内容は、MySQLの場合、それ以降のバージョンが対象となります。
EXPLAINとEXPLAIN ANALYZEの違い
| EXPLAIN | EXPLAIN ANALYZE | |
|---|---|---|
| クエリの実行 | しない | する |
| 実行計画の表示 | ◯ | ◯ |
| 実際の実行時間 | ✗ | ◯ |
| 用途 | クエリ規模の事前確認 | ボトルネックの特定 |
EXPLAIN
クエリを実際には実行せず、MySQLがどのような実行計画を立てているかを表示します。
統計情報をもとにした推定値のため、あくまで目安となります。
重いクエリを実行する前に規模感を把握したいときに向いています。
出力サンプル
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
# 以下、MySQLでの結果
id | select_type | table | type | key | rows | Extra
1 | SIMPLE | orders | ref | idx_user_id | 42 | NULL
主な観点
| 項目 | 説明 |
|---|---|
| type | アクセス方法。refやrangeは効率的。ALLはフルスキャンで注意 |
| rows | スキャンする推定行数。大きいほど重くなりやすい |
| key | 使用されるインデックス。NULLはインデックス未使用 |
EXPLAIN ANALYZE
クエリを実際に実行したうえで、各ステップの実行時間・処理行数を計測して表示します。
ボトルネックの特定に向いています。
実際にクエリが発行されます。
重いクエリには先にEXPLAINで規模感の確認を推奨。
出力サンプル
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1;
# 以下、MySQLでの結果
-> Index lookup on orders using idx_user_id (user_id=1)
(cost=14.71 rows=42) (actual time=0.032..0.187 rows=38 loops=1)
主な観点
| 項目 | 説明 |
|---|---|
| actual time | 実際の実行時間（単位はms） |
| rows（actual） | 実際に処理した行数。推定と乖離が大きい場合は統計情報が古い可能性あり |
| loops | そのステップが繰り返された回数 |
注意点：EXPLAIN ANALYZEの結果と実際の実行時間のズレ
EXPLAIN ANALYZEの結果が問題なさそうでも、実際にクライアントから実行すると思ったより時間がかかるケースも。
それには、executing（クエリ実行時間）とfetching（データ返却時間）を考慮する必要があります。
原因：executingとfetchingの違い
| フェーズ | 内容 | EXPLAIN ANALYZEで計測できるか |
|---|---|---|
| executing（クエリ実行時間） | クエリの処理・絞り込み | ◯ |
| fetching（データ返却時間） | 結果データのクライアントへの転送 | ✗ |
EXPLAIN ANALYZEが計測するのはexecutingのみです。
トータルの時間を計測したい場合は、SQLクライアントなどから直接実行してfetchingにかかる時間も確認する必要があります。
特にORDER BYが絡む場合
| 挙動 | |
|---|---|
| ORDER BYなし | 取得できた行から順次返却 |
| ORDER BYあり | 全件ソート完了後にまとめて返却 |
ORDER BY に LIMIT を組み合わせた場合は、MySQLの最適化により全件ソートが省略されることがあります。
EXPLAIN ANALYZEのソートノードのコストが小さく見えても、全件ソート後の一括転送が発生することでトータルが大幅に遅くなるケースがあります。
executingだけで判断せず、実際のクライアント実行時間も合わせて確認するのが確実です。
おわりに
EXPLAIN ANALYZEは実際にクエリを実行するため、EXPLAIN ANALYZEで実行して問題なければ大丈夫と思っていたのですが、アプリケーションレベルではfetchingも加味する必要がある点を認識できていませんでした。
今後アプリケーション向けにクエリを組む際は、実際に実行して実行時間に問題がないか確認するよう徹底したいです。