如何简化多连接SQL查询？求长查询优化技巧

一、简化后的查询语句 #

原查询通过三个独立子查询分别统计主场胜、客场胜和平局数据，再进行JOIN，存在重复扫描match和team表的问题，性能冗余。以下两种简化方案均能实现相同逻辑，且效率更高：

方案1：条件聚合直接统计 #

SELECT 
    t.team_api_id AS team_id,
    t.team_long_name AS 球队名称,
    -- 计算总胜场：主场胜+客场胜
    (SUM(CASE WHEN m.home_team_api_id = t.team_api_id AND m.home_team_goal > m.away_team_goal THEN 1 ELSE 0 END) +
     SUM(CASE WHEN m.away_team_api_id = t.team_api_id AND m.away_team_goal > m.home_team_goal THEN 1 ELSE 0 END)) * 1.0 /
    -- 计算总参赛场次
    COUNT(CASE WHEN m.home_team_api_id = t.team_api_id OR m.away_team_api_id = t.team_api_id THEN m.id END) AS winratio（胜率）
FROM team t
LEFT JOIN match m 
    ON t.team_api_id = m.home_team_api_id OR t.team_api_id = m.away_team_api_id
GROUP BY t.team_api_id, t.team_long_name
-- 过滤掉没有参赛记录的球队
HAVING COUNT(CASE WHEN m.home_team_api_id = t.team_api_id OR m.away_team_api_id = t.team_api_id THEN m.id END) > 0
ORDER BY winratio（胜率） DESC
LIMIT 10;

方案2：用CTE拆分比赛记录（更易读） #

WITH team_match_records AS (
    -- 把每场比赛拆成两条记录：主场球队一条，客场球队一条
    SELECT 
        home_team_api_id AS team_id,
        home_team_goal AS team_goal,
        away_team_goal AS opponent_goal
    FROM match
    UNION ALL
    SELECT 
        away_team_api_id AS team_id,
        away_team_goal AS team_goal,
        home_team_goal AS opponent_goal
    FROM match
)
SELECT 
    t.team_api_id AS team_id,
    t.team_long_name AS 球队名称,
    SUM(CASE WHEN team_goal > opponent_goal THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS winratio（胜率）
FROM team t
JOIN team_match_records ON t.team_api_id = team_match_records.team_id
GROUP BY t.team_api_id, t.team_long_name
ORDER BY winratio（胜率） DESC
LIMIT 10;

两种方案均只需扫描match表一次，避免了原查询的三次重复扫描，性能提升明显，同时语句结构更简洁。

二、高效多连接SQL查询技巧 #

• 用条件聚合替代多子查询JOIN：多次分组后JOIN会重复扫描表，条件聚合能在一次表扫描中完成多个统计维度的计算，大幅减少IO开销。
• 给连接字段加索引：match.home_team_api_id、match.away_team_api_id、team.team_api_id这类JOIN关键字段必须建立索引，否则会触发全表扫描，性能暴跌。
• 替换LEFT JOIN为INNER JOIN（如果允许）：如果业务逻辑不需要保留无匹配数据的行，用INNER JOIN代替LEFT JOIN，能缩小结果集，减少后续处理量。
• 提前过滤数据：在JOIN前通过WHERE子句过滤掉无关数据（比如特定赛季、联赛），缩小处理范围，避免不必要的计算。
• 避免OR型JOIN条件：OR可能导致索引失效，像方案2那样用UNION ALL拆分数据，能让数据库更好地利用索引优化查询。
• 明确GROUP BY字段：确保GROUP BY包含所有非聚合的SELECT字段，避免数据库隐式处理带来的性能损耗和结果不确定性。
• 只查询需要的字段：不要用SELECT *，只保留业务需要的字段，减少数据传输和内存占用。

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