如何编写高效的SQL（二）---IN跟EXISTS、NOT IN跟NOT EXISTS

该篇文章根据网上的资料跟本人编写SQL的那一点点经验总结出来的，如果有什么不准确的地方，请指正，感谢感谢！

从网上搜集的资料，无论MySQL还是Oracle，都有以下结论：

一、IN跟EXISTS

in是把外表和内表作hash连接，先进行子查询，再进行主查询，子查询能不能匹配到数据，都会对子查询全部匹配完毕。

exists是对外表作loop 循环，先进行主查询，再到子查询中过滤，若子查询匹配到结果，则退出子查询，然后返回true，该条记录被加入到结果集中。

如果查询的两个表大小相当，那么用in和exists差别不大。

如果两个表中一个较小，一个是很大，则子查询表大的用exists，子查询表小的用in，当主查询查询出来的结果集较小，但是子查询查询出来的结果集较大时，使用exists，反之使用in。也就是如果限制性强的条件在子查询，则使用in，如果限制性强的条件在主查询，则使用exists。

虽然结论如此，但是在实际工作中（oracle 11g），有时候会发现无论使用in还exists，效率都差不多，这是因为数据库做了优化。

关于Oracle的优化：

Oracle优化器规则中有三种分析规则，以前主要是基于规则的优化器，即通过预先定义一系列的优先级顺序，比如唯一索引优先于普通索引，又或者是等于索引优先于大于索引，这样即按规则的优先顺序去执行SQL，所以我们就有了在子句结果集很小的时候，in查询速度会快于exists，反之则exists速度会更快的结论，在oracle 9i中，默认提供是基于选择的优化器，即当有分析数据时，采用基于成本的优化方式，没有则仍采用基于规则的查询方式，这样优化模式下基本等同于基于规则或者基于成本。在10g之后的版本，默认都是以基于成本的方式进行，这时候，oracle会先找出可能的执行方式，然后计算出每个执行计划的成本，再选择以较低成本的方式进行计算，这样子在对in和exists的分析中，这两种写法会相互转换，那个统计的成本信息低则会选择那种方式。当然由于oracle的成本信息并不是全量统计得出来的结果，也会有一定的误差，再统计信息是需要人工（或定时）去执行统计的，如果操作大量数据后，没有进行统计，偏差也会很大。—https://www.linuxidc.com/Linux/2013-06/86053.htm

在mysql5.7中进行测试，由于数据的关系，只进行主查询是大表子查询是小表的测试：

可以看到的确是符合结论的，我觉得结论是正确的，只是不同的数据库或不同的数据库版本会进行不同的优化，所以，表现出来的结果不一定会符合结论。

用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格，如果效率相当，个人习惯使用in。

二、NOT IN跟NOT EXISTS

网上搜索到的结论是：如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

但是对于该结论，在Oracle11g中进行了测试，看执行计划，发现无论是那边的表大，cost是一样高的，而且执行计划完全相同（这部分应该也是数据库做了优化），发现即使是NOT IN，也是可以使用索引的（使用到了公司的数据库进行测试，图就不贴出来了）。

在MySQL5.7中进行测试：

首先，依然是进行主查询是大表，子查询是小表的测试：

可以看到用not in比not existss要快，看一下执行计划：

explain SELECT count(*) from wms_operation_record a where not EXISTS 
(select 1 from wms_user b where b.user_username=a.user_name);

explain SELECT count(*) from wms_operation_record a where a.user_name not in 
(select user_username from wms_user b);

使用not exists慢的原因是select_type是DEPENDENT SUBQUERY，也就是相关子查询，而使用not in是SUBQUERY，看官方的定义为：

SUBQUERY：子查询中的第一个SELECT；
DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询 。

在使用not exists的sql中，外面查询出来的结果集有98549，所以子查询需要执行98549次，说明not exists也是对外表作loop循环，先进行主查询，再到子查询中过滤的，造成了使用not exists比较慢。

再看key值，发现他们用的索引是一致的，区别在于type值，not exists是ref，而not in是index，先看type值的含义：

访问类型。依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引

• system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index
• const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描
• eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref
• ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。
• fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
• ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。
• unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值
• index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
• range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
• index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
• index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
• all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

然后，再进行主查询是小表，子查询是大表的测试：

explain SELECT count(*) from wms_user a where not EXISTS 
(select 1 from wms_operation_record b where a.user_username=b.user_name);

explain SELECT count(*) from wms_user a where a.user_username not in 
(select user_name from wms_operation_record b);

对比执行计划，以上两个SQL都有相关子查询，使用了相同的索引，区别在于连接查询类型type值上的不同：
一个是ref一个是index_subquery，而ref是要优于index_subquery的。

而且rows都是219跟510，rows是mysql找到符合查询的每一点上标准的那些行而需要读取的行的平均数。

ref这一列显示了之前的表在key列记录的索引中查找值所用的列或常量，如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func。

最后的结论是：

无论是IN跟EXISTS、NOT IN跟NOT EXISTS，如果两个表中一个小，一个大，则子查询表大的用exists（not exists），子查询表小的用in（not in），就mysql5.7跟Oracle11g来说。

最后，特别的，null值需要使用is null或is not null进行比较，在使用not in时，如果子查询的结果集中包含了null值，那么会导致返回的结果不准确，返回了空结果集。

比如

select count(user_id) from wms_user where user_username not in ('a','aa');

结果为：

而

select count(user_id) from wms_user where user_username not in ('a','aa',null);

只要not in子句中包含null，就会返回空结果集，可以把上面的sql理解为：

select count(user_id) from wms_user where user_username!='a' 
and user_username!='aa' and user_username!=null;

也可以使用外连接来替代not in：

select count(*) from wms_operation_record a where a.user_name 
not in (select user_username from wms_user b);

select count(*) from wms_operation_record 
left join wms_user on user_username=user_name where user_username is null;

在oracle中还可以：

select count(*) from wms_operation_record a,wms_user b 
where a.user_name=b.user_username(+) and b.user_username is null;