MySQL 中not in、join...is null、not exists 效率问题记录

记录下自己测试出的三个关键字的性能差异。
由于MySQL5.7和MySQL8.0有不同的优化，所以我们要分开来讲。

数据准备

我准备了一张tb_stu表和一张tb_class表

注：600个班级，12万个学生。tb_stu是大表，tb_class是小表。通过class_id关联。为了避免索引优化带来的影响，所以我两张表的id都没有设置主键和索引。

MySQL5.7测试

测试查询效率

外层表为大表的情况

我们首先测试一下当外层表是大表的时候，三个关键字的区别。分别执行以下三个sql语句

SELECT * FROM tb_stu WHERE class_id NOT IN (SELECT id FROM tb_class)
SELECT * FROM tb_stu s where not exists (select 1 from tb_class c where s.class_id = c.id)
SELECT s.* FROM tb_stu s LEFT JOIN tb_class c ON s.class_id = c.id WHERE c.id is null

在MySQL5.7中，当外层表是大表的时候，not in的效率是最高的，join…is null 次之，not exists最低。
**
用 explain 查看一下这3条sql语句的执行计划

外层表为小表的情况

在MySQL5.7中，当外层表是小表时，not in的效率是最高的，join…is null稍好于exists。
**

查询效率低的优化策略

从上面的测试中可以看出，当数据量较大时， not exists 和 join...is null 的效率都是比较差的。建议在编写sql的时候还是用 not in 。有人可能会问，加索引的的话是不是可以提高not exists 和 join...is null 的查询效率呢？答案是可以的。

对于 not exists ，如果我们要添加索引的话，应该给哪个字段添加呢？
首先我们要知道对于 not exists 语句中的外层表，它的索引无法在子查询中生效，所以我们应该给子查询中的那个表的关联字段添加索引。比如：

SELECT * FROM tb_stu s where not exists (select 1 from tb_class c where s.class_id = c.id)

我们应该给 tb_class 表的 id 字段添加索引（我亲身测试过，如果给 tb_stu 表的 class_id字段添加索引，效率没有任何提升），我添加了b树索引后的sql执行效率如下

对于 join... is null ，如果我们要加索引的话，要给where条件中那个字段添加索引。
比如：

SELECT s.* FROM tb_stu s LEFT JOIN tb_class c ON s.class_id = c.id WHERE c.id is null

我们应该给 tb_class 表的id字段添加索引，添加索引后sql的执行效率如下：

MySQL8.0测试

测试查询效率

外层表为大表的情况

从上面的执行结果可以看出，当外层表是大表的时候，在MySQL8.0中， not in 和 not exists 的效率是一样的了，但是 join...is null 的效率还是很低。

外层表为小表的情况

当外层表是小表的时候， join...is null 的效率是最好的， not in 和 not exists 稍差