如何优化统计间隔至少1小时的评论数量的重复子查询SQL语句

优化思路 #

原语句的重复子查询是用于获取当前评论之前的最新评论创建时间，我们只需要将该值提前计算一次复用，即可简化语句同时提升执行效率。

方案1：MySQL 8.0+ 最优写法（窗口函数，最简洁） #

使用LAG窗口函数直接获取相邻评论的时间差，无需嵌套子查询：

SELECT COUNT(*) AS `count`
FROM (
    SELECT 
        created_at,
        LAG(created_at) OVER (ORDER BY created_at) AS prev_created_at
    FROM comments
    WHERE user_id = '28'
) t
WHERE TIMESTAMPDIFF(SECOND, prev_created_at, created_at) > 3600 
   OR prev_created_at IS NULL;

逻辑说明 #

• 内层子查询先筛选用户28的所有评论，按创建时间排序后，用LAG函数取每条评论的上一条评论创建时间存入prev_created_at字段
• 外层直接判断时间间隔大于1小时，或者prev_created_at为空（即第一条评论）的条目计数即可

方案2：兼容MySQL 5.x 优化写法 #

如果使用不支持窗口函数的低版本MySQL，可将重复子查询抽为派生表字段，避免重复执行：

SELECT COUNT(c.id) AS `count`
FROM (
    SELECT 
        id,
        created_at,
        (
            SELECT MAX(created_at)
            FROM comments
            WHERE user_id = '28' AND created_at < c_main.created_at
        ) AS prev_created_at
    FROM comments c_main
    WHERE user_id = '28'
) c
WHERE TIMESTAMPDIFF(SECOND, prev_created_at, created_at) > 3600
   OR prev_created_at IS NULL;

逻辑说明 #

将原重复的子查询提前计算为每条评论的prev_created_at字段，外层判断时直接复用该值，子查询仅执行一次，性能比原语句提升一倍，写法也更简洁。

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