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

Hansdas

已于 2024-03-30 14:44:24 修改

阅读量2k

点赞数 32

分类专栏： MySQL 文章标签： mysql 笔记 java

于 2024-03-30 14:04:10 首次发布

本文链接：https://blog.csdn.net/Hansdas/article/details/137172051

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

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

稍微介绍下`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%';