что быстрее in или exists
Что быстрее in или exists
«Раньше было проще» — Подумал я, садясь за оптимизацию очередного запроса в SQL management studio. Когда я писал под MySQL, реально все было проще — или работает, или нет. Или тормозит или нет. Explain решал все мои проблемы, больше ничего не требовалось. Сейчас у меня есть мощная среда разработки, отладки и оптимизации запросов и процедур/функций, и все это нагромождение создает по-моему только больше проблем. А все почему? Потому что встроенный оптимизатор запросов — зло. Если в MySQL и PostgreSQL я напишу
и в каждой из табличек будет хотя бы по 5к строк — все зависнет. И слава богу! Потому что иначе в разработчике, в лучшем случае, вырабатывается ленность писать правильно, а в худшем он вообще не понимает что делает! Ведь этот же запрос в MSSQL пройдет аналогично
Встроенный оптимизатор причешет быдлозапрос и все будет окей.
Он так же сам решит, что лучше делать — exist или join и еще много чего. И все будет работать максимально оптимально.
Только есть одно НО. В один прекрасный момент оптимизатор споткнется о сложный запрос и спасует, и тогда вы получите большущую проблему. И получите вы ее, возможно, не сразу, а когда вес таблиц достигнет критической массы.
Так вот к сути статьи. exists и in — очень тяжелые операции. Фактически это отдельный подзапрос для каждой строчки результата. А если еще и присутствует вложенность, то это вообще туши свет. Все будет окей, когда возвращается 1, 10, 50 строк. Вы не почувствуете разницы, а возможно join будет даже медленнее. Но когда вытаскивается 500 — начнутся проблемы. 500 подзапросов в рамках одного запроса — это серьезно.
Пусть с точки зрения человеческого понимания in и exists лучше, но с точки зрения временных затрат для запросов, возвращающих 50+ строк — они не допустимы.
Нужно оговориться, что естественно, если где-то убывает — где-то должно прибывать. Да, join более ресурсоемок по памяти, ведь держать единовременно всю таблицу значений и оперировать ею — накладнее, чем дергать подзапросы для каждой строки, быстро освобождая память. Нужно смотреть конкретно по запросу и замерять — критично ли будет использование лишней памяти в угоду времени или нет.
Приведу примеры полных аналогий. Вообще говоря, я не встречал еще запросов такой степени сложности, которые не могли бы быть раскручены в каскад join’ов. Пусть на это уйдет день, но все можно раскрыть.
Повторюсь — данные примеры MSSQL оптимизатор оптимизирует под максимальную производительность и на таких простейших запросах тупняков не будет никогда.
Рассмотрим теперь пример реального запроса, который пришлось переписывать из-за того что на некоторых выборках он просто намертво зависал (структура очень упрощена и понятия заменены, не нужно пугаться некоей не оптимальности структуры бд).
Нужно вытащить все дубликаты «продуктов» в разных аккаунтах, ориентируясь на параметры продукта, его группы, и группы-родителя, если таковая есть.
Так вот это тот случай, когда оптимизатор спасовал. И для каждой строчки выполнялся тяжеленный exists, что убивало базу.
После данных преобразований производительность вьюхи увеличилась экспоненциально количеству найденных продуктов. Вернее сказать, время поиска оставалось практически независимым от числа совпадений и было всегда очень маленьким. Как и должно быть.
Это наглядный пример того, как доверие MSSQL оптимизатору может сыграть злую шутку. Не доверяйте ему, не ленитесь, join’те ручками, каждый раз думайте что лучше в данной ситуации — exists, in или join.
Оптимизация SQL в PostgreSQL: IN против EXISTS против ANY / ALL против JOIN
Это один из наиболее распространенных вопросов, задаваемых разработчиками, которые пишут SQL-запросы к базе данных PostgreSQL. Существует несколько способов, которыми подвыбор или поиск могут быть вставлены в оператор SQL. Оптимизатор PostgreSQL очень умен в оптимизации запросов, и многие запросы могут быть переписаны / преобразованы для повышения производительности.
Давайте обсудим тему с примером, для которого я использую схему, созданную pgbench.
Примечание: для тех, кто не знаком с pgbench, это инструмент для микро-бенчмаркинга, поставляемый с PostgreSQL. Пример схемы pgbench может быть инициализирован с некоторыми данными следующим образом:
Для этого примера я обновил баланс ветвей пары ветвей:
Запросы на включение
Задача SQL для этого примера: узнать количество учетных записей на ветку из pgbench_accounts для тех ветвей, где баланс на уровне филиала больше нуля. Этот запрос может быть написан четырьмя различными способами в соответствии со стандартами ANSI SQL.
1. Использование предложения IN
2. Используя предложение ANY
3. Использование предложения EXISTS
4. Использование INNER JOIN
При написании запроса можно предположить, что EXISTS и INNER JOIN могут быть лучше, потому что они могут использовать всю логику и оптимизацию для объединения двух таблиц, тогда как предложения IN и ANY должны иметь дело с подзапросами. Тем не менее, PostgreSQL (по крайней мере, PG 10 и выше) достаточно умен, чтобы создать один и тот же план выполнения для всех четырех вариантов!
Все вышеперечисленные запросы будут генерировать один и тот же план выполнения следующим образом:
Примечание: Примечание: подавите параллельное выполнение для лучшей читабельности и простого плана выполнения. Даже при параллельном плане выполнения все запросы создают один и тот же план выполнения.
Итак, можем ли мы заключить, что мы можем написать запрос так, как нам удобно, а ум PostgreSQL позаботится обо всем остальном? Подождите! Все может пойти по-другому, если мы возьмем сценарий исключения.
Запросы исключения
Задача SQL выглядит следующим образом : узнать количество учетных записей на ветку из pgbench_accounts EXCEPT для тех ветвей, где баланс на уровне ветви больше нуля.
Таким образом, четыре способа написания запросов становятся:
1. Использование NOT IN
2. Использование <> ALL
3. Использование NOT EXISTS
4. Использование LEFT JOIN и NULL
«NOT IN» и «<> ALL» создают план выполнения с подзапросами (SubPlan). Они соответственно:
Хотя NOT EXISTS и LEFT JOIN создают тот же план выполнения без подплана, как показано ниже:
Но что, если подзапросом возвращено большое количество строк (несколько сотен тысяч строк)? Давайте попробуем простой пример:
В этом случае план выполнения:
В этом случае план выполнения переключается на материализацию результата подплана, и расчетная стоимость возрастает до 25831564501.02! (При настройках по умолчанию в PostgreSQL, если количество строк из t2 меньше, чем приблизительно 100 КБ, используется хэшированный подплан, как мы уже обсуждали.)
Это приведет к существенному снижению производительности. Таким образом, предложение IN прекрасно работает, если подплан выбирает меньшее количество строк.
Подвох здесь в том, что когда происходит разработка, в таблицах будет меньше строк, и они будут работать по-разному с увеличением количества строк, так как план выполнения смещается и может привести к большим проблемам с производительностью в реальном производстве.
Есть ли еще сложности, о которых мы должны знать?
Да, могут быть преобразования типов данных, когда мы пишем запрос другим способом.
Например, утверждение типа:
приводит к неявному преобразованию типов данных значений полей в текст.
Несмотря на то, что предложение IN преобразуется в предложение ANY, преобразование типа данных поля «gen» не выполняется. И указанные значения ‘M’, ‘F’ конвертируются в bpchar, который является внутренним эквивалентом CHAR.
Резюме
При написании этого поста я намерен не отдавать предпочтение какому-либо конкретному способу написания запроса, а пролить некоторый свет на то, где что-то может пойти не так и что следует учитывать.
Старайтесь не думать от «Как разбить логику» на подзапросы.
Никогда не предполагайте, что запрос работает хорошо с небольшим количеством данных в таблице.
Используйте план EXPLAIN, чтобы понять, что происходит в фоновом режиме.
В общем случае EXISTS и прямое соединение таблиц часто приводят к хорошим результатам. PostgreSQL во многих случаях оптимизирует предложение IN для хешированного подплана. «IN» может привести к лучшему плану и выполнению в некоторых определенных ситуациях. Опять же, все зависит от того, как запрос переписан / преобразован внутри. Для лучшей оптимизации стоит потратить время на переписывание запросов.
Разница между EXISTS и IN в SQL?
В чем разница между предложением EXISTS и IN в SQL?
ОТВЕТЫ
Ответ 1
Ключевое слово exists может использоваться таким образом, но на самом деле оно предназначено как способ избежать подсчета:
in лучше всего использовать, когда у вас есть статический список:
Ответ 2
EXISTS сообщит вам, возвращает ли запрос какие-либо результаты. например:.
IN используется для сравнения одного значения с несколькими и может использовать литеральные значения, например:
Ответ 3
На основе оптимизатора правил:
На основе оптимизатора затрат:
Ответ 4
Я предполагаю, что вы знаете, что они делают, и поэтому используются по-разному, поэтому я буду понимать ваш вопрос так: Когда было бы хорошей идеей переписать SQL для использования IN вместо EXISTS или наоборот наоборот.
Это справедливое предположение?
Изменить. Причина, по которой я спрашиваю, заключается в том, что во многих случаях вы можете переписать SQL на основе IN для использования EXISTS вместо этого, и наоборот, а для некоторых движков базы данных оптимизатор запросов будет обрабатывать два по-разному.
можно переписать на:
Итак, мой вопрос по-прежнему стоит, является ли исходный плакат вопросом о том, что делает IN и EXISTS, и, следовательно, как его использовать, или он попросит переписать SQL, используя IN для использования EXISTS вместо этого, или наоборот, будет хорошая идея?
Ответ 5
Запрос 1
Запрос 2
Если в t1 ваш id имеет нулевое значение, то Query 1 найдет их, но Query 2 не сможет найти нулевые параметры.
Я имею в виду, что IN не может сравнивать ничего с NULL, поэтому он не имеет результата для null, но EXISTS может сравнивать все с нулевым.
Ответ 6
Ответ 7
Но IN менее эффективен, поэтому Exists быстрее.
Ответ 8
IN поддерживает только отношения равенства (или неравенство, когда ему предшествует НЕ).
Это синоним = любой/= some, например
Предполагается, что производительность и технические различия между EXISTS и IN могут возникать из-за конкретных реализаций/ограничений/ошибок поставщика, но во многих случаях это ничего, кроме мифов, созданных из-за отсутствия понимания внутренних систем баз данных.
Определение таблиц, точность статистики, конфигурация базы данных и версия оптимизатора оказывают влияние на план выполнения и, следовательно, на показатели производительности.
Ответ 9
IN следует извлекать, если значение конкретного столбца содержит IN список (1,2,3,4,5) Например. Выберите клиентов, которые находятся в следующих zipcodes, то есть значения zip_code находятся в списке (. ).
Когда использовать один над другим. когда вы чувствуете, что он читает правильно (лучше взаимодействует).
Ответ 10
Выше запроса вернет все записи, а ниже будет возвращено пустое.
Попробуйте и посмотрите результат.
Ответ 11
Ответ 12
Какой из них быстрее, зависит от количества запросов, полученных внутренним запросом:
EXIST оценивает значение true или false, но IN сравнивает множественное значение. Когда вы не знаете, что запись существует или нет, вы должны выбрать EXIST
Ответ 13
Причина в том, что оператор EXISTS работает на основе принципа «по крайней мере, найденного». Он возвращает true и останавливает таблицу сканирования, если найдена хотя бы одна соответствующая строка.
С другой стороны, когда оператор IN объединен с подзапросом, MySQL сначала обрабатывает подзапрос, а затем использует результат подзапроса для обработки всего запроса.
Общее правило состоит в том, что если в подзапрос содержит большой объем данных, оператор EXISTS обеспечивает лучшую производительность.
Однако запрос, который использует оператор IN, будет работать быстрее, если результирующий набор, возвращенный из подзапроса, очень мал.
Ответ 14
По той же причине, почему запрос не возвращает значение для = NULL vs имеет значение NULL. http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/
Ответ 15
Вы можете использовать подзапрос, чтобы проверить, существует ли набор записей. Для этого вам нужно использовать предложение exists с подзапросом. Ключевое слово exists всегда возвращает значение true или false.
Ответ 16
Я считаю, что у этого есть прямой ответ. Почему бы вам не проверить его у людей, которые разработали эту функцию в своих системах?
Если вы разработчик MS SQL, вот ответ от Microsoft.
Определяет, соответствует ли указанное значение любому значению в подзапросе или списке.
Задает подзапрос для проверки существования строк.
Ответ 17
Я обнаружил, что использование ключевого слова EXISTS часто очень медленное (это очень верно в Microsoft Access). Вместо этого я использую оператор объединения таким образом: should-i-use-the-keyword-exists-in-sql
Ответ 18
In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.
Ответ 19
EXISTS работает быстрее, чем IN. Если большинство критериев фильтра находится в подзапросе, тогда лучше использовать IN и если большинство критериев фильтра находится в основном запросе, то лучше использовать EXISTS.
Ответ 20
Если вы используете оператор IN, механизм SQL будет сканировать все записи, извлеченные из внутреннего запроса. С другой стороны, если мы используем EXISTS, механизм SQL остановит процесс сканирования, как только найдет совпадение.
Какая конструкция работает быстрее IN или EXISTS?
Вопрос, какая более производительная?
3 ответа 3
Кроме того чисто теоретически в случае in оптимизатор может начать выполнение с подзапроса и потом пытаться выбрать записи из основной таблицы по условию. В случае exists оптимизатор может решить идти по основной таблице, а для каждой ее записи проверять подзапрос. Но на практике оптимизатор не забудет посмотреть какие индексы есть в наличии, посмотреть статистику по таблицам и индексам и примет решение исходя из этой информации.
Но какой именно путь окажется на самом деле более быстрым предсказать в общем случае невозможно. Необходимо учитывать множество факторов. Если вас интересует скорость работы запроса, то для каждого запроса надо смотреть план выполнения и если план не устраивает пробовать разные варианты.
А единого рецепта что какая то конструкция быстрее или медленнее быть не может. Надо так же обратить внимание на то, что разные релизы СУБД (даже не крупные версии) могут по разному оптимизировать тот или иной запрос.
EXISTS
TRUE if a subquery returns at least one row.
IN
It tests a value for membership in a list of values or subquery (Equal-to-any-member-of test).
Попробуем с такими таблицами:
И сформулируем две задачи, соответствующие описаниям условий выше:
из таблицы t1 надо получить все записи, для которых в таблице t2 существует как минимум одна запись с тем же кодом
из таблицы t1 надо получить все записи с кодами, которые содержаться в листе кодов, полученных из таблицы t2
Составим запросы для решния этих задач и посммотрим как они выполняются:
Запрос #1
Как видим, оптимизатор посмотрел, что надо получить в результате каждого запроса, какие индексы построены, статистику на таблицы и индексы, и в результате, составил один план выполнения для обоих задач. Тем самым, посчитав, что для их вполнения так будет быстрей и эффективней.
если это условие применимо к главному запросу, то лучше использовать EXISTS
если оно по больше подходит к подзапросу, то лучшим выбором будет IN
Не стоит удивляться, что оптимизатор и на этот раз создаст один план, ведь обе таблицы идентичны.
Пробуйте оба варианта запроса на реальных данных (или максимально приближенном к ним примере). Сравните планы выполнения запросов. Задайте новый вопрос с примером данных и структурой таблиц, если есть подозрение, что оптимизатор выбрал не самый эффективный план и вследствии того, производительность отличается от ожидаемой.
Если отбросить тему про то что некоторые условия могут описаны только с помощью in, то при небольшом количестве возвращаемых записей в in и небольшом количестве записей в таблице из которой ведется выборка, разница будет смешной или даже не заметной. Все изменится тогда, когда либо в таблице, из которой ведется выборка, много записей, либо в таблице из которой выбирает in много записей, или и там и там их много. В этом случае, как я понял, появляется огромная разница за счет того, что exists находит первую подходящую запись и на этом заканчивает свою работу, если есть по соот. полю индекс, то это просто спросить значение в индексе и все, если мы говорим об in, то он сначала выбирает запросом все подходящие записи, а потом в полученном наборе начинает лопатить процессором в поисках первой подходящей записи, а позицию этой записи предугадать невозможно, т.е. в среднем будет перелопачиваться половина набора вернувшегося в in для каждой записи из выбираемой таблицы и это если построитель плана запроса применит in в конце накладываемых условий выборки, а не в начале, что приведет к тому, что количество проверок будет еще больше. В такой ситуации, логично было бы построить по значениям возвращенным in на лету индекс, но этого почему-то не делается, по крайней мере в PG.
Немного сравнительной статистики, есть 2 таблицы, одна 2 ляма записей, другая 40 лямов записей, выборка из первой таблицы всех записей отсутствующих во второй, exists 4-5 сек, in 6-7 часов, в результирующей выборке около 900K записей.
Всё ещё ищете ответ? Посмотрите другие вопросы с метками sql oracle или задайте свой вопрос.
Похожие
Подписаться на ленту
Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.
дизайн сайта / логотип © 2021 Stack Exchange Inc; материалы пользователей предоставляются на условиях лицензии cc by-sa. rev 2021.12.6.40898
Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.
SQL Server IN и EXISTS Производительность
Мне любопытно, что из следующего было бы более эффективным?
Я всегда был немного осторожен в использовании, IN потому что считаю, что SQL Server превращает набор результатов в большой IF оператор. Для большого набора результатов это может привести к снижению производительности. Для небольших наборов результатов я не уверен, что это предпочтительнее. Для больших наборов результатов не было EXISTS бы более эффективным?
EXISTS будет быстрее, потому что, как только двигатель обнаружит попадание, он перестанет искать, поскольку условие подтвердилось.
При наличии IN он соберет все результаты подзапроса перед дальнейшей обработкой.
Принятый ответ недальновиден, и вопрос в этом немного расплывчатый:
1) Также не указывайте явно, присутствует ли индекс покрытия слева, справа или с обеих сторон.
2) Ни то, ни другое не учитывает размер набора левой стороны ввода и набора правой стороны ввода.
(В вопросе просто упоминается общий большой набор результатов ).
Я считаю, что оптимизатор достаточно умен, чтобы конвертировать между «в» и «существует», когда существует значительная разница в стоимости из-за (1) и (2), в противном случае он может использоваться просто как подсказка (например, существует для поощрения использования индекс с возможностью поиска справа).
Я провел некоторое тестирование SQL Server 2005 и 2008, и как EXISTS, так и IN вернулись с точно таким же фактическим планом выполнения, как утверждали другие. Оптимизатор оптимален. 🙂
Здесь есть много вводящих в заблуждение ответов, в том числе один, получивший высокую оценку (хотя я не верю, что их операции навредили). Короткий ответ: это то же самое.
Я бы выбрал EXISTS через IN, см. Ссылку ниже:
Существует распространенное заблуждение, что IN ведет себя одинаково с EXISTS или JOIN с точки зрения возвращаемых результатов. Это просто неправда.
IN: возвращает true, если указанное значение соответствует любому значению в подзапросе или списке.
Существует: возвращает true, если подзапрос содержит какие-либо строки.
Присоединиться: объединяет 2 набора результатов в столбце соединения.
Планы выполнения в этих случаях обычно будут идентичными, но пока вы не увидите, как оптимизатор учитывает все другие аспекты индексов и т. Д., Вы никогда не узнаете.
Обычно EXISTS используется в коррелированном подзапросе, что означает, что вы ПРИСОЕДИНЯЕТЕСЬ к внутреннему запросу EXISTS со своим внешним запросом. Это добавит больше шагов для получения результата, так как вам нужно решить соединения внешнего запроса и соединения внутреннего запроса, а затем сопоставить их предложения where для объединения обоих.
Обычно IN используется без корреляции внутреннего запроса с внешним запросом, и это может быть решено всего за один шаг (в лучшем случае).
Если вы используете IN, а результатом внутреннего запроса являются миллионы строк с разными значениями, он, вероятно, будет выполнять МЕНЬШЕ, чем EXISTS, учитывая, что запрос EXISTS является производительным (имеет правильные индексы для соединения с внешним запросом).
Если вы используете EXISTS и соединение с вашим внешним запросом является сложным (требуется больше времени для выполнения, нет подходящих индексов), он замедлит запрос на количество строк во внешней таблице, иногда расчетное время для завершения может быть в днях. Если количество строк приемлемо для вашего оборудования или количество данных правильное (например, меньше значений DISTINCT в большом наборе данных), IN может работать быстрее, чем EXISTS.
Все вышеперечисленное будет отмечено, когда у вас будет достаточное количество строк в каждой таблице (честно говоря, я имею в виду то, что превышает пороги обработки вашего процессора и / или RAM для кэширования).
Итак, ОТВЕТ ЗАВИСИТ. Вы можете написать сложный запрос внутри IN или EXISTS, но, как правило, вы должны попытаться использовать IN с ограниченным набором различных значений и EXISTS, когда у вас много строк с большим количеством различных значений.
Хитрость заключается в том, чтобы ограничить количество сканируемых строк.