首页 / 知识

关于sql：“选择组中的最大值”的最佳性能查询？

2023-04-17 08:21:00

Best-performance query for “select max in group”?

我有一个简单的表注释(id INT, revision INT, comment VARCHAR(140))，内容如下：

1
2
3
4
5

1|1|hallo1|
1|2|hallo2|
1|3|hallo3|
2|1|hallo1|
2|2|hallo2|

我正在搜索一条SQL语句，该语句将返回具有最高修订版本的每个注释：

1 2	1\|3\|hallo3\| 2\|2\|hallo2\|

我想出了以下解决方案：

1
2
3
4
5
6
7

SELECT id, revision, comment
FROM comments
WHERE revision = (
SELECT MAX(revision)
FROM comments AS f
WHERE f.id = comments.id
);

但是对于大型数据集，这非常慢。是否有更好的查询来完成此任务？

这是一种使用适当索引不会令人费解的速度并且不使用子选择的方法：

1
2
3
4
5

SELECT comments.ID, comments.revision, comments.comment FROM comments
LEFT OUTER JOIN comments AS maxcomments
ON maxcomments.ID= comments.ID
AND maxcomments.revision > comments.revision
WHERE maxcomments.revision IS NULL

从此处的查询改编而成：
http://www.xaprb.com/blog/2007/03/14/how-to-find-the-max-row-per-group-in-sql-without-subqueries/

(从谷歌搜索：通过SQL最大组)

确保您已正确设置索引。索引ID，修订会很好。

这是您查询的另一种说法。尚未检查其执行计划，但是如果您很好地设置了索引，它应该会有所帮助：

1
2
3
4
5
6
7
8

SELECT c.*
FROM comments c
INNER JOIN (
SELECT id,MAX(revision) AS maxrev
FROM comments
GROUP BY id
) b
ON c.id=b.id AND c.revision=b.maxrev

编辑添加：

如果您使用的是SQL Server，则可能还需要签出索引视图：
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

再次编辑以添加信息：

1
2
3
4
5
6
7
8
9

Subquery:
25157 records
2 seconds
Execution plan includes an INDEX Seek (82%) base AND a Segment (17%)

LEFT OUTER JOIN:
25160 records
3 seconds
Execution plan includes two INDEX Scans @ 22% each WITH a RIGHT OUTER MERGE at 45% AND a FILTER at 11%

我仍然会使用子查询。

使用我们的其中一个表进行了测试，该表总共有近一百万行。在两个字段FIELD2和FIELD3上都存在索引。在不到3秒的时间内，查询在我们的开发者框中返回了83953行。

1
2
3
4
5
6
7
8
9
10
11

SELECT
FIELD1, FIELD2, FIELD3
FROM
OURTABLE (nolock) T1
WHERE FIELD3 =
(
SELECT MAX(FIELD3) FROM
OURTABLE T2 (nolock)
WHERE T1.FIELD2=T2.FIELD2
)
ORDER BY FIELD2 DESC

分析将是我的建议。

1
2
3
4

SELECT id, max_revision, comment
FROM (SELECT c.id, c.comment, c.revision, MAX(c.revision)OVER(partition BY c.id) AS max_revision
FROM comments c)
WHERE revision = max_revision;

没有子选择(或临时表)：

1
2
3
4
5
6

SELECT c1.ID, c1.revision, c1.comment
FROM comments AS c1
LEFT JOIN comments AS c2
ON c1.ID = c2.ID
AND c1.revision < c2.revision
WHERE c2.revision IS NULL

对于大型表，我发现此解决方案可以具有更好的性能：

1
2
3
4
5
6
7
8
9
10

SELECT c1.id,
c1.revision,
c1.comment
FROM comments c1
INNER JOIN ( SELECT id,
MAX(revision) AS max_revision
FROM comments
GROUP BY id ) c2
ON c1.id = c2.id
AND c1.revision = c2.max_revision

一种非常干净的方式来执行"最新的按ID排序x"类型查询。正确建立索引也应该很容易。

1
2
3
4
5
6
7
8

SELECT id, revision, comment
FROM comments
WHERE (id, revision) IN (
SELECT id, MAX(revision)
FROM comments
-- WHERE clause comes here if needed
GROUP BY id
)

从左字段开始的想法，但是如何在表中添加一个额外的字段：