【MySQL笔记】梳理下COUNT(*)，COUNT(1)，COUNT(字段名)区别、查询过程及常见问题

今天复盘了以下这个经典面试问题，做个记录

稍微介绍下COUNT

COUNT(expr):

• 返回SELECT语句查询的行中expr不为NULL的数量

• 结果是一个BIGINT

  • 顺便回顾下几个INT的区别

    类型	大小	有符号范围	无符号范围
    TINYINT	1 Bytes	(-2^4 , 2^4-1)	(0, 2^8)
    SMALLINT	2 Bytes	(-2^15, 2^15-1)	(0, 2^16)
    MEDIUMINT	3 Bytes	(-2^23, 2^23-1)	(0, 2^24)
    INT/INTEGER	4 Bytes	(-2^31, 2^31-1)	(0, 2^32)
    BIGINT	8 Bytes	(-2^63, 2^63-1)	(0, 2^64)

• 如果查询没有任何结果，返回0

COUNT(字段名)，COUNT(*)，COUNT(1)

在通过 count 函数统计有多少个记录时，MySQL 的 server 层会维护一个名叫 count 的变量。

后面就是遍历通过count+1来计数，三种COUNT()方式的不同就是在这个过程中。

COUNT(字段名)

先说结果：查询符合条件，并且字段名不为NULL的行数

查询过程

大概过程就是：

• server 层会循环向 InnoDB 读取一条记录
• 如果指定的字段名参数不为NULL，则count+1
• 如果符合条件的行数被读完，推出循环，将count发回客户端

但是过程中

COUNT(主键)有什么不同？

需要注意，参数中的字段名为主键的时候，具体查询过程又会根据表中【是否有二级索引】有所区别。

因为B+树中：

• 聚簇索引（主键索引）的叶子节点存放的是实际数据；
• 二级索引的叶子节点存放的是主键值，而不是实际数据。

可见，相同数量的二级索引记录比主键索引占用更少的存储空间（二级索引的B+树也比主键索引的B+树小）。

同样是遍历，二级索引的I/O成本明显小于主键索引，而我们都知道InnoDB之所以采用B+树作为索引的数据结构，很大程度就是为了减少将磁盘读到内存的I/O次数。

所以，

如果表中有二级索引时，【优化器】会优先遍历二级索引来计数，没有二级索引才会遍历主键索引

COUNT(非索引字段)呢？

那么如果这个参数字段不是索引的时候呢？

前面说了，参数里的字段名是主键索引时，考虑到遍历I/O成本，会优先走二级索引遍历计数，咱当时就有个疑问，如果字段不是索引呢？

答案很简单，就不会走索引了哈哈哈，explain一下，可以看到下面的type=ALL，就是全表扫描了，当然这样性能会受影响，所以没有特殊需求，不建议这么做

COUNT(1)

先说结果：与COUNT(*)类似，直接查询符合条件的行数

查询过程

• InnoDB 循环遍历聚簇索引（主键索引），但不会读取记录中的任何字段的值（因为1不是一个字段）
• server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1

与COUNT(主键字段)类似：

• 此时如果表中有二级索引会默认遍历二级索引计数，没有就遍历主键索引；
• 如果表中有多个二级索引，会选用**key_len最小的二级索引**进行遍历扫描，并计数

但不同的是，COUNT(1)少了【判断当前行参数字段是否为NULL】这一步，因为现在的参数是1，不可能是NULL，所以效率会高一点

COUNT(*)

先说结果：查询符合条件的行数

需要注意一点，这里的*跟select *不太一样，不是读取记录中所有字段，而是0

即使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理，最后执行的是COUNT(0)

查询过程

因此，count(*) 执行过程跟 count(1) 执行过程基本一样的，性能也差不多

常见面试问题

下面来盘一下相关的面试问题

三个查询的结果有什么不同？

• COUNT(字段名): 查询符合条件，并且字段名不为NULL的行数
• COUNT(*)和COUNT(1): 查询所有符合条件的行数

为什么不推荐使用COUNT(字段名)？

• 参数字段是索引，与COUNT(*)和COUNT(1)，会优先遍历key_len小的非聚簇索引。但，相比于COUNT(*)和COUNT(1)，COUNT(字段名)多了一步，即需要判断字段是否为NULL，效率会差一些；
• 如果参数字段不是索引，则会走全表搜索，效率更差了。

COUNT(1)和COUNT(*)之间有什么不同？效率哪个更高?

• 不同：二者都是查询所有符合条件的行数，不需要判断字段是否为NULL，区别是COUNT(*)等于把参数换成0，即最后执行的是COUNT(0)，因为0、1都不是NULL，所以查的是所有符合条件的行
• 效率：都差不多，但建议使用count(*)，因为这是SQL92定义的标准统计行数的语法

MySQL中，InnoDB和MyISAM对COUNT(*)有那些优化？

InnoDB与MyISAM的一些区别

先稍微回忆一下InnoDB与MyISAM的一些区别，比如：

• 事务与锁：
  • MyISAM不支持事务，MyISAM中的锁是表级锁；
  • 而InnoDB支持事务，支持表级锁与行级锁。
• 索引数据结构：
  • MyISAM支持多种索引数据结构，比如 B+ 树索引、R 树索引、Full-Text 索引，建表时默认的主键索引时B+树；
  • InnoDB索引结构只支持B+树索引；
• 索引中的叶子节点：
  • MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址；
  • InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身；

InnoDB与MyISAM对COUNT(*)的优化

优化的前提是查询语句中不包含where条件和group by条件

先说结论：

• MyISAM：会把表的总行数单独记录下来，如果执行count(*)时可以直接返回;
• InnoDB：会优先选择最小的非聚簇索引来扫表。

那么就有几个问题：

• 为什么InnoDB不像MyISAM那样存一个总行数？

  • 因为MyISAM是表级锁，不会有并发的行操作，所以查到的结果是准确的；而InnoDB支持事务，大部分事务都是行级锁（只有串行化才会上表级锁），行可能被并行修改，所以像MyISAM一样对总数做缓存不太安全

• 为什么MyISAM不想InnoDB那样优先选最小的非聚簇索引来计数？

  • 因为MyISAM索引树的叶子节点存的是一个数据的物理地址，查询时都需要回表，等于MyISAM建立的索引全都是二级索引

SELECT COUNT(*) 的时候，加不加where条件有差别吗？

这部分很多帖子都只在问题里罗列下，好像也没解答

其实就是跟InnoDB优先走二级索引有关，前面也提到了”优化的前提是查询语句中不包含where条件和group by条件“

稍微做个小实验:

-- actor_id 为主键索引，last_name 为二级索引，first_name不是索引
explain select count(*) from actor;
explain select count(*) from actor where actor_id > 3;
explain select count(*) from actor where first_name like 'P%';

结果如下：

可以看出：

加了where条件约束会使”优先遍历较小的二级索引计数“的优化失效，具体策略跟约束的字段有关：

• 如果where的条件是一个主键索引的约束，会走主键索引
• 如果where的条件不是索引，则会直接全表扫描

大表COUNT(*)时，如何优化？

当表中数据很庞大，即使各个引擎对COUNT(*)有优化，也很耗时，那么：

• 如果不需要很精确的计数，可以通过explain 或者 show table status查一个近似值
• 如果需要精确的计数，可以额外建个表存计数值（空间换时间，在给自己换点麻烦）

近似值

如果你的业务对于统计个数不需要很精确，可以使用 show table status 或者 explain 命令来表进行估算，执行 explain 命令效率是很高的，因为它并不会真正的去查询，结果中的rows只是一个估值

explain select count(*) from tb

（比如搜索引擎在搜索关键词的时候，给出的搜索结果条数是一个大概值）

额外建表保存计数值

字面意思，单独建张表存这个计数值，并在数据插入删除单独维护：

• 有新数据插入时，将计数表中的计数字段 + 1；

• 有数据删除时，将计数表中的计数字段 - 1

Reference

MyISAM中的索引方案
MySQL学习笔记：count(1)、count(*)、count（字段）的区别
count(*) 和 count(1) 有什么区别？哪个性能最好？