【字节跳动】数据挖掘面试题0014：SQL中count(1), count(*), count(列）区别

SQL 中 count(1)、count(*)、count(某列) 的区别

在 SQL 中，count() 是常用的聚合函数，但 count(1)、count(*) 和 count(某列) 的语义和性能存在细微差异，这也是面试中的高频考点。以下从定义、行为、性能和适用场景四个维度详细解析：

一、核心定义与行为差异

二、示例说明差异

假设有表 students 如下：

• SELECT count(*)：结果为 4（统计所有行）。
• SELECT count(1)：结果为 4（统计所有行）。
• SELECT count(name)：结果为 2（仅统计 name 列非 NULL 的行）。
• SELECT count(age)：结果为 2（仅统计 age 列非 NULL 的行）。

三、性能差异与优化

1. count(*) 与 count(1)

   • 多数数据库（如 MySQL、PostgreSQL）：二者执行效率完全相同，优化器会将 count(1) 视为 count(*)。
   • 少数数据库（如 Oracle）：count(1) 在特定索引扫描时可能略快，但差异可忽略不计。

2. count(某列)

   • 无索引：需逐行扫描，检查列值是否为 NULL，效率较低。
   • 有索引：若统计的列存在索引，数据库可能直接通过索引树快速统计非 NULL 值（无需扫描全量数据行）。

四、适用场景建议

1. 统计总行数：
   推荐使用 count(*)，语义清晰且性能最优。

2. 统计非空值数量：
   使用 count(某列)，例如统计用户表中填写了邮箱的用户数：

   SELECT count(email) FROM users;
   

3. 性能优化场景：

   • 若需频繁统计非空值，可为该列添加索引。
   • 避免在 count() 中使用表达式（如 count(column+1)），会强制全表扫描。

4. 关键区别总结

   函数	统计范围	是否包含 NULL	性能建议
   COUNT(*)	所有行	✅ 是	⭐️ 最优
   COUNT(1)	所有行（同 COUNT(*)）	✅ 是	等同 COUNT(*)
   COUNT(列)	指定列的非 NULL 值	❌ 否	需检查列值

五、面试应答要点

1. 行为差异：

   • count(*) 和 count(1) 统计所有行，count(某列) 仅统计非 NULL 值。

2. 性能考量：

   • count(*) 和 count(1) 性能无显著差异。
   • count(某列) 若有索引则可能更快，否则可能慢于 count(*)。

3. 数据库特定优化：

   • MySQL 对 count(*) 有特殊优化，即使表无索引也可能快速返回结果（依赖存储引擎）。
   • Oracle 在大表上 count(1) 可能略优于 count(*)（需结合执行计划分析）。

示例场景：
假设用户表 users 有 100 万行数据，其中 email 列有 20% 的 NULL 值：

• SELECT count(*)：返回 100 万。
• SELECT count(email)：返回 80 万。
• 若 email 列有索引，count(email) 可能比 count(*) 更快（索引扫描比全表扫描高效）。

六、索引扫描与全表扫描

在数据库中，索引扫描（Index Scan）比全表扫描（Table Scan）更高效的核心场景是：当查询只需访问少量数据时。以下是详细分析和常见面试考点：

1. 索引扫描的触发条件

索引扫描通常在以下情况触发：

• 过滤条件有效：查询的WHERE子句包含索引列（如WHERE age > 30），且过滤后的数据量较小（通常认为占全量数据的5-10%以下）。
• 覆盖索引：查询所需的字段（如SELECT id, age）全部包含在索引中，无需回表查询。
• 索引有序性：查询需要排序（如ORDER BY age），且索引本身有序（避免额外排序操作）。

2. 全表扫描的适用场景

全表扫描通常在以下情况更优：

• 过滤条件不高效：例如WHERE name LIKE '%张%'（左模糊无法利用索引）。
• 查询大部分数据：例如WHERE age > 10（若大部分数据满足条件，全表扫描可能更快）。
• 无合适索引：查询字段未建立索引，或索引选择性低（如性别字段）。

3. 常见面试问题点

Q1：索引扫描一定比全表扫描快吗？

A：不一定。

• 反例：当查询需要返回大部分数据（如WHERE age > 10），全表扫描可能更快。因为索引扫描需先访问索引，再回表查询数据，若数据量过大，IO成本反而更高。

Q2：如何判断数据库是否选择索引扫描？

A：通过执行计划（如MySQL的EXPLAIN）查看：

• type字段为index或range表示使用索引扫描。
• type字段为ALL表示全表扫描。

Q3：索引选择性对扫描方式的影响？

A：索引选择性（Unique Ratio）指索引列中不同值的数量与总行数的比例。

• 高选择性（如用户ID）：索引扫描效率高。
• 低选择性（如性别）：可能导致优化器放弃索引，选择全表扫描。

Q4：如何优化索引扫描？

A：

• 创建复合索引（如(age, gender)）覆盖常用查询。
• 避免索引冗余（如已有(a, b)索引，无需单独创建(a)索引）。
• 使用覆盖索引减少回表。

Q5：数据库如何选择扫描方式？

A：优化器基于以下因素估算成本：

• 索引选择性和统计信息。
• 表数据量和分布。
• 内存和IO性能。

4. 示例对比分析

假设表users有100万行数据，age列有索引：

总结

• 索引扫描优势：过滤少量数据、覆盖索引、利用有序性。
• 全表扫描优势：查询大量数据、无合适索引、过滤条件低效。
• 核心原则：索引是为了减少IO，若索引扫描的IO成本反而更高，优化器会选择全表扫描。