mysql file sort_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;

最佳答案 在MySQL中,GROUP BY意味着ORDER BY

要删除此隐式排序,请添加ORDER BY NULL

If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns. To avoid the overhead of sorting that GROUP BY produces, add ORDER BY NULL: