Oracle中exists和in的性能差异

关于exists和in

exists关键字和in关键字都能实现外表查询后的结果过滤功能。在SQL语句性能优化方面，建议exists代替in进行子查询，实际上二者分场景进行使用。

低效
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = 'MELB')
高效
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND EXISTS (SELECT 'X'
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = 'MELB')

• exists不关注子表查询之后的结果数据，只关心是否有符合条件后的数据记录的产生，这意味着优化器可以更早的停止执行子查询
• in会查询所有子表中数据，其次外表进行查询时，会将每一行数据进行与子结果集比对

由此看来，exists是要比in性能更好，速度更快

二者内部执行规则

实际上，二者在执行查询方面存在不同。

• 对于in，一般会先执行子查询，因为in会关注子查询的结果数据，所以会将子查询的结果缓存，然后执行外表查询，将每一行的数据与结果集比对，过滤结果，子查询的实际执行次数取决于子表行数。
• 对于exists，因为不会关注子查询的结果数据本身，一般并不会缓存子查询结果，所以执行顺序为外表驱动内表，查询外表的每一行之后再执行exists子句，但是基于上述所说的只要子查询有记录产生，便条件为真，即可返回结果，它不需要实际获取子查询的所有结果。子查询的实际执行次数取决于外表行数。

这样，存在两种不同的应用场景，当外表数据量远大于子表时，推荐使用in关键字，当内表数据远大于子表时，推荐使用exists关键字。

时间复杂度的粗略比较

可以对两者的时间复杂度进行粗略计算和比较，首先假设表中不存在任何字段的索引，因为对于有索引字段的查询，时间复杂度可以从O(n)优化到O(logn)，为了对比，现在不考虑索引的情况，且外表m行，内表n行。

• in

  • 先缓存子查询结果，子查询O(n)，再做外表查询O(m)，再做笛卡尔积比对运算，O(m*n)

• exists

  • 对外表查询O(m),内表非全查询，理想情况查询第一条就符合条件直接终止子查询返回结果，最坏情况最后一条符合条件，子表介于O(1)到O(n)，总O(m)~O(m*n)

粗略的比对，可以看出exist效率较高。

CBO优化器

对于Oracle 11g 版本后，引入了 Cost-Based Optimizer (CBO) ，CBO 优化器会根据统计信息来决定查询的最佳执行路径。不必过于纠结in和exists