mysql - 优化 'GROUP BY'-查询，消除 'Using where; Using temporary; Using filesort'

Question

我遇到了一个我似乎无法解决的 MySQL 问题。为了能够快速执行用于报告目的的 GROUP BY 查询，我已经将几个表非规范化为以下内容(该表由其他表上的触发器维护，我已经同意了与此):

DROP TABLE IF EXISTS stats;
CREATE TABLE stats (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datetime` datetime NOT NULL,
  `datetime_hour` datetime NOT NULL,
  `datetime_day` datetime NOT NULL,
  `step_id` int(11) NOT NULL,
  `check_id` int(11) NOT NULL,
  `probe_id` int(11) NOT NULL,

  `execution_step_id` int(11) NOT NULL,

  `value_of_interest` int(11) DEFAULT NULL,
  `internal` tinyint(1) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`),
  UNIQUE KEY `index_stats_on_execution_step_id` (`execution_step_id`),

  CONSTRAINT `stats_step_id_fk` FOREIGN KEY (`step_id`) REFERENCES `steps` (`id`) ON DELETE CASCADE,
  CONSTRAINT `stats_check_id_fk` FOREIGN KEY (`check_id`) REFERENCES `checks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `stats_probe_id_fk` FOREIGN KEY (`probe_id`) REFERENCES `probes` (`id`) ON DELETE CASCADE,
  CONSTRAINT `stats_execution_step_id_fk` FOREIGN KEY (`execution_step_id`) REFERENCES `execution_steps` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

无论我在表上放置什么索引，以下查询最终仍将使用 Using where; 进行解释；使用临时的；使用 filesort 或它们的任意组合(这些都会导致查询以 Not Acceptable 性能运行):

SELECT
  datetime_day,
  step_id,
  CAST(AVG(value_of_interest) AS UNSIGNED) AS value_of_interest
FROM
  stats
WHERE
  check_id = 78
  AND probe_id = 1
  AND (datetime_day >= '2014-03-28 15:58:00' AND datetime_day <= '2014-10-28 15:58:00')
  AND (internal = 0)
GROUP BY
  datetime_day, step_id
ORDER BY
  datetime_day, step_id

我需要在表定义中设置哪些索引和/或我需要如何修改查询才能以合理的查询执行计划执行？

环境规范:

• Fedora 版本 19(薛定谔的猫)
• mysql Ver 15.1 Distrib 5.5.34-MariaDB，适用于 Linux (x86_64)，使用 readline 5.1
• 6G RAM，30M 行

非常感谢您的帮助!

PS:第一次发帖，对于任何违反最佳实践的行为表示歉意。我很高兴学习...

编辑:

其中一个答案建议

ALTER TABLE `stats` ADD INDEX newindex (check_id, probe_id, internal, datetime_day, step_id);

这稍微改善了情况。我之前已经尝试过这个索引并得到以下结果:

+------+-------------+---------------------------+-------+---------------+----------+---------+------+--------+------------------------------------+
| id   | select_type | table                     | type  | possible_keys | key      | key_len | ref  | rows   | Extra                              |
+------+-------------+---------------------------+-------+---------------+----------+---------+------+--------+------------------------------------+
|    1 | SIMPLE      | stats                     | range | newindex      | newindex | 17      | NULL | 605682 | Using index condition; Using where |
+------+-------------+---------------------------+-------+---------------+----------+---------+------+--------+------------------------------------+

但是不应该有一种方法可以使用“松散/紧密索引扫描”执行查询，如 link 中提到的那样？不过，我似乎无法让它工作，而且我不确定我是否正确理解了提到的文章。

Answer 1

您有 600K 行需要扫描，因此它无法立即运行。

为什么需要CAST(AVG(value_of_interest) AS UNSIGNED)？可以通过在插入之前清理数据来避免这种情况吗？

该索引将使其“使用索引”，这将使其更快。但是，如果这不是您唯一的查询，那么添加它似乎很愚蠢。

INDEX newindex (check_id, probe_id, internal, datetime_day, step_id, value_of_interest)

开始/结束时间不正常有什么原因吗？ (15:58:00)

汇总数据仓库表的“真正”解决方案是构建和维护“汇总表”。对于相关查询，这样的表将包含 check_id、probe_id、internal、step_id、datetime_hour、SUM(value_of_interest)、COUNT(*)。前 5 个是主键。您每小时都会向表中添加另一行。报告(小时、天、周、月)将通过 SUM(sums)/SUM(counts) 获得 AVG。

更多讨论my Summary Table blog .