count(*)与count(1)的区别有多大？

数据库表的记录数为：

SQL> select count(*) from table_name t;

COUNT(*)
----------
      6873

1、使用count(*)的统计结果：

SQL> alter session set nls_language = "American";

Session altered.

SQL> set timing on;
SQL> set autotrace on;
SQL> select a.document_id,count(*) from table_name a group by a.document_id having count(a.document_id) >1;

DOCUMENT_ID   COUNT(*)
----------- ----------
          0         71

Elapsed: 00:00:05.20

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=90 Card=339 Bytes=16
          95)

   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=90 Card=339 Bytes=1695)
   3    2       TABLE ACCESS (FULL) OF 'table_name' (Cost=78 Card=6828
           Bytes=34140)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        837 consistent gets
          0 physical reads
          0 redo size
        230 bytes sent via SQL*Net to client
        242 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>
2、使用count(1)的统计结果：

SQL> alter session set nls_language = "American";

Session altered.

SQL> set timing on;
SQL> set autotrace on;
SQL> select a.document_id,count(1) from table_name a group by a.document_id having count(a.document_id) >1;

DOCUMENT_ID   COUNT(1)
----------- ----------
          0         71

Elapsed: 00:00:05.57

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=90 Card=339 Bytes=16
          95)

   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=90 Card=339 Bytes=1695)
   3    2       TABLE ACCESS (FULL) OF 'table_name' (Cost=78 Card=6828
           Bytes=34140)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        837 consistent gets
          0 physical reads
          0 redo size
        230 bytes sent via SQL*Net to client
        242 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>

其实：两个并没有多大差别！使用count(1)要比count(*)的用时多些！

对数据库表作分析之后的比较：

3、使用count(*)的结果：

SQL> analyze table table_name compute statistics;

Table analyzed.

Elapsed: 00:00:02.92
SQL> select a.document_id,count(*) from table_name a group by a.document_id having count(a.document_i

DOCUMENT_ID   COUNT(*)
----------- ----------
          0         71

Elapsed: 00:00:05.43

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=93 Card=341 Bytes=13
          64)

   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=93 Card=341 Bytes=1364)
   3    2       TABLE ACCESS (FULL) OF 'table_name' (Cost=82 Card=6873
           Bytes=27492)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        837 consistent gets
          0 physical reads
          0 redo size
        231 bytes sent via SQL*Net to client
        241 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>

4、使用count(1)的结果：

SQL> analyze table table_name compute statistics;

Table analyzed.

Elapsed: 00:00:02.89
SQL> select a.document_id,count(1) from table_name a group by a.document_id having count(a.document_id) >1;

DOCUMENT_ID   COUNT(1)
----------- ----------
          0         71

Elapsed: 00:00:04.95

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=93 Card=341 Bytes=13
          64)

   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=93 Card=341 Bytes=1364)
   3    2       TABLE ACCESS (FULL) OF 'table_name' (Cost=82 Card=6873
           Bytes=27492)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
        837 consistent gets
          0 physical reads
          0 redo size
        231 bytes sent via SQL*Net to client
        242 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          1 rows processed

SQL>

由此可见，对表作分析之后，使用count(1)要比count(*)的用时少些！

而且：表分析前后：count(*)分析后比count(*)分析前用时多了；count(1)分析后比count(1)分析前用时少了；对于提高性能来说，首先对表作分析，然后再使用count(1)就会省更多的时间。

后话：但是当表的数据量再大些时：

SQL> select count(*) from table_name;

COUNT(*)
----------
     37054

5、使用count(*)的结果：

SQL> alter session set nls_language = "American";

Session altered.

SQL> set timing on;
SQL> set autotrace on;
SQL> analyze table table_name compute statistics;

Table analyzed.

Elapsed: 00:00:28.28
SQL> select a.document_id,count(*) from table_name a group by a.document_id having count(a.document_id) >1;

DOCUMENT_ID   COUNT(*)
----------- ----------
          0        187
     317994          2

Elapsed: 00:00:05.98

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=457 Card=1844 Bytes=
          7376)

   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=457 Card=1844 Bytes=7376)
   3    2       TABLE ACCESS (FULL) OF 'table_name' (Cost=416 Card=37
          054 Bytes=148216)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       4315 consistent gets
          0 physical reads
          0 redo size
        254 bytes sent via SQL*Net to client
        242 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          2 rows processed

SQL>

6、使用count(1)的结果：

SQL> alter session set nls_language = "American";

Session altered.

SQL> set timing on;
SQL> set autotrace on;
SQL> analyze table table_name compute statistics;

Table analyzed.

Elapsed: 00:00:26.57
SQL> select a.document_id,count(1) from table_name a group by a.document_id having count(a.document_id) >1;

DOCUMENT_ID   COUNT(1)
----------- ----------
          0        187
     317994          2

Elapsed: 00:00:06.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=457 Card=1844 Bytes=
          7376)

   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=457 Card=1844 Bytes=7376)
   3    2       TABLE ACCESS (FULL) OF 'table_name' (Cost=416 Card=37
          054 Bytes=148216)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
       4315 consistent gets
          0 physical reads
          0 redo size
        254 bytes sent via SQL*Net to client
        241 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
          2 rows processed

SQL>
当表的数据量大些时，对表作分析之后，使用count(1)还要比使用count(*)用时多了！

从执行计划来看，count(1)和count(*)的效果是一样的。
但是在表做过分析之后，count(1)会比count(*)的用时少些（1w以内数据量），不过差不了多少。
这个也与表的记录数多少有关！如果1w以外的数据量，做过表分析之后，反而count(1)的用时比count(*)多了。

另外，当数据量达到10w多的时候，使用count(1)要比使用count(*)的用时稍微少点！

如果你的数据表没有主键，那么count(1)比count(*)快
如果有主键的话，那主键（联合主键）作为count的条件也比count(*)要快
如果你的表只有一个字段的话那count(*)就是最快的啦
count(*) count(1) 两者比较。主要还是要count(1)所相对应的数据字段。
如果count(1)是聚索引,id,那肯定是count(1)快。但是差的很小的。
因为count(*),自动会优化指定到那一个字段。所以没必要去count(1)，用count(*),sql会帮你完成优化的 
因此：count(1)和count(*)基本没有差别！

sql调优，主要是考虑降低：consistent gets和physical reads的数量。