从集合中的每一行获取最新行(5M+行)

Question

我有两个表-传感器，和读数。从传感器到读数，有一对多的关系。

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

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;

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