高吞吐量生产Web应用的SQL汇总表设计最佳实践咨询

兄弟，你这个场景太典型了——高吞吐Web应用做小时/日统计，踩过的坑我都熟。你列的三个方案确实各有硬伤：单行更新的锁竞争能把线程池堵死，单请求单行存久了表能胖到查不动，定时删数据又会卡插入。结合我在生产环境摸爬滚打的经验，给你几个落地性强的最佳实践：

一、核心方案：分桶写入+异步合并（关系型数据库原生支持） #

这是我用得最多的方案，完美解决锁竞争和数据膨胀问题：

• 写入逻辑：把原来的单行汇总拆成「小时+哈希分桶」的细粒度统计行。比如建一张临时统计表：

  CREATE TABLE temp_hourly_stats (
      bucket_id INT NOT NULL,
      hour TIMESTAMP NOT NULL,
      totalA BIGINT DEFAULT 0,
      totalB BIGINT DEFAULT 0,
      totalC BIGINT DEFAULT 0,
      PRIMARY KEY (bucket_id, hour)
  ) ENGINE=InnoDB;
  

  每个请求过来时，用用户ID/请求ID做哈希取模（比如bucket_id = user_id % 100），然后执行：

  INSERT INTO temp_hourly_stats (bucket_id, hour, totalA, totalB, totalC)
  VALUES (?, DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00'), ?, ?, ?)
  ON DUPLICATE KEY UPDATE
  totalA = totalA + VALUES(totalA),
  totalB = totalB + VALUES(totalB),
  totalC = totalC + VALUES(totalC);
  

  这样把单行更新的锁竞争分散到100个桶里，锁冲突概率直接降到1%，InnoDB的行锁几乎不会阻塞请求。
• 合并清理逻辑：用定时任务（比如每小时过5分钟执行），把当前小时之前的桶数据汇总到主统计表，然后快速清理临时表：

  -- 合并到主表
  INSERT INTO main_daily_stats (day, totalA, totalB, totalC)
  SELECT DATE(hour), SUM(totalA), SUM(totalB), SUM(totalC)
  FROM temp_hourly_stats
  WHERE hour < DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
  GROUP BY DATE(hour)
  ON DUPLICATE KEY UPDATE
  totalA = totalA + VALUES(totalA),
  totalB = totalB + VALUES(totalB),
  totalC = totalC + VALUES(totalC);
  
  -- 清理临时表（用TRUNCATE比DELETE快N倍，不会锁表）
  TRUNCATE TABLE temp_hourly_stats;
  

  要是怕TRUNCATE影响当前写入，也可以给临时表按小时分区，直接ALTER TABLE temp_hourly_stats DROP PARTITION p_2024052010;，秒级完成，完全不阻塞。

二、进阶优化：引入Redis做实时缓冲 #

如果你的统计需要准实时（比如延迟1分钟以内），或者数据库压力实在太大，可以加一层Redis缓冲：

• 写入逻辑：每个请求直接用Redis的HINCRBY做增量计数，比如：

  # 伪代码
  redis_key = f"stats:{datetime.now().strftime('%Y%m%d%H')}"
  redis.hincrby(redis_key, "totalA", amountA)
  redis.hincrby(redis_key, "totalB", amountB)
  redis.hincrby(redis_key, "totalC", amountC)
  

  Redis是单线程内存操作，完全没有锁竞争，吞吐量能轻松扛住每秒几十万次请求。
• 同步逻辑：用定时任务（比如每分钟一次）把Redis的统计数据批量写入数据库，然后删除Redis的旧键。这样数据库每天只需要写入24*60=1440行，压力几乎可以忽略。
  • 注意：一定要开启Redis的RDB+AOF持久化，避免重启丢数据；分布式场景下用Redis Cluster分片，进一步提升吞吐。

三、终极方案：换用OLAP数据库 #

如果你的统计需求复杂（比如多维度聚合、历史数据查询），直接换ClickHouse、StarRocks这类OLAP数据库：

• 这类数据库天生为高吞吐写入和大规模聚合设计，用MergeTree引擎按小时分区，写入时直接追加，自动后台合并数据，完全不用处理锁竞争和数据膨胀问题。
• 写入代码和单请求单行类似，但数据库会自动帮你做汇总，查询时直接查聚合后的结果，性能比关系型数据库高几个数量级。

方案取舍建议 #

• 实时性要求低（允许延迟1小时）、不想引入额外组件：选分桶写入+异步合并，纯关系型数据库就能搞定。
• 准实时统计、数据库压力大：选Redis缓冲+批量写入，兼顾吞吐和实时性。
• 复杂统计查询、海量历史数据：直接换OLAP数据库，从根源解决瓶颈。

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