MariaDB オプティマイザートレース – SQL クエリの詳細を理解する

MariaDBの「optimizer trace」を使えば、オプティマイザがなぜ特定のプランを選択し、どのバリエーションを却下するのかを段階的に理解できます。このJSONトレースからは、以下のことが分かります。 決断 コスト、結合順序、フィルタについて理解し、SQLクエリを目的に合わせて最適化できるようにするためです。.

中心点

• 透明性: JSONベースのトレースにより、リライト、コスト、および破棄されたプランが説明されています。.
• フォーカス: join_preparation と join_optimization が、最も重要な知見を提供します。.
• 制御システム: セッション変数により、オーバーヘッドとメモリ使用量を抑えることができます。.
• ワークフロー: 実行計画についてはEXPLAIN/ANALYZEを、その「理由」についてはTraceを使用する。.
• 実用的なメリット: インデックス、統計情報、および結合順序を適切に調整する。.

MariaDBのオプティマイザートレースとは何ですか？

MariaDBはバージョン10.4以降、 オプティマイザー Traceは、SELECT、UPDATE、またはDELETE文の主要な最適化フェーズをすべてJSON形式で記録します。 これにより、エンジンがクエリを拡張し、条件を正規化し、最終的に結合順序やインデックスアクセスを決定する過程を確認できます。この分析は、主に最終的な実行計画を表示するEXPLAINよりもはるかに詳細であり、却下された代替案とその理由も明らかにします。このトレースデータは接続ごとにメモリ上に保持され、以下からアクセス可能です。 information_schema.OPTIMIZER_TRACE 準備完了。これで、内部の完全かつ機械可読な説明が得られる ステップ, 、それらが実行計画につながった。.

オプティマイザートレースを有効にして読み出す

セッションごとにこの機能を個別に有効にすることで、システム全体への負荷をかけずに診断を実行し、 メモリ があります。通常、私は SET SESSION optimizer_trace = 'enabled=on'; 必要に応じて SET SESSION optimizer_trace_max_mem_size = 1048576; トレースが膨大になる場合は、それ以上のバージョン。その後、疑わしいクエリを実行し、 SELECT * FROM information_schema.OPTIMIZER_TRACE LIMIT 1\G;. 重要：このテーブルには、アクティブな接続における直近のクエリのみが保存されます。また、次のようなフィールドには注意を払っています。 MISSING_BYTES_BEYOND_MAX_MEM_SIZE 或いは INSUFFICIENT_PRIVILEGES 診断のヒントとして。この作業方法により、生産環境をスリムに保ち、分析を 正確.

変数／フィールド	目的	値の例
optimizer_trace	セッションごとのトレースを有効にする	'enabled=on'
optimizer_trace_max_mem_size	トレースごとの最大保存容量	1048576 (1 MB)
OPTIMIZER_TRACE.QUERY	元のSQL文	SELECT ...
OPTIMIZER_TRACE.TRACE	最適化のJSONドキュメント	JSONテキスト
MISSING_BYTES_BEYOND_MAX_MEM_SIZE	トレースが大きすぎる場合のバイト数の切り捨て	0 または数量
INSUFFICIENT_PRIVILEGES	読み取り権限だけで十分でしょうか？	0 または 1

JSON構造：join_preparation および join_optimization

JSONの構造は、以下のブロックに分かれています。 join_preparation そして join_optimization, これらは最も重要なので、まず最初に目を通す 備考 提供する。セクション join_preparation 拡張クエリ（expanded_query) を確認し、エンジンが条件や予測をどのように変換したかを確認します。2つ目のブロック join_optimization 行数の推定値、検討されたプラン、選択された結合順序、およびテーブルへの選択的なWHERE句の追加を記録します。特に有用なのはサブツリーです rows_estimation, 検討された実行プラン そして テーブルへの条件の付与, 、それらはコストの仮定やフィルターの設定を直接指し示しているからです。これにより、誤った評価や好ましくない点がどこにあるかを素早く把握できます。 インデックス 不十分な計画につながってしまう。.

EXPLAINおよびANALYZEによる比較

完全な評価を行うために、EXPLAIN、ANALYZE、そして トレース 決まった手順で。まず、私は 説明する 或いは EXPLAIN FORMAT=JSON, 、選択したプランとキーパスを確認します。その後、次のように設定します EXPLAIN ANALYZE …を設定して、実際の実行時間データや、ループ回数やフィルタリングされた行数などのカウント値を取得します。疑問点が残っている場合は、オプティマイザートレースを有効にし、オプティマイザが検討して却下したバリエーションを確認します。この解釈に関する簡潔な入門情報については、以下の記事が参考になります。 EXPLAIN ANALYZE の理解, 、必要に応じて参考にするものです。.

計画の決定内容を把握する：コスト、カーディナリティ、フィルター

意思決定の論理は、カーディナリティ、コストモデル、および フィルター 実行計画に沿って。トレースでは、検討対象の各結合順序について、エンジンがどの行セットを想定しているか、そしてそこからどのように総コストを算出しているかを確認できます。古い統計情報や好ましくない相関関係により、レンジスキャンが過小評価され、フルスキャンが優先されていないかを確認します。 さらに、コストのかかる結合ステップを削減するために、エンジンがWHERE条件を最も選択性の高いテーブルに十分に早い段階で適用しているかどうかも確認します。このようにして、なぜその実行計画が選択されたのか、そしてそれをどのように インデックス, 、リライトや統計データの管理に影響を与える。.

実践：簡単なフィルタクエリのトレース

時点では SELECT * FROM t1 WHERE a < 10 以下で確認します join_preparation, 、エンジンが投影を拡張し、場合によっては条件を統合したかどうか、これが私にとって最初の 指標 を提供します。その後、ブロック内に次のように表示されます rows_estimation, エンジンがレンジスキャン用に a フルテーブルスキャンと比較して予想される値です。非現実的な値が見つかった場合、私はそれを統計情報の古さやヒストグラムの欠如の兆候と解釈することがよくあります。セクション 検討された実行プラン その後、インデックスアクセスがフルスキャンよりも本当にコスト効率が良いかどうかを確認します。最後に、 テーブルへの条件の付与, 選択条件が a 予定より早く着手したため、所要時間が大幅に 下.

JSON関数：必要な部分を的確に抽出する

トレースはJSON形式で提供されているため、次のようにして特定のサブツリーを絞り込んでいます。 JSON_EXTRACT そして、繰り返し発生する事象について簡単な分析を作成し、 サンプル. 例えば、検討中のプランのリストのみを読み取り、特定の結合順序が体系的に失敗していないかを確認します。同様に、上位候補のコストフィールドを抽出し、ANALYZEデータと比較することで、誤った仮定を発見します。 簡単なビューやストアドプロシージャを使って、診断セッション向けのこれらのチェックを自動化しています。このようにして、軽量な モニタリング 永続的なトレースを有効にすることなく、オプティマイザーの決定を追跡します。.

代表的な活用事例とメリット

EXPLAIN で予期せぬフルスキャンが示され、かつ インデックス を知りたいのです。また、多くのテーブルの場合、トレースは選択された結合順序の根拠を示してくれるため、代替となる実行計画への道筋がわかります。 バージョンアップの際には、更新前後のトレースを保存し、オプティマイザの挙動の変化を評価しています。戦略的なチューニングに関する課題については、この概要が以下の点で役立ちます。 内部の最適化メカニズム, 、これをトレース結果と関連付けます。こうして、インデックス、統計情報、あるいはクエリの記述について、 調整ネジ を置く。.

生産におけるベストプラクティス

私は一貫して、トレースを次のように有効にしています。 セッション- 十分なデータが収集でき次第、設定を変更して診断を正常に終了します。大規模なトレースの場合は、 optimizer_trace_max_mem_size あくまで一時的な措置として、その後は値を再び小さく設定します。JSONファイルを共有する前には、機密性の高い定数、コメントテキスト、または業務上の指標をマスキングしています。 トレースは診断ツールとして意図的に使用していますが、継続的な監視にはスロークエリログ、パフォーマンスビュー、または外部プロファイラを優先しています。この規律を守ることで、システムをスリムに保ち、不要な オーバーヘッド 日々のビジネスの中で。

ツールミックスにおけるオプティマイザートレース

包括的なチューニングを行うために、私は計画の理解、原因分析、システム測定という一連のプロセスを整理し、それらを結びつけます。 調査結果. EXPLAIN は実行プランを表示し、ANALYZE は実際のコストを確認し、トレースはその決定の背景を明らかにしてくれます。 並行して、クエリ実行プランの概念を学び、キーの選択、カーディナリティ、および結合戦略におけるパターンを把握しています。この視点にとって良い補足となるのが、以下の簡潔な概要です。 クエリ実行プラン, 、建築に関する疑問がある際に参考にしているものです。そこから、信頼性の高い 優先順位 インデックス作成、リライト、およびパラメータに関する作業。.

さらに深く掘り下げる：range_analysis と鍵の選択

トレースにはしばしばブロックが含まれている 範囲分析 各テーブルについて、Rangeアクセス、Refアクセス、EQ-Refアクセスのいずれにどのインデックスが適用可能かを特定できるようにするためです。 オプティマイザはそこで、「idx_a での範囲検索」、「idx_b での範囲検索」、あるいは「フルスキャン」といった選択肢を比較し、それぞれにコストと予想行数を割り当て、最適な選択肢を決定します。 コストが高いために妥当なインデックスが却下されたことがわかった場合、次にその根拠となっている選択性や統計情報を確認します。仮定が正しくない場合、 アナライズテーブル （必要に応じて、継続的な統計情報を伴う）あるいは、より的を絞った カバレッジ指数 その決定を覆す。.

複合インデックスの分割状況を確認することも有用です。トレースには、条件がインデックスの最初の列のみを使用しているのか、それとも追加の述語が検索可能であり、他のキー列が実際に使用されるようになるのかが記録されます。 この情報をもとに、述語を書き直す（例えば関数の使用を避ける）べきか、あるいは一般的なフィルタリングやソートがカバーされるようにインデックスを拡張すべきかを判断します。.

ジョインの詳細：セミジョイン、BKA/MRR、およびジョインバッファ

マルチテーブルクエリの場合、トレースセクションからは、セミジョイン戦略が検討されたかどうか、またどの戦略が検討されたか（例：FirstMatch、DuplicateWeedout、LooseScan、Materialization）がわかります。 そこで、マテリアライゼーションのコストが高すぎる、あるいは選択性が低すぎるといった理由で、あるバリエーションが却下された理由を把握できます。また、 バッチキーアクセス（BKA） そして マルチレンジ・リード（MRR） 有効になっている場合、トレースに表示されます。これらの手法はキーの検索をまとめ、キャッシュの局所性を向上させます。トレースにBKA/MRRが表示されない場合は、以下を確認します。 optimizer_switch および次のようなパラメータなど join_cache_level. 。ランダムなキー検索が頻繁に行われるワークロードでは、この方法により結合フェーズを著しく高速化することができ、これはEXPLAIN ANALYZEで検証可能です。.

また、結合バッファのサイズと種類も重要な要素となります。トレースを確認することで、ネストループのバリエーションがバッファあり・なしで実行されたか、またフィルタがどの時点で適用されているかが明らかになります。 私は、バッファサイズを拡大するよりも、結合キーに対する追加のインデックスを作成するか、中間結果を削減するためのリライトを行う方が、より効率的な選択肢であるかどうかを評価しています。.

サブクエリ、派生テーブル、およびビュー

時点では join_preparation 私の考えでは、EXISTS/IN形式のサブクエリが セミジョイン 再編成された（in_to_exists)、派生テーブルがマージされているかどうか（derived_merge) あるいは具現化されたかどうか、そして コンディション・プッシュダウン 派生テーブルにまで及ぶ。これらの手順は極めて重要である。なぜなら、マージが行われないと、コストのかかるマテリアライゼーションが発生する可能性があるからだ。トレースでコストの高いマテリアライゼーションの決定が繰り返し見られる場合は、明示的な STRAIGHT_JOIN, 、ヒントやクエリの再構成（例：ターゲットを絞ったフィルタを適用した共通テーブル式）によって、エンジンがより効率的な戦略を採用するようになる場合があります。ビューについては、オプティマイザがビューの内容を十分に展開できているか、あるいは基となるテーブルに追加のインデックスが不足していないかを確認します。.

パーティショニングとプルーニング

パーティション化されたテーブルの場合、トレースには、パーティションキーおよび述語に基づいて除外されたパーティションが表示されます（パーティション・プルーニング). 期待されるプルーニングが行われない場合は、パーティションキーに基づいてフィルタをより早期に、かつサージブルに定義すべきというシグナルとなります。 また、パーティショニングとインデックスの相互作用にも注意を払っています。ローカルインデックスやグローバルインデックスが欠けている場合、プリニングが行われていてもエンジンが過度に多くの行を検査することになり、トレース上で高いスキャンコストとして現れます。.

ヒント、インデックスの指定、および optimizer_switch を的確に検証する

私はトレース機能を使って、ヒントやパラメータスイッチの効果を 占拠する. 例えば、次のように設定すると. フォース・インデックス あるいはオプティマイザ・ヒントの場合、トレースを見て、その代替案が実際に適用されたかどうか、またどのように評価されたかを確認できます。 optimizer_switch 戦略を一時的に有効化または無効化できます（例：semijoin、index_merge、またはderived_mergeの決定の場合）。 このトレースは、エンジンが指定した条件を受け入れたか、あるいは他の制約（例えばカーディナリティなど）が依然として優先されているかを確認するための証拠として役立ちます。必要に応じて、次のようなフォーマットフラグを使用することもあります。 one_line 或いは end_markers に於いて optimizer_trace-文字列を、私の解析ツールに合わせて可読性を調整するため。.

Update/DELETE と書き込みパス

オプティマイザ・トレースはSELECTに限定されません。UPDATEやDELETE文においても、アクセス経路がどのように選択されるか、またフィルタが影響を受ける行数を最小限に抑えるために十分に早い段階で適用されているかを確認できます。 WHEREフィルタがSargableでないか、あるいはインデックスの欠如が、実際の変更が実行される前に広範囲なスキャンフェーズを引き起こしていないかを確認します。 トレースから、コンパクトなインデックス（例：必要な列のみ）が不要な往復アクセスを回避し、それによってロックやログの量を削減できるかどうかを判断します。.

セキュリティ、権限、およびプリペアードステートメント

トレースを完全に読み取るには、十分なオブジェクト権限が必要です。それがない場合、このフィールドは INSUFFICIENT_PRIVILEGES 制限事項。そのため、本番環境に近いシナリオでは、アプリケーションと同じログイン情報、あるいは特別な権限を持つ診断用アカウントを使用しています。 プリペアードステートメントの場合、トレースには通常、パラメータがバインドされた最適化された形式がすでに表示されるため、機密性の高い定数を公開することなく、選択性を評価することができます。トレースを共有する必要がある場合は、データ保護要件を遵守するために、パラメータ値をマスキングするか、代表的な範囲に置き換えます。.

自動化：トレースの収集、分類、記録

分析結果を再現できるようにするため、トレースをランダムに抽出して診断テーブルに保存し、スキーマ、バージョン、セッション変数、タイムスタンプなどのメタデータを付加しています。これにより、インデックスの変更やバージョンアップの前後で diffen, 、どの決定が先送りされたか。ブロックを 検討された実行プラン そして rows_estimation コストの変化を素早く比較できるように、別々に保存しておく。簡単な補助クエリを使って、選択した結合順序や計算されたコストを抽出している――例えば、 JSON_EXTRACT(TRACE, '$.join_optimization.considered_execution_plans') – そして、その結果をEXPLAINやANALYZEの出力結果と一緒に保存します。これにより、チューニングの各ステップごとに信頼性の高いドキュメントが作成されます。.

制限事項、バージョン固有の特性、およびMySQLとの比較

トレースの主要な構造はMySQLを基準としていますが、詳細やフィールド名はMariaDBのバージョンによって若干異なる場合があります。そのため、私は 意味論的な 各セクション（リライト、行推定、検討対象のプラン、条件アタッチメント）に注目し、表面的な違いに惑わされないようにします。重要：MariaDBでは、アクティブな接続の最後のステートメントに焦点が当てられます。 したがって、連続する多くのステートメントを分析する場合は、関連する痕跡が上書きされないよう、実行直後に手動で、あるいはフックを介して自動的に読み出す必要があります。非常に大きなJSONについては、必要なメモリ容量を見積もり、理解しています。 MISSING_BYTES_BEYOND_MAX_MEM_SIZE 一時的に制限を引き上げ、分析を再度実行するよう促すものとして。.

日常で役立つ具体的なJSON抽出方法

最後に、実務で私がよく使っている、要点を素早く伝えるための簡潔なフレーズをいくつか紹介します：

• 選択された結合順序と候補リスト：決定の順序を把握できるように、プランプレフィックスとそれぞれに付随するテーブルを取得します。.
• 範囲の代替案とコスト：最も選択性の高いテーブルについて、評価済みのインデックスのリストを抽出することで、リライトや新しいインデックスを的確に評価します。.
• 初期に適用されたフィルター：私はその テーブルへの条件の付与-セクションを設け、強力な述語がデータソースにできるだけ近くなるようにする。.

これらの抽出結果のビューがわずかであるため、私はオプティマイザーの決定を分析するための簡潔な「分析ツール」を用意しており、必要に応じて診断セッション中にこれを有効にし、終了後に再び無効にしています。.

頻繁に起こるつまずきとトラブルシューティング

ヒストグラムが欠落していたり、統計データが古かったりすると、推定値が外れてしまい、 プラン 不要なフルスキャンを避けるためです。トレースで著しく異なるカーディナリティが見られた場合は、統計情報を更新し、適切なインデックスを設定するか、SARG可能なフィルタに書き換えます。情報が不十分なトレースは、以下によって識別します。 MISSING_BYTES_BEYOND_MAX_MEM_SIZE そして、一時的に上限を引き上げて対応します。ANALYZE によって代替パスの実行時間が短縮された場合は、トレースを確認して、どのコスト要因が選択されたバリエーションを優先させたのかを確認します。このようにして、段階的に知識のギャップを埋め、最終的に クラリティ 意思決定の論理について。.

簡単にまとめると

MariaDBのオプティマイザートレースは、JSONドキュメントを通じて、エンジンがクエリを再構成し、行数を推定し、実行計画を比較し、最終的に シーケンス を選択します。セッションごとにこれを有効にし、トレースを読み込み、確認します join_preparation そして join_optimization そして、その知見をEXPLAIN/ANALYZEと結びつけます。インデックスの使用不可、フィルタの遅延、推定値の誤りといった理由から、具体的な対策を導き出します。具体的には、より適切なインデックスの作成、統計情報の更新、そして明確なクエリの記述です。 JSON関数を使用して、必要な部分を抽出し、パターンを認識し、決定事項を再現可能な形で文書化します。このようにして、大規模なSQLワークロードも信頼性の高い パフォーマンス そして、チューニングに関する決定を明確にする。.