带GROUP BY的DISTINCT COUNT查询虽有索引仍过慢，如何优化？

针对你的4000万行数据表的统计需求，结合给出的执行计划和表结构，我来分享几个能有效解决Creating sort index性能瓶颈的优化方法：

1. 创建针对性的复合覆盖索引 #

当前你使用的是单独的zone索引，查询时需要回表获取creation_date和vessel_imo，而且分组排序需要额外的文件排序操作。咱们可以创建一个复合覆盖索引，把查询需要用到的所有字段都包含进去，同时匹配查询的过滤和分组顺序：

CREATE INDEX idx_zone_creation_vessel ON vessel_position(zone, creation_date, vessel_imo);

这个索引的优势在于：

• 以zone开头，完美匹配WHERE zone IS NOT NULL的过滤条件，快速定位目标数据
• 接着是creation_date，让同一区域内的数据按时间有序排列，刚好匹配DATE_FORMAT(creation_date, '%Y%u')的分组逻辑，避免额外排序
• 最后包含vessel_imo，实现覆盖索引效果，查询时无需回表读取原数据，直接从索引中获取所需字段

创建这个索引后，执行计划里的Using filesort和Creating sort index应该会消失，查询效率会大幅提升。

2. 利用MySQL 8.0+的函数索引（如果版本支持） #

如果你的MySQL版本是8.0及以上，可以直接针对分组用到的DATE_FORMAT(creation_date, '%Y%u')创建函数索引，进一步贴合查询逻辑：

CREATE INDEX idx_zone_week_vessel ON vessel_position(zone, DATE_FORMAT(creation_date, '%Y%u'), vessel_imo);

这样分组时直接匹配索引中预计算好的周格式数据，省去了实时计算和排序的开销，性能会更优。

3. 建立汇总表做数据预处理 #

由于你的查询是固定的“按区域按周统计”，属于周期性的聚合需求，对于4000万行的大数据表，最彻底的优化方式是提前汇总数据：

• 创建一个汇总表：

CREATE TABLE vessel_zone_weekly_summary (
    zone VARCHAR(50) NOT NULL,
    year_week VARCHAR(6) NOT NULL,
    vessel_count INT NOT NULL,
    PRIMARY KEY (zone, year_week)
);

• 定时执行（比如每天凌晨）数据同步脚本，将历史数据的统计结果写入汇总表：

REPLACE INTO vessel_zone_weekly_summary (zone, year_week, vessel_count)
SELECT zone, DATE_FORMAT(creation_date, '%Y%u') AS year_week, COUNT(DISTINCT vessel_imo) AS vessel_count
FROM vessel_position
WHERE creation_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY zone, year_week;

• 后续查询直接从汇总表读取：

SELECT zone, year_week AS date, vessel_count FROM vessel_zone_weekly_summary;

这种方式把大数据量的聚合计算从实时查询转移到后台定时任务，查询速度几乎是瞬间的，非常适合报表类的统计需求。

4. 调整GROUP BY顺序（配合复合索引） #

确保你的GROUP BY顺序和复合索引的前缀顺序一致，也就是GROUP BY zone, creation_date（或者对应的周格式字段），这样MySQL可以直接利用索引的有序性进行分组，不需要额外排序。

补充说明当前执行计划的问题 #

当前执行计划中使用zone索引扫描了2100多万行，然后需要对这些数据按zone和date分组排序，这就是Creating sort index（也就是文件排序）耗时的原因。上面的优化措施都是围绕消除这个排序开销、减少数据扫描量来设计的。

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