sql中in和exists、not in 和 not exists的区别

总结

• in语句只执行一次，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。适合子表比主表数据小的情况

• exists对外表作loop循环，每次loop循环再对内表进行查询。exists()适合子表比主表数据大的情况

• 当主表数据与子表数据一样大时,in与exists效率差不多,可任选一个使用

• 不管外表与内表的大小，not exists 的效率一般要高于 not in，not in不会走索引，not exists子查询还是会走索引。

---

一、区别

1.1 in的性能分析。

select * from A
where id in(select id from B)

我们知道上诉sql会先执行括号内的子查询,再执行主查询,因此相当于以下过程：

for select id from B
for select * from A where A.id = B.id

分析：

• 当前的in子查询是B表驱动A表
• mysql先将B表的数据一把查出来至于临时表中
• 遍历B表的数据，再去查A表（每次遍历都是一次连接交互，这里会耗资源），in查询相当于多个or条件的叠加
• 假设B有100000条记录，A有10条记录，会交互100000次数据库；再假设B有10条记录，A有100000记录，只会发生10次交互。

1.2 Exists的性能分析

select a.* from A a
where exists(select 1 from B b where a.id=b.id)

类似于以下过程:

for  select * from A
for  select 1 from B where B.id = A.id 

分析：

• 当前exists查询是A表驱动B表
• 与in不同，exists将A的纪录数查询到临时表，然后作loop循环，每次loop循环再对B表进行查询。因此A表的记录数决定了数据库的交互次数
• 假设A有10000条记录，B有10条记录，数据库交互次数为10000；假设A有10条，B有10000条，数据库交互次数为10。

小结

• 使用in时，B表驱动A
• 使用exists时，A表驱动B
• 所以我们写sql时应当遵循“小表驱动大表“的原则

1.3 not in 和 not exisit的区别

使用not in，那么对内外表都会进行全表扫描，没有用到索引；而not exists的子查询依然可以使用。所以无论哪个表大，用not exists都比not in快。

引申

在Mysql5.5和之前的版本中，没有引入物化和半连接的方式来优化子查询，查询优化器都会将in子查询转化为exists子查询。
。