ITPub博客

首页 > Linux操作系统 > Linux操作系统 > count(1) 和count(*) 性能测试。

count(1) 和count(*) 性能测试。

原创 Linux操作系统 作者:杨奇龙 时间:2011-03-21 13:58:37 0 删除 编辑
总以为count(1) 比count(*)的速度要快,但是事实上经过测试并非我们想象的那样。做测试如下:
yang@rac1>create table yangstar as select * from all_objects;
Table created.
yang@rac1>desc yangstar
 Name                           Null?    Type
 ----------------------------- -------- --------------------------------------------------------
 OWNER                         NOT NULL VARCHAR2(30)
 OBJECT_NAME                   NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                         VARCHAR2(30)
 OBJECT_ID                     NOT NULL NUMBER
 DATA_OBJECT_ID                         NUMBER
 OBJECT_TYPE                            VARCHAR2(19)
 CREATED                       NOT NULL DATE
 LAST_DDL_TIME                 NOT NULL DATE
 TIMESTAMP                              VARCHAR2(19)
 STATUS                                 VARCHAR2(7)
 TEMPORARY                              VARCHAR2(1)
 GENERATED                              VARCHAR2(1)
 SECONDARY                              VARCHAR2(1)
 NAMESPACE                     NOT NULL NUMBER
 EDITION_NAME                           VARCHAR2(30)
yang@rac1>create index yangstar_index on yangstar(object_id);
Index created.
yang@rac1>declare
  2   n_count number;
  3   n_time0 number;
  4   n_time1 number;
  5   n_time2 number;
  6   n_time3 number;
  7   n_time4 number;
  8   
  9   begin
 10   execute immediate'analyze table yangstar delete statistics';
 11   
 12   select count(*) into n_count from yangstar;
 13   
 14   n_time0 := dbms_utility.get_time;
 15   for i in 1..1000 loop
 16   select /*+index(yangstar yangstar_index)*/ count(*) into n_count from yangstar;
 17   end loop;
 18   
 19   n_time1 := dbms_utility.get_time;
 20   for i in 1..1000 loop
 21   select count(*) into n_count from yangstar;
 22   end loop;
 23   n_time2 := dbms_utility.get_time;
 24   for i in 1..1000 loop
 25   select count(1) into n_count from yangstar;
 26   end loop;
 27   n_time3 := dbms_utility.get_time;
 28   for i in 1..1000 loop
 29   select count(rowid) into n_count from yangstar;
 30   end loop;
 31   n_time4 := dbms_utility.get_time;
 32  
 33   dbms_output.put_line('the count of the table T : '||to_char(n_count));
 34   dbms_output.put_line('before analyze ,loop 1000,/*+hints*/count(*) :'||to_char(n_time1 - n_time0 ));
 35   dbms_output.put_line('before analyze ,loop 1000,count(*) :'||to_char(n_time2 - n_time1));
 36   dbms_output.put_line('before analyze ,loop 1000,count(1) :'||to_char(n_time3 - n_time2));
 37   dbms_output.put_line('before analyze ,loop 1000,count(rowid) :'||to_char(n_time4 - n_time3));
 38   
 39   execute immediate'analyze table yangstar compute statistics';
 40   
 41   select count(*) into n_count from yangstar;
 42   n_time1 := dbms_utility.get_time;
 43   for i in 1..1000 loop
 44   select count(*) into n_count from yangstar;
 45   end loop;
 46   
 47   n_time2 := dbms_utility.get_time;
 48   
 49   for i in 1..1000 loop
 50   select count(1) into n_count from yangstar;
 51   end loop;
 52   n_time3 := dbms_utility.get_time;
 53   for i in 1..1000 loop
 54   select count(rowid) into n_count from yangstar;
 55   end loop;
 56   n_time4 := dbms_utility.get_time;
 57   dbms_output.put_line('the count of the table T : '||to_char(n_count));
 58   dbms_output.put_line('after analyze ,loop 1000,count(*) :'||to_char(n_time2 - n_time1));
 59   dbms_output.put_line('after analyze ,loop 1000,count(1) :'||to_char(n_time3 - n_time2));
 60   dbms_output.put_line('after analyze ,loop 1000,count(rowid) :'||to_char(n_time4 - n_time3));
 61   end;  
 62  /
the count of the table T : 73381
before analyze ,loop 1000,/*+hints*/count(*) :1628
before analyze ,loop 1000,count(*) :1628
before analyze ,loop 1000,count(1) :1453
before analyze ,loop 1000,count(rowid) :1886
the count of the table T : 73381
after analyze ,loop 1000,count(*) :227
after analyze ,loop 1000,count(1) :222
after analyze ,loop 1000,count(rowid) :474

PL/SQL procedure successfully completed.
从结果上看,统计信息前后差别很大,但是纵向来比的话,count(1)和count(*)相差不大,但是与count(rowid)。
下面附上 执行计划:
yang@rac1>select count(*) from yangstar;
Execution Plan
----------------------------------------------------------
Plan hash value: 2603293318
--------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |     1 |    46   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| YANGSTAR_INDEX | 73381 |    46   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        171  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

yang@rac1>select count(1) from yangstar;
Execution Plan
----------------------------------------------------------
Plan hash value: 2603293318
--------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |     1 |    46   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| YANGSTAR_INDEX | 73381 |    46   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        171  consistent gets
          0  physical reads
          0  redo size
        528  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

yang@rac1>select count(rowid) from yangstar;
Execution Plan
----------------------------------------------------------
Plan hash value: 2603293318

----------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |     1 |    12 |    46   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |                |     1 |    12 |            |          |
|   2 |   INDEX FAST FULL SCAN| YANGSTAR_INDEX | 73381 |   859K|    46   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        171  consistent gets
          0  physical reads
          0  redo size
        532  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
从执行计划上面也都是一样的。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22664653/viewspace-690056/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
MySQL DBA NoSQL DEVOPS

注册时间:2009-10-07

  • 博文量
    1026
  • 访问量
    7825586