Select count(*)和Count（1）的区别和执行方式

Select count(*)和Count（1）的区别和执行方式

    在SQL Server中Count(*)或者Count(1)或者Count([列])或许是最常用的聚合函数。很多人其实对这三者之间是区分不清的。本文会阐述这三者的作用，关系以及背后的原理。

    往常我经常会看到一些所谓的优化建议不使用Count(* )而是使用Count（1），从而可以提升性能，给出的理由是Count( *)会带来全表扫描。而实际上如何写Count并没有区别。

    Count(1)和Count(*)实际上的意思是，评估Count（）中的表达式是否为NULL，如果为NULL则不计数，而非NULL则会计数。比如我们看代码1所示，在Count中指定NULL（优化器不允许显式指定NULL，因此需要赋值给变量才能指定）。

DECLARE @xx INT

SET @xx=NULL

SELECT COUNT(@xx) FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]

代码清单1.Count中指定NULL

 

     由于所有行都为NULL，则结果全不计数为0，结果如图1所示。

图1.显而易见，结果为0

 

    因此当你指定Count(*） 或者Count（1）或者无论Count(‘anything’)时结果都会一样，因为这些值都不为NULL，如图2所示。

图2.只要在Count中指定非NULL表达式，结果没有任何区别

 

 

那Count列呢？

    对于Count（列）来说，同样适用于上面规则，评估列中每一行的值是否为NULL，如果为NULL则不计数，不为NULL则计数。因此Count（列）会计算列或这列的组合不为空的计数。

 

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

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

    我们在Adventureworks2012示例数据库的[Person].[Address]表上删除所有的非聚集索引，在ModifyDate这个数据类型为DateTime的列上建立索引，我们看执行计划，如图3所示：

图3.使用了CreateDate的索引

 

    我们继续在StateProvinceID列上建立索引，该列为INT列，占4字节，相比之前8字节 DateTime类型的列更短，因此SQL Server选择了StateProvinceID索引。如图4所示。

图4.选择了更短的StateProvinceID索引

 

    因此，如果某个表上Count（*)用的比较多时，考虑在一个最短的列建立一个单列索引，会极大的提升性能。

分类:  SQL性能调优

好文要顶  关注我  收藏该文   

CareySon
关注 - 21
粉丝 - 3814

荣誉： 推荐博客

+加关注

39

3

(请您对文章做出评价)

« 上一篇： 在SQL Server中将数据导出为XML和Json
» 下一篇： SQL Server中使用Check约束提升性能

posted @  2015-02-10 14:51  CareySon 阅读( 43065) 评论( 48)  编辑  收藏

评论列表

  

#1楼   2015-02-10 14:59  yuanyifan   

支持！

支持(1) 反对(0)

  

#2楼   2015-02-10 15:04  冲杀   

直接指定主键列有啥问题呢？

支持(1) 反对(0)

  

#3楼   2015-02-10 15:05  重典   

+1

支持(0) 反对(0)

  

#4楼 [ 楼主]  2015-02-10 15:14  CareySon   

@ 重典
@yuanyifan
多谢支持。

支持(0) 反对(0)

  

#5楼 [ 楼主]  2015-02-10 15:15  CareySon   

@ 冲杀
没有任何问题，因为主键约束不允许null

在单表查询时效果和select Count（1）一样

支持(0) 反对(0)

  

#6楼   2015-02-10 15:49  Joe.TJ   

第一个例子有点极端吧！count(*)对表中数据的null值是计数的啊。

支持(0) 反对(0)

  

#7楼   2015-02-10 15:54  ShanksGao   

支持!

支持(0) 反对(0)

  

#8楼 [ 楼主]  2015-02-10 17:04  CareySon   

@ Joe.TJ
不计数

支持(0) 反对(0)

  

#9楼 [ 楼主]  2015-02-10 17:05  CareySon   

@ ShanksGao
多谢高兄~

支持(0) 反对(0)

  

#10楼   2015-02-10 17:21  禾火石头   

受教

支持(0) 反对(0)

  

#11楼   2015-02-10 17:56  Crazy_Yang   

写的很好 虽然问题很小 但是我一直没有去探索 还是楼主有专研精神

支持(0) 反对(0)

  

#12楼   2015-02-11 09:56  桦仔   

我的笔记有
所有的聚合函数都会忽略null值，只有count（*）例外


http://www.cnblogs.com/lyhabc/articles/3996187.html


--使用SELECT COUNT(*) 统计行数的时候要注意，要统计的那一列的行数要允许not null
--因为当使用SELECT COUNT(*)，sqlserver会使用最窄的索引来统计行数
--那么存在两种情况：
--1、最窄的索引的那一列允许null，而统计的那一列不允许null
--2、最窄的索引的那一列不允许null，而统计的那一列允许null

--第二种情况的测试
--2、最窄的索引的那一列不允许null，而统计的那一列允许null






--总结
--第一种情况的测试
--1、最窄的索引的那一列允许null，而统计的那一列不允许null
--无论count（列名）还是count（*）都可以统计出正确行数


--第二种情况的测试
--2、最窄的索引的那一列不允许null，而统计的那一列允许null
--需要count（列名），否则索引列为null的那些行就会忽略统计造成统计错误

支持(0) 反对(0)

  

#13楼 [ 楼主]  2015-02-11 10:10  CareySon   

@ 桦仔
使用SELECT COUNT(*) 统计行数的时候要注意，要统计的那一列的行数要允许not null

我不太明白这句是什么意思,count(*)和某一个具体列有什么关系？

支持(0) 反对(0)

  

#14楼 [ 楼主]  2015-02-11 10:10  CareySon   

@ Crazy_Yang
@禾火石头
细节里面还是有一些小道理的

支持(0) 反对(0)

  

#15楼   2015-02-11 10:11  fateswing   

赞！

支持(0) 反对(0)

  

#16楼   2015-02-11 10:17  桦仔   

@ CareySon

引用

@桦仔
使用SELECT COUNT(*) 统计行数的时候要注意，要统计的那一列的行数要允许not null

我不太明白这句是什么意思,count(*)和某一个具体列有什么关系？

就是要统计的那一列，我把实验都放在笔记里面了，之前我对这个问题也比较迷糊，最后做了一下实验

支持(0) 反对(0)

  

#17楼   2015-02-11 17:49  思考中   

@ 桦仔

引用

@CareySon

引用

引用@桦仔
使用SELECT COUNT(*) 统计行数的时候要注意，要统计的那一列的行数要允许not null

我不太明白这句是什么意思,count(*)和某一个具体列有什么关系？

就是要统计的那一列，我把实验都放在笔记里面了，之前我对这个问题也比较迷糊，最后做了一下实验

急死，你都说的是什么啊? 楼主在问 count(*)和某一个具体列有什么关系？

支持(2) 反对(0)

  

#18楼   2015-02-12 09:16  王清培   

好文，支持。

支持(0) 反对(0)

  

#19楼   2015-02-12 09:18  王清培   

宋哥我们是不是在北京12年MVPopenday见过。

支持(0) 反对(0)

  

#20楼 [ 楼主]  2015-02-12 10:10  CareySon   

@ 王清培
多谢王兄捧场。
哈是呀。

支持(0) 反对(0)

  

#21楼   2015-02-12 10:16  王清培   

@ CareySon
哈哈，谢谢宋哥当年对我的指点。
我一时没敢认，你还没变，依然那么多年轻。

支持(0) 反对(0)

  

#22楼 [ 楼主]  2015-02-12 10:50  CareySon   

@ 王清培
王兄太谦虚了

PS:本来也很年轻

支持(0) 反对(0)

  

#23楼   2015-02-12 11:30  markdown   

我自己去试了下，总结了下：
count 主键、*、1时，mysql会使用最短长度的索引列，计数所有行数；
count 无索引的列时，不会使用索引，计数非NULL的行数；
count 索引列时，会使用该索引列，计数非NULL的行数；

支持(1) 反对(0)

  

#24楼   2015-02-12 11:41  minttang   

@楼主－－－因此，如果某个表上Count（*)用的比较多时，考虑在一个最短的列建立一个单列索引，会极大的提升性能。
从上面运行的结果看来，似乎没有看到极大的性能提升。是不是有一个例子来对比下有索引和无索引？

支持(0) 反对(0)

  

#25楼   2015-02-12 11:54  旋剑   

@ CareySon
@桦仔

假如表的字段是:id,name（null）

他@桦仔 想用count(*) 来统计有名字的情况
但是count(*) = count(id) != count(name)

对于一般程序员来说，都会
count(*) where name 不为空

支持(0) 反对(0)

  

#26楼   2015-02-12 12:48  囧月言炎   

mark

支持(0) 反对(0)

  

#27楼   2015-02-12 13:31  幻天芒   

http://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better/2710659#2710659

支持(1) 反对(0)

  

#28楼 [ 楼主]  2015-02-12 14:54  CareySon   

@ markdown
是mysql的？

支持(0) 反对(0)

  

#29楼 [ 楼主]  2015-02-12 14:57  CareySon   

@ minttang
是因为你表上已经有一个not null的列有索引了，对比或许不明显，这块我这么写可能不严谨了

支持(0) 反对(0)

  

#30楼 [ 楼主]  2015-02-12 15:17  CareySon   

@ fateswing
thx

支持(0) 反对(0)

  

#31楼   2015-02-12 15:31  卓酷   

count(*)会自动选择宽度最窄的索引列来统计，如果该列存在null值则会被忽略；
count(指定列）时如果该列存在null，也会被忽略；

也就是说如果被指定或被自动选择的被统计列中存在null，将会影响结果。
但我们一般统计条数的时候都会把null先where掉的，所以这虽然是个隐患但往往被我们的编程习惯给弥补了。

支持(0) 反对(0)

  

#32楼 [ 楼主]  2015-02-12 15:57  CareySon   

@ 缪军
哪错了，您方便具体指出来吗？

支持(0) 反对(0)

  

#33楼   2015-02-12 16:08  muyufeng   

哪错了指出来，大家互相学习

支持(0) 反对(0)

  

#34楼   2015-02-12 16:13  Ideasex   

确实有所不同

支持(0) 反对(0)

  

#35楼 [ 楼主]  2015-02-12 17:11  CareySon   

@缪军

引用

1.对count(*)的约定,MSSqlsever在MSDN上说的很清楚:不获取任何特定列的信息,不丢弃任何行的副本,包括null;
2.不同的DBMS对待这个函数的约定可能是不一样的;
3.你的用例不够充分,不能证明你的结论,我这里举几个例子说明事情不是你想象的样子:
1)如果所有字段都是varchar(4000),是不是我增加一个bit型字段(并建索引)就能大幅提升count()的性能?
2)count(*)指定索引提示为主键
500) this.width=500;" alt="" src="http://images.cnitblog.com/blog/144836/...

1.我写的很清楚count中是评估其中内容，而*是常量，因此和具体的列无关。
2.我写的很明白是sql server
3-1 是的，但bit列必须有not null。我不需要证明我的结论，因为在SQL Server中就是按照这个套路来的。你说漏洞百出，你可以举个反例。

3-2 神逻辑，通过提示手动改变了规则然后指出我解释默认规则是错误的~

支持(0) 反对(0)

  

#36楼 [ 楼主]  2015-02-12 23:19  CareySon   

@ 缪军
建议你稍微学一下索引的基础知识。否则我无言以对。


不扫描索引是什么概念？是堆扫描吗？还是聚集索引扫描？这种方式会比扫描非聚集索引成本低？

无脑喷我只能呵呵了

支持(0) 反对(0)

  

#37楼   2015-02-13 07:32  microtry   

噢,我重新检查了测试,确实是我错了,之前的贴图时漏掉了关键信息,我检讨我的粗心和无知,收回我的评论,并表示抱歉

支持(0) 反对(0)

  

#38楼 [ 楼主]  2015-02-13 09:27  CareySon   

@ 卓酷
这个是不对的。select count(*)返回的结果是恒定的，不会因为使用某个索引而变化。因为只有not null的列上的索引才能被用于count(*)

支持(0) 反对(0)

  

#39楼   2015-03-22 10:34  Lvanhades666   

实验了下，第一个例子是有些极端吧？？
<img src="http://images.cnitblog.com/blog2015/523844/201503/221033217346104.jpg" alt="" border="0" "="" style="margin: 0px; padding: 0px; border: 0px; max-width: 400px;">

我测试了 SELECT COUNT(*) FROM TT1
是 4条

支持(1) 反对(0)

  

#40楼   2015-03-28 20:35  邦邦酱好   

膜拜一下，都是好文，感谢楼主的分享~~

支持(0) 反对(0)

  

#41楼 [ 楼主]  2015-03-28 21:40  CareySon   

@ 邦邦酱好
:-)

支持(0) 反对(0)

  

#42楼   2015-05-22 10:27  剑无声   

count(*)统计全为null的行

支持(0) 反对(0)

  

#43楼   2015-06-18 18:09  jinhuazhe2013   

好文要顶~

支持(0) 反对(0)

  

#44楼   2015-09-08 10:09  阳光明媚2015   

@ Lvanhades666
这个是SQL server里的吗？

为什么我试了不行呢？ 列都是null的没保存进去

支持(0) 反对(0)

  

#45楼   2015-09-08 10:15  Lvanhades666   

@ 阳光明媚2015
是的

支持(0) 反对(0)

  

#46楼   2015-10-25 00:03  SmallDong   

对sql中count(number),count(非字段名) 的疑惑

http://segmentfault.com/q/1010000003897771

支持(0) 反对(0)

  

#47楼   2015-11-17 17:30  Aseven   

你好，楼主，想问个问题，如果一个表中没有非聚集索引，那select count(*) 的执行计划也是显示聚集索引扫描，那为什么比select * 要快这么多呢？这个一直不明白，sqlserver读取数据的时候不都是以页为单位吗，这两个查询在大数据上面为什么会差距这么大？

支持(0) 反对(0)

  

#48楼   2015-11-24 14:09  zhangwf   

@ yuanyifan
不知道大家有没有试过，楼主用的数据库是哪一个？
我使用mysql就不一样，count(1),与count(单列主键）时间一致，用时少，
但count(*)名显示时间延长了。