含Group By、Having子句的MySQL查询为何执行极慢？

这种情况我之前在小内存服务器上折腾MySQL时碰到过，太闹心了！咱们来一步步捋清楚问题出在哪，以及怎么解决：

首先得明白为什么加了SQL_CALC_FOUND_ROWS、GROUP BY或HAVING就变慢甚至pending：

• 内存资源吃紧：你的系统只有2GB内存，MySQL默认配置没针对大数据量查询优化。GROUP BY/HAVING需要做排序或创建临时表，SQL_CALC_FOUND_ROWS还要额外统计匹配的总行数，这些操作都要消耗大量内存。当内存不够时，MySQL会被迫使用磁盘临时表，磁盘IO的速度比内存慢几个数量级，直接导致查询卡住。
• 缺少关键索引：如果GROUP BY、关联字段或者过滤条件的字段没有索引，MySQL就得做全表扫描+手动排序，10万行数据的排序开销会直接把系统拖垮，尤其是内存不足时，磁盘排序慢到离谱。SQL_CALC_FOUND_ROWS本身也会触发额外的全表统计，没索引的话雪上加霜。
• 临时表配置不合理：MySQL的tmp_table_size和max_heap_table_size默认值通常很小（比如几十MB），当GROUP BY需要的临时表超过这个阈值，就会转成MyISAM磁盘临时表，速度直接暴跌。

1. 调整MySQL内存配置 #

修改MySQL的配置文件（my.cnf或my.ini，根据系统不同路径可能不同），针对性调大临时表和排序相关的参数，注意别超过系统可用内存（2GB系统要留至少512MB给系统和其他进程）：

# 调大内存临时表的上限，避免转磁盘
tmp_table_size = 256M
max_heap_table_size = 256M

# 给排序操作分配足够内存，避免磁盘排序
sort_buffer_size = 8M

# 如果是InnoDB引擎，适当调大缓冲池（比如设成512M，别贪多）
innodb_buffer_pool_size = 512M

修改后重启MySQL生效。

2. 给关键字段加索引 #

这是提升GROUP BY/关联查询速度最有效的手段：

• 针对GROUP BY的字段单独建索引，比如你的查询是GROUP BY group_id，就执行：

  CREATE INDEX idx_chitentry_groupid ON chitentry(group_id);
  

• 如果查询里有WHERE过滤条件，把WHERE字段和GROUP BY字段组合成联合索引，效果更好，比如WHERE status = 1 GROUP BY group_id：

  CREATE INDEX idx_chitentry_status_groupid ON chitentry(status, group_id);
  

• 确保两张表的关联字段都有索引，比如chitentry.group_id和chitentrygroup.id都要建索引，避免关联时的全表扫描。

3. 替换SQL_CALC_FOUND_ROWS #

SQL_CALC_FOUND_ROWS在大数据量+GROUP BY的场景下效率极低，建议拆成两个查询：

-- 第一步：获取分页结果
SELECT col1, col2 FROM chitentry 
JOIN chitentrygroup ON chitentry.group_id = chitentrygroup.id
WHERE ... 
GROUP BY ... 
LIMIT ...;

-- 第二步：单独统计总行数
SELECT COUNT(*) FROM chitentry 
JOIN chitentrygroup ON chitentry.group_id = chitentrygroup.id
WHERE ...;

虽然是两次查询，但有索引的话COUNT(*)会非常快，比SQL_CALC_FOUND_ROWS高效得多。

4. 优化GROUP BY和HAVING逻辑 #

• 尽量把过滤条件提前到WHERE里，减少GROUP BY需要处理的数据量。比如把HAVING里针对原始行的条件移到WHERE：
  原查询：

  SELECT group_id, SUM(amount) FROM chitentry 
  GROUP BY group_id 
  HAVING amount > 1000;
  

  优化后：

  SELECT group_id, SUM(amount) FROM chitentry 
  WHERE amount > 1000 
  GROUP BY group_id;
  

• 如果必须用HAVING，确保聚合计算尽量简单，避免嵌套复杂函数。

5. 检查磁盘IO情况 #

如果内存调优和索引都加了还是慢，看看服务器的磁盘是不是机械硬盘（HDD），HDD的随机IO性能很差，换成SSD会有质的提升。

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