SQL性能MAX()

Question

SQL性能MAX()

sqlsql-serverperformancemaxquery-optimization

19

刚有个小问题。在尝试获取表格的最大值时，哪种方法更好？

SELECT MAX(id) FROM myTable WHERE (whatever)

或者

SELECT TOP 1 id FROM myTable WHERE (whatever) ORDER BY id DESC

我正在使用Microsoft SQL Server 2012。

- Ole Albers

2

你测试过了吗？如果优化器足够好的话，我会期望它们是相同的。 - Hogan

1

如果 id 是自增的，那么这个问题是https://dev59.com/A3RB5IYBdhLWcg3wj32c的重复。 - Ben

id 只是代表任何类型的任何列。 - Ole Albers

最终，这确实归结于索引而不是类型。 - Lucero

如果使用MAX(id) OVER (PARTITION BY col_name)，而不是TOP 1或仅MAX(id)，效率会更高吗？ - Sanushi Salgado

4个回答

6

第一个显然更清晰地表达了意图。

对于这个具体的查询，不应该有明显的性能差异（即使在myTable中没有行时结果是不同的，它们实际上应该几乎相同）。除非你有充分的理由来调整查询（例如已经证明存在性能问题），否则总是选择显示代码意图的那个。

- Lucero

3

另一个优点是第一个查询也不依赖于特定的数据库管理系统(DBMS)，这意味着您可以将相同的查询应用到几乎任何其他DBMS上，它仍然能够工作，而第二个查询则使用了SQL-Server特有的“TOP”关键字，只有SQL-Server支持。 - Zane Bien

4

所有值得信赖的查询优化器都应该生成性能相同的查询计划：如果正在优化的列上有索引，则两个查询都应使用它；如果没有索引，则两个查询都会产生全表扫描。

- Sergey Kalinichenko

0

虽然我怀疑计划中的TOP 1排序操作符成本过高。我尝试了TOP 1、TOP 100和TOP 101，所有这些都给出了相同的子树估算成本，尽管最后一个需要对所有行进行排序。- Martin Smith Jul 2 at 6:53

在这个例子中，无论您需要1行还是100行，优化器都必须执行相同数量的工作，即从表（聚集索引扫描）中读取所有行。然后对所有这些行进行排序（排序操作），因为列C上没有索引。最后只显示所需的内容。

SELECT TOP (1) b FROM dbo.x ORDER BY b DESC
option(recompile); 
SELECT TOP (100) b FROM dbo.x ORDER BY b DESC
option(recompile);

尝试上述代码，这里的top 1和top 100显示的成本不同，因为列b上有一个索引。因此，在这种情况下，您不需要读取所有行并对其进行排序，而是需要转到最后一页指针。对于一行数据，读取索引的最后一页上的最后一行。对于100行数据，找到最后一页上的最后一行，然后开始向后扫描，直到获取到这100行数据。

- Gulli Meel

这不正确。请阅读我提供的链接，了解“TOP N”排序如何工作。100是不同方法之间的魔数，但在计划成本中似乎没有考虑到这一点，当针对Aaron的演示数据运行时，对于“TOP 1”与“TOP 50000”，成本是相同的。对于“TOP 1”，它基本上只需要跟踪最大值，这与“MAX”需要做的相同，尽管实现方式不同。它不需要对所有50,000行进行排序。 - Martin Smith

1

通过扫描所有行并将每一行与迄今为止看到的“TOP 1”值进行比较，无需对完整的50,000行数据集进行排序。 - Martin Smith

如果列表没有排序，且在列C上没有索引，那么如何知道最大值呢？如Paul的例子所示，成本和计划将是相同的。唯一的问题是，在前101个查询时会溢出到tempdb，这会减慢前101个查询的速度，但成本仍然保持不变。他提到这将发生在他的数据样本中，其中有一个char（1000）的列，但在Aaron的情况下，他没有与Paul相同的数据样本。我将查看Paul的博客，并测试他的数据和Aaron的数据，并告诉您详细信息。 - Gulli Meel

1

没有必要将第2到50,000个位置的所有行排序。我们不关心那些。TOP 1 不会这样做，但 TOP 101 会。这在我链接的帖子中很清楚。然而，尽管 TOP 1 做的工作明显较少，但估计的子树成本并没有显示出任何成本差异，这是我的原始陈述。 - Martin Smith

2

成本将不会相同。扫描所有行并记住迄今为止最高的行具有O(n)复杂度。对所有行进行排序具有O(n log n)复杂度。 - Martin Smith

显示剩余7条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Aaron Bertrand · Accepted Answer

你可以通过检查执行计划来确定，这两种方法没有区别。如果id是聚集索引，你会看到有序的聚集索引扫描；如果没有索引，你仍然会看到表扫描或聚集索引扫描，但无论哪种情况都不会有顺序。

如果你想从行中提取其他值，TOP 1的方法可能很有用，这比在子查询中提取最大值然后连接更容易。如果您需要从行中获得其他值，则需要在两种情况下确定如何处理并列的情况。

尽管如此，在某些情况下，计划可能会有所不同，因此根据列是否具有索引以及是否单调递增而进行测试非常重要。我创建了一个简单的表，并插入了50000行：

CREATE TABLE dbo.x
(
  a INT, b INT, c INT, d INT, 
  e DATETIME, f DATETIME, g DATETIME, h DATETIME
);
CREATE UNIQUE CLUSTERED INDEX a ON dbo.x(a);
CREATE INDEX b ON dbo.x(b)
CREATE INDEX e ON dbo.x(e);
CREATE INDEX f ON dbo.x(f);

INSERT dbo.x(a, b, c, d, e, f, g, h)
SELECT 
  n.rn, -- ints monotonically increasing
  n.a,  -- ints in random order
  n.rn, 
  n.a, 
  DATEADD(DAY, n.rn/100, '20100101'), -- dates monotonically increasing
  DATEADD(DAY, -n.a % 1000, '20120101'),     -- dates in random order
  DATEADD(DAY, n.rn/100, '20100101'),
  DATEADD(DAY, -n.a % 1000, '20120101')
FROM
(
  SELECT TOP (50000) 
     (ABS(s1.[object_id]) % 10000) + 1, 
     rn = ROW_NUMBER() OVER (ORDER BY s2.[object_id])
  FROM sys.all_objects AS s1 
  CROSS JOIN sys.all_objects AS s2
) AS n(a,rn);
GO

在我的系统上，这将创建从1到50000的a/c值，3到9994之间的b/d值，以及从2010-01-01到2011-05-16的e/g值和从2009-04-28到2012-01-01的f/h值。

首先，让我们比较索引的单调递增整数列a和c。a具有聚集索引，而c没有：

SELECT MAX(a) FROM dbo.x;
SELECT TOP (1) a FROM dbo.x ORDER BY a DESC;

SELECT MAX(c) FROM dbo.x;
SELECT TOP (1) c FROM dbo.x ORDER BY c DESC;

结果：

enter image description here

第4个查询的主要问题是，与MAX不同，它需要排序。这里是3和4的比较:

enter image description here

所有这些查询变体都会遇到一个常见问题：对未索引列的MAX将能够利用聚集索引扫描并执行流式聚合，而TOP 1需要执行更昂贵的排序操作。

我进行了测试，并在测试b + d、e + g和f + h时看到了完全相同的结果。

因此，在我看来，除了产生更多符合标准的代码外，根据底层表格和索引（在将代码投入生产后可能会发生变化），使用MAX而不是TOP 1可能会带来潜在的性能优势。因此，如果没有其他信息，我会说MAX更可取。

（正如我之前所说的，如果你拉取额外的列，TOP 1可能真的是你想要的行为。如果你真的需要这个结果，你还需要测试MAX+JOIN方法。）