count(*)、count(val)和count(1)的解释

时间：2022-03-13 09:09:12

一、关于count的一些谣言：

1、count(*)比count(val)更慢！项目组必须用count(val)，不准用count(*)，谁用扣谁钱！

2、count(*)用不到索引，count(val)才能用到。

3、count(*)是统计出全表的记录，是吞吐量的操作，肯定用不到索引。

4、count(1)比count(*)的速度快。

二、验证count(*)和count(val)

1、首先创建一个表，使用count(*)和count(val)查询比较：

----删除echo表----
SQL> drop table echo purge;

drop table echo purge

           *

第 1 行出现错误:

ORA-00942: 表或视图不存在

----创建一张echo的测试表----

SQL> create table echo as select * from dba_objects;

表已创建。

SQL> update echo set object_id =  rownum;

已更新72509行。

SQL> commit;

提交完成。

SQL> set timing on

SQL> set linesize 100

SQL> set autotrace on

SQL> select count(*) from echo;

  COUNT(*)

----------

     72509

已用时间:  00: 00: 00.01

执行计划

----------------------------------------------------------

Plan hash value: 99109176

-------------------------------------------------------------------

| Id  | Operation       | Name | Rows  | Cost (%CPU)| Time      |

-------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    1 |   290   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |    1 |           |      |

|   2 |   TABLE ACCESS FULL| ECHO | 80064 |   290   (1)| 00:00:04 |

-------------------------------------------------------------------

Note

-----

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

统计信息

----------------------------------------------------------

      4  recursive calls

      0  db block gets

       1265  consistent gets

      0  physical reads

      11060  redo size

    425  bytes sent via SQL*Net to client

    415  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(*) from echo;

  COUNT(*)

----------

     72509

已用时间:  00: 00: 00.01

执行计划

----------------------------------------------------------

Plan hash value: 99109176

-------------------------------------------------------------------

| Id  | Operation       | Name | Rows  | Cost (%CPU)| Time      |

-------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    1 |   290   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |    1 |           |      |

|   2 |   TABLE ACCESS FULL| ECHO | 80064 |   290   (1)| 00:00:04 |

-------------------------------------------------------------------

Note

-----

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

统计信息

----------------------------------------------------------

      0  recursive calls

      0  db block gets

       1038  consistent gets

      0  physical reads

      0  redo size

    425  bytes sent via SQL*Net to client

    415  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 echo;

COUNT(OBJECT_ID)

----------------

       72509

已用时间:  00: 00: 00.01

执行计划

----------------------------------------------------------

Plan hash value: 99109176

---------------------------------------------------------------------------

| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    1 |    13 |   290   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |    1 |    13 |           |      |

|   2 |   TABLE ACCESS FULL| ECHO | 80064 |  1016K|   290   (1)| 00:00:04 |

---------------------------------------------------------------------------

Note

-----

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

统计信息

----------------------------------------------------------

      4  recursive calls

      0  db block gets

       1112  consistent gets

      0  physical reads

      0  redo size

    433  bytes sent via SQL*Net to client

    415  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 echo;

COUNT(OBJECT_ID)

----------------

       72509

已用时间:  00: 00: 00.01

执行计划

----------------------------------------------------------

Plan hash value: 99109176

---------------------------------------------------------------------------

| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    1 |    13 |   290   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |    1 |    13 |           |      |

|   2 |   TABLE ACCESS FULL| ECHO | 80064 |  1016K|   290   (1)| 00:00:04 |

---------------------------------------------------------------------------

Note

-----

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

统计信息

----------------------------------------------------------

      0  recursive calls

      0  db block gets

       1038  consistent gets

      0  physical reads

      0  redo size

    433  bytes sent via SQL*Net to client

    415  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(val)是一样快的。

2、建立索引做比较

SQL> create index idx_object_id on echo(object_id);

索引已创建。

已用时间:  00: 00: 05.69

SQL> select count(*) from echo;

  COUNT(*)

----------

     72509

已用时间:  00: 00: 00.05

执行计划

----------------------------------------------------------

Plan hash value: 99109176

-------------------------------------------------------------------

| Id  | Operation       | Name | Rows  | Cost (%CPU)| Time      |

-------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |    1 |   290   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |    1 |           |      |

|   2 |   TABLE ACCESS FULL| ECHO | 80064 |   290   (1)| 00:00:04 |

-------------------------------------------------------------------

Note

-----

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

统计信息

----------------------------------------------------------

      5  recursive calls

      0  db block gets

       1113  consistent gets

      0  physical reads

      0  redo size

    425  bytes sent via SQL*Net to client

    415  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 echo;

COUNT(OBJECT_ID)

----------------

       72509

已用时间:  00: 00: 00.08

执行计划

----------------------------------------------------------

Plan hash value: 1131838604

---------------------------------------------------------------------------------------

| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |           |     1 |    13 |    49    (0)| 00:00:01 |

|   1 |  SORT AGGREGATE       |           |     1 |    13 |        |          |

|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 80064 |  1016K|    49    (0)| 00:00:01 |

---------------------------------------------------------------------------------------

Note

-----

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

统计信息

----------------------------------------------------------

      4  recursive calls

      0  db block gets

    244  consistent gets

    161  physical reads

      0  redo size

    433  bytes sent via SQL*Net to client

    415  bytes received via SQL*Net from client

      2  SQL*Net roundtrips to/from client

      0  sorts (memory)

      0  sorts (disk)

      1  rows processed

哇，原来真的是用count(val)比count(*)要快啊，因为count(*)不能用到索引，而count(val)可以，真相真是如此吗？
    3、将object_id设置为非空

SQL> alter table echo modify object_id not null;

表已更改。

已用时间:  00: 00: 01.41

SQL> select count(*) from echo;

  COUNT(*)

----------

     72509

已用时间:  00: 00: 00.00

执行计划

----------------------------------------------------------

Plan hash value: 1131838604

-------------------------------------------------------------------------------

| Id  | Operation          | Name          | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |           |     1 |    49    (0)| 00:00:01 |

|   1 |  SORT AGGREGATE       |           |     1 |        |          |

|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 80064 |    49    (0)| 00:00:01 |

-------------------------------------------------------------------------------

Note

-----

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

统计信息

----------------------------------------------------------

      0  recursive calls

      0  db block gets

    169  consistent gets

      0  physical reads

      0  redo size

    425  bytes sent via SQL*Net to client

    415  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 echo;

COUNT(OBJECT_ID)

----------------

       72509

已用时间:  00: 00: 00.01

执行计划

----------------------------------------------------------

Plan hash value: 1131838604

-------------------------------------------------------------------------------

| Id  | Operation          | Name          | Rows  | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |           |     1 |    49    (0)| 00:00:01 |

|   1 |  SORT AGGREGATE       |           |     1 |        |          |

|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 80064 |    49    (0)| 00:00:01 |

-------------------------------------------------------------------------------

Note

-----

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

统计信息

----------------------------------------------------------

      0  recursive calls

      0  db block gets

    169  consistent gets

      0  physical reads

      0  redo size

    433  bytes sent via SQL*Net to client

    415  bytes received via SQL*Net from client

      2  SQL*Net roundtrips to/from client

      0  sorts (memory)

      0  sorts (disk)

      1  rows processed

--看来count(val)和count(*)其实一样快，如果索引列是非空的，count(*)可用到索引，此时一样快！真相真是如此吗？
其实两者根本没有可比性，性能比较首先考虑写法等价，这两个语句根本就不等价。

结论：

其实优化器里的算法是这么玩的，列的偏移量决定性能，列越靠后，访问的开销越大。由于count(*)的算法与列偏移量无关，所以count(*)最快，count(最后列val)最慢。

3、用实验验证上面的结论：

SQL> set serveroutput on

SQL> set echo on

SQL> drop table t purge;

表已删除。

----构造出有25个字段的表T----

DECLARE

  l_sql VARCHAR2(32767);

BEGIN

  l_sql := 'CREATE TABLE t (';

  FOR i IN 1..25

  LOOP

    l_sql := l_sql || 'n' || i || ' NUMBER,';

  END LOOP;

  l_sql := l_sql || 'pad VARCHAR2(1000)) PCTFREE 10';

  EXECUTE IMMEDIATE l_sql;

END;

 12  /

PL/SQL 过程已成功完成。

----将记录还有这个表T中填充----

DECLARE

  l_sql VARCHAR2(32767);

BEGIN

  l_sql := 'INSERT INTO t SELECT ';

  FOR i IN 1..25

  LOOP

    l_sql := l_sql || '0,';

  END LOOP;

  l_sql := l_sql || 'NULL FROM dual CONNECT BY level <= 10000';

  EXECUTE IMMEDIATE l_sql;

  COMMIT;

END;

 13  /

PL/SQL 过程已成功完成。

execute dbms_stats.gather_table_stats(ownname=>user, tabname=>'t')

SELECT num_rows, blocks FROM user_tables WHERE table_name = 'T';

PL/SQL 过程已成功完成。

SQL>

  NUM_ROWS     BLOCKS

---------- ----------

     10000       80

----以下动作观察执行速度，比较发现count(*)最快，count(最大列)最慢----

SQL> 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);

 14

  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;

 27  /

.18

.33

.39

.38

.42

.4

.45

.49

.48

.46

.48

.48

.55

.51

.56

.57

.61

.62

.75

.67

.68

.7

.73

.78

.77

.81

PL/SQL 过程已成功完成。

三、验证count(*)和count(1)
    沿用TOM大师的解释：

count(*)、count(val)和count(1)的解释

count(*)、count(val)和count(1)的解释

count(*)、count(val)和count(1)的解释

count(*)、count(val)和count(1)的解释的更多相关文章

  1. SQLSERVER 里SELECT COUNT&lpar;1&rpar; 和SELECT COUNT&lpar;&ast;&rpar;哪个性能好？

    SQLSERVER 里SELECT COUNT(1) 和SELECT COUNT(*)哪个性能好? 今天遇到某人在我以前写的一篇文章里问到 如果统计信息没来得及更新的话,那岂不是统计出来的数据时错误的 ...

  2. COUNT&lpar;&ast;&rpar;&comma;count&lpar;1&rpar;&comma;COUNT&lpar;ALL expression&rpar;，COUNT&lpar;DISTINCT expression&rpar;

    创建一个测试表 IF OBJECT_ID( 'dbo.T1' , 'U' )IS NOT NULL BEGIN DROP TABLE dbo.T1; END; GO )); GO INSERT INT ...

  3. hql中不能写count&lpar;1&rpar;能够写count&lpar;a&period;id&rpar;

    hql中不能写count(1)能够写count(a.id)里面写详细的属性 String hql="select new com.haiyisoft.vo.entity.cc.repo.Bu ...

  4. count（&ast;）、count（1）和count（列名）的区别

    count(*).count(1)和count(列名)的区别 1.执行效果上:   l  count(*)包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL l  count(1)包 ...

  5. 今天犯了个小错误：&lowbar;dataArray&period;count&gt&semi;1 和&lowbar;dataArray&period;count&gt&semi;0搞混淆了

    _dataArray.count>1 和_dataArray.count>0搞混淆了:当数据为一条时,条件不成立.应该_dataArray.count>＝1  或者>0   ( ...

  6. COUNT&lpar;&ast;&rpar;、COUNT&lpar;主键&rpar;、COUNT&lpar;1&rpar;

    MyISAM引擎,记录数是结构的一部分,已存cache在内存中; InnoDB引擎,需要重新计算,id是主键的话,会加快扫描速度: 所以select count(*)  MyISAM完胜! MyISA ...

  7. 【优化】COUNT&lpar;1&rpar;、COUNT&lpar;&ast;&rpar;、COUNT&lpar;常量&rpar;、COUNT&lpar;主键&rpar;、COUNT&lpar;ROWID&rpar;、COUNT&lpar;非空列&rpar;、COUNT&lpar;允许为空列&rpar;、COUNT&lpar;DISTINCT 列名&rpar;

    [优化]COUNT(1).COUNT(*).COUNT(常量).COUNT(主键).COUNT(ROWID).COUNT(非空列).COUNT(允许为空列).COUNT(DISTINCT 列名) 1. ...

  8. oracle count（&ast;） 和count（列）性能

    一直以为oracle中count(列)比count(*) 快,这篇文件解释了一下: http://blog.csdn.net/szstephenzhou/article/details/8446481

  9. COUNT&lpar;&ast;&rpar;&comma;count&lpar;1&rpar;&comma;COUNT&lpar;ALL expression&rpar;，COUNT&lpar;DISTINCT expression&rpar; BY Group by

    select column_2,count(column_2) as 'count(column_2)' ,count(column_1) as 'count(column_1)' ,count(*) ...

随机推荐

  1. poj2352消防站

    题目大意:有n个点的一棵树,每个点有两个值:w和c.现在要在其中若干点建立消防站,使得每个点到最近的消防站的距离不超过该点的c值,i点建立消防站的费用为w.求最小费用. 分析:本题显然是树型Dp.定义 ...

  2. PySide 简易教程&lt&semi;二&gt&semi;-------工欲善其事，必先利其器

    OK , 在Linux的开发环境下,对于我们的简短的PySide程序而言,不需要使用QtCreator,使用文本编辑器.之所以,使用文本编辑器,是因为小应用代码量很少,更重要的是一行行的写可以加深我们 ...

  3. 【BZOJ】1012&colon; &lbrack;JSOI2008&rsqb;最大数maxnumber 树状数组求区间最值

    题目链接:http://www.lydsy.com:808/JudgeOnline/problem.php?id=1012 题意:维护一个数列,开始时没有数值,之后会有两种操作, Q L :查询数列末 ...

  4. &lowbar;&lowbar;isset&lpar;&rpar;检测类内部变量是否设置

    __isset()--检测类内部私有变量是否存在 当执行isset方法时自动执行 class Per{ private $name; private $age; function __construc ...

  5. js将对象转成字符串-支持微信

    最近写一个微信项目时用到了 把对象转成字符串,因为我需要把它存在cookie中,碰到了一些问题,在这里分享一下. 要转换的就是这货~ var FBinf = { "workPlacesCod ...

  6. python标准数据类型

    Python3 中有六个标准的数据类型: Number(数字) String(字符串) List(列表) Tuple(元组) Sets(集合) Dictionary(字典) Python 中的变量不需 ...

  7. 使用SAS和JavaScript前端上传Azure Bolb大文件

    问题描述: Azure Storage Rest API提供了对于大文件分块上传方法,分别使用Put Block和Put Block List实现相关功能 参考链接: Uploading Large ...

  8. jsp 基础知识之指令元素

    由于考研和结业的事情,这里荒废了许久,而如今重新捡起来,是因为带到公司的碳素笔没有油了......    jsp的指令元素:通常以<%@开始,以%>结尾. jsp主要包括三种指令元素:pa ...

  9. 【Python3爬虫】斗鱼弹幕爬虫

    在网上找到了一份斗鱼弹幕服务器第三方接入协议v1.6.2,有了第三方接口,做起来就容易多了. 一.协议分析 斗鱼后台协议头设计如下: 这里的消息长度是我们发送的数据部分的长度和头部的长度之和,两个消息 ...

  10. MySQL之InnoDB数据页结构&lpar;转自掘金小册 MySQL是怎样运行的，版权归作者所有！&rpar;

    InnoDB为了不同的目的而设计了不同类型的页,我们把用于存放记录的页叫做数据页. 一个数据页可以被大致划分为7个部分,分别是 File Header,表示页的一些通用信息,占固定的38字节. Pag ...

相关文章