浅谈Oracle中COUNT函数的使用与性能问题

SQL干货分享 2021-04-19

2733

（CSDN博主：写代码也要符合基本法）

长久以来，关于 Oracle 中 COUNT 函数的使用可谓众说纷纭，江湖门派林立，直叫人摸不着头(tou)脑(fa)。

今天就让我们走近科学，一起揭开 COUNT 函数的神秘面纱。

首先我们准备一张科(sang)学(xin)合(bing)理(kuang)的拥有50个数字列的表 CUX_COUNT_TEST，并用十万行随机正整数把它一下子填满。

首先，我们来明确一点，作为聚合函数的一员，COUNT 同样会忽略空值，只统计非空列值的数目，例如：

SQL> SELECT COUNT(NULL) FROM cux_count_test;


COUNT(NULL)
-----------
          0

十万行数据直接给你整没

由此我们可以得出 COUNT(1) 和 COUNT(COL) 的区别：前者可以统计返回结果集中的实际行数，而后者统计的是结果集中指定列值不为空的行数。

市面上还广为流传着另一种写法 COUNT(*)，上文说聚合函数会忽略空值，那么这种写法岂不是会忽略空行？看似很有道理，实则完全错误，因为 COUNT 的参数只是一个值而不是记录啊，所以这里的 * 号不宜理解为结果集中的所有列。

COUNT 函数的参数只是一个值，事实上，世间的值在 COUNT 眼里只有两个：一是空值，一是非空。色不异空，空不异色，带师，我悟了！

所以，在使用 COUNT 统计行数时，只要不输入空值，是骡子是马子真无所谓，举个极端的例子：

SQL> SELECT COUNT(*) FROM (SELECT NULL, NULL FROM dual);


  COUNT(*)
----------
         1

由此我们可知，COUNT(*) 和 COUNT(COL) 的区别：同上。

接下来，我们引入一些搞基高级话题，对比一下 COUNT 各种用法的性能之差。

如前所述，COUNT(*) 中的 * 号并不是所有列的意思，所以它和 COUNT(1)等参数中赋予无意义的非空值的性能是一样的，多数场景下也是极好的，因为这样做优化器会忽略结果集实际的数据大小，减少了计算中的字节数。

SQL> SELECT COUNT(*) FROM cux_count_test;


----------------------------------------------------------
| Id  | Operation          | Name           | Cost (%CPU)|
----------------------------------------------------------
|   0 | SELECT STATEMENT   |                |   759 (100)|
|   1 |  SORT AGGREGATE    |                |            |
|   2 |   TABLE ACCESS FULL| CUX_COUNT_TEST |   759   (1)|
----------------------------------------------------------

执行计划中甚至没有 BYTES 信息。

讨论完虚拟列的情况，那么使用数据列除了会带来字节大小的影响，使用不同的列之间，是否又有区别呢？

为了增强对比，我们将实验表中的每一列分别做 100 次 COUNT 计算，来比较它们之间用时的差别：

DECLARE
  l_begin_time NUMBER;
  l_end_time   NUMBER;
  l_count      NUMBER;
BEGIN
  FOR j IN 1 .. 50 LOOP
    l_begin_time := dbms_utility.get_time;
    FOR k IN 1 .. 100 LOOP
      EXECUTE IMMEDIATE 'select count(col' || j || ') from cux_count_test'
        INTO l_count;
    END LOOP;
    l_end_time := dbms_utility.get_time;
    dbms_output.put_line((l_end_time - l_begin_time)  100);
  END LOOP;
END;

将运行结果复制到 Excel，使用生涩的手法制作一张折线图：

可见，列在表中的位置越靠后，对其做聚合统计所需时间就越多。

这是因为，虽然集合是无序的，但在每一行中，各列的位置是有序的，这个顺序就是表结构中各列的先后顺序。

COUNT 函数要判断列值是否为空，这就意味着计算过程中不光要扫描表数据块中的每一行，指针还要在每一行中再移动以找到指定的列值。

所以，如果语句指定 COUNT 统计的列有非空约束，也可提升语句执行性能。

例如，当我们给实验表中第一列和最后一列都加上非空约束后，再做类似的测试我们发现，不仅执行时间相差甚微，而且两个语句的执行计划中也可看出，优化器直接忽略了两列的字节数，这意味着实际上并没有访问列中的值。

SQL> SELECT COUNT(col1) FROM cux_count_test;


----------------------------------------------------------
| Id  | Operation          | Name           | Cost (%CPU)|
----------------------------------------------------------
|   0 | SELECT STATEMENT   |                |   759 (100)|
|   1 |  SORT AGGREGATE    |                |            |
|   2 |   TABLE ACCESS FULL| CUX_COUNT_TEST |   759   (1)|
----------------------------------------------------------


SQL> SELECT COUNT(col50) FROM cux_count_test;


----------------------------------------------------------
| Id  | Operation          | Name           | Cost (%CPU)|
----------------------------------------------------------
|   0 | SELECT STATEMENT   |                |   759 (100)|
|   1 |  SORT AGGREGATE    |                |            |
|   2 |   TABLE ACCESS FULL| CUX_COUNT_TEST |   759   (1)|
----------------------------------------------------------

实现非空约束，让充满哲学意味的 COUNT 函数一眼就看出你不是人列中不过是平平无奇的各种非空值而已。

除了非空约束，利用索引也是提升 COUNT 计算性能的一个选择。

原理至少有两个，单列的B树索引不会存储列中的空值，如此在只计算索引列的行数时，访问索引结构完全无需回表。

例如我们在实验表第一列增加索引后再观察执行计划的变化：

SQL> SELECT COUNT(col1) FROM cux_count_test;


-----------------------------------------------------------
| Id  | Operation        | Name              | Cost (%CPU)|
-----------------------------------------------------------
|   0 | SELECT STATEMENT |                   |   224 (100)|
|   1 |  SORT AGGREGATE  |                   |            |
|   2 |   INDEX FULL SCAN| CUX_COUNT_TEST_N1 |   224   (2)|
-----------------------------------------------------------

不仅完全忽略了字节信息，而且执行成本大大降低，这是因为语句仅访问了体积更小的索引块，而不需再访问表数据块。

最后，回归到 COUNT 执行的工作本质上，无非就是数数，此刻我们关注编码需求本身，如果可以让 COUNT 去数更少行数的结果集就能满足我们的需要，岂不美哉？

比如，我们只想知道表中是否存在符合某些条件的记录，不会真的有人会实在到把这些记录每一行都去数一遍吧，不会吧，不会吧？

SQL> SELECT COUNT(1) FROM cux_count_test WHERE col2 = 1;


------------------------------------------------------------------
| Id  | Operation          | Name           | Bytes | Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                |       |   760 (100)|
|   1 |  SORT AGGREGATE    |                |    13 |            |
|*  2 |   TABLE ACCESS FULL| CUX_COUNT_TEST |  1144 |   760   (1)|
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("COL2"=1)


SQL> SELECT COUNT(1)
  2    FROM cux_count_test
  3   WHERE col2 = 1
  4     AND rownum = 1;


-------------------------------------------------------------------
| Id  | Operation           | Name           | Bytes | Cost (%CPU)|
-------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |       |   760 (100)|
|   1 |  SORT AGGREGATE     |                |    13 |            |
|*  2 |   COUNT STOPKEY     |                |       |            |
|*  3 |    TABLE ACCESS FULL| CUX_COUNT_TEST |  1144 |   760   (1)|
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM=1)
   3 - filter("COL2"=1)

观察上例，我们发现，使用 ROWNUM 限制条件后，执行计划中多出了 COUNT STOPKEY 这一操作，这是为使用 ROWNUM 限制条件的语句量身定制的一种操作，它意味着一旦 ROWNUM 条件达成时则停止继续查询，从而节省扫描全表数据带来的耗时。

oracle

文章转载自SQL干货分享，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

浅谈Oracle中COUNT函数的使用与性能问题

评论