sql server中count(*),count(col),count(1)的区别

前言
记得很早以前就有人跟我说过，在使用count的时候要用count(1)而不要用count(*)，因为使用count(*)的时候会对所有的列进行扫描，相比而言count(1)不用扫描所有列，所以count(1)要快一些。当时是对这一结论深信不疑，虽然不知道为什么。今天正好有时间研究研究看count(*)和count(1)到底有没有性能差异。
我的测试环境是SQL Server 2005 SP2开发版。
在进行测试之前先建立一些测试的数据，代码如下：
create table test(a int, b varchar(100))
go

declare @n int
set @n = 1
while @n < 100000
begin
if @n%3 = 0
insert into test values (@n, null)
if @n%3 = 1
insert into test values (@n, str(@n))
if @n%3 = 2
insert into test values (@n, ‘this is text’)
set @n = @n+1
end
这里先说明一下，为了测试的目的，test表里面是故意没有加索引的。

count(*)与count(1)的对比
现在我们开始验证count(*)和count(1)的区别，验证方法很简单，如果两个语句执行效率不一样的话它们的查询计划肯定会不一样的，我们先执行set showplan_text on打开SQL执行计划显示，然后我们执行相应的SQL语句。
先是count(*)：
select count(*) from test

/*—————————————————————————
====== 下面是执行计划 ======
|–Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|–Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
|–Table Scan(OBJECT:([AdventureWorks].[dbo].[test]))
————————————————————————–*/
接着count(1)：
select count(1) from test

/*—————————————————————————
====== 下面是执行计划 ======
|–Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|–Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
|–Table Scan(OBJECT:([AdventureWorks].[dbo].[test]))
————————————————————————–*/
对比下两个执行计划我们可以发现是完全一样的，这也就说明count(*)和count(1)的执行效率是完全一样的，根本不存在所谓的单列扫描和多列扫描的问题。

count(col)与count(*)的对比
同样，我们先看一下两个不同count方式的执行计划。
count(*)的执行计划看上面的例子。
count(b)的执行计划：
select count(b) from test

/*—————————————————————————
====== 下面是执行计划 ======
|–Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|–Stream Aggregate(DEFINE:([Expr1005]=COUNT([AdventureWorks].[dbo].[test].)))
|–Table Scan(OBJECT:([AdventureWorks].[dbo].[test]))
————————————————————————–*/
现在能看到这两个执行计划唯一不同的地方就是COUNT的内容，对于count(*)是”|—Stream Aggregate(DEFINE:([Expr1005]=count(*)))”，对于count(b)是”|—Stream Aggregate(DEFINE:([Expr1005]=COUNT([AdventureWorks].[dbo].[test].)))”，那这两种count方式会不会有什么不一样呢？
让我们先看一下BOL里面对count(*)以及count(col)的说明：
COUNT(*) 返回组中的项数。包括 NULL 值和重复项。
COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。

expression
除 text、image 或 ntext 以外任何类型的表达式。不允许使用聚合函数和子查询。

*
指定应该计算所有行以返回表中行的总数。COUNT(*) 不需要任何参数，而且不能与 DISTINCT 一起使用。
COUNT(*) 不需要 expression 参数，因为根据定义，该函数不使用有关任何特定列的信息。
COUNT(*) 返回指定表中行数而不删除副本。它对各行分别计数。包括包含空值的行。
也就是说count(*)只是返回表中行数，因此SQL Server在处理count(*)的时候只需要找到属于表的数据块块头，然后计算一下行数就行了，而不用去读取里面数据列的数据。而对于count(col)就不一样了，为了去除col列中包含的NULL行，SQL Server必须读取该col的每一行的值，然后确认下是否为NULL，然后在进行计数。因此count(*)应该是比count(col)快的，下面我们来验证一下。
我们通过在同样的条件下将select count(…) from test执行1000次来看两种count方式是否是一样的：
先看count(*)
declare @n int, @a int
set @n = 1
while @n <= 1000
begin
select @a = count(*) from test
set @n = @n+1
end

/*——————————
执行结果：29s
—————————–*/
接着看count(col)
declare @n int, @a int
set @n = 1
while @n <= 1000
begin
select @a = count(b) from test
set @n = @n+1
end

/*——————————
执行结果：57s
—————————–*/
从执行结果可以看出相差还是很大的，count(*)比count(col)快了一倍。
不过因为count(*)和count(col)使用的目的是不一样的，在必须要使用count(col)的时候还是要用的，只是在统计表全部行数的时候count(*)就是最佳的选择了。
另外：这里用到的跑1000次的方法也可以用在比较count(*)和count(1)上，在这里你将得到两个一样的执行时间。

count(col)与count(distinct col)比较
同样，我们先对比一下两个执行计划。
select count(b) from test

/*—————————————————————————
====== 下面是执行计划 ======
|–Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|–Stream Aggregate(DEFINE:([Expr1005]=COUNT([AdventureWorks].[dbo].[test].)))
|–Table Scan(OBJECT:([AdventureWorks].[dbo].[test]))
————————————————————————–*/

select count(distinct b) from test

/*—————————————————————————
====== 下面是执行计划 ======
|–Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))
|–Stream Aggregate(DEFINE:([Expr1007]=COUNT([AdventureWorks].[dbo].[test].)))
|–Hash Match(Aggregate, HASH:([AdventureWorks].[dbo].[test].),
RESIDUAL:([AdventureWorks].[dbo].[test]. = [AdventureWorks].[dbo].[test].))
|–Table Scan(OBJECT:([AdventureWorks].[dbo].[test]))
————————————————————————–*/
从执行计划我们可以看到，因为表test没有索引，在执行count(distinct col)的时候是通过Hash Match的方式来查找相同值的行，这显然会耗费大量的CPU，同时我们也可以知道count(col)能比count(distinct col)快很多的。（如果test的列b有索引的话count(distinct col)的方式会不一样，走的是group by，但同样还是会比count(col)慢的，这个大家可以自己试一下）。
我们可以同样做一个执行1000次看花费的时间来做一个直观的对比。
declare @n int, @a int
set @n = 1
while @n <= 1000
begin
select @a = count(b) from test
set @n = @n+1
end

/*——————————
执行结果：57s
—————————–*/

declare @n int, @a int
set @n = 1
while @n <= 1000
begin
select @a = count(distinct b) from test
set @n = @n+1
end

/*——————————
执行结果：2min 36s
—————————–*/

索引与count的关系
我们上面讨论的都是表的索引结构不变的情况下count的变化，在表索引不变时对表做全表扫描所消耗的IO是不变的，不管是采取那种方式。现在在这里我们将看看不同类型的表索引对count会有什么样的变化，因为索引结构的改变对IO影响是最大的，在这里我们注重关注IO的变化情况。
先罗列一下我们要用到的SQL语句，包括查看IO，TIME、执行计划以及建立索引的。
– 打开IO显示
set statistics io on
– 打开执行时间显示
set statistics time on
– 打开执行计划显示
set showplan_text on

– 建立聚集索引pk_test
create clustered index pk_test on test (a)
– 建立非聚集索引ix_a
create index ix_a on test (a)
– 建立非聚集索引ix_b
create index ix_b on test (b)

堆表和聚集索引表上的count(*)
在这里我们先取得test没有建立索引之前执行count(*)的消耗，然后再在test上对a列建立一个聚集索引，然后再看看同样语句的执行计划和IO。
select count(*) from test

/*—————————————————————————
====== 对于堆表的执行计划 =====
|–Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|–Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
|–Table Scan(OBJECT:([AdventureWorks].[dbo].[test]))
====== 对于堆表的执行时间和IO =====
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。

(1 行受影响)
表 ‘test’。扫描计数 1，逻辑读取 302 次，物理读取 0 次，预读 0 次，
lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 31 毫秒，占用时间 = 33 毫秒。

—————————————————————————–
====== 对于聚集索引表的执行计划 =====
|–Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|–Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
|–Clustered Index Scan(OBJECT:([AdventureWorks].[dbo].[test].[pk_test]))
====== 对于聚集索引的执行时间和IO =====
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

(1 行受影响)
表 ‘test’。扫描计数 1，逻辑读取 304 次，物理读取 0 次，预读 0 次，
lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 31 毫秒，占用时间 = 34 毫秒。
—————————————————————————*/
从实际测试我们可以看到，堆表和聚集索引表上的count是没有什么区别的，甚至于聚集索引表上的IO还要多2（这是因为多了两个聚集索引的数据块造成的）。如果你对聚集索引的结构很了解的话也是不难解释的：其实聚集索引并没有单独的保留所有索引列的信息，而只是将表中的行的物理顺序按照聚集索引列的顺序整理了一下，因此对聚集索引的扫描和对堆表的扫描是一样的，没有什么本质上的区别。
因此聚集索引对于count来说是没有帮助的。

非聚集索引上的count
现在我们执行前面给出的语句为test表增加一个非聚集索引ix_a然后看看执行计划和IO情况。
select count(*) from test

/*—————————————————————————
====== 对于非聚集索引表的执行计划 =====
|–Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|–Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
|–Index Scan(OBJECT:([AdventureWorks].[dbo].[test].[ix_a]))
====== 对于非聚集索引表的执行时间和IO =====
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

表 ‘test’。扫描计数 1，逻辑读取 126 次，物理读取 0 次，预读 0 次，
lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 31 毫秒，占用时间 = 32 毫秒。
—————————————————————————*/
从执行结果可以看到，逻辑读的次数明显的减少了，因为计算行数这个操作对于全表扫描或是非聚集索引的扫描结果是一样的，而相对来说非聚集索引的数据量是肯定会比表的数据量小很多的，同样的做一次全部扫描所花费的IO也就要少很多了。
同样的对于一个count(col)的操作来说，对col的索引做count同样是能达到count(col)的目的的，相比全表扫描一样可以节省很多的IO操作。
select count(a) from test

/*—————————————————————————
====== 对于非聚集索引表的执行计划 =====
|–Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|–Stream Aggregate(DEFINE:([Expr1005]=COUNT([AdventureWorks].[dbo].[test].[a])))
|–Index Scan(OBJECT:([AdventureWorks].[dbo].[test].[ix_a]))
====== 对于非聚集索引表的执行时间和IO =====
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

表 ‘test’。扫描计数 1，逻辑读取 126 次，物理读取 0 次，预读 0 次，
lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
CPU 时间 = 46 毫秒，占用时间 = 49 毫秒。

那Count（*）具体如何执行？

    前面提到Count( )有不为NULL的值时，在SQL Server中只需要找出具体表中不为NULL的行数即可，也就是所有行（如果一行值全为NULL则该行相当于不存在）。那么最简单的执行办法是找一列NOT NULL的列，如果该列有索引，则使用该索引，当然，为了性能，SQL Server会选择最窄的索引以减少IO。

—————————————————————————*/

结论
这里把上面实验的结果总结一下：

count(*)和count(1)执行的效率是完全一样的。

count(*)的执行效率比count(col)高，因此可以用count(*)的时候就不要去用count(col)。

count(col)的执行效率比count(distinct col)高，不过这个结论的意义不大，这两种方法也是看需要去用。

如果是对特定的列做count的话建立这个列的非聚集索引能对count有很大的帮助。

如果经常count(*)的话则可以找一个最小的col建立非聚集索引以避免全表扫描而影响整体性能。

当然，在建立优化count的索引之前一定要考虑新建立的索引会不会对别的查询有影响，影响有多大，要充分考虑之后再决定是否要这个索引，这是很重要的一点，不要捡了芝麻丢了西瓜。