MySQL玩家活动统计查询执行耗时过长，求优化方案

现有一个统计企业旗下玩家步行、骑行活动总得分、总步数、总骑行距离的MySQL查询，数据库仅存数百条记录，但执行耗时约215秒，原查询语句如下：

SELECT sum(score) as total_score,
                (SELECT sum(pa.distance_covered) 
                from activities a, players_score b, players_activity pa 
                where a.id=b.id_activity 
                and a.id = pa.id_activity 
                and a.atype='Read data / Sync - Walk' 
                and b.id_company=company.id
                ) as total_steps,
                (SELECT sum(pa.distance_covered) 
                from activities a, players_score b, players_activity pa 
                where a.id=b.id_activity 
                and a.id = pa.id_activity 
                and a.atype='Read data / Sync - Cycling' 
                and b.id_company=company.id
                ) as total_cycling,
                company.id_challenge 
        FROM `players_score` 
            join company on players_score.id_company=company.id 
        group by company.id_challenge 
        order by total_score DESC

问题分析 #

原查询的核心问题是分组后每个企业都会触发两次独立的子查询，每次子查询都要关联三张表做扫描计算，相当于重复执行了N次（N为企业分组数）全量关联，导致性能急剧下降。另外隐式逗号连接的写法也不利于优化器做关联优化。

优化后的查询语句 #

用条件聚合替代子查询，只需要一次关联所有表即可完成所有统计：

SELECT 
    SUM(ps.score) AS total_score,
    SUM(CASE WHEN a.atype = 'Read data / Sync - Walk' THEN pa.distance_covered ELSE 0 END) AS total_steps,
    SUM(CASE WHEN a.atype = 'Read data / Sync - Cycling' THEN pa.distance_covered ELSE 0 END) AS total_cycling,
    c.id_challenge
FROM players_score ps
JOIN company c ON ps.id_company = c.id
JOIN activities a ON a.id = ps.id_activity
JOIN players_activity pa ON a.id = pa.id_activity
GROUP BY c.id_challenge
ORDER BY total_score DESC;

优化说明 #

1. 避免重复子查询：通过CASE WHEN在一次聚合中完成不同活动类型的统计，不再为每个分组重复执行子查询
2. 显式JOIN语法：让MySQL优化器更清晰地识别表关联逻辑，便于生成更优的执行计划
3. 减少表扫描次数：原查询多次扫描activities、players_score、players_activity三张表，优化后仅需一次扫描

进一步性能提升建议 #

给相关字段添加联合索引，让查询直接通过索引获取数据，避免全表扫描：

• 给players_score建立联合索引：idx_ps_company_activity_score(id_company, id_activity, score)
• 给activities建立联合索引：idx_a_id_type(id, atype)
• 给players_activity建立联合索引：idx_pa_activity_distance(id_activity, distance_covered)

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