MySQL数据库count语句详解

select count(*) 应该是一个比较常用的语句，用来统计记录行数。

但是，慢慢地你会发现，这个语句越来越慢了，为什么呢？

count(*) 的实现方式

首先，我们来看下它的实现方式。

MySQL 中，不同的存储引擎，count(*) 的实现方式是不同的。

1、MyISAM 引擎，表级锁，不需考虑事务，比较简单粗暴，直接将表的总行数存储在磁盘上，因此效率很高；

2、InnoDB 引擎中，行级锁，需要考虑事务，执行时，需要一行行的把数据查出来，然后累加；

为啥 MyISAM 就可以这样做呢？因为它不支持事务啊，不会有并发的数据库行数修改，所以查询得到的行数是准确的，不用担心数据不一致的问题。

而 InnoDB 就不一样了。

由于 MVCC 的存在，InnoDB 在当前执行环境下，对一共有多少数据行是不确定的，比如：

假设，表 t 中有 10000 条数据，有下面三个用户并行的会话：

1、A 启动事务，查询表的总行数；
2、C 直接插入一条数据，然后查询总行数；
3、B 启动事务，插入一条数据，然后查询总行数；
4、C 查询总行数；

注意，上面启动的事务都没有提交。

 

A、B、C 查询的结果都不相同。

B 读到的是 10002，是因为可重复读隔离级别的存在，而 C 未开启事务，因此无法看到别的事务的更新；

综上，InnoDB 引擎中，在每一个会话中，都需要逐行读取数据，然后计数返回总行数。

InnoDB 对 count(*) 的优化

我们知道，COUNT(*)的目的只是为了统计总行数，所以，他根本不关心自己查到的具体值，所以，他如果能够在扫表的过程中，选择一个成本较低的索引进行的话，那就可以大大节省时间。

InnoDB中索引分为聚簇索引（主键索引）和非聚簇索引（非主键索引），聚簇索引的叶子节点中保存的是整行记录，而非聚簇索引的叶子节点中保存的是该行记录的主键的值。所以，相比之下，非聚簇索引要比聚簇索引小很多。

MySQL会优先选择最小的非聚簇索引来扫表。所以，当我们建表的时候，除了主键索引以外，创建一个非主键索引还是有必要的。

这种情况下，InnoDB 在执行 count(*) 时，就会判断使用哪个索引，会选择最小的树来进行遍历。

在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

小结

1、由于 MyISAM 引擎不需要支持事务，因此可以快速返回 count(*)；
2、show table status 命令虽然返回很快，但是不准确；
3、InnoDB 执行 count(*) 时会遍历全表，因此性能较差；

 

count(*)、count(1)、count(主键)、count(字段)的区别

以下，基于 InnoDB。

含义区别

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL  

count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL  

count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计

性能区别

对于 count(字段)：

1. 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，无需判断为null，按行累加；
2. 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

对于 count(主键id)，InnoDB 会遍历全表，取每行的主键 id，返回给 server 层，server 层拿到数据后，进行判断累加。

对于 count(1)，InnoDB 仍遍历全表，但是不取值，server 层对返回的每一行数据新增一个 1，然后进行判断累加；因此，count(1) 要更快些，因为无需取值。从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化。count(*) 肯定不是 null，按行累加。

结论：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)，因为这个是SQL92定义的标准统计行数的语法。