请问你们中是否有人使用 Count(1)
而不是 Count(*)
,是否会在性能上有明显差异?还是这只是一个从过去流传下来的遗留习惯?
具体的数据库是 SQL Server 2005
。
请问你们中是否有人使用 Count(1)
而不是 Count(*)
,是否会在性能上有明显差异?还是这只是一个从过去流传下来的遗留习惯?
具体的数据库是 SQL Server 2005
。
没有区别。
原因:
在线书籍说“
COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )
”
“1”是非空表达式:所以它与COUNT(*)
相同。
优化器将其识别为微不足道的东西。
与EXISTS (SELECT * ...
或EXISTS (SELECT 1 ...
相同
示例:
SELECT COUNT(1) FROM dbo.tab800krows
SELECT COUNT(1),FKID FROM dbo.tab800krows GROUP BY FKID
SELECT COUNT(*) FROM dbo.tab800krows
SELECT COUNT(*),FKID FROM dbo.tab800krows GROUP BY FKID
同样的IO,同样的计划,一切都有效
编辑,2011年8月
编辑,2011年12月
COUNT(*)
在ANSI-92中特别提到(查找“标量表达式125
”)
情况:
a)如果指定了COUNT(*),则结果是T的基数。
也就是说,ANSI标准认为你的意思是显而易见的。由于这种迷信,RDBMS供应商已经将COUNT(1)
优化掉了。否则,它将按照ANSI进行评估
b)否则,让TX成为单列表,该表是将应用于T的每一行并消除空值的结果。如果消除了一个或多个空值,则会引发完成条件:警告-
我在SQL Server团队工作,希望能在这个帖子中澄清一些问题(我之前没有看到过,所以很抱歉工程团队之前没有这样做)。
select count(1) from table
与select count(*) from table
之间没有语义上的区别,也没有明显的性能差异。
首先,select count(1) from table
与select count(*) from table
之间没有语义上的区别。它们在所有情况下返回相同的结果(如果不是这样,则是一个错误)。正如其他答案中所指出的,select count(column) from table
在语义上有所不同,并且并不总是返回与count(*)
相同的结果。
这些情况发生的可能性极小,但在当前优化器的架构中有潜在的可能性。
SQL Server的优化器作为一个搜索程序(类比于计算机程序下棋,搜索各种不同查询部分的替代方案并计算出最便宜的计划),该搜索有一些限制,以保持查询编译在合理的时间内完成。对于超过最简单的查询,搜索有几个阶段,并且根据优化器认为查询的成本如何来处理查询的交易。
有三个主要的搜索阶段,每个阶段都可以运行更具攻击性(昂贵)的启发式算法,尝试找到比先前任何解决方案更便宜的计划。最终,在每个阶段结束时,会有一个决策过程,试图确定是否应该返回迄今为止找到的计划,还是应该继续搜索。
此过程使用迄今为止所花费的总时间与迄今为止找到的最佳计划的估计成本进行比较。因此,在不同速度的CPU的不同机器上,由于在早期阶段计划超时而导致计划与进入下一个搜索阶段不同,这是可能的(虽然很少见)。
还有一些与最后阶段超时和非常消耗机器全部内存的昂贵查询相关的相似场景(在64位上通常不是问题,但在32位服务器上这是一个更大的问题)。
最终,如果您得到了一个不同的计划,则运行时性能将有所不同。我认为编译时间的差异永远不可能导致发生这些情况。
请使用您想要的任何一种,因为这些都不会以任何实际形式影响表现。(诚实地说,在 SQL 中影响性能的因素远远超出了这个主题。)
希望这能有所帮助。我确实写过一章关于优化器是如何工作的书籍,但我不知道在这里张贴是否合适(因为我仍然相信会从中获得微薄的版税)。因此,我会发布一个链接,让您可以查看我在英国 SQLBits 上关于优化器高级搜索各个主要阶段的演讲,以便您更详细地了解它们。
这是相关视频链接: 深入了解 SQL Server 查询优化器1
也经历了同样的扩展。我基于这个性能测试 https://dev59.com/VXI-5IYBdhLWcg3w9tdw#6140367,并且在那个答案中还有一个查询示例,当列级别权限处于活动状态时,使用`1`的查询会出现意外错误。 - Martin SmithSYS_GUID()
是一个计算密集型函数。在我的测试数据库中,t_even
是一个有 1,000,000
行的表。这个查询:SELECT COUNT(SYS_GUID())
FROM t_even
这段代码运行了48
秒,因为函数需要评估每个返回的SYS_GUID()
以确保它不是NULL
。
然而,这个查询:
SELECT COUNT(*)
FROM (
SELECT SYS_GUID()
FROM t_even
)
这段代码运行了2
秒,因为它甚至没有尝试评估SYS_GUID()
(尽管*
是COUNT(*)
的参数)。
COUNT(*)
如何依赖于 SYS_GUID
的值? - QuassnoiCOUNT(*)
来运行,它需要一个表格,因此子查询应该像一个表格一样工作。否则,我不认为 COUNT(*)
会返回一个有意义的值。 - asgsa && b
进行优化,如果 a
为 false,则不会去评估 b
。这个原则在这里同样适用。 - Quassnoimap
方法的作用,您是否看到这两个表达式:t_even.map(() => sys_guid()).length
和t_even.length
始终返回相同的值?Oracle的优化器足够聪明,可以看到并优化掉map
部分。 - Quassnoilength
不完全取决于集合包含的内容,而是取决于其元素数量。如果这个数字存储在集合的元数据中(这不适用于 Oracle 或大多数其他现代 RDBMS,但适用于旧版 MySQL 的存储引擎 MyISAM),那么 COUNT(*)
只需要从元数据中获取值即可。 - Quassnoi显然,COUNT(*)
和COUNT(1)
总是返回相同的结果。因此,如果其中一个比另一个慢,那么这实际上是由于优化器中存在错误。由于这两种形式在查询中使用非常频繁,因此数据库管理系统不允许这种错误存在而不予修复。因此,你会发现在所有主要的SQL数据库管理系统中,这两种形式的性能(可能)是相同的。
COUNT(*)
具体意思是“表达式的基数”(可以是基本表、视图、派生表、CTE等)。COUNT(*)
容易解析。如果使用任何其他表达式,则需要解析器确保它不引用任何列(例如a
是文字的情况下的 COUNT('a')
和a
是列的情况下的COUNT(a)
可能会产生不同的结果)。COUNT(*)
也可以很容易地被挑选出来,这是在与多个供应商的SQL提供内容一起工作时非常有用的技能。SELECT COUNT(*) FROM MyPersistedTable;
,认为DBMS很可能保存了表的基数统计信息。COUNT(1)
和COUNT(*)
在语义上是等效的,所以我使用COUNT(*)
。COUNT(*)
和COUNT(1)
在结果和性能方面是相同的。
我希望优化器可以确保在奇怪的边缘情况之外没有真正的差异。
就像任何事情一样,唯一真正的方法是测量您特定的情况。
话虽如此,我一直使用COUNT(*)
。
由于这个问题一再出现,我在这里提供一个答案。我希望能为初学者们提供一些关于“最佳实践”的建议。
SELECT COUNT(*) FROM something
统计记录数,这是一项容易的任务。
SELECT COUNT(1) FROM something
检索每条记录的1并统计非空的1的数量,本质上也是统计记录数,只不过更加复杂。
尽管如此:好的数据库管理系统会注意到第二个语句将导致与第一个语句相同的计数,并相应地重新解释它,以免做无必要的工作。因此通常两个语句都会产生相同的执行计划并花费相同的时间。
但是从可读性的角度来看,你应该使用第一个语句。你想要计算记录数,所以就计算记录数,而不是表达式。只有在想要计算某些非空出现次数时,才使用COUNT(expression)。
我在一台8GB内存的Hyper-V虚拟机上对SQL Server 2012进行了快速测试,您可以自行查看结果。在运行这些测试时,我未运行除SQL Server Management Studio之外的其他窗口应用程序。
我的表模式:
CREATE TABLE [dbo].[employee](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Employee
表中的记录总数:178090131(约1.78亿行)
第一个查询:
Set Statistics Time On
Go
Select Count(*) From Employee
Go
Set Statistics Time Off
Go
第一次查询的结果:
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 35 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 10766 ms, elapsed time = 70265 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
第二个查询:
Set Statistics Time On
Go
Select Count(1) From Employee
Go
Set Statistics Time Off
Go
第二个查询的结果:
SQL Server parse and compile time:
CPU time = 14 ms, elapsed time = 14 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 11031 ms, elapsed time = 70182 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SET STATISTICS TIME ON
select count(1) from MyTable (nolock) -- table containing 1 million records.
SQL Server 执行时间:
CPU 时间 = 31 毫秒,经过时间 = 36 毫秒。
select count(*) from MyTable (nolock) -- table containing 1 million records.
SQL Server 执行时间:
CPU 时间 = 46 毫秒,经过时间 = 37 毫秒。
我已经运行了这个程序数百次,每次都清除缓存...结果会因服务器负载的变化而有所不同,但几乎总是 count(*)
的 CPU 时间更高。
count(*)
和count(1)
的结果时间也相差不到几毫秒。 - Jeff Atwood
COUNT(*)
、COUNT(1)
和COUNT(pk)
之间的区别,哪种更好?,还有COUNT(*)
和COUNT(column-name)
之间的区别,哪个更准确?。可能会存在其他重复问题。 - Jonathan Leffler