如何修复更新缓慢的查询

Question

我正在使用以下查询来填充表从一大组读数中得出的每日极端温度。有33个温度计，每分钟读取一次读数；因此，每天大约有46K个读数(即行)。但是每天只有33行被添加到extremes。

最初，我设想每次插入新的读数时都运行这个查询，以使今天的极端情况保持最新。然而，我很快发现这个查询需要很长时间才能运行：在我的MacBook上，一整天的阅读需要5分半钟。

我很有兴趣深入了解为什么它如此缓慢，也许还有如何让这个查询更快，或者是一个更好的替代方案。注意，extremes同时将Sensor_ID和Date作为主键，因为这是每行的唯一性。

谢谢

insert into extremes(Date, Sensor_ID, `min`, `max`, `avg`)
select date(DateTime) as `Date`, Sensor_ID as Sensor_ID,
min(Value) as `min`, max(Value) as `max`, avg(Value) as `avg`
from readings where date(`DateTime`) = date(NOW())
group by date(DateTime), Sensor_ID
on duplicate key update 
`min` = values(`min`), `max` = values(`max`), `avg` = values(`avg`);

根据要求，以下是表格

CREATE TABLE `readings` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Sensor_ID` int(11) NOT NULL,
`DateTime` datetime NOT NULL,
`Value` double NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `ID_UNIQUE` (`ID`),
KEY `ID_idx` (`Sensor_ID`),
CONSTRAINT `ID` FOREIGN KEY (`Sensor_ID`) REFERENCES `sensors` (`ID`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=54500039 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
CREATE TABLE `extremes` (
`Date` datetime NOT NULL,
`Sensor_ID` int(11) NOT NULL,
`min` double DEFAULT NULL,
`max` double DEFAULT NULL,
`avg` double DEFAULT NULL,
`updates` int(11) DEFAULT '0',
PRIMARY KEY (`Date`,`Sensor_ID`),
KEY `ID_idx` (`Sensor_ID`),
CONSTRAINT `foo` FOREIGN KEY (`Sensor_ID`) REFERENCES `sensors` (`ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Answer 1

向readings表中的DateTime列添加索引。

然后尝试以下SQL：

insert into extremes(Date, Sensor_ID, `min`, `max`, `avg`)
select date(DateTime) as `Date`, Sensor_ID as Sensor_ID,
min(Value) as `min`, max(Value) as `max`, avg(Value) as `avg`
from readings where `DateTime` >= date_format(curdate(), '%Y-%m-%d 00:00:00')
group by date(DateTime), Sensor_ID
on duplicate key update 
`min` = values(`min`), `max` = values(`max`), `avg` = values(`avg`);

Answer 2

UNIQUE KEY `ID_UNIQUE` (`ID`),

减慢对CCD_ 7的修改。它是冗余，因为`PRIMARY KEY是唯一密钥。放下它。

只在插入的一行上执行IODKU，而不是在所有行上执行：

insert into extremes(Date, Sensor_ID, `min`, `max`)
VALUES(... , ..., ..., ...)   -- Place constants here (from the sensor)
on duplicate key update 
`min` = LEAST(`min`, values(`min`)),
`max` = GREATEST(`max`, values(`max`);

然后有一个夜间工作来设置平均

这样，您就可以触摸1行，而不是最多1440行。

另一种技术是收集一分钟的读数，然后将其应用于一个查询中。

你有数百万个传感器吗？重新思考用4字节的INT代替Sensor_ID；存在较小的整数。

你在哪里找到那些传感器的？我怀疑您是否需要超过FLOAT(4字节)的7位有效数字，而不是8字节的DOUBLEs。

关于数据类型，我的观点是——收缩数据也会加快速度，尤其是当你的RAM中有太多数据要缓存时。

短语："Sensor_ID和Date都是主键"意味着有两个不同的PK，这是不可能的。相反，"Sensor_ID和Date形成一个复合主键"。是的，这就是你需要的那张桌子。你是把Date放在第一位还是最后一位取决于你典型的SELECT是什么

CCD_ 14是另一成本。每次插入时，都需要检查另一个表，以验证id的存在。到目前为止，您已经充分调试了代码；FK可以说是一种浪费。

avg可以每分钟计算一次，但(1)在一天结束之前，它有点没有意义，(2)它需要一个额外的列(带计数)。