鍍金池/ 問(wèn)答/數(shù)據(jù)庫(kù)  網(wǎng)絡(luò)安全/ MySQL5.7中60萬(wàn)數(shù)據(jù)的表分組統(tǒng)計(jì)很慢

MySQL5.7中60萬(wàn)數(shù)據(jù)的表分組統(tǒng)計(jì)很慢

我有個(gè)表service_message大概有60萬(wàn)條數(shù)據(jù)，表中記錄了不同設(shè)備收到消息的時(shí)間：

SHOW CREATE TABLE service_message

CREATE TABLE `service_message` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` INT(11) NOT NULL COMMENT '設(shè)備Id',
  `property_id` INT(11) NOT NULL COMMENT '服務(wù)Id',
  `value` TEXT NOT NULL COMMENT '數(shù)值',
  `time` DATETIME NOT NULL COMMENT '數(shù)據(jù)上報(bào)時(shí)間',
  PRIMARY KEY (`id`),
  KEY `device_id` (`device_id`),
  KEY `time` (`time`),
  KEY `property_id` (`property_id`)
) ENGINE=INNODB AUTO_INCREMENT=844512 DEFAULT CHARSET=utf8

clipboard.png

clipboard.png

clipboard.png

clipboard.png

我需要從中按設(shè)備id統(tǒng)計(jì)最新的時(shí)間值。我的sql語(yǔ)句是這樣寫的：

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

clipboard.png

查詢時(shí)間要3~4秒，很慢。

我用explain看了執(zhí)行計(jì)劃，但是我是小白，看不懂：

clipboard.png

所以。我的問(wèn)題是，有沒(méi)有優(yōu)化的方法？

回答
編輯回答
神經(jīng)質(zhì)

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

       service_message GROUP BY device_id order by null; ```
  2. 不需要實(shí)時(shí)結(jié)果的話，把查詢結(jié)果緩存起來(lái)。
2017年6月3日 14:14
編輯回答
貓館

把device_id和time建立聯(lián)合索引，應(yīng)該也是很快的。

2017年3月10日 21:11
編輯回答
司令

看你的數(shù)據(jù)好像id越大，time就越大，所以不妨換個(gè)思路

SELECT
    device_id,
    `time`
FROM
    service_message
WHERE
    id IN (
        SELECT
            max(id)
        FROM
            service_message
        GROUP BY
            device_id
    )
2018年6月22日 19:51
編輯回答
脾氣硬

這個(gè)表已經(jīng)60多w數(shù)據(jù)了，目前也是正在增長(zhǎng)中，不建議使用動(dòng)態(tài)查詢得出結(jié)果的方式。可否另外鍵創(chuàng)建一張統(tǒng)計(jì)表

CREATE TABLE `service_message` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` INT(11) NOT NULL COMMENT '設(shè)備Id',
  `last_time` DATETIME NOT NULL COMMENT '數(shù)據(jù)的最新上報(bào)時(shí)間',
  PRIMARY KEY (`id`),
  KEY `device_id` (`device_id`),
  KEY `last_time` (`time`),
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

使用腳本的方式，每天定時(shí)是計(jì)算記錄在表內(nèi)，下次查看結(jié)果的時(shí)候可以直接查詢統(tǒng)計(jì)表。

2017年8月29日 06:24