mysql - 加快 SQL 查询

Question

我已将温度存储在 MySQL 数据库中。该表称为 temperatures。例如，它包含列 dtime 和 temperature。第一个是测量温度的时间(列类型是 DATETIME)，而后者显然是温度(类型是 FLOAT)。

目前我使用以下查询来获取特定时间段内的温度。

SELECT dtime, temperature 
FROM temperatures 
WHERE dtime BETWEEN "2012-11-15 00:00:00" AND "2012-11-30 23:59:59" 
ORDER BY dtime DESC

我想在结果中添加当天的平均温度。我尝试了以下方法。

SELECT 
    dtime AS cPVM,
    temperature,
    (
        SELECT AVG(temperature) 
        FROM temperatures 
        WHERE DATE(dtime) = DATE(cPVM)
    ) AS avg
FROM temperatures 
WHERE dtime BETWEEN "2012-11-15 00:00:00" AND "2012-11-30 23:59:59" 
ORDER BY dtime DESC

工作正常，但这真的非常慢。在此期间获取结果大约需要 5 秒，而第一个(没有平均值)在 0.03 秒内完成。

SELECT DATE(dtime), AVG(temperature) 
FROM temperatures 
WHERE DATE(dtime) BETWEEN "2012-11-15" AND "2012-11-30" 
GROUP BY DATE(dtime) 
ORDER BY dtime DESC

然而，这一个是在 0.04 秒内完成的。

如何更有效地获取平均温度？

Answer 1

使用连接而不是相关子查询:

SELECT dtime, temperature, avg_temperature
FROM temperatures 
JOIN (
    SELECT DATE(dtime) AS date_dtime, AVG(temperature) AS avg_temperature
    FROM temperatures
    WHERE dtime >= '2012-11-15' AND dtime < '2012-12-01'
    GROUP BY DATE(dtime)
) AS avg_t
ON date_dtime = DATE(dtime)
WHERE dtime dtime >= '2012-11-15' AND dtime < '2012-12-01'
ORDER BY dtime DESC