mysql in 加子查询

mysql in 加子查询"/>

mysql in 加子查询

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

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

解释显示，EXISTS并且NOT EXISTS两者都使用了一个依赖子查询，并且IN / NOT

IN都只使用了一个子查询..所以我很好奇关联子查询如何比子查询更快？

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

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在演员？

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