MySQL优化GROUP BY索引性能？

Question

我需要使用 volkszaehler.org 从超过一百万行的表中检索数据，下面是 ORM 创建的内容:

CREATE TABLE `data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `channel_id` int(11) DEFAULT NULL,
  `timestamp` bigint(20) NOT NULL,
  `value` double NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ts_uniq` (`channel_id`,`timestamp`),
  KEY `IDX_ADF3F36372F5A1AA` (`channel_id`)
)

现在，选择分组数据很慢，尤其是在像 Raspberry Pi 这样的低性能平台上运行时:

SELECT MAX(timestamp) AS timestamp, SUM(value) AS value, COUNT(timestamp) AS count 
FROM data WHERE channel_id = 4 AND timestamp >= 1356994800000 AND timestamp <= 1375009341000 
GROUP BY YEAR(FROM_UNIXTIME(timestamp/1000)), DAYOFYEAR(FROM_UNIXTIME(timestamp/1000));

解释:

SIMPLE  data    ref ts_uniq,IDX_ADF3F36372F5A1AA    ts_uniq 5   const   2066    Using where; Using temporary; Using filesort

查询需要遍历 50k 条记录，在 Core i5 上耗时 1.5 秒，在 RasPi 上耗时 6 秒。

除了减少数据量之外，还有什么可以提高性能的吗？

Answer 1

增加数据量，而不是减少数据量，这就是您所需要的:您在 GROUP BY 子句中有两个函数，如果这样，您计算 YEAR(FROM_UNIXTIME(timestamp/1000)) 和 DAYOFYEAR(FROM_UNIXTIME(timestamp/1000)) 预先在触发器中并将值存储到其他字段中，您的 SELECT 语句会快得多。

除此之外，您可以将 timestamp 除以 1000*3600*24=86400000 并仅按一个字段进行分组，从而将其截断到最近的一天，因为我看不到分组中的一个点分别按年份和日期分组，当您只能按日期分组时:

SELECT 
 MAX(timestamp) AS timestamp, 
 SUM(value) AS value, 
 COUNT(timestamp) AS count 
FROM data WHERE 
 channel_id = 4 AND 
 timestamp >= 1356994800000 AND 
 timestamp <= 1375009341000 
GROUP BY timestamp/86400000;

就我个人而言，之后我会添加日期字段，对其进行索引并在触发器中更新它，这样我就可以从 GROUP BY 中删除所有算术表达式。在这种情况下，将使用索引。