高性能MySQL count(1)与count(*)的差别

 

-------------------------------------------------------------------------------------------------第一篇-------------------------------------------------------------------------------------------------------------------

 

sql调优，主要是考虑降低：consistent gets和physical reads的数量。

count(1)与count(*)比较：

如果你的数据表没有主键，那么count(1)比count(*)快
如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快
如果你的表只有一个字段的话那count(*)就是最快的啦
count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*),自动会优化指定到那一个字段。所以没必要去count(?)，用count(*),sql会帮你完成优化的

count详解：

count(1)跟count(主键)一样，只扫描主键。

count(*)跟count(非主键)：

　　count(*)将返回表格中所有存在的行的总数包括值为null的行，然而count(列名)将返回表格中除去null以外的所有行的总数(有默认值的列也会被计入）.
　　distinct 列名,得到的结果将是除去值为null和重复数据后的结果

count(主键) 不一定比count(其余索引) 快： 

索引是一种B+树的结构，以块为单位进行存储。假设块大小是1K，主键索引大小为4B，有一个字段A的索引大小为2B。 
同样一个块，能存放256个主键索引，但是能存放512个字段A的索引。 
假设总数据是2K条，意味着主键索引占用了8个块，而A字段索引占用了4个块，统计时用主键索引需要经历的块多，IO次数多。效率也比A字段索引慢。

总结

 

1.如果在开发中确实需要用到count()聚合，那么优先考虑count(*)，因为mysql数据库本身对于count(*)做了特别的优化处理。

有主键或联合主键的情况下，count(*)略比count(1)快一些。 
没有主键的情况下count(1)比count(*)快一些。 
如果表只有一个字段，则count(*)是最快的。
2.使用count()聚合函数后，最好不要跟where age = 1；这样的条件，会导致不走索引，降低查询效率。除非该字段已经建立了索引。使用count()聚合函数后，若有where条件，且where条件的字段未建立索引，则查询不会走索引，直接扫描了全表。 
3.count(字段),非主键字段，这样的使用方式最好不要出现。因为它不会走索引.

 

--------------------------------------------------------------------------------第二篇----------------------------------------------------------------------------------------------------------------

 

首先，以我们最常见的两种数据库表引擎MyISAM和Innodb来讲。

MyISAM

MyISAM在统计表的总行数的时候会很快，但是有个大前提，不能加有任何WHERE条件。这是因为：MyISAM对于表的行数做了优化，具体做法是有一个变量存储了表的行数，如果查询条件没有WHERE条件则是查询表中一共有多少条数据，MyISAM可以做到迅速返回，所以也解释了如果加WHERE条件，则该优化就不起作用了。细心的同学会发现，innodb的表也有这么一个存储了表行数的变量，但是很遗憾这个值是一个估计值，没有什么实际意义。

Innodb

在该引擎下，COUNT(1)和COUNT(*)哪个快呢？结论是：这俩在高版本的MySQL（5.5及以后，5.1的没有考证）是没有什么区别的，也就没有COUN(1)会比COUNT(*)更快这一说了。

WHY？这就要从COUNT()函数的具体含义说起了。”

 COUNT()有两个非常不同的作用：它可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL）。如果在COUNT()的括号中定了列或者列表达式，则统计的就是这个表达式有值的结果数。......COUNT()的另一个作用是统计结果集的行数。当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用COUNT(*)的时候，这种情况下通配符*并不像我们猜想的那样扩展成所有的列，实际上，他会忽略所有列而直接统计所有的行数“——《高性能MySQL》。

通常，我们将第一个字段（一般是ID）作为主键，那么这个时候COUNT(1)实际统计的就是行数（此处表达有误，详见文章结尾），因为主键肯定是非NULL的。问题是Innodb是通过主键索引来统计行数的吗？结论是：如果该表只有一个主键索引，没有任何二级索引的情况下，那么COUNT(*)和COUNT(1)都是通过通过主键索引来统计行数的。如果该表有二级索引，则COUNT(1)和COUNT(*)都会通过占用空间最小的字段的二级索引进行统计，也就是说虽然COUNT(1)指定了第一列（此处表达有误，详见文章结尾）但是innodb不会真的去统计主键索引（一般为第一个字段的索引）。

实验

第一步

新建一张基于Innodb的表，只有一个ID主键，并插入5w的测试数据，建表语句如下：

CREATE TABLE `tb_news` (
  `id` bigint(21) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  `content` mediumtext NOT NULL,
  `count_ass` char(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=50001 DEFAULT CHARSET=utf8

这个时候执行COUNT(1)和COUNT(*)可以看到解释器的结果如下（两者一致，所以就只截了一张图），可以看到，两者都用了主键索引进行行数的统计：

第二步

新建一个二级索引title，之后在分别看一下COUNT(1)和COUNT(*)的解释器结果（两者依然完全一致），这时已经用二级索引进行统计而非主键索引：

第三步

在我们之前特地预留的一个小字段count_ass字段建一个索引，到这一步目前表中有三个索引：一个主键索引，两个二级索引。

 

这时候我们再看一下COUNT(1)和COUNT(*)会通过哪个索引来统计行数（两者还是一致）。

 

原理

目前基于磁盘的数据库或者搜索引擎（比如Lucene）的性能瓶颈主要都是在IO阶段，相比于CPU和RAM，IO操作实在太慢了，所以这类系统的优化方向也都都是类似的——尽一切可能减少IO的次数（所以很多用ES的程序在性能优化到极限的时候选择直接上SSD）。这里统计行数的操作，查询优化器的优化方向就是选择能够让IO次数最少的索引，也就是基于占用空间最小的字段所建的索引（每次IO读取的数据量是固定的，索引占用的空间越小所需的IO次数也就越少）。而Innodb的主键索引是聚簇索引（包含了KEY，除了KEY之外的其他字段值，事务ID和MVCC回滚指针）所以主键索引一定会比二级索引（包含KEY和对应的主键ID）大，也就是说在有二级索引的情况下，一般COUNT()都不会通过主键索引来统计行数，在有多个二级索引的情况下选择占用空间最小的。

如果说有张Innodb的表只有主键索引，而且记录还比较大（比如30K），则统计行的操作会非常慢，因为IO次数会很多（这里就不做实验截图了，有兴趣可以自己试一下）。

一个优化方案就是预先建一个小字段并建二级索引专门用来统计行数，极端情况下这种优化速度提高上千倍也是正常的。

结论

结论就是对于COUNT(1)和COUNT(*)执行优化器的优化是完全一样的，并没有COUNT(1)会比COUNT(*)快这个说法