NOT IN和NOT EXISTS还有不一样的时候哦

亦凉 2022-06-11 05:48 136阅读 0赞

原文链接[https://dzone.com/articles/mysql-when-not-in-is-not-equal-to-not-exists][https_dzone.com_articles_mysql-when-not-in-is-not-equal-to-not-exists]

> 对于MySQL, “NOT IN”和”NOT EXISTS”是一样的原理吗？不全是，在处理null时就出现意外了。

当你想对两个表进行差集操作的时候，可以配合子查询，使用`NOT EXISTS` 或 `NOT IN` ，`NOT IN` 更加显得清晰、简单。如今的数据库系统都会将这两种查询方式优化成相同的执行计划获得类似的结果，处理外部和内部查询相关性。

有个很重要的区别是，如果在子查询的结果里返回了NULL，`NOT IN` 子句会执行失败，因为NULL和任何值都不相等。除了这个，`NOT IN` 和`NOT EXISTS` 应该就没什么区别了，另外，有[资源][Link 1]说`NOT IN`更快，所以使用得也会多一点。

这篇文章就要来说说`NOT IN` 慢得如蜗牛的一个例子了，nulls是罪魁祸首。

下面有两个表，追综用户点击流的数据，包括登陆用户和匿名用户，其中`EVENTS.USER_ID`可以是null的。当用户不为null的时候，次级索引(USER\_ID列)是占很大基数的。

create table USERS
    (
      ID    integer auto_increment primary key,
      ...
    )
    create table EVENTS
    (
      ID      integer auto_increment primary key,
      TYPE    smallint not null,
      USER_ID integer
      ...
    )
    create index EVENTS_USER_IDX on EVENTS(USER_ID);

现在使用`NOT IN`从这个两个表中检索出没有特定事件的用户，并且保证null不会出现在子查询返回的结果里，查询长这样：

select ID from USERS where ID in (1, 7, 2431, 87142, 32768) and ID not in ( select USER_ID from EVENTS where TYPE = 7 and USER_ID is not null );

在我的测试结果集中，USERS表有100,000行记录，EVENTS表有10,000,000记录，EVENTS大约75%的USER\_ID是null的。在我i7处理器、12G的内存、SSD固态硬盘的笔记本里执行了两分钟，oh，my god，简直不能接受啊。

来使用`NOT EXISTS` 看看吧：

select ID from USERS where ID in (1, 7, 2431, 87142, 32768) and not exists ( select 1 from EVENTS where USER_ID = USERS.ID and TYPE = 7 );

这个版本的sql语句执行了0.01秒，正是我所期待的。  
来比较它们的执行计划，第一个是`NOT IN`的查询，第二个是`NOT EXISTS`的。  
*NOT IN*

*NOT EXISTS*

两个执行计划内容都差不多: 都是从USERS表中选择数据行，然后使用嵌套循环连接(`dependent subquery`)从EVENTS表里查询出数据，都使用了`EVENTS_USER_IDX`索引在子查询中选择行，每一步都估算出相同的记录数。

但仔细看一下连接类型(join)——type列，`NOT IN`使用的是[index\_subquery][index_subquery]， 然而`NOT EXISTS`使用的却是[ref][]。再看一下ref列：`NOT EXISTS`明确和外部字段进行关联，`NOT IN`使用的是函数，这里发生了什么。

`index_subquery`连接类型表示MySQL会扫描索引去查找子查询的相关行，这也没有什么问题。`EVENTS_USER_IDX` 是“narrow”类型的：只有一列，所以引擎也不会读取大量的数据块去查到和外部询匹配的id。笔者我后边又使用很多的查询去测试这个索引，所有都在几百毫秒内返回了结果。

为了得到更多的信息，使用`explain extended` 查看`NOT IN`sql 语句的执行计划，然后接着执行`show warnings` 看到如下内容：

/* 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`) in EVENTS on EVENTS_USER_IDX checking NULL where ((`example`.`EVENTS`.`TYPE` = 7) and (`example`.`EVENTS`.`USER_ID` is not null)) having 
            (`example`.`EVENTS`.`USER_ID`)))))))

对于`on EVENTS_USER_IDX checking NULL` 我不能找到一个合适的解释，我的理解是：优化器断定在它在执行一个IN的子查询，并且结果集中可以包含NULL，做这个决定的时候，它不考虑where子句中的null检查，结果就是从750万数据中检查USER\_ID为null的记录，同时还有和外部查询关联匹配的记录。通过”检查(examine)”，笔者的意思是它将读取表行并应用`not null`的条件。再者，基于运行查询所花费的时间，我认为它为外部查询中的每个候选值执行了此操作。

因此，当你在一个可以为NULL的列上使用`IN`或者`NOT IN` 子查询时，可以好好想想使用`EXISTS`或`NOT EXISTS`代替。

参考链接：  
[http://blog.csdn.net/liuxilil/article/details/6718409][http_blog.csdn.net_liuxilil_article_details_6718409]  
[http://blog.chinaunix.net/uid-21879027-id-3818874.html][http_blog.chinaunix.net_uid-21879027-id-3818874.html]  
[http://www.cnblogs.com/zhengyun\_ustc/p/slowquery3.html][http_www.cnblogs.com_zhengyun_ustc_p_slowquery3.html]

[https_dzone.com_articles_mysql-when-not-in-is-not-equal-to-not-exists]: https://dzone.com/articles/mysql-when-not-in-is-not-equal-to-not-exists
[Link 1]: https://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
[index_subquery]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_index_subquery
[ref]: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_ref
[http_blog.csdn.net_liuxilil_article_details_6718409]: http://blog.csdn.net/liuxilil/article/details/6718409
[http_blog.chinaunix.net_uid-21879027-id-3818874.html]: http://blog.chinaunix.net/uid-21879027-id-3818874.html
[http_www.cnblogs.com_zhengyun_ustc_p_slowquery3.html]: http://www.cnblogs.com/zhengyun_ustc/p/slowquery3.html