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

末蓝、 2022-04-06 05:48 202阅读 0赞

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

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

### 1、数据准备 ###

[以下命令执行有问题请参照上篇文章][Link 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

参数分析：

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

### 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）这种写法。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center][]

[Link 1]: https://blog.csdn.net/newbie_907486852/article/details/84893393
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center]: /images/20220406/fa3c4cc549354ef7901e81fe2426c510.png