JAVA面试题分享二百一十：为什么禁止使用 count( 列名 ) 或 count( 常量 )来替代 count(*) ？

目录

引言

认识COUNT

COUNT(列名) vs COUNT(*) vs COUNT(常量)

优化背后的COUNT(*)

MyISAM引擎

InnoDB引擎

COUNT(*)与COUNT(1)的性能比较

结论

---

引言

在数据库操作的世界里，增删改查（CRUD）构成了程序员日常工作的核心。特别是查询（Retrieve）操作，在所有的数据库活动中它可能是最频繁的。而在众多查询操作中，行数统计无疑占据了重要的地位。对于数据库行数的统计，我们通常依赖一个强大的SQL函数：COUNT。

认识COUNT

COUNT在数据库中的定义是用来统计行数的，具体来说，它有几种不同的使用方式：

1. COUNT(expr)：返回SELECT语句检索到的行中，表达式expr具有非NULL值的行数，返回结果为BIGINT类型。

2. 当没有符合条件的行时，COUNT返回0。

3. 而COUNT(*)则包括了所有的行，即使列值为NULL。

例如，考虑以下表格和数据：

CREATE TABLE tbl_example (id INT, id2 INT);
INSERT INTO tbl_example VALUES (NULL, NULL);
INSERT INTO tbl_example VALUES (1, NULL);
INSERT INTO tbl_example VALUES (NULL, 1);
INSERT INTO tbl_example VALUES (NULL, 1);
INSERT INTO tbl_example VALUES (NULL, 1);
INSERT INTO tbl_example VALUES (NULL, 1);
INSERT INTO tbl_example VALUES (NULL, 1);
INSERT INTO tbl_example VALUES (1,NULL);
...

如果我们分别使用COUNT(*)、COUNT(id)和COUNT(id2)来查询，将会得到不同的结果：

SELECT COUNT(*), COUNT(id), COUNT(id2) FROM tbl_example;

结果会是：

8, 2, 5

COUNT(id)和COUNT(id2)只统计了具有非NULL值的行，而COUNT(*)统计了所有行。

COUNT(列名) vs COUNT(*) vs COUNT(常量)

那么，为什么《Java 开发手册》等许多最佳实践推荐避免使用COUNT(列名)或COUNT(常量)来替代COUNT(*)呢？

• COUNT(列名)：统计特定列非NULL值的行数，这在你只关注特定列时有用，但如果你的目标是统计表中的行数，这可能会导致误导。

• COUNT(常量)：尽管COUNT(1)（常量为1）在结果上与COUNT(*)相同，但它并不是标准，可能会引起混淆。

• COUNT(*)：是标准的SQL用法，被定义用于统计行数，大多数数据库系统对此进行了优化，从而提供了更高的效率。

优化背后的COUNT(*)

不同的数据库引擎对COUNT(*)有不同的优化策略。

MyISAM引擎

MyISAM存储引擎不支持事务，使用表级锁，因此它能够在没有WHERE条件的情况下直接返回表中的总行数，这是因为整个表被锁定，行数是一个固定的值。

InnoDB引擎

相比之下，InnoDB支持事务，并且使用行级锁。因此，它不能像MyISAM那样优化COUNT(*)操作。但是，InnoDB对COUNT(*)做了其他类型的优化。从MySQL 8.0.13开始，针对InnoDB的SELECT COUNT(*) FROM tbl_name，如果查询中没有WHERE或GROUP BY等条件，会尝试使用成本较低的索引进行表扫描，这通常意味着使用非聚簇索引，因为它们的大小通常小于聚簇索引，从而可以更快地统计行数。

COUNT(*)与COUNT(1)的性能比较

关于COUNT(*)和COUNT(1)的性能，有很多争论。然而，根据MySQL官方文档，InnoDB对SELECT COUNT(*)和SELECT COUNT(1)操作的处理方式是相同的，不存在性能差异。

因此，建议使用COUNT(*)，因为它是SQL92标准定义的用于统计行数的语法，这使得COUNT(*)在各种数据库系统中得到了优化。

结论

在数据库查询中，COUNT(*)是统计表行数的推荐方式。它不仅符合SQL92标准，而且得益于数据库系统的优化，从而提供了更高的效率。因此，除非有特定的列值统计需求，否则应该直接使用COUNT(*)来查询表的行数。这不仅是为了提高查询效率，还为了避免因列值为NULL而引起的统计不准确的问题。所以，请在编写SQL查询时，优先考虑COUNT(*)。