MySQL按时间戳排序+Limit查询部分设备性能异常的排查与优化

你的查询select * from device_heartbeats where device_id = ? order by time desc limit 1存在两种潜在执行路径，MySQL优化器基于统计信息选择了使用time索引反向扫描的方案，但不同device_id的记录分布导致了性能差异：

1. 执行路径选择逻辑：

   • 路径1：使用device_id索引，取出该设备的所有记录，再按time排序后取第一条。但对于记录多的设备（如device_id=3、5），排序成本高，优化器会避开这条路径。
   • 路径2：使用time索引做反向扫描（从最新时间开始遍历），逐个检查行的device_id是否匹配，找到第一条符合条件的就停止。优化器认为这条路径的成本更低，因为只需要扫描少量行就能命中。

2. 性能差异的根源：

   • 对于记录量大的设备（如device_id=3、5），最新的时间区间内大概率存在它们的心跳记录，所以反向扫描time索引时，只需要扫描极少数行就能找到匹配项，因此耗时极短。
   • 对于device_id=4这类记录量中等，但最新时间区间内几乎没有其记录的设备，反向扫描time索引时需要遍历大量无关行，直到找到第一条匹配的记录，这就导致了1.59秒的高耗时。

最根本的解决办法是创建联合索引(device_id, time)，这个索引可以让查询直接定位到目标设备的最新心跳记录，彻底消除执行计划的不确定性：

1. 创建联合索引：

   CREATE INDEX idx_device_time ON device_heartbeats(device_id, time DESC);
   

   （指定time DESC可以让索引直接按时间降序存储，进一步减少排序开销）

2. 验证优化效果：
   执行EXPLAIN查询时，应该会看到优化器选择这个联合索引，type列显示ref，Extra列显示Using index condition或直接命中索引取数，无论哪个device_id，查询耗时都会稳定在毫秒级。

3. 可选：清理冗余索引（非必须）
   如果你的业务中没有单独使用time索引的场景，可以考虑删除单独的time索引，减少写入时的索引维护成本：

   DROP INDEX IDX...bb8c ON device_heartbeats;
   

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