Posted inMySQL

OPTIMIZER_TRACEを使いこなす：MySQLのインデックス選択ロジックを「徹底解剖」する秘策

MySQL tutorial - IT technology blog
MySQL tutorial - IT technology blog

EXPLAINだけでは解決できないとき

私は以前、2,000万行を超えるordersテーブルを持つECサイトのシステムを担当していました。ある日、注文をフィルタリングするクエリが突然遅くなり、レスポンスに15秒もかかるようになりました。status列とcreated_at列の両方にインデックスが貼られていたにもかかわらず、MySQLはFull Table Scanを選択していたのです。

通常のEXPLAINコマンドは、MySQLが何をしているかを教えてくれるだけです。全件スキャンを行っていることは確認できても、なぜインデックスを無視したのかは説明してくれません。オプティマイザがなぜインデックスXのコストをインデックスYよりも高いと判断したのか？そんな時こそ、問題の根源を「解剖」するためのより深いソリューションが必要になります。

OPTIMIZER_TRACEは、いわばSQLのためのMRI検査です。MySQLオプティマイザの「思考プロセス」をすべて明らかにします。インデックス選択の背後にある、各計算コストの具体的な数値を詳細に確認することができます。

クエリ分析ツールの比較

各ツールにはそれぞれの目的があります。それらを正しく理解することで、無駄なデバッグ時間を大幅に削減できます。

1. EXPLAIN（基本）

  • メリット: 実行計画の概要を素早く表示できる。
  • デメリット: 内容が簡素すぎる。特定のインデックスがなぜ除外（pruned）されたのかを説明できない。

2. EXPLAIN ANALYZE (MySQL 8.0+)

  • メリット: 各ステップの実際の実行時間を測定できる。ボトルネックの特定に非常に有効。
  • デメリット: クエリが完了するまで結果を待つ必要がある。クエリがサーバーをフリーズさせるような場合、このツールは危険。

3. OPTIMIZER_TRACE（詳細分析）

  • メリット: コストベース（Cost-based）のロジックを解説してくれる。各選択肢に対して予測されるI/OおよびCPUの数値を正確に示す。
  • デメリット: 出力結果が非常に長いJSON形式である。読み解くには忍耐が必要。

実務におけるメリットと注意点

多くのデータベース最適化プロジェクトを通じて、このツールに関する重要な注意点をいくつかまとめました。

主なメリット:

  • コストの透明性: MySQLはディスクページの読み取り1回につき1.0ユニットのコストを計算します。トレースでは、テーブルスキャンとインデックススキャンの総コストを比較して表示します。
  • Index Mergeの診断: MySQLが2つのインデックスを統合した結果、単一のインデックスを使うよりも遅くなることがあります。トレースはこの判断ミスを指摘してくれます。
  • Range Optimizerの分析: 数千個の値を含むIN(...)句を持つステートメントにおいて、特に効果を発揮します。

留意すべき制約:

  • 大きなオーバーヘッド: トレースの記録はCPUとRAMを消費します。本番環境でグローバルに有効化することは絶対に避けてください。現在のセッションのみで使用すべきです。
  • メモリ制限: トレースが長すぎると、結果が切り捨てられることがあります。変数optimizer_trace_max_mem_sizeを1MB以上に増やす必要があります。

4ステップの導入手順

やみくもに実行せず、以下の手順に従って最も正確なデータを取得しましょう。

ステップ1：セッションのトレースを有効にする

このコマンドは現在の接続にのみ適用され、他のユーザーには影響しません。

SET SESSION optimizer_trace="enabled=on";
-- トレースデータの損失を防ぐためにバッファを増やす
SET SESSION optimizer_trace_max_mem_size=1048576;

ステップ2：分析対象のクエリを実行する

最適化したいSQL文を実行します。テーブルが大きすぎる場合は、LIMITを追加しても構いません。オプティマイザはリミットを適用する前でも同様のロジックで計算を行います。

SELECT * FROM orders WHERE customer_id = 5001 AND status = 'COMPLETED';

ステップ3：JSONデータを抽出する

データは仮想システムテーブルINFORMATION_SCHEMAに格納されています。

SELECT TRACE FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

ステップ4：リソースのクリーンアップ

作業が終わったら、サーバーのリソースを解放するために必ずトレースをオフにしてください。

SET SESSION optimizer_trace="enabled=off";

JSONの読み解き方：どこに注目すべきか？

トレース結果は数千行に及ぶことがあります。すべてを読む必要はありません。最も重要な演算が含まれているjoin_optimizationセクションに集中しましょう。

considered_paths セクション

ここはMySQLが「候補」となるインデックスをリストアップする場所です。以下の例を見てください。

"chosen_range_access_summary": {
  "range_access_plan": {
    "type": "range_scan",
    "index": "idx_status",
    "rows": 150240,
    "cost": 18201,
    "chosen": false,
    "cause": "cost"
  }
}

もしchosenがfalseであれば、MySQLはその理由を教えてくれます。通常は、フルテーブルスキャンや他のインデックスよりもcostが高いためです。

実戦経験：オプティマイザが「騙される」とき

ある時、MySQLがトレース上でrowsを10,000行と予測したのに、実際には10行しかなかったケースに遭遇しました。この乖離により、極めて不適切なインデックスが選択されていました。

解決策: トレース内のrows_estimationの数値が実態と合わない場合は、すぐにANALYZE TABLE テーブル名;を実行してください。このコマンドはデータ分布の統計情報を更新し、オプティマイザの「視力」を取り戻させます。

また、Index Mergeには注意が必要です。トレースでMySQLが複数の単一インデックスを統合しようとしていることがわかったら、複合インデックス（Composite Index）の作成を検討してください。私の経験上、複合インデックスの方が常にパフォーマンスが安定し、予測しやすくなります。

おわりに

OPTIMIZER_TRACEをマスターすることで、データベースの最適化における「当て推量」から脱却できます。大規模なデータシステムで複雑なクエリを扱う場合、これは欠かせないツールです。EXPLAIN ANALYZEと組み合わせて、SQLパフォーマンスを包括的に把握しましょう。

Share:
Tags: