MySQL存储玩家统计数据：全量记录与30天数据查询及排行需求

Hey Alex, let's break this down step by step—you’ve got two key goals here: storing every single kill, death, and win event long-term, and building efficient queries to pull recent player stats and monthly leaderboards. Here’s a practical, scalable approach for MySQL:

First, you’ll need a granular event table to log every individual action. This table will be your single source of truth for all player activity. Let’s call it player_game_events:

CREATE TABLE player_game_events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    player_id VARCHAR(50) NOT NULL, -- Use your actual player ID format (INT works too)
    event_type ENUM('kill', 'death', 'win') NOT NULL,
    event_timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    match_id VARCHAR(50) NULL, -- Optional: link events to specific matches if needed
    -- Indexes are critical for fast queries
    INDEX idx_player_time (player_id, event_timestamp),
    INDEX idx_type_time (event_type, event_timestamp)
);

• The idx_player_time index speeds up queries for a specific player’s recent activity.
• The idx_type_time index makes leaderboard queries (filtering by event type + time) much faster.

If you already have a players table for basic user info (like usernames), make sure player_id in this table is a foreign key to players.player_id for referential integrity.

Whenever a player gets a kill, dies, or wins a match, insert a row into this table. Example queries:

• Log a kill:

  INSERT INTO player_game_events (player_id, event_type) VALUES ('alex_123', 'kill');
  

• Log a win with a match ID:

  INSERT INTO player_game_events (player_id, event_type, match_id) VALUES ('alex_123', 'win', 'match_789');
  

The event_timestamp uses CURRENT_TIMESTAMP to auto-record when the event happens, so you don’t have to pass it manually.

To get a player’s kill count over the past month:

SELECT COUNT(*) AS total_kills
FROM player_game_events
WHERE player_id = 'alex_123'
  AND event_type = 'kill'
  AND event_timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY);

For a combined view of kills, deaths, and wins in one query:

SELECT
  SUM(CASE WHEN event_type = 'kill' THEN 1 ELSE 0 END) AS total_kills,
  SUM(CASE WHEN event_type = 'death' THEN 1 ELSE 0 END) AS total_deaths,
  SUM(CASE WHEN event_type = 'win' THEN 1 ELSE 0 END) AS total_wins
FROM player_game_events
WHERE player_id = 'alex_123'
  AND event_timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY);

For the top 10 players by kills in the last 30 days:

SELECT
  player_id,
  COUNT(*) AS kill_count
FROM player_game_events
WHERE event_type = 'kill'
  AND event_timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY player_id
ORDER BY kill_count DESC
LIMIT 10;

If you want to display usernames instead of IDs (using a players table):

SELECT
  p.player_name,
  COUNT(*) AS kill_count
FROM player_game_events e
JOIN players p ON e.player_id = p.player_id
WHERE e.event_type = 'kill'
  AND e.event_timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY e.player_id, p.player_name
ORDER BY kill_count DESC
LIMIT 10;

Swap event_type = 'kill' with 'win' to get a win leaderboard instead.

• Index Maintenance: Keep the indexes we added—they’ll prevent full-table scans as your event table grows.
• Summary Tables (Optional): If you have thousands of concurrent players, aggregating the event table every time might get slow. Create a player_monthly_stats table to pre-compute totals, and update it daily via a cron job or MySQL event:

  CREATE TABLE player_monthly_stats (
      player_id VARCHAR(50) NOT NULL,
      month_start DATE NOT NULL,
      total_kills INT DEFAULT 0,
      total_deaths INT DEFAULT 0,
      total_wins INT DEFAULT 0,
      PRIMARY KEY (player_id, month_start),
      INDEX idx_month (month_start)
  );
  

  This lets you query leaderboards directly from the summary table instead of scanning raw events.
• Table Partitioning: For extremely large datasets (millions of rows), partition the player_game_events table by event_timestamp (e.g., monthly partitions). This limits the data scanned during time-range queries.

内容的提问来源于stack exchange，提问作者Alex K.