Подзапросы против присоединений

Я рефакторил медленный раздел приложения, которое мы унаследовали от другой компании, чтобы использовать внутреннее соединение вместо подзапроса, такого как:

WHERE id IN (SELECT id FROM ...)

Реорганизованный запрос выполняется примерно в 100 раз быстрее. (~ 50 секунд до ~ 0,3) Я ожидал улучшения, но кто-нибудь может объяснить, почему это так радикально? Все столбцы, использованные в предложении where, были проиндексированы. SQL выполняет запрос в предложении where один раз для строки или чего-то еще?

Обновление - Объясните результаты:

Разница заключается во второй части запроса "where id in()" -

2   DEPENDENT SUBQUERY  submission_tags ref st_tag_id   st_tag_id   4   const   2966    Using where

vs 1 проиндексированная строка с объединением:

    SIMPLE  s   eq_ref  PRIMARY PRIMARY 4   newsladder_production.st.submission_id  1   Using index

Ответ 1

A "коррелированный подзапрос" (то есть тот, в котором условие where зависит от значений, полученных из строк содержащего запроса) будет выполняться один раз для каждой строки. Некорректированный подзапрос (тот, в котором условие where не зависит от содержащего запроса) будет выполняться один раз в начале. Механизм SQL делает это различие автоматически.

Но, да, объяснительный план даст вам грязные детали.

Ответ 2

Вы выполняете подзапрос один раз для каждой строки, тогда как соединение происходит по индексам.

Ответ 3

Здесь пример того, как подзапросы оцениваются в MySQL 6.0.

Новый оптимизатор преобразует такие подзапросы в объединения.

Ответ 4

Запустите план объяснения для каждой версии, он скажет вам, почему.

Ответ 5

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

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

Ответ 6

Обычно это результат оптимизации, который не может понять, что подзапрос может выполняться как объединение, и в этом случае он выполняет подзапрос для каждой записи в таблице, а затем присоединяет таблицу в подзапросе к таблице, которую вы запрашивают. Некоторые из более "хозяйственных" баз данных лучше в этом, но они по-прежнему упускают его иногда.

Ответ 7

Этот вопрос несколько общий, поэтому здесь общий ответ:

В основном, запросы занимают больше времени, когда MySQL имеет множество строк для сортировки.

Сделайте это:

Запустите EXPLAIN для каждого из запросов (JOIN'ed, затем Subqueried) и опубликуйте результаты здесь.

Я думаю, что различие в интерпретации MySQL этих запросов было бы опытом обучения для всех.

Ответ 8

В подзапросе должен быть запущен 1 запрос для каждой возвращаемой строки. Внутреннее соединение просто должно выполнить 1 запрос.

Ответ 9

Посмотрите план запроса для каждого запроса.

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

Ответ 10

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

Ответ 11

Подзапрос, вероятно, выполнял "полное сканирование таблицы". Другими словами, не использовать индекс и возвращать слишком много строк, из которых необходимо, чтобы отфильтровать "От главного запроса".

Просто догадывайтесь без подробностей, конечно, но об общей ситуации.

Ответ 12

При подзапросе вы должны повторно выполнить второй SELECT для каждого результата, и каждое выполнение обычно возвращает 1 строку.

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

Ответ 13

Это не столько подзапрос, сколько предложение IN, хотя объединения основаны, по крайней мере, на Oracle SQL engine и работают очень быстро.

Ответ 14

Взято из справочного руководства (14.2.10.11 Перезаписывание подзапросов как соединений):

LEFT [OUTER] JOIN может быть быстрее, чем эквивалентный подзапрос, потому что сервер может оптимизировать его лучше - факт, который не специфичен только для MySQL Server.

Таким образом, подзапросы могут быть медленнее, чем LEFT [OUTER] JOINS.