MySQL中获取各团队最低积分对应ID及查询优化需求

从teams表中获取每个team（外键字段）对应最低points（已建立索引）的记录id。现有查询可实现功能，但需确认其在大数据量表中的性能，同时要求对查询进行简化与优化。

原查询语句 #

SELECT T.id from teams as T
INNER JOIN (
    SELECT MIN(T1.points) AS P FROM teams AS T1
    GROUP BY T1.team LIMIT 5
) TJOIN ON T.points IN (TJOIN.P)
GROUP BY T.team
ORDER BY T.points ASC LIMIT 5

teams表结构及数据 #

预期结果 #

原查询的性能问题分析 #

1. 逻辑冗余：子查询先按team分组取最小points，主查询又通过points IN (TJOIN.P)关联后再次按team分组，当不同team的最小points值相同时，会匹配到无关记录，额外增加分组计算开销。
2. 索引利用不足：仅依赖points单字段索引，分组和关联过程中无法直接通过索引定位到每个team最小points对应的id，大数据量下会产生大量回表和数据扫描。
3. LIMIT位置不合理：子查询的LIMIT 5无实际意义，分组后每个team仅返回一条最小points记录；主查询LIMIT 5才控制最终结果数，但原逻辑会先处理全量数据再限制，效率低下。

优化后的查询方案 #

方案1：窗口函数（推荐，逻辑清晰高效）

SELECT id
FROM (
    SELECT 
        id,
        team,
        ROW_NUMBER() OVER (PARTITION BY team ORDER BY points ASC) AS rn
    FROM teams
) t
WHERE rn = 1
ORDER BY points ASC
LIMIT 5;

• 核心逻辑：按team分组，对每组内的points升序排序，取每组第一条（即points最小的记录）。
• 索引优化：建立联合索引(team, points, id)，让分组、排序直接在索引上完成，无需回表扫描原表，性能提升显著。

方案2：关联子查询（适配不支持窗口函数的老版本数据库）

SELECT t.id
FROM teams t
INNER JOIN (
    SELECT team, MIN(points) AS min_points
    FROM teams
    GROUP BY team
) t_min ON t.team = t_min.team AND t.points = t_min.min_points
ORDER BY t.points ASC
LIMIT 5;

• 核心逻辑：先按team分组取最小points，再通过team和points精准关联到对应id，避免了原查询中points IN的多值匹配问题。
• 索引优化：同样建议建立(team, points, id)联合索引，让子查询分组取最小值、主查询关联都能走索引。

性能对比 #

• 原查询：大数据量下会产生全表/索引扫描+两次分组计算，性能随数据量增长急剧下降。
• 优化后查询：利用联合索引时，仅需扫描索引即可完成分组、排序和匹配，时间复杂度接近O(n)，远优于原查询的O(n log n)。

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