MySQL计算30分钟移动平均查询无结果且持续运行问题求助

核心问题点 #

1. 子查询作用域错误：子查询里引用的time_stamp是主查询的字段别名，子查询无法直接访问该别名，导致每遍历主表一行数据，就会触发一次全表扫描来筛选时间条件，数据量稍大就会让查询陷入长时间执行状态。
2. 时间范围逻辑颠倒：BETWEEN要求左侧值小于等于右侧值，但你写的CURRENT_TIMESTAMP()（当前时间）在前，SUBTIME(CURRENT_TIMESTAMP(),"00:30:00")（30分钟前）在后，这样的范围不会匹配任何有效数据。
3. 表名不一致：主查询用my_df.hourly_values，子查询用my_db.hourly_values，大概率是笔误，需统一库名。

修复后的查询语句 #

如果你的需求是获取当前时间前30分钟内所有数据的原始字段，同时展示该时间段的平均值，可以用以下两种方案：

方案1：窗口函数实现（MySQL 8.0+推荐，性能更高）

SELECT
    CONVERT_TZ(timestamp, 'US/Central', 'UTC') AS time_stamp,
    current_value,
    thisMonthPeakValue,
    lastMonthPeakValue,
    ROUND(AVG(current_value) OVER (), 2) AS 30minAvg
FROM my_db.hourly_values
WHERE CONVERT_TZ(timestamp, 'US/Central', 'UTC') BETWEEN SUBTIME(CURRENT_TIMESTAMP(), '00:30:00') AND CURRENT_TIMESTAMP()
ORDER BY time_stamp DESC
LIMIT 10000;

方案2：子查询预计算平均值（兼容低版本MySQL）

SELECT
    CONVERT_TZ(a.timestamp, 'US/Central', 'UTC') AS time_stamp,
    a.current_value,
    a.thisMonthPeakValue,
    a.lastMonthPeakValue,
    b.avg_value AS 30minAvg
FROM my_db.hourly_values a
CROSS JOIN (
    SELECT ROUND(SUM(current_value)/COUNT(current_value), 2) AS avg_value
    FROM my_db.hourly_values
    WHERE CONVERT_TZ(timestamp, 'US/Central', 'UTC') BETWEEN SUBTIME(CURRENT_TIMESTAMP(), '00:30:00') AND CURRENT_TIMESTAMP()
) b
WHERE CONVERT_TZ(a.timestamp, 'US/Central', 'UTC') BETWEEN SUBTIME(CURRENT_TIMESTAMP(), '00:30:00') AND CURRENT_TIMESTAMP()
ORDER BY time_stamp DESC
LIMIT 10000;

性能优化建议 #

• 给timestamp字段添加索引，避免全表扫描：

  CREATE INDEX idx_hourly_timestamp ON my_db.hourly_values(timestamp);
  

• 如果频繁做时区转换，建议在表中新增一个存储UTC时间的字段，提前转换并存入，再给该字段建索引，避免每次查询都调用CONVERT_TZ函数消耗性能。

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