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

问：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博客 ” ，链接：http://blog.itpub.net/30162081/viewspace-1986201/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30162081/viewspace-1986201/