MYSQL count(*) 和 count(1) 哪个更好?

2021-11-20 00:00:00 performance sql mysql

相关(SQL Server): Count(*) vs Count(1)

你能告诉我什么是更好的性能(MySQL)吗?计数(*)还是计数(1)?

Could you please tell me what is better in performance (MySQL)? Count(*) or count(1)?

推荐答案

这是 MySQL 的答案.

This is a MySQL answer.

它们的执行完全相同 - 除非您使用的是 MyISAM，否则存在 COUNT(*) 的特殊情况.无论如何，我总是使用 COUNT(*).

They perform exactly the same - unless you are using MyISAM, then a special case for COUNT(*) exists. I always use COUNT(*) anyway.

https://dev.mysql.com/doc/refman/5.6/en/aggregate-functions.html#function_count

对于 MyISAM 表，COUNT(*) 被优化为快速返回，如果SELECT 从一张表中检索，不检索其他列，并且没有 WHERE 子句.例如:

For MyISAM tables, COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause. For example:

mysql> SELECT COUNT(*) FROM student;

此优化仅适用于MyISAM表，因为为此存储引擎存储了精确的行数并且可以非常快速地访问.COUNT(1) 只受相同如果第一列定义为NOT NULL，则优化.

This optimization only applies to MyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly. COUNT(1) is only subject to the same optimization if the first column is defined as NOT NULL.

<小时>###编辑你们中的一些人可能错过了幽默的黑暗尝试.我更愿意将此作为一个非重复的问题，以便 MySQL 对 SQL Server 做一些不同的事情.所以我投票重新提出这个问题(答案显然是错误的).

上述MyISAM优化同样适用于

###EDIT Some of you may have missed the dark attempt at humour. I prefer to keep this as a non-duplicate question for any such day when MySQL will do something different to SQL Server. So I threw a vote to reopen the question (with a clearly wrong answer).

The above MyISAM optimization applies equally to

COUNT(*)
COUNT(1)
COUNT(pk-column)
COUNT(any-non-nullable-column)

所以真正的答案是它们总是相同.

So the real answer is that they are always the same.

相关文章