count(*)和count(1)的区别---InnoDB 没区别

原创于 2019-10-18 20:37:54 发布 · 323 阅读

0 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

26 篇文章

订阅专栏

本文探讨了在不同数据库引擎下，如InnoDB和MyISAM，使用SQL COUNT(*)和COUNT(1)函数的性能差异。对于InnoDB，两者表现相同，而MyISAM在特定条件下，COUNT(*)能更快返回结果，主要依赖于存储引擎的优化。

Wrong

~~通常，count(*)和count(1)的计算结果一致。~~
~~若有主键，count(主键)的运行效率最快；若无主键，count(1）比count(*)运行效率要快。~~
~~若整个表只有一个行，count()的运行效率最快；若多于一个行时，count(1)要比count()运行效率快。因为count(*)会扫描整个表。~~

~~https://www.jianshu.com/p/84379d16e9b2~~

Right to MySQL8.x

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.

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.