关于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，所以知道有上限就好，平常不用太在意。

...全文