COUNT(*) vs. COUNT(1) vs. COUNT(pk):哪个更好?

我经常发现以下三种变体:

SELECT COUNT(*) FROM Foo;
SELECT COUNT(1) FROM Foo;
SELECT COUNT(PrimaryKey) FROM Foo;

据我所知，它们都做同样的事情，我发现自己在代码库中使用了这三个。然而，我不喜欢用不同的方法做同一件事。我应该坚持哪一个?他们中有谁比另外两个好吗?

141691 次浏览
小开

至少在Oracle上它们都是相同的:http://www.oracledba.co.uk/tips/count_speed.htm

小开

其中两个总是得到相同的答案:

  • COUNT(*)计算行数
  • COUNT(1)也计算行数

假设pk是一个主键，并且值中不允许有空值，则

  • COUNT(pk)也计算行数

然而，如果pk没有被约束为非空，那么它会产生一个不同的答案:

  • COUNT(possibly_null)计算列possibly_null中具有非空值的行数。

  • COUNT(DISTINCT pk)也计算行数(因为主键不允许重复)。

  • COUNT(DISTINCT possibly_null_or_dup)计算列possibly_null_or_dup中不同的非空值的数量。

  • 当列possibly_duplicated上有NOT NULL子句时，COUNT(DISTINCT possibly_duplicated)计数列possibly_duplicated中不同(必然非空)值的数量。

通常，我写COUNT(*);它是SQL最初推荐的符号。类似地，对于EXISTS子句，我通常写WHERE EXISTS(SELECT * FROM ...)，因为这是最初的推荐符号。替代方案应该没有好处;优化器应该能看穿更模糊的符号。

小开
最佳答案

底线

使用COUNT(field)或COUNT(*)，并始终坚持使用它，如果你的数据库允许COUNT(tableHere)或COUNT(tableHere.*)，就使用它。

简而言之，不要将COUNT(1)用于任何事情。它是一个一招的小马，很少做你想要的，在这些罕见的情况下相当于count(*)

使用count(*)进行计数

对于所有需要计算所有内容的查询，使用*，即使对于连接，也使用*

SELECT boss.boss_id, COUNT(subordinate.*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

但是不要对左连接使用COUNT(*)，因为即使从属表不匹配父表中的任何内容，它也会返回1

SELECT boss.boss_id, COUNT(*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

不要被那些建议愚弄，当在COUNT中使用*时，它会从你的表中获取整行，说*很慢。SELECT COUNT(*)上的*和SELECT *上的*彼此没有关系，它们是完全不同的东西，它们只是共享一个共同的令牌，即*。

另一种语法

事实上，如果不允许将字段命名为与其表名相同的字段，RDBMS语言设计器可以给COUNT(tableNameHere)与COUNT(*)相同的语义。例子:

为了计算行数，我们可以这样做:

SELECT COUNT(emp) FROM emp

他们可以让事情变得更简单:

SELECT COUNT() FROM emp

对于LEFT join，我们可以这样写:

SELECT boss.boss_id, COUNT(subordinate)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

但是他们不能这样做(COUNT(tableNameHere))，因为SQL标准允许用与其表名相同的名称命名字段:

CREATE TABLE fruit -- ORM-friendly name
(
fruit_id int NOT NULL,
fruit varchar(50), /* same name as table name,
and let's say, someone forgot to put NOT NULL */
shape varchar(50) NOT NULL,
color varchar(50) NOT NULL
)

用空计数

而且，如果字段的名称与表名匹配，那么使字段为空也不是一个好的做法。假设你在fruit字段上有值'Banana'， 'Apple'， NULL， 'Pears'。这不会计算所有行，它只会产生3行，而不是4行

SELECT count(fruit) FROM fruit

虽然一些RDBMS这样做的原则(计数表的行，它接受表名作为COUNT的参数)，这将在Postgresql中工作(如果在以下两个表中任何一个没有subordinate字段，即字段名和表名之间没有名称冲突):

SELECT boss.boss_id, COUNT(subordinate)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

但是，如果我们稍后在表中添加subordinate字段，可能会导致混乱，因为它将计算字段(可以为空)，而不是表行。

所以为了安全起见，使用:

SELECT boss.boss_id, COUNT(subordinate.*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

count(1):一招小马

特别是COUNT(1)，它是一个“一招鲜吃遍天”的，它只在一个表查询上工作得很好:

SELECT COUNT(1) FROM tbl

但是当你使用连接时，这个技巧在多表查询时不会产生语义混乱，特别是你不能写:

-- count the subordinates that belongs to boss
SELECT boss.boss_id, COUNT(subordinate.1)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

那么COUNT(1)在这里是什么意思呢?

SELECT boss.boss_id, COUNT(1)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

是这个吗?

-- counting all the subordinates only
SELECT boss.boss_id, COUNT(subordinate.boss_id)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

或者…?

-- or is that COUNT(1) will also count 1 for boss regardless if boss has a subordinate
SELECT boss.boss_id, COUNT(*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

通过仔细思考，你可以推断出COUNT(1)与COUNT(*)是相同的，无论连接类型是什么。但是对于LEFT JOINs结果，我们不能将COUNT(1)塑造为:COUNT(subordinate.boss_id)， COUNT(subordinate.*)

所以你可以使用以下任何一种方法:

-- count the subordinates that belongs to boss
SELECT boss.boss_id, COUNT(subordinate.boss_id)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

适用于Postgresql，很明显你想要计算集合的基数

-- count the subordinates that belongs to boss
SELECT boss.boss_id, COUNT(subordinate.*)
FROM boss
LEFT JOIN subordinate on subordinate.boss_id = boss.boss_id
GROUP BY boss.id

另一种计算集合基数的方法，非常像英语(只是不要让列的名称与其表名相同):http://www.sqlfiddle.com/ !1/98515/7

select boss.boss_name, count(subordinate)
from boss
left join subordinate on subordinate.boss_code = boss.boss_code
group by boss.boss_name

你不能这样做:http://www.sqlfiddle.com/ !1/98515/8

select boss.boss_name, count(subordinate.1)
from boss
left join subordinate on subordinate.boss_code = boss.boss_code
group by boss.boss_name

你可以这样做，但这会产生错误的结果:http://www.sqlfiddle.com/ !1/98515/9

select boss.boss_name, count(1)
from boss
left join subordinate on subordinate.boss_code = boss.boss_code
group by boss.boss_name
小开

我感觉不同DBMS的性能特征是不同的。这完全取决于他们选择如何实施它。因为我在甲骨文工作过很长时间，所以我将从这个角度来讲述。

COUNT(*) -在传递给count函数之前，将整个行提取到结果集中，如果行不为空，count函数将聚合1

COUNT(1) -不获取任何行，相反，当WHERE匹配时，对表中的每一行调用常量值1的count。

COUNT(PK) - Oracle中的PK被索引。这意味着Oracle只能读取索引。通常，索引B+树中的一行比实际行小很多倍。因此，考虑到磁盘IOPS速率，Oracle通过单个块传输可以从Index中获取比整行多很多倍的行。这将导致更高的查询吞吐量。

从这里你可以看到，在Oracle中，第一次计数是最慢的，而最后一次计数是最快的。

小开

之前的提问和回答

网上书籍表示“COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )”

"1"是一个非空表达式，因此它与COUNT(*)相同。 优化器将其识别为< em >的< / em >，因此给出相同的计划。一个PK是唯一且非空的(至少在SQL Server中)，因此COUNT(PK) = COUNT(*)

这与EXISTS (SELECT * ...或EXISTS (SELECT 1 ...类似

并参见ANSI 92规范，第6.5节，一般规则，情况1

        a) If COUNT(*) is specified, then the result is the cardinality
of T.


b) Otherwise, let TX be the single-column table that is the
result of applying the <value expression> to each row of T
and eliminating null values. If one or more null values are
eliminated, then a completion condition is raised: warning-
null value eliminated in set function.

提交答案