基于本文回答

播面 播面

文图音视，全方位拆解八股文
0
评论
请从左侧选择一个分类

MySQL中COUNT(*), COUNT(1), COUNT(列名) 有什么区别？

知识点图片

本文对比 COUNT(*)、COUNT(1) 和 COUNT(列名)。COUNT(*) 与 COUNT(1) 功能性能一致，统计所有行；COUNT(列名) 只统计列中非空行。推荐使用 COUNT(*) 统计总数。

这是一个在 MySQL 面试和实际工作中都经常被问到的问题。我们来详细解释一下 COUNT(*), COUNT(1) 和 COUNT(列名) 之间的区别。

结论先行（太长不看版）

  1. 功能区别:

    • COUNT(*) 和 COUNT(1): 都会统计结果集中的所有行数，包括 NULL 值的行。它们的功能完全一样。
    • COUNT(列名): 只统计指定列中非 NULL 值的行数。

  2. 性能区别:

    • COUNT(*) 和 COUNT(1): 在现代 MySQL（特别是 InnoDB 存储引擎）中，性能没有区别。MySQL 优化器会把 COUNT(1) 自动优化成 COUNT(*)，并选择最优的索引进行计数。
    • COUNT(列名): 性能取决于该列是否有索引。
      • 如果没有索引，需要全表扫描，性能较差。
      • 如果有索引，扫描索引即可，性能较好。但通常还是比 COUNT(*) 慢，因为 COUNT(*) 可以选择一个更小的二级索引来扫描。

  3. 推荐用法:

    • 想统计总行数时，永远使用 COUNT(*)。这是 SQL 的标准写法，意图清晰，且性能最优。
    • 想统计某列非空值的数量时，才使用 COUNT(列名)。

详细解释

1. COUNT(*)

COUNT(*) 是 SQL-92 标准中定义的用于计算行数的标准语法。

  • 含义: 它会获取结果集中的所有行并进行计数。* 在这里并不是指“所有列”，而是一个代表“所有行”的通配符。
  • 如何工作: MySQL 优化器会智能地分析这条语句，它并不会真的去读取所有列的数据。相反，它会寻找一个最小的可用索引来完成计数。因为只需要知道有多少行，所以选择扫描最小的索引（通常是二级索引）可以大大减少 I/O 操作，从而提升性能。
  • 是否包含NULL: 是的，它计算的是物理行数，不管行中是否有 NULL 值。

2. COUNT(1)

COUNT(1) 是 COUNT(*) 的一个常见变体。

  • 含义: 它计算的是结果集中，值为 1 的这个表达式不为 NULL 的行数。因为 1 是一个常量，永远不为 NULL，所以其效果等同于计算所有行。
  • 如何工作: 很多人误以为 COUNT(1) 比 COUNT(*) 快，因为看起来它不需要处理 *。但在现代的 MySQL 优化器中，COUNT(1) 会被识别并转换成与 COUNT(*) 完全相同的执行计划。因此，它们之间没有性能差异。
  • 是否包含NULL: 是的，因为它统计的是所有行。

为什么会有 COUNT(1) 比 COUNT(*) 快的说法？
这可能源于非常古老的数据库系统或某些特定场景。但在主流的 MySQL 版本（如 5.7+ 和 8.0+）中，这种说法已经不再成立。官方文档也明确指出 COUNT(*) 是经过特殊优化的。

3. COUNT(列名)

COUNT(列名) 的功能与前两者有本质不同。

  • 含义: 它只计算指定 列名 中值不为 NULL 的行的数量。
  • 如何工作: 它需要扫描指定的列，并对每一行的该列值进行 NULL 判断。
    • 如果该列有索引，MySQL 会扫描这个索引来计数。
    • 如果该列没有索引，MySQL 将不得不进行全表扫描，逐行检查该列的值，性能会很差。
  • 是否包含NULL: 不包含。这是它和 COUNT(*) 最核心的区别。

示例:
假设我们有这样一个表 students:

id (PK) name email
1 Alice alice@example.com
2 Bob NULL
3 Charlie charlie@example.com
4 David NULL

执行不同的 COUNT 查询：

sql 
SELECT COUNT(*) FROM students;
-- 结果: 4 (计算所有行)

SELECT COUNT(1) FROM students;
-- 结果: 4 (计算所有行, 效果同上)

SELECT COUNT(id) FROM students;
-- 结果: 4 (id 是主键，不可能为 NULL)

SELECT COUNT(name) FROM students;
-- 结果: 4 (name 列没有 NULL 值)

SELECT COUNT(email) FROM students;
-- 结果: 2 (只计算了 email 列不为 NULL 的两行)

不同存储引擎下的行为差异

这个话题还与 MySQL 的存储引擎有关，主要是 MyISAM 和 InnoDB 的对比。

  • MyISAM: MyISAM 引擎内部有一个计数器，专门用来存放表的总行数。所以，当你对整个 MyISAM 表执行 SELECT COUNT(*) FROM table_name (不带 WHERE 条件)时，它会直接返回这个预存的计数值，速度极快，时间复杂度是 O(1)。
  • InnoDB: InnoDB 是一个支持事务和多版本并发控制（MVCC）的存储引擎。由于事务的存在，不同事务在同一时刻看到的表的行数可能是不同的。因此，InnoDB 无法像 MyISAM 那样维护一个简单的行数计数器。当执行 COUNT(*) 时，InnoDB 必须通过扫描索引或表来实时计算行数，时间复杂度是 O(N)。不过，如前所述，它会选择最小的索引来优化这个扫描过程。

当 COUNT(*) 带有 WHERE 条件时，无论 MyISAM 还是 InnoDB，都必须扫描表来找到符合条件的行，此时它们的性能差异就不那么明显了。

总结表格

语法 (Syntax) 含义 (Meaning) 是否包含NULL值 性能与优化 推荐场景
COUNT(*) 统计结果集中的所有行数。 是 性能最优。优化器会选择最小的索引进行扫描。 统计总行数的首选。
COUNT(1) 统计结果集中的所有行数。 是 与 COUNT(*) 完全相同，会被优化器转换。 不推荐，用 COUNT(*) 更标准、更清晰。
COUNT(列名) 统计指定列中非NULL值的行数。 否 性能依赖于该列是否有索引。通常比COUNT(*)慢。 需要统计某列有多少个有效值（非空）时使用。
00:00
00:00