问：select count(*)可以用来查询表的记录数，听说count(1)、count(主键)、count(非主键列)也可以，这几个有什么区别吗？

答：不考虑Null的情况

count(1)和count(主键)、count(*) 这几个只扫描主键Index就可以得到数据
count(非主键列)是扫描表的
所以相对来说，count(1)和count(主键)这两个效率高
还有一种写法是count（ROWID）这也是只扫描Index的，效率高
事实上，效率上的问题不用考虑太多，count(*),count(1)和count(主键)都没错，结合实际数据库运行一下，选择你合适的就可以

考虑Null的情况

count(主键)肯定没有空值
count(*)和count(1)能取出含有空值的所有记录数
count(非主键列)不含空值

以hr测试用户下的employees表为例，分别输出查询结果以及执行计划，便于大家进行对比分析。

查询结果

SQL> select count(*) from employees;

COUNT(*)
----------
107

SQL> select count(1) from employees;

COUNT(1)
----------
107

SQL> select count(employee_id) from employees;－－主键列

COUNT(EMPLOYEE_ID)
------------------
107

SQL> select count(commission_pct) from employees;－－非主键列（包含空值）

COUNT(COMMISSION_PCT)
---------------------
35

SQL> select count(rowid) from employees;

COUNT(ROWID)
------------
107

执行计划

SQL> set autot trace exp
SQL> select count(*) from employees;

Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------

SQL> select count(1) from employees;

Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------

SQL> select count(employee_id) from employees;

Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------

SQL> select count(commission_pct) from employees;

Execution Plan
----------------------------------------------------------
Plan hash value: 1756381138

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 214 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------

SQL> select count(rowid) from employees;

Execution Plan
----------------------------------------------------------
Plan hash value: 3580537945

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 | | |
| 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1284 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------

~~~~~~~ the end~~~~~~~~~

hoegh

2016.02.03

ITPub博客

Ask Hoegh（4）——select count(*)和select count(1)、count(column)有区别吗？

查询结果

执行计划

最新文章