MySQL COUNT(*) 和COUNT(1)的区别

先说结论：推荐使用COUNT(*)

 

通过MySQL5.7官方文档https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html可知

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

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

InnoDB引擎下COUNT(*) 和COUNT(1)没有性能差异。

MyISAM引擎下使用COUNT(1)时如果第一列定义为NOT NULL就和COUNT(*) 一样，否则性能没有COUNT(*)好。

 

所以推荐使用COUNT(*)，无论时InnoDB还是MyISAM都能保证最优性能，并且MySQL8.0又对COUNT(*)做了优化。

官方文档链接https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html