MySQL 从数据中提取所有设备的最近的一条记录效率对比

1、用over、orderby，耗时0.16s

SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY device_id ORDER BY update_time DESC) AS rn
    FROM
        water_level_detection
) AS t
WHERE rn = 1;

2、用concat，耗时0.13s

select * from water_level_detection u where concat(u.device_id,u.update_time) in(
	select concat(u1.device_id,max(u1.update_time)) from water_level_detection u1 group by u1.device_id);

3、用order by、group by，耗时0.18s

select u2.device_id,any_value(u2.update_time) from
 (select * from water_level_detection u order by u.update_time) u2 
 GROUP BY u2.device_id;