最近一仁兄,问到关于count(普通列), count(*),count(1),count(index)还有count(主键)执行效率,所以,今天抽个空,自己做个实验,测试测试不同的情况,我测试的思路是从执行计划上和运行时间这两方面入手,如有不正确之出,还敬请指出!!
- 首先确定的是:
count(*)包括了所有的列,在统计结果时,不会忽略列值为NULL ;
count(1)包括了所有列,用1代表代码行,在统计结果时,不会忽略列值为NULL ;
count(列名)只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是只空字符串或者0,而是表示null)的计数,即某个字段值为NULL时,不统计;
-
所以我这里测试比对的是都不为null的情况,即该Table,即存在主键,也存在Index列,不为空的列
-
结论:
该Table数据量:33681874(千万级)
根据执行计划,都是走PK查询,所有时间基本相差不大,如下:
Count(1):00:00:05.13;
count(*):00:00:05.29;
Count(主键):00:00:05.07;
Count(Index):00:00:05.10;
Count(普通列):00:00:05.29;
所以对于有主键非null的Table来说,执行效率:
Count(主键) > Count(Index) > Count(1) > Count(*) = Count(普通列)
- 测试过程:
主键为:cnarc1010_seq_no
Index栏位:FN_OFFICE
非Null栏位:preparer_id
SQL> set linesize 200
SQL> set pagesize 9999
SQL> set timing on
- Count(1)
SQL> select count(1) from CNARC1010;
COUNT(1)
----------
33681874 –数据量
Elapsed: 00:00:05.13 --运行时间
运行时间:00:00:05.13
查看该SQL的执行计划:
SQL> explain plan for
2 select count(1) from CNARC1010;
Explained.
SQL> @explaint
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
Plan hash value: 1264924159
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15832 (1)| 00:03:10 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| CNARC1010_PK | 33M| 15832 (1)| 00:03:10 | -- 走PK查询
-------------------------------------------------------------------------
9 rows selected.
- Count(*)
SQL> select count(*) from CNARC1010;
COUNT(*)
----------
33681874 –数据量
Elapsed: 00:00:05.29 --运行时间
运行时间:00:00:05.29
查看该SQL的执行计划:
SQL> explain plan for
2 select count(*) from CNARC1010;
Explained.
SQL> @explaint
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
Plan hash value: 1264924159
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15832 (1)| 00:03:10 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| CNARC1010_PK | 33M| 15832 (1)| 00:03:10 | --走PK查询
-------------------------------------------------------------------------
9 rows selected.
- Count(主键)
SQL> select count(cnarc1010_seq_no) from CNARC1010;
COUNT(CNARC1010_SEQ_NO)
-----------------------
33681874 –数据量
Elapsed: 00:00:05.07 --运行时间
运行时间:00:00:05.07
查看该SQL的执行计划:
SQL> explain plan for
2 select count(cnarc1010_seq_no) from CNARC1010;
Explained.
SQL> @explaint
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
Plan hash value: 1264924159
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15832 (1)| 00:03:10 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| CNARC1010_PK | 33M| 15832 (1)| 00:03:10 | -- 走PK查询
-------------------------------------------------------------------------
9 rows selected.
- Count(Index)
SQL> select count(FN_OFFICE) from CNARC1010;
COUNT(FN_OFFICE)
----------------
33681874 –数据量
Elapsed: 00:00:05.10 --运行时间
运行时间:00:00:05.10
查看该SQL的执行计划:
SQL> explain plan for
2 select count(FN_OFFICE) from CNARC1010;
Explained.
SQL> @explaint
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
Plan hash value: 1264924159
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15832 (1)| 00:03:10 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| CNARC1010_PK | 33M| 15832 (1)| 00:03:10 | --走PK查询
-------------------------------------------------------------------------
9 rows selected.
- Count(普通列)
SQL> select count(preparer_id) from CNARC1010;
COUNT(PREPARER_ID)
------------------
33681874 –数据量
Elapsed: 00:00:05.29 --运行时间
运行时间:00:00:05.29
查看该SQL的执行计划:
SQL> explain plan for
2 select count(preparer_id) from CNARC1010;
Explained.
SQL> @explaint
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------
Plan hash value: 1264924159
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15832 (1)| 00:03:10 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| CNARC1010_PK | 33M| 15832 (1)| 00:03:10 | --走PK查询
-------------------------------------------------------------------------
9 rows selected.