Mysql 中 IN 和 EXSIT 的 使用和区别

目录

一、IN与EXISTS的区别

1、IN查询分析

2、EXISTS查询分析

3、结论：

---

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

一、IN与EXISTS的区别

1、IN查询分析

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

等价于：1、SELECT id FROM B ----->先执行in中的查询

              2、SELECT *  FROM A  WHERE A.id = B.id

以上in()中的查询只执行一次，它查询出B中的所有的id并缓存起来，然后检查A表中查询出的id在缓存中是否存在，如果存在则将A的查询数据加入到结果集中，直到遍历完A表中所有的结果集为止。

以下用遍历结果集的方式来分析IN查询

通过以上程序可以看出，当B表的数据较大时不适合使用in()查询，因为它会将B表中的数据全部遍历一次

例如：

1、A表中有100条记录，B表中有1000条记录，那么最多可能遍历100*1000次，效率很差

2、A表中有1000条记录，B表中有100条记录，那么最多可遍历1000*100此，内循环次数减少，效率大大提升

结论：IN()查询适合B表数据比A表数据小的情况，IN()查询是从缓存中取数据

2、EXISTS查询分析

语法：SELECT  字段 FROM  table WHERE EXISTS（subquery）; 也就是说，外查询的结果集，作为条件，subquery必须用，只要subquery 查询出了 结果（not null），那么 外表 这条 结果集就 保留下来

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

以上查询等价于：

1、SELECT * FROM A;

2、SELECT I FROM B WHERE B.id = A.id;

EXISTS()查询会执行SELECT * FROM A查询，执行A.length次，并不会将EXISTS()查询结果结果进行缓存，因为EXISTS()查询返回一个布尔值true或flase，它只在乎EXISTS()的查询中是否有记录，与具体的结果集无关。

EXISTS()查询是将主查询的结果集,即外表查询的结果,放到子查询中做验证，根据验证结果是true或false来决定主查询数据结果是否得以保存。

以下用遍历结果集的方式来分析EXISTS查询

从以上程序可以看出：

当B表的数据比A表的数据大时适合使用EXISTS()查询，因为它不用遍历B操作，只执行一次查询就OK了

例如：

1、A表有100条记录,B表有1000条记录,那么EXISTS()会执行100次去判断A表中的id是否与B表中的id相等.因为它只执行A.length次，可见B表数据越多,越适合EXISTS()发挥效果.

2、A表有10000条记录,B表有100条记录,那么EXISTS()还是执行10000次,此时不如使用in()遍历10000*100次,因为IN()是在内存里遍历数据进行比较,而EXISTS()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

3、结论：

exists()适合B表比A表数据大的情况

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

 

4.思考点先 确定大小表，再确定那个是主要查询的表，在确定用哪种方式，并例如A，B两种表，A大表，要查B主表，要么就是 B一定是外表后面查的，即 A表先查，A又是大表，那么只能用exsit ,也就是说，子查询 都可用 in or exsit两种方式完成，只是要根据性能要求选择

5. 外表，内表的概念

先查询的为 内表 即 外表 in(内表)， 内表 exsit(外表)

6.in 和 exsit 都是 内表查询的结果给外表用，而 exist 的写法的特殊，导致exsit的 内表查询结果只能作为 table 不能作为 过滤数据，因为 写法 内表写在前，后面外表要用过滤条件的时候，必须拿到 外表的查询结果是一张表 才行 例如 select id from A exsit (select * from B where B.id = A.id) 这里最后一个 A 是 select id form A 的结果临时表,而 in 的 子查询的 生成 临时表的结果可以是 table（这里的指的是 多列的table）,也可以是 过滤字段

所以简单来说，in 过滤的结果 就是 字段 in () 是否为 true ,而 exsit的 过滤结果就是 exsit() (subquery) 里面的subquery是否 能查询出 not null的结果，能 那么 外表的 这条记录保留下来