必知必会——关于SQL中的NOT IN优化

作者：Guilhem Bichot 译：徐轶韬

如果您使用 SELECT…WHERE x NOT IN（SELECT y FROM…）等“ NOT IN”编写SQL查询，必须了解当“ x”或“ y”为NULL时会发生什么？如果不是您想要的结果，我将在这里告诉您如何解决。

首先，一个简单的情况：如果“ x”和“ y”是使用NOT NULL子句创建的列，则它们永远不会为NULL。让我们考虑其他情况。复杂性源于以下事实：NULL可以理解为“未指定，可能是任何东西”，因此SQL的观点是，它无法知道NULL是否等于一个值或“东西”。产生的答案既不是TRUE，也不是FALSE，它产生UNKNOWN，MySQL将其打印为NULL：

在开始之前，我们需要记住另外两个SQL细节：

• WHERE针对行测试条件，并且仅当此条件为TRUE时才让行通过（拒绝FALSE和UNKNOWN）。
• NOT（TRUE）为FALSE，NOT（FALSE）为TRUE，NOT（UNKNOWN）为UNKNOWN。

现在我们已经准备好，让我们看一下这个例子：

这是一张房屋清单表，我们都知道用于房屋加热的能源类型包括（“煤”，“木材”，“天然气”等，或者不加热时为NULL）。

查看所有用煤炭或木材加热的房屋：

查看其他房屋：

我们有一间不带暖气的房子A，另一间使用油的房子B：

当我们测试房屋A时：

加热 IN (“coal”, “wood”) -> UNKNOWN，用SQL表示加热为NULL时，NULL可能是coal，也可能是wood，或者不是，我们不知道…

加热 NOT IN (“coal”, “wood”) -> UNKNOWN 因为NOT IN应用于NOT且IN是UNKNOWN，所以NOT（UNKNOWN）是UNKNOWN。

作为结果：

因为WHERE消除了条件不为TRUE的行，所以消除了房屋A。从SQL的角度来看，上面两个SELECT的结果是正确的。现在轮到您决定它们是否符合您的期望。

如果符合预期，那么一切都很好。但是我知道对于某些人来说，这不是他们所期望的。例如，有些人震惊地看到IN和NOT IN都错过了A房屋，就像A不在两组（“coal”, “wood”和另一组）中一样；似乎是看不见的，有点像幽灵……

问题的关键是当我设计房屋表时，我的意思是NULL为“无”，“无暖气”。

与SQL的理解不同，SQL意味着NULL为“也许是煤炭，天然气或其他，或者什么都不是”。 因此，就我的意图而言，NULL不可能是煤炭或木材，因此我希望IN不返回A，并且我希望NOT IN返回A。

那么，应该怎么做才能使NOT IN表现出预期的效果？

简单！我只需要用SQL更好地表达我想要的内容即可。 我可以将NOT IN更改为IN NOT TRUE：

这将让IN返回FALSE或UNKNOWN的房屋通过；因此，A和B会如我所愿地通过。

“ NOT IN（子查询）”也会发生相同的问题。让我们添加此表：

查询加热不产生二氧化碳的房屋：

>没有结果。

再次缺少A。同样，解决方案是：

现在我得到A。将其重写为IN IS NOT TRUE效果很好。

我可以改写为NOT EXISTS，但这是需要更多的编辑工作：

这也返回A。

如果我做两次重写中的任何一个，我就会以某种方式向MySQL声明我希望NULL是我的NOT IN的明确匹配项。另一个好处是，这还使MySQL可以更“积极地”进行优化。当NOT IN的任何一侧是可为空的列（此处是我们的情况）时，

SELECT … WHERE heating NOT IN (SELECT name …)

不能转换为反联接（MySQL 8.0.17的新功能），因为NOT IN与NULL的行为与关系代数中反联接的定义不匹配。因此，MySQL在执行此查询的方式上受到限制。

但，SELECT … WHERE heating IN (SELECT name …) IS NOT TRUE

可以转换为反连接。对于NOT EXISTS重写也是如此。

我们可以在EXPLAIN中进行检查；首先，我们有一个初始的NOT IN，其中一个查询计划显示每个房屋执行一个子查询，并且每次都进行表扫描（这效率很低）：

现在，这是重写的查询，它们正确地使用了反联接，因此可以从我们新的基于哈希的联接算法中受益（在版本8.0.18中引入了内联接，并在8.0.20中扩展为半联接，反联接和外部联接）：

反连接计划确实更快。为了进行实验检验，我们创建一百万个随机房屋：

RAND（）返回0到1之间的数字；ROUND（）*5将其舍入为0到5之间的整数；0到4获得真实的能源，而5获得NULL（因为在CASE中未指定5）。

要获得一百万个房屋，我只需要重复上一次的INSERT几次。现在我的搜索查询时间是：

反联接计划以更少的百分之二十的时间返回更多的行（如预期的那样，包括NULL）。

得出的结论是：当使用NOT IN时，如果无法避免使用NULL，请确认NULL的行为，如果不符合预期，请考虑“不正确或不存在”的替代方案。

感谢您使用MySQL！