リレーショナル ランキングのリファレンス アーキテクチャ

アーキテクチャの図

アーキテクチャのサービス

• Azure Functions
• Azure SQL Database

アーキテクチャの考慮事項

このリファレンス アーキテクチャでは、ゲームのプレイヤー名と、ゲームの 1 つ以上のディメンションと期間にわたって集計された上位の対戦相手のランクを示すものとして "ランキング" を定義します。

このリファレンス アーキテクチャでは、次のような大規模な要件を想定しています。

• 100 万人のプレイヤーが 1 日あたり 4 回ゲームをプレイします。 これは以下に相当します。
  • 1 日 あたりのゲーム回数は 400 万回 (1 秒あたり約 46 回)
  • 1 か月あたりのゲーム回数は約 1 億 2,100 万回
  • 1 年あたりのゲーム回数は約 14 億 6,000 万回
• 最終的な個々のゲーム スコアは、ゲームの完了直後にプレイヤーに表示される必要があります。
• プレイヤーは自分のランクに基づいて毎日のリワードを獲得し、ランクの定義に使用されるグローバル ランキングは 4 分ごとに簡単に更新される (十分な実行時間のバッファーをとって実行される) 必要があります。
• ゲーム履歴は "ホット パス" の外に定期的にアーカイブされる必要があります。 アーカイブは迅速に行われ、同時実行のゲーム結果アクティビティに影響を及ぼさないようにする必要があります。 また、このリファレンス アーキテクチャのデータ層は、1 つまたは複数のプラットフォーム (Xbox、PlayStation、デスクトップ、iOS、Android、その他) でアクセスできる単一のゲームを対象として想定しています。

このリファレンス アーキテクチャは複数のゲームをサポートするように拡張できますが、ゲーム別に分離することは、個々のテナントのスケーリングと "迷惑な隣人" や同時実行の問題の回避に役立ちます。

ゲーム ランキングのパフォーマンスとスケーラビリティのためのサービス層

ゲームの起動と継続的かつ大規模なスループットのための十分なリソースを確保するために、Premium または Business Critical サービス層モデルにデータ層を実装することを検討してください。 これらのサービス層は、基になる SSD ストレージからの低待機時間の応答が必要なゲーム向けに設計されており、基盤となるインフラストラクチャに障害が発生した場合に迅速な回復を可能にします。 Business Critical では、プライマリ データベースの無料の読み取り可能なセカンダリ レプリカを使用できます。

注意

このアーキテクチャで推奨されているすべてのスキーマとクエリは、Azure SQL Database Business Critical Gen 5 サービス層で、14 個の vCore と 16 億行のゲームスコア履歴を使用してテスト済みです。

データベース スキーマ

このリファレンス アーキテクチャでは、次のベース テーブルと補助的な制約およびインデックスを使用します。 各テーブルは "実用最小限の" スキーマを示すことを目的としており、ゲームの要件に基づいて拡張できます。

このスキーマは、次の利用シナリオに対応します。

• 個々のゲームの完了後に、最終的なレベルとスコアが GameCompletion テーブルに挿入されます。 このテーブルは、新しい行の挿入や、ランキング スナップショットのランク付け結果の定期的な計算のためにアクセスされる必要があります。
• GameCompletion テーブルには、理想的には、“ホット パス” のデータ (ランク付けの計算に頻繁に使用されることが想定されるデータ) のすべての行が含まれます。 アクセス頻度の低い古いデータには、GameCompletionHistory テーブルを使用します。このリファレンス アーキテクチャで、GameCompletion から古いデータを削除するのにかかる時間を最小限に抑えるために高速なパーティション切り替えを利用する方法を示します。 GameCompletion のパーティション関数とスキームについては、後で説明します。これにより、効率的なデータ アーカイブが容易になります。
• GamePlatform テーブルと Player テーブルには、ゲーム プラットフォームとプレイヤー関連の補足データが含まれます。 このデータは GameCompletion テーブルと GameCompletionHistory テーブルに結合できます。
• LeaderboardSnapshotテーブルには、計算されたランキングのランク付け結果が含まれます。
  • これは、ランク付け結果を返すためにアプリケーションによって使用されるテーブルです。
  • このテーブルは、必要なスケジュールと頻度 (たとえば、現在の日次ランキングのランク付けを 4 分ごとに計算するなど) に基づいて、別のプロセスによって設定される必要があります。
  • データは GameCompletion テーブルを使用して計算され、必要に応じて GameCompletionHistory テーブルも使用されます。
  • 結果へのアクセスには常に LeaderboardSnapshot テーブルを使用します (同じランク付け結果を GamePlatform から繰り返し再計算するのを避けます)。
• RankResultType テーブルには、ゲームに必要なさまざまな種類のランキング スナップショットが含まれます。 このテーブルの主キーを LeaderboardSnapshot で使用して、取得されるランク付け結果の種類を示すことができます。

以下のセクションで、前述した各オブジェクトのスキーマについて詳しく説明します。

Player テーブル

このテーブルには、結合のパフォーマンスを最適化するために生成されたプレイヤーの代理キー、プレイヤーの “ナチュラル キー”、およびエイリアス列が含まれます。

注意

ナチュラル キーは、"現実世界の" 属性 (たとえば Xbox Live ID) に基づく一意のキーです。

代理キーは、システムによって生成される一意のキーです。 この種類のキーは、実際の固有の意味はありませんが、パフォーマンスを最適化する (最適なキーのデータ型を選択できるようにする) ために使用でき、後からのナチュラル キーの変更への依存も最小限に抑えることができます。 たとえば、ゲーム プレイヤーが Live ID を変更した場合、代理キーを使用していれば、すべてのプレイヤー ゲーム履歴に対してナチュラル キーの複数の変更が必要になる代わりに、Player テーブルに対する 1 つの変更だけで済みます。

設定は、コア ゲーム認証データ ソースとシステムに基づいて行われるものとします。

CREATE TABLE dbo.Player
(
    PlayerSurrogateKey BIGINT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    PlayerNaturalKey UNIQUEIDENTIFIER NOT NULL,
    PlayerGamerAlias NVARCHAR(256) NOT NULL
);
GO

GamePlatform テーブル

このテーブルは、特定のゲームをプレイできるプラットフォーム (Xbox、iOS、Android など) を示します。

CREATE TABLE dbo.GamePlatform
(GamePlatformSurrogateKey BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
 GamePlatformName NVARCHAR(256) NOT NULL
);
GO

INSERT dbo.GamePlatform
(GamePlatformName)
VALUES (N'Xbox'), (N'iOS'), ('PS'), ('Android');
GO

GameCompletion のパーティション関数とスキーム

大規模なテーブルはすべて、可能であればパーティション分割に対応する必要があります。 これにより、時間の経過に伴うアーカイブと、"コールド パス" データのスケール アウトが可能になります。 次のパーティション関数とスキームは、連続したテーブルの例に使用されます。 この例では、パーティション分割が 2019 年の各月に実行され、ゲームの要件とデータ保持ポリシーに基づいて時間の経過と共に拡張 (分割と結合) することができます。

CREATE PARTITION FUNCTION GameCompletionMonthPartitionFunction (DATETIME2)
AS RANGE RIGHT FOR VALUES
(   '20190201',
    '20190301',
    '20190401',
    '20190501',
    '20190601',
    '20190701',
    '20190801',
    '20190901',
    '20191001',
    '20191101',
    '20191201'
);
GO

CREATE PARTITION SCHEME GamePartitionMonthPartitionScheme
AS PARTITION GameCompletionMonthPartitionFunction
ALL TO
    (
        [PRIMARY]
    );
GO

GameCompletion テーブル

GameCompletion テーブルには、レベル、ゲーム完了日、プレイヤー、ゲーム プラットフォーム別の最終スコアが設定されます。 このテーブルは、ゲームの完了日でパーティション分割されます。

CREATE TABLE dbo.GameCompletion
(
    GameCompletionSurrogateKey BIGINT IDENTITY(1, 1),
    PlayerSurrogateKey BIGINT NOT NULL,
    GamePlatformSurrogateKey BIGINT NOT NULL,
    GameCompletionLevel INT NOT NULL,
    GameCompletionScore BIGINT NOT NULL,
    GameCompletionDate DATETIME2 NOT NULL
) ON GamePartitionMonthPartitionScheme (GameCompletionDate);
GO

また、非常に大きな結果セットに対して低遅延の分析ランク付けクエリを有効にするために、このテーブルにクラスター化された列ストア インデックスを追加します。

CREATE CLUSTERED COLUMNSTORE INDEX CCI_GameCompletion
ON dbo.GameCompletion
ON GamePartitionMonthPartitionScheme(GameCompletionDate);
GO

データの品質をサポートするために、次の主キーと 2 つの外部キー制約をこのテーブルに追加します。

ALTER TABLE dbo.GameCompletion
ADD CONSTRAINT PK_GameCompletionOriginal
    PRIMARY KEY NONCLUSTERED (
                                 GameCompletionSurrogateKey,
                                 GameCompletionDate
                             ) ON GamePartitionMonthPartitionScheme(GameCompletionDate);
GO

ALTER TABLE dbo.GameCompletion
ADD CONSTRAINT FK_GameCompletion_PlayerSurrogateKeyOriginal
    FOREIGN KEY (PlayerSurrogateKey)
    REFERENCES dbo.Player (PlayerSurrogateKey);
GO

ALTER TABLE dbo.GameCompletion
ADD CONSTRAINT FK_GameCompletion_GamePlatformSurrogateKeyOriginal
    FOREIGN KEY (GamePlatformSurrogateKey)
    REFERENCES dbo.GamePlatform (GamePlatformSurrogateKey);
GO

GameCompletion テーブルに対するアドホック アクティビティは最小限に抑えてください。 このテーブルは、受信スコア行 (1 秒あたり約 50 行) の高速取り込みに使用される必要があります。 GameCompletion にアクセスしてランク付け結果を得る代わりに、顧客に表示されるレポートの作成とアプリケーション層のキャッシュには LeaderboardSnapshot テーブルを使用します。

GameCompletionHistory テーブル

GameCompletionHistory テーブルは、"コールド パス" のアクセスで履歴行を保持することを目的としています。 GameCompletion テーブルの基盤となるパーティション スキームに一致するような履歴テーブルを保持することを検討してください。 これにより、GameCompletion から GameCompletionHistory への高速なパーティション切り替えが可能になります (これについては後で説明します)。

CREATE TABLE dbo.GameCompletionHistory
(
    GameCompletionSurrogateKey BIGINT NOT NULL,
    PlayerSurrogateKey BIGINT NOT NULL,
    GamePlatformSurrogateKey BIGINT NOT NULL,
    GameCompletionLevel INT NOT NULL,
    GameCompletionScore BIGINT NOT NULL,
    GameCompletionDate DATETIME2 NOT NULL
) ON GamePartitionMonthPartitionScheme (GameCompletionDate);
GO

CREATE CLUSTERED COLUMNSTORE INDEX CCI_GameCompletionHistory
ON dbo.GameCompletionHistory
ON GamePartitionMonthPartitionScheme(GameCompletionDate);
GO

ALTER TABLE dbo.GameCompletionHistory
ADD CONSTRAINT PK_GameCompletionHistory
    PRIMARY KEY NONCLUSTERED (
        GameCompletionSurrogateKey,
        GameCompletionDate
    ) ON GamePartitionMonthPartitionScheme(GameCompletionDate);
GO

RankResultType テーブル

RankResultType テーブルには、ゲームに対して生成するランク付けカテゴリが含まれます。 下の例には、全期間、日、週、月、年に対するランク付けが含まれます。 実際のランキング結果とは別のテーブルでこれを参照させることで、将来のランク付けオプションのスケーラビリティを実現できます。

CREATE TABLE dbo.RankResultType
(
    RankResultTypeSurrogateKey SMALLINT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    RankResultTypeDescription NVARCHAR(256) NOT NULL
);
GO

INSERT dbo.RankResultType
(
    RankResultTypeDescription
)
VALUES
('All Time'),
('Day'),
('Week'),
('Month'),
('Year');
GO

LeaderboardSnapshot テーブル

LeaderboardSnapshot テーブルには、可能なランク付けの種類の配列に対する、特定の時点のランキングのランク付け結果が含まれます。 これにより、ソース スコア データに対してランク付けの再計算を繰り返し行うことなく、最新の結果や履歴の結果への反復可能なアクセスが可能になります。 また、ランク付け結果の種類に外部キー制約を追加します。 (特定の時点のレベル、スコア、日付を保持して) 計算後のデバッグを容易にするために、一部の列が元のスコア テーブルから保持されていることにも注意してください。

CREATE TABLE dbo.LeaderboardSnapshot
(
    PlayerSurrogateKey BIGINT NOT NULL,
    GamePlatformSurrogateKey BIGINT NOT NULL,
    LeaderboardSnapshotDate DATETIME2 NOT NULL,
    GameCompletionLevel INT NOT NULL,
    GameCompletionScore BIGINT NOT NULL,
    GameCompletionDate DATETIME2 NOT NULL,
    RankResultTypeSurrogateKey SMALLINT NOT NULL,
    RankResult BIGINT NOT NULL,
    CONSTRAINT PK_LeaderboardSnapshot
        PRIMARY KEY CLUSTERED (
            PlayerSurrogateKey,
            GamePlatformSurrogateKey,
            LeaderboardSnapshotDate,
            GameCompletionDate,
            RankResultTypeSurrogateKey
        )
);
GO

ALTER TABLE dbo.LeaderboardSnapshot
ADD CONSTRAINT FK_LeaderboardSnapshot_PlayerSurrogateKey
    FOREIGN KEY (RankResultTypeSurrogateKey)
    REFERENCES dbo.RankResultType (RankResultTypeSurrogateKey);
GO

データ アクセス パターン

次のセクションでは、設定されたスキーマに対するランキング関連のアクセス パターンについて詳しく説明します。

ランク付け操作

次に、トップ スコアと完了レベルに基づいて、特定の 1 日のすべてのプレイヤーとプラットフォームのランク付けを生成する例を示します。 結果が LeaderboardSnapshot テーブルに格納されることに注意してください (前述した、履歴に16 億行が含まれる参照サービス層では、ほとんどの操作に列ストア インデックスとバッチ モード実行を利用してもこのクエリに約 20 秒かかります)。

;WITH CTE_Leaderboard
AS (SELECT DENSE_RANK() OVER (ORDER BY GameCompletionScore DESC, GameCompletionLevel DESC) AS RankResult,
           PlayerSurrogateKey,
           GamePlatformSurrogateKey,
           GameCompletionLevel,
           GameCompletionScore,
           GameCompletionDate
    FROM dbo.GameCompletion
    WHERE CAST(GameCompletionDate AS DATE) = '2/26/2019')
INSERT dbo.LeaderboardSnapshot
(
    PlayerSurrogateKey,
    GamePlatformSurrogateKey,
    LeaderboardSnapshotDate,
    GameCompletionLevel,
    GameCompletionScore,
    GameCompletionDate,
    RankResultTypeSurrogateKey,
    RankResult
)
SELECT DISTINCT
       CTE_Leaderboard.PlayerSurrogateKey,
       CTE_Leaderboard.GamePlatformSurrogateKey,
       SYSDATETIME() AS LeaderboardSnapshotDate,
       GameCompletionLevel,
       GameCompletionScore,
       GameCompletionDate,
       2 AS RankResultTypeSurrogateKey, -- Day 
       CTE_Leaderboard.RankResult
FROM CTE_Leaderboard
ORDER BY RankResult;
GO

前の操作により、ランキング テーブルにランク付け行が挿入されました。 特定の日付とランク付けの種類についてこれらの結果を取得するには、次のクエリを使用します。

SELECT PlayerSurrogateKey,
       GamePlatformSurrogateKey,
       LeaderboardSnapshotDate,
       GameCompletionLevel,
       GameCompletionScore,
       GameCompletionDate,
       RankResultTypeSurrogateKey,
       RankResult
FROM dbo.LeaderboardSnapshot
WHERE LeaderboardSnapshotDate = '2019-02-28 18:08:01.0052490' 
    AND RankResultTypeSurrogateKey = 2
ORDER BY RankResult;
GO

ヒント

LeaderboardSnapshot テーブルに追加の非クラスター化インデックスを作成して、(共通の術部と結合条件に基づく) 共通のランキング結果クエリ パターンをサポートすることをお勧めします。 前のクエリ例では、次のインデックスが WHERE 句の述部で参照される両方の列をカバーするため、パフォーマンスの向上に役立ちます。

読み取りのパフォーマンスと、書き込みのオーバーヘッドのトレードオフを考慮してください。 追加のインデックスを作成すると読み取りパフォーマンスが向上しますが、記憶域と書き込みのオーバーヘッドも増加します。

CREATE NONCLUSTERED INDEX [LeaderboardSnapshot_LeaderboardSnapshotDate_RankResultTypeSurrogateKey]
ON [dbo].[LeaderboardSnapshot] ([LeaderboardSnapshotDate],[RankResultTypeSurrogateKey])
INCLUDE ([GameCompletionLevel],[GameCompletionScore],[RankResult])
WITH (DATA_COMPRESSION = PAGE);
GO

結果のページングによるリアルタイムのランク付け

特定の範囲の値 (たとえばページング結果) のみに対するランキング スナップショットが必要な場合は、T-SQL の OFFSET ROWS FETCH NEXT 機能を使用できます。 たとえば、3 月 10 日の GameCompletion のすべての行に対する上位 100 のランキングを以下に示します (前述のサービス層とテーブル サイズで、実行時間は約 20 秒です)。

;WITH CTE_Leaderboard
AS (SELECT DENSE_RANK() OVER (ORDER BY GameCompletionScore DESC, GameCompletionLevel DESC) AS RankResult,
           PlayerSurrogateKey,
           GamePlatformSurrogateKey,
           GameCompletionLevel,
           GameCompletionScore,
           GameCompletionDate
    FROM dbo.GameCompletion
    WHERE CAST(GameCompletionDate AS DATE) = '3/10/2019')
INSERT dbo.LeaderboardSnapshot
(
    PlayerSurrogateKey,
    GamePlatformSurrogateKey,
    LeaderboardSnapshotDate,
    GameCompletionLevel,
    GameCompletionScore,
    GameCompletionDate,
    RankResultTypeSurrogateKey,
    RankResult
)
SELECT DISTINCT
       CTE_Leaderboard.PlayerSurrogateKey,
       CTE_Leaderboard.GamePlatformSurrogateKey,
       SYSDATETIME() AS LeaderboardSnapshotDate,
       GameCompletionLevel,
       GameCompletionScore,
       GameCompletionDate,
       2 AS RankResultTypeSurrogateKey, -- Day
       CTE_Leaderboard.RankResult
FROM CTE_Leaderboard
ORDER BY RankResult OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;
GO

ランク付け結果 "ランクの近いプレイヤー”

次の例では、2019 年 5 月 1 日時点の全プレイヤーを対象とした日次ランキングを生成したとします。 全プレイヤーに対する結果が LeaderboardSnapshot テーブルに設定されます。
ここで、次の条件と要件について考えてみましょう。

• プレイヤー “242695” は、この特定のランク付けスナップショットでは 24,421 位にランク付けされています。
• このプレイヤーは、自分の上下 10 人ずつのプレイヤーの表示を希望しています。

ランク付けは既に生成されて LeaderboardSnapshot テーブルに設定されているため、次の手順としては、次のようなクエリを実行してプレイヤー "242695" のランクの近い対戦相手のウィンドウを生成します。

下の例では、ゲーム完了日とランク付けの種類に対する 1 つの共通テーブル式 (CTE_NearMe) を定義し、プレイヤー 242695 の上下のランク付け境界を定義するための 2 番目の共通テーブル式 (CTE_PlayerRank) を定義した後、これらの CTE を使用して、最も近いプレイヤーのランク付け行をプルするクエリを定義します。

;WITH CTE_NearMe
AS (SELECT PlayerSurrogateKey,
           GamePlatformSurrogateKey,
           LeaderboardSnapshotDate,
           GameCompletionLevel,
           GameCompletionScore,
           GameCompletionDate,
           RankResultTypeSurrogateKey,
           RankResult
    FROM dbo.LeaderboardSnapshot
    WHERE CAST(GameCompletionDate AS DATE) = '5/1/2019'
          AND RankResultTypeSurrogateKey = 2),
      CTE_PlayerRank
AS (SELECT (CTE_NearMe.RankResult - 10) AS LowerRankBound,
           (CTE_NearMe.RankResult + 10) AS UpperRankBound
    FROM CTE_NearMe
    WHERE CTE_NearMe.PlayerSurrogateKey = 242695)
SELECT CTE_NearMe.PlayerSurrogateKey,
       CTE_NearMe.GamePlatformSurrogateKey,
       CTE_NearMe.GameCompletionLevel,
       CTE_NearMe.GameCompletionScore,
       CTE_NearMe.GameCompletionDate,
       CTE_NearMe.RankResultTypeSurrogateKey,
       CTE_NearMe.RankResult
FROM CTE_NearMe
WHERE RankResult
BETWEEN
(
    SELECT LowerRankBound FROM CTE_PlayerRank
) AND
(
    SELECT UpperRankBound FROM CTE_PlayerRank
)
ORDER BY RankResult;
GO

単一参照の処理

繰り返しになりますが、GameCompletion テーブルに対するアドホック アクティビティは最小限に抑えてください。 このテーブルを (ランキングの生成ではなく) 個別スコアのリアルタイムの取得に使用する場合は、予想される参照値に基づいて補助的な非クラスター化 B-tree インデックスを追加することを検討します。

一般的でないシナリオとして、GameCompletion の基になる列ストア インデックスだけを使用して単一のゲーム結果の参照操作を行う例を次に示します。

SELECT GameCompletionSurrogateKey,
       PlayerSurrogateKey,
       GamePlatformSurrogateKey,
       GameCompletionLevel,
       GameCompletionScore,
       GameCompletionDate
FROM dbo.GameCompletion
WHERE PlayerSurrogateKey = 944799
    AND GameCompletionDate = '2019-12-30 00:00:02.9428411';
GO

非クラスター化インデックスを使用しなくても、この例の単一参照クエリは 200 ミリ秒弱で実行できます。 インデックス追加の利点を、全体的な書き込み遅延のオーバーヘッドおよび記憶域のフットプリントと比較して評価してください。

古くなったデータの移行

このリファレンス アーキテクチャは、古いデータを簡単に履歴テーブルにアーカイブして移行するのに役立ちます。 次のクエリは、古くなったデータを GameCompletionHistory に移行する方法を示しています。

-- Switching out the oldest data by month
-- Partition “1” in this example is January 2019 data
ALTER TABLE dbo.GameCompletion 
    SWITCH PARTITION 1 
        TO dbo.GameCompletionHistory PARTITION 1;
GO

このリファレンス アーキテクチャで説明されているすべての手順に従っていれば、変更とアーカイブのプロセスはただちに (1 秒未満で) 実行されます。 アーカイブされたデータは、GameCompletionHistory ですぐに使用できます。

その他のリソースとサンプル

• パーティション テーブルとパーティション インデックス
• 列ストア インデックス - 設計ガイダンス
• Azure SQL Database でのクエリのパフォーマンスを手動でチューニングする
• クラスター化インデックスと非クラスター化インデックスの概念 SQL Server のインデックスのアーキテクチャとデザイン ガイド
• ゲーム業界のワークロードを対象とした Azure SQL Database (技術ホワイト ペーパー)

価格設定

Azure サブスクリプションをお持ちでない場合は、無料アカウント を作成して 12 か月間の無料サービスの利用を開始できます。 それらのサービスの制限を超えない限り、Azure 無料アカウントで無償で提供されているサービスに対して料金が発生することはありません。 Azure Portal または使用状況ファイルを通じて使用状況を確認する方法について説明します。

これらのリファレンス アーキテクチャの実行中に使用される Azure サービスのコストはユーザーが負担します。 その合計は使用状況によって異なります。 リファレンス アーキテクチャで使用されていた各サービスの価格は、Web ページで確認ください。

• Azure Functions
• Azure SQL Database

また、Azure の料金計算ツールを使用して、使用する予定の Azure サービスのコストを構成および見積もることもできます。 価格は概算であり、実際の価格の見積もりを意図したものではありません。 実際の価格は、購入日、支払い通貨、Microsoft との契約の種類によって異なります。 価格の詳細については、Microsoft の営業担当者にお問い合わせください。