oracle 索引提升查询速度, in 和 exist 效率

做记录：

今天有一个有153万条数据的表，发现查询很慢：

select count(y) as transfereeNum,x
from t_ast_subject_invest_order
where x= '111' and ORDER_STATUS!=1
GROUP BY x;

执行时间大概2-3s 。。

给字段x 加上索引后，时间为0.007s . 查询速度明显提升。

2. 关于in 和 exist 效率问题

#外表内表同量级

select o.*
      from x o
      WHERE o.ORDER_STATUS!=1 and o.trans_from_order IN (select t1.ORDER_NO from x t1 )
61.123s

select o.*
      from x o
      WHERE o.ORDER_STATUS!=1 andEXISTS (select 1 from x t1 where t1.ORDER_NO=o.trans_from_order)
65.162s

本人实测 x表数据量是 153万条，使用in 和 exists 查询，耗时差不多，甚至in 还快一点。所以网上说的 in 比 exists 快。。显然是没有测试考虑。就算加上索引，时间也是差不多的。

网上查到如下说法：

1. EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用：
2.IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

# 外表小（2000） 内表大（150万）
select o.*
      from loan.x o
      WHERE  EXISTS (select 1 from loan.y t1 where t1.TRANS_FROM_ORDER=o.project_no);  #1s
select o.*
      from loan.x o
      WHERE project_no in (select TRANS_FROM_ORDER from loan.y t1 );  # 1s

# 外表大（150万） 内表小（2000）


select t1.*
      from loan.y t1
      WHERE  EXISTS (select 1 from loan.x o where t1.TRANS_FROM_ORDER=o.project_no);
执行计划

select t1.*
      from loan.y t1
      WHERE TRANS_FROM_ORDER in (select project_no from loan.x);

使用navicat oracle 测试发现并没有较大区别，不管外大内小，还是外小外大。郁闷。。但是可以肯定的是加上索引，in 和 exist 的影响可以忽略。

总结：

测试下来in 和 exists 好像差别不大。。和网上描述的并不一致，可能是我的工具问题，我使用的navicat premium ,可能有查询缓存。而且我使用该工具的 Explain selected 或者 oracle的 explain plan for 命令，

发现in 和 exists 的执行计划是一样的（看上图）。真不知道该信谁的了。

秒客网

oracle 索引提升查询速度, in 和 exist 效率

相关文章