mysql - 四舍五入的主日期时间键的MySQL改进

我正在研究通过首先舍入它们的主日期时间键来从一个数据库联接多个表的查询。该数据库包含来自openhab的精确度数据，并且每个表仅具有一个名为Date的主Datetime行和一个名为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，查询时间相当短。

最佳答案

在解决这个问题之前，我怀疑它的有效性。如果我有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 ...？