MySQL COUNT(*) COUNT(1) 与 COUNT(列) 的区别
MySQL COUNT(*) COUNT(1) 与 COUNT(列) 的区别
文章目录
- 1.COUNT()
- 2.COUNT(*) COUNT(1) 与 COUNT(列) 的功能？
- 3. 统计表行数性能区别
- 3.1 COUNT(主键) 的执行过程？
- 3.2 COUNT(1) 的执行过程？
- 3.3 COUNT(*) 的执行过程？
- 3.4 COUNT(字段) 的执行过程？
- 3.5 小结
- 4.为什么要通过遍历的方式来计数？
- 5.如何优化 COUNT(*)？
- 第一种：近似值
- 第二种：额外表保存表记录数
- 参考文献
1.COUNT()
COUNT() 是一个统计记录数的聚合函数，语法如下：
COUNT(expr) [over_clause]函数的参数 expr 不仅可以是列名，也可以是其他任意表达式。
over_clause 表示 COUNT 以窗口函数工作，MySQL 8.0 开始支持，这个不在本文展开，感兴趣的同学请参考 Section 14.20.2, “Window Function Concepts and Syntax”。
COUNT() 函数作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。
对于 COUNT 的使用，常见的使用方式是：
- COUNT(*)
- COUNT(1)
- COUNT(列)
三者在功能和性能上有区别吗？且听我一一道来。
2.COUNT(*) COUNT(1) 与 COUNT(列) 的功能？
COUNT(*) 返回结果集中所有记录数，包含字段为 NULL 的记录。
COUNT(1) 功能上等同于 COUNT(*)。
COUNT(列名) 返回结果集中指定字段不为 NULL 的记录数。
3. 统计表行数性能区别
统计表的总记录数下面这 4 种做法：
- COUNT(*)
- COUNT(1)
- COUNT(主键)
- COUNT(字段)
关于四者的性能，基于常用的 InnoDB 存储引擎来说明。
先说结论：
COUNT(*) = COUNT(1) > COUNT(主键) > COUNT(字段)
要弄明白这个，我们得要深入 COUNT 的实现原理。
在通过 COUNT 函数统计有多少条记录时，MySQL 的 server 层会维护一个名叫 count 的变量。
server 层会循环向 InnoDB 读取一条记录，如果 COUNT 函数指定的参数不为 NULL，那么就会将变量 count 加 1，直到符合查询的全部记录被读完，就退出循环。最后将 count 变量的值发送给客户端。
3.1 COUNT(主键) 的执行过程？
InnoDB 是通过 B+ 树来保存记录的，根据索引的类型又分为聚簇索引和二级索引，它们区别在于，聚簇索引的叶子节点存放的是实际数据，而二级索引的叶子节点存放的是主键值，而不是实际数据。
用下面这条语句作为例子：
// id 为主键值
SELECT COUNT(id) FROM t_order;如果表里只有主键索引，没有二级索引时，那么，InnoDB 循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，就会 id 值判断是否为 NULL，如果不为 NULL（主键不能为 NULL），就将 count 变量加 1。
但是，如果表里有二级索引时，InnoDB 循环遍历的对象就不是聚簇索引，而是二级索引。
这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。
3.2 COUNT(1) 的执行过程？
用下面这条语句作为例子：
SELECT COUNT(1) FROM t_order;如果表里只有主键索引，没有二级索引时。
那么，InnoDB 循环遍历聚簇索引（主键索引），将读取到的记录返回给 server 层，但是不会读取记录中的任何字段的值，因为 COUNT 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1。
可以看到，COUNT(1) 相比 COUNT(主键) 少一个步骤，就是不需要读取记录中的字段值，所以通常会说 COUNT(1) 执行效率会比 COUNT(主键) 高一点。
但是，如果表里有二级索引时，InnoDB 循环遍历的对象就二级索引了。
3.3 COUNT(*) 的执行过程？
看到 * 这个字符的时候，是不是大家觉得是读取记录中的所有字段值？
对于 SELECT * 这条语句来说是这个意思，但是在 COUNT(*) 中并不是这个意思。
COUNT(*) 其实等于 COUNT(0)，也就是说，当你使用 COUNT(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。
所以，COUNT(*) 执行过程跟 COUNT(1) 执行过程基本一样，性能没有什么差异。
在 MySQL 8.0 的官方手册中有这么一句话：
InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.
意思是 InnoDB 以相同的方式处理 SELECT COUNT(*) 和 SELECT COUNT(1) 操作。没有性能差异。
3.4 COUNT(字段) 的执行过程？
COUNT(字段) 的执行效率相比前面的 COUNT(1)、 COUNT(*)、 COUNT(主键) 执行效率是最差的。
用下面这条语句作为例子：
-- name 是普通字段不是索引字段
SELECT COUNT(name) FROM t_order;对于这个查询来说，会采用全表扫描的方式来计数，所以它的执行效率是比较差的。
3.5 小结
COUNT(1)、 COUNT(*)、 COUNT(主键) 在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。
所以，如果要执行 COUNT(1)、 COUNT(*)、 COUNT(主键) 时，尽量在数据表上建立二级索引，这样优化器会自动采用 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。
再来，就是不要使用 COUNT(字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式来统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给这个字段建立一个二级索引。
4.为什么要通过遍历的方式来计数？
你可能会好奇，为什么 COUNT 函数需要通过遍历的方式来统计记录个数？
我前面将的案例都是基于 Innodb 存储引擎来说明的，但是在 MyISAM 存储引擎里，执行 COUNT 函数的方式是不一样的，通常在没有任何查询条件下的 COUNT(*)，MyISAM 的查询速度要明显快于 InnoDB。
使用 MyISAM 引擎时，执行 COUNT 函数只需要 O(1 )复杂度，这是因为每张 MyISAM 的数据表都有一个 meta 信息存储了 row_count 值，由表级锁保证一致性，所以直接读取 row_count 值就是 COUNT 函数的执行结果。
而 InnoDB 存储引擎是支持事务的，同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的，所以无法像 MyISAM一样，只维护一个 row_count 变量。
举个例子，假设表 t_order 有 100 条记录，现在有两个会话并行以下语句：
在会话 A 和会话 B的最后一个时刻，同时查表 t_order 的记录总个数，可以发现，显示的结果是不一样的。所以，在使用 InnoDB 存储引擎时，就需要扫描表来统计具体的记录。
而当带上 WHERE 条件语句之后，MyISAM 跟 InnoDB 就没有区别了，它们都需要扫描表来进行记录个数的统计。
5.如何优化 COUNT(*)？
如果对一张大表经常用 COUNT(*) 来统计表行数，其实是很不好的。
比如下面我这个案例，表 t_order 共有 1200+ 万条记录，我也创建了二级索引，但是执行一次 select count(*) from t_order 要花费差不多 5 秒！
面对大表的记录数统计，我们有没有什么其他更好的办法呢？
第一种：近似值
如果你的业务对于统计个数不需要很精确，比如搜索引擎在搜索关键词的时候，给出的搜索结果条数是一个大概值。
这时，我们就可以使用 show table status 或者 explain 命令来表进行估算。
执行 explain 命令效率是很高的，因为它并不会真正的去查询，下图中的 rows 字段值就是 explain 命令对表 t_order 记录的估算值。
第二种：额外表保存表记录数
如果是想精确的获取表的记录总数，我们可以将这个计数值保存到单独的一张计数表中。
当我们在数据表插入一条记录的同时，将计数表中的计数字段 + 1。也就是说，在新增和删除操作时，我们需要额外维护这个计数表。
参考文献
MySQL 8.0 Reference Manual :: 5.3.4.8 Counting Rows 14.19.1 Aggregate Function Descriptions - mysql.com count(*) 和count(1) 有什么区别？哪个性能最好？- 小林coding