现实见过在许多开发规范要求中禁止使用count(*)，那有没有对此产生过怀疑呢，为什么数据库系统不记录总行数？为什么要禁用count(*)？count(*)与count(1)以及count(字段)有什么区别？

首先我们就需要了解在MySQL中count(*)的实现方式，对于不同的存储引擎有不同的实现方式

  • MyISAM引擎把一个表的行数都记录在磁盘上，执行count(*)时会直接返回该值，效率很高(无过滤条件的情况下)
  • InnoDB引擎则需要把数据一行一行读取出来，然后计数

那InnoDB为什么不学MyISAM一样把这个记录也保存起来呢？这实际上是由于MVCC(多版本并发控制)的原因，InnoDB表“不确定应该返回多少行”。当事务中对记录进行增加或删除时，由于事务还没提交，在事务内和事务外获取到的行数是不一致的。因此为了获取准确的结果，只能把数据一行行地读取计算。

当然，对于count(*)MySQL是进行了优化的。InnoDB是索引组织表，主键索引的叶子节点存放了整行数据，而普通二级索引的叶子节点则包含key和主键值，因此普通二级索引比主键索引要小的多。对于count(*)遍历那个索引结果都是一致的，因此InnoDB会选择最小的索引树来进行扫描遍历。

这时有人就会说了，在执行show table status时，输出结果中也有一个TABLE_ROWS字段用于表示当前表的行数，是否能直接使用这个值？答案是否定的，索引统计的值是通过采样估算而来的，准确度相差很大。

下面就要比较不同count用法的区别了，我们常见的用法有count(*)、count(主键ID)、count(字段)、count(1)。count是一个聚合函数，对数据一行行判断，如果不是null就加1，否则跳过，最后输出累加值。

  • count(主键ID)：InnoDB会遍历整张表，把每一行的ID取出来，返回给Server层，Server层判断不可能为空就按行累加
  • count(1)：InnoDB会遍历整张表，但不取值。Server层对返回的每一行都放入数字1，判断不可能为空，按行累加
  • count(字段)：如果字段定义非空的话，一行行读取字段记录，判断不能为null，按行累加；如果定义允许为null，在执行时需要把每行都取出来进行判断是否为null，不是null则累加
  • count(*)：并不会把全部字段取出来，系统对其进行了优化，不取值，直接累加

按照效率来算的话，count(字段)<count(主键ID)<count(1)≈count()，所以完全可以使用count()

Tips：欢迎大家参加林晓斌老师的课程MySQL实战45讲