含JOIN与WHERE子句的GROUP BY查询性能问题及解决

问题场景 #

处理包含90万条记录的tableA时，出现明显性能差异：

• 仅按已建单列索引的date列分组的GROUP BY查询，耗时0.1-0.2秒
• 添加LEFT JOIN关联表或WHERE过滤条件后，查询耗时飙升至5秒左右

具体现象 #

1. 基础快速查询（耗时0.1-0.2秒）：

SELECT date, COUNT(*) AS occurrences
FROM tableA
GROUP BY date
ORDER BY date ASC

2. 添加LEFT JOIN后耗时变长（耗时约5秒）：

SELECT date, COUNT(date) AS occurrences
FROM tableA
LEFT JOIN tableB ON tableA_refColumn = tableB_id
GROUP BY date
ORDER BY date ASC

3. 添加WHERE过滤后耗时变长（耗时约5秒）：

SELECT date, COUNT(*) AS occurrences
FROM tableA
WHERE binColumn = 1
GROUP BY date
ORDER BY date ASC

额外测试：尝试子查询优化无效果；将SELECT date改为SELECT *时，查询耗时同样大幅增加。

原因分析 #

原date列的单列索引仅能满足基础分组查询需求。添加LEFT JOIN或WHERE条件后：

• WHERE过滤需先筛选binColumn，单列索引无法覆盖过滤逻辑，数据库需回表扫描大量数据
• LEFT JOIN需关联tableA_refColumn，单列索引无法支撑关联后的分组操作，同样需要额外的全表或大范围扫描

解决方法 #

创建覆盖复合索引，让数据库直接通过索引完成过滤、关联和分组操作，无需回表：

1. 针对带WHERE过滤的查询，创建复合索引：

CREATE INDEX idx_tableA_binColumn_date ON tableA (binColumn, date);

2. 针对带LEFT JOIN的查询，创建复合索引：

CREATE INDEX idx_tableA_refColumn_date ON tableA (tableA_refColumn, date);

创建对应索引后，上述查询耗时可大幅降低至接近基础查询的水平。

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