【データベース】統計情報と単一カラムのヒストグラム

Transcript

1. 【データベース】 統計情報と 単一カラムのヒストグラム
2. 目次 1. ヒストグラムとは？ 2. 統計情報のヒストグラムとは？ 3. 統計情報のヒストグラムの種類 4. RDBMSごとのヒストグラム仕様の比較 5. 各ヒストグラムの活用例（クエリ最適化） 6. 検証の準備：ヒストグラムの確認方法 7. 検証：ヒストグラムによるクエリ最適化の影響 8. まとめ
3. 1.　ヒストグラムとは？ • データの分布を視覚的に表すグラフ • データを一定の区間（ビン）に分け、それぞれの頻度を棒グラフで表現 ◦ X軸：データの範囲（ビン）、Y軸：各ビンのデータ数 • データの偏りやばらつきを直感的に理解しやすい ◦ 例：テストの点数分布、商品の売上分布 など • データベースでは統計情報としてクエリ最適化に活用
4. 1.　ヒストグラムとは？ • 棒グラフとヒストグラムの違い 比較項目 棒グラフ ヒストグラム 目的 カテゴリごとの比較 データの分布を表現 X軸 独立したカテゴリ (商品A, B, C) 連続した値の範囲 (0-10, 10-20, 20-30) バーの間隔 あり（区別が明確） なし（範囲が連続） データの種類 定性的データ（カテゴリ） 定量的データ（数値） 例 「テストの点数比較」 「テストの点数分布」
5. 1.　ヒストグラムとは？ 棒グラフ（イメージ） カテゴリごとの比較
6. 1.　ヒストグラムとは？ ヒストグラム（イメージ） データの分布 （範囲）
7. 2.　統計情報のヒストグラムとは？ • データベースがクエリ最適化のために持つ統計情報の一種 • カラム内のデータの分布 を記録し、実行計画の精度を向上 させる • WHERE句のフィルタリングや結合処理 で、 適切なインデックスや結合方式を選択するために活用 • ヒストグラムがないと、データ分布を誤認し、 非効率なクエリプランが選ばれる可能性あり • RDBMSごとに異なるヒストグラムの実装が存在
8. 2.　統計情報のヒストグラムとは？ ヒストグラム （イメージ）
9. 3.　統計情報のヒストグラムの種類 • 等間隔ヒストグラム （Equal-Width Histogram） ◦ 各ビンの幅が均一（例：0-10, 10-20, 20-30…） ◦ シンプルで計算コストが低い ◦ データが均等に分布している場合に適している • 等深度ヒストグラム （Equal-Height Histogram） ◦ 各ビンに同じ数のデータを含める（ビンの幅は可変） ◦ データが偏っていても、分布の特徴を反映しやすい ◦ 値の偏りが大きいデータに適している • ハイブリッドヒストグラム （Hybrid Histogram） ◦ 等間隔＋等深度の組み合わせ ◦ データベース製品ごとに最適化された手法（製品によってルールが異なる） ◦ 特定の値が頻出するデータに適している • ✅ 統計情報のヒストグラムは、データの分布を適切に表現するために種類が選ばれる！
10. 3.　統計情報のヒストグラムの種類 • 等間隔ヒストグラム （イメージ）
11. 3.　統計情報のヒストグラムの種類 • 等深度ヒストグラム （イメージ）
12. 3.　統計情報のヒストグラムの種類 • ハイブリッドヒストグラム （イメージはPostgreSQLの等深度＋MCV） 📌 「高頻度の値」は個別のビンに分ける（ MCVの考 え方） 　→「特定の値（例：31）は個別のビンとして扱い、他 の値は統合」 📌 「その他の値」は等深度で分割 　→「データ数を均等にするように、範囲ごとにグ ループ化！」 📌 ビンの幅は可変！データの偏りに応じて調整さ れる 　→「等間隔ではなく、データの分布によって区切り が変わるよ！」 📌 ヒストグラム＋MCVのハイブリッド手法！ 　→「ヒストグラムの精度を上げるため、頻出値を個 別に記録する仕組み！」 ✅ 「データの偏りを考慮し、実行計画をより最適 化！」 判断基準はDBが決め るためざっくり理解で OK
13. 4.　RDBMSごとのヒストグラム仕様の比較 RDBMS 等間隔 等深度 その他 Oracle ❌ なし ✅ あり ハイブリッド（等深度 + MCV） PostgreSQL ❌ なし ✅ あり ハイブリッド（等深度 + MCV） ※Oracleとは相違点あり MySQL ❌ なし ✅ あり シングルトンヒストグラム
14. 5.　各ヒストグラムの活用例（クエリ最適化） • 等間隔ヒストグラム →　データが均等に分布する場合に適切。シンプルな実行計画で処理 ✅ 活用例：ログデータのタイムスタンプ集計（均等な間隔でデータが存在） • 等深度ヒストグラム →　データの密度に応じて範囲を調整。偏ったデータでも適切な実行計画に。 ✅ 活用例：売上データ（特定の価格帯に集中している場合の最適化） • ハイブリッドヒストグラム（等深度 + MCV） →　頻出値（MCV）を個別管理し、その他のデータは等深度でカバー。 ✅ 活用例：アクセスログ解析（特定のIPが大量に記録される場合のフィルタリング）
15. 6.　検証の準備：ヒストグラムの確認方法 • 検証環境 項目 内容 データベース PostgreSQL テストデータ 400万件のデータ 検証対象 pg_stats の MCV & ヒストグラム の影響 スキャン方法の確認 EXPLAIN ANALYZE を使用
16. 6.　検証の準備：ヒストグラムの確認方法 • テーブル構造 & データの分布 カラム名 データの分布 検証の目的 pg_stats 情報 COL_A 一意なデータ (1〜400万) 主キー検索で Index Scan になるか？ MCVなし / ヒストグラムあり COL_B 100000 が 40% を占める MCV の影響で Seq Scan になるか？ MCVあり / ヒストグラムあり COL_C 10000(50%) & 90000(50%) 2種類の場合、MCVの影響はどうか？ MCVあり / ヒストグラムなし COL_D A〜J の 10 種類(均等) カテゴリデータ(10種類)で MCVの影響はどうか？ MCVあり / ヒストグラムなし
17. 6.　検証の準備：ヒストグラムの確認方法 • pg_stats のデータ カラム名 n_distinct most_common_vals（MCV） histogram_bounds correlation COL_A -1 （ユニーク） NULL {112, 38992…} (多いため略) 0.83 COL_B 31803 {100000} （39.95%） {10005, 10899…} (多いため略) -0.65 COL_C 2 {10000, 90000} （約50%ずつ） NULL 1.0 COL_D 10 {E, G, B, F, H, C, A, I, J, D} （約10%ずつ） NULL 0.108 ユニーク な値の数 よく出る値リスト (NULLはMCVを使用しない) 等深度ヒストグラムの境界 (NULLは等深度を使用しない ) カラム値の並びと物理順の相関 1 に近い：IndexScan向き -1に近い：逆IndexScan向き 0 に近い：FullScanの可能性 「物理順」 　→ディスク上のデータの並び 　　（実際の保存順） 「カラム値の順序」 　→カラムの値を大小順に並べた場合の 論理的な概念（物理順とは関係なし）
18. 6.　検証の準備：ヒストグラムの確認方法 • 検証用のクエリ クエリ 意味 SELECT * FROM A WHERE COL_A = 100000; 主キー検索で Index Scan になるか？ SELECT * FROM A WHERE COL_B = 100000; MCV の影響で Seq Scan になるか？ SELECT * FROM A WHERE COL_B = 50000; 低頻度値で Index Scan になるか？ SELECT * FROM A WHERE COL_C = 10000; 二つの山がある分布(バイモーダル分布) はスキャンに影響するか？ SELECT * FROM A WHERE COL_C = 90000; 同上 SELECT * FROM A WHERE COL_D = 'A'; カテゴリ型データで Bitmap Index Scan になるか？
19. 7.　検証：ヒストグラムによるクエリ最適化の影響 • 検証結果 クエリ スキャン方法 COL_A = 100000; Index Scan COL_B = 100000; Seq Scan COL_B = 50000; Index Scan COL_C = 10000; Index Scan COL_C = 90000; Index Scan COL_D = 'A'; Bitmap Index Scan
20. 7.　検証：ヒストグラムによるクエリ最適化の影響 ✅ 結果からの考察（1 / 2） • COL_B = 100000 は Seq Scan になった ◦ 40% のデータを取得するため、Index Scan より Seq Scan が効率的 ◦ correlation = 0 に近いため、Index Scan ではランダムアクセスが発生し、 Seq Scan の方が適切と判断された可能性が高い。 ◦ もし correlation = 1.0 または -1.0 に近ければ、Index Scan が選ばれた可能 性がある。 • COL_B = 50000 は Index Scan になった ◦ MCV に登録されていないため、PostgreSQL はヒストグラムを参照 ◦ 低頻度値なので Index Scan を選択
21. 7.　検証：ヒストグラムによるクエリ最適化の影響 ✅ 結果からの考察（2 / 2） • COL_C = 10000 / 90000 は Index Scan ◦ MCV によって両方の値が登録されていたため、 適切に Index Scan が選ばれた ◦ 対象行数は多いがcorrelation = 1.0（物理順とカラム値が一致）な ので、インデックススキャンが効率的 • COL_D = 'A' は Bitmap Index Scan ◦ 大まかな理由は同上と同様 ◦ correlation = 0.108（ほぼ順番バラバラ）でIndexScan非効率。 しかしCOL_D = 'A' は全体の10％程度のためBitmap Index Scan。 （Bitmap Index Scan：複数行をまとめて処理するのに効率的）
22. 8.　まとめ • 📌 ヒストグラムの役割 ◦ データの分布を記録し、クエリの実行計画を最適化する ◦ WHERE 句のフィルタリングや結合処理で、適切なスキャン方法を選択するために重要 • 📌 ヒストグラムの種類 ◦ 等間隔：均等なビン幅（均等分布向け） ◦ 等深度：データ量を均等に分ける（偏りのあるデータ向け） ◦ ハイブリッド：等深度＋MCV で最適化（製品によって仕様が異なる） • 📌 検証結果のポイント ◦ MCV に登録された値 → 適切に Seq Scan or Index Scan を選択 ◦ correlation = 1.0 に近い場合 → Index Scan が効率的 ◦ correlation = 0 に近い場合 → Seq Scan or Bitmap Index Scan が選ばれる ▪ correlation = 0 に近い場合、Seq Scan が最適になることがある！ ▪ Seq Scan は、連続的なデータ読み込みによりキャッシュ効率が高く、 Index Scan のランダム I/O (O(N)) よりも速くなる場合がある！ • Index Scan はインデックスを参照しつつランダムアクセスが発生 → 取得対象が多いと I/O コスト増 • Seq Scan はディスクを連続的に読み込み、 I/O が最適化される