文章出處

SQLSERVER 里SELECT COUNT(1) 和SELECT COUNT(*)哪個性能好？

今天遇到某人在我以前寫的一篇文章里問到

如果統計信息沒來得及更新的話，那豈不是統計出來的數據時錯誤的了

這篇文章的地址：SQLSERVER是怎麼通過索引和統計信息來找到目標數據的(第三篇)

之前我以為SELECT COUNT(*)是根據統計信息來的，但是后來想了一下，這個肯定不是

那么SQLSERVER怎麼統計SELECT COUNT(*)的呢？？

其實SQLSERVER也是使用掃描的方法

大家也可以先看一下：SQLSERVER中的ALLOCATION SCAN和RANGE SCAN

但是這里不討論是ALLOCATION SCAN還是RANGE SCAN，大家知道SQLSERVER使用的是掃描的方式就可以了

聚集索引表

SQL腳本如下：

 1 USE [pratice]
 2 GO
 3 
 4 --建立聚集索引表
 5 CREATE TABLE ct1(c1 INT, c2 VARCHAR (2000));
 6 GO
 7 --建立聚集索引
 8 CREATE CLUSTERED INDEX t1c1 ON ct1(c1);
 9 GO
10  
11 --插入測試數據
12 DECLARE @a INT;
13 SELECT @a = 1;
14 WHILE (@a <= 12)
15 BEGIN
16     INSERT INTO ct1 VALUES (@a, replicate('a', 2000))
17     SELECT @a = @a + 1
18 END
19 GO
20 
21 
22 
23 
24 --查詢數據
25 SELECT * FROM ct1

View Code

看一下執行計劃

（圖片一）

1 SET STATISTICS PROFILE ON
2 GO
3 SELECT COUNT(*) FROM [dbo].[ct1]

（圖片二）

這里需要了解流聚合運算符

MSDN對于流聚合運算符的解釋

（圖片三）

宋沄劍的文章里也有對流聚合運算符的解釋

SQL Server中的執行引擎入門

重點是理解：Stream Aggregate 運算符按一列或多列對行分組，然后計算由查詢返回的一個或多個聚合表達式

Stream Aggregate 運算符按一列對行分組，然后計算由查詢返回的一個聚合表達式

我們用下面兩個圖會清楚一些

（圖片四）

（圖片五）

SQLSERVER對表中的行分組進行掃描，但是SQLSERVER以多少行為一組來進行掃描呢？？這個不得而知了

為什麼要使用流聚合？

大家一定會自然而然地想到分組統計提高性能，特別是表中數據量非常大的時候，分組統計特別有用

計算標量運算符只是把聚合的結果隱式轉換為int類型

大家知道ct1表只有兩列，但是SELECT COUNT(3) FROM [dbo].[ct1]也能夠返回表中的行數

1 SELECT COUNT(1) FROM [dbo].[ct1]

1 SELECT COUNT(3) FROM [dbo].[ct1]

（圖片六）

就算用列名都是一樣的執行計劃

1 SELECT COUNT(c1) FROM [dbo].[ct1]
2 SELECT COUNT(c2) FROM [dbo].[ct1]

（圖片七）

SQLSERVER究竟以哪一列來進行表的行數統計的呢？？？？？？

答案就在

Stream Aggregate 運算符要求輸入的數據要按某列進行排序，如果由于前面的 Sort 運算符或已排序的索引查找或掃描導致數據尚未排序，

則優化器將在此運算符前面使用一個 Sort 運算符，使表的某列是有序排序的。

1 SELECT  COUNT(*)
2 SELECT  count（3）
3 SELECT  count（c2）

（圖片八）

上面三個SQL語句都是按照聚集索引的第一個字段（ct1表中的c1列）來進行統計的

因為聚集索引的第一個字段是根據建立聚集索引的時候的排序順序預先排好序

Stream Aggregate 運算符要求輸入的數據要按某列進行排序

所以無論是指定字段名、*還是數字，都是根據聚集索引的第一個字段來統計

堆表

SQL腳本如下:

 1 CREATE TABLE t1(c1 INT, c2 VARCHAR (8000));
 2 GO
 3 
 4 
 5 --插入測試數據
 6 
 7 
 8 
 9 DECLARE @a INT;
10 SELECT @a = 1;
11 WHILE (@a <= 12)
12 BEGIN
13     INSERT INTO t1 VALUES (@a, replicate('a', 5000))
14     SELECT @a = @a + 1
15 END
16 GO
17  
18 
19 
20 --查詢數據
21 SELECT * FROM t1

View Code

（圖片九）

（圖片十）

堆表這里使用的是ALLOCATION SCAN

因為分配頁面的時候是根據c1列的值從1~12進行分配的

（圖片十一）

109頁面存放的c1值是1

120頁面存放的c1值是2

174頁面存放的c1值是3

193頁面存放的c1值是4

8316頁面存放的c1值是5

8340頁面存放的c1值是6

8351頁面存放的c1值是7

8353頁面存放的c1值是8

。

（圖片十二）

這里執行計劃在流聚合之前并沒有進行排序的原因：因為建表進行頁面分配的時候已經按照C1列的值進行有序的頁面分配

所以當ALLOCATION SCAN的時候，C1列已經是有序的了

（圖片十三）

不明白的童鞋可以再看一下：SQLSERVER中的ALLOCATION SCAN和RANGE SCAN

為什麼SQLSERVER選擇統計C1列的值，因為C1列的值是可以排序的，C2列不能排序，統計不了

那么如果一個表中沒有可以用來排序的列呢????

先drop掉t1表，再建立t1表，腳本如下：

 1 CREATE TABLE t1(c1 VARCHAR (2), c2 VARCHAR (8000));
 2 GO
 3 
 4 
 5 --插入測試數據
 6 DECLARE @a INT;
 7 SELECT @a = 1;
 8 WHILE (@a <= 12)
 9 BEGIN
10     INSERT INTO t1 VALUES ('a', replicate('a', 5000))
11     SELECT @a = @a + 1
12 END
13 GO
14  
15 
16 --查詢數據
17 SELECT * FROM t1

View Code

結果是

（圖片十四）

我覺得SQLSERVER應該會在表中加上一列，類似用來區分聚集索引頁面重復值的UNIQUIFIER(KEY)列

當查詢完畢之后就刪除掉這一列

（圖片十五）

非聚集索引表

SQL腳本如下:

 1 CREATE TABLE nct1(c1 INT, c2 VARCHAR (8000));
 2 GO
 3 --建立非聚集索引
 4 CREATE  INDEX nt1c1 ON nct1(c1);
 5 GO
 6  
 7 --插入數據
 8 DECLARE @a INT;
 9 SELECT @a = 1;
10 WHILE (@a <= 10)
11 BEGIN
12     INSERT INTO nct1 VALUES (@a, replicate('a', 5000))
13     SELECT @a = @a + 1
14 END
15 GO
16 
17 --查詢數據
18 SELECT * FROM [dbo].[nct1]
19

View Code

（圖片十六）

大家一定要記住:非聚集索引是建立在c1列上的!!!

下面兩個SQL語句都是一樣的,都是根據c1列的值進行統計,而SQLSERVER只掃描非聚集索引頁面,而不掃描數據頁面

1 SELECT  COUNT(*) FROM [dbo].[nct1]
2 
3 SELECT  COUNT(3) FROM [dbo].[nct1]

SELECT COUNT(*) FROM [dbo].[nct1]是不需要到數據頁面去讀取c2列的數據的,只需要掃描非聚集索引頁面（c1列）就可以了

SELECT COUNT(3) FROM [dbo].[nct1]跟SELECT COUNT(*) FROM [dbo].[nct1]也是一樣

不知道大家還記得書簽查找不，如果SQLSERVER掃描了非聚集索引頁面之后還需要到數據頁面去讀取其他字段的數據的話，就需要RID查找運算符

（圖片十七）

SQLSERVER聚集索引與非聚集索引的再次研究（下）

SELECT COUNT(*) FROM [dbo].[nct1]和SELECT COUNT(3) FROM [dbo].[nct1]的掃描方式跟前面說的聚集索引表是差不多的

這里就不一一敘述了~

而SELECT COUNT(c2) FROM [dbo].[nct1]為什麼會用表掃描呢？

1 SELECT  COUNT(c2) FROM [dbo].[nct1]

c2列不在非聚集索引頁面里，所以需要表掃描

（圖片十八）

SELECT COUNT(c2) FROM [dbo].[nct1]跟前面說的堆表是差不多的，這里就不一一敘述了

總結

做了這麼多實驗

可以總結出：select count(*)、count(數字)、count(字段名)是沒有性能差別的！！

我說的沒有差別是在相同的條件下，就像非聚集索引表，如果使用

SELECT COUNT(c2) FROM [dbo].[nct1]

跟SELECT COUNT(*) FROM [dbo].[nct1]、SELECT COUNT(3) FROM [dbo].[nct1]相比肯定有差別

因為SELECT COUNT(c2) FROM [dbo].[nct1]走的是表掃描

如果SELECT COUNT(c1) FROM [dbo].[nct1]

跟SELECT COUNT(*) FROM [dbo].[nct1]、SELECT COUNT(3) FROM [dbo].[nct1]相比是沒有差別的

（圖片十九）

大家走的都是非聚集索引掃描

無論是聚集索引表、堆表、非聚集索引表都是掃描表中的記錄來統計出表中的行數的

希望大家看完這篇文章之后，不再一知半解了，這是我的希望o(∩_∩)o

如有不對的地方，歡迎大家拍磚o(∩_∩)o

-----------------------------------------------------------------------

補上IO和時間的比較 2013-10-19

---------------------------------

聚集索引表

1 SET STATISTICS IO ON
2 SET STATISTICS TIME ON
3 GO
4 SELECT COUNT(*) FROM [dbo].[ct1]

1 SQL Server 分析和編譯時間: 
2    CPU 時間 = 0 毫秒，占用時間 = 2 毫秒。
3 
4 (1 行受影響)
5 表 'ct1'。掃描計數 1，邏輯讀取 5 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
6 
7 SQL Server 執行時間:
8    CPU 時間 = 15 毫秒，占用時間 = 2 毫秒。

1 SET STATISTICS IO ON
2 SET STATISTICS TIME ON
3 GO
4 SELECT COUNT(1) FROM [dbo].[ct1]

1 SQL Server 分析和編譯時間: 
2    CPU 時間 = 0 毫秒，占用時間 = 2 毫秒。
3 
4 (1 行受影響)
5 表 'ct1'。掃描計數 1，邏輯讀取 5 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
6 
7 SQL Server 執行時間:
8    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。

1 SET STATISTICS IO ON
2 SET STATISTICS TIME ON
3 GO
4 SELECT COUNT(c1) FROM [dbo].[ct1]

1 SQL Server 分析和編譯時間: 
2    CPU 時間 = 0 毫秒，占用時間 = 1 毫秒。
3 
4 (1 行受影響)
5 表 'ct1'。掃描計數 1，邏輯讀取 5 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
6 
7 SQL Server 執行時間:
8    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。

---------------------------------------------------

堆表

1 SET STATISTICS IO ON
2 SET STATISTICS TIME ON
3 GO
4 SELECT COUNT(*) FROM [dbo].[t1]

 1 SQL Server 分析和編譯時間: 
 2    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 3 
 4 SQL Server 執行時間:
 5    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 6 
 7 SQL Server 執行時間:
 8    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 9 SQL Server 分析和編譯時間: 
10    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
11 
12 (1 行受影響)
13 表 't1'。掃描計數 1，邏輯讀取 12 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
14 
15 SQL Server 執行時間:
16    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。

1 SET STATISTICS IO ON
2 SET STATISTICS TIME ON
3 GO
4 SELECT COUNT(1) FROM [dbo].[t1]

 1 SQL Server 分析和編譯時間: 
 2    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 3 
 4 SQL Server 執行時間:
 5    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 6 
 7 SQL Server 執行時間:
 8    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 9 SQL Server 分析和編譯時間: 
10    CPU 時間 = 0 毫秒，占用時間 = 79 毫秒。
11 
12 (1 行受影響)
13 表 't1'。掃描計數 1，邏輯讀取 12 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
14 
15 SQL Server 執行時間:
16    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。

1 SET STATISTICS IO ON
2 SET STATISTICS TIME ON
3 GO
4 SELECT COUNT(c1) FROM [dbo].[t1]

 1 SQL Server 分析和編譯時間: 
 2    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 3 
 4 SQL Server 執行時間:
 5    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 6 
 7 SQL Server 執行時間:
 8    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 9 SQL Server 分析和編譯時間: 
10    CPU 時間 = 0 毫秒，占用時間 = 1 毫秒。
11 
12 (1 行受影響)
13 表 't1'。掃描計數 1，邏輯讀取 12 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
14 
15 SQL Server 執行時間:
16    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。

-----------------------------------------------------------------------------------------

非聚集索引表

1 SET STATISTICS IO ON
2 SET STATISTICS TIME ON
3 GO
4 SELECT COUNT(*) FROM [dbo].[nct1]

 1 SQL Server 分析和編譯時間: 
 2    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 3 
 4 SQL Server 執行時間:
 5    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 6 
 7 SQL Server 執行時間:
 8    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 9 SQL Server 分析和編譯時間: 
10    CPU 時間 = 0 毫秒，占用時間 = 1 毫秒。
11 
12 (1 行受影響)
13 表 'nct1'。掃描計數 1，邏輯讀取 2 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
14 
15 SQL Server 執行時間:
16    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。

1 SET STATISTICS IO ON
2 SET STATISTICS TIME ON
3 GO
4 SELECT COUNT(1) FROM [dbo].[nct1]

 1 SQL Server 分析和編譯時間: 
 2    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 3 
 4 SQL Server 執行時間:
 5    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 6 
 7 SQL Server 執行時間:
 8    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 9 SQL Server 分析和編譯時間: 
10    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
11 
12 (1 行受影響)
13 表 'nct1'。掃描計數 1，邏輯讀取 2 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
14 
15 SQL Server 執行時間:
16    CPU 時間 = 0 毫秒，占用時間 = 49 毫秒。

1 SET STATISTICS IO ON
2 SET STATISTICS TIME ON
3 GO
4 SELECT COUNT(c1) FROM [dbo].[nct1]

 1 SQL Server 分析和編譯時間: 
 2    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 3 
 4 SQL Server 執行時間:
 5    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 6 
 7 SQL Server 執行時間:
 8    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
 9 SQL Server 分析和編譯時間: 
10    CPU 時間 = 0 毫秒，占用時間 = 0 毫秒。
11 
12 (1 行受影響)
13 表 'nct1'。掃描計數 1，邏輯讀取 2 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
14 
15 SQL Server 執行時間:
16    CPU 時間 = 0 毫秒，占用時間 = 1 毫秒。

2014-6-21補充：

USE [sss]
--建表
CREATE TABLE counttb ( id INT NULL )

--插入數據
INSERT  INTO [dbo].[counttb]
        ( [id] )
        SELECT  1
        UNION ALL
        SELECT  NULL 

--統計行數
SELECT  COUNT(1) ,
        COUNT(*) ,
        COUNT(id)
FROM    [dbo].[counttb]


--查詢索引的統計值
SELECT  a.[rowcnt] ,
        b.[name]
FROM    sys.[sysindexes] AS a
        INNER JOIN sys.[objects] AS b ON a.[id] = b.[object_id]
WHERE   b.[name] = 'counttb'


--創建非聚集索引
CREATE INDEX ix_counttb_id ON [dbo].[counttb] (id)


--統計行數
SELECT  COUNT(1) ,
        COUNT(*) ,
        COUNT(id)
FROM    [dbo].[counttb]