在mysql中使用avg的慢速查询

Question

我有一张桌子：

 CREATE TABLE `table1` (
  `object` varchar(255) NOT NULL,
  `score` decimal(10,3) NOT NULL,
  `timestamp` datetime NOT NULL
  KEY `ex` (`object`,`score`,`timestamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

有910万行，我正在运行以下查询：

SELECT `object`, `timestamp`, AVG(score) as avgs
  from `table1`
 where timestamp >= '2011-12-14'
   AND timestamp <= '2011-12-13'
 group by `object`
 order by `avgs` ASC limit 100;

日期来自用户输入。查询需要6-10秒，取决于日期的范围。运行时似乎随着行数的增加而增加。

我能做些什么来改善这一点呢？

我试过：

• 修改索引(将查询时间从最大13秒降到最大10秒)
• 将存储移动到快速SAN (无论参数如何，查询时间减少了大约0.1秒)。

当查询运行时，服务器上的CPU和内存负载似乎不会太高。

Answer 1

fast SAN表现更好的原因是

是因为您的查询要求复制到临时表，

并需要对大型结果集进行文件排序。

你有五个讨厌的因素。

对象的query

• group-by

• sorting

• varchar 255
• range
• 错误的索引

把时间戳分解成两个字段，

date, time

为对象构建另一个引用表，

因此，您可以使用整数，例如object_id (而不是varchar 255)来表示对象。

重新建立索引

date (date type), object_id

将查询更改为

where date IN('2011-12-13', '2011-12-14', ...)