COUNT(*) vs COUNT(1) vs COUNT(pk):哪一个更好?(复制)

This question already has an answer here:

这个问题已经有了答案:

• Count(*) vs Count(1) - SQL Server 11 answers
• 计数(*)vs计数(1)- SQL Server 11答案

I often find these three variants:

我经常发现这三种变体:

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

As far as I can see, they all do the same thing, and I find myself using the three in my codebase. However, I don't like to do the same thing different ways. To which one should I stick? Is any one of them better than the two others?

就我所见，它们都做同样的事情，我发现我在代码基中使用了这三个。然而，我不喜欢用不同的方式做同样的事情。我应该坚持哪一个?他们之中有谁比另外两个更好吗?

6 个解决方案

#1

Bottom Line

Use either COUNT(field) or COUNT(*), and stick with it consistently, and if your database allows COUNT(tableHere) or COUNT(tableHere.*), use that.

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

In short, don't use COUNT(1) for anything. It's a one-trick pony, which rarely does what you want, and in those rare cases is equivalent to count(*)

简而言之，不要对任何事情使用COUNT(1)。这是一匹诡计多端的小马，它很少做你想做的事，在那些罕见的情况下相当于count(*)

Use count(*) for counting

Use * for all your queries that need to count everything, even for joins, use *

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

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

But don't use COUNT(*) for LEFT joins, as that will return 1 even if the subordinate table doesn't match anything from parent table

但是不要使用COUNT(*)作为左连接，因为它将返回1，即使从属表与父表不匹配。

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

Don't be fooled by those advising that when using * in COUNT, it fetches entire row from your table, saying that * is slow. The * on SELECT COUNT(*) and SELECT * has no bearing to each other, they are entirely different thing, they just share a common token, i.e. *.

不要被那些建议在COUNT中使用*时，它会从您的表中取出整行，并说*很慢的建议所迷惑。SELECT COUNT(*)和SELECT *互不相关，它们是完全不同的东西，它们只是共享一个共同的令牌，即*。

An alternate syntax

In fact, if it is not permitted to name a field as same as its table name, RDBMS language designer could give COUNT(tableNameHere) the same semantics as COUNT(*). Example:

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

For counting rows we could have this:

对于计算行，我们可以这样:

SELECT COUNT(emp) FROM emp

And they could make it simpler:

他们可以使它更简单:

SELECT COUNT() FROM emp

And for LEFT JOINs, we could have this:

对于左连接，我们可以这样:

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

But they cannot do that (COUNT(tableNameHere)) since SQL standard permits naming a field with the same name as its table name:

但是它们不能那样做(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
)

Counting with null

And also, it is not a good practice to make a field nullable if its name matches the table name. Say you have values 'Banana', 'Apple', NULL, 'Pears' on fruit field. This will not count all rows, it will only yield 3, not 4

而且，如果字段的名称与表名匹配，那么将字段设为nullable也不是一个好的实践。假设你有"香蕉"，"苹果"，零，"梨"在水果场上。这不会计算所有的行，它只会产生3，而不是4

SELECT count(fruit) FROM fruit

Though some RDBMS do that sort of principle (for counting the table's rows, it accepts table name as COUNT's parameter), this will work in Postgresql (if there is no subordinate field in any of the two tables below, i.e. as long as there is no name conflict between field name and table name):

尽管一些RDBMS的原则(表的行数,它接受表名作为计算的参数),这将在Postgresql工作(如果没有下属字段下面的两个表,即只要没有名称字段名之间的冲突和表名):

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

But that could cause confusion later if we will add a subordinate field in the table, as it will count the field(which could be nullable), not the table rows.

但是，如果我们在表中添加一个从属字段，这会引起混乱，因为它会计算字段(可以为nullable)，而不是表行。

So to be on the safe side, use:

为了安全起见，使用:

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

count(1): The one-trick pony

In particular to COUNT(1), it is a one-trick pony, it works well only on one table query:

特别是计数(1)，它是一个单技巧小马，它只适用于一个表查询:

SELECT COUNT(1) FROM tbl

But when you use joins, that trick won't work on multi-table queries without its semantics being confused, and in particular you cannot write:

但是，当您使用join时，如果不混淆多表查询的语义，那么这个技巧就不能工作，尤其是您不能编写:

-- 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

So what's the meaning of COUNT(1) here?

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

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

Is it this...?

这是…吗?

-- 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 this...?

或者…?

-- 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

By careful thought, you can infer that COUNT(1) is the same as COUNT(*), regardless of type of join. But for LEFT JOINs result, we cannot mold COUNT(1) to work as: COUNT(subordinate.boss_id), COUNT(subordinate.*)

通过仔细考虑，您可以推断COUNT(1)与COUNT(*)是相同的，而不考虑连接的类型。但是对于左连接结果，我们不能将COUNT(1)设置为:COUNT(从属于。boss_id)， COUNT(从属于。*)

So just use either of the following:

因此，只要使用下列任何一种:

-- 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

Works on Postgresql, it's clear that you want to count the cardinality of the set

在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

Another way to count the cardinality of the set, very English-like (just don't make a column with a name same as its table name) : http://www.sqlfiddle.com/#!1/98515/7

另一种计算集合基数的方法，非常像英语(只是不要让一个列的名称与其表名相同):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

You cannot do this: http://www.sqlfiddle.com/#!1/98515/8

您不能这样做: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

You can do this, but this produces wrong result: http://www.sqlfiddle.com/#!1/98515/9

您可以这样做，但是这会产生错误的结果:http://www.sqlfiddle.com/#!

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

#2

Two of them always produce the same answer:

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

• COUNT(*) counts the number of rows
• COUNT(*)计算行数
• COUNT(1) also counts the number of rows
• COUNT(1)也计算行数

Assuming the 'pk' is a primary key and that no nulls are allowed in the values, then

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

• COUNT(pk) also counts the number of rows
• COUNT(pk)也计算行数

However, if 'pk' is not constrained to be not null, then it produces a different answer:

但是，如果'pk'不被约束为非空，则会产生不同的答案:

• COUNT(possibly_null) counts the number of rows with non-null values in the column possibly_null.

  COUNT(possible bly_null)对可能为空的列中具有非空值的行数进行计数。

• COUNT(DISTINCT pk) also counts the number of rows (because a primary key does not allow duplicates).

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

• COUNT(DISTINCT possibly_null_or_dup) counts the number of distinct non-null values in the column possibly_null_or_dup.

  COUNT(惟一可能的null_or_dup)计算可能的列中的不同非空值的数量。

• COUNT(DISTINCT possibly_duplicated) counts the number of distinct (necessarily non-null) values in the column possibly_duplicated when that has the NOT NULL clause on it.

  COUNT(不同的可能性重复)计数在该列上有NOT NULL子句的情况下，列中不同的(不一定非NULL)值的数量。

Normally, I write COUNT(*); it is the original recommended notation for SQL. Similarly, with the EXISTS clause, I normally write WHERE EXISTS(SELECT * FROM ...) because that was the original recommend notation. There should be no benefit to the alternatives; the optimizer should see through the more obscure notations.

通常,我写COUNT(*);它是SQL的原始推荐符号。类似地，使用exist子句，我通常在存在的地方写(从…中选择*)，因为那是最初的推荐符号。替代方案不应带来任何好处;优化器应该看穿更模糊的符号。

#3

This will depend on the type of database you are using as well as the type of table in some cases.

这将取决于您正在使用的数据库的类型，以及在某些情况下表的类型。

For example, using MySQL, count(*) will be fast under a MyISAM table but slow under an InnoDB. Under InnoDB you should use count(1) or count(pk).

例如，使用MySQL, count(*)在MyISAM表下会很快，而在InnoDB下会很慢。在InnoDB中，您应该使用count(1)或count(pk)。

#4

At least on Oracle they are all the same: http://www.oracledba.co.uk/tips/count_speed.htm

至少在Oracle上是一样的:http://www.oracledba.co.uk/tips/count_spe.htm

#5

Asked and answered before...

问和回答之前……

Books on line says "COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )"

在线上的书写着“COUNT ({[[[[ALL |明显]表达式]| *})”

"1" is a non-null expression so it's the same as COUNT(*). The optimiser recognises it as trivial so gives the same plan. A PK is unique and non-null (in SQL Server at least) so COUNT(PK) = COUNT(*)

“1”是一个非空表达式，因此它与COUNT(*)相同。乐观主义者认为这是微不足道的，所以给出了同样的计划。PK是唯一且非空的(至少在SQL Server中)，所以COUNT(PK) = COUNT(*)

This is a similar myth to EXISTS (SELECT * ... or EXISTS (SELECT 1 ...

这是一个类似的神话(选择*……)或存在(选择1……)

And see the ANSI 92 spec, section 6.5, General Rules, case 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.

#6

I feel the performance charecteristics changes from DBMS to DBMS. Its all on how they choose to implment it. Since I have worked extensively on oracle, Ill tell from that perspective.

我觉得性能特点从DBMS到DBMS都在变化。这完全取决于他们如何选择去恳求它。既然我在oracle上做了大量的工作，我就从这个角度来说吧。

COUNT(*) - Fetches entire row into result set befor passing on to the count function, count function will aggregate 1 if the row is not null

COUNT(*) -将整个行读取到结果集befor传递到COUNT函数，如果行不为空，COUNT函数将聚合1。

COUNT(1) - Will not fetch any row, instead count is called with a constant value 1 for each row in the table when the where matches.

COUNT(1) -将不获取任何行，而是在where匹配时，为表中的每一行调用COUNT，其值为1。

Count(PK) - PK's in oracle is indexed. This means Oracle has to read only the index. Normally one row in Index B+ Tree is many times smaller than the actual row. So considering the disk IOPS rate, Oracle can fetch many times more rows from Index with a single block transfer as compared to entire row. This leads to higher througput of the query.

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

From this you can see the first count is the slowest and the last count is the fastest in Oracle.

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