MySQL 中 count(*)、count(1) 和 count(字段名) 有什么区别？

我们来深入聊聊 MySQL 中 COUNT(*)、COUNT(1) 和 COUNT(字段名) 这三兄弟的奥秘。

它们都是用来计算行数的，但 统计的范围和底层执行的方式有所不同，这会导致它们在特定场景下的效率差异。

• COUNT(*) 和 COUNT(1) 都是统计 所有行数，包括那些字段值全部为 NULL 的行。它们在绝大多数现代 MySQL (InnoDB 引擎) 下，效率几乎一样，并且通常是最高效的。
• COUNT(字段名) 是统计 指定字段值不为 NULL 的行数。如果这个字段上存在 NULL 值，或者没有合适的索引，它的效率可能会低于前两者。

深入理解：底层原理与优化

要理解它们，我们需要把事情说清楚，就好像你在探究一个公司的员工人数：

1. COUNT(*)：数一数有多少个“活生生的实体”

• 它的意图： COUNT(*) 的核心目的是统计表中有多少条记录（行）。它不关心每条记录里面具体有什么数据，只要这条记录存在，就把它算进去。
• MySQL 的聪明之处（优化器）: 当你使用 COUNT(*) 时，MySQL 的“大脑”（查询优化器）知道你只是想知道总共有多少行。它会非常聪明地选择最快的路径来完成这个任务。
  • 如果表很小，或者在 MyISAM 引擎下： MySQL 就像你直接问前台“我们公司现在有多少员工？” MyISAM 引擎在表内部就存储了精确的行数，所以 COUNT(*) 几乎是瞬间完成，因为它是直接读取这个预存的数字。
  • 如果在 InnoDB 引擎下（现代 MySQL 的主力）： InnoDB 为了支持高并发和事务，它不会像 MyISAM 那样在表头直接存一个精确的行数（因为行数会随着并发事务的增删改而不断变化）。所以，它需要“数一数”。
    • 但 InnoDB 不会傻乎乎地去扫描整个表的所有数据（那样太慢了）。它会选择最小的那个二级索引（非主键索引）来扫描。为什么？因为二级索引通常比主键索引小得多（只包含索引列和主键值），扫描它所需要的数据量更少，IO 操作也更少，所以更快。如果表没有二级索引，它才会去扫描主键索引（聚簇索引）。
    • 你可以想象成，公司为了快速点人数，不看花名册（全表数据），而是看员工的工牌号（二级索引），因为工牌号是按顺序发的，而且只需要扫描工牌号这个小本子就行。

2. COUNT(1)：数一数每个“存在”的“1”

• 它的意图： COUNT(1) 的核心目的也是统计表的行数。这里的 1 只是一个常量，它表示“找到一行，就用常量 1 来代表它存在”，然后统计这些“1”的个数。它同样不关心行里的实际数据。
• 与 COUNT(*) 的相似之处： 因为 COUNT(1) 和 COUNT(*) 在逻辑上都是统计“所有行”的存在性，所以 MySQL 优化器会把它们视为等价的操作。
  • 在 MyISAM 引擎下，它和 COUNT(*) 一样，都是瞬间读取预存的行数。
  • 在 InnoDB 引擎下，它也和 COUNT(*) 一样，优化器会选择最快的索引（通常是最小的二级索引）来扫描，而不是全表扫描。
• 总结： 在绝大多数情况下，COUNT(*) 和 COUNT(1) 的执行计划和效率是完全相同的。它们都被优化器视为“只关心行数，不关心行内容”的特殊计数操作。

3. COUNT(字段名)：数一数某个“特定属性”不为空的行

• 它的意图： COUNT(字段名) 的目的是统计指定 字段名 不为 NULL 的行数。如果某个行的这个字段是 NULL，那么这一行就不会被计数。
• 执行方式：
  • 为了判断字段是否为 NULL，数据库需要实际访问这个字段的值。
  • 如果该字段上有索引： MySQL 可以扫描该字段的索引来判断哪些值不是 NULL。如果索引是 B-tree 索引，并且字段不允许 NULL，效率会比较高。但如果字段允许 NULL，即使有索引，也需要跳过索引中的 NULL 值。
  • 如果该字段上没有索引： MySQL 就需要进行全表扫描，一行一行地读取数据，然后检查每一行的这个特定字段的值是否为 NULL。这个过程会非常慢，因为它涉及到大量的磁盘 I/O。
• 比喻： 就像你在公司点名，但这次你只统计那些“有手机号码”的员工。你需要去查看每个员工的档案，找到“手机号码”那一栏，如果发现是空的，就不算这个人。这显然比简单地数总人数要麻烦一些。如果公司把所有手机号码整理成了一本电话簿（索引），你查起来就快一些。

流程图：InnoDB 引擎的计数决策路径

理解了背后的原理，我们来看看 InnoDB 引擎（现代 MySQL 的主流）在面对这几种计数请求时，它的大脑是如何思考和决策的：

性能总结与最佳实践

• MyISAM 引擎：
  • COUNT(*) 和 COUNT(1) 是最快的，因为它们直接读取元数据。
  • COUNT(字段名) 会慢一些，因为它需要实际扫描数据来检查 NULL 值。
• InnoDB 引擎：
  • COUNT(*) 和 COUNT(1) 几乎没有性能差异，它们都会被优化器智能地处理，通常通过扫描最小的二级索引来完成，所以速度很快。
  • COUNT(字段名) 的性能取决于：
    • 该字段是否有索引：有索引会比没索引快很多。
    • 该字段是否允许 NULL 值：如果允许 NULL，即使有索引，也需要额外的逻辑来跳过 NULL 值。
  • 通常，COUNT(*) 或 COUNT(1) 在 InnoDB 中会比 COUNT(字段名) 更优，因为优化器可以更灵活地选择最佳的索引路径。

发散思考：为什么 InnoDB 不像 MyISAM 那样直接存行数？

这是一个非常深刻的问题！它的答案在于 MySQL 两种引擎设计的核心理念：

1. 并发控制与事务隔离（MVCC）：

   • MyISAM：采用表级锁。当有写入操作（INSERT/UPDATE/DELETE）时，整个表都会被锁定。这意味着在任何给定时间，表的状态都是固定的，行数是确定的，所以可以很容易地在元数据中存储精确的行数。
   • InnoDB：采用行级锁和多版本并发控制（MVCC）。这意味着在同一个时间点，可能有多个事务在并行地读写数据。一个事务看到的表数据，可能是它开始时的一个“快照”，而不是最新的实时数据。
     • 举个例子：事务 A 在计数时，事务 B 可能正在插入或删除数据。如果 InnoDB 维护一个全局的精确行数，那么 A 看到的行数就会不断变化，这会破坏事务的隔离性（可重复读）。
     • 因此，InnoDB 宁愿每次都去“数一遍”（通过扫描索引），以确保在当前事务的“快照”中得到一个一致的行数。这是为了牺牲一点点计数速度，来换取更高的并发性和更好的事务隔离性。

2. 崩溃恢复与数据一致性：

   • MyISAM 如果在写入过程中崩溃，可能会导致预存的行数与实际行数不一致。
   • InnoDB 的崩溃恢复机制更加健壮，它通过 redo log 和 undo log 来保证数据的一致性，即使在崩溃后也能恢复到正确的状态，这使得维护一个实时行数变得更加复杂和低效。

• 对于 MyISAM 引擎： COUNT(*) 和 COUNT(1) 都是 O(1) 操作，瞬间完成。
• 对于 InnoDB 引擎： COUNT(*) 和 COUNT(1) 会被优化器处理得非常高效，通常扫描最小的二级索引。COUNT(字段名) 的效率取决于字段是否有索引以及是否存在 NULL 值。

在绝大多数情况下，尤其是在使用 InnoDB 引擎的现代 MySQL 中，强烈建议使用 COUNT(*) 来统计总行数。

把它交给 MySQL 优化器去处理是最好的选择，它会根据你表的实际情况（包括索引）自动选择最高效的计数策略。你不需要去纠结 COUNT(*) 和 COUNT(1) 哪个更快，因为它们在优化器层面通常被视为等价，并执行相同的优化策略。只有当你确实需要统计某个特定字段的非 NULL 值数量时，才使用 COUNT(字段名)。