リファレンスアーキテクチャ: データウェアハウス / 分析
分析クエリ (レポート、集計、広範なスキャン) は OLTP のトランザクションとは根本的に異なる: 数は少ないが非常に広範で長時間実行されるクエリ。このツリーは、分析を OLTP から分離する判断を起点に、ロード方式 (ETL vs ELT)、列指向/columnstore ストレージ、スタースキーマによるモデリング、パーティショニング + 圧縮、マテリアライズドビューによる事前計算などのコンポーネントを、同時実行性とスループットのトレードオフを考慮しながら段階的に説明する。
分析ワークロードは OLTP と同じデータベースで実行されているか?
最もよくある間違いは、稼働中のトランザクション DB で重いレポートを実行し、OLTP のレイテンシを悪化させること。最初の判断は分離: 分析はまだ OLTP インスタンスで実行されているのか、別の分析環境を構築しているのか、それともデータをそこへ移す方法を決めようとしているのか?
- レポートはまだ稼働中の OLTP で実行されており、OLTP を遅くしている
- 別のウェアハウスがある。データをどうロードすべきか?
- ウェアハウスはあるがクエリがまだ遅い。どうモデリングすべきか?
変換はどこで行うか: ロード前か、ロード後か?
ソースからウェアハウスにデータを移す際、ロード前に別の処理層でクレンジング/変換を行う (ETL) のか、それともまず生データをウェアハウスにロードし、強力なウェアハウスエンジン内で SQL によって変換する (ELT) のか? 判断は、ウェアハウスの処理能力とスキーマの柔軟性の必要性によって決まる。
- 変換は複雑/外部で行う。ロード前に実施する
- ウェアハウスは強力。生データでロードし、内部で SQL によって変換する
クエリのボトルネックはストレージのレイアウトか、データモデルか?
分析が遅い場合、主な対策は 2 つある。行指向ストレージでは広範なスキャンが行全体を読み取るが、列指向ストレージは必要な列のみを読み取り、圧縮効率も高い。モデル側では、過度な正規化は多数の結合を生みクエリを遅くする。スタースキーマ (ファクト + ディメンション) は分析を簡素化する。
- 広範な集計スキャンが遅い。読み取る列は少ない
- 結合が多すぎる、複雑な正規化スキーマ
- 同じ重いレポートが繰り返し同じ結果を出している
推奨: 分析を OLTP から分離する
トランザクションと分析のワークロードは、同じエンジン上では互いを圧迫する: 長いレポートがロック/IO を保持し、短いトランザクションを遅らせる。分析を別のターゲット (リードレプリカまたは別の分析用ウェアハウス) に移すことで、双方の負荷が軽減される。
推奨: ETL (ロード前に変換)
ETL では、データをソースから抽出し (Extract)、別の処理層でクレンジングと変換を行い (Transform)、その後にウェアハウスにロードする (Load)。ウェアハウスには、クリーンでモデルに準拠したデータのみが入る。
推奨: ELT (ウェアハウス内で変換)
ELT では、まず生データをウェアハウスにロードし、変換はウェアハウス自体の強力な SQL エンジン内で行う。最新の列指向ウェアハウスは処理能力が高いため、変換をデータのもとへ持っていくのではなく、データを一度移して内部で処理する。
推奨: 列指向 / columnstore ストレージ + 圧縮
分析クエリは通常、全行にわたって少数の列をスキャンする。列指向ストレージはデータを列単位で保持し、必要な列のみを読み取る。類似した値が隣接するため圧縮効率が非常に高く、IO が劇的に減少する。
推奨: スタースキーマ (ファクト + ディメンション)
分析モデルでは、メジャーを保持するファクトテーブルが中心にあり、その周囲を説明的なディメンションテーブルが囲む。過度に正規化されたスキーマと比べて結合の数が減り、クエリがシンプルになり、エンジンがより良い実行計画を生成する。
推奨: マテリアライズドビューで事前計算する
同じ重い集計 (日次売上、月次サマリ) が繰り返し実行され同じ結果を出す場合は、結果をマテリアライズドビューに物理的に保存し、レポートが事前計算された結果を読み取るようにする。重い計算は一度だけ実行され、クエリは即座に返る。