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(字段)