Mysql Exists vs IN - коррелированный подзапрос против подзапроса?

Мне интересно, как выполнение EXISTS() должно быть быстрее, чем IN().

Я был отвечая на вопрос, когда Билл Карвин поднял хороший момент. когда вы используете EXISTS(), он использует коррелированный подзапрос (зависимый подзапрос), а IN() использует только подзапрос.

EXPLAIN показывает, что 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                 |
+-------+-------------------+-----------+-------+---------------+-----------+--------+----------+--------+------------------------------+

FEW вопросы

В приведенных выше объяснениях, как EXISTS имеют using where и using index в дополнениях, но NOT EXISTS не имеет using where в дополнительных настройках?

Как коррелированный подзапрос быстрее, чем подзапрос?

Ответ 1

Это ответ на RDBMS-агностик, но тем не менее может помочь. По моему мнению, коррелированный (ака, зависимый) подзапрос, пожалуй, наиболее часто ошибочно обвиняемый виновником плохой работы.

Проблема (как это чаще всего описывается) заключается в том, что она обрабатывает внутренний запрос для каждой строки внешнего запроса. Поэтому, если внешний запрос возвращает 1000 строк, а внутренний запрос возвращает 10 000, тогда ваш запрос должен прокручивать через 10 000 000 строк (внешний × внутренний) для получения результата. По сравнению с 11 000 строк (внешний + внутренний) из некоррелированного запроса по тем же наборам результатов это не очень хорошо.

Однако это самый худший сценарий. Во многих случаях СУБД сможет использовать индексы для резкого сокращения числа строк. Даже если только внутренний запрос может использовать индекс, 10 000 строк становятся ~ 13 исками, что снижает общее количество до 13 000.

Оператор exists может прекратить обработку строк после первого, сокращая стоимость запроса, особенно когда большинство внешних строк соответствуют хотя бы одной внутренней строке.

В некоторых редких случаях я видел, как SQL Server 2008R2 оптимизировал коррелированные подзапросы для объединения слияния (которое обходит оба набора только один раз - наилучший возможный сценарий), где подходящий индекс можно найти как в внутренних, так и в внешних запросах.

Реальным виновником плохой производительности не обязательно являются коррелированные подзапросы, но вложенные проверки.

Ответ 2

Это зависит от версии MySQL - в оптимизаторе запросов MySQL есть ошибки в версиях до 6.0.

Подзапросы с "IN" не были оптимизированы правильно (но выполнялись снова и снова, как и зависимые). Эта ошибка не влияет на запросы exists или объединения.

Проблема заключается в том, что для оператора, который использует подзапрос IN, оптимизатор переписывает его как коррелированный подзапрос. Рассмотрим следующее оператор, который использует некоррелированный подзапрос:

SELECT... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

Оптимизатор перезаписывает оператор в коррелированный подзапрос:

SELECT... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

Если внутренний и внешний запросы возвращают строки M и N, соответственно, время выполнения становится порядка O (M × N), а не O (M + N), как это было бы для некоррелированного подзапроса.

Refs.

Ответ 3

Как вы уже знаете, подзапрос не использует значения из внешнего запроса, поэтому он выполняется только один раз. Коррелированный подзапрос синхронизирован, поэтому он выполняется для каждой строки, обработанной во внешнем запросе.

Преимущество использования EXISTS заключается в том, что если он считается выполненным, выполнение подзапроса прекращается после возврата хотя бы одной строки. Таким образом, это может быть быстрее простого подзапроса. Но это не общее правило! Все зависит от запроса, который вы выполняете, оптимизатора запросов и версии механизма выполнения SQL.

EXISTS рекомендуется использовать, если у вас есть условный оператор if, потому что он, конечно, быстрее, чем count.

Вы не можете сравнивать оба подзапроса, используя простой тест из 4 или 3 запросов.

Надеюсь, это полезно!