当前位置： 首页 > news >正文

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

news 来源：原创 2024/4/27 14:04:05

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]

因为在创建非聚集索引前和创建非聚集索引后的行数值都是一样的，可以看出COUNT(*) COUNT(1) 和COUNT(ID)

的统计方式不一样，所以没有可比性

一般我们在统计行数的时候都会把NULL值统计在内的，所以这样的话，最好就是使用COUNT(*) 和COUNT(1) ，这样的速度最快！！

相关文章：

  • hfrk2410_a1.1开发板移植linux-2.6.32.27--网卡篇(cs8900)
  • VS2005相关----不能添加新项
  • nexus启动错报:1067 与jdk9相关
  • 谈谈VIP漂移那点破事
  • 程序员的几款利器
  • 数据库系统设计_银行业务管理系统
  • 云桌面、云课堂究竟是什么？企业追捧是坑还是福？
  • 对hibernate框架的认知及总结
  • 三个常用的PHP图表类库
  • 在android源码中单独编译modkoid工程遇见的问题
  • Hanlp中使用纯JAVA实现CRF分词
  • kotlin使用spring mvc（三）
  • 在Ubuntu 11.04中安装Openresty
  • Nginx 部署HTTPS
  • 浅谈IP地址-1
  • 11111111
  • canvas实际项目操作，包含：线条，圆形，扇形，图片绘制，图片圆角遮罩，矩形，弧形文字...
  • classpath对获取配置文件的影响
  • IDEA常用插件整理
  • iOS动画编程-View动画[ 1 ] 基础View动画
  • PhantomJS 安装
  • puppeteer stop redirect 的正确姿势及 net::ERR_FAILED 的解决
  • Python语法速览与机器学习开发环境搭建
  • Spring Cloud Alibaba迁移指南（一）：一行代码从 Hystrix 迁移到 Sentinel
  • Wamp集成环境 添加PHP的新版本
  • 创建一种深思熟虑的文化
  • 技术发展面试
  • 离散点最小（凸）包围边界查找
  • 盘点那些不知名却常用的 Git 操作
  • 区块链分支循环
  • 应用生命周期终极 DevOps 工具包
  • 3月27日云栖精选夜读 | 从 “城市大脑”实践，瞭望未来城市源起 ...
  • # Apache SeaTunnel 究竟是什么？
  • # 安徽锐锋科技IDMS系统简介
  • #162 (Div. 2)
  • （1）(1.8) MSP(MultiWii 串行协议)(4.1 版)
  • (3)Dubbo启动时qos-server can not bind localhost22222错误解决
  • （aiohttp-asyncio-FFmpeg-Docker-SRS）实现异步摄像头转码服务器
  • （BFS）hdoj2377-Bus Pass
  • （十七）Flask之大型项目目录结构示例【二扣蓝图】
  • （十三）Flask之特殊装饰器详解
  • (一) storm的集群安装与配置
  • （一）spring cloud微服务分布式云架构 - Spring Cloud简介
  • (转)Mysql的优化设置
  • （转）Unity3DUnity3D在android下调试
  • （转）德国人的记事本
  • （转）淘淘商城系列——使用Spring来管理Redis单机版和集群版
  • .360、.halo勒索病毒的最新威胁：如何恢复您的数据？
  • .NET C#版本和.NET版本以及VS版本的对应关系
  • .NET Entity FrameWork 总结 ，在项目中用处个人感觉不大。适合初级用用，不涉及到与数据库通信。
  • .Net中wcf服务生成及调用
  • @开发者，一文搞懂什么是 C# 计时器！
  • @我的前任是个极品 微博分析
  • [ 云计算 | Azure 实践 ] 在 Azure 门户中创建 VM 虚拟机并进行验证
  • [1] 平面(Plane)图形的生成算法