优化大表中带范围查询与GROUP BY的MySQL慢查询

先来梳理下你的场景：你有一个记录职位浏览数据的表jobs_view_stats，现在要统计2022年11月每个职位的浏览次数，但当前查询耗时4分钟。我们先整理下现有信息，再一步步给出针对性优化方案。

现有表结构 #

CREATE TABLE `jobs_view_stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) NOT NULL,
  `created_at` datetime NOT NULL,
  `account_id` int(11) DEFAULT NULL,
  `country` varchar(2) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_D05BC6799FDS15210` (`job_id`),
  KEY `FK_YTGBC67994591257` (`account_id`),
  KEY `jobs_view_stats_created_at_id_index` (`created_at`,`id`),
  CONSTRAINT `FK_YTGBC67994591257` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `job_views_jobs_id_fk` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=79976587 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='New jobs views system'

当前执行的查询语句 #

SELECT COUNT(id) as view, job_id
from jobs_view_stats
WHERE jobs_view_stats.created_at between '2022-11-01 00:00:00' AND '2022-11-30 23:59:59'
GROUP BY jobs_view_stats.job_id

查询执行计划 #

问题分析 #

从执行计划能看出两个核心性能瓶颈：

• Using temporary：数据库需要创建临时表来存储分组中间结果
• Using filesort：需要对临时表中的数据排序来完成分组

当前使用的索引(created_at, id)只能满足时间过滤的需求，但无法覆盖分组和统计的逻辑，导致数据库不得不回表取数据，再做额外的排序和临时表操作，处理150万+数据时自然会慢。

优化方案（按优先级排序） #

1. 创建覆盖索引（最推荐，立竿见影）

创建一个包含查询所需所有字段的复合索引，让数据库不需要回表，直接通过索引完成过滤、分组和统计：

CREATE INDEX idx_created_at_job_id ON jobs_view_stats (created_at, job_id);

这个索引的优势：

• 前缀created_at能快速定位到指定时间范围内的所有行，匹配WHERE条件
• 索引包含job_id，分组时可以直接用索引中的值，彻底避免Using filesort和临时表
• 配合调整查询语句，把COUNT(id)换成COUNT(*)（因为created_at是NOT NULL字段，两者结果一致，但COUNT(*)统计效率更高）：

SELECT COUNT(*) as view, job_id
from jobs_view_stats
WHERE created_at >= '2022-11-01' AND created_at < '2022-12-01'
GROUP BY job_id;

这里把时间范围改成created_at < '2022-12-01'，还能避免漏掉2022-11-30 23:59:59.999这类接近午夜的记录。

2. 预计算汇总表（适合频繁查询固定时间范围的场景）

如果这类月度/季度统计查询经常执行，且对数据实时性要求不高，可以提前预计算结果：

• 先创建汇总表：

CREATE TABLE job_view_monthly_stats (
    job_id int(11) NOT NULL,
    year_month varchar(7) NOT NULL, -- 格式如'2022-11'
    view_count int(11) NOT NULL DEFAULT 0,
    PRIMARY KEY (job_id, year_month),
    KEY idx_year_month (year_month)
) ENGINE=InnoDB;

• 用定时任务（比如MySQL事件、应用层脚本）在低峰期执行汇总：

INSERT INTO job_view_monthly_stats (job_id, year_month, view_count)
SELECT job_id, '2022-11' as year_month, COUNT(*)
FROM jobs_view_stats
WHERE created_at >= '2022-11-01' AND created_at < '2022-12-01'
GROUP BY job_id
ON DUPLICATE KEY UPDATE view_count = VALUES(view_count);

之后查询直接从汇总表取数，速度会快到毫秒级：

SELECT view_count as view, job_id FROM job_view_monthly_stats WHERE year_month = '2022-11';

3. 调整MySQL配置辅助优化

如果暂时无法创建新索引，可以调整临时表相关配置，让临时表尽量在内存中创建，避免写磁盘：

tmp_table_size = 256M
max_heap_table_size = 256M

不过这只是临时缓解，核心优化还是要靠覆盖索引。

内容的提问来源于stack exchange，提问作者Yasir