Mysql存在与IN —相关子查询与子查询？

Question

我很好奇为何EXISTS()应该比更快地执行IN()。

当比尔·卡尔文提出一个要点时，我正在回答一个问题。当您使用EXISTS()它时，它使用相关子查询（依赖子查询），而IN（）仅使用子查询。

解释显示，EXISTS并且NOT EXISTS两者都使用了一个依赖子查询，并且IN / NOT IN都只使用了一个子查询..所以我很好奇关联子查询如何比子查询更快？

我以前使用过EXISTS，它的执行速度比IN快，这就是我感到困惑的原因。

这是带有说明的SQLFIDDLE

EXPLAIN SELECT COUNT(t1.table1_id) 
FROM table1 t1 
WHERE EXISTS
(   SELECT 1 
    FROM table2 t2
    WHERE t2.table1_id <=> t1.table1_id
);

+-------+-----------------------+-----------+-------+---------------+-----------+--------+--------------------------+--------+------------------------------+
| ID    |   SELECT_TYPE         |   TABLE   | TYPE  | POSSIBLE_KEYS |   KEY     |KEY_LEN |  REF                     |   ROWS |  EXTRA                       |
+-------+-----------------------+-----------+-------+---------------+-----------+--------+--------------------------+--------+------------------------------+
|  1    |   PRIMARY             |   t1      | index | (null)        |   PRIMARY |   4    | (null)                   |   4    |  Using where; Using index    |
|  2    |   DEPENDENT SUBQUERY  |   t2      | REF   | table1_id     |  table1_id|   4    | db_9_15987.t1.table1_id  |   1    |  Using where; Using index    |
+-------+-----------------------+-----------+-------+---------------+-----------+--------+--------------------------+--------+------------------------------+

EXPLAIN SELECT COUNT(t1.table1_id) 
FROM table1 t1 
WHERE NOT EXISTS
(   SELECT 1 
    FROM table2 t2
    WHERE t2.table1_id = t1.table1_id
);
+-------+-----------------------+-----------+-------+---------------+-----------+--------+--------------------------+--------+------------------------------+
| ID    |   SELECT_TYPE         |   TABLE   | TYPE  | POSSIBLE_KEYS |   KEY     |KEY_LEN |  REF                     |   ROWS |  EXTRA                       |
+-------+-----------------------+-----------+-------+---------------+-----------+--------+--------------------------+--------+------------------------------+
|  1    |   PRIMARY             |   t1      | index | (null)        |   PRIMARY |   4    | (null)                   |   4    |  Using where; Using index    |
|  2    |   DEPENDENT SUBQUERY  |   t2      | ref   | table1_id     |  table1_id|   4    | db_9_15987.t1.table1_id  |   1    |  Using index                 |
+-------+-----------------------+-----------+-------+---------------+-----------+--------+--------------------------+--------+------------------------------+

EXPLAIN SELECT COUNT(t1.table1_id) 
FROM table1 t1 
WHERE t1.table1_id NOT IN 
(   SELECT t2.table1_id 
    FROM table2 t2
);
+-------+-------------------+-----------+-------+---------------+-----------+--------+----------+--------+------------------------------+
| ID    |   SELECT_TYPE     |   TABLE   | TYPE  | POSSIBLE_KEYS |   KEY     |KEY_LEN |  REF     |   ROWS |  EXTRA                       |
+-------+-------------------+-----------+-------+---------------+-----------+--------+----------+--------+------------------------------+
|  1    |   PRIMARY         |   t1      | index | (null)        |   PRIMARY |   4    | (null)   |   4    |  Using where; Using index    |
|  2    |   SUBQUERY        |   t2      | index | (null)        |  table1_id|   4    | (null)   |   2    |  Using index                 |
+-------+-------------------+-----------+-------+---------------+-----------+--------+----------+--------+------------------------------+

一些问题

在上面的解释，如何做EXISTS具有using where和using index在群众演员，但NOT EXISTS没有using where在演员？

相关子查询如何比子查询更快？

Answer 1

这是与RDBMS无关的答案，但可能仍会有所帮助。以我的理解，相关的（又名，依存的）子查询可能是性能不佳的最常被错误指控的元凶。

问题（如最经常描述的那样）是它为外部查询的每一行处理内部查询。因此，如果外部查询返回1,000行，而内部查询返回10,000，则您的查询必须遍历10,000,000行（外部x内部）以产生结果。与相同结果集上的非相关查询的11,000行（外部+内部）相比，这是不好的。

但是，这只是最坏的情况。在许多情况下，DBMS将能够利用索引来大大减少行数。即使仅内部查询可以使用索引，10,000行还是〜13搜索，这将总数降低到13,000。

的exists操作者可在第一后停止处理的行，削减查询成本进一步，尤其是当最外层行匹配至少一个内部行。

在极少数情况下，我看到SQL Server 2008R2将相关子查询优化为合并联接（该联接仅遍历两个集合-
最好的情况），在内部和外部查询中都可以找到合适的索引。

导致性能下降的真正原因不一定是 相关子查询 ，而是 嵌套扫描 。