count(0)，count(*)，count(1)，count(列)有什么区别？

1. 基本概念与语义

• COUNT(*)

  • 统计表中所有行的总数，包括NULL 值和重复值 。
  • 例如：SELECT COUNT(*) FROM employees;
  • 语义：接统计所有行数，不关心列的具体值。

• COUNT(1) / COUNT(0)

  • 统计满足条件的行数，将 1 或 0 视为常量（非空值）。
  • 例如：SELECT COUNT(1) FROM employees;
  • 语义：与 COUNT(*) 类似，但 MySQL 会将 COUNT(1) 和 COUNT(0) 优化为相同的执行计划。

• COUNT(某一列)

  • 统计指定列中 非 NULL 值的行数。
  • 例如：SELECT COUNT(department_id) FROM employees;
  • 语义：仅统计该列值不为 NULL 的行。

2.核心区别

3.性能分析

• COUNT(*) vs COUNT(1) vs COUNT(0)
• MySQL 官方文档指出：InnoDB 会以相同的方式处理 COUNT(*) 和 COUNT(1)，没有性能差异。
• 优化策略：
  • 如果表存在 二级索引（非主键索引），MySQL 会自动选择 最小的索引 （索引的存储空间最小）进行扫描（减少 I/O）。
  • 如果表 没有二级索引，则会扫描聚簇索引（主键索引） 。
  • COUNT(0) 与 COUNT(1) 逻辑一致，性能相同。
• COUNT(某一列)
  • 性能取决于是否对该列建立了索引：
    • 有索引：通过索引快速统计非 NULL 值的行数，原因是：索引中不会存在空值,因此，COUNT(username) 直接统计索引中存在的条目数（即非 NULL 值的行数）。
    • 无索引：需 全表扫描 检查列值是否为 NULL，效率最低。
  • 建议：如果需要统计某列的非空值数量，应为该列建立索引。

4.场景描述

上面提到了几个点：
1.count(*)和count(数字)InnoDB选择最小的索引进行扫描，包含空值，什么叫最小的索引
2.count(列)具体是怎么统计该列值非空的行数的

针对上面两个问题，我们一起看一下

4.1 什么是“最小的索引”？

“最小的索引” 指的是 索引的存储空间较小，通常是因为：

• 索引的列数量少（单列索引 vs 多列索引）。
• 索引列的数据类型占用空间小（如 INT 比 VARCHAR(255) 占用更少空间）。
• 索引的层级较短（B+树的深度更小，需扫描的节点更少）。

4.2 为什么选择最小的索引？

• 减少I/O开销：
  • 索引的存储空间越小，扫描该索引时需要读取的磁盘数据块（Block）越少，从而减少I/O操作次数。
  • I/O是数据库性能的瓶颈之一，减少I/O能显著提升查询速度。
• 优化扫描效率：
  • 较小的索引通常对应更短的B+树层级，查询时需要遍历的节点更少，定位数据更快。

4.3 为什么二级索引更可能被选为“最小的索引”？

• 聚簇索引（主键索引）的特性：
  • 聚簇索引的叶子节点存储完整的数据行（包括所有列数据），因此它的索引体积通常较大。
  • 例如：主键是 UUID（16字节）时，聚簇索引的每个叶子节点需要存储完整的行数据，体积远大于二级索引。
• 二级索引的特性：
  • 二级索引的叶子节点仅存储索引列的值 + 主键值，体积远小于聚簇索引 。
  • 如果主键较小（如自增 INT），二级索引的体积会更小。

4.4 具体场景示例

假设有一个表 users：

CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,  -- 自增主键（4字节）username VARCHAR(50),               -- 可能较大的字段email VARCHAR(100),                 -- 可能较大的字段INDEX idx_username(username),       -- 二级索引1（单列）INDEX idx_email(email)              -- 二级索引2（单列）
);

场景1：执行 SELECT COUNT(*) FROM users;

• MySQL会比较所有可用索引的大小：
  • 聚簇索引（主键 id）的叶子节点存储完整的 username 和 email 数据，体积最大。
  • 二级索引（如 idx_username 或 idx_email）的叶子节点仅存储索引列值 + 主键 id，体积更小。
• 优化器选择：
  • 选择最小的二级索引（如 idx_username）进行扫描，因为它的体积更小，扫描速度更快。

场景2：执行SELECT COUNT(username) FROM users WHERE username IS NOT NULL;

• 直接使用 idx_username：
  • 二级索引 idx_username 的叶子节点存储了 username 的值（非空）和主键 id，可以直接统计非空值的数量，无需回表。
  • 性能优于扫描聚簇索引。

场景3：执行 SELECT COUNT(username) FROM users WHERE username IS NOT NULL和执行 SELECT COUNT(username) FROM users WHERE username 性能是否相同

• COUNT(username) 会自动忽略 NULL 值：即使不加 WHERE username IS NOT NULL，COUNT(username) 也会统计 username 列中 非 NULL 值的行数。
• 语义上等效：两种写法的查询结果 完全相同，因为 COUNT(username) 本身就会过滤掉 NULL 值。
• 性能上的差异：
  • 优化器可能认为 WHERE username IS NOT NULL 是一个 过滤条件，需要对索引中的每条记录进行判断，即使判断结果始终为真（因为索引中没有 NULL 值），优化器仍可能 保留这个条件，导致cpu额外的开销
  • 而针对count(列)优化器知道 COUNT(username) 的语义是“统计非 NULL 值”，且索引中已排除 NULL 值，因此可以直接利用索引统计总数。这种情况下，无需回表，也无需额外的条件判断。

5.面试回答模板

首先推荐使用count(*)，count(数字)和count(*)，InnoDB引擎会以相同的方式处理，性能相同，如果表存在二级索引，会选择最小索引即代价最小的方式检索出行数，无二级索引则会利用聚簇索引检索行数，而count(列)，如果有索引，则可以根据索引直接统计出非空的行数，如果没有索引，则需要全表查询，并进行非空判断，性能很差，所以优先推荐count(*)，并且MyIsAM引擎下，行数信息存储在 MyISAM 表的索引文件中，可以直接获取，效率更高。