具有四舍五入的主日期时间键的 MySQL 改进

Question

我正在处理一个查询，通过首先舍入它们的主要日期时间键来连接一个数据库中的多个表。数据库包含来自 openhab 的性能数据，每个表只有一个名为 Time 的主日期时间行和一个名为 Value 的值行。随着日期的写入，可能会出现一秒的小延迟，并且我无法直接比较主键。这是我到目前为止得到的:

SELECT FROM_UNIXTIME(((UNIX_TIMESTAMP(i1.Time)+30)DIV 60)*60) AS rounded_time,i1.Value,i2.Value,i4.Value,i5.Value 
FROM item1 i1,item2 i2,item4 i4,item5 i5
WHERE
(((UNIX_TIMESTAMP(i1.Time)+30)DIV 60)*60) = (((UNIX_TIMESTAMP(i2.Time)+30)DIV 60)*60) AND
(((UNIX_TIMESTAMP(i1.Time)+30)DIV 60)*60) = (((UNIX_TIMESTAMP(i4.Time)+30)DIV 60)*60) AND 
(((UNIX_TIMESTAMP(i1.Time)+30)DIV 60)*60) = (((UNIX_TIMESTAMP(i5.Time)+30)DIV 60)*60) AND 
UNIX_TIMESTAMP(i1.Time) > UNIX_TIMESTAMP(date_sub(now(), interval 1 day));

这个查询已经用了 4 秒，再合并一个项目已经让我的服务器爆满了。

Details

Expected Result

Explain Result

编辑 1:通过在两个方向上允许 60 秒的偏移量，我设法通过以下连接获得了合理的结果(如果两者都需要，甚至不确定):

SELECT date_format(item1.Time, '%Y-%m-%d %H:%i') AS TIME,item1.Value AS I_TEMP,item2.Value AS I_HUM,item4.Value AS I_PRESS,item5.Value AS I_LIGHT,item6.Value AS O_HUM,item7.Value AS O_TEMP,item8.Value AS O_PRESS
FROM item1
LEFT JOIN item2 ON (item2.time BETWEEN item1.time AND date_add(item1.time, interval 60 SECOND)) OR (item1.time BETWEEN item2.time AND date_add(item2.time, interval 60 SECOND))
LEFT JOIN item4 ON (item4.time BETWEEN item1.time AND date_add(item1.time, interval 60 SECOND)) OR (item1.time BETWEEN item4.time AND date_add(item4.time, interval 60 SECOND))
LEFT JOIN item5 ON (item5.time BETWEEN item1.time AND date_add(item1.time, interval 60 SECOND)) OR (item1.time BETWEEN item5.time AND date_add(item5.time, interval 60 SECOND))
LEFT JOIN item6 ON (item6.time BETWEEN item1.time AND date_add(item1.time, interval 60 SECOND)) OR (item1.time BETWEEN item6.time AND date_add(item6.time, interval 60 SECOND))
LEFT JOIN item7 ON (item7.time BETWEEN item1.time AND date_add(item1.time, interval 60 SECOND)) OR (item1.time BETWEEN item7.time AND date_add(item7.time, interval 60 SECOND))
LEFT JOIN item8 ON (item8.time BETWEEN item1.time AND date_add(item1.time, interval 60 SECOND)) OR (item1.time BETWEEN item8.time AND date_add(item8.time, interval 60 SECOND))
WHERE
item1.Time > date_sub(now(), interval 3 HOUR)

同时 INDEXING 也在工作，如图所示 here由于 Rick James，查询时间相当短。

Answer 1

在解决这个问题之前，我怀疑它的有效性。如果我有 08:09:29 和 08:09:31，它们只相隔 2 秒，但你的“舍入”技术会说它们是不同的。你需要类似 TIMEDIFF(...) < 30 的东西以避免那个“错误”。

要解决您的性能问题...这不能使用任何索引:

UNIX_TIMESTAMP(i1.Time) > UNIX_TIMESTAMP(date_sub(now(), interval 1 day));

改成这样:

i1.Time > now() - interval 1 day

你可以使用这个:

INDEX(Time)

这将可能加快查询速度。

但是……还有一个更严重的问题。您正在进行“交叉连接”。这意味着比较 4 个表中的所有行组合。如果每行只有 1000 行，则组合是 一万亿!

也许你需要JOIN ... ON ... ？