优化加载速度非常慢的子查询选择

问题描述：

我有一个有点棘手的选择，因为我试图显示必须在飞行中计算的数据。优化加载速度非常慢的子查询选择

数据从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语句：

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 noob，刚开始为我的智能家居建立一些选择，我真的不知道如何改善这一点。

从专业人士的任何想法？ :)

这绝对不是答案，但也许你应该使用一些特定的时间序列数据库？ Graphite https://graphiteapp.org/有一堆统计函数，Influxdb（https://www.influxdata.com/time-series-platform/influxdb/）具有SQL语法，我们也有prometheus等等。这种解决方案的优点 - 它专为处理指标而设计，比查询大量时间要快得多mysql –

是否必须每天进行24次测量，还是可以更多？ –

@PeterM我不认为测量的数量不是那么重要。我认为6以上的任何事情都应该给出好的结果，这与官方的计算结果没有太大的差别。更大的问题是，他们必须等待一天的时间间隔。否则，你可能会在夜晚或白天过重，这将提供一个错误的结果。这就是为什么我用全小时时间戳创建“虚拟表”的原因 – Thyraz

答

除非我监督一些真正显而易见的事情，并且无关紧要地计算每天的平均计算结果，您可以真正简化查询并摆脱子查询。这也应该会提高你的速度。

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。

这基本上是我第一次尝试做的，但是我猜想至少会错过一个分组。否则，我会在整个时间范围内取回1个值，而不是每天有值的表，对吗？但主要问题是，它并不确定一天中均匀分布的值。所以如果我有更多的温度变化记录在晚上比天白天，我会得到一个平均温度太低。 – Thyraz

结果应该是两个日期之间每天的平均值。它不考虑测量中的量或间隔。测量结果是否未按设定的时间间隔记录？ –

不，很遗憾，当检测到更改时会记录它们。： -/ 这就是创建每小时时间戳，然后搜索上次记录的值的全部原因。所以我恐怕没有一种更快的方法来计算正确的值。 – Thyraz

优化加载速度非常慢的子查询选择

相关推荐