sql查询性能小测试

测试环境为oracle10g

 

创建一个表

create table TEST_A
(
  ID   NUMBER(20),
  NAME VARCHAR2(20),
  CODE CHAR(20)
)

 

生成100W条数据，每个字段的值均为1到100W，进行如下性能测试。未创建索引之前的执行时间

select * from test_a t where id=219 --2.25s,1.7s
select * from test_a t where name='219' --2.391s ,1.7s
select * from test_a t where trim(code) = '219' --2.469s,2.172s

 均加上索引，后的执行时间

--behind add index
select * from test_a t where id=219 --0.016s
select * from test_a t where name='219' --0.016s
select * from test_a t where trim(code) = '219' --2.5s,2.172s
select * from test_a t where substr(code,0,3) = '219'--取出所有2.48s,前15条0.063s,0.03s
select * from test_a t where substr(code,0,6) = '100000'--2.297s,2.36s

 

初步说明NUMBER做主键类型与VARCHAR2的性能差别并不明显。(一般认为VARCHAR2的查询性能比CHAR和NUMBERS类型要差，后来测试使用VARCAHR(4000)数据达100W条时，性能也基本无差）

还说明在字段上使用函数，将查询使用不到索引，这点要特别注意！ 通过查看执行计划，可以看使用的是全表扫描。

 

再测试一下count的性能。

select count(*) from test_a t --1.8s ,1.7s  --count(1)是一样的
select count(id) from test_a t --0.812s, 0.797s
select count(name) from test_a t --0.906s
select count(code) from test_a t --1.453s, 1.9s

 

没有过滤条件时， 对数据量大的表，少用count(*)，尽量count主键索引的字段。

有过滤条件时，速度取决了过滤之后的记录数。两种写法性能基本无差异。

 

 

500W条数据的测试结果

select * from test_a t where id=219 --0.016s
select * from test_a t where name='219' --0.016s
select * from test_a t where code = rpad('100000',20,' ');--0.016s

select * from test_a t where trim(code) = '219' --33.3s
select * from test_a t where substr(code,0,3) = '219'--前15条0.125,0.047s
select count(code) from test_a t where substr(code,0,3) = '219'--12.8s
select * from test_a t where substr(code,0,6) = '100000'--11.579s

 

select count(*) from test_a --46s
select count(id) from test_a --5.28s,4.84s
select count(*) from test_a where id>1000 and id <10000--0.016s
select count(id) from test_a where id>1000 and id <10000 --0.016s

 

对大表进行查询分页时，最好先确定一个查询条件。否则结果集出来的速度会比较慢。因为分页先要计算总记录数。