mysql - 使用加载速度非常慢的子查询优化选择

Question

我有一个有点棘手的 SELECT，因为我尝试显示必须即时计算的数据。

数据从 SmartHome 系统记录并显示在可视化解决方案 Grafana 中。因此，我必须在 MySQL 中处理所有这些工作，并且无法真正编辑数据或前端来完成其中的一些工作。

图表应显示可在 UI 中选择的时间范围内的每日平均温度。MySQL中的数据是这样的一张表:

   DEVICE    |       READING       |       VALUE       |       TIMESTAMP
-----------------------------------------------------------------------------
 Thermometer |     temperature     |        20.0       |  2107.10.12 00:12:59
 Thermometer |     temperature     |        20.2       |  2107.10.12 00:24:12
                                  ...

该请求首先创建一个虚拟表(不在数据库中)，其中包含大约 10 年中每个完整小时的时间戳。它运行得非常快，似乎不是我获取缓慢的原因

之后，我将虚拟表精简为仅在图表中可见时间范围内的值。在所有这些整小时时间戳上，我必须运行子选择才能获取整小时之前记录的最后一个温度值。

然后将这些值按天分组并计算平均值。这样我就可以获得从 00:00 到 23:00 的每个整小时超过 24 个值的平均值。根据不同的天气站点，官方平均气温通常是这样计算的。

这是选择语句:

SELECT 
    filtered.hour as time, 
    AVG((SELECT VALUE
        FROM history
        WHERE READING="temperature" AND DEVICE="Thermometer" AND TIMESTAMP <= filtered.hour
        ORDER BY TIMESTAMP DESC
        LIMIT 1
    )) as value
FROM (
    SELECT calculated.hour as hour FROM (
            SELECT DATE_ADD(DATE_SUB(DATE($__timeTo()), INTERVAL 10 YEAR), INTERVAL t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i HOUR) as hour 
            FROM (SELECT 0 as i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
                     (SELECT 0 as i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
                     (SELECT 0 as i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
                     (SELECT 0 as i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
                     (SELECT 0 as i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
    ) calculated
    WHERE calculated.hour >=  $__timeFrom() AND calculated.hour <= $__timeTo()
) filtered  
GROUP BY DATE(filtered.hour)

对于一周的时间跨度，图表显示大约需要 5-10 秒。一个月你就接近半分钟。我的所有其他(无需计算的简单获取)图表都在大约或不到一秒的时间内加载。

由于我完全是 MySQL 菜鸟，刚刚开始为我的智能家居构建一些 SELECT，所以我真的不知道如何改进。

专业人士有什么想法吗？ :)

Answer 1

除非我正在监督一些非常明显的事情，并且每天平均计算出多少结果并不重要，否则您可以真正简化查询并摆脱子查询。这也会提高你的速度。

SELECT DATE(`TIMESTAMP`) AS `date`, AVG(`VALUE`) AS `value` FROM `history` WHERE `READING`='temperature' AND `DEVICE`='Thermometer' AND DATE(`TIMESTAMP`) BETWEEN 'date1' AND 'date2'

只需将 date1 和 date2 替换为您想要的值，例如 2017-10-15。