MySQL slow avg min max 使用文件排序

Question

我如何优化查询，它已经花费了大约 2 秒，“resulta”表中有大约 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;

Answer 1

在 MySQL 中，GROUP BY 表示 ORDER BY

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

MySQL docs for SELECT 中提到了这一点

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:

还有 ORDER BY Optimization