sn_id字段为主键,以下是 count(sn_id) 和 count(*)
执行的时间, 想问, 为何 count(*) 会比count(sn_id) 快? SELECT
count(sn_id)
FROM
sn {executed in 235 msec} SELECT
count(sn_id)
FROM
sn {executed in 187 msec}
SELECT
count(sn_id)
FROM
sn {executed in 219 msec}
SELECT
count(sn_id)
FROM
sn {executed in 266 msec} SELECT
count(sn_id)
FROM
sn {executed in 203 msec}=====================
SELECT
count(*)
FROM
sn {executed in 188 msec} SELECT
count(*)
FROM
sn {executed in 172 msec} SELECT
count(*)
FROM
sn {executed in 172 msec} SELECT
count(*)
FROM
sn {executed in 172 msec}
SELECT
count(*)
FROM
sn {executed in 156 msec}
SELECT
count(*)
FROM
sn {executed in 172 msec}
执行的时间, 想问, 为何 count(*) 会比count(sn_id) 快? SELECT
count(sn_id)
FROM
sn {executed in 235 msec} SELECT
count(sn_id)
FROM
sn {executed in 187 msec}
SELECT
count(sn_id)
FROM
sn {executed in 219 msec}
SELECT
count(sn_id)
FROM
sn {executed in 266 msec} SELECT
count(sn_id)
FROM
sn {executed in 203 msec}=====================
SELECT
count(*)
FROM
sn {executed in 188 msec} SELECT
count(*)
FROM
sn {executed in 172 msec} SELECT
count(*)
FROM
sn {executed in 172 msec} SELECT
count(*)
FROM
sn {executed in 172 msec}
SELECT
count(*)
FROM
sn {executed in 156 msec}
SELECT
count(*)
FROM
sn {executed in 172 msec}
查看他们的执行计划,都是走的主键索引!
count(*) 是oracle内部针对表已经做好处理的,以前有中说法叫count 某列会快些的说法,其实是不然的!再个 count 某列 的结果 与 count(*)的结果不一定会一样,当某列为空时 则不会就行数!
insert #aa(f) select 1 union all select 2 union all select 2 union all select 3 union all select 3 union all select 3 union all select 4 union all select 4 union all select 4 union all select 4 union all select null
select count(*) from #aa
select count(f) from #aa
go
首先要 先谢谢您的解析,
因为项目需要,select count() 需要经常操作,并且并发量比较大,数据量又特别的大
所以才发了自己的一些想法出来比较,请教各位大虾而已
项目中需要 select count( )需要经常操作,这个几乎每个项目都会经常用到的,一般的分页都会用到,经常是先count(*) 一次得到总页数 ,再做操作的。
用count(*) 完全没问题,oracle内部已经对count(*)做过处理的,而且count(列)的数据结果不一定正确。刚学的时候谁都会这样做,直接比对查询结果的速度!这是最正常不过的思维了,不会像某些码侬写了代码根本就不管什么效率的事情了,已经非常不错了。再个这种查询 直接比对下他们的执行计划就知道了,执行方式都是通过 走 主键索引来统计的!
如果count(*) 包括s_id is null 走全表扫描。
这个用执行计划很容易证明。