SQL 对于自动增量字段：MAX(ID) vs TOP 1 ID ORDER BY ID DESC
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/590079/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
For autoincrement fields: MAX(ID) vs TOP 1 ID ORDER BY ID DESC
提问by Binoj Antony
I want to find the highest AutoIncremented value from a field. (its not being fetched after an insert where I can use @@SCOPE_IDENTITYetc)
Which of these two queries would run faster or gives better performance.
Idis the primary key and autoincrementfield for Table1. And this is for Sql Server 2005.
我想从一个字段中找到最高的 AutoIncremented 值。（在我可以使用的插入之后，它不会被获取@@SCOPE_IDENTITY）这两个查询中的哪一个会运行得更快或提供更好的性能。
Id是 的主键和autoincrement字段Table1。这是针对 Sql Server 2005 的。
SELECT MAX(Id) FROM Table1
SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC
[Edit]
Yes in this case Idis the field on which I have defined the clustered index.
If the index is ID DESCthen what..
And yes it would be nice to know how the performance would be affected if
1. Id is a clustered index + primary key.
2. Id is a clustered index and not primary key.
3. Id is a non clustered index ASC + primary key.
4. Id is a non clustered index ASC and not primary key.
5. Id is a non clustered index DESC + primary key.
6. Id is a non clustered index DESC and not primary key.
7. Id is just AutoIncrement
[编辑]
在这种情况下Id是我定义聚集索引的字段。
如果索引是ID DESC什么..
是的，很高兴知道如果
1.Id 是聚集索引 + 主键，性能会受到怎样的影响。
2. Id 是聚集索引而不是主键。
3.Id为非聚集索引ASC+主键。
4. Id 是非聚集索引 ASC 而不是主键。
5.Id是非聚集索引DESC+主键。
6. Id 是非聚集索引 DESC 而不是主键。
7. id 只是AutoIncrement
Hope its not a tall order!
希望这不是一项艰巨的任务！
采纳答案by Quassnoi
In theory, they will use same plans and run almost same time.
理论上，他们将使用相同的计划并运行几乎相同的时间。
In practice,
在实践中，
SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC
will more probably use a PRIMARY KEY INDEX.
更有可能使用PRIMARY KEY INDEX.
Also, this one is more extendable if you will decide to select some else column along with id.
此外，如果您决定选择其他列和id.
An actual plan on MAX()says:
一个实际的计划MAX()说：
SELECT <- AGGREGATE <- TOP <- CLUSTERED INDEX SCAN
, while plan for TOP 1says:
，而计划TOP 1说：
SELECT <- TOP <- CLUSTERED INDEX SCAN
, i. e. aggregateis omitted.
，即aggregate省略。
Aggregate actually won't do anything here, as there is but one row.
Aggregate 实际上不会在这里做任何事情，因为只有一行。
P. S.As @Mehrdad Afshariand @John Sansomnoted, on a non-indexed field MAXis slightly faster (of course not 20times as optimizer says):
PS正如@Mehrdad Afshari并@John Sansom指出，在非索引字段MAX上稍快（当然不是20优化器所说的时间）：
-- 18,874,368 rows SET LANGUAGE ENGLISH SET STATISTICS TIME ON SET STATISTICS IO ON PRINT 'MAX' SELECT MAX(id) FROM master PRINT 'TOP 1' SELECT TOP 1 id FROM master ORDER BY id DESC PRINT 'MAX' SELECT MAX(id) FROM master PRINT 'TOP 1' SELECT TOP 1 id FROM master ORDER BY id DESC PRINT 'MAX' SELECT MAX(id) FROM master PRINT 'TOP 1' SELECT TOP 1 id FROM master ORDER BY id DESC PRINT 'MAX' SELECT MAX(id) FROM master PRINT 'TOP 1' SELECT TOP 1 id FROM master ORDER BY id DESC PRINT 'MAX' SELECT MAX(id) FROM master PRINT 'TOP 1' SELECT TOP 1 id FROM master ORDER BY id DESC Changed language setting to us_english. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. MAX SQL Server Execution Times: CPU time = 0 ms, elapsed time = 20 ms. (строк обработано: 1) Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 447, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 5452 ms, elapsed time = 2766 ms. TOP 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 6813 ms, elapsed time = 3449 ms. MAX SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 44, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 5359 ms, elapsed time = 2714 ms. TOP 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 6766 ms, elapsed time = 3379 ms. MAX SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 5406 ms, elapsed time = 2726 ms. TOP 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 6780 ms, elapsed time = 3415 ms. MAX SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 85, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 5392 ms, elapsed time = 2709 ms. TOP 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 10, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 6766 ms, elapsed time = 3387 ms. MAX SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 5374 ms, elapsed time = 2708 ms. TOP 1 SQL Server Execution Times: CPU time = 0 ms, elapsed time = 1 ms. (строк обработано: 1) Table 'master'. Scan count 3, logical reads 32655, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 6797 ms, elapsed time = 3494 ms.
回答by John Sansom
If there is a clustered index there is virtually no difference in performance between the two queries.
如果存在聚集索引，则两个查询之间的性能几乎没有区别。
This is becuase both will perform a Clustered Index Scan that will bear 100% of the query cost.
这是因为两者都将执行聚集索引扫描，该扫描将承担 100% 的查询成本。
Performing the two queries on a column that does not have an index results in 3 operators being used in both execution plans.
在没有索引的列上执行两个查询会导致在两个执行计划中使用 3 个运算符。
The Top clause uses the Sort operator and the Max function uses a Stream Aggregate operator.
Top 子句使用 Sort 运算符，Max 函数使用 Stream Aggregate 运算符。
When there is no index, the MAX() function provides better performance.
当没有索引时，MAX() 函数提供更好的性能。
Proof of concept can be found and full walkthrough of a test scenario can be found here
可以在此处找到概念证明和测试场景的完整演练
回答by Mike DeFehr
Nobody mentioned IDENT_CURRENT('Table1') - blows them all away - of course it only works on identity columns, but that wasthe question...
没有人提到IDENT_CURRENT（“表1”） -打击他们全都冲去-当然这仅适用于标识列，但是这是问题...
回答by Anton Gogolev
Just compare execution plans and you'll see (press Ctrl+Min Management Studio when editing a query). My wild guess will be that these queries are equally performant provided there is a (clustered) index on Idcolumn.
只需比较执行计划，您就会看到（Ctrl+M编辑查询时在 Management Studio 中按下）。我的猜测是，如果列上有（聚集）索引，这些查询的性能是一样的Id。
However, this as a whole is a very badidea.
然而，这作为一个整体是一个非常糟糕的主意。
回答by Mehrdad Afshari
MAXis generally faster.
MAX一般更快。
Both queries will use the index on the column if exists.
如果存在，两个查询都将使用列上的索引。
If no index exists on the column, the TOP 1query will use a Top N Sortoperator to sortthe table instead of stream aggregation, which makes it slower.
如果列上不存在索引，则TOP 1查询将使用Top N Sort运算符对表进行排序，而不是使用流聚合，这会使其速度变慢。
MAXalso provides better readability.
MAX还提供了更好的可读性。
Side Note: while MAXwill use a stream aggregate operator in the execution plan in the indexed case, it doesn't have any specific cost as it's just processing a single row (Actual Rows = 1). You can compare queries by running them in a single batch and see the relative cost. In the indexed case, both queries will cost 50%. I tested the non-indexed case on a table with about 7000 rows and TOP will cost 65% in comparison to MAX that costs 35%.
旁注：虽然MAX在索引情况下将在执行计划中使用流聚合运算符，但它没有任何特定成本，因为它只是处理单行 ( Actual Rows = 1)。您可以通过在单个批处理中运行查询来比较查询并查看相对成本。在索引情况下，两个查询都将花费 50%。我在一个大约有 7000 行的表上测试了非索引案例，与 MAX 成本 35% 相比，TOP 将花费 65%。
回答by GateKiller
I've just tested the two SQL statements you provided against a typical dataset:
我刚刚针对典型数据集测试了您提供的两个 SQL 语句：
SELECT MAX(Id) FROM Table1
SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC
And SELECT TOP 1 Id FROM Table1 ORDER BY Id DESCis marginally faster because it has one last step in the execution plan. Here are the execution plans each query carries out:
并且SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC稍微快一点，因为它在执行计划中有最后一步。以下是每个查询执行的执行计划：
SELECT MAX(Id) FROM Table1
从表 1 中选择 MAX(Id)
Clustered Index Scan >> Top >> Stream Aggregate >> Select
聚集索引扫描>>顶部>>流聚合>>选择
SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC
SELECT TOP 1 Id FROM Table1 ORDER BY Id DESC
Clustered Index Scan >> Top >> Select
聚集索引扫描>>顶部>>选择
回答by Amy B
Yes in this case Id is the field on which I have defined the clustered index. If the index is ID DESC then what.. And yes it would be nice to know how the performance would be affected if
- Id is a clustered index + primary key.
- Id is a clustered index and not primary key.
- Id is a non clustered index ASC + primary key.
- Id is a non clustered index ASC and not primary key.
- Id is a non clustered index DESC + primary key.
- Id is a non clustered index DESC and not primary key.
- Id is just AutoIncrement
是的，在这种情况下 Id 是我在其上定义聚集索引的字段。如果索引是 ID DESC 那么什么.. 是的，很高兴知道如果
- Id 是聚集索引 + 主键。
- Id 是聚集索引而不是主键。
- Id 是非聚集索引 ASC + 主键。
- Id 是非聚集索引 ASC 而不是主键。
- Id 是非聚集索引 DESC + 主键。
- Id 是非聚集索引 DESC 而不是主键。
- Id 只是 AutoIncrement
For Cases 1 and 2, both will perform a clustered index scan that returns a single record. There is no IO difference between the two queries.
对于案例 1 和案例 2，两者都将执行返回单个记录的聚集索引扫描。两个查询之间没有 IO 差异。
For Cases 3, 4, 5 and 6, both will perform an index scan that returns a single record. There is no IO difference between the two queries.
对于案例 3、4、5 和 6，两者都将执行返回单个记录的索引扫描。两个查询之间没有 IO 差异。
For Case 7, both will perform a table scan. There is no difference in the IO cost.
对于案例 7，两者都将执行表扫描。IO 成本没有区别。
Summary: Case 1-6 are made of win! If you're in Case 7, then you've already lost from an IO standpoint.
总结：案例1-6都是赢了！如果您在案例 7 中，那么从 IO 的角度来看，您已经失败了。
You can measure IO by using SQL's Query analyzer. Run this before your query.
您可以使用 SQL 的查询分析器来衡量 IO。在您的查询之前运行它。
SET STATISTICS IO ON
