mysql count(*) 是否比 count(specific_field) 效率低得多? [英] Is mysql count(*) much less efficient than count(specific_field)?

问题描述

可能的重复:
COUNT() vs. COUNT(1) vs. COUNT(pk):哪个更好?
count() 和 count(column_name)，有什么区别?
count(*) vs count(column-name) - 哪个更正确?

Possible Duplicate:
COUNT() vs. COUNT(1) vs. COUNT(pk): which is better?
count() and count(column_name), what's the diff?
count(*) vs count(column-name) - which is more correct?

在 select 语句中使用 count(*) 的好处是我可以将它与任何表一起使用，这使得自动化脚本更容易:

The benefit of using count(*) in a select statement is that I can use it with any table and that makes automating scripts easier:

count_sql = 'select count(*)' + getRestOfSQL('tablename');

但是，它是否比使用 count(specific_field) 效率低?

But, is it less efficient than using count(specific_field)?

推荐答案

对于 InnoDB

如果 specific_field 不可为空，则它们是等效的并且具有相同的性能.

If specific_field is not nullable, they are equivalent and have the same performance.

如果 specific_field 可以为空，它们不会做同样的事情.COUNT(specific_field) 计算具有非空值的 specific_field 的行.这需要查看每一行的 specific_field 的值.COUNT(*) 只计算行数，在这种情况下可以更快，因为它不需要检查 specific_field 的值.

If specific_field is nullable, they don't do the same thing. COUNT(specific_field) counts the rows which have a not null value of specific_field. This requires looking at the value of specific_field for each row. COUNT(*) simply counts the number of rows and in this case can be faster as it does not require examining the value of specific_field.

对于 MyISAM

对以下内容进行了特殊优化，因此它甚至不需要获取所有行:

There is a special optimization for the following so that it does not even need to fetch all rows:

SELECT COUNT(*) FROM yourtable

这篇关于mysql count(*) 是否比 count(specific_field) 效率低得多?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！