如何设计数据库以实现指定时段内Top N热门图片实时统计？

嘿，这个需求其实挺典型的——既要准确统计近7天的浏览量，又要能实时响应像D那样突然暴涨的排名变化对吧？我来给你拆解几个可行的数据库设计方案，适配不同的业务规模：

1. 核心表结构打底 #

首先得有两张基础表，先把数据存住：

图片基础信息表 (images) #

这张表存图片的固定信息，包括你提到的总浏览量：

CREATE TABLE images (
    image_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL UNIQUE, -- 比如A、B、C、D
    total_views BIGINT DEFAULT 0, -- 你已有的总浏览量字段
    upload_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    -- 其他元数据比如图片路径、描述之类的按需添加
);

浏览日志表 (image_view_logs) #

这是关键！因为我们要统计近7天的浏览量，光靠总浏览量没法拆分时间段，所以必须记录每一次浏览的时间戳：

CREATE TABLE image_view_logs (
    log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    image_id INT NOT NULL,
    view_timestamp DATETIME DEFAULT CURRENT_TIMESTAMP, -- 精确记录浏览发生时间
    FOREIGN KEY (image_id) REFERENCES images(image_id)
);

每次用户浏览图片时，往这张表里插一条记录，同时更新images里的total_views（这个你已经在做了）。

2. 实时获取Top3的实现方案 #

接下来分场景看怎么高效拿到近7天的Top3：

场景一：小流量业务（每天浏览量几万级） #

直接实时计算就行，不用搞复杂的预计算，SQL写起来很直观：

SELECT 
    i.image_id, 
    i.name, 
    COUNT(l.log_id) AS last_7d_views
FROM images i
JOIN image_view_logs l ON i.image_id = l.image_id
WHERE l.view_timestamp >= NOW() - INTERVAL 7 DAY
GROUP BY i.image_id, i.name
ORDER BY last_7d_views DESC
LIMIT 3;

不过要给image_view_logs加个联合索引，不然数据多了会慢：

CREATE INDEX idx_image_time ON image_view_logs(image_id, view_timestamp);

场景二：中高流量业务（每天几十万到百万级） #

实时扫描7天的日志会越来越慢，这时候就得用预计算+缓存来提速：

第一步：加一张日统计汇总表 (image_daily_view_stats)

按天统计每个图片的浏览量，避免每次都扫全量日志：

CREATE TABLE image_daily_view_stats (
    stat_id INT PRIMARY KEY AUTO_INCREMENT,
    image_id INT NOT NULL,
    stat_date DATE NOT NULL, -- 统计日期，比如'2024-05-20'
    daily_views BIGINT DEFAULT 0,
    UNIQUE KEY idx_image_date (image_id, stat_date) -- 避免重复统计同一天的数据
);

第二步：实时或定时更新统计数据

• 定时方式：每天凌晨跑个脚本，把前一天的浏览日志统计到这张表里，比如用SQL：

  INSERT INTO image_daily_view_stats (image_id, stat_date, daily_views)
  SELECT image_id, DATE(view_timestamp) AS stat_date, COUNT(*)
  FROM image_view_logs
  WHERE DATE(view_timestamp) = CURDATE() - INTERVAL 1 DAY
  GROUP BY image_id, stat_date
  ON DUPLICATE KEY UPDATE daily_views = VALUES(daily_views);
  

• 实时方式：如果要更及时（比如不想等凌晨），可以用消息队列，每收到一条浏览事件，就更新当天的统计数：

  INSERT INTO image_daily_view_stats (image_id, stat_date, daily_views)
  VALUES (?, CURDATE(), 1)
  ON DUPLICATE KEY UPDATE daily_views = daily_views + 1;
  

第三步：查询近7天Top3

现在查询就快多了，只需要汇总最近7天的日统计数据，再加上当天的实时日志（如果用定时更新的话，当天的数据还没进统计表）：

SELECT 
    i.image_id,
    i.name,
    -- 过去6天的统计数 + 当天的实时浏览数
    COALESCE(SUM(s.daily_views), 0) + COALESCE(l.today_views, 0) AS last_7d_views
FROM images i
LEFT JOIN image_daily_view_stats s 
    ON i.image_id = s.image_id 
    AND s.stat_date >= CURDATE() - INTERVAL 6 DAY -- 近7天包括今天，所以减6天
LEFT JOIN (
    SELECT image_id, COUNT(*) AS today_views
    FROM image_view_logs
    WHERE DATE(view_timestamp) = CURDATE()
    GROUP BY image_id
) l ON i.image_id = l.image_id
GROUP BY i.image_id, i.name
ORDER BY last_7d_views DESC
LIMIT 3;

第四步：加缓存扛并发

把查询出来的Top3结果存到Redis里，设置个短过期时间（比如1分钟），用户请求先读缓存，缓存失效再查数据库。这样就算10分钟内D的浏览量暴涨，最多1分钟后缓存更新就能看到新排名了。

场景三：超高流量业务（每天千万级以上） #

这时候数据库查询可能还是不够快，得用Redis做滑动窗口统计：

• 每一次浏览事件，执行Redis命令：HINCRBY image:views:hourly:{image_id} {当前小时} 1（比如当前是2024-05-20 14点，键就是image:views:hourly:1:2024052014）
• 查询近7天Top3时，计算最近7*24=168个小时的哈希值总和，然后排序取前3
• 定期清理超过7天的小时统计键，避免Redis内存溢出

这种方式完全绕过数据库，响应速度极快，适合超高并发的场景，但需要额外维护Redis的统计逻辑。

3. 关键优化点 #

• 给image_view_logs按时间分区（比如按天分区），这样查询近7天数据时，只扫描对应分区，速度翻倍
• 所有关联查询的字段都要加索引，避免全表扫描
• 如果不需要精确到每个用户的浏览记录，甚至可以把image_view_logs做成只存每小时的统计数，减少数据量

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