COUNT(*)与COUNT(列)到底谁更快?
*count(列)当列值为空,将不被统计。
1、数据准备
--做个试验,看看到底谁更快?
drop table t purge;
create table t as select * from dba_objects;
update t set object_id =rownum ;
--设置执行计划自动跟踪(sqlplus)
set timing on
set linesize 1000
set autotrace on
2、COUNT(*)与COUNT(列)没有索引下的执行计划
没建索引一样快
select count(*) from t;
SQL> set autotrace on
SQL> select count(*) from t;
COUNT(*)
----------
72670
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 290 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 85840 | 290 (1)| 00:00:04 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
1119 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
select count(object_id) from t;
SQL> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
72670
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 291 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL| T | 85840 | 1089K| 291 (1)| 00:00:04 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
1119 consistent gets
0 physical reads
0 redo size
537 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
参数分析:
参数 | 解释 |
---|---|
recursive calls | 在用户和系统级生成的递归调用的数目 |
db block gets | 当前方式从缓冲区高速缓冲中读取的 总块数 |
consistent gets | 数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块 |
physical reads | 数据请求总数在回滚段Buffer中的数据一致性读所需要的数据块 |
redo size | 该操作产生的redo的数量,其单位为Bytes |
bytes sent via SQL*Net to client | 通过SQL*NET发送给客户端的字节 |
bytes received via SQL*Net from client | 通过SQL*NET接收给客户端的字节 |
SQL*Net roundtrips to/from client | SQL*Net往返行程 |
sorts (memory) | 在 SORT_AREA_SIZE 中的排序操作的数量 |
sorts (disk) | 在磁盘上执行的排序量 |
rows processed | 执行的行数 |
3、为列添加索引后COUNT(*)与COUNT(列)
为列object_id创建索引,COUNT(列)快
SQL> create index idx_object_id on t(object_id);
索引已创建。
SQL> set autotrace on
SQL> select count(*) from t;
COUNT(*)
----------
72670
执行计划
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 290 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 72670 | 290 (1)| 00:00:04 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1040 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
72670
执行计划
----------------------------------------------------------
Plan hash value: 1131838604
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 72670 | 46 (1)| 00:00:04 |
-------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
169 consistent gets
161 physical reads
0 redo size
537 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
4、设置列为not null
count(object_id)和count(*)一样快。
SQL> alter table T modify object_id not null;
表已更改。
SQL> select count(*) from t;
COUNT(*)
----------
72670
执行计划
----------------------------------------------------------
Plan hash value: 1131838604
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 72670 | 46 (3)| 00:00:01 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
208 recursive calls
0 db block gets
199 consistent gets
0 physical reads
0 redo size
529 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select count(object_id) from t;
COUNT(OBJECT_ID)
----------------
72670
执行计划
----------------------------------------------------------
Plan hash value: 1131838604
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_OBJECT_ID | 72670 | 46 (3)| 00:00:01 |
-------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
169 consistent gets
0 physical reads
0 redo size
537 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
总结:但是真的结论是这样的么。其实不然。其实在数据库中count(*)和count(列)根本就是不等价的,count(*)是针对于全表的,而count(列)是针对于某一列的,如果此列值为空的话,count(列)是不会统计这一行的。所以两者根本没有可比性,性能比较首先要考虑写法等价,这两个语句根本就不等价。也就失去了去比较的意义!!!
5、测试到底谁更快
SQL> SET SERVEROUTPUT ON
SQL> SET ECHO ON
SQL> DROP TABLE t;
表已删除。
SQL> DECLARE
2 l_sql VARCHAR2(32767);
3 BEGIN
4 l_sql := 'CREATE TABLE t (';
5 FOR i IN 1..25
6 LOOP
7 l_sql := l_sql || 'n' || i || ' NUMBER,';
8 END LOOP;
9 l_sql := l_sql || 'pad VARCHAR2(1000)) PCTFREE 10';
10 dbms_output.put_line('打印sql语句:'||l_sql);
11 EXECUTE IMMEDIATE l_sql;
12 END;
13 /
打印sql语句:CREATE TABLE t (n1 NUMBER,n2 NUMBER,n3 NUMBER,n4 NUMBER,n5 NUMBER,n6
NUMBER,n7 NUMBER,n8 NUMBER,n9 NUMBER,n10 NUMBER,n11 NUMBER,n12 NUMBER,n13
NUMBER,n14 NUMBER,n15 NUMBER,n16 NUMBER,n17 NUMBER,n18 NUMBER,n19 NUMBER,n20
NUMBER,n21 NUMBER,n22 NUMBER,n23 NUMBER,n24 NUMBER,n25 NUMBER,pad
VARCHAR2(1000)) PCTFREE 10
PL/SQL 过程已成功完成。
向表中填充10000条数据数据:
SQL> DECLARE
2 l_sql VARCHAR2(32767);
3 BEGIN
4 l_sql := 'INSERT INTO t SELECT ';
5 FOR i IN 1..25
6 LOOP
7 l_sql := l_sql || '0,';
8 END LOOP;
9 l_sql := l_sql || 'NULL FROM dual CONNECT BY level <= 10000';
10 dbms_output.put_line('打印sql语句:'||l_sql);
11 EXECUTE IMMEDIATE l_sql;
12 COMMIT;
13 END;
14 /
打印sql语句:INSERT INTO t SELECT
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL FROM dual CONNECT BY
level <= 10000
PL/SQL 过程已成功完成。
--以下动作观察执行速度,比较发现COUNT(*)最快,COUNT(最大列)最慢
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;
/
--结论:
--原来优化器是这么搞的:列的偏移量决定性能,列越靠后,访问的开销越大。
--由于count(*)的算法与列偏移量无关,所以count(*)最快。
--开发中将经常访问的列放到前面,推荐使用count(1)这种写法。
![](https://i-blog.csdnimg.cn/blog_migrate/07df64cf5f8739d9e4ee18585da0bd9c.jpeg#pic_center)