SQL优化之-In和Exists

IN和EXISTS是极具争论的两个单词，我们通过一个普通的示例来看看两者的效率之差：

有A，B两张表，A的结构如下:

CREATE TABLE A(
  aid    VARCHAR2(16),
  bid    VARCHAR2(16),
  CONSTRAINT pk_a PRIMARY KEY (aid, bid)
)；

 

B的结构如下:

CREATE TABLE B(
  bid    VARCHAR2(16),
  bname    VARCHAR2(16),
  CONSTRAINT pk_b PRIMARY KEY (bid)
)；

 

其中A.bid和B.bid关联，为提高查询速度，Oracle已经自动对两张表的主键建立了索引。

问题：A和B中的数据都过百万条，写一条Oracle支持的SQL语句，把A中bid在B中不存在的记录删除。

 

根据表结构，很容易就想到用IN来解决，于是写出：

DELETE FROM A a WHERE a.bid NOT IN (SELECT bid FROM B)

遗憾的是，它的执行效率很低，两张表的数据达到百万条时，这条SQL的执行无疑会让你失去耐性。

 

我们借助PLSQL Developer来看看它的执行计划(选择SQL按F5即可)：

 

 

从图中可以看出，这条SQL在执行时分别对表A和B都进行了一次全表扫描，没有用到索引，这无疑是最差的执行效果了，所以需要优化，略做思考，写出一条同等执行效果的SQL：

DELETE FROM A a WHERE NOT EXISTS (SELECT b.bid FROM B b WHERE a.bid = b.bid)

这条SQL也不难理解，只是把IN换成了EXISTS，我们再来看看它的执行计划：

 

 

从图中可以看出，这条SQL在执行时只对表A进行了全表扫描，而对B扫描时使用了索引PK_B，所以效率会有百倍千倍的提升。

 

测试：

A表数据1W条，B表数据1W条

DELETE FROM A a WHERE a.bid NOT IN (SELECT bid FROM B) --执行所用时间：53.313s
DELETE FROM A a WHERE NOT EXISTS (SELECT b.bid FROM B b WHERE b.bid = a.bid)--执行所用时间2.266s

 

A表数据2W条，B表数据2W条

DELETE FROM A a WHERE a.bid NOT IN (SELECT bid FROM B) --执行所用时间：1231.609s
DELETE FROM A a WHERE NOT EXISTS (SELECT b.bid FROM B b WHERE b.bid = a.bid)--执行所用时间3.734s

 

可以看出，随着记录数的增加，效率悬殊越来越大。