【十五】MySQL中in、exists、not in 、not exists区别

在MYSQL的连表查询中，最好是遵循‘小表驱动大表的原则’

在执行SELECT... FROM... WHERE ...IN(SELECT ...)查询时，通常是先执行IN（）中的查询（并且执行这一次）IN中查询的表就是驱动表，所以这个表应该小一点。

在执行SELECT... FROM... WHERE ...EXISTS(SELECT ...)查询时，通常是先执行SELECT... FROM... WHERE ...查询，此处查询的表才是驱动表，所以应该是小一点的表。

一、in 

例子：

SELECT * FROM A WHERE id IN (SELECT id FROM B);

 执行顺序：

 1.先执行子查询select id from B,子查询的结果集会缓存着，此处B表是全表扫描

SELECT id FROM B会首先执行，并且只执行这一次

2.再执行外面的主查询select * from B where id in，此处A表如果id字段有索引会走索引

适用场景：

1.子查询的B表数量较小

2.外大内小

二、exists

例子:

SELECT * FROM a WHERE EXISTS(SELECT 1 FROM b WHERE B.id = A.id);

执行顺序：

exists是对外表做loop循环，每次loop循环再对内表（子查询）进行查询

1.先执行主查询select * from a 。此处是A全表扫描

2.将主查询的结果放到子查询中验证 select 1 from b where b.id = a.id。此处B表如果id字段有索引会走索引

 适用场景：

1.子查询B表的数量较大

2.外小内大

三、not in

内外表都全表扫描，无索引，效率低，可考虑使用not exists，也可使用A left join B on A.id=B.id where B.id is null 进行优化。

例子：

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

 执行顺序：

1、select id from B 首先执行并且只执行这一次，将查询到的数据放入缓存。此处B表是全表扫描。

2、执行主查询 select * from A，将查询到的数据与之前缓存中的数据做笛卡尔积。此处A表也是全表扫描

3、根据判断条件 where id not in 遍历查询出符合条件的记录，加入结果集。

  适用场景：

1.不适用

四、not exists

 例子：

select * from A where not exists (select id from B where A.id = B.id);

执行顺序：

1、执行主查询 select * from A，根据查询到的每一条记录，将A.id带入到EXISTS子查询进行判断，如果符合查询条件，返回true，否则返回false.  此时如果B表id字段有索引则会走索引

2、根据EXISTS返回的bool型结果，如果为true则将这条数据保存到结果集，否则不保存。

3、回到第一步进行loop循环，直到遍历完外表A

适用场景：

1.NOT EXISTS比NOT IN要快，因为NOT IN 中内外表都要全表扫描，用不着索引，而NOT EXISTS子查询内表依然能用索引。

2. 外小内大