关于mysql:简单分析-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 引擎就会进行;

参考资料:
not-exists-vs-not-in
not-in-vs-not-exists_StackOverflow
execution-plans-in-sql-server

【腾讯云】云产品限时秒杀,爆款1核2G云服务器,首年50元

阿里云限时活动-2核2G-5M带宽-60G SSD-1000G月流量 ,特惠价99元/年(原价1234.2元/年,可以直接买3年),速抢

本文由乐趣区整理发布,转载请注明出处,谢谢。

您可能还喜欢...

发表评论

您的电子邮箱地址不会被公开。

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据