1、环境
操作系统:winxp系统 cpu:p8700 双核2.53 内存:2GB 数据库:oracle9i
2、表结构
sql代码:
drop table base_customer;
create table base_customer
(
uuid number(10) not null,
customerId varchar(20),
showName varchar(30),
trueName varchar(30),
image varchar(100),
pwd varchar(50),
registerTime timestamp,
securityKey varchar(10),
primary key (uuid),
unique(customerId)
);
create index idx_customer_registerTime on base_customer(registerTime);
drop table base_customer_sign;
create table base_customer_sign
(
uuid number(10) not null,
signCustomerUuid number(10) not null,
signTime timestamp not null,
signCount number(10) not null,
signSequenceCount number(10) not null,
primary key (uuid)
);
create index idx_sign on base_customer_sign(signCustomerUuid);
3、索引及数据量
sql代码:
base_customer 100w条
uuid主键
customerId 唯一索引
base_customer_sign 100条
uuid主键
signCustomerUuid 非唯一索引
4、初始化数据用例
java代码:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Timestamp;
import java.util.Date;
import oracle.jdbc.OracleDriver;
public class Test {
public static void main(String[] args) throws Exception {
initData();
}
public static void initData() throws Exception {
DriverManager.registerDriver(new OracleDriver());
Connection conn = null;
try {
String url = "jdbc:oracle:thin:@localhost:1521:orcl2";
String username = "test";
String password = "test";
conn = DriverManager.getConnection(url, username, password);
conn.setAutoCommit(false);
conn.createStatement().execute("truncate table base_customer");
PreparedStatement psst = conn.prepareStatement("insert into base_customer values(?,?,?,?,?,?,?,?)");
for(int i=1; i<=1000000;i++) {//100w
int count = 1;
psst.setInt(count++, i);
psst.setString(count++, "user" + i);
psst.setString(count++, "user" + i);
psst.setString(count++, "user" + i);
psst.setString(count++, "user" + i);
psst.setString(count++, "user" + i);
psst.setTimestamp(count++, new Timestamp(System.currentTimeMillis()));
psst.setString(count++, "key" + i);
psst.addBatch();
psst.executeBatch();
conn.commit();
}
PreparedStatement psst2 = conn.prepareStatement("insert into base_customer_sign values(?,?,?,?,?)");
for(int i=1; i<=0;i++) {//100
int count = 1;
psst2.setInt(count++, i);
psst2.setInt(count++, i);
psst2.setTimestamp(count++, new Timestamp(System.currentTimeMillis()));
psst2.setInt(count++, 1);
psst2.setInt(count++, 1);
psst2.addBatch();
psst2.executeBatch();
conn.commit();
}
psst.close();
conn.commit();
} catch (Exception e) {
e.printStackTrace();
conn.rollback();
} finally {
conn.close();
}
}
}
5、场景
5.1、第一组 内表大 外表小
用例1、
sql代码:
select count(*) from base_customer_sign where signCustomerUuid in (select uuid from base_customer where trueName like 'user%')
执行计划:
如图1-1
执行时间:
0.015秒
结论:数据库执行了优化,根本不是我们需要的用例。
用例2
java代码:
select count(*) from base_customer_sign where signCustomerUuid in (select uuid from base_customer where trueName like 'user%' group by uuid)
执行计划:
如图1-2
执行时间:
28.672秒
结论:内表如果查询回来很多数据并要排序的话,效率很极低,因此内表适合返回数据量小的表,例外是用例1场景。
用例3
java代码:
select count(*) from base_customer_sign
where exists (select 1 from base_customer where trueName like 'user%' and base_customer.uuid = base_customer_sign.signCustomerUuid)
执行计划:
如图1-3
执行时间:
0.016秒
结论:外表执行全扫描,如果外表大很降低效率。
用例4
java代码:
select count(*) from base_customer_sign where signCustomerUuid in (select uuid from base_customer where customerId like 'user%')
执行计划:
如图1-4
执行时间:
13.61秒
结论:即使内表很小,但外表数据量很大 同样是低效。
用例5
java代码:
select count(*) from base_customer_sign
where exists (select 1 from base_customer where base_customer.customerId like 'user%' and base_customer.uuid = base_customer_sign.signCustomerUuid)
执行计划:
如图1-5
执行时间:
0.032秒
结论:内表大,外表小,速度快。
第二组 内表小 外表大
用例6
java代码:
select * from base_customer where uuid in (select signCustomerUuid from base_customer_sign where trueName like 'user%')
执行计划:
如图1-6
执行时间:
3.844秒
结论:外表全扫描,慢。
用例7
java代码:
select count(*) from base_customer
where exists (select 1 from base_customer_sign where trueName like 'user%' and base_customer.uuid = base_customer_sign.signCustomerUuid)
执行计划:
如图1-7
执行时间:
3.828秒
结论:和用例6一样。
用例8
java代码:
select count(*) from base_customer where uuid in (select signCustomerUuid from base_customer_sign where uuid>1 and signSequenceCount < 1)
执行计划:
如图1-8
执行时间:
0.031秒
结论:sql被优化,使用base_customer_sign作为外表,而且和内表是通过连接搞定,效率快。
用例9
java代码:
select count(*) from base_customer
where exists (select 1 from base_customer_sign where base_customer_sign.uuid>1 and base_customer.uuid = base_customer_sign.signCustomerUuid)
执行计划:
如图1-9
执行时间:
3.531秒
结论:外表全表扫描快不了。
总结:
1、 in可能被优化为 连接
2、 in 在未被优化时,外表小,内表大时(要建临时表并排序 耗时) 效率低
3、 exists 外表数据量大,速度肯定慢,,即使是in同样一样,而对于内表数据量多少跟索引有关。
4、 in 和 exists 在外表返回的数据量很大时也是低效的。
因此,,外表(驱动表) 应该都尽可能的小。
5、 not in 不走索引的,因此不能用
6、 not exists走索引的。
自己总结,难免有纰漏 本人只测试以上9个简单的用例,复杂场景可能未考虑到,因此在调优时 应该会看执行计划,根据执行计划决定哪个是高效的。
http://sishuok.com/forum/posts/list/1154.html
分享到:
相关推荐
“exists”和“in”是Oracle中,都是查询某集合的值是否存在在另一个集合,但对不同的数据有不同的用法,主要是在效率问题上存在很大的差别,以下有两个简单例子,以说明 “exists”和“in”的效率问题。
in和exists的区别与执行效率问题解析
oracle in和exists性能解析
里面自己根据网上的资源整理出来的一份sql中in,exists,not in,not exists的使用方法以及注意事项等,有助于初学的朋友们借鉴。
如果存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。 SQL中IN和EXISTS用法的区别 NOT IN sql in与exists区别
基本数据插入 except和intersect和exists和not exists和union和union all sql server
关于Exists,not Exists.in ,not in效率的说明。关于Exists,not Exists.in ,not in效率的说明。关于Exists,not Exists.in ,not in效率的说明。关于Exists,not Exists.in ,not in效率的说明。关于Exists,not Exists.in...
SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句.txt欢迎下载!
“exists”和“in”的效率问题,不同的语句exists和in效率有所不同
且看接下来的具体分析:in其实是将外表和内表进行hash join,exists是先对外表进行loop操作,然后每次loop后再对内表进行查询。 如果两张表大小差不多,那么exists和in的效率差不多。 例如: 一张大表为A,一张小表B...
in与exists之争(11g).sql
oracle中exists_和in的效率问题详解
总结:exists 和not exists语句强调是否返回结果集,不要求知道返回什么,与in的区别就是,in只能返回一个字段值,exists允许返回多个字段 提醒:文章中提供了exists和not exists的常用示例,已经经过本人测试,...
exists 和 not exists的详细解释
系统要求进行SQL优化,对效率比较低的SQL进行优化,使其运行效率更高,其中要求对SQL中的部分in/not in修改为exists/not
sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句
MySQL优化之in,exists,not in,not exists的区别in与existsin查询过程结论:exists查询过程:结论:not in与not existsnot in查询过程:结论:not exists查询过程:结论: 首先我们使用两个用户表作为实例 insert ...
一个是问in exist的区别,一个是not in和not exists的区别