EXPLAIN ANALYZE とオプティマイザトレースで不適切な実行計画をデバッグする

コマンド

-- Plan + actual time and rows (the query actually runs)
EXPLAIN ANALYZE
SELECT region, COUNT(*) FROM shop.big WHERE id < 20000 GROUP BY region;

-- Why the optimizer chose this plan: optimizer trace
SET SESSION optimizer_trace = 'enabled=on', SESSION optimizer_trace_max_mem_size = 1048576;
SELECT * FROM shop.big WHERE region = 'TR' AND id > 100 LIMIT 5;
SELECT JSON_PRETTY(JSON_EXTRACT(TRACE, '$**.considered_execution_plans')) AS plans,
       MISSING_BYTES_BEYOND_MAX_MEM_SIZE
FROM information_schema.OPTIMIZER_TRACE;
SET SESSION optimizer_trace = 'enabled=off';

概要

EXPLAIN ANALYZE は、実行計画の各ステップについて推定値 (cost、rows) と実績値 (actual time、rows、loops) を並べて表示する。オプティマイザトレースは、検討されたアクセスパス、そのコスト、各パスが選択または却下された理由を示す。

使用する場面

クエリが突然遅くなった場合 (実行計画の変更)、オプティマイザが誤ったインデックスを選択する場合、または推定行数と実績行数の不一致の原因を探る場合。

注意

EXPLAIN ANALYZE はクエリを実際に実行するため、本番環境での重いクエリには注意する。SELECT、TABLE および複数テーブルの UPDATE / DELETE で動作する。推定行数と実績行数の差が 10 倍を超える場合は統計 / ヒストグラムの問題を示す (ANALYZE TABLE、ヒストグラム)。ネステッドループの内側では actual rows はループごとの値であり、合計 = rows × loops。MISSING_BYTES_BEYOND_MAX_MEM_SIZE > 0 の場合はトレースが切り詰められているため、optimizer_trace_max_mem_size を引き上げる。トレースは同一セッションの最後のステートメントのみを保持する。