...
ウェブホスティングのロゴ

MySQLのヒストグラム – インデックスなしでもより良いクエリプランを作成する

MySQLのヒストグラム これにより、オプティマイザに実際の分布データが提供され、選択性を正確に推定して、より高速なクエリープランを生成できるようになります。多くの場合、追加のインデックスなしでこれを実現できます。 ここでは、MySQL 8以降でANALYZE TABLEを使用してヒストグラムを設定・確認し、結合、フィルタリング、スキャンにおけるより適切な判断に活用する方法について解説します。.

中心点

ショートフォーカス: 以下の要点では、ヒストグラムを使用する際に私が特に注意している点を示します。.

  • 選択性 「直感」ではなく：より現実的なカーディナリティ推定
  • 索引なし より高速：偏った分布における最適な計画の選択
  • タイプ 理解する：シングルトンと等高線を状況に応じて適切に活用する
  • バケット 税制：メタデータコストとのバランスを考慮した解消策
  • ケア 注目ポイント：更新、確認、必要に応じて削除

インデックスのないヒストグラムがなぜ効果的か

私はこうしている。 ヒストグラム, 、そうしないと、オプティマイザはしばしば一様分布を前提としてしまい、その結果、不適切な実行計画を選択してしまうためである。ヒストグラムは、 値の分布 ある列を近似的にスキャンし、それによって =、>、BETWEEN、IN、IS NULL といった述語に対して現実的な選択性の推定値を提供します。 これに基づき、オプティマイザは、インデックス・レンジ・スキャン、テーブル・スキャン、あるいはネストループによる結合戦略のどれが最適かを判断します。例えば、ある条件が全行のわずか0.1%にしか当てはまらない場合、広範囲なスキャンではなく、ターゲットを絞ったアクセスを優先します。 一方、フィルタによってほぼすべての行が対象となる場合は、メリットのないコストのかかるインデックスアクセスは行わず、それによって 効率性 すべての計画において。.

MySQL 8.0 のヒストグラムの種類

私は2つに区別している タイプ: シングルトンとイキハイト。シングルトン・ヒストグラムは、頻繁に現れる単一の値を別々のバケットにまとめます。これは、「アクティブ」、「非アクティブ」、「アーカイブ」など、支配的なカテゴリが少ない列に最適です。 Equi-Heightヒストグラムは、各バケットにほぼ同数の値が割り当てられるように値の範囲を分割します。 ライン を含んでいます。これは、価格、タイムスタンプ、あるいは「穴のある」ID範囲など、連続的または不規則な分布に適しています。どちらのバリエーションも、オプティマイザーに対してフィルタのヒット率をより正確に提供します。私は常に、個人的な好みではなく、データの特性に基づいてタイプを選択しています。.

技術的な基礎：MySQLにおけるタイプ選択の制御

MySQLが具体的な ヒストグラムのバリエーション データの分布に基づいて自動的に決定されます。実際には、異なる値の数（NDV）がバケット数に比べて十分に少ない場合、実質的にシングルトンヒストグラムが生成されます。そうでない場合は、等高ヒストグラムが生成されます。したがって、私はそのタイプを「選択」します 間接的, 適切な列と適切なバケット数を設定することで。 カテゴリの数は非常に少ないものの、特定のカテゴリが圧倒的に多い列については、それらの値に対してシングルトン並みの精度が得られるよう、意図的にバケット数を少なく設定しています。また、分布が細かく、連続的なデータの場合は、EXPLAINが希望する 選択性 を反映している。.

重要：ヒストグラムとは 1段組. 列間の依存関係（例：status と country）を直接表現することはできません。そのような場合は、最も選択性の高い列にヒストグラムを作成し、それに応じて結合順序を調整すると効果的です。.

バケットの正しい選び方

MySQLはデフォルトで100を使用します バケット, 、ただし WITH N BUCKETS を使用すれば 1 から 1024 まで指定可能です。バケット数を増やすと解像度は向上しますが、メタデータ量と分析負荷も増大します。私は通常、控えめな設定から始め、EXPLAIN での影響を測定し、実行計画が依然として不適切と思われる場合に段階的に増やしていきます。 値が集中している場合（例：90件が%というステータス）、多くの場合、少数のバケットで十分です。一方、価格やタイムスタンプが細かく分散している場合は、バケット数を増やす価値があります。目標は、適切な 粒度, 、管理上の負担を不必要に増やすことなく、誤判断を著しく減らした。.

実践：ANALYZE TABLE を使用したワークフロー

私は明確な方針に従っています ワークフロー: まず、WHERE句やJOIN条件によく登場し、明らかに偏った分布を示している列を特定します。 次に、`ANALYZE TABLE tbl UPDATE HISTOGRAM ON col WITH N BUCKETS;` を使用してヒストグラムを生成し、`INFORMATION_SCHEMA.COLUMN_STATISTICS` を通じて確認します。 データの移動後は、ANALYZE TABLE を使用して再度更新します。統計情報が適切でない場合は、ANALYZE TABLE tbl DROP HISTOGRAM ON col; を実行して削除します。実行計画の効果を評価するために、以下の情報を確認します。 EXPLAIN ANALYZE の解釈 および推定値と実績値の比較 ライン より。

具体的な指示と管理

私は、明確で少ない手順に従って再現性のある方法で作業を行い、生成されたJSON統計情報を確認しています。.

-- 個々の列に対してヒストグラムを作成する
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 32 BUCKETS;
ANALYZE TABLE orders UPDATE HISTOGRAM ON created_at WITH 128 BUCKETS;

-- 1回の実行で複数の列に同じバケット数を設定
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, payment_method WITH 64 BUCKETS;

-- ヒストグラムを特定して削除
ANALYZE TABLE orders DROP HISTOGRAM ON status;

-- 統計情報の目視確認
SELECT
  SCHEMA_NAME, TABLE_NAME, COLUMN_NAME,
  JSON_PRETTY(HISTOGRAM) AS histogram
FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE SCHEMA_NAME = DATABASE()
  AND TABLE_NAME = 'orders'
  AND COLUMN_NAME IN ('status','created_at');

EXPLAIN ANALYZE を使って、その効果を即座に評価します：

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE status = 'canceled'
  AND created_at >= NOW() - INTERVAL 7 DAY;

推定値は改善されるか 行 顕著な変化が見られ、例えばフルスキャンからインデックス・レンジ・スキャンへと計画が変更されたり、結合順序が変更されたりした場合は、その対策は成功したと言えます。差異が依然として大きい場合は、バケット数を増減させて再度比較します。.

例：注文ステータスと稀な値

注文テーブルでは、「completed」というステータスが圧倒的に多く、「pending」は中程度に多く、「canceled」は極めて稀である。この 経営難 ヒストグラムがないと、選択性の誤りを招きやすくなります。APIが「canceled」をクエリすると、狭い範囲のインデックスアクセスで十分なにもかかわらず、オプティマイザが誤ってフルテーブルスキャンを選択してしまう可能性があります。 シングルトンヒストグラムを使用することで、MySQLは「canceled」がごくわずかな割合しか占めていないことを認識し、インデックス・レンジ・スキャンに切り替えたり、ジョインの順序を最適化したりします。これによりレイテンシが低減され、すべての バリアント フィルターの。厳格なSLOが設定されたダッシュボードでは、この修正によって反応速度が著しく向上することがよくあります。.

時系列とタイムスタンプ

時系列データには多くの アクセス 最新のデータに基づいており、古い時間枠は通常、ほとんど使用されない。created_at や updated_at に基づく等高線ヒストグラムは、アクセス頻度の高い時間帯と低い時間帯を明確に区別する。 これにより、オプティマイザは、レンジスキャンが適切か、それともテーブルスキャンの方がより迅速に結果に到達できるかを正確に見極めることができます。特に、大規模なテーブルに対する部分的な時間フィルターを適用する場合、実行計画の大幅な変更とI/Oコストの低減が顕著に確認できます。私は、 統計 ここでは、日々の業務に合わせて重点が移り変わるため、より頻繁に更新されています。.

パーティション、データ型、および照合順序

パーティション化されたテーブルについて、データの分布を確認します すべてのパーティションについて. 大きなばらつき（例えば月単位など）があると、グローバルヒストグラムが平滑化されることがあります。個々のパーティションが極端に選択的であるか、あるいは極端に幅が広い場合は、WHERE句にパーティションプルーニングフィルターを追加して、それでも実行計画の品質が適切かどうかを検証します。 全体として、MySQLがパーティションを早期に 除外する 缶。

ヒストグラムは、スカラー型で比較可能なデータ型（数値、日付・時刻、適切な照合順序が設定されたVARCHAR/CHAR）で最も効果を発揮します。 LOB／JSONデータ 私はどちらかといえば 生成されたコラム 抽出・型指定された値を用いて、必要に応じてヒストグラムや指標を追加する。文字列の場合、 照合 比較ロジック。照合順序によっては、値が一致する場合があります（例：大文字・小文字の区別）。現実的な選択性を得るため、クエリと照合順序を一致させています。.

限界と失敗

ヒストグラムは、とりわけ単一の列について 定数 確かに、多列間の依存関係については、限られた範囲でしか表現できません。相関の強い列や動的なパラメータ（例えば、アプリケーション側で設定されるもの）の場合、その限界が露呈します。ブール型フィールドや、分布がほぼ均一な列については、追加の統計情報を活用してもメリットがほとんどありません。 一方で、バケット数が多すぎたり、メンテナンスが過度になったりすると、管理や分析にかかる時間が増加する可能性があります。そのため、私はヒストグラムを適切に活用し、定期的に 効果 実際の仕様に基づいて。.

オプティマイザーの確認と更新

私はそれを確認します。 用途 ヒストグラムから『ANALYZE TABLE』および関連するオプティマイザ・オプションに至るまで、プランナーが統計情報を適切に活用できるようにします。負荷の高いシステムでは、更新処理をトラフィックの少ない時間帯に行うか、大規模なデータロード終了後にバッチ処理として実行するように計画しています。 更新の前後で、EXPLAINおよびEXPLAIN ANALYZEの出力を比較し、変更された結合順序、フィルタリングステップ、およびコストモデルを評価します。悪影響が確認された場合は直ちに対応し、統計情報をロールバックします。さらなる制御については、 オプティマイザーのオプション 他の統計値との関連性によって、気づかれないうちに誤った結果が 前提条件 生成する。.

監視、回帰防止、およびプレイブック

私は軽量なものを自作しています プレイブック 本番運用については：

  • ベースラインの設定：変更を行う前に、EXPLAIN ANALYZE、実行時間、「rows examined」、ハンドラカウンターを記録しておく。.
  • ヒストグラムの作成・変更：フィルタ列を重点的に設定し、バケットは控えめに設定する。.
  • その直後に測定を行う：計画値、推定値と実績値の行数を比較。乖離率が10倍を超える場合は、私にとっては警告サインとなる。.
  • 微調整：バケットを上下に調整し、必要に応じてクエリ内のフィルターの順序を調整します。.
  • ロールバックの準備をしておく：レイテンシが増加した場合は、DROP HISTOGRAMを実行する。.
  • 自動化：ETLロードや大規模なDML処理の後、メンテナンスウィンドウ内でANALYZEを実行する。.

原因分析には、以下の方法を用いています オプティマイザのトレース また、EXPLAIN ANALYZE を使用して、プランナーがヒストグラムに基づいて適切な選択対象テーブルを「優先」しているかどうかを確認します。 A/Bテストでは、統計情報の影響を個別に評価するために、試しに結合順序（STRAIGHT_JOIN）を固定したり、個々のインデックスの使用を強制・禁止したりしています。.

運営面では、短いものが有効であることが実証されている 変更ログ 各テーブルにつき：列、バケット数、時点、変更前／変更後の測定値。これにより、後日の修正が容易になり、不明確な相互作用を防ぐことができます。.

運用上の側面：ロック、コスト、ポータビリティ

ANALYZE TABLE は メタデータのロック テーブル上で実行されますが、通常の読み取り／書き込み操作を恒久的にブロックすることはありません。非常に大規模なテーブルの場合は、十分な時間を確保するようにしています。ヒストグラムの生成はサンプリングに基づいて行われ、メモリに制限があります（キーワード：計算用の内部メモリ）。 統計データ自体の占有容量はそれほど大きくありません。100～256個のバケットを持つ列1つあたり、数十キロバイトから数百キロバイト程度が現実的な目安です。とはいえ、多数の列に多数のテーブルが乗じるため、合計容量は計算しておく必要があります。 表示されているメタデータ.

時点では 論理ダンプ (mysqldump) では、ヒストグラムはデータとして移行されません。復元後は、意図的にヒストグラムを再作成します。 インプレースアップグレードの場合は、ヒストグラムは保持されます。サーバー側では、各オブジェクトに対して ANALYZE TABLE を実行するための十分な権限が必要です。規制の厳しい環境では、このメンテナンス作業をメンテナンスパイプラインに組み込んでいます。.

ヒストグラムが役に立たない場合

私はそれを省く ヒストグラム 値が非常に少なく、そもそも概算で十分に対応できる列については、優れたインデックスがすでに最小限のヒットセットをカバーしている場合でも、ヒストグラムによって追加の利点が得られることはめったにありません。均一な分布では、手間のかかる細かい粒度設定は必要ありません。 変動が激しく、書き込みが頻繁に行われるシステムでは、メンテナンスを頻繁に実行しすぎると、不必要な負荷が発生する可能性があります。そのような状況では、私は エネルギー むしろ、インデックス戦略、クエリ設計、およびキャッシュについて。.

表形式のカンペ

私は以下のものを利用しています 概要 迅速な意思決定のために：どのヒストグラムタイプが適しているか、バケットをどのように設定するか、そしてどの程度のコストが発生するか。 この表は、問題のあるクエリのレビューを行う際の参考資料として役立ちます。私は、EXPLAIN ANALYZE や本番環境のメトリクスから得た知見に基づいて、この表を更新しています。その際、データの分布は変化し、過去の仮定は時代遅れになる可能性があることに留意しています。重要なのは、 計画の質 実際の測定結果によって裏付ける。.

アスペクト 推薦 ベネフィット トレードオフ 例
タイプ 値が少なく、そのうちのいくつかが支配的な場合のシングルトン 一般的なカテゴリごとの正確なヒット率 連続した領域ではあまり役に立たない 注文状況
タイプ 歪んだ連続データにおけるEqui-Height 値の範囲全体にわたるより正確な推定 バケット数が多い場合のメタデータが増える created_at、price
バケット 100から始めて、その後調整する バランスの取れた解像度 512～1024の場合、分析およびストレージへの負荷が高くなる 「100個のバケツ」
ケア 大規模なデータ変更を行った後は、ANALYZEを実行してください 現在の選択性 メンテナンスの時間を確保する ANALYZE TABLE … UPDATE HISTOGRAM
コントロール COLUMN_STATISTICS を使用して確認する 透明性と監査 JSONの解釈が必要 INFORMATION_SCHEMA.COLUMN_STATISTICS

チューニング全体の構図における位置づけ

私は治療する ヒストグラム インデックス、クエリ設計、キャッシュ、ハードウェアパラメータに並ぶ構成要素として。優れたヒストグラムは、多くの場合、結合順序を変更し、I/Oを削減し、応答時間を一定に保つことができます。とはいえ、私はこれだけで適切なインデックス戦略や効率的なスキーマを置き換えるつもりはありません。 実行計画の決定プロセスをより深く掘り下げることで、次のようなメリットが得られます。 実行計画の理解 そして、コストモデルと実際の稼働時間を比較します。私は定期的に、その ワークロード 統計とまだ合致しているのか、それとも調整が必要なのか。.

高度な結合シナリオ

ヒストグラムは、フィルタが適用された複数のテーブルが関与している場合に特に有用です。例：

SELECT o.id, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'DE'
  AND o.status = 'canceled'
  AND o.created_at >= NOW() - INTERVAL 30 DAY;

ヒストグラムがない場合、オプティマイザーは o.status=’canceled‘ の選択性を過小評価したり、ドイツ人ユーザーの割合を過大評価したりする可能性があります。ヒストグラムを使用すると、 u.country そして o.status （必要に応じて、以下のサイトでも） o.created_at) 設計者はたいてい、この組み合わせが極めて選択的であることを認識しています。 実際の運用では、MySQLがまず小さい部分集合を特定し（例：users(country)やorders(status, created_at)のインデックスを介して）、その後でジョインを実行する様子が見られます。大きなテーブルをスキャンするのではなく、です。 これにより、I/O、バッファ、CPUリソースを節約でき、負荷がかかっている状況でもレイテンシを安定させることができます。.

ヒストグラムは単に 1段組 であるため、インデックス戦略は依然として重要です。（status、created_at）に基づく複合インデックスを使用することで、レンジスキャンをさらに高速化できます。この場合、ヒストグラムの主な役割は、オプティマイザがこれを 戦略 そもそも安価だと認識している。.

実践のためのまとめ

をセットした。 MySQL- オプティマイザが標準統計情報に基づいて誤った判断を下し、偏った分布が不適切な実行計画を生成する場合、ヒストグラムを使用します。ANALYZE TABLE を使用して、フィルタや結合で主要となる列の統計情報を、目的を絞って作成、更新、削除します。 SingletonとEqui-Heightのどちらを選択するかはデータに基づいて決定し、バケット数は測定結果をもとに調整します。EXPLAIN ANALYZEを使用して、結合順序、フィルタの位置、スキャンが意図した通りに変更されているかを確認します。これにより、最小限の オーバーヘッド クエリの実行速度が明らかに向上――多くの場合、追加のインデックスを必要とせずに。.

現在の記事