MySQL优化查询：按胜率排序生成Top30用户榜单

核心思路 #

放弃遍历用户表的低效方式，直接从games表拆分逗号分隔的胜负用户ID，统计每个用户的胜场/负场数，再计算胜率排序。这种方式避免了大量用户表与游戏表的关联查询，效率提升明显。

具体实现SQL #

WITH split_winners AS (
    -- 拆分所有获胜用户ID，统计胜场数
    SELECT 
        SUBSTRING_INDEX(SUBSTRING_INDEX(g.winners, ',', n.n), ',', -1) AS user_id,
        COUNT(*) AS win_count
    FROM games g
    JOIN (
        -- 生成数字序列，覆盖单场最多获胜用户数（可按需调整数量）
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL 
        SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) n
    ON CHAR_LENGTH(g.winners) - CHAR_LENGTH(REPLACE(g.winners, ',', '')) >= n.n - 1
    GROUP BY user_id
),
split_loosers AS (
    -- 拆分所有失败用户ID，统计负场数
    SELECT 
        SUBSTRING_INDEX(SUBSTRING_INDEX(g.loosers, ',', n.n), ',', -1) AS user_id,
        COUNT(*) AS lose_count
    FROM games g
    JOIN (
        -- 同样生成数字序列，覆盖单场最多失败用户数
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL 
        SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) n
    ON CHAR_LENGTH(g.loosers) - CHAR_LENGTH(REPLACE(g.loosers, ',', '')) >= n.n - 1
    GROUP BY user_id
)
-- 关联胜负统计，计算胜率并取Top30
SELECT 
    COALESCE(w.user_id, l.user_id) AS user_id,
    -- 处理负场为0的情况（设为极大值，确保这类用户排在最前）
    CASE 
        WHEN l.lose_count = 0 THEN 999999.99
        ELSE ROUND(w.win_count / l.lose_count, 4) 
    END AS rating
FROM split_winners w
FULL JOIN split_loosers l ON w.user_id = l.user_id
-- 过滤掉既无胜场也无负场的用户
WHERE COALESCE(w.win_count, 0) + COALESCE(l.lose_count, 0) > 0
ORDER BY rating DESC
LIMIT 30;

关键优化说明 #

• 避免用户表遍历：直接从games表拆分数据，减少跨表关联的开销
• 数字序列表：用UNION生成的数字序列适配逗号分隔的ID拆分，无需额外创建辅助表
• FULL JOIN处理全量用户：覆盖只有胜场或只有负场的用户，避免遗漏
• 特殊情况处理：负场数为0时设置高胜率值，避免除法报错同时保证排名合理性

版本兼容提示 #

如果使用MySQL 5.x版本（不支持CTE），可以将拆分逻辑转换为子查询，或者创建临时表存储拆分后的胜负记录，再进行统计。

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