如何修复更新缓慢的查询

sf6xfgos 于 2021-06-15 发布在 Mysql

关注(0)|答案(2)|浏览(250)

我使用下面的查询从大量读数中填充一个每日极端温度表。有33个温度计，每分钟读数一次；因此，每天大约有46k个读数（即行）。但只有33行被添加到 extremes 每天。
最初我设想每次插入一个新的读数时都运行这个查询，以便使今天的极端保持最新。然而，我很快发现这个查询需要很长时间才能运行：在我的MacBook5上½ 全天阅读的分钟数。
我很想了解一下为什么查询速度这么慢，以及如何使查询速度更快，或者更好的替代方法。注意 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`);

按要求，这是table

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

mysql query-optimization

来源：https://stackoverflow.com/questions/54120461/how-to-fix-slow-update-query

2条答案

按热度按时间

qoefvg9y1#

将索引添加到 DateTime 列中的 readings table。
然后尝试以下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`);

赞(0）回复(0）举报 2021-06-16

bt1cpqcv2#

UNIQUE KEY `ID_UNIQUE` (`ID`),

减慢对的修改 readings . 它是多余的，因为`主键是唯一键。放下它。
仅对要插入的一行执行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`);

然后每晚做一个工作来设定平均值。
这样的话，你接触的是一排，而不是1440。
另一种方法是收集一分钟的读数，然后将它们应用到一个查询中。
你有数百万个传感器吗？重新考虑使用4字节 INT 为了 Sensor_ID ; 有更小的整数。
你在哪里找到那些传感器的？我怀疑您是否需要超过 FLOAT （4字节）而不是8字节 DOUBLEs .
关于数据类型，我的观点是——缩小数据也会加快速度，特别是当你有太多的数据要缓存在ram中时。
短语：“传感器id和日期都作为主键”意味着有两个不同的pk，这是不可能的。取而代之的是“传感器id和日期形成一个复合主键”。是的，那就是你要的那张table。不管你把 Date 第一个还是最后一个取决于你典型的 SELECT 是。 FOREIGN KEYs 是另一个成本。每次完成插入时，都需要检查另一个表以验证id的存在；FK可以说是一种浪费。
这个 avg 可以每分钟计算一次，但是（1）在一天结束之前它是没有意义的，并且（2）它需要额外的列（带计数）。

赞(0）回复(0）举报 2021-06-16

我来回答

如何修复更新缓慢的查询

2条答案

相关问题

热门标签

最新问答