• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

关于COUNT的效率问题,不要不看查询计划

guguda2008 2013-11-06 06:06:14
加精
在我上一个帖子里讨论了SELECT 1的问题,后面有两个家伙因为COUNT的效率问题互相回帖,于是我决定新开一贴P谣。
关于COUNT的写法,大致有以下几种:
COUNT(*)
COUNT(1)
COUNT(主键)
COUNT(列名)

我还是写个小例子:
USE TEMPDB
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
COL1 INT
,COL2 INT
,COL3 INT
,COL4 INT
)
GO
CREATE CLUSTERED INDEX INX_TB_COL1_COL2 ON TB(COL1,COL2)
GO
CREATE INDEX INX_TB_COL3 ON TB(COL3)
GO
INSERT INTO TB
SELECT
T1.number
,T2.number
,CASE WHEN T1.number%3=0 THEN NULL ELSE T1.number END
,T1.NUMBER+T2.number
FROM MASTER..spt_values T1
INNER JOIN MASTER..spt_values T2 ON T1.number=T2.number-1
GO 10

SELECT COUNT(*)
FROM TB

SELECT COUNT(1)
FROM TB

SELECT COUNT(COL1)
FROM TB

SELECT COUNT(COL2)
FROM TB

SELECT COUNT(COL3)
FROM TB

SELECT COUNT(COL4)
FROM TB

SELECT COUNT(DISTINCT COL3)
FROM TB

SELECT COUNT(1)
,COUNT(COL3)
,COUNT(DISTINCT COL3)
,COUNT(COL4)
,COUNT(DISTINCT COL4)
FROM TB


自己逐个运行SELECT看执行计划,你会发现前五个的执行计划看起来是一样的,都是走INX_TB_COL3,这是因为系统判断COL3的索引大小小于COL1+COL2的聚集索引,而且COL3中包含了前五个语句所需要的所有列。

但看到第一步的INDEX_SCAN中,会发现COUNT(*)和COUNT(1)是没有输出对象的,即假输出,只输出返回行数,不输出具体的列。而COUNT(COL1),COUNT(COL2),COUNT(COL3)都有一个OUTPUTLIST,分别是这三列。这是因为系统要算这三列中有多少非NULL的值,而COUNT(*)和COUNT(1)都是有一行算一行,不管是不是NULL。

COUNT(COL4)走的是INX_TB_COL1_COL2,也就是聚集索引扫描了,因为COL3中只包含键列COL1、COL2和值列COL3,所以COUNT(COL4)无法走COL3,只能走聚集索引扫描,也就是全表扫描。

后面的COUNT(DISTINCT COL3)是为了让你们对比计划的,所谓DISTINCT,就是在输出了COL3再GROUP BY一下再计算行数。

最后一句是演示COUNT用法,无其它意义。


结论:
COUNT(1)和COUNT(*)是一样的,你可以在里面写任何常量,如COUNT('CSDNDSB'),不会输出一列常量,不会去系统表里找所有列名。
COUNT(1)和COUNT(列名)意义不同,不能放在一起比较。唯一可比较的情况是列名是单主键表的主键列,即聚集非空单键值索引,这种情况下除非想要强制走聚集索引扫描,否则COUNT(1)优于COUNT(列名),因为前者允许计划选择最估索引,而且没有列输出。

关于那个COUNT的数量上限问题,虽然有,但基本不会遇到。因为INT的上限是21亿,有这么大数据量的表会不会用MSSQL本身就是个问题,如果真的在MSSQL里有这么个表还有人敢用COUNT算总数量那这人的智商不会超过60,所以知道有上限就好,平常不用太在意。

...全文
5599 点赞 收藏 72
写回复
72 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
老张一笑 2015-03-06
COUNT(1)和COUNT(*) 貌似在早期的sql 2000 中也是不一样的. 一些老人把这习惯带了下去.
回复
土豆131421 2015-02-10
顶个,说的很对
回复
baopeng82 2015-01-19
呵呵 进来学习下 补补知识
回复
xiaoxiangqing 2015-01-19
找索引就是最快
回复
卖水果的net 版主 2015-01-19
引用 30 楼 pengqian098 的回复:
我没有仔细研究过, 但是某些人说 count(1)比count(*)性能好。
你得问他,好在哪里? 一般情况下,可能会回答你”快“ 或 ”效率高“。你再问快(效率高)在哪里,他可能就开始编了。。。。
回复
sysplay 2015-01-18
COUNT_BIG 的用法与 COUNT 函数类似。 两个函数唯一的差别是它们的返回值。 COUNT_BIG 始终返回 bigint 数据类型值。 COUNT 始终返回 int 数据类型值。
回复
山鹰的天空 2015-01-16
回复
zzyoucan 2014-01-22
一直不太懂count(*)和count(1)什么区别
回复
飞啊子 2013-12-04
引用 楼主 guguda2008 的回复:
关于那个COUNT的数量上限问题,虽然有,但基本不会遇到。因为INT的上限是21亿,有这么大数据量的表会不会用MSSQL本身就是个问题,如果真的在MSSQL里有这么个表还有人敢用COUNT算总数量那这人的智商不会超过60,所以知道有上限就好,平常不用太在意。
亲爱的楼主,还有
 COUNT_BIG(1) 
呢.
回复
jwwyqs 2013-11-21
回复
nixinming 2013-11-20
good good study, day day up!谢谢楼主!
回复
fafffhf 2013-11-16
SDGSGESD
回复
herry321 2013-11-15
回复
maokyou 2013-11-15
,这个问题确实很多新手犯迷糊,谢谢楼主分享,学习学习!
回复
纳兰霸 2013-11-13
学习,感谢
回复
密码木木 2013-11-13
vold.FSTAB
回复
edrsdsf 2013-11-13
ok11111
回复
oh_Maxy 2013-11-12
引用 36 楼 xiaoxiao081228 的回复:
[quote=引用 34 楼 guguda2008 的回复:] [quote=引用 26 楼 xiaoxiao081228 的回复:] 学习了~~~ 我看到有个“最估索引”,特别想问问是什么东西
最优索引,打错字了。意会即可。[/quote] 呵呵…确实写的蛮好的,每款数据库的实现方式不一样但是最后的结果都是一样的 count(*)是不太推荐使用的,一般用count(1)或者count(主键)替代。至于为什么,我忘了[/quote] 编程世界里,*号含义太多了的原因吧。。
回复
kilior 2013-11-12
跨数据库角度,优劣顺序是 count(1) > count(主键) > count(列名) > count(*)
回复
PB菜鸟 2013-11-12
回复
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2013-11-06 06:06
社区公告
暂无公告