NOT IN和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 应该就没什么区别了，另外，有资源说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  IDfrom    USERSwhere   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  IDfrom    USERSwhere   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

+----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+| 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 | Using where; Using index ||  2 | DEPENDENT SUBQUERY | EVENTS | NULL       | index_subquery | EVENTS_USER_IDX | EVENTS_USER_IDX | 5       | func |  195 |    10.00 | Using where              |+----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+

NOT EXISTS

+----+--------------------+--------+------------+-------+-----------------+-----------------+---------+------------------+------+----------+--------------------------+| 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 | Using where; Using index ||  2 | DEPENDENT SUBQUERY | EVENTS | NULL       | ref   | EVENTS_USER_IDX | EVENTS_USER_IDX | 5       | example.USERS.ID |   97 |    10.00 | Using where              |+----+--------------------+--------+------------+-------+-----------------+-----------------+---------+------------------+------+----------+--------------------------+

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

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

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

为了得到更多的信息，使用explain extended 查看NOT INsql 语句的执行计划，然后接着执行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.chinaunix.net/uid-21879027-id-3818874.html
http://www.cnblogs.com/zhengyun_ustc/p/slowquery3.html