データベース

MariaDB クエリオプティマイザーの内部構造解説：基礎、実行プラン、実践

私が説明するのは、 MariaDB オプティマイザ 実務からの事例：彼がどのように実行プランを構築し、コストを見積もり、なぜ時々見当違いになるのか。これにより、SQL実行プランを的確に読み解き、インデックスを適切に活用し、直感ではなく事実に基づいてオプティマイザを誘導できるようになります。.

中心点

まず冒頭で、最も重要な構成要素を簡潔にまとめます。これにより、読者の皆さんが以降のセクションを的確に理解し、概要保持する。.

フェーズ: 解析、準備、最適化、実行は、あらゆるクエリのライフサイクルを構成しています。.
コストモデル: マイクロ秒単位の時間ベースの値が、インデックスの選択、スキャン、および結合の順序を制御します。.
統計情報: カーディナリティとヒストグラムは、選択性の推定を決定づける。.
透明性: EXPLAIN、EXPLAIN ANALYZE、およびOptimizer Traceは、ブラックボックスの内側を明らかにします。.
チューニング: インデックス、クエリのリライト、ANALYZE TABLE、およびコストパラメータが処理速度を向上させます。.

MariaDBにおけるクエリのライフサイクル

プランが策定される前に、クエリは4つの段階を経ますが、私は日常業務において、以下の目的でこれらを重点的に確認しています。原因処理が遅くなる原因を特定する。パーシングの際、MariaDBはSQLを内部構造に変換するため、この段階で構文エラーが検出される。準備段階では、エンジンがテーブル、カラム、および候補となるインデックスを検証し、簡単な変換を行う。続いて最適化が行われ、候補となる実行計画が計算され、コストモデルを用いて評価されます。実行段階では、サーバーは選択された実行計画を段階的に実行します。具体的には、読み取り、結合、フィルタリング、返却という順序で行われます。.

私は解析エラーをフェーズごとに明確に分類しています。そうすることで、原因の特定がより迅速に進み、対策的を絞って対処する。パフォーマンスの問題は、たいてい最適化に原因がある：誤った見積もり、インデックスの欠如、あるいは不適切なジョイン順序などだ。パーシングエラーは些細なものだが、プリペアリングの段階では、ビューの展開やサブクエリの変換といった工夫が必要になる場合もある。実行段階では、事前にフルスキャンが選択されていた場合、非効率性が容赦なく露呈します。そのため、私は調査のたびに、これら4つの段階すべてを体系的に確認することから始めます。.

オプティマイザーが内部でどのように判断するか

MariaDBはコストベースで動作し、 コスト関数. 各バリエーションについて、サーバーは読み込まれる行数、WHERE/ONの選択性、テーブルスキャン、インデックススキャン、レンジスキャンといったアクセス種別、および個々の操作にかかる時間を推定します。内部的には、サーバーは`join_preparation`と`join_optimization`を区別しています。`join_preparation`では、クエリの書き換え、条件の簡略化、サブクエリの変換、およびビューの展開が行われます。 join_optimizationでは、結合順序を計算し、ref_optimizer_key_usesを介してインデックス候補を検証し、範囲スキャンを通じて行数を推定し、条件を可能な限り早い段階で具体的なテーブルに割り当てます。.

この仕組みによって、小さなフィルターが間違った場所に設置されているだけで、なぜ高価な結果がある。attaching_conditions_to_tables の処理が遅れて行われると、実行計画は不必要に多くの行を結合処理に引きずり込んでしまう。統計情報が古くなっている場合、rows_estimation や Selectivity の値が誤ったものとなり、オプティマイザは一見有利だが実際には遅いアクセスパスを採用してしまう。私はまさにこれらの調整点に着目しています。すなわち、より正確な統計情報、より明確な述語、適切にソートされた複合インデックスです。これらを改善すると、実行プランの選択が顕著に変化することがよくあります。.

MariaDB 11.0以降のコストモデル

最新のリリースでは、作業の評価を大まかな重み付けではなく、次のように行っています。 マイクロ秒 具体的なストレージ操作に対して。optimizer_disk_read_cost、optimizer_disk_read_ratio、optimizer_where_cost といったパラメータにより、モデルは実際の実行時間にさらに近づきます。これにより、オプティマイザは実際の時間想定に基づいて、インデックス・レンジ・スキャンとフルスキャンを比較します。 LAST_QUERY_COST は推定総コストを表示し、以前よりも現実との相関性が大幅に向上しています。データ集約型システムにおいては、このよりきめ細かな評価基準が即座に効果を発揮します。.

ハードウェアの特性が標準的な仮定と相反し、その結果、 プラン選択 歪める。NVMe SSD、分散ストレージ、あるいは専用のキャッシュは、ディスク比率や読み取り時間を著しく変化させる可能性があります。optimizer_costs をわずかに調整するだけで、MariaDB は適切な実行パスを優先するようになります。私は変更点をすべて記録し、その後 EXPLAIN ANALYZE を確認してその影響を測定しています。測定を行わなければ、チューニングは賭け事になってしまいます。.

選択性、統計、ヒストグラム

正確な推定は、正確な カーディナリティ そして信頼性の高い選択性。MariaDBは列ごとのさまざまな値に関する統計情報を保持しており、オプションで分布のヒストグラムを利用することも可能です。特に、不均一なデータ（ホットスポット、ジップ分布、季節的なパターンなど）は、ヒストグラムの恩恵を受けます。大規模なデータ変更を行った後は、最適化が実際のデータに基づいて動作するように、ANALYZE TABLEを実行するようにしています。これを忘れると、客観的に見て誤ったフルスキャンが行われるリスクがあります。.

ANALYZEを、以下の条件に合わせて定期ジョブとして設定する予定です。 変更点 データ量および重要なテーブルにおいて。列の分布が大きく歪んでいる場合、ヒストグラムは特異値の選択性を現実的に把握するのに役立ちます。これにより、レンジスキャンやマージ戦略における誤った評価が軽減されます。適切な複合インデックスと組み合わせることで、ヒット精度が劇的に向上します。その結果、実行時間が短縮され、I/Oも削減されます。.

EXPLAIN および実行計画の読み方

決定内容を可視化するために、EXPLAIN、EXPLAIN EXTENDED、および FORMAT=JSON. 標準的なカラムからは、一目で概要を把握できます：id、select_type、table、type、possible_keys、key、key_len、ref、rows、および場合によってはfiltered。 type=ALL はフルスキャンを示しており、これはほとんど望ましくない。FORMAT=JSON では、条件がどのように移動されたか、またオプティマイザがどのパスを評価したかが詳細に示される。ホスティングの文脈では、以下のガイドを参照することをお勧めする：ホスティングにおける実行プラン, 、計画情報をインフラの影響と関連付けるため。.

素早く解釈するために、典型的な値を簡潔にまとめた小さな表が役立っており、それによって 誤った解釈 防止する。.

EXPLAINフィールド	代表値	実務における意義
タイプ	ALL、range、ref、eq_ref、const	右に行くほど選択性が強くなり、「ALL」はフルスキャンを示す。.
possible_keys	索引一覧	理論的に適合する指標――ここに候補が欠けていれば、構造も欠けていることになる。.
鑰	インデックス名	実際に使用されるインデックス。空の場合はインデックスを使用しないことを意味する。.
行	番号	推定読了行数；現実と大きく乖離している＝統計の精度が低い。.
フィルタリング済み	パーセント	フィルターを通過した後の量がどれくらいか。少ないほど良い場合が多い。.

オプティマイザーが時々的外れになる理由

どのコストモデルもあらゆる状況に当てはまるわけではないため、修正します失敗的を絞って。古い統計情報は、行数の誤った推定や不適切な結合順序につながります。構成が不適切な複合インデックスは、複数列のフィルタリング時にインデックスの利用を妨げます。過度にネストされたサブクエリは、効果的なリライトを困難にし、マテリアライゼーションを阻害します。フィルタの欠落や誤解を招くようなフィルタは、有用な述語が適用される前に、エンジンに多くの行を移動させることを余儀なくさせます。.

まず、クエリの記述が インデックス 実際に活用しているのは、左側プレフィックス規則、適切なソート順序、WHERE句での列に対する関数の使用回避です。その後、EXPLAIN ANALYZEを確認し、推定値が実際の結果と一致しているかを確認します。一致しない場合は、ANALYZE TABLEを実行し、必要に応じてリライトを行います。 FORCE INDEXやヒントの使用は、将来の最適化を制限する可能性があるため、あくまで最後の手段とします。.

オプティマイザートレースを効果的に活用する

EXPLAINだけでは不十分な場合は、オプティマイザトレースを有効にして、次の点を追跡します。決断 JSONログ内です。そこでは、どのプランが検討され、却下され、あるいは受け入れられたかを確認できます。条件の適用が遅れた理由や、インデックスが候補から外れた理由も把握できます。また、このログには条件の並べ替え状況も記録されています。この視点は理解を深め、次回のチューニングに向けた具体的な改善策を示してくれます。.

トレースの関連する部分を、クエリハッシュとともに保存し、 パラメータ記録しておく。そうすれば、後でどの変更がどのような効果をもたらしたかを比較できる。MariaDBサーバーのドキュメントやエコシステム内のさまざまな講演では、これらのフィールドについて詳しく説明されている（出典：MariaDBサーバーのドキュメント「クエリオプティマイザー」および「オプティマイザートレース」）。このツールを使えば、試行錯誤よりも早く誤った仮定を見つけ出すことができます。特に、複雑な結合を行う際に時間を大幅に節約できます。.

実践：データベース・チューニングのステップバイステップ

私は、あらゆる最適化を明確な測定. 問題の検出は、モニタリングとそれを通じて行っています。遅いクエリログ. 。その後、EXPLAINとEXPLAIN ANALYZEを比較し、実行計画と実際の結果を重ね合わせて確認します。インデックス戦略は、WHERE、JOIN、ORDER BYに合わせて調整します。複合インデックスは、アクセス頻度の高い箇所に合わせて設定します。FORCE INDEXは、統計情報が正しくてもオプティマイザが誤った候補を選択した場合にのみ使用します。.

どのステップにも、以下のケアが欠かせません。 統計情報: アクセス頻度の高いテーブルに対する ANALYZE TABLE 実行、偏った分布に対するヒストグラムの作成。不要なサブクエリを簡素化し、必要に応じて中間結果をマテリアライズし、古いワークアラウンドを整理します。特殊なハードウェアを使用する場合は、オプティマイザコストを確認し、マイクロ秒単位のモデルが正しいことを保証します。変更の都度、変更前後の値を記録し、その効果を長期的に追跡できるようにしています。.

オプティマイザーの典型的な問題と解決策

possible_keysが埋まっているにもかかわらずEXPLAIN type=ALLが表示される場合、まず以下を確認します。 選択性. 複合インデックス内の列順序が適切でない場合や、関数の影響でインデックスが利用できないことがよくあります。そのような場合、順序を入れ替えたり、問題となる関数を削除したり、述語を分割したりします。結合順序が不適切な場合は、選択条件の厳しいテーブルを先に持ってくるなど、早期フィルタリングが可能かどうかを確認します。サブクエリについては、適切であれば結合やTEMPORARYテーブルに変換します。.

私は、著しく逸脱している点からも、誤った判断を見抜くことができる行計画と現実のギャップ。その場合は、ANALYZE TABLE または対象の列に対するヒストグラムが役立ちます。正しい統計情報でも目的が達成できない場合は、明示的なヒントの使用を検討します。その前に、クロスチェックと測定値を確実に記録しておき、後続のオプティマイザのバージョンが、保存されたデータによってパフォーマンスが低下しないようにします。ここでのドキュメント作成における徹底した姿勢が、最終的に報われるのです。.

ホスティングの背景と運用上の側面

クエリの品質とインフラストラクチャは互いに適合していなければなりません。そうでなければ、アプリケーションの価値が台無しになってしまいます。 ポテンシャル. 高速なSSD、一貫性のあるキャッシュ、そして整然とした構成は、オプティマイザが適切な判断を下すための基盤となります。トラフィックが多い環境ではフルスキャンは許されず、わずかな不良クエリでもシステム全体の動作を著しく遅らせてしまいます。本番環境におけるMySQL/MariaDB環境向けに、以下のような実践的なヒントが提供されています。 MySQL オプティマイザー計画とプラットフォームの組み合わせに関する有益な示唆。この側面も考慮に入れることで、ボトルネックが深刻化する前に未然に防ぐことができます。.

私は常に計画分析を、以下の指標と結びつけています 入出力, 、レイテンシ、および並行処理。値が想定したコストモデルと一致しない場合は、パラメータを確認します。その後、バッファサイズ、並列ワークロード、およびホットセットの分布を確認します。こうした視点を持つことで、クエリとリソースを調和させて運用し、ピーク時の負荷を制御可能な範囲に抑えることができます。.

実務における結合パスとアクセスパス

私は、以下のことを説明することで、多くの誤解を解いています。 アクセス方法 意図的に比較検討する。一つは範囲- または ref-アクセスはほぼ常に成功する ALL. 一意キーに基づく論理結合の場合（eq_ref) では、計画が特に安定しています。また、 カバレッジ指数 クエリを完全に処理できる：必要な列がすべてインデックスに含まれている場合、MariaDBはコストのかかるテーブルへのアクセスを省くことができます。. インデックス・コンディション・プッシュダウン（ICP） インデックス内で追加のWHERE条件を事前に検証するのに役立ちます。これにより、返される行数とI/Oが削減されます。.

について インデックスのマージ MariaDB では、複数のインデックスを組み合わせる（共通部分／和集合）ことができます。これは OR 述語や複数の選択条件がある場合に役立ちますが、適切に選択された複合インデックスよりも処理速度が遅くなる場合が多いです。また、私は以下の点についても評価しています。 MRR （マルチレンジ読み取り）および BKA (Batched Key Access)。MRRは、ランダムI/Oを平滑化するために読み込む主キーをソートします。一方、BKAはジョインのルックアップを束ねるもので、特に非カバレッジジョインにおいてその効果を発揮します。実際には、optimizer_switch を使用して BKA/MRR をテストし、EXPLAIN ANALYZE で I/O パターンが減少しているかどうかを確認しています。一方、MariaDB が ブロックネストループ (BNL) の場合、多くの場合、ジョインバッファ（join_buffer_size）を増やすか、あるいは実際のインデックス結合を可能にするリライトを行う方が効果的です。.

-- 例：結合＋フィルタ＋並べ替え用の複合インデックス
CREATE INDEX ix_orders_cust_status_created
  ON orders (customer_id, status, created_at);

-- 一般的なアクセス例
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'open' AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 50;

上記のインデックスを使用することで、オプティマイザは最も選択性の高い順序を選択し、フィルタを早期に評価し、多くの場合、追加のファイルソートを必要とせずにソートを実行することができます。.

ORDER BY、GROUP BY、ファイルソート、および一時テーブル

並べ替えや集計には時間がかかります。私が確実に ORDER BY そして GROUP BY インデックスの順序に従って実行できます。これは、プレフィックスと方向が完全に一致する場合に機能します。そうでない場合は、 ファイルソート ソートバッファ（sort_buffer_size）および必要に応じて一時テーブルを使用します。結果セットに幅の広いTEXT/BLOB列が含まれている場合、MariaDBの方が高速に処理されます オンディスク TEMP-Tables（Aria）について。必要な列のみを選択したり、大きなフィールドは最後に読み込んだり、長さが制限されたプレフィックスを使用したりすることで、問題を未然に防いでいます。.

集計を行う際は、可能な限り、, ルーズ・インデックス・スキャン （例：先頭インデックス部分での GROUP BY）を行い、グループ化に沿って複合インデックスを選択します。中間結果が大きくなると、適切なキーを用いたマテリアライゼーションの方が、単一のメガ結合よりもスケーラビリティに優れています。私は定期的にハンドラのメトリクスや Created_tmp_* カウンターを測定し、ソートや一時テーブルのホットスポットを特定しています。.

サブクエリ、セミジョイン、およびマテリアライゼーション

多くのサブクエリは、準備段階で効率的に書き換えることができます。IN/EXISTS構文は、 セミジョイン 実行し、マテリアライゼーションやLooseScanといった戦略を用います。オプティマイザーが derived_merge 実行できた：派生テーブル（またはWITH-CTE）が外側の実行計画に組み込まれると、そのインデックスを直接利用できるようになる。それができない場合、サブクエリは一時テーブルに格納されます。その際は、可能であれば（例えば、キー列に対してSELECT DISTINCT/ORDER BYを実行するなどして）キーを割り当て、結合操作が「行方不明」にならないようにします。.

-- 例：INの代わりにEXISTSを使用し、マージ可能な派生テーブル
SELECT o.id
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM payments p
  WHERE p.order_id = o.id AND p.state = 'captured'
);

-- 明確なキーを用いた派生テーブル
WITH paid_orders AS (
  SELECT DISTINCT order_id
  FROM payments
  WHERE state = 'captured'
)
SELECT o.*
FROM orders o
JOIN paid_orders po ON po.order_id = o.id;

EXPLAIN FORMAT=JSON を使用して、以下のことを確認します。 具現化された 或いは 従属サブクエリ 選出されたかどうか、および条件（条件プッシュダウン) 早めに手を打つ。.

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

パーティション分割はインデックスの代わりにはなりませんが、 1回のアクセスあたりのデータ量 大幅に削減する。オプティマイザーは、述語が パーティションキー 明確に一致し、関数によって判別不能にならないようにするためです。そのため、パーティション化されたテーブルのWHERE句ではDATE(created_at)のような式は避け、代わりに範囲の境界値を使用しています。EXPLAINを実行すると、どのパーティションが読み込まれるかが分かります。範囲が広い場合は、プルーニングが不十分であることを示しています。.

小さなパーティションが多すぎると、計画のオーバーヘッドが増大します。そのため、適切な粒度（例えば、毎日ではなく毎月など）を選択し、パーティションごとの統計情報を最新の状態に保ち（ANALYZE PARTITION）、重要なインデックスがパーティション内にローカルに存在しているかどうかを確認しています。移行プロジェクトでは、レプリケーションやバックアップへの影響も考慮に入れます。これら2つは、パーティション分割をどの程度積極的に行うかに影響を与えるからです。.

サージビリティとリライトパターン

最もシンプルな手段は依然として サージビリティ – インデックスを活用できる条件。WHERE句での列に対する関数の使用は避け、列側で定数を返し、必要に応じてOR条件を ユニオン・オール. 先頭にアンカーのないLIKE検索（「%foo」）には、BTREEインデックスは役に立たない。この場合は、フルテキスト検索か、適切な検索サービスを利用することを検討している。計算には インデックスが設定された生成列, 、これにより、オプティマイザがインデックス内のロジックを特定できるようになります。.

-- アンチパターン：列に対する関数
WHERE DATE(created_at) = '2026-08-01'
-- より良い方法：生データに基づく範囲指定
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'

-- アンチパターン：OR句によりインデックスが利用できない
WHERE status = 'open' OR customer_id = 42
-- より良い方法：UNION ALL を使用した 2 つの検索と、それぞれに独自のインデックス
(SELECT ... WHERE status = 'open')
UNION ALL
(SELECT ... WHERE customer_id = 42');

複合指数については、私は 左側接頭辞の規則 厳密に遵守し、列を選択性の順、および後で必要となるソート順に並べ替えます。降順の ORDER BY が必要になる場合は、インデックスのレイアウトにその点を反映させます。そうすることで、ファイルソートを回避できます。.

オプティマイザースイッチとコストの微調整

クエリを実行する前に、次の点を確認します。 optimizer_switch およびバッファ。以下のような機能として mrr, batched_key_access, index_merge, セミジョイン, derived_merge 或いは 派生型に対する条件プッシュダウン はセッションごとに調整可能です。私はテストセッションごとに候補を意図的に有効化し、EXPLAIN ANALYZE で測定を行い、効果が得られない場合は元に戻します。結合パスは、十分な 結合バッファサイズ; さまざまな種類の ソート・バッファ・サイズ. 同時に、並行処理による負荷がかかった際にサーバーがスワップ状態に陥らないよう、並行処理数に対するバッファの状況を常に監視しています。.

コスト面では、必要に応じて、前述の optimizer_costs マイクロ秒単位で。私の指針は、測定ポイントを記録した、小さくて元に戻せるステップを踏むことです。私は LAST_QUERY_COST 妥当性チェックを行うため、また、計画は具体的なリテラルに大きく依存する可能性があるため、現実的なパラメータ値を用いて測定を繰り返します。.

計画の安定性、回帰分析、チームのワークフロー

優れた計画であっても、データの増加やバージョンの変更によって傾く. そのため、クエリハッシュ、EXPLAIN-JSON、オプティマイザトレースの抜粋、EXPLAIN ANALYZEの実行時間といった実行計画に関する情報を確実に記録しています。インデックスの変更やリライトについては、変更前後の証拠を添付したプルリクエストとして提出しています。 CI/CD環境では、代表的なデータセットを用いて、重要なクエリを自動的に検証しています。これにより、 回帰計画 早朝に。.

厄介なケースには、私は ヒント （FORCE INDEX、STRAIGHT_JOIN、クエリごとのoptimizer_switch）は最後の手段として用意されていますが、使用は控えめにし、有効期限を設けてください。むしろ、原因（統計情報、インデックス、クエリの記述）そのものを改善する方が望ましいです。チーム内では、Sargability、インデックス設計、測定の徹底に関する簡潔なガイドラインを設けることで、新機能の導入によってパフォーマンス上の問題が見過ごされることを防ぐことができます。.

概要：計画から成果へ

誰が利用できるか プラン 理解することが、パフォーマンスを左右します。「Parsing」「Preparing」「Optimizing」「Executing」の各フェーズを分析することで、どこで時間が浪費されているかが明らかになります。バージョン11.0以降で導入された時間ベースのコストモデル、適切に管理された統計情報、ヒストグラムにより、見積もりの信頼性が高まります。 EXPLAIN、EXPLAIN ANALYZE、およびオプティマイザートレースにより透明性が確保され、私はそれを具体的な対策へと落とし込みます。適切なインデックス戦略、明確なクエリ設計、そして適切なインフラストラクチャにより、MariaDBのクエリは常に高速な応答を実現します。.