当你想对两个表进行差分操作时，你有两个选择：使用NOTEXISTS子查询或NOTIN。后者可以说更容易编写，并且可以使查询方法更加明显。现代数据库系统可以优化两个执行计划以获得类似的查询结果，并且可以处理外部和内部的查询依赖性（我说“现代”是因为我在1990年代中期使用Oracle7.3时吸取了教训，它确实没有这个功能）。这两种构造之间有一个很大的区别：如果子查询返回的结果为NULL，则不会执行NOTIN条件，因为NULL不等于它或其他值。但是如果你注意到这一点，它们是等价的。事实上，这些消息告诉我们NOTIN查询更快，人们更喜欢用它查询。这篇文章讲的是数据库速度明显变慢的情况，而空值是罪魁祸首。考虑以下可能用于跟踪点击流数据的两个表。由于我们跟踪匿名用户和注册用户，因此EVENTS.USER_ID可以为空。但是，当用户不可用时，次要指标将具有更高的基数。createtableUSERS(IDintegerauto_incrementprimarykey,...)createtableEVENTS(IDintegerauto_incrementprimarykey,TYPEsmallintnotnull,USER_IDinteger...)createindexEVENTS_USER_IDXonEVENTS(USER_ID);好的，现在让我们使用这些表：从一小部分用户开始，我们想找到那些没有特定事件的用户。使用NOTIN子句，并确保空值不会出现在内部结果中，查询如下所示：selectIDfromUSERSwhereIDin(1,7,2431,87142,32768)andIDnotin(selectUSER_IDfromEVENTSwhereTYPE=7andUSER_IDisnotnull);对于我的测试数据集，USERS表有100,000行，EVENTS表有10,000,000行，EVENTS表中大约75%的USER_ID是空的。我在我的笔记本电脑上运行这个查询，它有一个Corei7处理器、12GB内存和一个SSD。我已经运行了大约2分钟，它只是……哇。让我们用NOTEXISTS和相关子句替换NOTIN：selectIDfromUSERSwhereIDin(1,7,2431,87142,32768)andnotexists(select1fromEVENTSwhereUSER_ID=USERS.IDandTYPE=7);这个版本运行时间为0.01秒，比我预期的要短。是时候比较执行计划了。第一个计划来自NOTIN查询，第二个来自NOTEXISTS。+----+--------------------+--------+------------+---------------+----------------+-----------------+--------+-----+-----+---------+-----------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+--------------------+--------+------------+----------------+----------------+----------------+----------+------+--------+------------+------------------------+|1|PRIMARY|USERS|NULL|range|PRIMARY|PRIMARY|4|NULL|5|100.00|Usingwhere;Usingindex||2|DEPENDENTSUBQUERY|EVENTS|NULL|index_subquery|EVENTS_USER_IDX|EVENTS_USER_IDX|5|func|195|10.00|Usingwhere|+----+----------------+--------+-----------+--------------+----------------+-----------------+--------+-----+-----+---------+------------------------+执行计划几乎相同：都从USERS表中选择行，然后使用嵌套循环从EVENTS表中检索join("DEPENDENTSUBQUERY")OK。两者都声称使用EVENTS_USER_IDX来选择子查询中的行。他们在每一步都估计了相似的行数。但更仔细地查看连接类型。NOTIN版本使用index_subquery，而NOTEXISTS版本使用ref。再看看ref列：NOTEXISTS版本使用对其他列的显式引用，而NOTIN使用函数。这里发生了什么？index_subquery连接类型意味着MySQL将扫描索引以查找子查询的相关行。这可能是问题所在吗？我不这么认为，因为EVENTS_USER_IDX索引是“narrow”类型的：它只有一列，所以引擎不应该读取很多块来找到对应于外部查询的ID行（事实上，我试过各种查询来测试这个索引，并且都在百分之几秒内运行）。有关更多信息，我转而使用“扩展”执行计划。查看这个计划，使用explainextended作为查询前缀，然后使用showwarnings得到MySQL优化器优化过的查询语句。这是来自NOTIN查询（??为清楚起见重新格式化）：/*select#1*/select`example`.`USERS`.`??ID`AS`ID`from`example`.`USERS`where((`example`.`USERS`.`??ID`in(1,7,2431,87142,32768))and(not((`example`.`USERS`.`??ID`,(((`example`.`USERS`.`ID`)inEVENTSonEVENTS_USER_IDXcheckingNULLwhere((`example`.`EVENTS`.`TYPE`=7)and(`example`.`EVENTS`.`USER_ID`isnotnull))having(`example`.`EVENTS`.`USER_ID`)))))))我找不到“在EVENTS_USER_IDX上检查NULL”的解释，但我认为发生的情况是：优化器认为它正在执行一个可以在结果中包含NULL的IN查询；当做出这个决定时，它不考虑where子句中的空检查。因此它将检查（检查）USER_ID为空的750万行，以及匹配来自外部查询的值的几十行。“检查”是指它将读取表行，然后应用非空条件。此外，根据运行查询所需的时间，我认为它会为外部查询中的每个候选值执行此操作。因此，本文的主题是：每当您想在可空列上使用IN或NOTIN子查询时，请重新考虑并改用EXISTS或NOTEXISTS。
