代码改变世界

你还在争论 count(*) 与 count(column) 哪个更快?

2020-09-28 21:09  askscuti  阅读(254)  评论(0编辑  收藏  举报

count(*) 和 count(列) 哪个更快? 版本:12cR2

1. 构造测试数据

SQL> create table test as select * from dba_objects;

Table created.

SQL> update test set object_id=rownum;

72825 rows updated.

SQL> commit;

Commit complete.

SQL> set timing on
SQL> set autotrace on;

2. 对比 count(*) 与 count(列)

我们每条语句执行两次,以第二次执行结果为准(缓存)

  • COUNT(*)
SQL> select count(*) from test; (第一次)
SQL> select count(*) from test; (第二次)

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

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Cost (%CPU)| Time	  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |   387   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	  |	1 |	       |	  |
|   2 |   TABLE ACCESS FULL| TEST | 79431 |   387   (1)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  5  db block gets
       1433  consistent gets
	  0  physical reads
	  0  redo size
	544  bytes sent via SQL*Net to client
	607  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
  • COUNT(列)
SQL> select count(object_id) from test; (第一次)
SQL> select count(object_id) from test; (第二次)

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

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |    13 |   387   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	  |	1 |    13 |	       |	  |
|   2 |   TABLE ACCESS FULL| TEST | 79431 |  1008K|   387   (1)| 00:00:01 |
---------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  5  db block gets
       1433  consistent gets
	  0  physical reads
	  0  redo size
	552  bytes sent via SQL*Net to client
	607  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

观察两条SQL语句执行计划和统计信息,CPU开销都是 387,逻辑读都是 1433,谁快?

3. 添加索引

为 object_id 字段添加索引后再次查询对比

SQL> create index idx_object_id on test(object_id);

Index created.

4. 二次对比 count(*) 与 count(列)

  • COUNT(*)
SQL> select count(*) from test; (第一次)
SQL> select count(*) from test; (第二次)

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

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

-------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Cost (%CPU)| Time	  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |   387   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |	  |	1 |	       |	  |
|   2 |   TABLE ACCESS FULL| TEST | 79431 |   387   (1)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  5  db block gets
       1433  consistent gets
	  0  physical reads
	  0  redo size
	544  bytes sent via SQL*Net to client
	607  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
  • COUNT(列)
SQL> select count(object_id) from test; (第一次)
SQL> select count(object_id) from test; (第二次)

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

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1131838604

---------------------------------------------------------------------------------------
| Id  | Operation	      | Name	      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | 	      |     1 |    13 |    46	(0)| 00:00:01 |
|   1 |  SORT AGGREGATE       | 	      |     1 |    13 | 	   |	      |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 79431 |  1008K|    46	(0)| 00:00:01 |
---------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	169  consistent gets
	  0  physical reads
	  0  redo size
	552  bytes sent via SQL*Net to client
	607  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

创建索引后,观察两条SQL语句执行计划和统计信息,count(*) CPU开销是 387,逻辑读是 1433;count(object_id) CPU开销是 46,逻辑读是 169。谁快?

5. 添加非空约束

为 object_id 列添加非空 not null 约束后再次查询对比

SQL> alter table test modify object_id not null;

Table altered.

Elapsed: 00:00:00.49

6. 三次对比 count(*) 与 count(列)

  • COUNT(*)
SQL> select count(*) from test; (第一次)
SQL> select count(*) from test; (第二次)

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

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1131838604

-------------------------------------------------------------------------------
| Id  | Operation	      | Name	      | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | 	      |     1 |    46	(0)| 00:00:01 |
|   1 |  SORT AGGREGATE       | 	      |     1 | 	   |	      |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 79431 |    46	(0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	169  consistent gets
	  0  physical reads
	  0  redo size
	544  bytes sent via SQL*Net to client
	607  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
  • COUNT(列)
SQL> select count(object_id) from test; (第一次)
SQL> select count(object_id) from test; (第二次)

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

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1131838604

-------------------------------------------------------------------------------
| Id  | Operation	      | Name	      | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      | 	      |     1 |    46	(0)| 00:00:01 |
|   1 |  SORT AGGREGATE       | 	      |     1 | 	   |	      |
|   2 |   INDEX FAST FULL SCAN| IDX_OBJECT_ID | 79431 |    46	(0)| 00:00:01 |
-------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	169  consistent gets
	  0  physical reads
	  0  redo size
	552  bytes sent via SQL*Net to client
	607  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

为 object_id 列添加索引和非空约束后,观察两条SQL语句执行计划和统计信息,CPU开销都是 46,逻辑读都是 169,谁快?

7. 他们谁更快?

你走你的阳关道,我过我的独木桥!咱们井水不犯河水,两个东西不等价,谈何对比?