MySQL获取各传感器最新记录平均值慢查询优化求助

问题背景 #

需求为获取每个标记为需计算平均值（s.s_average = 1）的传感器的最新时间记录的温湿度平均值，当前查询可实现功能，但执行耗时达30秒。主表sensorlogs现有5782条数据且持续增长，使用MySQL版本为5.7.19-0ubuntu0.16.04.1，需优化查询提升执行速度。

原查询语句 #

SELECT AVG(l_temp) AS temp, AVG(l_hum) AS hum, MAX(l_timestamp) AS stamp FROM sensorlogs AS s1
    LEFT JOIN sensors s
      ON (s.s_id = s1.l_s_id)
    WHERE s.s_average = 1 AND s1.l_timestamp = (SELECT MAX(s2.l_timestamp) FROM sensorlogs AS s2 WHERE s2.l_s_id = s1.l_s_id)

执行计划 #

表结构 #

CREATE TABLE `sensorlogs` (
  `l_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `l_s_id` int(11) NOT NULL,
  `l_temp` decimal(20,1) NOT NULL,
  `l_hum` decimal(20,1) NOT NULL,
  KEY `l_timestamp` (`l_timestamp`),
  KEY `l_s_id` (`l_s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `sensors` (
  `s_id` int(11) NOT NULL AUTO_INCREMENT,
  `s_ident` varchar(500) DEFAULT NULL,
  `s_created` date NOT NULL,
  `s_average` int(11) NOT NULL DEFAULT '0',
  `s_alert` int(11) NOT NULL DEFAULT '0',
  `s_thresh` int(11) NOT NULL DEFAULT '0',
  `s_l_id` int(11) NOT NULL,
  `s_location` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

优化方案 #

1. 创建针对性复合索引 #

从执行计划可见，sensorlogs表执行了多次全表扫描，核心原因是缺少能同时支持按传感器ID筛选、按时间戳排序的索引。创建以下复合索引：

-- 加速每个传感器最新记录的查询
CREATE INDEX idx_sid_timestamp ON sensorlogs(l_s_id, l_timestamp);
-- 加速sensors表按s_average筛选并关联的操作
CREATE INDEX idx_average_sid ON sensors(s_average, s_id);

2. 重写查询，消除相关子查询 #

原查询中的相关子查询会对sensorlogs的每一行执行一次，时间复杂度为O(n²)。改为先批量获取所有传感器的最新记录，再关联计算平均值：

SELECT AVG(sl.l_temp) AS temp, AVG(sl.l_hum) AS hum, MAX(sl.l_timestamp) AS stamp
FROM sensors s
INNER JOIN (
    -- 先获取每个传感器的最新记录
    SELECT sl_inner.l_s_id, sl_inner.l_temp, sl_inner.l_hum, sl_inner.l_timestamp
    FROM sensorlogs sl_inner
    INNER JOIN (
        SELECT l_s_id, MAX(l_timestamp) AS latest_ts
        FROM sensorlogs
        GROUP BY l_s_id
    ) sl_latest ON sl_inner.l_s_id = sl_latest.l_s_id AND sl_inner.l_timestamp = sl_latest.latest_ts
) sl ON s.s_id = sl.l_s_id
WHERE s.s_average = 1;

3. 修正连接类型 #

原查询使用LEFT JOIN，但WHERE条件s.s_average = 1会自动过滤掉sensors表中无匹配的记录，等价于INNER JOIN。显式使用INNER JOIN可以让MySQL优化器选择更高效的连接顺序，优先筛选出符合条件的传感器，再关联对应的日志记录。

4. 验证优化效果 #

执行优化后的查询前，可通过EXPLAIN命令查看新的执行计划，确保：

• sensorlogs表的查询使用了idx_sid_timestamp索引（key列显示该索引名）
• type列不再是ALL，而是ref或range
• 避免了Using join buffer等低效操作

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