MySQL之COUNT(*)效率

记得又一次例会大家讨论到了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(*) 是表示整个结果集有多少条记录；