COUNT()詳解

首先要了解使用COUNT()與其它替代方法之間有何不同，以及這個(gè)不同的成因。COUNT()的完整語(yǔ)法是：

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

其中，expression可以是任意表達(dá)式，但不能處理唯一標(biāo)識(shí)符，文本，二進(jìn)制或圖像數(shù)據(jù)，也不能使用聚合函數(shù)與子查詢。雖然表達(dá)式大多都是針對(duì)表中的一個(gè)列，但ALL是缺省的，所以COUNT(expression)等價(jià)于COUNT(ALL expression)。

COUNT(*)返回表的所有紀(jì)錄數(shù)，而COUNT(expression)則返回符合表達(dá)式計(jì)算結(jié)果的非空紀(jì)錄數(shù)。通常情況下，COUNT(DISTINCT expression)將相同的紀(jì)錄只計(jì)算為一個(gè)?？梢钥吹?，根據(jù)不同的COUNT()描述，可以得到不同的查詢結(jié)果。

實(shí)踐出真知

如前所說(shuō)，有人以為COUNT(columnname)比COUNT(*)要快，因?yàn)镃OUNT(*)必須讀取所有列的紀(jì)錄(就象運(yùn)行SELECT * FROM MYTABLE指令)，而COUNT(columnname)只需讀取指定列的紀(jì)錄。這個(gè)理解是錯(cuò)誤的，有好幾個(gè)理由。

首先，如果SQL Server不讀取整行紀(jì)錄就無(wú)法得到單個(gè)列的內(nèi)容。SQL Server用磁盤(pán)上8KB的數(shù)據(jù)頁(yè)來(lái)儲(chǔ)存各行紀(jì)錄。這些頁(yè)面中安放一行或多行紀(jì)錄(取決于每行紀(jì)錄的大小。在有些意外情況下，一行紀(jì)錄可能大于8060 字節(jié))，當(dāng)SQL Server要處理這些頁(yè)面數(shù)據(jù)時(shí)就將數(shù)據(jù)讀到內(nèi)存(RAM)中。為確定單行(或多行)紀(jì)錄的值，要將完整的磁盤(pán)頁(yè)讀到內(nèi)存中。這些頁(yè)面可能已經(jīng)緩存在內(nèi)存，這樣就會(huì)加快運(yùn)行速度。但SQL仍然要從內(nèi)存中讀取整頁(yè)的數(shù)據(jù)來(lái)檢查一行紀(jì)錄中的某個(gè)列數(shù)據(jù)。

因?yàn)橹灰螳@得紀(jì)錄數(shù)(行數(shù))，SQL Server不是讀取這些數(shù)據(jù)頁(yè)，而是讀取索引數(shù)據(jù) - 如果有索引的話。索引的存儲(chǔ)方法與數(shù)據(jù)一樣，也使用一個(gè)8 KB的索引頁(yè)。索引總是比整行數(shù)據(jù)要小(索引只包括數(shù)列甚至一列的數(shù)據(jù))，一個(gè)索引頁(yè)可以安放比一個(gè)數(shù)據(jù)頁(yè)更多行的紀(jì)錄。這意味著SQL Server用索引頁(yè)檢查紀(jì)錄數(shù)時(shí)讀取的頁(yè)面數(shù)量比用數(shù)據(jù)頁(yè)要少，這是好事。

不但COUNT(索引域名)這樣做，COUNT(*)同樣也使用索引來(lái)計(jì)算行數(shù)。有時(shí)，COUNT(columnname)中指定的列不是索引列，而表中有其它列做索引。在這種情況下，COUNT(*)可以使用索引列來(lái)計(jì)算紀(jì)錄數(shù)，而COUNT(非索引域名)就不得不通過(guò)讀取數(shù)據(jù)頁(yè)來(lái)計(jì)算非空紀(jì)錄數(shù)了。

可以用SQL Query

Analyzer來(lái)測(cè)試下面的腳本(如果測(cè)試器未將結(jié)果顯示設(shè)置為文本方式，可按Ctrl-T設(shè)置)：

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輸出執(zhí)行查詢時(shí)所需的I/O使用數(shù)量，可以用這個(gè)量來(lái)比較不同查詢語(yǔ)句中的資源使用情況，從而決定該用哪種查詢。腳本執(zhí)行后立即可得到統(tǒng)計(jì)結(jié)果。這里關(guān)心的是讀取邏輯和/或物理頁(yè)的數(shù)量。邏輯頁(yè)是從內(nèi)存讀取數(shù)據(jù)(數(shù)據(jù)頁(yè)和/或索引頁(yè))的頁(yè)面數(shù)量，物理頁(yè)是從磁盤(pán)讀取的頁(yè)面數(shù)量。在我的機(jī)器上用二個(gè)COUNT()得到的紀(jì)錄數(shù)都是830，如果從沒(méi)有在Order表中增刪過(guò)紀(jì)錄，那么你也應(yīng)該得到這個(gè)數(shù)。現(xiàn)在來(lái)看看從運(yùn)行上述腳本后得到的邏輯頁(yè)讀次數(shù)(多運(yùn)行幾次腳本，可以緩存物理頁(yè))，我的第一條指令讀取3次邏輯頁(yè)，而第二條指令讀取了21次邏輯頁(yè)。因?yàn)镺rder表中的CustomerId列沒(méi)有建索引，所以第二條指令讀取的是數(shù)據(jù)頁(yè)，而第一條指令讀取的是索引頁(yè)(我的機(jī)器上，Order表有ShippersOrders索引)。

到底用哪條指令好？

如上闡明，用COUNT(*)肯定不會(huì)更差。相反，有時(shí)用COUNT(expression)反而更差。通常表中都有索引列，所以不會(huì)遇到上述問(wèn)題。但糟糕的是COUNT的使用不當(dāng)可能得到與預(yù)期不同的結(jié)果！比方說(shuō)，一個(gè)早期的應(yīng)用中用COUNT(columnname)來(lái)計(jì)算指定列的行數(shù)，而這個(gè)列是不許有空值(NULL)的。后來(lái)又把這個(gè)列設(shè)定為允許空值，這時(shí)應(yīng)用得到的不是整個(gè)表的行數(shù)，而是表中指定列的非空行數(shù)！這可能不是早先設(shè)計(jì)的目的并可能因此導(dǎo)致大錯(cuò)。

但是...

通常情況下，沒(méi)有理由不使用COUNT(*)。但正如本文開(kāi)始所說(shuō)，有時(shí)要(或應(yīng)該)用COUNT(expression)。一個(gè)明顯的例子是只想得到指定列的非空行數(shù)。另一個(gè)特例是在使用COUNT()的同時(shí)使用還要用聚合函數(shù)。例如，有個(gè)表要對(duì)數(shù)據(jù)進(jìn)行排序，某些行內(nèi)存在空值(NULL )?，F(xiàn)在要求列的平均值。通?？梢杂肁VG()來(lái)達(dá)到目的。但為了說(shuō)明問(wèn)題，我們不使用這個(gè)函數(shù)。

比較以下二個(gè)指令就能看到問(wèn)題癥結(jié)所在：

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

這二個(gè)指令將返回不同的結(jié)果。因?yàn)镾UM()是忽略NULL值的(NULL不按0計(jì)算)。如果總數(shù)(sum)是1500，行數(shù)為150，其中column列有50行紀(jì)錄為空值，那么，第一條查詢指令得到的結(jié)果是10(1500/150)，而第二條查詢指令得到的結(jié)果是15(1500/100)。在我的數(shù)據(jù)庫(kù)咨詢工作中經(jīng)常遇到這個(gè)問(wèn)題。這也是那些不了解不同聚合函數(shù)(如上述的SUM()和COUNT())處理空值的不同方式的SQL程序員所遇到的問(wèn)題所在。