mysql - 获取集合中每行的最新行(5M+ 行)

Question

我有 2 个表 - 传感器和读数。 传感器与读数之间存在一对多关系。

我需要从传感器查询所有行，然后从每行的读数获取最新(即最大时间戳)数据。我尝试过:

SELECT sensors.*, readings.value, readings.timestamp
FROM sensors
LEFT JOIN readings ON readings.sensor_id = sensors.id
GROUP BY readings.sensor_id

问题是，我有 600 万行数据，并且查询需要近两分钟才能执行。有没有更有效的方法可以获取每个传感器的最后读数/值？

Answer 1

这就是我解决问题的方法:

• 它涉及一个填充 latest_readings 表的触发器
• 它涉及另一个表，我将其命名为 latest_readings。

表格

我将 sensor_id 设置为唯一，因为我假设每个传感器都有一个读数。可以通过添加附加列来按类型进行分类。

使用唯一索引的原因:我们将使用 MySQL 的 INSERT INTO ... ON DUPLICATE KEY UPDATE 来为我们完成所有艰苦的工作。如果某个特定传感器有读数，它会被更新 - 否则，它会被插入(在一个查询中)。

您还可以将 sensor_id 设置为外键。我跳过了那部分。

CREATE TABLE latest_readings (
    id int unsigned not null auto_increment,
    sensor_id int unsigned not null,
    reading_id int unsigned not null,
    primary key(id),
    unique (sensor_id)
) ENGINE = InnoDB;

触发器

触发器类型为插入后。我假设该表名为 readings 并且包含 sensor_id 列。相应地进行调整。

DELIMITER $$

CREATE
    TRIGGER `readings_after_insert` AFTER INSERT ON `readings` 
        FOR EACH ROW BEGIN
            INSERT INTO readings 
                (sensor_id, reading_id) 
                VALUES
                (NEW.sensor_id, NEW.id)

                ON DUPLICATE KEY UPDATE reading_id = NEW.id
                ;
        END;
$$

DELIMITER ;

如何查询最新的传感器读数

我再次假设了列名称，因此请进行相应调整。

SELECT
r.reading_value

FROM readings r

INNER JOIN latest_readings latest
ON latest.sensor_id = r.sensor_id

WHERE r.sensor_id = 12345;

免责声明:这只是一个示例，它可能包含错误，这意味着它不是复制粘贴解决方案。如果有些东西不起作用，而且很容易修复 - 请这样做:)