count(*)，count(1)，count(字段)的对比

最新推荐文章于 2022-09-03 01:32:34 发布

jzzw

最新推荐文章于 2022-09-03 01:32:34 发布

阅读量244

点赞数

分类专栏： Mysql Oracle

本文链接：https://blog.csdn.net/u014710633/article/details/102514782

版权

Oracle 同时被 2 个专栏收录

56 篇文章 1 订阅

订阅专栏

Mysql

44 篇文章 2 订阅

订阅专栏

Oracle：

参考 https://cloud.tencent.com/developer/article/1388976的方法，使用10053进行分析。
使用的版本为Oracle 11.2.0.4

SQL> alter session set events='10053 trace name context forever,level 2';

会话已更改。

SQL> create table test1012(id1 number primary key,id2 number,id3 number);

表已创建。

SQL> create unique index idx_1012_3 on test1012(id3);

索引已创建。

SQL> declare
  2  begin
  3    for i in 1 .. 10000 loop
  4      insert into test1012 values (i, i + 1, i + 2);
  5    end loop;
  6    commit;
  7  end;
  8  /

PL/SQL 过程已成功完成。

SQL> select count(*) from test.test1012;

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

SQL> select count(1) from test.test1012;

  COUNT(1)
----------
     10000

SQL> select count(id1) from test.test1012;

COUNT(ID1)
----------
     10000

SQL> select count(id2) from test.test1012;

COUNT(ID2)
----------
      9999

SQL> select count(id3) from test.test1012;

COUNT(ID3)
----------
     10000

然后去看看trace文件(只截取部分)

*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TEST"."TEST1012" "TEST1012"
kkoqbc: optimizing query block SEL$1 (#0)
        
        :
    call(in-use=1216, alloc=16344), compile(in-use=56264, alloc=58632), execution(in-use=2464, alloc=4032)

kkoqbc-subheap (create addr=0x0000000000E7FA10)
============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id  | Operation              | Name        | Rows  | Bytes | Cost  | Time      |
---------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT       |             |       |       |     7 |           |
| 1   |  SORT AGGREGATE        |             |     1 |       |       |           |
| 2   |   INDEX FAST FULL SCAN | SYS_C0011149|   10K |       |     7 |  00:00:01 |
---------------------------------------------+-----------------------------------+


CNT:   Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT:     COUNT() to COUNT(*) done.
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(1)" FROM "TEST"."TEST1012" "TEST1012"
kkoqbc: optimizing query block SEL$1 (#0)
        
        :
    call(in-use=1240, alloc=16344), compile(in-use=56384, alloc=58632), execution(in-use=2464, alloc=4032)

kkoqbc-subheap (create addr=0x0000000000E7FA10)
============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id  | Operation              | Name        | Rows  | Bytes | Cost  | Time      |
---------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT       |             |       |       |     7 |           |
| 1   |  SORT AGGREGATE        |             |     1 |       |       |           |
| 2   |   INDEX FAST FULL SCAN | SYS_C0011149|   10K |       |     7 |  00:00:01 |
---------------------------------------------+-----------------------------------+
** Using dynamic sampling card. : 10000
** Dynamic sampling updated table card.
  Table: TEST1012  Alias: TEST1012
    Card: Original: 10000.000000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  9.05  Resp: 9.05  Degree: 0
      Cost_io: 9.00  Cost_cpu: 1699400
      Resp_io: 9.00  Resp_cpu: 1699400
  Access Path: index (index (FFS))
    Index: SYS_C0011149
    resc_io: 7.00  resc_cpu: 442429
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  7.01  Resp: 7.01  Degree: 1
      Cost_io: 7.00  Cost_cpu: 442429
      Resp_io: 7.00  Resp_cpu: 442429
  Access Path: index (FullScan)
    Index: SYS_C0011149
    resc_io: 21.00  resc_cpu: 649550
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
    Cost: 21.02  Resp: 21.02  Degree: 1
******** Begin index join costing ********
  ****** trying bitmap/domain indexes ******
  Access Path: index (FullScan)
    Index: SYS_C0011149
    resc_io: 21.00  resc_cpu: 649550
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
    Cost: 21.02  Resp: 21.02  Degree: 0
  Access Path: index (FullScan)
    Index: SYS_C0011149
    resc_io: 21.00  resc_cpu: 649550
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
    Cost: 21.02  Resp: 21.02  Degree: 0
  Bitmap nodes:
    Used SYS_C0011149
      Cost = 26.273311, sel = 1.000000
  ****** finished trying bitmap/domain indexes ******
******** End index join costing ********
  Best:: AccessPath: IndexFFS
  Index: SYS_C0011149
         Cost: 7.01  Degree: 1  Resp: 7.01  Card: 10000.00  Bytes: 0

***************************************


CNT:   Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT:     Converting COUNT(ID1) to COUNT(*).
CNT:     COUNT() to COUNT(*) done.
query block SEL$1 (#0) unchanged
*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(ID1)" FROM "TEST"."TEST1012" "TEST1012"
kkoqbc: optimizing query block SEL$1 (#0)
        
        :
    call(in-use=1240, alloc=16344), compile(in-use=56752, alloc=58632), execution(in-use=2464, alloc=4032)

kkoqbc-subheap (create addr=0x0000000000E7FA10)
============
Plan Table
============
---------------------------------------------+-----------------------------------+
| Id  | Operation              | Name        | Rows  | Bytes | Cost  | Time      |
---------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT       |             |       |       |     7 |           |
| 1   |  SORT AGGREGATE        |             |     1 |       |       |           |
| 2   |   INDEX FAST FULL SCAN | SYS_C0011149|   10K |       |     7 |  00:00:01 |
---------------------------------------------+-----------------------------------+



CNT:   Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT:     COUNT() to COUNT(*) not done.
query block SEL$1 (#0) unchanged
*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT("TEST1012"."ID2") "COUNT(ID2)" FROM "TEST"."TEST1012" "TEST1012"
kkoqbc: optimizing query block SEL$1 (#0)
        
        :
    call(in-use=1240, alloc=16344), compile(in-use=56752, alloc=58632), execution(in-use=2464, alloc=4032)

kkoqbc-subheap (create addr=0x0000000000E7FA10)
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |     9 |           |
| 1   |  SORT AGGREGATE     |         |     1 |    13 |       |           |
| 2   |   TABLE ACCESS FULL | TEST1012|   10K |  127K |     9 |  00:00:01 |
--------------------------------------+-----------------------------------+


CNT:   Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT:     COUNT() to COUNT(*) not done.
query block SEL$1 (#0) unchanged
*******************************************
Peeked values of the binds in SQL statement
*******************************************

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT("TEST1012"."ID3") "COUNT(ID3)" FROM "TEST"."TEST1012" "TEST1012"
kkoqbc: optimizing query block SEL$1 (#0)
        
        :
    call(in-use=1240, alloc=16344), compile(in-use=56752, alloc=58632), execution(in-use=2464, alloc=4032)

kkoqbc-subheap (create addr=0x0000000000E7FA10)
============
Plan Table
============
-------------------------------------------+-----------------------------------+
| Id  | Operation              | Name      | Rows  | Bytes | Cost  | Time      |
-------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT       |           |       |       |     7 |           |
| 1   |  SORT AGGREGATE        |           |     1 |    13 |       |           |
| 2   |   INDEX FAST FULL SCAN | IDX_1012_3|   10K |  127K |     7 |  00:00:01 |
-------------------------------------------+-----------------------------------+

我们可以发现，每一步都有一个 CNT: Considering count(col) to count(*)，是不是Oracle在做这种count()聚合查询时，都在尝试能不能转换成count(*)，如果可以的话就直接将sql进行转换，例如：

SELECT COUNT(*) "COUNT(1)" FROM "TEST"."TEST1012" "TEST1012"

看似count(1),实则count(*)。理所当然的，他们的执行计划也是一样的，都是对主键的index fast full scan

所以如果只是为了统计行数，我们可以认为count(*)=count(1)=count(主键)
count(字段)的话，更多的是业务上的需求(去掉null值的统计)，因为有个筛选的过程，所以相比来说肯定是要慢点。

Mysql：

我们这里就只讨论InnoDB了。我的Mysql版本是5.7.19。
和Oracle的区别是，InnoDB的表是IOT，所以对于Mysql来说，二级索引上的信息也足够我们进行count()的，在count(1)和count(*)时，Mysql会选取它认为最快的遍历方式，比如我这实验：

mysql> show create table emp3;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                    |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp3  | CREATE TABLE `emp3` (
  `id` int(10) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptid` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_emp3_salary` (`salary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

mysql> desc select count(id) from emp3;
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key             | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp3  | NULL       | index | NULL          | idx_emp3_salary | 5       | NULL | 997998 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> desc select count(*) from emp3;
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key             | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp3  | NULL       | index | NULL          | idx_emp3_salary | 5       | NULL | 997998 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> desc select count(1) from emp3;
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key             | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp3  | NULL       | index | NULL          | idx_emp3_salary | 5       | NULL | 997998 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select count(name) from emp3;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | SIMPLE      | emp3  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997998 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select count(salary) from emp3;
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key             | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | emp3  | NULL       | index | NULL          | idx_emp3_salary | 5       | NULL | 997998 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------------+---------+------+--------+----------+-------------+

可以看到，Mysql选择了遍历idx_emp3_salary这个二级索引。
对于InnoDB来说，在遍历之后，InnoDB会把结果取出来，返回给Server层，然后由Server层进行非空判断并累加。
但是 count(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。
(参考：https://time.geekbang.org/column/article/72775)

从官方文档上我们也可以看到：
Prior to MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index. As of MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, the clustered index is scanned.
InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

所以对于InnoDB，我们可以认为count(*)≈count(1)>count(主键)>count(字段)

jzzw

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
count(*)，count(1)，count(字段)的对比

Oracle：参考 https://cloud.tencent.com/developer/article/1388976的方法，使用10053进行分析。使用的版本为Oracle 11.2.0.4SQL> alter session set events='10053 trace name context forever,level 2';会话已更改。SQL> cr...
复制链接

扫一扫