小編精選 - 技術文章翻譯 · 01月31日

資料庫 101：如何為 100 萬玩家的遊戲建立排行榜模型。

有沒有想過像《英雄聯盟》 、 《要塞英雄》甚至《Rockband》這樣的遊戲是如何建立排行榜模型的？在本文中，我們將了解如何正確建模模式以以極其高效的方式處理它們！

如果您剛開始使用一般資料庫或資料庫，您可能需要閱讀我的第一篇文章《資料庫 101：初學者的資料一致性》。那篇文章記錄了我自己對有多少資料庫範例的探索，因為我的眼光遠遠超出了我以前僅使用 SQL 和 MySQL 的經驗。我正在資料庫 101系列中追蹤我的研究。

距離我發表本系列的第一篇文章已經快一年了！感謝您在我學習主題時與我在一起。您的評論和想法總是非常有幫助！

1. 序言

YARG 遊戲玩法截圖

從我還是個孩子的時候起，就像大多數普通開發者一樣，我就對遊戲及其製作方式著迷。說到這裡，我要跟大家介紹一下我兒時最喜歡的遊戲：《吉他英雄3：搖滾傳奇》。

十多年後，我決定嘗試在開源環境中為一些遊戲做出貢獻，例如rust-ro（Rust Ragnarok Emulator）以及本文的主角： YARG（Yet Another Rhythm Game）。

YARG 實際上是另一個節奏遊戲，但這個專案的不同之處在於它是完全開源的，他們聯合了遊戲開發和設計方面的傳奇貢獻者來讓這個專案能夠運作。

突然之間，這款遊戲被 Twitch 上的 Guitar Hero/Rockband 主播們所採用並玩，我想：好吧，這是一個開源專案，所以也許我可以利用我的資料庫技能來建立一個速度極快的排行榜或儲存過去的比賽。

一開始只是在他們的 Discord 上進行了一次簡單的聊天，後來變成了關於如何讓這個專案更快發展的長時間討論。

然後我決定和我的老闆談談，問他我是否可以和 YARG 的人一起工作，條件是建立一些足夠酷的東西來實現ScyllaDB（NoSQL 寬列資料庫），因為我在那裡擔任開發倡導者。您不會相信ScyllaDB帶來的簡單性和可擴展性如何完美契合YARG.in的需求！

無論如何，談話是廉價的。讓我向您展示一些程式碼和概念！

2.QDD－查詢驅動的資料建模

NoSQL 與關係型資料庫

當我們談論使用NoSQL進行開發時，大多數情況下我們應該理解，根據範例（文件、圖形、寬列等），您應該先了解要執行哪個查詢。

在 MySQL 中，主要目標是了解一致性，而在 Scylla 中，您應該專注於查詢並基於該查詢建立模式。

在這個專案中，我們將處理兩種類型的範例，它們是：

核心價值
寬列（聚類）

現在讓我們來談談我們建模的查詢/功能。

2.1 功能：儲存匹配

提交詳情 YARG

每次完成 YARG 遊戲時，最有趣的事情就是提交您的分數以及許多其他遊戲內指標。

基本上它將是基於主索引的單一查詢，僅此而已。

SELECT score, stars, missed_notes, instrument, ...  
FROM leaderboard.submisisons  
WHERE submission_id = 'some-uuid-here-omg'

2.2 功能：排行榜

排行榜 Figma 文件

現在我們的主要目標是：一個超酷的排行榜，在良好的資料建模之後你不需要關心它。排行榜是按歌曲計算的，因此每次您播放特定歌曲時，您的最佳成績都會被保存並排名。

然而，這個介面有一個重要的點，那就是有過濾器來準確地知道要帶來「哪個」排行榜：

歌曲 ID：必填
儀器：必填
修飾符：必需
難度：必填
玩家 ID：可選
分數：可選

想像一下我們的查詢如下所示，它會傳回按分數降序排列的結果：

SELECT 
    player_id, score, ...
FROM 
    leaderboard.song_leaderboard 
WHERE 
    instrument = 'guitar' AND
    difficulty = 'expert' AND
    modifiers = {'none'} AND
    track_id = 'dani-california' 
LIMIT 
    100;

--   player_id  | score
----------------+-------
--        tzach | 12000
--  danielhe4rt | 10000
--     kadoodle |  9999
----------------+-------

現在我們知道了將在這裡使用的功能，但是您能想像最終的模式將如何嗎？

不？好的，讓我來幫助你！

3. 資料建模時間！

是時候深入研究 ScyllaDB 的資料建模並更好地了解如何擴展它了。

3.1 - 匹配建模

遊戲結束畫面

首先，讓我們先來了解遊戲本身：

這是一個節奏遊戲；
您一次播放一首特定的歌曲；
您可以在遊戲前啟動“修改器”，讓您的生活變得更輕鬆或更困難；
您必須選擇一種樂器（例如吉他、鼓、貝斯和麥克風）。
遊戲玩法的各個方面都會被跟踪，例如：
- Score;
- Missed notes;
- Overdrive count;
- Play speed (1.5x ~ 1.0x);
- Date/time of gameplay;
- And other cool stuff.

考慮到這一點，我們可以輕鬆地開始我們的資料建模，這將變成這樣：

CREATE TABLE IF NOT EXISTS leaderboard.submissions (
    submission_id uuid,
    track_id text,
    player_id text,
    modifiers frozen<set<text>>,
    score int,
    difficulty text,
    instrument text,
    stars int,
    accuracy_percentage float,
    missed_count int,
    ghost_notes_count int,
    max_combo_count int,
    overdrive_count int,
    speed int,
    played_at timestamp,
    PRIMARY KEY (submission_id, played_at)
);

讓我們跳過所有int/text值並跳到set<text> 。

集合類型可讓您儲存特定類型的專案清單。我決定使用這個清單來儲存修飾符，因為它非常適合。看看查詢是如何執行的：

INSERT INTO leaderboard.submissions (
    submission_id, 
    track_id,
    modifiers, 
    played_at
) VALUES (
    some-cool-uuid-here,
    'starlight-muse'
    {'all-taps', 'hell-mode', 'no-hopos'},
    '2024-01-01 00:00:00'
);

使用這種類型，您可以輕鬆儲存專案清單以供以後檢索。

另一個很酷的資訊是這個查詢是一個鍵值對！這意味著什麼？

由於您始終僅透過submission_id來查詢它，因此它可以歸類為鍵值。

3.2 排行榜建模

排行榜濾鏡 Figma

在本文的這一部分中，您將學習一些很酷的寬列資料庫概念。

在我們的排行榜查詢中，如前所述，我們總是需要在 WHERE 子句中使用一些動態值，這意味著這些值將屬於分區鍵，而聚類鍵將具有可以是「可選」的值。

分區鍵是基於您新增的用於標識值的欄位組合的雜湊。你明白了嗎？不？好吧，我也花了一段時間才明白這一點，但讓我向你展示一些東西：

假設您玩了Starlight - Muse 100 次。如果您要查詢此訊息，將透過score或player_id等聚類鍵區分出100倍不同的結果。

SELECT 
    player_id, score ---
FROM 
    leaderboard.song_leaderboard 
WHERE 
    track_id = 'starlight-muse' 
LIMIT 
    100;

如果有 1.000.000 個玩家播放這首歌，你的查詢會變得很慢，並且將來會成為一個問題，因為你的分區鍵只包含一個字段，即track_id 。

但是，如果您向Partition Key加入更多字段，例如玩遊戲之前的強制性內容，也許我們可以縮小這些可能性以實現更快的查詢。現在你看到大局了嗎？加入諸如“樂器” 、 “難度”和“修改器”等欄位將為您提供一種均勻分割有關特定曲目的資訊的方法。

讓我們想像一些簡單的數字：


-- Query Partition ID: '1'
SELECT 
    player_id, score, ...
FROM 
    leaderboard.song_leaderboard 
WHERE 
    instrument = 'guitar' AND
    difficulty = 'expert' AND 
    modifiers = {'none'} AND -- Modifiers Changed
    track_id = 'starlight-muse' 
LIMIT 
    100;

-- Query Partition ID: '2'
SELECT 
    player_id, score, ...
FROM 
    leaderboard.song_leaderboard 
WHERE 
    instrument = 'guitar' AND
    difficulty = 'expert' AND
    modifiers = {'all-hopos'} AND -- Modifiers Changed
    track_id = 'starlight-muse' 
LIMIT 
    100;

因此，如果您以特定形狀建立查詢，它將始終查找特定令牌並根據這些特定分區鍵檢索資料。

我們來看看最終的建模，談談聚類鍵和應用層：

CREATE TABLE IF NOT EXISTS leaderboard.song_leaderboard (
    submission_id uuid,
    track_id text,
    player_id text,
    modifiers frozen<set<text>>,
    score int,
    difficulty text,
    instrument text,
    stars int,
    accuracy_percentage float,
    missed_count int,
    ghost_notes_count int,
    max_combo_count int,
    overdrive_count int,
    speed int,
    played_at timestamp,
    PRIMARY KEY ((track_id, modifiers, difficulty, instrument), score, player_id)
) WITH CLUSTERING ORDER BY (score DESC, player_id ASC);

分區鍵的定義如上所述，由我們所需的參數組成，例如：track_id、修飾符、難度和樂器。在聚類鍵上，我們新增了Score和player_id 。

請注意，預設情況下，聚類欄位按score DESC排序，以防萬一玩家得分相同，選擇獲勝者的標準將按alphabetical ¯\_(ツ)_/¯。

首先很容易理解的是，我們每個玩家只有一個分數，但透過這種建模，如果玩家以不同的分數兩次經歷同一條賽道，它將產生兩個不同的條目。

INSERT INTO leaderboard.song_leaderboard  (
    track_id, 
    player_id,
    modifiers,
    score,
    difficulty,
    instrument,
    stars, 
    played_at
) VALUES (
    'starlight-muse',
    'daniel-reis', 
    {'none'}, 
    133700, 
    'expert', 
    'guitar', 
    '2023-11-23 00:00:00'
);

INSERT INTO leaderboard.song_leaderboard (
    track_id,
    player_id,
    modifiers,
    score,
    difficulty,
    instrument,
    stars, 
    played_at
) VALUES (
    'starlight-muse',
    'daniel-reis', 
    {'none'}, 
    123700, 
    'expert', 
    'guitar', 
    '2023-11-23 00:00:00'
);


SELECT 
    player_id, score
FROM 
    leaderboard.song_leaderboard 
WHERE 
    instrument = 'guitar' AND
    difficulty = 'expert' AND
    modifiers = {'none'} AND
    track_id = 'starlight-muse' 
LIMIT 
    2;

--   player_id  | score
----------------+-------
--  daniel-reis | 133700
--  daniel-reis | 123700
----------------+-------

那我們要如何解決這個問題呢？嗯，這本身不是問題。這是一個特點！哈哈

身為開發人員，您必須根據專案需求建立自己的業務規則，這也不例外。我這麼說是什麼意思？

您可以在插入新條目之前執行簡單的DELETE查詢，並確保在該特定分區鍵組內， player_id中的特定資料不會低於新分數。

-- Before Insert the new Gampleplay

DELETE FROM 
    leaderboard.song_leaderboard 
WHERE 
    instrument = 'guitar' AND
    difficulty = 'expert' AND
    modifiers = {'none'} AND
    track_id = 'starlight-muse' AND
    player_id = 'daniel-reis' AND
    score <= 'your-new-score-here';

-- Now you can insert the new payload...

這樣我們就完成了簡單的排行榜系統，該系統與 YARG 中執行的系統相同，也可以在每秒數百萬個條目的遊戲中使用:D