MySQL 中的 COUNT(id) 与 COUNT(*)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1697137/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
COUNT(id) vs. COUNT(*) in MySQL
提问by James Simpson
Is there a difference between the following queries, assuming there is a primary field 'id' in the table (as in speed, etc)?
假设表中有一个主要字段“id”（如速度等），以下查询之间是否存在差异？
SELECT COUNT(id)
FROM table
vs.
对比
SELECT COUNT(*)
FROM table
采纳答案by Pascal Thivent
Have a look at Count(*) vs Count(col)at www.mysqlperformanceblog.com, they discuss this topic for various 'col' types (NOT NULL or not, with index, etc) and this for MyISAM and InnoDB tables.
在 www.mysqlperformanceblog.com 上查看Count(*) 与 Count(col)，他们讨论了各种“col”类型（NOT NULL 与否，带索引等）的主题，以及 MyISAM 和 InnoDB 表的主题。
回答by davek
I know the question is about MySQL, but for what it's worth, count(*) is recommended for Oracle: which goes to show that this is database specific (see comment above from BalusC). Since a lot of databases (MS-SQL, MySQL) have information schema tables that hold various types of metadata, there are bound to be differences if one syntax is simply looking up a readily-available value, and another is going straight to the table. At the end of day: try different options, an see what EXPLAIN is telling you is going on behind the scenes.
我知道这个问题是关于 MySQL 的，但是对于它的价值，建议将 count(*) 用于 Oracle：这表明这是特定于数据库的（请参阅上面来自 BalusC 的评论）。由于很多数据库（MS-SQL、MySQL）都有保存各种类型元数据的信息模式表，如果一种语法只是查找一个容易获得的值，而另一种则直接访问该表，那么肯定会有差异. 在一天结束时：尝试不同的选项，看看 EXPLAIN 告诉你在幕后发生了什么。
回答by Satakshi Pandey
COUNT(*) with InnoDB is slower than COUNT(ID) because InnoDB doesn't cache the row count.
InnoDB 的 COUNT(*) 比 COUNT(ID) 慢，因为 InnoDB 不缓存行数。
On the other hand, with MyISAM, count(*) query would be faster and return result in no time because MyISAM maintains the count of rows.
另一方面，使用 MyISAM，count(*) 查询会更快并立即返回结果，因为 MyISAM 维护行数。
It is easy for MyISAM to maintain the count of rows since only one transaction executes at any time, because MyISAM does table-level locking. After a transaction commits, it is easy for MyISAM to update the row count for a table (taking into account inserts and deletes done by the transaction) before the next transaction starts.
MyISAM 很容易维护行数，因为在任何时候只执行一个事务，因为 MyISAM 执行表级锁定。事务提交后，MyISAM 很容易在下一个事务开始之前更新表的行数（考虑到事务完成的插入和删除）。
However, with InnoDB, which uses row-level locking, there are multiple concurrently executing (and as-yet uncommitted) transactions. To ensure consistency, each transaction has to see the table (including the number of rows in the table) as of the beginning of the transaction, plus its own changes. Thus the number of rows in the table is obviously potentially different for each concurrent transaction. Therefore there is no single “correct” total number of rows at any given time (unless there are no update transactions running). With multiple concurrent transactions, it is not really possible to cache the total number of rows in a table.
但是，对于使用行级锁定的 InnoDB，有多个并发执行（和尚未提交）的事务。为确保一致性，每个事务都必须查看自事务开始时的表（包括表中的行数），以及其自身的更改。因此，对于每个并发事务，表中的行数显然可能不同。因此，在任何给定时间都没有单一的“正确”总行数（除非没有更新事务在运行）。对于多个并发事务，实际上不可能缓存表中的总行数。
回答by Guguzz
Count(*) Count(Ename) Can show diff outputs because Ename is not a column with Not null constraint and it surely be having some null values Which are not being counted.
Count(*) Count(Ename) 可以显示差异输出，因为 Ename 不是具有 Not null 约束的列，它肯定有一些未计算的空值。
Hope it helps..!
希望能帮助到你..！
