SQLSERVER 里SELECT COUNT(1) 和SELECT COUNT(*)哪个性能好？

SQLSERVER 里SELECT COUNT(1) 和SELECT COUNT(*)哪个性能好？

今天遇到某人在我以前写的一篇文章里问到

如果统计信息没来得及更新的话，那岂不是统计出来的数据时错误的了

这篇文章的地址：SQLSERVER是怎麽通过索引和统计信息来找到目标数据的(第三篇)

之前我以为SELECT COUNT(*)是根据统计信息来的，但是后来想了一下，这个肯定不是

那么SQLSERVER怎麽统计SELECT COUNT(*)的呢？？

其实SQLSERVER也是使用扫描的方法

大家也可以先看一下：SQLSERVER中的ALLOCATION SCAN和RANGE SCAN

但是这里不讨论是ALLOCATION SCAN还是RANGE SCAN，大家知道SQLSERVER使用的是扫描的方式就可以了

---

聚集索引表

SQL脚本如下：

USE [pratice]
GO

--建立聚集索引表
CREATE TABLE ct1(c1 INT, c2 VARCHAR (2000));
GO
--建立聚集索引
CREATE CLUSTERED INDEX t1c1 ON ct1(c1);
GO
 
--插入测试数据
DECLARE @a INT;
SELECT @a = 1;
WHILE (@a <= 12)
BEGIN
    INSERT INTO ct1 VALUES (@a, replicate('a', 2000))
    SELECT @a = @a + 1
END
GO




--查询数据
SELECT * FROM ct1 

看一下执行计划

（图片一）

SET STATISTICS PROFILE ON
GO
SELECT COUNT(*) FROM [dbo].[ct1]

 

（图片二） 

这里需要了解流聚合运算符

 MSDN对于流聚合运算符的解释

（图片三）

 

宋沄剑的文章里也有对流聚合运算符的解释

SQL Server中的执行引擎入门

 

重点是理解：Stream Aggregate 运算符按一列或多列对行分组，然后计算由查询返回的一个或多个聚合表达式

Stream Aggregate 运算符按一列对行分组，然后计算由查询返回的一个聚合表达式

我们用下面两个图会清楚一些

 

（图片四）

 

（图片五）

SQLSERVER对表中的行分组进行扫描，但是SQLSERVER以多少行为一组来进行扫描呢？？这个不得而知了

为什麽要使用流聚合？

大家一定会自然而然地想到分组统计提高性能，特别是表中数据量非常大的时候，分组统计特别有用

 

计算标量运算符只是把聚合的结果隐式转换为int类型

 

大家知道ct1表只有两列，但是SELECT COUNT(3) FROM [dbo].[ct1]也能够返回表中的行数

SELECT COUNT(1) FROM [dbo].[ct1]

SELECT COUNT(3) FROM [dbo].[ct1]

（图片六）

 就算用列名都是一样的执行计划

SELECT COUNT(c1) FROM [dbo].[ct1]
SELECT COUNT(c2) FROM [dbo].[ct1]

（图片七）

 

SQLSERVER究竟以哪一列来进行表的行数统计的呢？？？？？？

答案就在

Stream Aggregate 运算符要求输入的数据要按某列进行排序，如果由于前面的 Sort 运算符或已排序的索引查找或扫描导致数据尚未排序，

则优化器将在此运算符前面使用一个 Sort 运算符，使表的某列是有序排序的。

SELECT  COUNT(*)
SELECT  count（3）
SELECT  count（c2）

（图片八）

上面三个SQL语句都是按照聚集索引的第一个字段（ct1表中的c1列）来进行统计的

因为聚集索引的第一个字段是根据建立聚集索引的时候的排序顺序预先排好序

Stream Aggregate 运算符要求输入的数据要按某列进行排序

所以无论是指定字段名、*还是数字，都是根据聚集索引的第一个字段来统计

---

堆表

SQL脚本如下:

CREATE TABLE t1(c1 INT, c2 VARCHAR (8000));
GO


--插入测试数据



DECLARE @a INT;
SELECT @a = 1;
WHILE (@a <= 12)
BEGIN
    INSERT INTO t1 VALUES (@a, replicate('a', 5000))
    SELECT @a = @a + 1
END
GO
 


--查询数据
SELECT * FROM t1

 

（图片九）

 

 （图片十）

堆表这里使用的是ALLOCATION SCAN

因为分配页面的时候是根据c1列的值从1~12进行分配的

（图片十一）

109页面存放的c1值是1

120页面存放的c1值是2

174页面存放的c1值是3

193页面存放的c1值是4

8316页面存放的c1值是5

8340页面存放的c1值是6

8351页面存放的c1值是7

8353页面存放的c1值是8

。

。

。

。

。

（图片十二）

这里执行计划在流聚合之前并没有进行排序的原因：因为建表进行页面分配的时候已经按照C1列的值进行有序的页面分配

所以当ALLOCATION SCAN的时候，C1列已经是有序的了

（图片十三）

不明白的童鞋可以再看一下：SQLSERVER中的ALLOCATION SCAN和RANGE SCAN

为什麽SQLSERVER选择统计C1列的值，因为C1列的值是可以排序的，C2列不能排序，统计不了

那么如果一个表中没有可以用来排序的列呢????

先drop掉t1表，再建立t1表，脚本如下：

CREATE TABLE t1(c1 VARCHAR (2), c2 VARCHAR (8000));
GO


--插入测试数据
DECLARE @a INT;
SELECT @a = 1;
WHILE (@a <= 12)
BEGIN
    INSERT INTO t1 VALUES ('a', replicate('a', 5000))
    SELECT @a = @a + 1
END
GO
 

--查询数据
SELECT * FROM t1

结果是

（图片十四）

我觉得SQLSERVER应该会在表中加上一列，类似用来区分聚集索引页面重复值的UNIQUIFIER(KEY)列

当查询完毕之后就删除掉这一列

（图片十五）

 

---

非聚集索引表

SQL脚本如下:

CREATE TABLE nct1(c1 INT, c2 VARCHAR (8000));
GO
--建立非聚集索引
CREATE  INDEX nt1c1 ON nct1(c1);
GO
 
--插入数据
DECLARE @a INT;
SELECT @a = 1;
WHILE (@a <= 10)
BEGIN
    INSERT INTO nct1 VALUES (@a, replicate('a', 5000))
    SELECT @a = @a + 1
END
GO

--查询数据
SELECT * FROM [dbo].[nct1]
 

（图片十六）

大家一定要记住:非聚集索引是建立在c1列上的!!!

下面两个SQL语句都是一样的,都是根据c1列的值进行统计,而SQLSERVER只扫描非聚集索引页面,而不扫描数据页面

SELECT  COUNT(*) FROM [dbo].[nct1]

SELECT  COUNT(3) FROM [dbo].[nct1]

SELECT  COUNT(*) FROM [dbo].[nct1]是不需要到数据页面去读取c2列的数据的,只需要扫描非聚集索引页面（c1列）就可以了

SELECT  COUNT(3) FROM [dbo].[nct1]跟SELECT  COUNT(*) FROM [dbo].[nct1]也是一样

不知道大家还记得书签查找不，如果SQLSERVER扫描了非聚集索引页面之后还需要到数据页面去读取其他字段的数据的话，就需要RID查找运算符

（图片十七）

SQLSERVER聚集索引与非聚集索引的再次研究（下）

SELECT  COUNT(*) FROM [dbo].[nct1]和SELECT  COUNT(3) FROM [dbo].[nct1]的扫描方式跟前面说的聚集索引表是差不多的

这里就不一一叙述了~

 

而SELECT  COUNT(c2) FROM [dbo].[nct1]为什麽会用表扫描呢？

SELECT  COUNT(c2) FROM [dbo].[nct1]

c2列不在非聚集索引页面里，所以需要表扫描

（图片十八）

SELECT  COUNT(c2) FROM [dbo].[nct1]跟前面说的堆表是差不多的，这里就不一一叙述了

---

总结

做了这麽多实验

可以总结出：select count(*)、count(数字)、count(字段名)是没有性能差别的！！

我说的没有差别是在相同的条件下，就像非聚集索引表，如果使用

SELECT  COUNT(c2) FROM [dbo].[nct1]

跟SELECT  COUNT(*) FROM [dbo].[nct1]、SELECT  COUNT(3) FROM [dbo].[nct1]相比肯定有差别

因为SELECT  COUNT(c2) FROM [dbo].[nct1]走的是表扫描

如果SELECT  COUNT(c1) FROM [dbo].[nct1]

跟SELECT  COUNT(*) FROM [dbo].[nct1]、SELECT  COUNT(3) FROM [dbo].[nct1]相比是没有差别的

（图片十九）

大家走的都是非聚集索引扫描

 

无论是聚集索引表、堆表、非聚集索引表都是扫描表中的记录来统计出表中的行数的

 

希望大家看完这篇文章之后，不再一知半解了，这是我的希望o(∩_∩)o

 

如有不对的地方，欢迎大家拍砖o(∩_∩)o

 

-----------------------------------------------------------------------

补上IO和时间的比较 2013-10-19

---------------------------------

聚集索引表

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT COUNT(*) FROM [dbo].[ct1]

SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。

(1 行受影响)
表 'ct1'。扫描计数 1，逻辑读取 5 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
   CPU 时间 = 15 毫秒，占用时间 = 2 毫秒。

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT COUNT(1) FROM [dbo].[ct1]

SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。

(1 行受影响)
表 'ct1'。扫描计数 1，逻辑读取 5 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

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

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT COUNT(c1) FROM [dbo].[ct1]

SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

(1 行受影响)
表 'ct1'。扫描计数 1，逻辑读取 5 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

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

---------------------------------------------------

堆表

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT COUNT(*) FROM [dbo].[t1]

SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

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

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)
表 't1'。扫描计数 1，逻辑读取 12 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

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

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT COUNT(1) FROM [dbo].[t1]

SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

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

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 79 毫秒。

(1 行受影响)
表 't1'。扫描计数 1，逻辑读取 12 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

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

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT COUNT(c1) FROM [dbo].[t1]

SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

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

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

(1 行受影响)
表 't1'。扫描计数 1，逻辑读取 12 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

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

-----------------------------------------------------------------------------------------

非聚集索引表

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT COUNT(*) FROM [dbo].[nct1]

SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

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

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

(1 行受影响)
表 'nct1'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

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

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT COUNT(1) FROM [dbo].[nct1]

SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

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

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)
表 'nct1'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

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

SET STATISTICS IO ON
SET STATISTICS TIME ON
GO
SELECT COUNT(c1) FROM [dbo].[nct1]

SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

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

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(1 行受影响)
表 'nct1'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。

 

2014-6-21补充：

USE [sss]
--建表
CREATE TABLE counttb ( id INT NULL )

--插入数据
INSERT  INTO [dbo].[counttb]
        ( [id] )
        SELECT  1
        UNION ALL
        SELECT  NULL 

--统计行数
SELECT  COUNT(1) ,
        COUNT(*) ,
        COUNT(id)
FROM    [dbo].[counttb]


--查询索引的统计值
SELECT  a.[rowcnt] ,
        b.[name]
FROM    sys.[sysindexes] AS a
        INNER JOIN sys.[objects] AS b ON a.[id] = b.[object_id]
WHERE   b.[name] = 'counttb'


--创建非聚集索引
CREATE INDEX ix_counttb_id ON [dbo].[counttb] (id)


--统计行数
SELECT  COUNT(1) ,
        COUNT(*) ,
        COUNT(id)
FROM    [dbo].[counttb]

因为在创建非聚集索引前和创建非聚集索引后的行数值都是一样的，可以看出COUNT(*) COUNT(1) 和COUNT(ID)

的统计方式不一样，所以没有可比性

一般我们在统计行数的时候都会把NULL值统计在内的，所以这样的话，最好就是使用COUNT(*) 和COUNT(1) ，这样的速度最快！！