MySQL5.7中60万数据的表分组统计很慢

Question

我有个表service_message大概有60万条数据，表中记录了不同设备收到消息的时间：

SHOW CREATE TABLE service_message

CREATE TABLE `service_message` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` INT(11) NOT NULL COMMENT '设备Id',
  `property_id` INT(11) NOT NULL COMMENT '服务Id',
  `value` TEXT NOT NULL COMMENT '数值',
  `time` DATETIME NOT NULL COMMENT '数据上报时间',
  PRIMARY KEY (`id`),
  KEY `device_id` (`device_id`),
  KEY `time` (`time`),
  KEY `property_id` (`property_id`)
) ENGINE=INNODB AUTO_INCREMENT=844512 DEFAULT CHARSET=utf8

我需要从中按设备id统计最新的时间值。我的sql语句是这样写的：

SELECT device_id,MAX(`time`) AS`time` FROM service_message GROUP BY device_id

查询时间要3~4秒，很慢。

我用explain看了执行计划，但是我是小白，看不懂：

所以。我的问题是，有没有优化的方法？

Answer 1

看你的数据好像id越大，time就越大，所以不妨换个思路

SELECT
    device_id,
    `time`
FROM
    service_message
WHERE
    id IN (
        SELECT
            max(id)
        FROM
            service_message
        GROUP BY
            device_id
    )

Answer 2

1. . 取消默认的排序： `sql SELECT device_id,MAX(time) AStime FROM

      service_message GROUP BY device_id order by null; ```

2. 不需要实时结果的话，把查询结果缓存起来。

Answer 3

这个表已经60多w数据了，目前也是正在增长中，不建议使用动态查询得出结果的方式。可否另外键创建一张统计表

CREATE TABLE `service_message` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` INT(11) NOT NULL COMMENT '设备Id',
  `last_time` DATETIME NOT NULL COMMENT '数据的最新上报时间',
  PRIMARY KEY (`id`),
  KEY `device_id` (`device_id`),
  KEY `last_time` (`time`),
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

使用脚本的方式，每天定时是计算记录在表内，下次查看结果的时候可以直接查询统计表。

Answer 4

把device_id和time建立联合索引，应该也是很快的。