用 D1 做遊戲排行榜：分頁、快取、即時更新

遊戲大廳 14 款共用一套排行榜系統 — 全域榜（所有遊戲累計）+ 各遊戲獨立榜。看似簡單，但要做到「load 快、寫入不卡、即時更新」三者兼顧，需要 schema 設計、INDEX 配合、快取策略三層配合。

Schema 設計：兩張表 vs 一張表

選 1：一張 scores 表存所有歷史紀錄

CREATE TABLE scores (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT,
  game_id TEXT,
  score INTEGER,
  created_at TEXT DEFAULT (datetime('now'))
);
-- 排行榜查詢：
SELECT user_id, MAX(score) as best
FROM scores WHERE game_id = ?
GROUP BY user_id ORDER BY best DESC LIMIT 20;

缺點：每次查排行榜都要 GROUP BY 全表，O(N log N)。

選 2：兩張表 — scores（歷史）+ best_scores（每人單一最佳）

CREATE TABLE best_scores (
  user_id TEXT NOT NULL,
  game_id TEXT NOT NULL,
  score INTEGER NOT NULL,
  updated_at TEXT,
  PRIMARY KEY (user_id, game_id)
);
CREATE INDEX idx_best_game_score ON best_scores(game_id, score DESC);

-- 排行榜查詢：
SELECT user_id, score FROM best_scores
WHERE game_id = ? ORDER BY score DESC LIMIT 20;

優點：INDEX 直接命中，查詢 O(log N)。寫入時 UPSERT。

UPSERT 寫法

SQLite (D1) 支援 INSERT OR REPLACE：

INSERT OR REPLACE INTO best_scores
  (user_id, game_id, score, updated_at)
VALUES (?, ?, ?, datetime('now'));

但這會無條件覆寫 — 即使新分數低於舊紀錄也覆寫。應該：

INSERT INTO best_scores (user_id, game_id, score, updated_at)
VALUES (?, ?, ?, datetime('now'))
ON CONFLICT(user_id, game_id) DO UPDATE SET
  score = MAX(best_scores.score, excluded.score),
  updated_at = CASE
    WHEN excluded.score > best_scores.score
    THEN excluded.updated_at
    ELSE best_scores.updated_at
  END;

這個 UPSERT 只在分數真的更高時才更新，省 INDEX 重建。

分頁：cursor-based 而不是 offset

排行榜的「下一頁」用 OFFSET 100 性能很糟（O(offset)）。改用 cursor：

-- 第一頁
SELECT * FROM best_scores
WHERE game_id = ?
ORDER BY score DESC, user_id ASC
LIMIT 20;

-- 下一頁（傳上一頁最後一筆的 score + user_id）
SELECT * FROM best_scores
WHERE game_id = ?
  AND (score, user_id) < (?, ?)
ORDER BY score DESC, user_id ASC
LIMIT 20;

O(log N) 不論第幾頁。

JOIN users 的考量

排行榜要顯示玩家名稱與頭像 — 跨表 JOIN：

SELECT b.score, u.name, u.avatar, u.title
FROM best_scores b
INNER JOIN users u ON b.user_id = u.id
WHERE b.game_id = ?
ORDER BY b.score DESC LIMIT 20;

D1 對 INNER JOIN 支援良好，10K 筆 best_scores + 1K users 的 JOIN 約 5–10ms。

快取策略：Cache API

排行榜更新頻率低（一場結束才寫入），讀取頻率高。完美的快取場景：

const cacheKey = new Request(`https://internal/lb/${gameId}`);
const cached = await caches.default.match(cacheKey);
if (cached) return cached;

const result = await db.prepare(SQL).bind(gameId).all();
const response = new Response(JSON.stringify(result), {
  headers: { 'Content-Type': 'application/json' },
});
response.headers.append('Cache-Control', 'public, max-age=60');
await caches.default.put(cacheKey, response.clone());
return response;

60 秒快取 = 高峰時段 1000 個請求只打 1 次 D1。

失效策略：寫入時主動清

async function submitScore(userId, gameId, score) {
  await db.prepare(UPSERT_SQL).bind(userId, gameId, score).run();
  // 主動失效快取
  await caches.default.delete(
    new Request(`https://internal/lb/${gameId}`)
  );
  // 全域排行榜也要失效
  await caches.default.delete(
    new Request('https://internal/lb/global')
  );
}

全域排行榜（跨遊戲累計）

SELECT u.name, u.avatar,
  SUM(b.score) as total,
  COUNT(b.game_id) as games_played
FROM best_scores b
INNER JOIN users u ON b.user_id = u.id
GROUP BY b.user_id
ORDER BY total DESC LIMIT 20;

有 INDEX 配合下這個 query 在 10K users × 14 games（≈ 140K best_scores）約 30–50ms。

D1 寫入限制

免費版 10 萬寫入/月 = 平均 30/分鐘。對小型遊戲夠用，但有突發流量時注意：

每場遊戲只送一次：遊戲結算時送，不要中途送
客戶端 dedup：相同分數送過就不再送
批次寫入：D1 支援 batch（多 INSERT 合一個 transaction）

即時排名查詢

「你目前在第幾名？」

SELECT COUNT(*) + 1 as rank
FROM best_scores
WHERE game_id = ? AND score > ?;

O(log N) 因為 INDEX。

結語

D1 + 一點 SQL 巧思就能撐起 14 款遊戲共用的排行榜，沒有 Redis、沒有自架資料庫。對小到中型遊戲規模這個架構是甜蜜點。

用 D1 做遊戲排行榜：分頁、快取、即時