MYSQL中的NOT IN和NOT EXISTS不一样的时候 - 简书登录注册写文章首页下载APP会员IT技术MYSQL中的NOT IN和NOT EXISTS不一样的时候王帅199207关注赞赏支持MYSQL中的NOT IN和NOT EXISTS不一样的时候原文链接：Database Zone
对于MySQL, “NOT IN”和”NOT EXISTS”是一样的原理吗？不全是，在处理null时就出现意外了。
当你想对两个表进行差集操作的时候，可以配合子查询，使用NOT EXISTS 或 NOT IN ，NOT IN 更加显得清晰、简单。如今的数据库系统都会将这两种查询方式优化成相同的执行计划获得类似的结果，处理外部和内部查询相关性。
有个很重要的区别是，如果在子查询的结果里返回了NULL，NOT IN 子句会执行失败，因为NULL和任何值都不相等。除了这个，NOT IN 和NOT EXISTS 应该就没什么区别了，另外，NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL
文章和评论有相关比较，可以参考一下。
这篇文章就要来说说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
+----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+
| 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代替。
推荐阅读更多精彩内容mysql explain用法和结果的含义转自：http://blog.chinaunix.net/uid-540802-id-3419311.html e...小陈阿飞阅读 906评论 0赞 2pySpark 中文API (2)pyspark.sql模块 模块上下文 Spark SQL和DataFrames的重要类： pyspark.sql...mpro阅读 9,026评论 0赞 13《时间看得见》总结作为一个13年效率手册使用者，我对这本书中提到的效率手册基本使用方法熟知。此书之于我，更像是提醒我去做一次系统的梳...登登登Veronica阅读 308评论 0赞 0照片里的故事之五昨天去看了《战狼Ⅱ》。看得很过瘾，尤其是连篇累牍的溢美之词加上近三十亿的票房收入，加深了我对国产片终究会走出低谷，...徐小多杭州阅读 1,627评论 2赞 8徐议文，佛系女孩心宽体胖慢悠悠——中考倒计时（三十九）今天是5月27日，离中考还有18天。 所谓“佛系”，其实当然只是一种调侃，它指的是不焦躁不执著，以平和的心态去面对...俞春霞阅读 491评论 0赞 1致成长的自己——11.25日，11.26日演讲比赛11.25日即兴演讲比赛参赛感悟 大家好，我是11期三班的偶然。 第一次参加演讲比赛确实很紧张，在演讲时，我怕超时...清清是颗小太阳阅读 532评论 0赞 4评论0赞22赞3赞赞赏更多好文