mysql - 按日期查询记录计数并填充直方图的建议方法

Question

我有一个带有时间戳(日期)列的日志表，我想创建一个直方图来显示 7 天内每小时向该表添加多少条记录。

而且表中的条目数量是随机的，有时一小时内只有 100 条记录，有时可能在一小时内达到 10000 条以上。

查询数据的最佳方法是什么？

方案一:查询最近7天的所有记录，然后在代码中迭代，按照小时填充一个数组列表。

选项 2:按小时进行计数查询，并运行 24 * 7 = 168 次。

目前我正在使用选项1，但是当日志表中的条目数量很大时，处理时间(和内存使用量)非常糟糕。但是运行 168 mysql 命令是更好的选择吗？

Answer 1

选项 3:从 GROUP BY 查询返回“按小时”的计数

 SELECT DATE_FORMAT(t.dt,'%Y-%m-%d %H') AS yyyy_mm_dd_hh
      , COUNT(1) AS cnt
   FROM logtable
  WHERE t.dt >= ? 
    AND t.dt <  ? + INTERVAL 7 DAY
  GROUP BY DATE_FORMAT(t.date,'%Y-%m-%d %H')
  ORDER BY DATE_FORMAT(t.date,'%Y-%m-%d %H')

不幸的是，对于日志表中没有行的小时，此查询不会返回“零计数”。这些要么需要由应用程序填写，要么我们需要使用维度表以不同的方式编写查询，这样我们才能保证每小时返回一行。

拥有一个合适的索引，以 dt 为前导列将允许 MySQL 执行索引范围扫描操作。

就性能而言，这可能比 168 个单独的查询更快，也比向客户端返回大量行进行计数更快。

<小时/>

为了返回“零计数”，我们需要一个每小时返回一行的行源。如果我们有一个表可以返回从 0 到 167 的连续整数集，那就太好了...

 SELECT s.i
   FROM myints s
  WHERE s.i >= 0 
    AND s.i <  168
  ORDER BY s.i

这样，我们就可以生成我们想要检查的一组“小时”范围。

 SELECT c.d + INTERVAL s.i HOUR AS dt
   FROM (SELECT '2016-04-01' + INTERVAL 0 HOUR AS d) c
   JOIN myints s
     ON s.i >= 0 
    AND s.i <  168
  ORDER BY s.id

我们可以将其用作内联 View

 SELECT h.dt
      , COUNT(t.dt) AS cnt
   FROM ( SELECT c.d + INTERVAL s.i HOUR AS dt
            FROM (SELECT '2016-04-01' + INTERVAL 0 HOUR AS d) c
            JOIN myints s
              ON s.i >= 0 
             AND s.i <  168
           ORDER BY s.id
        ) h
   LEFT
   JOIN logtable
     ON t.dt >= h.dt
    AND t.dt <  h.dt + INTERVAL 1 HOUR
  GROUP BY h.dt
  ORDER BY h.dt