count(*)内幕? 有主键吗? 试试下面的是不是快些:select count(KeyField) from tablename 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 count(加索引的字段)速度会很快! 我知道在mysql 中 count(*) 是属于 优化的语句。速度非常的快,但在其他数据库中就不太清楚了。好像在ACCESS 中根本就用 count(*) 得不出结果来 我试了一下count(1)、count(索引的字段)、count(*)的速度是一样的。看以来ORACLE在执行count(*)的时候会自动按照最佳索引查找。正在找关于oracle tuning的资料,找到以后再和大家交流。To: MountLion,看来你还要看看服务器的优化,恐怕从SQL方面很难提高 asktom上说count(*), count(1), count(uniquecolumn)应该完全一样(没有任何区别)。不过,我做了大量实验,认为某些情况下还是有区别的,感兴趣的话,我可以贴出来。另外,如果表上有unique index, count(*)会比较快一些,这种情况下,count(*)会只对该index进行range scan. 当然,前提是采用cbo, 且对该表执行过analyze.不过,无论如何,对1m纪录的表求count,都不会太快,毕竟要进行一次full scan(要么是对表本身,要么对unique index). To: oldwain(老斗) 能贴出来吗?谢谢! 不愧是小型机,我的Sun Ultra5d,Informix On-line 9.13 UC2,50万条记录,Select count(*) from tablename;60秒 ORACLE的count(*)要进行full scan,所以肯定很慢。 哪有那么慢,我现在记不清了,不过好象用sql server,奔3 (?百),大于50万条记录也不要15秒。 MountLion " 一个大约3M条记录的表,"运行查询select count(*) from tablename,需要大约15秒钟。 你是说有3百万条记录吗? M好像是兆的意思,也就是10^6,很恐怖噢! 看来速度真是很快。 我建议你另建一个表(记录这个表的记录数量),每次insert/delete时,就改变新建表的值,OK? 我估计你不会经常进行写操作。再说每次多写这么一个字段,不算浪费资源吧。 以后你统计起来也很方便。干吧。 以下为我的实验的脚本,有兴趣的朋友可以用此脚本测试,最好能贴回测试结论。asktom上tom用同样的脚本的到的结果,却证实count(*)与count(1)相同,很奇怪!在svrmgrl下运行(set timing on 在svrmgrl比sqlplus提供的信息较详细) drop sequence seq_r1000; drop table r1000; create sequence seq_r1000;create table r1000 (id number);insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;commit;set timing onselect count(*) from r1000, r1000;select count(1) from r1000, r1000;select count(*) from r1000, r1000;select count(1) from r1000, r1000;-- 每个语句运行两次,确保排除cache的影响以下为我的测试结果(环境 817/win2000):===========================C:\>svrmgrlOracle Server Manager Release 3.1.7.0.0 - ProductionCopyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.Oracle8i Enterprise Edition Release 8.1.7.0.0 - ProductionWith the Partitioning optionJServer Release 8.1.7.0.0 - ProductionSVRMGR> connect scott/tigerConnected.SVRMGR> insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;1000 rows processed.SVRMGR> commit;Statement processed.SVRMGR> set timing onTiming ONSVRMGR> select count(*) from r1000, r1000;COUNT(*)---------- 40000001 row selected.Parse 0.00 (Elapsed) 0.00 (CPU)Execute/Fetch 1.33 (Elapsed) 0.00 (CPU)Total 1.33 0.00SVRMGR> select count(1) from r1000, r1000;COUNT(1)---------- 40000001 row selected.Parse 0.02 (Elapsed) 0.00 (CPU)Execute/Fetch 2.36 (Elapsed) 0.00 (CPU)Total 2.38 0.00SVRMGR> select count(*) from r1000, r1000;COUNT(*)---------- 40000001 row selected.Parse 0.01 (Elapsed) 0.00 (CPU)Execute/Fetch 1.34 (Elapsed) 0.00 (CPU)Total 1.35 0.00SVRMGR> select count(1) from r1000, r1000;COUNT(1)---------- 40000001 row selected.Parse 0.00 (Elapsed) 0.00 (CPU)Execute/Fetch 2.33 (Elapsed) 0.00 (CPU)Total 2.33 0.00SVRMGR>============================以下为tom的测试结果:============================drop sequence seq_r1000;drop table r1000;create sequence seq_r1000;create table r1000 (id number);insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;analyze table r1000 compute statistics;select count(*) from r1000, r1000;select count(1) from r1000, r1000;alter session set sql_trace=true;declare n number;begin for i in 1 .. 10 loop select count(*) into n from r1000, r1000; select count(1) into n from r1000, r1000; end loop;end;/shows:SELECT COUNT(*)FROM R1000,R1000call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 10 0.00 0.00 0 0 0 0Fetch 10 12.46 12.53 0 40 80 10------- ------ -------- ---------- ---------- ---------- ---------- ----------total 21 12.46 12.53 0 40 80 10Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 29 (recursive depth: 1)Rows Row Source Operation------- --------------------------------------------------- 10 SORT AGGREGATE10000000 MERGE JOIN CARTESIAN 10010 TABLE ACCESS FULL R100010000000 SORT JOIN 10000 TABLE ACCESS FULL R1000********************************************************************************SELECT COUNT(1)FROM R1000,R1000call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.01 0 0 0 0Execute 10 0.00 0.01 0 0 0 0Fetch 10 12.38 12.38 0 40 80 10------- ------ -------- ---------- ---------- ---------- ---------- ----------total 21 12.38 12.40 0 40 80 10Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 29 (recursive depth: 1)Rows Row Source Operation------- --------------------------------------------------- 10 SORT AGGREGATE10000000 MERGE JOIN CARTESIAN 10010 TABLE ACCESS FULL R100010000000 SORT JOIN 10000 TABLE ACCESS FULL R1000注意:我使用了set timing on, 而tom使用的是sqltrace, 理论上讲sqltrace更为精确,不过我通过sqltrace看过同样有30%-50%差异(该结果未保存,故无法提供)。希望各位测试后,能把结论贴出来。 大约2,800,000条记录,每行的宽度有553字节。我不是DBA,只是用户,做个实验而已。该表基本上不需要delete,只是要经常insert,update和select。大概有些数据库会记录表的当前记录数(可能还要针对用户吧),Oracle不会这样做 以下为我的实验的脚本,有兴趣的朋友可以用此脚本测试,最好能贴回测试结论。asktom上tom用同样的脚本的到的结果,却证实count(*)与count(1)相同,很奇怪!在svrmgrl下运行(set timing on 在svrmgrl比sqlplus提供的信息较详细) drop sequence seq_r1000; drop table r1000; create sequence seq_r1000;create table r1000 (id number);insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;commit;set timing onselect count(*) from r1000, r1000;select count(1) from r1000, r1000;select count(*) from r1000, r1000;select count(1) from r1000, r1000;-- 每个语句运行两次,确保排除cache的影响以下为我的测试结果(环境 817/win2000):===========================C:\>svrmgrlOracle Server Manager Release 3.1.7.0.0 - ProductionCopyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.Oracle8i Enterprise Edition Release 8.1.7.0.0 - ProductionWith the Partitioning optionJServer Release 8.1.7.0.0 - ProductionSVRMGR> connect scott/tigerConnected.SVRMGR> insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;1000 rows processed.SVRMGR> commit;Statement processed.SVRMGR> set timing onTiming ONSVRMGR> select count(*) from r1000, r1000;COUNT(*)---------- 40000001 row selected.Parse 0.00 (Elapsed) 0.00 (CPU)Execute/Fetch 1.33 (Elapsed) 0.00 (CPU)Total 1.33 0.00SVRMGR> select count(1) from r1000, r1000;COUNT(1)---------- 40000001 row selected.Parse 0.02 (Elapsed) 0.00 (CPU)Execute/Fetch 2.36 (Elapsed) 0.00 (CPU)Total 2.38 0.00SVRMGR> select count(*) from r1000, r1000;COUNT(*)---------- 40000001 row selected.Parse 0.01 (Elapsed) 0.00 (CPU)Execute/Fetch 1.34 (Elapsed) 0.00 (CPU)Total 1.35 0.00SVRMGR> select count(1) from r1000, r1000;COUNT(1)---------- 40000001 row selected.Parse 0.00 (Elapsed) 0.00 (CPU)Execute/Fetch 2.33 (Elapsed) 0.00 (CPU)Total 2.33 0.00SVRMGR>============================以下为tom的测试结果:============================drop sequence seq_r1000;drop table r1000;create sequence seq_r1000;create table r1000 (id number);insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;analyze table r1000 compute statistics;select count(*) from r1000, r1000;select count(1) from r1000, r1000;alter session set sql_trace=true;declare n number;begin for i in 1 .. 10 loop select count(*) into n from r1000, r1000; select count(1) into n from r1000, r1000; end loop;end;/shows:SELECT COUNT(*)FROM R1000,R1000call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.00 0 0 0 0Execute 10 0.00 0.00 0 0 0 0Fetch 10 12.46 12.53 0 40 80 10------- ------ -------- ---------- ---------- ---------- ---------- ----------total 21 12.46 12.53 0 40 80 10Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 29 (recursive depth: 1)Rows Row Source Operation------- --------------------------------------------------- 10 SORT AGGREGATE10000000 MERGE JOIN CARTESIAN 10010 TABLE ACCESS FULL R100010000000 SORT JOIN 10000 TABLE ACCESS FULL R1000********************************************************************************SELECT COUNT(1)FROM R1000,R1000call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.00 0.01 0 0 0 0Execute 10 0.00 0.01 0 0 0 0Fetch 10 12.38 12.38 0 40 80 10------- ------ -------- ---------- ---------- ---------- ---------- ----------total 21 12.38 12.40 0 40 80 10Misses in library cache during parse: 1Optimizer goal: CHOOSEParsing user id: 29 (recursive depth: 1)Rows Row Source Operation------- --------------------------------------------------- 10 SORT AGGREGATE10000000 MERGE JOIN CARTESIAN 10010 TABLE ACCESS FULL R100010000000 SORT JOIN 10000 TABLE ACCESS FULL R1000注意:我使用了set timing on, 而tom使用的是sqltrace, 理论上讲sqltrace更为精确,不过我通过sqltrace看过同样有30%-50%差异(该结果未保存,故无法提供)。希望各位测试后,能把结论贴出来。 用count(*)查找是全表扫描,很慢。 求查询语句 生活服务成sns应用潮流 就这么点分了,各位帮帮忙!exec(@sql)问题,急急急! 求助:max函数 SQL Server 2005数据库中的Output子句 ***************设备联网问题*********************** SQL语句 做数据库订阅与发布对服务器有什么要求? 视图问题和建表问题 数据库触发器的问题 在sql server 2000 中怎样存入图像? 在Ms_Sql_Server下这种触发器怎么写?
mysql 中 count(*) 是属于 优化的语句。速度非常的快,但在其他数据库中就不太清楚了。好像在ACCESS 中根本就用 count(*) 得不出结果来
To: MountLion,看来你还要看看服务器的优化,恐怕从SQL方面很难提高
不过,我做了大量实验,认为某些情况下还是有区别的,感兴趣的话,我可以贴出来。另外,如果表上有unique index, count(*)会比较快一些,这种情况下,count(*)会只对该index进行range scan. 当然,前提是采用cbo, 且对该表执行过analyze.不过,无论如何,对1m纪录的表求count,都不会太快,毕竟要进行一次full scan(要么是对表本身,要么对unique index).
Select count(*) from tablename;
60秒
" 一个大约3M条记录的表,"运行查询select count(*) from tablename,需要大约15秒钟。
你是说有3百万条记录吗? M好像是兆的意思,也就是10^6,很恐怖噢!
看来速度真是很快。
我建议你另建一个表(记录这个表的记录数量),每次insert/delete时,就改变新建表的值,OK? 我估计你不会经常进行写操作。再说每次多写这么一个字段,不算浪费资源吧。
以后你统计起来也很方便。干吧。
asktom上tom用同样的脚本的到的结果,却证实count(*)与count(1)相同,很奇怪!在svrmgrl下运行(set timing on 在svrmgrl比sqlplus提供的信息较详细) drop sequence seq_r1000;
drop table r1000;
create sequence seq_r1000;
create table r1000 (id number);
insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;
commit;
set timing on
select count(*) from r1000, r1000;
select count(1) from r1000, r1000;
select count(*) from r1000, r1000;
select count(1) from r1000, r1000;-- 每个语句运行两次,确保排除cache的影响以下为我的测试结果(环境 817/win2000):===========================C:\>svrmgrlOracle Server Manager Release 3.1.7.0.0 - ProductionCopyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - ProductionSVRMGR> connect scott/tiger
Connected.
SVRMGR> insert into r1000 select seq_r1000.nextval from all_objects where
rownum<1001;
1000 rows processed.
SVRMGR> commit;
Statement processed.
SVRMGR> set timing on
Timing ON
SVRMGR> select count(*) from r1000, r1000;
COUNT(*)
----------
4000000
1 row selected.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 1.33 (Elapsed) 0.00 (CPU)
Total 1.33 0.00
SVRMGR> select count(1) from r1000, r1000;
COUNT(1)
----------
4000000
1 row selected.
Parse 0.02 (Elapsed) 0.00 (CPU)
Execute/Fetch 2.36 (Elapsed) 0.00 (CPU)
Total 2.38 0.00
SVRMGR> select count(*) from r1000, r1000;
COUNT(*)
----------
4000000
1 row selected.
Parse 0.01 (Elapsed) 0.00 (CPU)
Execute/Fetch 1.34 (Elapsed) 0.00 (CPU)
Total 1.35 0.00
SVRMGR> select count(1) from r1000, r1000;
COUNT(1)
----------
4000000
1 row selected.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 2.33 (Elapsed) 0.00 (CPU)
Total 2.33 0.00
SVRMGR>============================
以下为tom的测试结果:============================drop sequence seq_r1000;
drop table r1000;
create sequence seq_r1000;
create table r1000 (id number);
insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;analyze table r1000 compute statistics;
select count(*) from r1000, r1000;
select count(1) from r1000, r1000;
alter session set sql_trace=true;declare
n number;
begin
for i in 1 .. 10
loop
select count(*) into n from r1000, r1000;
select count(1) into n from r1000, r1000;
end loop;
end;
/
shows:SELECT COUNT(*)
FROM
R1000,R1000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 12.46 12.53 0 40 80 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 12.46 12.53 0 40 80 10Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29 (recursive depth: 1)Rows Row Source Operation
------- ---------------------------------------------------
10 SORT AGGREGATE
10000000 MERGE JOIN CARTESIAN
10010 TABLE ACCESS FULL R1000
10000000 SORT JOIN
10000 TABLE ACCESS FULL R1000********************************************************************************SELECT COUNT(1)
FROM
R1000,R1000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 10 0.00 0.01 0 0 0 0
Fetch 10 12.38 12.38 0 40 80 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 12.38 12.40 0 40 80 10Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29 (recursive depth: 1)Rows Row Source Operation
------- ---------------------------------------------------
10 SORT AGGREGATE
10000000 MERGE JOIN CARTESIAN
10010 TABLE ACCESS FULL R1000
10000000 SORT JOIN
10000 TABLE ACCESS FULL R1000注意:我使用了set timing on, 而tom使用的是sqltrace, 理论上讲sqltrace更为精确,不过我通过sqltrace看过同样有30%-50%差异(该结果未保存,故无法提供)。
希望各位测试后,能把结论贴出来。
我不是DBA,只是用户,做个实验而已。
该表基本上不需要delete,只是要经常insert,update和select。
大概有些数据库会记录表的当前记录数(可能还要针对用户吧),Oracle不会这样做
asktom上tom用同样的脚本的到的结果,却证实count(*)与count(1)相同,很奇怪!在svrmgrl下运行(set timing on 在svrmgrl比sqlplus提供的信息较详细) drop sequence seq_r1000;
drop table r1000;
create sequence seq_r1000;
create table r1000 (id number);
insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;
commit;
set timing on
select count(*) from r1000, r1000;
select count(1) from r1000, r1000;
select count(*) from r1000, r1000;
select count(1) from r1000, r1000;-- 每个语句运行两次,确保排除cache的影响以下为我的测试结果(环境 817/win2000):===========================C:\>svrmgrlOracle Server Manager Release 3.1.7.0.0 - ProductionCopyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved.Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - ProductionSVRMGR> connect scott/tiger
Connected.
SVRMGR> insert into r1000 select seq_r1000.nextval from all_objects where
rownum<1001;
1000 rows processed.
SVRMGR> commit;
Statement processed.
SVRMGR> set timing on
Timing ON
SVRMGR> select count(*) from r1000, r1000;
COUNT(*)
----------
4000000
1 row selected.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 1.33 (Elapsed) 0.00 (CPU)
Total 1.33 0.00
SVRMGR> select count(1) from r1000, r1000;
COUNT(1)
----------
4000000
1 row selected.
Parse 0.02 (Elapsed) 0.00 (CPU)
Execute/Fetch 2.36 (Elapsed) 0.00 (CPU)
Total 2.38 0.00
SVRMGR> select count(*) from r1000, r1000;
COUNT(*)
----------
4000000
1 row selected.
Parse 0.01 (Elapsed) 0.00 (CPU)
Execute/Fetch 1.34 (Elapsed) 0.00 (CPU)
Total 1.35 0.00
SVRMGR> select count(1) from r1000, r1000;
COUNT(1)
----------
4000000
1 row selected.
Parse 0.00 (Elapsed) 0.00 (CPU)
Execute/Fetch 2.33 (Elapsed) 0.00 (CPU)
Total 2.33 0.00
SVRMGR>============================
以下为tom的测试结果:============================drop sequence seq_r1000;
drop table r1000;
create sequence seq_r1000;
create table r1000 (id number);
insert into r1000 select seq_r1000.nextval from all_objects where rownum<1001;analyze table r1000 compute statistics;
select count(*) from r1000, r1000;
select count(1) from r1000, r1000;
alter session set sql_trace=true;declare
n number;
begin
for i in 1 .. 10
loop
select count(*) into n from r1000, r1000;
select count(1) into n from r1000, r1000;
end loop;
end;
/
shows:SELECT COUNT(*)
FROM
R1000,R1000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 10 12.46 12.53 0 40 80 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 12.46 12.53 0 40 80 10Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29 (recursive depth: 1)Rows Row Source Operation
------- ---------------------------------------------------
10 SORT AGGREGATE
10000000 MERGE JOIN CARTESIAN
10010 TABLE ACCESS FULL R1000
10000000 SORT JOIN
10000 TABLE ACCESS FULL R1000********************************************************************************SELECT COUNT(1)
FROM
R1000,R1000
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 10 0.00 0.01 0 0 0 0
Fetch 10 12.38 12.38 0 40 80 10
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 12.38 12.40 0 40 80 10Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29 (recursive depth: 1)Rows Row Source Operation
------- ---------------------------------------------------
10 SORT AGGREGATE
10000000 MERGE JOIN CARTESIAN
10010 TABLE ACCESS FULL R1000
10000000 SORT JOIN
10000 TABLE ACCESS FULL R1000注意:我使用了set timing on, 而tom使用的是sqltrace, 理论上讲sqltrace更为精确,不过我通过sqltrace看过同样有30%-50%差异(该结果未保存,故无法提供)。
希望各位测试后,能把结论贴出来。