文章大纲
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 的行)。
三、性能差异与优化
count(*)
与count(1)
- 多数数据库(如 MySQL、PostgreSQL):二者执行效率完全相同,优化器会将
count(1)
视为count(*)
。 - 少数数据库(如 Oracle):
count(1)
在特定索引扫描时可能略快,但差异可忽略不计。
- 多数数据库(如 MySQL、PostgreSQL):二者执行效率完全相同,优化器会将
count(某列)
- 无索引:需逐行扫描,检查列值是否为 NULL,效率较低。
- 有索引:
若统计的列存在索引,数据库可能直接通过索引树快速统计非 NULL 值(无需扫描全量数据行)
。
四、适用场景建议
统计总行数:
推荐使用count(*)
,语义清晰且性能最优。统计非空值数量:
使用count(某列)
,例如统计用户表中填写了邮箱的用户数:SELECT count(email) FROM users;
性能优化场景:
- 若需频繁统计非空值,可为该列添加索引。
- 避免在
count()
中使用表达式(如count(column+1)
),会强制全表扫描。
关键区别总结
函数 统计范围 是否包含 NULL
性能建议 COUNT(*)
所有行 ✅ 是 ⭐️ 最优 COUNT(1)
所有行(同 COUNT(*)
)✅ 是 等同 COUNT(*)
COUNT(列)
指定列的非 NULL
值❌ 否 需检查列值
五、面试应答要点
行为差异:
count(*)
和count(1)
统计所有行,count(某列)
仅统计非 NULL 值。
性能考量:
count(*)
和count(1)
性能无显著差异。count(某列)
若有索引则可能更快,否则可能慢于count(*)
。
数据库特定优化:
- MySQL 对
count(*)
有特殊优化,即使表无索引也可能快速返回结果(依赖存储引擎)。 - Oracle 在大表上
count(1)
可能略优于count(*)
(需结合执行计划分析)。
- MySQL 对
示例场景:
假设用户表 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
列有索引:
查询场景 | 扫描方式 |
原因 |
---|---|---|
WHERE age = 20 (1万行) |
索引扫描 | 过滤后数据量小,索引扫描效率高。 |
WHERE age > 10 (90万行) |
全表扫描 | 过滤后数据量大,全表扫描避免多次IO。 |
SELECT id, age WHERE age=20 |
索引扫描(覆盖) | 索引包含所有所需字段,无需回表。 |
SELECT * WHERE name LIKE '%张%' |
全表扫描 | 左模糊无法利用索引。 |
总结
- 索引扫描优势:
过滤少量数据、覆盖索引、利用有序性
。 - 全表扫描优势:查询大量数据、无合适索引、过滤条件低效。
- 核心原则:
索引是为了减少IO,若索引扫描的IO成本反而更高,优化器会选择全表扫描
。