コンテンツに移動
データベース

Spanner で多対多の関係を解決して最適化する

2023年12月4日
Google Cloud Japan Team

※この投稿は米国時間 2023 年 11 月 28 日に、Google Cloud blog に投稿されたものの抄訳です。

ソフトウェア設計者は、多くの場合、組織、マネージャー、従業員などの「現実世界」のエンティティをモデル化する必要があります。このモデル化の一環として、多対多の関係が頻繁に発生します。これらは、両側のエンティティが複数の関連するインスタンスを持つことができる関係を表すために使用されます。そのエンティティの例として、複数の直属の部下を持つマネージャーや、複数の非直属のマネージャーを持つ従業員が挙げられます。これは、あるデータベース テーブルの 1 つのレコードが別のテーブルの複数のレコードに関連付けられることで、データベースに反映されます。

リレーショナル モデルのコンテキストでは、多対多の関係を実現するには、中間ブリッジ テーブルを使用し、関係についての情報を追加するオプションとともに両側の主キー値のペアを保存する必要があります。このモデルは、整合性の確保と冗長性の軽減の原則を促進します。

多対多の関係のモデル化はリレーショナル データベースに広く適用できるコンセプトですが、そのような関係を大規模かつ効率的に解決するには、Spanner 固有の最適化とトレードオフを考慮する必要があります。

これらのオプションは主に、この記事で説明する最もよく使用されるアクセス パターンに依存します。

サンプル スキーマ

ユーザーがさまざまなグループに参加できる一方で、グループには複数のユーザーを含めることができる次の例について考えてみましょう。正規化のプロセスにより、Membership(メンバーシップ)という名前の中間テーブルが作成され、この関係が解決されます。以下の具体例では、関係に関する追加情報が、enrolled(登録済み)に保存されます。

https://storage.googleapis.com/gweb-cloudblog-publish/images/1_-_SCHEMA.max-600x600.png
読み込んでいます...

次に、さまざまなクエリパターンと、それらがパフォーマンスに及ぼす影響、および一般的な Spanner パフォーマンスの最適化について見てみましょう。Spanner の機能を使用してクエリを最適化する方法を説明します。

バランスの取れたアクセス

n:m の関係には基本的に 2 つのクエリパターンのパスがあります。今回の例では、次のようになります。

  1. ユーザー X が参加しているグループ: 特定のユーザーまたはユーザーのセットが関連付けられているグループを取得します。
  2. グループ Y に参加しているユーザー: ユーザーが属する特定のグループまたは一連のグループのユーザーを取得します。

多くの場合、主要なクエリパターンが事前にわからないため、そのような場合は、両方に対応するバランスの取れた最適化を選択する必要があります。

クエリパターン 1: ユーザー X が参加しているグループ

読み込んでいます...

このクエリパスは、特定のユーザーについて、そのユーザーが関連付けられているグループを解決する必要があります。Spanner テーブル内のレコードは、主キー(PK)によって辞書編集的に編成されているため、効率的な検索のために自動的にインデックスが付けられます。したがって、この特定のクエリは、membership テーブルの主キーが複合キーであり、そのプレフィックスが user_id であるため、関係を解決するために適度に速く実行されます。

クエリパターン 2: グループ Y に参加しているユーザー

逆クエリ、つまり特定のグループに属するすべてのユーザーを検索すると、(データの形状に応じて)実行速度が大幅に遅くなります。

読み込んでいます...

これは、制約された group_id での結合を解決するには、membership テーブルに対してテーブルのフルスキャンを実行する必要があるためです。

https://storage.googleapis.com/gweb-cloudblog-publish/images/2_-_TABLE_SCAN_MEMBERSHIP.max-900x900.png

上記のクエリをクエリ 1 と同じレイテンシ範囲にするためには、インデックスを作成する必要があります。

読み込んでいます...

これにより、効率的なインデックス スキャンが行われます。

https://storage.googleapis.com/gweb-cloudblog-publish/images/3_-_INDEX_SCAN_GROUP_ID.max-600x600.png

インデックスを追加することでクエリのパフォーマンスが向上しましたが、その代償として(インデックスへの)追加の書き込みが発生し、レイテンシが増加します。

ただし、クエリには最適化の余地がまだあります。membership テーブルには、SELECT 句を通じてクエリが実行される関係(enrolled タイムスタンプ)に関する追加データが保持されます。その結果、チェーンのさらに上位のプランでは、分散クロス適用が行われます。これは、本質的に、非 PK 属性を取得するためのインデックス テーブルからの追加の往復です。

https://storage.googleapis.com/gweb-cloudblog-publish/images/4_-_DISTRIBUTED_CROSS_APPLY.max-800x800.png

これをさらに最適化するには、Spanner の STORING 句を使用して属性(enrolled)をインデックスに保存することをおすすめします。

読み込んでいます...

追加のストレージと書き込みレイテンシのわずかな増加という犠牲が伴いますが、利点としてクエリの速度が向上することは注目に値します。

外部キー関係

便利な選択肢としては、関係のバックアップ インデックスを自動的に作成する Spanner の外部キー関係を利用することです。

読み込んでいます...

参照元の列に対して 1 つ目のインデックスが作成されます。また、参照先の列に対して 2 つ目のインデックスが作成されます。この場合、外部キーは参照先テーブルの主キーを指すため、参照先テーブルの 2 つ目のインデックスは必要ありません。

バックアップ インデックスはユーザー作成のインデックスではないため、SPANNER_IS_MANAGED 値が true のデータベースの情報スキーマ(NFORMATION_SCHEMA.INDEXES)でのみ表示されます。

これらの特定のクエリ例で外部キーに依存することの欠点は、enrolled 属性をバックアップ インデックスに同時保存してクエリ処理を高速化するための STORING 句を指定できないことです。

高スケール向けのインターリーブによるレイテンシの向上

n:m 関係のアクセス パターンは、多くの場合一方向です。たとえば、ほとんどの場合、ユーザーが所属するグループがクエリされる可能性があります。このシナリオでは、Membership テーブルを User テーブルにインターリーブすることが合理的です。

読み込んでいます...

この最適化パターンは、上記の方法と比較して、大量のレコードをカバーする関係に対応できる規模の面で優れています。

大規模なシナリオで双方向の低レイテンシ クエリを処理することが要件の場合、解決策は、スキーマを非正規化し、インターリーブ方式で両方のクエリ方向を処理する 2 つの membership テーブルを作成することです。

読み込んでいます...

欠点は、アプリケーションが関係を解決するために 2 つの中間テーブル(membership_user、membership_group)にデータを複製する必要があることです。結果として得られるテーブルは、健全なリレーショナル モデル(つまり 3NF)に関して正規化されておらず、すべての欠点があり、その結果、書き込みレイテンシ バジェットも増加します。

まとめ

この記事では、多対多の関係をモデル化する場合のスキーマ設計を決定する際に考慮すべき Spanner 固有の最適化とトレードオフについて説明しました。Spanner でのこのタイプの関係に対する最適化戦略を選択する際は、各アプローチに関連するトレードオフを考慮しつつ、アプリケーションの特定のアクセス パターンとパフォーマンス要件に基づくものにする必要があります。データベース スキーマを慎重に設計し、Spanner の機能を活用することで、多対多の関係における効率とクエリ パフォーマンスのバランスを取ることができます。

この投稿のガイダンスを実践するには、こちらのクイックスタートを確認することをおすすめします。ここには、Spanner のインスタンス、データベース、テーブルを作成する方法に関する情報が記載されています。

-Spanner、シニア スタッフ ソリューション アーキテクト Szabolcs Rozsnyai

-Spanner、シニア スタッフ ソリューション アーキテクト Karthi Thyagarajan

投稿先