MySQL 对 GROUP BY YEAR 和 GROUP BY YEAR 进行文件排序月

Question

我有一个大表来存储我的网络应用程序的调试信息。问题是该表现在有 500,000 行，并且其中一个查询速度很慢，因为未使用索引。

SQL：

EXPLAIN SELECT count(*) AS `count`, month(event_date) AS `month`, year(event_date) AS `year`FROM events WHERE 1 = 1 GROUP BY year(event_date) DESC, month(event_date) DESC LIMIT 6;

结果：

SIMPLE  events  index   NULL    event_date  8   NULL    139358  Using index; Using temporary; Using file sort

这是表结构。

CREATE TABLE IF NOT EXISTS `events` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Event Primary Key',
`event_number` int(11) NOT NULL,
`user_id` int(11) unsigned NOT NULL COMMENT 'User ID',
`server_id` int(11) unsigned DEFAULT NULL COMMENT 'The ID of the remote log client',
`remote_id` int(11) unsigned DEFAULT NULL COMMENT 'The Event Primary Key from the remote client',
`event_date` datetime NOT NULL COMMENT 'Event Datetime in local timezone',
`event_date_utc` datetime NOT NULL COMMENT 'Event Datetime in UTC timezone',
`event_type` varchar(255) NOT NULL COMMENT 'The type of event',
`event_source` varchar(255) NOT NULL COMMENT 'Text description of the source of the event',
`event_severity` varchar(255) NOT NULL COMMENT 'Notice, Warning etc',
`event_file` text NOT NULL COMMENT 'The full file location of the source of the event',
`event_file_line` int(11) NOT NULL COMMENT 'The line in the file that triggered the event',
`event_ip_address` varchar(255) NOT NULL COMMENT 'IP Address of the user that triggered the event',
`event_summary` varchar(255) NOT NULL COMMENT 'A summary of the description',
`event_description` text NOT NULL COMMENT 'Full description of the event',
`event_trace` text NOT NULL COMMENT 'Full PHP trace',
`event_synced` int(1) unsigned DEFAULT '0',
PRIMARY KEY (`id`),
KEY `event_type` (`event_type`),
KEY `event_source` (`event_source`),
KEY `user_id` (`user_id`),
KEY `server_id` (`server_id`),
KEY `event_date` (`event_date`)
)

如果有人有任何想法可以在不进行文件排序的情况下获得相同的结果，那就太棒了！

Answer 1

GROUP BY 意味着 MySQL 中的 ORDER BY

因此，请尝试添加 ORDER BY NULL：这通常会消除文件排序

请参阅 "ORDER BY Optimization"

Answer 2

您的关键问题是您没有指定 WHERE 子句。您使用 WHERE 1=1 是毫无意义的。问题是您试图从 MySQL 获取 YEAR 和 MONTH 而不限制行数，因此它处理 MONTH(..) 和 YEAR(.. .) 在能够处理 GROUP 之前对每一行进行处理。

事实上，在我之前的建议之后，它仍然没有使用 INDEX，这表明您的查询比您所透露的更多，如果是这种情况，请告诉我，我可以更轻松地帮助您。否则，我建议您检查以下内容（尽管我不得不猜测您的目的，因为您没有说明您想要实现的目标）

如果您是在过去 6 个日历月之后，那么以下内容也会有很大帮助。

SELECT
    COUNT(id) AS `count`, 
    MONTH(event_date) AS `month`, 
    YEAR(event_date) AS `year`
FROM events
-- Get the first day of this month, and subtract 6 months
WHERE event_date > SUBDATE(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL 6 MONTH)
GROUP BY `year` DESC, `month` DESC;

如果您有其他 WHERE 标准，这将更改给出的建议，因此如果是这种情况，请更新

Answer 3

除了其他人发布的内容之外：

如果您运行 EXPLAIN SELECT... 并且 MySQL 报告它对该查询没有使用索引（或者不是您想要的索引），您可以通过以下方式解决此问题使用SELECT...FORCE INDEX...查询数据。有关其语法的更多详细信息，请查看此处： http:// /dev.mysql.com/doc/refman/5.6/en/index-hints.html

Answer 4

1. 您正在使用 SELECT *，这意味着选择所有行，以便扫描整个表 - 尝试选择要显示的特定行

2. 此外，没有参数来过滤数据，因此读取并返回整个表，尝试按日期或其他参数进行限制