MySQL Histogram：インデックスを追加せずにクエリを高速化する秘策 – ITFROMZERO

Table of Contents

クイックスタート：5分でできるヒストグラムの設定

インデックスを貼っているのに、ある時は高速（0.5秒）で、ある時は低速（10秒）でSQLが実行されるという経験はありませんか？それは、データの密度に関する情報が不足しているため、MySQL Optimizerが「推測」で動いているからかもしれません。インデックスのようにデータベースに負荷をかけず、データの全体像を把握させるために、ヒストグラムを作成してみましょう。

例えば、数百万行の orders テーブルがあり、order_status カラムのデータ分布をMySQLに正確に理解させたい場合は次のようにします：

-- order_statusカラムに対して100個のバケットでヒストグラムを作成
ANALYZE TABLE orders UPDATE HISTOGRAM ON order_status WITH 100 BUCKETS;

-- データディクショナリで結果を確認
SELECT * FROM information_schema.column_statistics 
WHERE table_name = 'orders' AND column_name = 'order_status';

たった一行のコマンドで、データの「密度マップ」を作成できました。これにより、Optimizerは最高のパフォーマンスを得るために、いつインデックスを使い、いつフルテーブルスキャン（Full Table Scan）を行うべきかを正確に判断できるようになります。

なぜインデックスが「無力」になることがあるのか？

以前携わったEコマースプロジェクトで、1,500万レコードを持つ products テーブルがありました。category_id カラムにはインデックスが貼られていました。しかし、全データの40%を占めるような一般的なカテゴリーをクエリすると、MySQLがフルテーブルスキャンではなくインデックスを無理に使おうとし、数百万回のランダムI/Oが発生してシステムがハングアップしてしまいました。

原因は単純です：インデックスはMySQLにデータが「どこにあるか」を教えるだけで、「どのように分布しているか」は教えないからです。

MySQLのヒストグラム（バージョン8.0以降）はこの問題を根本的に解決します. 値の出現頻度の統計を非常に軽量なスナップショットとして保存します。INSERTやUPDATEのたびに更新が必要なインデックスとは異なり、ヒストグラムは明示的に ANALYZE コマンドを実行したときだけリソースを消費します。

比較：インデックス vs ヒストグラム

インデックス: 自動的に更新され、特定の行の検索には適していますが、書き込み速度（INSERT/UPDATE）を低下させます。
ヒストグラム: 手動で更新され、特定の行の検索はサポートしませんが、Optimizerが推定行数（Selectivity）を正確に算出するのを助けます。データの偏り（skewed data）があるカラムに最適です。

知っておくべき2種類のヒストグラム

MySQLは、データカラムの特徴に基づいて適切なヒストグラムの種類を自動的に選択します：

1. Singleton Histogram（シングルトン・ヒストグラム）

このタイプは、カーディナリティ（値の種類）が低いカラムに適しています。例えば、3つの値しかない gender や、5つのステータスを持つ order_status などです。各「バケット」は特定の値を表し、その正確な割合を保持します。

2. Equi-Height Histogram（等高ヒストグラム）

price や created_at のように値の範囲が広い場合、MySQLはEqui-Heightヒストグラムを使用します。データは各グループの行数がほぼ等しくなるように分割されます。これにより、WHERE price BETWEEN 100 AND 500 のようなクエリを効率的に処理できます。

プロのようにヒストグラムを管理する

すべてのカラムにむやみにヒストグラムを作成しないでください. 頻繁にフィルタ条件（WHERE句）に使用され、かつデータの偏りが大きいカラムに集中しましょう。

精度の調整

デフォルトのバケット数は100です。地理座標やシステムエラーコードのようにデータが非常に複雑な場合は、この数値を最大1024まで増やして精度を高めることができます：

ANALYZE TABLE users UPDATE HISTOGRAM ON age WITH 256 BUCKETS;

不要になったヒストグラムの削除

データ構造が完全に変わった場合、古いヒストグラムはOptimizerに誤った判断をさせる可能性があります。その場合は削除してください：

ANALYZE TABLE users DROP HISTOGRAM ON age;

大規模プロジェクトからの実践的な経験

長年大規模データベースの最適化を行ってきた中で、ヒストグラムを使用する際の3つの黄金律を導き出しました：

偏ったデータ（Skewed Data）を優先する： 例えば顧客の80%がホーチミン市に集中し、残りの20%が他の62省に分散している場合、ヒストグラムがあればMySQLはすべての省に対して同じクエリ戦略を適用することを避けます。
変動の少ないカラムに使用する： 自動更新されないため、ヒストグラムは製品カテゴリや履歴データなどのカラムに理想的です。1秒間に数千回変更されるようなカラムへの使用は避けましょう。
レポート用のインデックスの代わりに使う： 1日の終わりの BI/レポーティングクエリにおいて、.ibd ファイルを重くする10個のインデックスを貼る代わりに、ヒストグラムを使用します。クエリ効率は同等でありながら、日中の注文INSERT速度には全く影響を与えません。

以前、1億行のログテーブルを扱ったことがあります。error_code にインデックスを貼ったところ、インデックスファイルのサイズが4GB増加しました。インデックスを削除し、512個のバケットを持つヒストグラムに置き換えた結果、エラー統計クエリは5倍速くなり、データファイルも大幅に軽量化されました。

技術的な注意点

ヒストグラムは現在、数値、文字列、日付などの基本的なデータ型のみをサポートしています。JSON型や空間データ型（Spatial）には対応していません。統計情報が常に実際のデータに即したものになるよう、週に一度 UPDATE HISTOGRAM を実行するCronジョブを設定することを忘れないでください。

ヒストグラムを理解して適切に活用することで、むやみにインデックスを貼るのではなく、MySQL Optimizerをより効果的に制御できるようになります。