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

can_do

浏览: 263956 次
性别:
来自: 北京

最近访客更多访客>>

WoKo_Jb

limengna845567

bitzgx

raymond.chen

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

count(*)count(1)database sql

数据库表的记录数为：

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(*)基本没有差别！

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

【编写于 2009-01-20】

分享到：