SQL查询提速咨询：新手GROUP BY查询性能优化

嘿，作为SQL新手能写出这样的查询已经很棒了！不过咱们可以从逻辑准确性和性能优化两个方面来改进，先帮你梳理下影响当前查询速度的因素，再给出具体的优化方案。

一、影响查询性能的核心因素 #

你的查询耗时主要来自这几个点：

• 两次全表扫描：两个子查询各自完整遍历了一遍MY_TABLE，相当于数据库要读两次整张表的数据，如果表的行数很多，这会带来很大的IO开销。
• 重复的COUNT(DISTINCT)计算：COUNT(DISTINCT CustomerID)本身是比较耗时的操作（需要去重排序），你现在在两个子查询里各做了一次，相当于重复执行了两次高成本计算。
• 冗余的二次聚合：先对主/客队分别分组聚合，再用UNION ALL合并后再次分组求和，多了一层不必要的聚合步骤，增加了内存和CPU的消耗。
• 缺少针对性索引：如果没有合适的索引，数据库只能做全表扫描，没法快速定位到分组和计算需要的数据，这是大多数慢查询的根源。

另外悄悄提一句：你的原查询存在逻辑错误——如果同一个观众既看过某球队的主场比赛，又看过它的客场比赛，会被统计两次（主场算1，客场算1，最后SUM成2），但实际应该只算1个独立观众。如果这不是你的预期，咱们得先修正这个逻辑~

二、具体优化方案 #

1. 修正逻辑+减少全表扫描次数 #

把两次全表扫描合并成一次，先将主客队数据拆分为统一的格式，再一次性完成去重统计：

SELECT 
    League,
    YEAR(MatchDate) AS Season,  -- 用YEAR()替代DATE_FORMAT更高效
    Team AS Teams,
    COUNT(DISTINCT CustomerID) AS totalnum
FROM (
    -- 一次扫描表，同时拆分主客队为两行数据
    SELECT 
        League,
        MatchDate,
        CustomerID,
        HomeTeam AS Team
    FROM MY_TABLE
    UNION ALL
    SELECT 
        League,
        MatchDate,
        CustomerID,
        AwayTeam AS Team
    FROM MY_TABLE
) AS combined
GROUP BY League, Season, Teams
ORDER BY totalnum DESC;

这个版本只扫描一次表，并且直接统计每个球队在赛季联赛中的独立观众总数（避免了重复统计同一观众的问题），逻辑更准确，性能也更好。

2. 添加复合索引，彻底提速 #

创建覆盖索引可以让数据库不用回表查询，直接从索引里拿到所有需要的数据：

-- 创建针对主客队场景的复合索引
CREATE INDEX idx_match_league_date_team_customer ON MY_TABLE (League, MatchDate, HomeTeam, CustomerID);
CREATE INDEX idx_match_league_date_awayteam_customer ON MY_TABLE (League, MatchDate, AwayTeam, CustomerID);

如果你的数据库支持函数索引（比如MySQL 8.0+），还可以把YEAR(MatchDate)提前计算好，进一步优化分组效率：

CREATE INDEX idx_match_league_season_team_customer ON MY_TABLE (League, YEAR(MatchDate), HomeTeam, CustomerID);
CREATE INDEX idx_match_league_season_awayteam_customer ON MY_TABLE (League, YEAR(MatchDate), AwayTeam, CustomerID);

这时候查询里的Season用YEAR(MatchDate)就能直接利用索引的排序，分组速度会大幅提升。

3. 进一步优化COUNT(DISTINCT) #

如果你的表中CustomerID和比赛的组合重复很多（比如同一个观众多次看同一球队的比赛），可以先去重再统计：

SELECT 
    League,
    Season,
    Team AS Teams,
    COUNT(*) AS totalnum
FROM (
    -- 先对观众-球队-赛季-联赛去重
    SELECT DISTINCT
        League,
        YEAR(MatchDate) AS Season,
        CustomerID,
        HomeTeam AS Team
    FROM MY_TABLE
    UNION ALL
    SELECT DISTINCT
        League,
        YEAR(MatchDate) AS Season,
        CustomerID,
        AwayTeam AS Team
    FROM MY_TABLE
) AS unique_views
GROUP BY League, Season, Teams
ORDER BY totalnum DESC;

这种方式把去重操作提前，减少后续分组的数据量，在某些场景下比COUNT(DISTINCT)更高效。

三、额外的小建议 #

• 尽量用YEAR(MatchDate)替代DATE_FORMAT(MatchDate, '%Y')，前者是内置的日期函数，计算更快，也更容易利用索引。
• 避免在GROUP BY和ORDER BY里使用别名（虽然很多数据库支持），直接用字段或函数表达式，减少数据库的解析开销。

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