データベースの統計情報の収集
目次
統計情報とは
データベースの統計情報とは、データベースのクエリオプティマイザが最適なクエリ実行プランを決定するために使用するメタデータです。統計情報には、テーブルやインデックスに含まれるデータの分布や行数、カラムごとの値の分布などが含まれます。
統計情報は、クエリの効率を大幅に左右します。正確な統計情報があれば、クエリオプティマイザは適切なインデックスを使用したり、最適な結合順序を選んだりして、クエリの実行を高速化できます。しかし、統計情報が古いか不正確だと、非効率なクエリプランが選ばれ、処理時間が長くなるリスクがあります。
統計情報の役割
統計情報の役割は、以下の通りです。
-
データ分布の把握: データベース内のデータがどのように分布しているかを把握することで、効率的なクエリ実行を可能にします。例えば、カラムの値が一意か、重複が多いかによって、クエリプランナーがインデックススキャンやテーブルスキャンを選択します。
-
クエリプランの最適化: クエリオプティマイザは、統計情報を使用して各種クエリ演算のコストを推定し、最適な実行プランを決定します。正確な統計情報があることで、クエリの実行コストが最小化されます。
-
インデックスの効果的な使用: 統計情報が最新であれば、クエリオプティマイザはどのインデックスを使用すべきかを適切に判断します。これにより、データベース全体をスキャンするよりも、インデックスを使った効率的なデータ取得が可能になります。
統計情報が不足するとどうなるか？
統計情報が不足したり古くなったりすると、以下の問題が発生します。
-
クエリのパフォーマンス低下: 統計情報が不正確だと、クエリオプティマイザが最適でない実行プランを選ぶことがあり、クエリの実行時間が長くなる可能性があります。
-
インデックスの無駄遣い: 古い統計情報を基にインデックスを使用すると、むしろインデックスを使用しない方が効率的な場合に、無駄なインデックススキャンが行われることもあります。
-
システムリソースの浪費: 非効率なクエリがシステムリソース（CPU、I/O、メモリ）を過剰に消費し、他のクエリにも悪影響を与えることがあります。
統計情報の収集
データベースで効率的なクエリを実行するためには、統計情報を定期的に収集・更新することが重要です。統計情報は、データベースの状態に応じて動的に変化するため、収集頻度や方法に注意を払う必要があります。主に、サンプリングと完全スキャンという2つの手法があります。
サンプリングと完全スキャンの違い
サンプリング
サンプリングは、データベース全体をスキャンするのではなく、データの一部を抽出して統計情報を収集する方法です。サンプリングの特徴は、収集コストが低く、高速に処理できる点です。ただし、サンプリング率が低いと統計情報の精度が下がり、クエリパフォーマンスに影響を与えるリスクがあります。
-
メリット:
- 収集時間が短い
- リソースの消費が少ない
- 大規模データベースでも負荷を抑えて統計情報を更新できる
-
デメリット:
- 統計情報の精度が劣ることがある
- データの偏りが正確に反映されない場合がある
完全スキャン
完全スキャンは、データベース全体をスキャンして統計情報を収集する方法です。正確な統計情報が得られますが、データ量が多い場合、収集コストが高く、処理に時間がかかるというデメリットがあります。
-
メリット:
- 統計情報の精度が高い
- データの偏りや分布を正確に把握できる
- クエリオプティマイザが最適な実行プランを選びやすい
-
デメリット:
- 大規模データベースでは時間とリソースを大量に消費する
- 更新頻度が少ない場合は、すぐに統計情報が古くなる可能性がある
実行計画における統計情報の役割
統計情報は、クエリオプティマイザが実行プランを最適化するために欠かせない要素です。クエリ実行時にどのインデックスを使うべきか、どの結合方法を選ぶべきか、さらにはテーブルスキャンを行うべきかといった判断が、この統計情報に基づいて行われます。
-
インデックスの選定:
クエリオプティマイザは、統計情報を基にインデックスの有無や効果を判断します。特定のカラムが一意であるか、値の分布に偏りがあるかを統計情報で判断し、適切なインデックスを使用します。 -
結合順序の最適化:
複数のテーブルを結合する際、統計情報が各テーブルのサイズやデータの分布を示すことで、クエリオプティマイザは最も効率的な結合順序を選択します。 -
スキャン方法の選択:
統計情報によって、クエリオプティマイザはテーブルスキャンを行うべきか、インデックススキャンを行うべきかを決定します。統計情報が正確であれば、より効率的なクエリ実行が可能です。
サンプリングと完全スキャンの違い
データベースにおける統計情報の収集方法には、主にサンプリングと完全スキャンの2つのアプローチがあります。これらはデータベースのサイズやパフォーマンス要件に応じて選択され、クエリの実行効率に大きく影響を与えます。
サンプリング
サンプリングは、データベース全体をスキャンせずに、データの一部だけを抽出して統計情報を収集する手法です。この方法は、大規模データベースで特に有効です。
-
メリット:
- 収集時間が短い: サンプリングはデータの一部をスキャンするため、全体スキャンに比べて時間が大幅に短縮されます。
- リソース消費が少ない: 大規模データベースの場合、リソースを大量に消費せずに統計情報を更新できるため、サーバーの負荷を抑えられます。
- 適用例: 例えば、eコマースのように大量のトランザクションが発生する環境では、サンプリングにより効率的に統計情報を更新し、パフォーマンスを維持することができます。
-
デメリット:
- 精度が低い場合がある: サンプリング率が低すぎると、統計情報の精度が低くなり、クエリオプティマイザが誤った判断をする可能性があります。特に、データに偏りがある場合、サンプリングによる情報では偏りが正確に反映されないことがあります。
- データの偏りが反映されにくい: 一部のカラムに極端な偏りがある場合、サンプリングによって得られた統計情報がその偏りを正確に反映しないことがあります。この場合、クエリプランの選択が最適でなくなることがあります。
完全スキャン
完全スキャンは、データベース全体をスキャンして統計情報を収集する方法です。この方法は、統計情報の精度が高く、データの偏りや分布を正確に把握できる点が特徴です。
-
メリット:
- 正確な統計情報が得られる: データベース全体を対象にスキャンするため、統計情報が非常に正確です。クエリオプティマイザは、これに基づいて最適なクエリプランを選択できます。
- データの偏りを正確に把握できる: 特に、カラム内のデータに偏りがある場合や、一意性の高いデータが含まれる場合、完全スキャンはその分布を正確に捉えます。
- 適用例: 統計情報がパフォーマンスに大きく影響する金融システムや、データの正確性が要求されるバッチ処理において、完全スキャンが有効です。
-
デメリット:
- 処理コストが高い: データベース全体をスキャンするため、処理にかかる時間やリソースの消費が非常に大きくなります。特に、巨大なデータベースではこのコストが顕著です。
- 更新頻度が低くなる: リソースのコストが高いため、統計情報を頻繁に更新することが難しくなり、結果的に統計情報が古くなるリスクがあります。
選択のポイント
サンプリングと完全スキャンのどちらを選ぶかは、データベースの規模やパフォーマンス要件に依存します。
- 大規模データベースの場合は、サンプリングが効率的です。ただし、サンプリング率を適切に設定しないと、統計情報の精度が落ち、クエリパフォーマンスが悪化する可能性があります。
- 正確な統計情報が求められるケースでは、完全スキャンが適しています。特に、クエリパフォーマンスがシステムの成否に直結する金融やミッションクリティカルな環境では、完全スキャンによる高精度な統計情報が必須です。
サンプリングと完全スキャンの選択基準
統計情報の収集方法として、サンプリングと完全スキャンのどちらを選択するかは、データベースの特性やシステムの要件に応じて決定されます。それぞれの特性を理解し、適切に使い分けることがクエリパフォーマンスの最適化に重要です。
サンプリングを選択する基準
-
大規模なデータベース
- データベースが非常に大規模で、全体をスキャンするには多大な時間とリソースがかかる場合は、サンプリングが有効です。
- 適用例: 数億行以上のレコードがあるデータベースや、ビッグデータ処理では、サンプリングを行うことでパフォーマンスを維持しつつ、必要な統計情報を効率的に収集できます。
-
リアルタイム性が求められる場合
- eコマースサイトやSNSのように、データが頻繁に更新されリアルタイム性が求められる場合は、サンプリングを行うことで統計情報の更新が高速に行われます。
- 適用例: 例えば、ユーザーが多くのトランザクションを同時に行う環境では、統計情報の更新に時間をかけていられないため、サンプリングで効率的に統計情報を収集する方が適切です。
-
リソースの節約が必要な場合
- データベースサーバーのリソース（CPU、メモリ、I/O）の負荷を抑えたい場合、サンプリングによる統計情報の収集が効果的です。
- 適用例: 小規模の開発環境や、共有リソースを使用している場合、完全スキャンによる負荷を避けるためにサンプリングが利用されます。
完全スキャンを選択する基準
-
正確な統計情報が必要な場合
- サンプリングでは統計情報の精度に限界があるため、データの偏りが激しい場合や、クエリパフォーマンスに大きな影響を与えるデータベースでは、完全スキャンを行うべきです。
- 適用例: 特定のカラムに一意の値が多い場合や、データ分布が不均一な場合、完全スキャンを使用することで正確な統計情報が得られ、クエリオプティマイザの判断が改善されます。
-
中小規模のデータベース
- データ量が少なく、スキャンしてもリソースに大きな負荷がかからない場合は、完全スキャンが適しています。
- 適用例: 小規模な業務データベースや、更新頻度が低いシステムでは、完全スキャンを定期的に行うことで高精度の統計情報が得られます。
-
バッチ処理や夜間メンテナンスで使用する場合
- 日中の運用負荷が軽減される夜間や、バッチ処理のタイミングで完全スキャンを実行することが効果的です。これにより、日常業務のパフォーマンスを犠牲にせず、正確な統計情報を維持できます。
- 適用例: 金融機関や医療機関など、正確な処理が求められる環境では、夜間に完全スキャンを実行して、正確な統計情報を確保する運用がよく行われます。
サンプリングと完全スキャンの併用
実際には、サンプリングと完全スキャンを併用することで、効率と精度のバランスを取ることが一般的です。例えば、定期的にはサンプリングを行い、重要なタイミング（システムのメンテナンス時など）では完全スキャンを実施することで、クエリパフォーマンスとリソース効率を両立できます。
統計情報が不適切な場合のリスク
統計情報が古くなったり、不正確な状態のままだと、データベースのクエリオプティマイザは最適な実行計画を選択できず、パフォーマンスに悪影響を与える可能性があります。以下に統計情報が不適切な場合に発生するリスクを説明します。
1. クエリパフォーマンスの低下
統計情報が古くなっていると、クエリオプティマイザは不正確なデータを基に最適化を行い、非効率なクエリ実行プランを選択する可能性があります。これにより、処理時間が長くなるリスクが高まります。
- 例: 古い統計情報を使用している場合、インデックスがあるにもかかわらずテーブルスキャンを選択してしまうことがあり、パフォーマンスが大幅に低下します。逆に、サンプリングや完全スキャンによって最新の統計情報が適用されている場合、クエリオプティマイザは正確なプランを選択し、パフォーマンスが向上します。
2. リソースの過剰消費
統計情報が不適切だと、非効率なクエリプランが選ばれることで、メモリやCPU、ディスクI/Oなどのリソースが無駄に消費されます。これにより、他のクエリやアプリケーションのパフォーマンスに悪影響を与えることがあります。
- 補足: 特に、大規模データベースで統計情報が不正確だと、クエリが非常に重くなり、システム全体のレスポンスが低下する原因となります。
3. 結果の信頼性の低下
統計情報が正確でない場合、クエリ結果の処理順序や実行計画に影響を与え、データ取得の効率が低下します。特に、結合や集計処理において、統計情報が古いと計算結果が遅くなり、処理に支障をきたす可能性があります。
4. 実行計画の違いを比較
以下は、統計情報が不適切な場合と適切に更新された場合の実行計画の違いを示す画像です。この検証では、データの20％が更新された後、サンプリングと完全スキャンを適用して実行計画を比較しました。
確認内容 (処理時間を主に確認)
サンプリング
-
観点: サンプリング率何%で試験実施前の処理時間に近づくか
- 試験実施前（統計情報が最新）
- 全データの12.5％更新後
- 10％サンプリングし実行計画を取得
- 全データの12.5％更新後
- 15％サンプリングし実行計画を取得
- 全データの12.5％更新後
- 20％サンプリングし実行計画を取得
完全スキャン
-
観点: 更新後完全スキャンで試験実施前の処理時間に近づくか
- 試験実施前（統計情報が最新）
- 全データの12.5％更新し実行計画を取得
- 完全スキャンを実施し実行計画を取得
環境
- データベース: PostgreSQL
- テーブル名: PEOPLE
- データ数: 400万件
-
インデックス
- インデックス名: Idx_people_duplication_forty_type
- 指定カラム: duplication_forty_type
-
カラム
- duplication_forty_type（INTEGER型、重複のあるデータ）
- 値は0～39の40種類
- 1種類あたり10万件（全体の2.5％）
- duplication_forty_type（INTEGER型、重複のあるデータ）
-
検証対象のクエリ:
SELECT * FROM people WHERE duplication_forty_type = 1
実践
サンプリング
- 試験実施前（統計情報が最新）
-
全データの12.5％更新後
UPDATE public.people SET duplication_forty_type = duplication_forty_type + 100 WHERE duplication_forty_type IN(5, 6, 7, 8, 9);
-
10％サンプリングし実行計画を取得
SET default_statistics_target = 10; ANALYZE public.people;
-
15％サンプリングし実行計画を取得
SET default_statistics_target = 15; ANALYZE public.people;
-
20％サンプリングし実行計画を取得
SET default_statistics_target = 20; ANALYZE public.people;
完全スキャン
- 試験実施前（統計情報が最新）
-
全データの12.5％更新後
UPDATE public.people SET duplication_forty_type = duplication_forty_type + 100 WHERE duplication_forty_type IN(5, 6, 7, 8, 9);
-
完全スキャン
SET default_statistics_target = 100; ANALYZE public.people;
処理時間の変化（実行計画のEXCLUSIVEを使用）
まとめ
今回の検証および実務での実装例を通して、統計情報の適切な管理がデータベースのクエリパフォーマンスに与える影響の大きさを確認しました。統計情報が最新であれば、クエリオプティマイザが正しい実行計画を選択し、パフォーマンスの向上が期待できます。一方で、統計情報が不正確な場合、処理時間が長くなり、システム全体のリソースに負荷がかかるリスクが高まります。
サンプリングと完全スキャンの使い分け
サンプリングによる統計情報の更新は、大規模データベースでの効率的な手法ですが、精度に限界があります。一方、完全スキャンは精度が高いものの、リソースの消費が大きいため、システムの負荷状況に応じた適切な使い分けが重要です。
統計情報管理の重要性
データベースの規模やシステム要件に応じて、統計情報を適切に管理することが、システム全体のパフォーマンスを最適化するために欠かせません。定期的な統計情報の更新や、自動化の導入を検討することで、運用の効率化とクエリパフォーマンスの向上を図ることができます。