代码改变世界

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

2020-09-28 21:09  askscuti  阅读(436)  评论(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. 他们谁更快？

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