JAVA面试题分享五百三十二：count(*) count(id) count(1)

目录

count(参数)函数解释

count()各种用法对比

效率对比

为什么innodb中没有把表记录总数存储起来？

MySQL如何对count(*) 操作的优化的？

show table status输出的总行数TABLE_ROWS 能替代count(*)吗？

`COUNT(*)`、`COUNT(id)` 和 `COUNT(1)` 的原理

---

count(参数)函数解释

count() 是一个聚合函数，对于返回的结果集，一行一行的判断，函数参数不是NULL时累计值就加1，否则不加，最后返回累计值。
没有过滤条件时，myisam中把一个表的总数存在磁盘上，执行count(*)直接返回这个数，效率很高。innodb中需要把数据一行一行的从引擎中读出来，然后累计。

count()各种用法对比

根据函数解释可以知道，count(*),count(1),count(主键id)都表示满足条件的总行数，而count(字段)表示满足条件的总行数里面参数 “字段” 不为NULL的总个数。

• count(主键id)
  innodb会遍历整张表，把每一行的id取出来，返回给server层，server层拿到id后，判断不可能为NULL，就按行累加。

• count(1)
  innodb会遍历整张表，但不取值，server层对于返回的每一行，放一个数字1进去，判断不可能为NULL，按行累加。

• count(字段)

1. 如果字段定义为not null,一行行的从记录中读出这个字段，判断不可能为空，按行累加。
2. 如果字段定义允许为null， 执行时需要先把值取出来进行判断，判断不是null才累加。

• count(*)
  不会把字段全部取出来，而是专门做了优化，不取值，因为肯定不是null，直接按行累加。

操作	是否取值	是否判断	备注
count(*)	否	否	最快
count(1)	否	是
count(主键id)	是	是	可能使用最小的索引树
count(字段)	是	是	字段上无索引时，只能选主键索引

`COUNT(*)`、`COUNT(id)` 和 `COUNT(1)` 是用于计算行数的 SQL 聚合函数，它们在某些方面有一些区别。

- `COUNT(*)`：`COUNT(*)` 是一种特殊的语法，它返回结果集中的行数，不考虑任何列的值。它会将表中的每一行都计数，包括含有NULL值的行。因此，即使列中包含NULL值，它也会将其计入计数。使用 `COUNT(*)` 时 会扫描整个表，这可能导致性能下降。例如：

```sql

SELECT COUNT(*) FROM table_name;

```

- `COUNT(column)`：`COUNT(column)` 指定了要计数的具体列，它仅计算指定列中的非空值的行数。这意味着只有当该列不为NULL时，才会对它进行计数。使用 `COUNT(column)` 会忽略该列中的NULL值，因此在有NULL值的列上使用这个函数可以更准确地计算行数。例如：

```sql

SELECT COUNT(column) FROM table_name;

```

- `COUNT(1)`：`COUNT(1)` 是一种常用的优化写法。它计算的是任意列上的非空行数，因为在这种情况下，计算的列没关系。这意味着它会忽略所有列中的NULL值，只计算非空行的数目。使用 `COUNT(1)` 时会比使用 `COUNT(*)` 效率更高，因为它不需要扫描整个表的所有列。例如：

```sql

SELECT COUNT(1) FROM table_name;

```

上述提到的三种写法在一般情况下都可以达到相同的结果，只是在对待NULL值和对性能的考虑上略有不同。你可以根据具体场景和需求选择使用哪种写法。

效率对比

count(字段)< count(主键id) < count(1)≈count(*)

为什么innodb中没有把表记录总数存储起来？

因为即使同一时刻的多个查询，多版本并发控制（MVCC）的原因，表中总记录数是不确定的，各个事务中得到的结果可能也不一样。

比如在可重复读的隔离级别下，每一行记录要判断这个记录对这个会话是否可见，因此对count（*）请求来说，innodb只好把数据一行一行读出并做出判断，可见的行才能用于当前查询。

MySQL如何对count(*) 操作的优化的？

由于innodb中主键索引树的叶子节点是数据，普通索引树的叶子节点是主键值，因此普通索引比主键索引小很多。对于conut(*)遍历那个索引树得到的结果逻辑上都是一样的，因此MySQL优化器会找到最小的那个树去遍历。

在保证逻辑正确的前提下， 尽量减少数据的扫描，是数据库设计的通用法则之一。

show table status输出的总行数TABLE_ROWS 能替代count(*)吗？

不能。TABLE_ROWS是通过采样估算出来的，因此这个值很不准确。官方文档描述误差可达到40%-50%

`COUNT(*)`、`COUNT(id)` 和 `COUNT(1)` 的原理

- `COUNT(*)`：`COUNT(*)` 是一种特殊的语法，它会扫描整个表的所有行，不考虑任何列的值。由于它不需要对具体的列进行计算，因此在执行时可以更快地得到结果。它会计算结果集中的行数，包括含有NULL值的行。

- `COUNT(column)`：`COUNT(column)` 是对指定的列进行计数。它只计算指定列中非空值的行数，也就是说该列不为NULL的行才会被计数。在执行时，它会遍历该列的每一行，检查是否为NULL，然后累计计数。

- `COUNT(1)`：`COUNT(1)` 是一种常用的优化写法，它不考虑任何具体的列，只关注行的存在与否。由于在执行时并不需要读取具体的列值，所以使用 `COUNT(1)` 可以更快地得到结果。它会忽略所有列中的NULL值，只计算非空行的数目。

这些函数的目标都是计算行数，只是在对待NULL值和对性能的考虑上有所不同。`COUNT(*)` 不管是不是 NULL 都计入，而 `COUNT(column)` 和 `COUNT(1)` 均只计入非 NULL 行。

count(*)是例外,并不会把全部字段取出来,而是专门做了优化,不取值。count(*) 肯定不是null ,按行累加。所以这里推荐使用count(*)

《阿里巴巴Java开发手册》有如下要求：

执行效果如下：

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL

count(列名)只包括列名那一列，即某个列名值为NULL时，不统计。所以说不推荐，这里统计是数量可能会少一部分

效率如下：

字段为主键，count(字段)会比count(1)快
字段不为主键，count(1)会比count(字段)快
如果表多个列并且没有主键，则 count（1） 的执行效率优于 count（*），（一般表都会有主键，所以可以忽略这个比较）
如果有主键，则 select count（主键）的执行效率是最优的，
如果表只有一个字段，则 select count（*）最优。

2万数据经过测试如下： count（*）、count（1）、count(id)、count(字段非主键) 、count(字段主键)差别不大

count(1)

 

count(字段非主键)

count(字段主键)

count（*）

270万数据经过测试如下： count（*）、count（1）、count(id)、count(字段非主键) 、count(字段主键) 差别任然不是很大

420万数据经过测试如下： count（*）、count（1）、count(id)、count(字段非主键) 、count(字段主键) 差别任然不到1秒

1000万数据经过测试如下： count（*）、count（1）、count(id)、count(字段非主键) 、count(字段主键) 稍微有点差别