内部信息源与外部信息源搜索引擎优化方法的异同(NOT,“NOTIN”和”NOTEXISTSEXISTS)

　　内部信息源与外部信息源搜索引擎优化方法的异同(NOT,“NOTIN”和”NOTEXISTSEXISTS)

　　原文链接：数据库专区

　　MySQL的“不存在”和“不存在”是否相同？不是所有的，在处理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 可以为空。当用户不为空时，二级索引（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 为空。在我的i7处理器、12G内存和SSD固态硬盘的笔记本上执行了两分钟，天啊，简直不能接受。

　　让我们看一下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的查询。

　　不在

　　+----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+

| 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 |

+----+--------------------+--------+------------+----------------+-----------------+-----------------+---------+------+------+----------+--------------------------+

　　不存在

　　+----+--------------------+--------+------------+-------+-----------------+-----------------+---------+------------------+------+----------+--------------------------+

| 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表中选择数据行，然后使用嵌套循环连接（依赖子查询）从EVENTS表中查询数据，都使用EVENTS_USER_IDX索引在子查询中选择行，每一步都估计相同的记录数。

　　但是仔细看一下连接类型（join）——类型列，使用了NOT IN，但使用了NOT EXISTS。再看一下 ref 列：NOT EXISTS 与外部字段显式关联，NOT IN 使用函数，这里发生了什么。

　　index_subquery 连接类型意味着 MySQL 将扫描索引以查找子查询的相关行，这很好。EVENTS_USER_IDX 属于“窄”类型：只有一列，因此引擎不会读取大量数据块来查找与外部查询匹配的 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`)))))))

　　关于 EVENTS_USER_IDX 检查 NULL 找不到合适的解释，我的理解是：优化器断定它正在执行一个 IN 子查询，结果集可以收录 NULL，在做出这个决定时，它没有考虑 where 子句750万条数据中的空检查结果是检查USER_ID为空的记录，以及匹配外部查询关联的记录。通过“检查”，我的意思是它将读取表格行并应用非空条件。同样，根据运行查询所需的时间，我认为它对外部查询中的每个候选值都执行此操作。

　　因此，当您对 NULLable 列使用 IN 或 NOT IN 子查询时，请仔细考虑使用 EXISTS 或 NOT EXISTS。