记得又一次例会大家讨论到了count(*)和count(col)的问题,大家当时说的都count(col)比较快,最近看了几篇文章,发现大家的这种观点可能是有问题的。关于这个问题网上有很多版本,看的我都凌乱了。我还是整理了一下。
在不加WHERE限制条件的情况下,COUNT(*)与COUNT(COL)基本可以认为是等价的;但是在有WHERE限制条件的情况下,COUNT(*)会比COUNT(COL)快非常多;
具体的数据参考如下:
mysql> SELECT COUNT(*) FROM testCount where fid = 604;
+————+
| COUNT(fid) |
+————+
| 79000 |
+————+
1 row in set (0.03 sec)
mysql> SELECT COUNT(tid) FROM testCount where fid = 604;
+————+
| COUNT(tid) |
+————+
| 79000 |
+————+
1 row in set (0.33 sec)
mysql> SELECT COUNT(pid) FROM testCount where fid = 604;
+————+
| COUNT(pid) |
+————+
| 79000 |
+————+
1 row in set (0.33 sec)
仔细查阅累下手册,发现当没有WHERE语句对于整个mysql的表进行count运算的时候,MyISAM类型的表中保存有总的行数,而当添加有WHERE限定语句的时候Mysql需要对整个表进行检索从而得出count的数值
COUNT(*)通常是对主键进行索引扫描,而COUNT(COL)就不一定了,另外前者是统计表中的所有符合的纪录总数,而后者是计算表中所有符合的COL的纪录数。还有有区别的。
COUNT时的WHERE
简单说下,就是COUNT的时候,如果没有WHERE限制的话,MySQL直接返回保存有总的行数
而在有WHERE限制的情况下,总是需要对MySQL进行全表遍历。
优化总结:(网上的观点)
1.任何情况下SELECT COUNT(*) FROM tablename是最优选择;
2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询;
3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。
Count(1)和count(*)比较
比较这两个的方法很简单,使用set showplan_text on命令可以查看语句的执行计划
select count(*) from test
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
|--Table Scan(OBJECT:([AdventureWorks].[dbo].[test]))
select count(1) from test
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
|--Table Scan(OBJECT:([AdventureWorks].[dbo].[test]))
对比下两个执行计划我们可以发现是完全一样的,这也就说明count(*)和count(1)的执行效率是完全一样的,根本不存在所谓的单列扫描和多列扫描的问题。
count(col)与count(*)的对比
同样,我们先看一下两个不同count方式的执行计划。
count(*)的执行计划看上面的例子。
count(b)的执行计划:
select count(b) from test
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1005],0)))
|--Stream Aggregate(DEFINE:([Expr1005]=COUNT([AdventureWorks].[dbo].[test].[b])))
|--Table Scan(OBJECT:([AdventureWorks].[dbo].[test]))
现在能看到这两个执行计划唯一不同的地方就是COUNT的内容,对于count(*)是"|—Stream Aggregate(DEFINE:([Expr1005]=count(*)))",对于count(b)是"|—Stream Aggregate(DEFINE:([Expr1005]=COUNT([AdventureWorks].[dbo].[test].[b])))",那这两种count方式会不会有什么不一样呢?
让我们先看一下BOL里面对count(*)以及count(col)的说明:
COUNT(*) 返回组中的项数。包括 NULL 值和重复项。
COUNT(ALL expression) 对组中的每一行都计算 expression 并返回非空值的数量。
expression
除 text、image 或 ntext 以外任何类型的表达式。不允许使用聚合函数和子查询。
*
指定应该计算所有行以返回表中行的总数。COUNT(*) 不需要任何参数,而且不能与 DISTINCT 一起使用。
COUNT(*) 不需要 expression 参数,因为根据定义,该函数不使用有关任何特定列的信息。
COUNT(*) 返回指定表中行数而不删除副本。它对各行分别计数。包括包含空值的行。
也就是说count(*)只是返回表中行数,因此SQL Server在处理count(*)的时候只需要找到属于表的数据块块头,然后计算一下行数就行了,而不用去读取里面数据列的数据。而对于count(col)就不一样了,为了去除col列中包含的NULL行,SQL Server必须读取该col的每一行的值,然后确认下是否为NULL,然后在进行计数。因此count(*)应该是比count(col)快的,下面我们来验证一下。
我们通过在同样的条件下将select count(…) from test执行1000次来看两种count方式是否是一样的:
先看count(*)
declare @n int, @a int
set @n = 1
while @n <= 1000
begin
select @a = count(*) from test
set @n = @n+1
end
?
/*------------------------------
?执行结果:29s
?-----------------------------*/
接着看count(col)
declare @n int, @a int
set @n = 1
while @n <= 1000
begin
select @a = count(b) from test
set @n = @n+1
end
?
/*------------------------------
?执行结果:57s
?-----------------------------*/
从执行结果可以看出相差还是很大的,count(*)比count(col)快了一倍。
不过因为count(*)和count(col)使用的目的是不一样的,在必须要使用count(col)的时候还是要用的,只是在统计表全部行数的时候count(*)就是最佳的选择了。
最后奉上我的建议:
在大家写完语句后如果想优化,可以通过set showplan_text on 来查看语句的执行计划,进行优化。明确了优化目标之后,我们需要确定达到我们目标的方法。对于 SQL 语句来说,加快语句的执行速度的方法,那就是改变 SQL 的执行计划,让他尽量“少走弯路”,尽量通过各种“捷径”来找到我们需要的数据,以达到 “减少 IO 次数” 和 “降低 CPU 计算” 的目标。
另外一点就是count(col),和count(*)返回的结果其实不一样。
count(column) 是表示结果集中有多少个column字段不为空的记录;
count(*) 是表示整个结果集有多少条记录;