mysql设置图片存储最大值_MySQL使用filesort缓慢平均最大最大值

我如何优化查询,它已经花费了大约2秒,在“结果”表中有大约50万条记录,但我预计这会增加到数十亿.

SELECT

hopcount, hop, round( avg( rtt ) , 2 ) AS avg, min( rtt ) AS min, max( rtt ) AS max

FROM results

JOIN traces ON id = trace

WHERE target =9

AND rtt > -1

GROUP BY hop`

解释输出：

id select_type table type possible_keys key key_len ref rows Extra

1 SIMPLE traces ref PRIMARY,fk_traces_1_idx fk_traces_1_idx 5 const 26333 Using where; Using temporary; Using filesort

1 SIMPLE results ref trace trace 5 pinger.traces.id 7 Using where

表：

CREATE TABLE IF NOT EXISTS `results` (

`hop` int(11) DEFAULT NULL,

`trace` int(11) DEFAULT NULL,

`rtt` int(11) NOT NULL,

`seq` int(11) NOT NULL,

KEY `trace` (`trace`),

KEY `fk_hops_id` (`hop`),

KEY `seq` (`seq`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `traces` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`hopcount` smallint(6) NOT NULL,

`target` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `fk_traces_1_idx` (`target`,`id`,`time`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=52308;