ITPub博客

首页 > Linux操作系统 > Linux操作系统 > select count(1) 和select count(*)的区别

select count(1) 和select count(*)的区别

原创 Linux操作系统 作者:释怀355 时间:2013-08-01 22:12:16 0 删除 编辑
select count(1) 和select count(*)都是统计某个表中或者视图的记录条数,那他们两个有什么区别呢?以下我将做下试验。
通过运行的时间和IO的统计值,执行计划来进行观察
use AdventureWorks2008R2
go
--在运行前开启时间和IO统计的设置,选中显示执行计划
--set statistics time on
--set statistics io on

select COUNT(*) from dbo.DatabaseLog
第一次执行:
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 19 ms.

(1 row(s) affected)
Table 'DatabaseLog'. Scan count 1, logical reads 5, physical reads 1, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 78 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

第二次执行:
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)
Table 'DatabaseLog'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


其后多次运行都跟第二次运行耗费的IO,time基本都是一样的,执行计划当然也是一样的。

在执行select count(1) 时,我们为了避免sql重用上面的执行计划,所以使用以下命令先将执行计划缓存先清除掉
use AdventureWorks2008R2
go
--dbcc freeproccache
--set statistics time on
--set statistics io on

select COUNT(1) from dbo.DatabaseLog
第一次运行:
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)
Table 'DatabaseLog'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


第二次执行:
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'DatabaseLog'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


有些人开始怀疑select count (1)是不是还是重用之前select count(*)的执行计划,所以我下面又重新使用一张表,然后先运行select count(1)运行了两遍,然后再清楚执行计划缓存,再执行select count(*),然后我们观察两者的区别


use AdventureWorks2008R2
go
--set statistics time on
--set statistics io on
select COUNT(1) from Person.EmailAddress
第一次执行:
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)
Table 'EmailAddress'. Scan count 1, logical reads 186, physical reads 4, read-ahead reads 182, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 92 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


第二次执行:
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'EmailAddress'. Scan count 1, logical reads 186, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 5 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

清楚执行计划缓存
dbcc freeproccache

use AdventureWorks2008R2
go
--set statistics time on
--set statistics io on
select COUNT(1) from Person.EmailAddress
第一次执行:
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'EmailAddress'. Scan count 1, logical reads 186, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 5 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

第二次执行
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'EmailAddress'. Scan count 1, logical reads 186, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 5 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.


经过以上对比,我们可以看出其实select count(1)和select count(*)对于有索引的表几乎是等同的,执行计划都走的是index scan,花费的成本,时间,IO几乎都是一样的。这个实验也推翻了我们认为select count(1)要比select count(*)优的说法。

以上是对于有索引的表做的测试,下面我将做堆表(即没有索引的表)的测试:
测试一:
CREATE TABLE TEST_COUNT
(
 ID  INT NOT NULL,
 COL1 VARCHAR(10) NULL,
 COL2 VARCHAR(13) NULL,
 COL3 DATETIME NULL,
 COL4 DATETIME NULL
)

DECLARE @A INT
SELECT @A=0
WHILE (@A<100000)
BEGIN
    INSERT INTO TEST_COUNT
    SELECT @A,CAST(REPLICATE(@A,10) AS VARCHAR(10)),CAST(REPLICATE(@A,10) AS VARCHAR(10)),GETDATE(),GETDATE()
    SELECT @A=@A+1
END


USE TEST
GO
--dbcc freeproccache
select COUNT(*) from dbo.TEST_COUNT

开销:
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(1 row(s) affected)
Table 'TEST_COUNT'. Scan count 1, logical reads 715, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 20 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.



USE TEST
GO
--dbcc freeproccache
select COUNT(1) from dbo.TEST_COUNT
开销:
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)
Table 'TEST_COUNT'. Scan count 1, logical reads 715, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 23 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.



根据以上测试的结果,我们可以得出结论,select count(1)和select count(*)基本可以等效了,开销和执行计划都一样

最后我还想测一个问题,对于有多个索引的表,那我们在select count(1)或者select count(*)或者select 具体某一列,那他们的执行计划又是怎样的。
举例测试下:
CREATE TABLE DBO.TEST_COUNT
(
 ID  INT NOT NULL,
 COL1 VARCHAR(10) NULL,
 COL2 VARCHAR(13) NULL,
 COL3 DATETIME NULL,
 COL4 DATETIME NULL,
 col5 int null,
 col6 bit null
)
DECLARE @A INT
SELECT @A=0
WHILE (@A<100000)
BEGIN
    INSERT INTO TEST_COUNT
    SELECT @A,CAST(REPLICATE(@A,10) AS VARCHAR(10)),CAST(REPLICATE(@A,10) AS VARCHAR(10)),GETDATE(),GETDATE(),@A,null
    SELECT @A=@A+1
END
alter  table  DBO.TEST_COUNT
add  constraint PK_TEST_COUNT primary key (ID ASC)
CREATE NONCLUSTERED INDEX IX_TEST_COUNT_COL4 ON DBO.TEST_COUNT
(
 COL4
)

CREATE NONCLUSTERED INDEX IX_TEST_COUNT_col55 ON DBO.TEST_COUNT
(
 col5
)WHERE (COL5>100)

CREATE NONCLUSTERED INDEX IX_TEST_COUNT_col6 ON DBO.TEST_COUNT
(
 col6
)
SELECT COUNT(*) FROM dbo.TEST_COUNT         --count(*)
SELECT COUNT(1) FROM dbo.TEST_COUNT          --count(1)
SELECT COUNT(ID) FROM dbo.TEST_COUNT         --count(主键)
SELECT COUNT(COL3) FROM dbo.TEST_COUNT     --count(一般列)
SELECT COUNT(COL4) FROM dbo.TEST_COUNT     --count(一般非聚集索引列,列类型为int)
SELECT COUNT(col5) FROM dbo.TEST_COUNT       --count(筛选索引列)
select COUNT(COL6) from dbo.TEST_COUNT          --count(一般非聚集索引列)
执行计划:

运行结果:

从以上的查询结果及查询计划,我们可以得出一个结论:
当具有聚集索引的表上存在多个非聚集索引时,
select count(1),count(*),count(主键),那sqlserver会选择扫描一个列最窄的一般的非聚集索引,因为这些非聚集索引上存储了我们的主键列。
select(一般索引列),sqlserver会选择扫描自己的索引.
select(筛选索引列)因为筛选索引的数据不全,而其他索引上又不包含该列信息,所以sqlserver选择扫描聚集索引。
select(一般数据列)因为没有哪个索引包含该列信息,所以sqlserver也选择扫描聚集索引
从查询结果,我又有了额外的收获,因为col6列都是null值,所以count(col6)就是0,也就是sqlserver认为null值就是该行没有值。
又临时想到一个问题,如果该表为存在多个非聚集索引的堆表,那这几个查询sqlserver的执行计划有是怎样的呢?
测试下吧,删掉主键(因为表上没有其他聚集索引,所以这里默认为主键为聚集索引)
alter  table  DBO.TEST_COUNT
drop  constraint PK_TEST_COUNT
执行计划如下:

运行结果和上面一样。
根据执行计划,我们总结一下:
存在有非聚集索引的堆表上,
select count(1),count(*),count(主键),那sqlserver会选择扫描一个列最窄的一般的非聚集索引,因为这些非聚集索引上存储了我们的RID。
select(一般索引列),sqlserver会选择扫描自己的索引.
select(筛选索引列)因为筛选索引的数据不全,而其他索引上又不包含该列信息,所以sqlserver选择全表扫描。
select(一般数据列)因为没有哪个索引包含该列信息,所以sqlserver也选择全表扫描






来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27026361/viewspace-767651/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-11

  • 博文量
    28
  • 访问量
    67210