mysql - 用一个查询替换用于查找最后条目的大量单个查询

Question

我有一个包含传感器设备(~1k+行)的mysql(8.0.16)表和一个包含来自传感器的大量数据(~25M+行)的表。时间戳和 m2m_device_id 列上有单个索引，而两列上有组合索引。

我想获取每个设备的最新数据，目前正在这样做

SELECT * FROM `m2m_datas` WHERE (m2m_device_id = 980) ORDER BY timestamp DESC LIMIT 1;
SELECT * FROM `m2m_datas` WHERE (m2m_device_id = 981) ORDER BY timestamp DESC LIMIT 1;
SELECT * FROM `m2m_datas` WHERE (m2m_device_id = 982) ORDER BY timestamp DESC LIMIT 1;
and so on ...

这需要 500 毫秒到 4 秒之间，具体取决于数据库的状态。

我虽然可以通过使用子查询或联接来改进它，以提高速度并减少查询计数。所以我首先想出了这样的东西:

SELECT device.name, (
  SELECT timestamp
  FROM m2m_datas 
  WHERE m2m_device_id = device.id 
  ORDER BY timestamp DESC limit 1 ) d 
FROM m2m_devices device;

这需要更多时间(大约 10 到 15 秒)，而且我没有从数据中获取所有列。

经过一番研究，我尝试了以下方法

SELECT device.name, datapoint.timestamp, datapoint.user_data 
FROM m2m_devices device 
INNER JOIN m2m_datas datapoint ON datapoint.id = (
  SELECT d.id FROM m2m_datas AS d WHERE d.m2m_device_id = device.id ORDER BY timestamp DESC LIMIT 1
)

如果我愿意，至少我可以在这里获取所有数据，但这甚至需要更多时间(25 秒到 40 秒)。

在尝试时，我对上述内容提出了一个轻微的变体，如果需要，我还可以增加我的 LIMIT 子句

SELECT device.name, datapoint.timestamp, datapoint.user_data 
FROM m2m_devices device 
INNER JOIN m2m_datas datapoint ON datapoint.id IN (
  SELECT * FROM (
    SELECT d.id FROM m2m_datas AS d WHERE d.m2m_device_id = device.id ORDER BY timestamp DESC LIMIT 1
  ) as t
)

有趣的是，这花费了更少的时间(10 秒到 17 秒)。

所以我不知道如何提高查询的性能。看来单独对所有设备进行单一查询是最好的选择。

我在这里错过了什么吗？是否有一些更好的查询可以至少在同一时间内获得相同的结果？

Answer 1

一种常见的方法如下。适当的索引，这对于大多数情况来说应该足够快......

SELECT x.* 
  FROM m2m_datas x
  JOIN 
     ( SELECT m2m_device_id
            , MAX(timestamp) timestamp 
         FROM m2m_datas
        GROUP 
           BY m2m_device_id
     ) y
    ON y.m2m_device_id = x.m2m_device_id
   AND y.timestamp = x.timestamp;