查询各用户交易类型最大计数及SQL慢查询优化求助

需要查询每个用户各交易类型的最大交易计数（即每个用户交易次数最多的交易类型及其对应次数）。

原始数据表 #

期望输出结果 #

当前使用的SQL语句 #

SELECT 
    DISTINCT(t.user_name), t.discom, c.*
FROM transactions AS t
LEFT JOIN (
    SELECT MAX(id) AS id, user_id, COUNT(id) AS `count` FROM transactions
    GROUP BY user_name, discom
) AS c ON c.user_id = t.user_id
GROUP BY t.user_name
ORDER BY c.count DESC

该语句仅处理3500条用户数据就耗时数分钟，需优化查询效率。

当前SQL存在逻辑冗余、分组字段不匹配等问题，导致查询效率低下，以下是针对性优化方案：

方案一：使用窗口函数（MySQL 8.0+ 适用） #

利用ROW_NUMBER()窗口函数直接对每个用户的交易类型计数排序，筛选出排名第一的记录，逻辑清晰且性能高效：

SELECT user_id, type, count
FROM (
    SELECT 
        user_id,
        type,
        COUNT(*) AS count,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY COUNT(*) DESC) AS rn
    FROM transactions
    GROUP BY user_id, type
) AS ranked
WHERE rn = 1;

方案二：关联子查询（低版本MySQL 适用） #

若不支持窗口函数，可通过关联子查询筛选每个用户的最大交易次数：

SELECT t1.user_id, t1.type, COUNT(*) AS count
FROM transactions t1
GROUP BY t1.user_id, t1.type
HAVING COUNT(*) = (
    SELECT MAX(cnt)
    FROM (
        SELECT COUNT(*) AS cnt
        FROM transactions t2
        WHERE t2.user_id = t1.user_id
        GROUP BY t2.type
    ) AS sub
);

额外性能提升建议 #

• 给transactions表创建复合索引：(user_id, type)，该索引能直接覆盖分组和计数操作，大幅减少数据扫描量
• 移除不必要的DISTINCT和冗余关联逻辑，避免无意义的数据处理

内容的提问来源于stack exchange，提问作者Mr.Singh