MySQL - 计算每条记录在接下来的 5 秒内有多少行

Question

我有一张表tb:

ApplyID, ApplyDate,  
=================================  
John,    2008-01-23 12:00:01    
Joe,     2008-01-23 12:00:02  
Mary,    2008-01-23 12:00:02  
Snoopy,  2008-01-23 12:00:06  
Snoopy,  2008-01-23 12:00:07  
Snoopy,  2008-01-23 12:00:11  
John,    2008-01-23 12:00:21  

我想计算每一行在接下来的 5 秒内有多少行。
输出如下:

ApplyID, ApplyDate, Sessions
=================================  
John,    2008-01-23 12:00:01, 3   
Joe,     2008-01-23 12:00:02, 4  
Mary,    2008-01-23 12:00:02, 4  
Snoopy,  2008-01-23 12:00:06, 3  
Snoopy,  2008-01-23 12:00:07, 2  
Snoopy,  2008-01-23 12:00:11, 1  
John,    2008-01-23 12:00:21, 1  

我使用的查询:

SELECT p1.ApplyID, 
    p1.ApplyDate,
   (
     SELECT COUNT(*)
     FROM tb p2
     WHERE p2.ApplyDate >= p1.ApplyDate
        AND UNIX_TIMESTAMP(p2.ApplyDate)- UNIX_TIMESTAMP(p1.ApplyDate) <= 5
   ) AS sessions
FROM tb p1
ORDER BY ApplyDate

它有效，但需要很长时间才能显示结果。有没有更好的方法来提高查询性能？

Answer 1

有时 join 的性能优于子查询。你可以试试:

select  p1.ApplyID
,       p1.ApplyDate,
,       count(*)
from    tb p1
join    tb p2
on      p2.ApplyDate between p1.ApplyDate and p1.ApplyDate + interval 5 second
group by
        p1.ApplyID
,       p1.ApplyDate
order by
        p1.ApplyDate

在 ApplyDate 上创建索引可能会有所帮助:

create index IX_TB_ApplyDate on tb (ApplyDate, ApplyID)

两个注意事项。因为你只选择了ApplyID和ApplyDate，这个索引甚至覆盖了你的查询。并确保您的查询不使用 UNIX_TIMESTAMP，这可能会阻止 MySQL 使用索引。