时间戳

首页 > 解决方案 > 如何修复慢更新查询

mysql - 如何修复慢更新查询

问题描述

我正在使用以下查询从大量读数中填充每日极端温度表。有33个温度计，每分钟读取一次；因此，每天大约有 46K 读数（即行）。extremes但每天只添加 33 行。

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

我很想了解为什么它这么慢，也许如何使这个查询更快，或者更好的选择。Noteextremes同时具有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

标签： mysqlquery-optimization

解决方案

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

然后尝试以下 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`);

推荐阅读