关系排行榜参考体系结构

体系结构关系图

体系结构服务

• Azure Functions
• Azure SQL 数据库

体系结构注意事项

在此参考体系结构中，我们将“排行榜”定义为，显示游戏玩家姓名以及一个或多个游戏维度和时间范围内排名位居前列的竞争对手的聚合排名。

此参考体系结构假定满足以下大规模要求：

• 100 万名玩家，每名玩家每天玩 4 个游戏。 这相当于：
  • 每天 400 万个游戏（每秒约 46 个游戏）
  • 每月约 1.21 亿个游戏
  • 每年约 14.6 亿个游戏
• 游戏结束后，应立即向玩家提供个人最终游戏得分
• 玩家根据自己的排名获取每日奖励，并且用于评定排名的全球排行榜应该可以轻松地每 4 分钟刷新一次（运行时应有足够的执行时间缓冲）。
• 应定期从“热路径”存档游戏历史记录。 存档速度要快，并且不得影响并发游戏结果活动。 此参考体系结构中的数据层还假定涵盖可跨一个或多个平台（Xbox、PlayStation、Desktop、iOS、Android 等）访问的单个游戏。

尽管可以扩展此参考体系结构以支持多个游戏，但按游戏进行隔离有助于单独的租户缩放，并避免出现“干扰”邻居及并发问题。

游戏排行榜服务层的性能和可伸缩性

为了确保为游戏发布和持续的大规模吞吐量提供充足的资源，请考虑在高级或业务关键服务层模型的基础上实现数据层。 这些服务层专为需要底层 SSD 存储提供低延迟响应的游戏而设计，并且可在底层基础结构出现故障时支持实现快速恢复。 业务关键模型可提供主数据库的免费可读辅助副本。

备注

此体系结构中推荐的所有架构和查询都在 Azure SQL 数据库业务关键第 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 表进行一次更改，而无需对所有玩家游戏历史记录进行多次自然键更改。

我们将根据您的核心游戏身份验证数据源和系统来假定玩家人数。

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

我们还将向此表添加一个聚集 columnstore 索引，从而对非常大的结果集实现低延迟分析排名查询：

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

为了确保数据质量，我们向此表添加了下列主键和两个外键约束：

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 表的临时活动。 此表应该用于快速引入传入的得分行（约 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

数据访问模式

下一节详细介绍针对已填充架构的排行榜相关访问模式。

排名操作

下面的示例会根据最高得分和完成级别生成单日所有玩家和平台的排名。 请注意，结果将填充到 LeaderboardSnapshot 表中（在前面提到的参考服务层上，历史记录中有 16 亿行，此查询使用 Columnstore 索引和批处理模式执行来完成大部分操作，需要约 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”的邻近范围的竞争对手。

在下面的示例中，我们为游戏结束日期和排名类型定义了一个通用表表达式 (CTE_NearMe)，为定义玩家 242695 的排名上限/下限设置了第二个通用表格表达式 (CTE_player rank)，然后运行查询，使用这些 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 树索引。

下面是一个针对不常见场景的示例，其中仅使用 GameCompletion 的底层 Columnstore 索引执行单个游戏结果查找操作：

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

更改和存档进程将立即运行（亚秒级），假定已按照此参考体系结构中的说明执行所有步骤。 存档的数据将立即在 GameCompletionHistory 中可用。

其他资源和示例

• 分区表和索引
• Columnstore 索引 - 设计指南
• 在 Azure SQL 数据库中手动优化查询性能
• 描述的聚集索引和非聚集索引 SQL Server 索引体系结构和设计指南
• 适用于游戏行业工作负载的 Azure SQL 数据库技术白皮书

定价

如果您没有 Azure 订阅，可以创建免费帐户，开始使用 12 个月的免费服务。 除非您超出这些服务的使用限制，否则无需为 Azure 免费帐户中包含的这些免费服务付费。 了解如何通过 Azure 门户或使用情况文件查看服务使用情况。

您需要承担运行这些参考体系结构时所使用的 Azure 服务的费用。 总金额将因使用情况而异。 请参阅参考体系结构中使用的每项服务的定价网页：

• Azure Functions
• Azure SQL 数据库

您还可以使用 Azure 定价计算器，以配置和估算您计划使用的 Azure 服务的成本。 价格是估计值，不会用作实际的报价单。 实际价格可能会有所不同，具体取决于购买日期、支付货币，以及您与 Microsoft 之间的协议类型。 有关定价的其他信息，请联系 Microsoft 销售代表。