关于SQL Server的COUNT()指令

在有关SQL Server的讨论中，时常遇到这样一个问题：到底应该使用COUNT(*)还是使用COUNT(columnname)，其中columnname是要计算纪录数的列名。一些讨论区和邮件列表给出的建议往往都是使用COUNT(columnname)比使用COUNT(*)更好。但这并不总是对的，有时甚至完全错了。尽管有些场合可以(或者说是应该)使用COUNT(columnname)，但不能总是这样使用。给出错误建议的原因可能是人们对SQL Server处理数据的内部机制还缺乏了解。

COUNT()详解

首先要了解使用COUNT()与其它替代方法之间有何不同，以及这个不同的成因。COUNT()的完整语法是：

 COUNT ( { [ ALL | DISTINCT ] expression } | * ) )

其中，expression可以是任意表达式，但不能处理唯一标识符，文本，二进制或图像数据，也不能使用聚合函数与子查询。虽然表达式大多都是针对表中的一个列，但ALL是缺省的，所以COUNT(expression)等价于COUNT(ALL expression)。

COUNT(*)返回表的所有纪录数，而COUNT(expression)则返回符合表达式计算结果的非空纪录数。通常情况下，COUNT(DISTINCT expression)将相同的纪录只计算为一个。可以看到，根据不同的COUNT()描述，可以得到不同的查询结果。


实践出真知

如前所说，有人以为COUNT(columnname)比COUNT(*)要快，因为COUNT(*)必须读取所有列的纪录(就象运行Select * FROM MYTABLE指令)，而COUNT(columnname)只需读取指定列的纪录。这个理解是错误的，有好几个理由。

首先，如果SQL Server不读取整行纪录就无法得到单个列的内容。SQL Server用磁盘上8KB的数据页来储存各行纪录。这些页面中安放一行或多行纪录(取决于每行纪录的大小。在有些意外情况下，一行纪录可能大于8060 字节)，当SQL Server要处理这些页面数据时就将数据读到内存(RAM)中。为确定单行(或多行)纪录的值，要将完整的 磁盘页读到内存中。这些页面可能已经缓存在内存，这样就会加快运行速度。但SQL仍然要从内存中读取整页的数据来检查一行纪录中的某个列数据。

因为只要求获得纪录数(行数)，SQL Server不是读取这些数据页，而是读取索引数据 - 如果有索引的话。索引的存储方法与数据一样，也使用一个8  KB的索引页。索引总是比整行数据要小(索引只包括数列甚至一列的数据)，一个索引页可以安放比一个数据页更多行的纪录。这意味着SQL Server用索引页检查纪录数时读取的页面数量比用数据页要少，这是好事。

不但COUNT(索引域名)这样做，COUNT(*)同样也使用索引来计算行数。有时，COUNT(columnname)中指定的列不是索引列，而表中有其它列做索引。在这种情况下，COUNT(*)可以使用索引列来计算纪录数，而COUNT(非索引域名)就不得不通过读取数据页来计算非空纪录数了。


可以用SQL Query 

Analyzer来测试下面的脚本(如果测试器未将结果显示设置为文本方式，可按Ctrl-T设置)：

USE Northwind
GO
SET STATISTICS IO ON
Select COUNT(*) FROM orders
Select COUNT(CustomerId) FROM orders
Select * FROM orders
SET STATISTICS IO OFF

指令SET STATISTICS IO ON要求SQL Server输出执行查询时所需的I/O使用数量，可以用这个量来比较不同查询语句中的资源使用情况，从而决定该用哪种查询。脚本执行后立即可得到统计结果。这里关心的是读取逻辑和/或物理页的数量。逻辑页是从内存读取数据(数据页和/或索引页)的页面数量，物理页是从磁盘读取的页面数量。在我的机器上用二个COUNT()得到的纪录数都是830，如果从没有在Order表中增删过纪录，那么你也应该得到这个数。现在来看看从运行上述脚本后得到的逻辑页读次数(多运行几次脚本，可以缓存物理页)，我的第一条指令读取3次逻辑页，而第二条指令读取了21次逻辑页。因为Order表中的CustomerId列没有建索引，所以第二条指令读取的是数据页，而第一条指令读取的是索引页(我的机器上，Order表有ShippersOrders索引)。


到底用哪条指令好？

如上阐明，用COUNT(*)肯定不会更差。相反，有时用COUNT(expression)反而更差。通常表中都有索引列，所以不会遇到上述问题。但糟糕的是COUNT的使用不当可能得到与预期不同的结果！比方说，一个早期的应用中用COUNT(columnname)来计算指定列的行数，而这个列是不许有空值(NULL)的。后来又把这个列设定为允许空值，这时应用得到的不是整个表的行数，而是表中指定列的非空行数！这可能不是早先设计的目的并可能因此导致大错。


但是...

通常情况下，没有理由不使用COUNT(*)。但正如本文开始所说，有时要(或应该)用COUNT(expression)。一个明显的例子是只想得到指定列的非空行数。另一个特例是在使用COUNT()的同时使用还要用聚合函数。例如，有个表要对数据进行排序，某些行内存在空值(NULL )。现在要求列的平均值。通常可以用AVG()来达到目的。但为了说明问题，我们不使用这个函数。

比较以下二个指令就能看到问题症结所在：

Select SUM(column) / COUNT(*) FROM table
Select SUM(column) / COUNT(column) FROM table

这二个指令将返回不同的结果。因为SUM()是忽略NULL值的(NULL不按0计算)。如果总数(sum)是1500，行数为150，其中column列有50行纪录为空值，那么，第一条查询指令得到的结果是10(1500/150)，而第二条查询指令得到的结果是15(1500/100)。在我的数据库咨询工作中经常遇到这个问题。这也是那些不了解不同聚合函数(如上述的SUM()和COUNT())处理空值的不同方式的SQL程序员所遇到的问题所在。