什么是MYSQL count(*)或count(1)更好的选择? [英] What is better in MYSQL count(*) or count(1)?

问题描述

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

您能告诉我什么是性能更好的(MySQL)吗? Count(*)还是count(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.

http://dev.mysql.com/doc/refman /5.1/zh-CN/group-by-functions.html#function_count

如果SELECT从一个表检索，没有检索其他列并且没有WHERE子句，则优化

COUNT(*)以非常快地返回.例如:

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表，因为为该存储引擎存储了确切的行数，并且可以非常快速地对其进行访问.对于InnoDB之类的事务存储引擎，存储准确的行数会更加麻烦，因为可能会发生多个事务，每个事务都可能影响计数.

This optimization applies only to MyISAM tables only, because an exact row count is stored for this storage engine and can be accessed very quickly. For transactional storage engines such as InnoDB, storing an exact row count is more problematic because multiple transactions may be occurring, each of which may affect the count.

---

你们当中有些人可能错过了幽默的黑暗尝试.当MySQL将做与SQL Server不同的事情时，我宁愿将此问题保留为任何重复的问题.因此，我投了票重新提出问题(答案显然错误).

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

以上MyISAM优化同样适用于

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.

这篇关于什么是MYSQL count(*)或count(1)更好的选择?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！