译：MySQL counting-rows、function_count

前言

最近做项目，发现代码中对于计算行数的时候出现count(*)，count(1)，count(id)之类的，实际上笔者以前看过类似的博客文章，但是具体是什么原因，并没有具体的了解，刚好遇到了，当前主流的数据库是MySQL，查询MySQL官方文档，发现了其中的缘由。总结分析，干脆就翻译一下。

counting-rows

以MySQL的8.4为例，当然可以切换其他版本：counting-rows：

数据库通常用于回答这样的问题：“某种确定的类型的数据在表中出现的频率是多少？”例如，你可能想知道你有多少宠物，或者每个主人有多少宠物，或者你可能想对你的动物进行各种类型的宠物普查操作。

计算你拥有动物的总数与“pet宠物表中有多少行？”是同一个问题，因为每只宠物对应一条记录。COUNT(*) 计算行数，所以统计你动物的查询如下所示：

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

之前，你检索了拥有宠物的人的姓名（指的MySQL文档的select查询语句）。如果你想知道每个主人有多少宠物，可以使用 COUNT()：

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

前面的查询使用 GROUP BY 对每个所有者的所有记录进行分组。将 COUNT() 与 GROUP BY 结合使用对于在不同分组下描述您的数据非常有用。以下示例显示了执行宠物普查操作的不同方式。

每个物种的动物数量：

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------+----------+

各种性别的宠物数量：

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+

（在此输出中，NULL表示性别未知。）

每种物种和性别组合的动物数量：

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | NULL |        1 |
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

使用 COUNT() 时，你不需要检索整张表。例如，仅对狗和猫做条件查询时，先执行查询条件，看起来是这样的：

mysql> SELECT species, sex, COUNT(*) FROM pet
       WHERE species = 'dog' OR species = 'cat'
       GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+

或者，如果你只想要已知性别动物的每个性别的数量：

mysql> SELECT species, sex, COUNT(*) FROM pet
       WHERE sex IS NOT NULL
       GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

如果除了 COUNT() 值之外还要查询列数据，则需要使用GROUP BY 使用相同的列。否则，将会发生以下情况：

如果启用了 ONLY_FULL_GROUP_BY SQL 模式，会发生错误：

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'menagerie.pet.owner';
this is incompatible with sql_mode=only_full_group_by

如果没有启用 ONLY_FULL_GROUP_BY，则查询会将所有行视为单个组进行处理，但为每个列选择的值是不确定的。服务器可以自由地从任何行中选择值：

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Harold |        8 |
+--------+----------+
1 row in set (0.00 sec)

也可以跳转 Section 14.19.3, “MySQL Handling of GROUP BY”. 看Section 14.19.1, “Aggregate Function Descriptions” 文档信息关于 COUNT(expr) 行为及相关优化。

其实是讲如何使用count(*)进行数据的统计，并没有说明count(*) count(1) count(column)的说明。

cout函数

上面说跳转到count()函数的说明：function_count

COUNT(expr) [over_clause]

返回 SELECT 语句检索的行中 expr 的非 NULL 值的数量。结果是一个 BIGINT 值。

如果没有匹配的行，COUNT() 返回 0。COUNT(NULL) 也返回 0。

如果存在 over_clause，此函数将作为窗口函数执行。over_clause 文档信息：Section 14.20.2, “Window Function Concepts and Syntax”.

mysql> SELECT student.student_name,COUNT(*)
       FROM student,course
       WHERE student.student_id=course.student_id
       GROUP BY student_name;

COUNT(*) 有些不同，它返回检索到的行数，无论这些行是否包含 NULL 值。

对于像 InnoDB 这样的事务型存储引擎，存储准确的行数是个难题。可能会有多个事务同时进行，每个事务都可能影响计数。

InnoDB 不会在表中维护内部行数计数，因为并发事务可能会在同一时间“看到”不同数量的行。因此，SELECT COUNT(*) 语句只会统计当前事务可见的行数。

如果没有额外的子句，例如 WHERE 或 GROUP BY，对于 InnoDB 表，SELECT COUNT(*) FROM tbl_name 查询性能已针对单线程工作负载进行了优化。

InnoDB 处理 SELECT COUNT(*) 语句时，会遍历最小的可用二级辅助索引，除非索引或优化器强制hint指示优化器使用其他索引。如果不存在二级辅助索引，InnoDB 会通过扫描聚簇索引来处理 SELECT COUNT(*) 语句。

如果索引记录没有完全在缓冲池中，处理 SELECT COUNT(*) 语句会花费一些时间。为了更快地计数，可以创建一个计数表，并让应用程序根据它所执行的插入和删除操作来更新它。不过，在有成千上万的并发事务同时对同一个计数表进行更新的情况下，这种方法可能无法很好地扩展。如果只需要大致的行数，可以使用 SHOW TABLE STATUS。

InnoDB 处理 SELECT COUNT(*) 和 SELECT COUNT(1) 操作的方式相同。没有性能差异。

对于 MyISAM 表，如果 SELECT 从一张表中检索、不检索其他列且没有 WHERE 子句，COUNT(*) 会被优化以非常快速地返回。例如：

mysql> SELECT COUNT(*) FROM student;

此优化仅适用于 MyISAM 表，因为这种存储引擎会存储精确的行数，并且可以非常快速地访问。只有在第一列被定义为 NOT NULL 时，COUNT(1) 才会受到相同的优化。

其实这里MySQL的官方文档已经明确说明了count(*) count(1) count(expr)的区别，并且说了其中的引擎和对应的优化。

总结是计算innodb表的全表数据：count(*)=count(1) > count(column)

顺便翻译count(DISTINCT expr)

COUNT(DISTINCT expr,[expr...])

返回具有不同非 NULL expr 列的值的行数。

如果没有匹配的行，COUNT(DISTINCT) 返回 0。

mysql> SELECT COUNT(DISTINCT results) FROM student;

在 MySQL 中，你可以通过giving a list 表达式来获取不包含 NULL 的distinct表达式组合的数量。在标准 SQL 中，你需要将所有表达式在 COUNT(DISTINCT ...) 中进行连接。

总结

通过阅读MySQL的官方文档，明白了count()函数的用途，MySQL在innodb存储引擎下，count(*)=count(1) > count(column) ，在特殊时候，比如二级索引就是这个列的时候count(*)=count(column) ，以及，count(*)是怎么优化这个性能的.

如果是MyISAM引擎，则直接存储了表的行数，全表行数效率最高。