MySQL中基于非主键分组获取最新记录的SQL优化咨询

问题场景 #

需要从Metrics表中，基于非主键列BuildId分组，获取最新的2个Build对应的所有记录。表结构如下：

TestID BuildId TestResult  TestMetrics
a123     b345     Pass       metric1
a234     b234     Fail       metric2
a345     b345     Fail       metric3
a456     b123     Fail       metric4
a567     b234     Pass       metric5

预期结果为最新2个Build的所有记录（按BuildId降序排列）：

TestID BuildId TestResult  TestMetrics
a123     b345     Pass       metric1
a345     b345     Fail       metric3
a234     b234     Fail       metric2
a567     b234     Pass       metric5

原SQL因表数据量庞大效率极低，现提供更高效的实现方案。

原SQL的问题分析 #

原SQL语句：

select * from Metrics as m inner join 
   (select n.BuildId from Metrics as n group by n.BuildId order by n.BuildId desc limit 2)
on m.BuildId = n.BuildId
order by BuildId desc

核心问题：

• 子查询用GROUP BY n.BuildId去重，无索引时会触发全表扫描，还需创建临时表处理分组，大表下性能极差
• 未利用索引加速排序和过滤操作

高效优化方案 #

1. 先创建关键索引

如果BuildId上无索引，优先创建单列索引：

CREATE INDEX idx_metrics_buildid ON Metrics(BuildId);

若需基于开发分支添加过滤条件（比如存在Branch列），创建联合索引更高效：

CREATE INDEX idx_metrics_branch_buildid ON Metrics(Branch, BuildId);

索引是大表查询性能提升的核心，能让排序、去重、过滤操作直接走索引，避免全表扫描。

2. 优化后的SQL语句

SELECT m.*
FROM Metrics m
INNER JOIN (
    -- 用DISTINCT替代GROUP BY，结合索引快速去重并排序
    SELECT DISTINCT BuildId
    FROM Metrics
    -- 有分支过滤需求时添加：WHERE Branch = 'your_dev_branch'
    ORDER BY BuildId DESC
    LIMIT 2
) n ON m.BuildId = n.BuildId
ORDER BY m.BuildId DESC;

3. 备选方案（MySQL 8.0+）：使用窗口函数

若你的MySQL版本为8.0及以上，可采用窗口函数实现，逻辑更直观：

SELECT TestID, BuildId, TestResult, TestMetrics
FROM (
    SELECT *,
           -- 按BuildId降序排名，相同BuildId归为同一排名
           DENSE_RANK() OVER(ORDER BY BuildId DESC) AS build_rank
    FROM Metrics
    -- 分支过滤条件：WHERE Branch = 'your_dev_branch'
) t
WHERE build_rank <= 2
ORDER BY BuildId DESC;

注意：窗口函数方案在数据量极大时，若无合适索引，性能可能不如JOIN方案，优先推荐带索引的JOIN方案。

方案优势 #

• 索引让子查询的去重和排序操作无需扫描全表，速度大幅提升
• 子查询仅返回2个BuildId，主查询通过BuildId索引快速匹配所有对应记录
• 联合索引能先过滤指定分支的数据，进一步缩小查询范围，适配多分支场景

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