SQL,count(*)与count(列)_sql count两个列-优快云博客

本文链接：https://blog.youkuaiyun.com/solgen/article/details/80630215

本文探讨了在不同场景下使用SQL的COUNT(*)与COUNT(列)的区别及性能对比，包括无索引、有索引等情况，并提供了实例分析。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 准备

create table t as select * from dba_objects;

2. 无index

2.1 select (*) from t;

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 324 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T | 173K| 2197K| 324 (1)| 00:00:06 |
---------------------------------------------------------------------------

2.2 select count(object_id) from t;

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 324 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T | 173K| 2197K| 324 (1)| 00:00:06 |
---------------------------------------------------------------------------

总结：
无INDEX的时候，count(*)和count(列) 走全表扫描，是一样的。

但是在消耗的时间的问题上，如果执行次数如够多，就会发现，
count(*)最快，count(列)的执行速度会随着列的偏移位置而越来越慢！

这是因为优化品的cost算法是和列偏移量有关的。列越靠后，性能越低
count(*)和列偏移量无关，所以性能最佳（参见，收获不至sql优化,17章）

3. 有index，但列值没有指定非空，且没有空值的情况

create index idx_t_1 on t(object_id)

3.1 select count(*) from t;

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 324 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T | 173K| 2197K| 324 (1)| 00:00:06 |
---------------------------------------------------------------------------

3.2 select count(object_id) from t

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 57 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 2197K| 57 (0)| 00:00:02 |
---------------------------------------------------------------------------------

COUNT(OBJECT_ID)
----------------
196954

4 有index，列没有指定非空，且有空值的情况

update t set object_id = null where rownum < 1000;

4.1 select count(*) from t;
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 324 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 173K| 324 (1)| 00:00:06 |
-------------------------------------------------------------------
COUNT(OBJECT_ID)
----------------
196954

4.2 select count(object_id) from t;
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 57 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 2197K| 57 (0)| 00:00:02 |
---------------------------------------------------------------------------------
COUNT(OBJECT_ID)
----------------
195955

count(列)会快，但是，可以看到结果两个不一样，少999
count(*)和count(object_id)不等价，所以这种情况要慎重

4.3 在其他非索引，但有空值的列上使用count()

update t set status = null where rownum < 2000;

select count(status) from t;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 324 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS FULL| T | 173K| 845K| 324 (1)| 00:00:06 |
---------------------------------------------------------------------------

COUNT(STATUS)
-------------
194958
可以看出结果少了1999，可以看出count(列)，不统计空值

5. 有index，列非空的情况

update t set object_id = rownum;
alter table t modify object_id not null;

5.1 select count(*) from t;
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 124 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 124 (0)| 00:00:03 |
-------------------------------------------------------------------------

5.2 select count(object_id) from t;
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 124 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 124 (0)| 00:00:03 |
-------------------------------------------------------------------------

可以看出，两种情况一样，都会走索引

5.3 select count(status) from t;
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 324 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | TABLE ACCESS FULL| T | 173K| 845K| 324 (1)| 00:00:06 |
---------------------------------------------------------------------------
count(列)，列为非索引列时，走全表扫描

总结：
各种情况下，与count(列)与count(*)比较
+--------------------------------------+-----------------------+---------------------------+
| | count(索引列) | count(非索引列) |
+--------------------------------------+-----------------------+---------------------------+
|无索引、无空值 | -------- | 等价，count(*)快，见2.2 |
|无索引、有空值 | -------- | 不等价 |
|有索引、列没指定NOT NULL、无空值| 等价、比count(*)快 | 等价、一样快 |
|有索引、列没指定NOT NULL、有空值| 不等价 | 不等价 |
|有索引、列指定NOT NULL | 等价、一样快 | 等价、count(*)快，见5.3 |

+--------------------------------------+------------------------+---------------------------+

6. 扩展

组合索引，且count(列)为索引前列

drop index idx_t_1;
create index idx_t_1 on t(object_id,object_type);

6.1 select count(*) from t;
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 83 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 83 (0)| 00:00:02 |
-------------------------------------------------------------------------

6.2 select count(object_id) from t;
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 83 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 83 (0)| 00:00:02 |
-------------------------------------------------------------------------

6.3 select count(object_type) from t;

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 83 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_T_1 | 173K| 83 (0)| 00:00:02 |
-------------------------------------------------------------------------

可以看出，对于索引快速全扫描，索引前列和索引后列，其他没有啥差别

至于相同的查询条件为什么前面的cost为124,后面的为83