简单分析 MySQL 中 NOT IN 比 NOT EXISTS 效率低的原因

首先,要注意的是,NOT INNOT EXISTS 在某些情况下相等,但是 INEXISTS 在所有情况下都是不相等的;只有在相比较的两个字段都不允许存在空值(NOT NULL)时 NOT INNOT EXISTS 才相等,只要相比较的某个字段存在 null 值,那么它们就不能等同而视。具体来说,只要相比较的字段存在一个 null 值,NOT IN 就不会匹配任何行。
为什么会这样呢?我们先来看看 NOT IN 原理:

SELECT * FROM student s WHERE s.id NOT IN ( SELECT a.id FROM assistant )

上面这条语句等价于:
SELECT * FROM student s WHERE ( s.id != (SELECT a.id FROM assistant WHERE a.id=1) AND s.id != (SELECT a.id FROM assistant WHERE a.id=2) AND s.id != (SELECT a.id FROM assistant WHERE a.id=3) AND s.id != (SELECT a.id FROM assistant WHERE a.id=4) )

假设 a.id = 1 这一行的值为 null,那么 != 就会返回未知(UNKNOWN),由于这些值通过 AND 连接起来,所以最终 WHERE 子句不会得到 true,也就不会返回任何记录。
在 SQL 中,= 比较并不是只有 true 和 false 两个值:
SELECT 1 WHERE 1 = 1#true SELECT 1 WHERE 1 = 0#false SELECT 1 WHERE 1 = NULL#unknown SELECT 1 WHERE NULL = NULL#unknown

实际上,所有关于 null 值的比较都返回 unknown。
回到我们刚才提到的 NOT INNOT NULL 相等的情况,下面这两种写法是相等的:
SELECT * FROM student s WHERE s.id NOT IN ( SELECT a.id FROM assistant )SELECT * FROM student s WHERE NOT EXISTS ( SELECT 1 FROM assistant a WHERE s.id = a.id )

对于 NOT INNOT NULL 不相等的情况,我们假设 assistant 表的 id 字段包含空值,那么 NOT IN 实际上会执行以下查询:
SELECT * FROM student s WHERE NOT EXISTS ( SELECT 1 FROM assistant a WHERE s.id = a.id) AND NOT EXISTS ( SELECT 1 FROM assistant a WHERE a.id IS NULL)

因此,NOT IN 不会返回任何结果。
另外值得一提的是,如果比较的字段允许 null 值但实际上表中没有 null 值,那么执行计划可能会更糟糕,有可能会执行更多的嵌套查询,导致查询效率低下。
总结:当涉及到的字段允许存在 null 值(即使数据表中没存 null 值)时,执行计划会做更多的工作来进行 NOT IN 查询;而当涉及到的字段不允许存在 null 值时,NOT INNOT EXISTS 效果是一样的。
执行计划(execution plan):当执行一条查询时,处理引擎(processsing engine)会产生多个执行计划,并选择查询性能最好的一个执行计划来执行。
扩展:IN 和 EXISTS 的区别?
两者都是用来匹配值,不同的是:
  • IN:如果指定的值匹配到了子查询中的值,返回 true;SQL 引擎会扫描子查询中的所有记录;
  • EXISTS:如果匹配到了值,SQL 引擎就会停止;
【简单分析 MySQL 中 NOT IN 比 NOT EXISTS 效率低的原因】参考资料:
not-exists-vs-not-in
not-in-vs-not-exists_StackOverflow
execution-plans-in-sql-server

    推荐阅读