设计近24小时Top K热门歌曲统计服务及genre维度扩展方案

Let's tackle this system design problem head-on—we're dealing with huge scale here (1 billion songs, 200 million users), so every decision needs to balance speed, scalability, and cost. Let's break it down into core components, starting with the big picture.

The flow will look like this:

1. Play event notifications (from the existing listener service) get ingested into a high-throughput message queue.
2. Real-time processors update aggregated play counts, both globally and (for future needs) per genre.
3. A caching layer serves fast Top K queries, with periodic syncs from the aggregated data.
4. Persistent storage keeps historical data for re-computations and long-term tracking.

First, we need to handle the flood of play events reliably:

• Message Queue: Use a distributed queue like Kafka to ingest play events. It’s built for high throughput (millions of events/sec) and durability, so we don’t lose any play notifications. Each event should include user_id, song_id, timestamp, and we can add genre_id upfront (pulled from song metadata) to avoid joins later.
• Idempotency: To handle duplicate notifications, use a unique key (user_id + song_id + rounded_timestamp e.g., rounded to 1 minute) to skip reprocessing the same play.
• Real-Time Aggregators: Deploy consumer services that read from Kafka and update two sets of aggregated data:
  • Global play counts per song
  • Genre-specific play counts per song

We need efficient ways to track and retrieve Top K without burning through resources:

• Sliding Window with Time Buckets: Split the 24-hour window into hourly buckets. Each bucket stores play counts for that hour. When calculating the 24-hour total, we sum counts from the last 24 buckets. Old buckets can be deleted automatically to save space.
• Global Top K:
  • For approximate counts (good enough for most use cases), use a Count-Min Sketch—it’s memory-efficient (works for 1B songs with GBs of RAM instead of TBs) and gives bounded error.
  • For exact counts, use Redis Sorted Sets (ZSets) where each entry is song_id with a score equal to its total play count across active buckets. We can update the ZSet incrementally as events come in.
• Genre-Specific Top K: Maintain a separate ZSet per genre (e.g., key: genre:top_k:rock) with the same structure—song IDs and their play counts within that genre.

Caching is make-or-break for low-latency Top K queries:

• Primary Cache Layer: Use a Redis cluster to store:
  • The global Top K ZSet (cached with a TTL of 1–5 minutes, depending on how fresh results need to be)
  • Per-genre Top K ZSets for popular genres (rock, pop, etc.)—cache these more aggressively since they’re queried often.
• Local Cache: Add a local cache (like Guava Cache) on application servers to store frequently requested Top K results (e.g., global Top 100, rock Top 50). This reduces load on the Redis cluster.
• Cache Refresh: Combine scheduled updates (e.g., every minute, a job re-computes and updates the cache) with lazy loading. If a cache entry expires, return the stale data temporarily while asynchronously refreshing the new results to avoid query spikes.

We need persistent storage for historical data and fallback re-computations:

play_events_summary (Aggregated Hourly Data) #

CREATE TABLE play_events_summary (
    bucket_hour DATETIME NOT NULL, -- e.g., '2024-05-20 14:00:00' (hourly buckets)
    song_id BIGINT NOT NULL,
    genre_id INT NOT NULL,
    play_count INT NOT NULL DEFAULT 0,
    PRIMARY KEY (bucket_hour, song_id),
    INDEX idx_genre_bucket (genre_id, bucket_hour)
);

This table lets us quickly compute 24-hour totals by summing across the last 24 bucket_hour entries, either globally or filtered by genre_id.

song_metadata (Static Song Data) #

CREATE TABLE song_metadata (
    song_id BIGINT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    genre_id INT NOT NULL,
    FOREIGN KEY (genre_id) REFERENCES genres(genre_id)
);

Storing genre_id directly here avoids expensive joins when processing play events or querying genre-specific Top K.

To support genre-specific queries, we just extend the core system:

• Real-Time Tracking: As play events come in, update both the global ZSet and the genre-specific ZSet for the song’s genre.
• Querying: For a genre like rock, either:
  1. Pull the pre-cached ZSet from Redis (fastest option), or
  2. Fall back to the database: sum play_count across the last 24 buckets where genre_id = rock_genre_id, sort by total count, and take the top K.
• Cross-Genre Queries: If someone wants Top K across rock + folk, fetch each genre’s Top K from cache, merge the results in the application layer, sort, and take the final Top K—this is faster than a complex database query.

• Data Expiry: Schedule a nightly job to delete play_events_summary entries older than 24 hours to keep the database lean.
• Cold Start: When the system first launches, return a default popular song list until enough play data is collected.
• Scalability: Shard Redis ZSets by genre or song ID range to handle large volumes. For the database, shard play_events_summary by bucket_hour or genre_id.
• Approximate vs Exact: Use Count-Min Sketch for global counts if exact numbers aren’t critical—it saves massive memory. Reserve exact counts for high-priority genres or use cases.

内容的提问来源于stack exchange，提问作者Ryan