二十六、COUNT(*)与COUNT（列）到底谁更快？

于 2018-12-08 21:04:47 发布
阅读量899 收藏
点赞数
分类专栏： Oracle 文章标签： count（*）） count（列）
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/newbie_907486852/article/details/84893919
版权

        COUNT(*)与COUNT(列)到底谁更快？

*count（列）当列值为空，将不被统计。

1、数据准备

以下命令执行有问题请参照上篇文章

--做个试验，看看到底谁更快？
drop table t purge;
create table t as select * from dba_objects;
update t set object_id =rownum ;

--设置执行计划自动跟踪（sqlplus）
set timing on 
set linesize 1000
set autotrace on

2、COUNT(*)与COUNT（列）没有索引下的执行计划

没建索引一样快

select count(*) from t;


SQL> set autotrace on
SQL> select count(*) from t;

  COUNT(*)
----------
     72670


执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   290   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 85840 |   290   (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       1119  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

select count(object_id) from t;

SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
           72670


执行计划
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   291   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 85840 |  1089K|   291   (1)| 00:00:04 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       1119  consistent gets
          0  physical reads
          0  redo size
        537  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

参数分析：

参数解释
recursive calls在用户和系统级生成的递归调用的数目
db block gets当前方式从缓冲区高速缓冲中读取的 总块数
consistent gets数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块
physical reads数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块
redo size该操作产生的redo的数量，其单位为Bytes
bytes sent via SQL*Net to client通过SQL*NET发送给客户端的字节
bytes received via SQL*Net from client通过SQL*NET接收给客户端的字节
SQL*Net roundtrips to/from clientSQL*Net往返行程
sorts (memory)在 SORT_AREA_SIZE 中的排序操作的数量
sorts (disk)在磁盘上执行的排序量
rows processed执行的行数

3、为列添加索引后COUNT(*)与COUNT（列）

为列object_id创建索引，COUNT（列）快


SQL> create index idx_object_id on t(object_id);

索引已创建。

SQL> set autotrace on
SQL> select count(*) from t;

  COUNT(*)
----------
     72670


执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   290   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 72670 |   290   (1)| 00:00:04 |
-------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1040  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
           72670


执行计划
----------------------------------------------------------
Plan hash value: 1131838604



-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   46   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 72670 |   46   (1)| 00:00:04 |
-------------------------------------------------------------------

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        169  consistent gets
        161  physical reads
          0  redo size
        537  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

4、设置列为not null

count(object_id)和count(*)一样快。


SQL> alter table T modify object_id  not  null;

表已更改。

SQL> select count(*) from t;

  COUNT(*)
----------
     72670


执行计划
----------------------------------------------------------
Plan hash value: 1131838604

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    46   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 72670 |    46   (3)| 00:00:01 |
-------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
        208  recursive calls
          0  db block gets
        199  consistent gets
          0  physical reads
          0  redo size
        529  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
           72670


执行计划
----------------------------------------------------------
Plan hash value: 1131838604

-------------------------------------------------------------------------------
| Id  | Operation             | Name          | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |     1 |    46   (3)| 00:00:01 |
|   1 |  SORT AGGREGATE       |               |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 72670 |    46   (3)| 00:00:01 |
-------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        169  consistent gets
          0  physical reads
          0  redo size
        537  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

      总结：但是真的结论是这样的么。其实不然。其实在数据库中count(*)和count(列)根本就是不等价的，count(*)是针对于全表的，而count(列)是针对于某一列的，如果此列值为空的话，count(列)是不会统计这一行的。所以两者根本没有可比性，性能比较首先要考虑写法等价，这两个语句根本就不等价。也就失去了去比较的意义！！！

5、测试到底谁更快

SQL> SET SERVEROUTPUT ON
SQL> SET ECHO ON
SQL> DROP TABLE t;

表已删除。

SQL> DECLARE
  2    l_sql VARCHAR2(32767);
  3  BEGIN
  4    l_sql := 'CREATE TABLE t (';
  5    FOR i IN 1..25
  6    LOOP
  7      l_sql := l_sql || 'n' || i || ' NUMBER,';
  8    END LOOP;
  9    l_sql := l_sql || 'pad VARCHAR2(1000)) PCTFREE 10';
 10    dbms_output.put_line('打印sql语句:'||l_sql);
 11    EXECUTE IMMEDIATE l_sql;
 12  END;
 13  /
打印sql语句:CREATE TABLE t (n1 NUMBER,n2 NUMBER,n3 NUMBER,n4 NUMBER,n5 NUMBER,n6
NUMBER,n7 NUMBER,n8 NUMBER,n9 NUMBER,n10 NUMBER,n11 NUMBER,n12 NUMBER,n13
NUMBER,n14 NUMBER,n15 NUMBER,n16 NUMBER,n17 NUMBER,n18 NUMBER,n19 NUMBER,n20
NUMBER,n21 NUMBER,n22 NUMBER,n23 NUMBER,n24 NUMBER,n25 NUMBER,pad
VARCHAR2(1000)) PCTFREE 10

PL/SQL 过程已成功完成。

向表中填充10000条数据数据：

SQL> DECLARE
  2    l_sql VARCHAR2(32767);
  3  BEGIN
  4    l_sql := 'INSERT INTO t SELECT ';
  5    FOR i IN 1..25
  6    LOOP
  7      l_sql := l_sql || '0,';
  8    END LOOP;
  9    l_sql := l_sql || 'NULL FROM dual CONNECT BY level <= 10000';
 10    dbms_output.put_line('打印sql语句:'||l_sql);
 11    EXECUTE IMMEDIATE l_sql;
 12    COMMIT;
 13  END;
 14  /
打印sql语句:INSERT INTO t SELECT
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL FROM dual CONNECT BY
level <= 10000

PL/SQL 过程已成功完成。

--以下动作观察执行速度，比较发现COUNT(*)最快，COUNT(最大列）最慢
DECLARE
  l_dummy PLS_INTEGER;
  l_start PLS_INTEGER;
  l_stop PLS_INTEGER;
  l_sql VARCHAR2(100);
BEGIN
  l_start := dbms_utility.get_time;
  FOR j IN 1..1000
  LOOP
    EXECUTE IMMEDIATE 'SELECT count(*) FROM t' INTO l_dummy;
  END LOOP;
  l_stop := dbms_utility.get_time;
  dbms_output.put_line((l_stop-l_start)/100);

  FOR i IN 1..25
  LOOP
    l_sql := 'SELECT count(n' || i || ') FROM t';
    l_start := dbms_utility.get_time;
    FOR j IN 1..1000
    LOOP
      EXECUTE IMMEDIATE l_sql INTO l_dummy;
    END LOOP;
    l_stop := dbms_utility.get_time;
    dbms_output.put_line((l_stop-l_start)/100);
  END LOOP;
END;
/

--结论：
--原来优化器是这么搞的：列的偏移量决定性能，列越靠后，访问的开销越大。
--由于count(*)的算法与列偏移量无关，所以count(*)最快。
--开发中将经常访问的列放到前面，推荐使用count（1）这种写法。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值