SQL Server中count(*), count(col), count(1)的对比

最新推荐文章于 2024-02-09 15:33:03 发布

昕玫

最新推荐文章于 2024-02-09 15:33:03 发布

阅读量1.2k

点赞数

分类专栏：数据库优化 sql 文章标签：数据库

数据库同时被 3 个专栏收录

14 篇文章 0 订阅

订阅专栏

优化

6 篇文章 0 订阅

订阅专栏

sql

4 篇文章 0 订阅

订阅专栏

链接：http://www.lansz.com/html/2008/06/sqlserver_howto_count.html

前言 count(*)与count(1)的对比 count(col)与count(*)的对比 count(col)与count(distinct col)比较索引与count的关系

堆表和聚集索引表上的count(*) 非聚集索引上的count

结论参考资料

top 前言

记得很早以前就有人跟我说过，在使用count的时候要用count(1)而不要用count(*)，因为使用count(*)的时候会对所有的列进行扫描，相比而言count(1)不用扫描所有列，所以count(1)要快一些。当时是对这一结论深信不疑，虽然不知道为什么。今天正好有时间研究研究看count(*)和count(1)到底有没有性能差异。

我的测试环境是SQL Server 2005 SP2开发版。

在进行测试之前先建立一些测试的数据，代码如下：

create table test(a int, b varchar(100))

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表里面是故意没有加索引的。

top 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)的执行效率是完全一样的，根本不存在所谓的单列扫描和多列扫描的问题。

top 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].[b])))

|--Table Scan(OBJECT:([AdventureWorks].[dbo].[test]))

--------------------------------------------------------------------------*/

现在能看到这两个执行计划唯一不同的地方就是COUNT的内容，对于count(*)是"|—Stream Aggregate(DEFINE:([Expr1005]=count(*)))"，对于count(b)是"|—Stream Aggregate(DEFINE:([Expr1005]=COUNT([AdventureWorks].[dbo].[test].[b])))"，那这两种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)上，在这里你将得到两个一样的执行时间。

top 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].[b])))

|--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].[b])))

|--Hash Match(Aggregate, HASH:([AdventureWorks].[dbo].[test].[b]),

RESIDUAL:([AdventureWorks].[dbo].[test].[b] = [AdventureWorks].[dbo].[test].[b]))

|--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

-----------------------------*/

top 索引与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)

top 堆表和聚集索引表上的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来说是没有帮助的。

top 非聚集索引上的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 毫秒。