mysql - 计算开始时间和结束时间内某个时刻的记录

Question

假设我有一个这样的表:

CREATE TABLE foo (
    gid BIGINT PRIMARY KEY,
    starttime BIGINT,
    endtime BIGINT
);

此表存储一系列游戏的开始和结束时间(以“从纪元算起的秒数”格式)。现在，我想知道在给定时刻有多少游戏在运行。这是自然查询:

SET @t = UNIX_TIMESTAMP('2012-07-12 12:00:00');
SELECT COUNT(f.gid) FROM foo f WHERE @t BETWEEN f.starttime AND f.endtime;

复杂的是，我需要每五分钟执行一次此操作(每场比赛只持续几分钟，而我们每小时有几千次)并且可能持续六个月。我确实有循环遍历我感兴趣的日期范围并以五分钟为间隔生成 @t 的过程。问题是查询太慢了。我目前将所有 @t 存储在一个单独的表中，我已编制索引，如下所示:

CREATE TABLE bar (
    interval BIGINT PRIMARY KEY
);

所以我现在的查询是:

SELECT b.interval, COUNT(f.gid)
FROM bar b LEFT JOIN foo f
    ON b.interval BETWEEN f.starttime AND f.endtime
GROUP BY b.interval;

这太慢了，对表“foo”的索引似乎没有任何帮助。我觉得这可能是标准查询模式的标准问题，因此非常感谢您的帮助。

Answer 1

令我印象深刻的是，一旦间隔过去，您真的不需要再次计算正在运行的游戏数。

那么为什么不向 bar 添加另一列呢？

CREATE TABLE bar (
    interval BIGINT PRIMARY KEY,
    runningGames INT)

这样，你只需要设置一个计划任务每​​五分钟运行一次

INSERT into bar 
SELECT  UNIX_TIMESTAMP(NOW()),
        COUNT(*) 
FROM    foo
WHERE   endtime is null

这样您就不必一次又一次地重新创建六个月的数据