Question

SQL查询速度 Select Count(1)

Select Count(1) From (

Select * from ParsedData_SH where UserId = 15020016 And NOT EXISTS (
SELECT 1 FROM ParsedData_SH b
WHERE 1 = 1
AND CONVERT(CHAR(10), ParsedData_SH.RecvTime, 120) = CONVERT(CHAR(10), b.RecvTime, 120)
AND b.RecvTime > ParsedData_SH.RecvTime
AND UserId = 15020016)

) as SL
里面子查询很快，查询每天最后一条记录，但查询共多少条的时候就很慢
什么原因，怎么优化

Answer 1

如果null参与聚集运算，则除count(*)之外其它聚集函数都忽略null。
如：
ID DD
1 e
2 null
select count(*) from table --结果是2
select count(DD) from table ---结果是1
有说count(1)效率高，感觉差不多，没啥区别。

一、关于count的一些谣言：
1、count(*)比count(val)更慢！项目组必须用count(val)，不准用count(*)，谁用扣谁钱！
2、count(*)用不到索引，count(val)才能用到。
3、count(*)是统计出全表的记录，是吞吐量的操作，肯定用不到索引。
4、count(1)比count(*)的速度快。
二、验证count(*)和count(val)
1、首先创建一个表，使用count(*)和count(val)查询比较：

----删除echo表----
SQL> drop table echo purge;
drop table echo purge
*
第 1 行出现错误:
ORA-00942: 表或视图不存在

----创建一张echo的测试表----
SQL> create table echo as select * from dba_objects;

表已创建。

SQL> update echo set object_id = rownum;

已更新72509行。

SQL> commit;

提交完成。

SQL> set timing on
SQL> set linesize 100
SQL> set autotrace on
SQL> select count(*) from echo;

COUNT(*)
----------
72509

已用时间: 00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 99109176

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 290 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| ECHO | 80064 | 290 (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
1265 consistent gets
0 physical reads
11060 redo size
425 bytes sent via SQL*Net to client
415 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(*) from echo;

COUNT(*)
----------
72509

已用时间: 00: 00: 00.01

执行计划
----------------------------------------------------------
Plan hash value: 99109176

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 290 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| ECHO | 80064 | 290 (1)| 00:00:04 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1038 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 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 echo;

COUNT(OBJECT_ID)
----------------
72509

已用时间: 00: 00: 00.01