MySQL如何查询每组uuid最新两条数据的server_time分钟差值

索引优化（必做，保证200万数据查询性能） #

创建覆盖联合索引，查询时无需回表、无需全表扫描，直接通过索引定位每个uuid的最新两条记录：

CREATE INDEX idx_uuid_server_time ON `Sensor Data` (uuid, server_time DESC);

MySQL 8.0+ 最优查询方案 #

用窗口函数实现，逻辑清晰性能最优，200万条数据查询耗时通常在1秒以内：

SELECT 
  uuid,
  TIMESTAMPDIFF(MINUTE, MIN(server_time), MAX(server_time)) AS diff_minute
FROM (
  SELECT 
    uuid,
    server_time,
    ROW_NUMBER() OVER (PARTITION BY uuid ORDER BY server_time DESC) AS rn
  FROM `Sensor Data`
) t
WHERE rn <= 2
GROUP BY uuid
HAVING COUNT(*) = 2; -- 过滤仅存在1条记录的uuid，无需计算差值

查询结果说明：

• diff_minute 就是每个uuid最新两条记录的时间差，单位为分钟
• 如果不需要过滤单条记录的uuid，可以删除HAVING子句，单条记录的差值会返回NULL

MySQL 5.x 兼容查询方案 #

如果使用不支持窗口函数的低版本MySQL，用关联查询实现，同样走索引性能达标：

SELECT 
  t1.uuid,
  TIMESTAMPDIFF(MINUTE, MAX(t2.server_time), t1.latest_time) AS diff_minute
FROM (
  SELECT uuid, MAX(server_time) AS latest_time
  FROM `Sensor Data`
  GROUP BY uuid
) t1
INNER JOIN `Sensor Data` t2 
  ON t2.uuid = t1.uuid 
  AND t2.server_time < t1.latest_time
GROUP BY t1.uuid, t1.latest_time;

内容的提问来源于stack exchange，提问作者dev Joshi