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

发布于:2025-07-10 ⋅ 阅读:(25) ⋅ 点赞:(0)

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

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

一、核心定义与行为差异

语法 定义 是否忽略 NULL
count(*) 统计 所有行 的数量,无论列值是否为 NULL
count(1) 对每行插入一个常量值 1,然后统计 1 的数量,本质与 count(*) 相同。
count(某列) 统计指定列中非 NULL 值的数量。

二、示例说明差异

假设有表 students 如下:

id name age
1 Alice 20
2 Bob NULL
3 NULL 22
4 NULL NULL
  • 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字段为indexrange表示使用索引扫描。
  • type字段为ALL表示全表扫描。
Q3:索引选择性对扫描方式的影响?

A:索引选择性(Unique Ratio)指索引列中不同值的数量与总行数的比例。

  • 高选择性(如用户ID):索引扫描效率高。
  • 低选择性(如性别):可能导致优化器放弃索引,选择全表扫描。
Q4:如何优化索引扫描?

A

  • 创建复合索引(如(age, gender))覆盖常用查询。
  • 避免索引冗余(如已有(a, b)索引,无需单独创建(a)索引)。
  • 使用覆盖索引减少回表。
Q5:数据库如何选择扫描方式?

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

  • 索引选择性和统计信息。
  • 表数据量和分布。
  • 内存和IO性能。
4. 示例对比分析

假设表users有100万行数据,age列有索引:

查询场景 扫描方式 原因
WHERE age = 20(1万行) 索引扫描 过滤后数据量小,索引扫描效率高。
WHERE age > 10(90万行) 全表扫描 过滤后数据量大,全表扫描避免多次IO。
SELECT id, age WHERE age=20 索引扫描(覆盖) 索引包含所有所需字段,无需回表。
SELECT * WHERE name LIKE '%张%' 全表扫描 左模糊无法利用索引。
总结
  • 索引扫描优势过滤少量数据、覆盖索引、利用有序性
  • 全表扫描优势:查询大量数据、无合适索引、过滤条件低效。
  • 核心原则索引是为了减少IO,若索引扫描的IO成本反而更高,优化器会选择全表扫描

网站公告

今日签到

点亮在社区的每一天
去签到