是否为“ SELECT COUNT（列）”？比“ SELECT COUNT（*）”更快/更慢？ [英] Is "SELECT COUNT(column)" faster/slower than "SELECT COUNT(*)"?

问题描述

我正在MSSQL2008中运行这样的查询：

I'm running a query like this in MSSQL2008:

select count(*)
from t1
inner join t2 on t1.id = t2.t1_id
inner join t3 on t1.id = t3.t1_id 

假设 t1.id 有 NOT NULL 约束。由于它们是内部联接，因此 t1.id 永远不能为空，因此使用 count（t1.id）代替 count（*）应该产生完全相同的最终结果。我的问题是：效果会一样吗？

Assume t1.id has a NOT NULL constraint. Since they're inner joins and t1.id can never be null, using count(t1.id) instead of count(*) should produce the exact same end result. My question is: Would the performance be the same?

我还想知道联接是否会影响这一点。我意识到添加或删除联接将影响性能和结果集的长度。假设在不更改联接模式的​​情况下，将 count 设置为仅定位到一个表。有什么不同吗？换句话说，这两个查询之间有区别：

I'm also wondering whether the joins could affect this. I realize that adding or removing a join will affect both performance and the length of the result set. Suppose that without changing the join pattern, you set count to target only one table. Would it make any difference? In other words, is there a difference between these two queries:

select count(*)    from t1 inner join t2 on t1.id = t2.t1_id 
select count(t1.*) from t1 inner join t2 on t1.id = t2.t1_id 

在MySQL中的COUNT（id）与COUNT（*）为MySQL回答了这个问题，但我找不到针对MS-SQL的答案，而且根本找不到将 join 因素考虑在内的任何内容。

COUNT(id) vs. COUNT(*) in MySQL answers this question for MySQL, but I couldn't find answers for MS-SQL specifically, and I can't find anything at all that takes the join factor into account.

注意：：我试图在Google和SO上都找到此信息，但是很难弄清楚如何在搜索结果中加上文字。

NOTE: I tried to find this information on both Google and SO, but it was difficult to figure out how to word my search.

推荐答案

我尝试了几个 SELECT COUNT（*）from MyTable vs 。从MyTable中选择COUNT（SomeColumn）具有各种大小的表，其中 SomeColumn 曾经是集群键列，一旦它在非聚集索引中，

I tried a few SELECT COUNT(*) FROM MyTable vs. SELECT COUNT(SomeColumn) FROM MyTable with various sizes of tables, and where the SomeColumn once is a clustering key column, once it's in a non-clustered index, and once it's in no index at all.

在所有情况下，无论表大小如何（从30万行到1.7亿行），我都从未见过任何差异（无论是速度还是执行计划）-在所有情况下， COUNT 是通过执行聚集索引扫描->即扫描整个表，基本上。如果涉及到非聚集索引，则将在该索引上进行扫描-即使执行 SELECT COUNT（*）！

In all cases, with all sizes of tables (from 300'000 rows to 170 million rows), I never see any difference in terms of either speed nor execution plan - in all cases, the COUNT is handled by doing a clustered index scan --> i.e. scanning the whole table, basically. If there is a non-clustered index involved, then the scan is on that index - even when doing a SELECT COUNT(*)!

在速度或计数方式上似乎没有任何区别-要计算全部，SQL Server只需要扫描整个表即可-期间。

There doesn't seem to be any difference in terms of speed or approach how those things are counted - to count them all, SQL Server just needs to scan the whole table - period.

在SQL Server 2008 R2开发人员版上进行了测试

Tests were done on SQL Server 2008 R2 Developer Edition

这篇关于是否为“ SELECT COUNT（列）”？比“ SELECT COUNT（*）”更快/更慢？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！