Подзапросы против объединений
Я провел рефакторинг медленного раздела приложения, унаследованного от другой компании, чтобы использовать внутреннее соединение вместо подзапроса, например:
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
Не дубликат. Этот вопрос касается разительной разницы в производительности. Другой вопрос является более общим, открытым о плюсах и минусах каждого подхода и почему один подход кажется более популярным.
@simhumileco Это не улучшение, это не разница, это противоречит тому, что написал автор, и такое редактирование стиля кода неуместно. Когда мне следует вносить правки в код?
Привет @philipxy, я не собирался вмешиваться в мысли автора, а только для того, чтобы фрагмент кода был более читабельным и написанным более аккуратно.
Ответы 14
Запустите план объяснения для каждой версии, он расскажет вам, почему.
Посмотрите план запроса для каждого запроса.
В которой и Присоединиться могут быть реализованы как правило с использованием одного и того же плана выполнения, поэтому как правило имеет нулевое ускорение от переключения между ними.
Ха-ха, я <3 Sql убираю это голосование против, потому что они не знают, как читать планы запросов.
Вы выполняете подзапрос один раз для каждой строки, тогда как соединение происходит по индексам.
Я не думаю, что это правда. Механизм SQL должен выполнить подзапрос только один раз и использовать результат в виде списка.
Это зависит - если подзапрос каким-то образом коррелирует с внешним запросом (использует его данные), он выполняется с каждой строкой.
Возможно, в данном случае это правда, но в целом это не так.
EXPLAIN OP говорит DEPENDENT SUBQUERY, что является наиболее четким индикатором такого поведения.
Оптимизатор работал не очень хорошо. Обычно их можно преобразовать без каких-либо различий, и оптимизатор может это сделать.
С подзапросом вы должны повторно выполнить второй SELECT для каждого результата, и каждое выполнение обычно возвращает 1 строку.
При объединении второй SELECT возвращает намного больше строк, но вам нужно выполнить его только один раз. Преимущество в том, что теперь вы можете присоединиться к результатам, а соединение отношений - это то, в чем база данных должна быть хороша. Например, оптимизатор может определить, как теперь лучше использовать индекс.
Это не столько подзапрос, сколько предложение IN, хотя объединения лежат в основе, по крайней мере, механизма SQL Oracle и выполняются очень быстро.
где на самом деле не является плохим по своей сути.
Подзапрос, вероятно, выполнял «полное сканирование таблицы». Другими словами, не использовать индекс и возвращать слишком много строк, которые требовалось отфильтровать в запросе Where from основного запроса.
Конечно, предположение без подробностей, но это обычная ситуация.
Обычно это результат того, что оптимизатор не может определить, что подзапрос может быть выполнен как соединение, и в этом случае он выполняет подзапрос для каждой записи в таблице, а не присоединяется к таблице в подзапросе к таблице, которую вы запрашиваете. Некоторые из более «корпоративных» баз данных лучше справляются с этим, но они все же иногда пропускают это.
«Коррелированный подзапрос» (т.е. такой, в котором условие where зависит от значений, полученных из строк содержащего запроса) будет выполняться один раз для каждой строки. Некоррелированный подзапрос (тот, в котором условие where не зависит от содержащего запроса) будет выполняться один раз в начале. Механизм SQL автоматически делает это различие.
Но да, план объяснения даст вам грязные детали.
Обратите внимание, что DEPENDENT SUBQUERY означает то же самое, что и «коррелированный подзапрос».
Это несколько общий вопрос, поэтому вот общий ответ:
Как правило, запросы занимают больше времени, если в MySQL нужно отсортировать тонны строк.
Сделай это:
Запустите EXPLAIN для каждого из запросов (JOIN'ed, затем Subqueried) и опубликуйте результаты здесь.
Я думаю, что увидеть разницу в интерпретации этих запросов MySQL было бы полезно для всех.
перед запуском запросов к набору данных они проходят через оптимизатор запросов, оптимизатор пытается организовать запрос таким образом, чтобы он мог удалить как можно больше кортежей (строк) из набора результатов как можно быстрее. Часто, когда вы используете подзапросы (особенно плохие), кортежи не могут быть исключены из набора результатов до тех пор, пока не начнется выполнение внешнего запроса.
Не видя запроса, трудно сказать, что было такого плохого в оригинале, но я предполагаю, что это было то, что оптимизатор просто не мог сделать намного лучше. Запуск объяснения покажет вам метод оптимизатора для получения данных.
Подзапрос where должен запускать 1 запрос для каждой возвращаемой строки. Внутреннее соединение просто должно выполнить 1 запрос.
Вот пример того, как подзапросы оцениваются в MySQL 6.0.
Новый оптимизатор преобразует такие подзапросы в соединения.
Это отличная статья об улучшенном оптимизаторе MySQL 6.0, спасибо
Взято из Справочного руководства (14.2.10.11 Перезапись подзапросов как объединений):
A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone.
Таким образом, подзапросы могут быть медленнее, чем LEFT [OUTER] JOINS.
Возможный дубликат Присоединиться к подзапросу