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

最新推荐文章于 2023-12-26 11:30:37 发布
最新推荐文章于 2023-12-26 11:30:37 发布
阅读量979 收藏
点赞数
文章标签： sql 性能 测试 count

一、关于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)查询比较：

<span style="font-size:14px;">----删除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(*)
----------

已用时间:  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)


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

SQL> select count(*) from echo;

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

已用时间:  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)


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

SQL> select count(object_id) from echo;

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

已用时间:  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)


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

SQL> select count(object_id) from echo;

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

已用时间:  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)


统计信息
----------------------------------------------------------
 recursive calls
 db block gets
 consistent gets
 physical reads
 redo size
 bytes sent via SQL*Net to client
 bytes received via SQL*Net from client
 SQL*Net roundtrips to/from client
 sorts (memory)
 sorts (disk)
 rows processed</span>

从上面的执行计划可以看出count(*)和count(val)是一样快的。

    2、建立索引做比较

<span style="font-size:14px;">SQL> create index idx_object_id on echo(object_id);

索引已创建。

已用时间:  00: 00: 05.69
SQL> select count(*) from echo;

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

已用时间:  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)


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

SQL> select count(object_id) from echo;

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

已用时间:  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)


统计信息
----------------------------------------------------------
 recursive calls
 db block gets
 consistent gets
 physical reads
 redo size
 bytes sent via SQL*Net to client
 bytes received via SQL*Net from client
 SQL*Net roundtrips to/from client
 sorts (memory)
 sorts (disk)
 rows processed</span>

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

<span style="font-size:14px;">SQL> alter table echo modify object_id not null;

表已更改。

已用时间:  00: 00: 01.41

SQL> select count(*) from echo;

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

已用时间:  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)


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

SQL> select count(object_id) from echo;

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

已用时间:  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)


统计信息
----------------------------------------------------------
 recursive calls
 db block gets
 consistent gets
 physical reads
 redo size
 bytes sent via SQL*Net to client
 bytes received via SQL*Net from client
 SQL*Net roundtrips to/from client
 sorts (memory)
 sorts (disk)
 rows processed</span>

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

结论：

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

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

<span style="font-size:14px;">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;
 /

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;
 /

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
---------- ----------
      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);
 
  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;
 /
.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 过程已成功完成。</span>

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

 
十二试炼
关注
MySQL count(1) count(*) 比较 详解
chenshun123的博客
03-24 2万+
count(*) 将返回表格中所有存在的行的总数包括值为 null 的行，然而 count(列名) 将返回表格中除去 null 以外的所有行的总数 (有默认值的列也会被计入)，这点对于所有数据的 COUNT 计算都是一样的count(1) 与 count(*) 比较 :1&gt; 如果数据表没有主键，那么 count(1) 比 count(*) 快2&gt; 如果有主键的话，那主键 (联合主键) ...
自记录-mysql-count函数
weixin_40955206的博客
12-23 171
mysql.count
count（*）和 count（1）
beichyes的博客
04-12 2088
是 SQL 标准语法并且在大多数情况下都可以正常使用，包括处理包含 NULL 值的行。所以在实际使用中，如果你需要统计某个表中的所有行数，那么使用。是较为保险和稳妥的做法。但是如果你关心性能，或者只想统计非空行的数量，那么可以考虑使用其他更高效或更精确的方法。总之，如果你只是想要统计行数，而不用关心 NULL 值或其他列的值，那么建议使用。
count(*) count(1)与count(字段)的区别
weixin_46200547的博客
08-31 6088
一、执行结果 count(*) 和count(1) 都是统计行数，而count(col) 是统计col列非null的行数 二、执行计划 MyISAM与InnoDB，正如在不同的存储引擎中，count(*)函数的执行是不同的 在MyISAM存储引擎中，count(*)函数是直接读取数据表保存的行记录数并返回,效率很高，但是如果添加了where条件的话，MyISAM表也不能返回得很快。 在InnoDB存储引擎中，count(*)函数是先从内存中读取表中的数据到内存缓冲区，然后扫描全表获得行记录数。在使用coun
mysql中count(), group by, order by使用详解
09-09
在MySQL中，`COUNT()`, `GROUP BY`, 和 `ORDER BY` 是三个非常重要的SQL语句组成部分，它们各自承担着不同的职责，同时也常被结合在一起使用以满足复杂的数据查询需求。 `COUNT()` 是一个聚合函数，它用于计算指定...
count_sort.zip_count_sort_in
09-21
std::vector<int> count(max_val - min_val + 1, 0); // 计数 for (int num : arr) { count[num - min_val]++; } // 累加计数 for (int i = 1; i < count.size(); ++i) { count[i] += count[i - 1]; } ...
count(1)和count(*)详解
学渣渣
07-19 843
如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null。如：     ID     DD      1      e      2    null     select  count(*) from table  --结果是2     select  count(DD)  from table  ---结果是1 效率问题： 当表的数据量大些时，对表作分析之后，使...
深入理解PHP中的count函数
10-22
$val1 = count($arr1); // $val1的值将会是5 ``` 2. 使用COUNT_RECURSIVE模式计算多维数组： ```php $arr2 = array('apple', 'banana', array('cat', 'camel'), 'dog'); $val2_1 = count($arr2); // $val2_1的值将...
COUNT(1)和COUNT(*) 的区别
xintingandzhouyang的专栏
03-21 1万+
两者本质上没有任何区别。 参见MYSQL 参考手册 InnoDBhandlesSELECT COUNT(*)andSELECT COUNT(1)operations in the same way. There is no performance difference. 一、count情况 1、count(1)：可以统计表中所有数据，不统计所有的列，...
(count(*)和count(1)的区别
weixin_52860572的博客
06-06 6412
(count(*)和count(1)的区别
count(1)、count(*)、count(列名)的含义、区别、执行效率
热门推荐
宝付支的博客
09-19 5万+
维护同事的代码发现他的 sql 查询记录条数都是 count(1)，而我则习惯用 count(*)，这有什么区别呢？ 含义： 1、count(*) ：统计所有的行数，包括为null的行（COUNT(*)不单会进行全表扫描，也会对表的每个字段进行扫描。而COUNT('x')或者COUNT(COLUMN)或者COUNT(0)等则只进行一个字段的全表扫描）。 2、count(1)：计算一共有多少符...
count(1)和count(*)区别详述
不负韶话
03-14 3011
count(1)和count(*)区别详述
count(*)和count(1)
IOT_player的博客
12-18 815
count(*)、count(1)或者是count(1000)在InnoDB储存引擎中没有性能区别。MySQL5.5之后默认使用InnoDB。
Spark实现SQL的count(*),count(distinct * *) 即pv、uv
IBoyMan
07-03 8439
自己上代码，如果不理解的地方，可以百度或留言import org.apache.spark.{SparkConf,SparkContext} /** * Created by zhang on 2018/6/29. */ object WordCount { def dealTime(line:String) ={ line.substring(0,10) } def ...
MySQL 中 count(*) 和 count(1) 有什么区别？哪个性能最好？
小林coding
01-06 2万+
大家好，我是小林。 当我们对一张数据表中的记录进行统计的时候，习惯都会使用 count 函数来统计，但是 count 函数传入的参数有很多种，比如 count(1)、count(*)、count(字段) 等。 到底哪种效率是最好的呢？是不是 count(*) 效率最差？ 我曾经以为 count(*) 是效率最差的，因为认知上 selete * from t 会读取所有表中的字段，所以凡事带有 * 字符的就觉得会读取表中所有的字段，当时网上有很多博客也这么说。 但是，当我深入 count 函数的原理后，被啪啪
【MySQL进阶】count(*) 、count(1)、count(字段)到底有什么区别？哪种性能好？
blblccc
12-30 348
【MySQL进阶】count(*) 、count(1)、count(字段)到底有什么区别？哪种性能好？
count(*) 和 count(1)和count()区别
最新发布
gengxiao3的博客
12-26 512
所以没必要去count(1)，用count(*)，sql会帮你完成优化，所以count(1)和count()基本没有差别。count(字段)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数， 即某个字段值为NULL时，不统计。从执行计划看，count(1)和count(*)的效果是一样的。当表的数据量比较大，对表作分析之后，使用count(1)比使用count(*)用时多。三、count(*) 和 count(1)和count(指定字段)区别。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值