Sql что быстрее in или join

SQL JOIN vs IN performance?

I have a case where using a JOIN or an IN will give me the correct results. Which typically has better performance and why? How much does it depend on what database server you are running? (FYI I am using MSSQL)

7 Answers 7

Generally speaking, IN and JOIN are different queries that can yield different results.

is not the same as

, unless b.col is unique.

However, this is the synonym for the first query:

See this article in my blog for performance details:

Funny you mention that, I did a blog post on this very subject.

Short answer: you have to test it and individual databases vary a lot.

As a general rule of thumb, I think if you have indices on your foreign key columns, and if you’re using only (or mostly) INNER JOIN conditions, then the JOIN will be slightly faster.

But as soon as you start using OUTER JOIN, or if you’re lacking foreign key indexes, the IN might be quicker.

This Thread is pretty old but still mentioned often. For my personal taste it is a bit incomplete, because there is another way to ask the database with the EXISTS keyword which I found to be faster more often than not.

So if you are only interested in values from table a you can use this query:

The difference might be huge if col is not indexed, because the db does not have to find all records in b which have the same value in col, it only has to find the very first one. If there is no index on b.col and a lot of records in b a table scan might be the consequence. With IN or a JOIN this would be a full table scan, with EXISTS this would be only a partial table scan (until the first matching record is found).

If there a lots of records in b which have the same col value you will also waste a lot of memory for reading all these records into a temporary space just to find that your condition is satisfied. With exists this can be usually avoided.

I have often found EXISTS faster then IN even if there is an index. It depends on the database system (the optimizer), the data and last not least on the type of index which is used.

Источник

Sql что быстрее in или join

«Раньше было проще» — Подумал я, садясь за оптимизацию очередного запроса в 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» может привести к лучшему плану и выполнению в некоторых определенных ситуациях. Опять же, все зависит от того, как запрос переписан / преобразован внутри. Для лучшей оптимизации стоит потратить время на переписывание запросов.

Источник

Исследуем производительность JOIN в MySQL

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

Давайте исследуем этот вопрос более подробно и посмотрим, действительно ли JOIN — это плохо, и когда вообще стоит задумываться об этом.

О чём я не буду писать

Для начала я бы хотел сразу сказать, что я не буду делать:

— тюнинг MySQL: все настройки берутся по умолчанию ( в том числе innodb_buffer_pool_size = 8 Мб и прочее )
— интеграцию с языками программирования: все запросы будут делаться через MySQL клиент Sequel Pro, и время будет замеряться исходя из его показаний
— очевидные вещи, вроде джойна при выборке 3х строк: вопрос, экономить на спичках, или нет, я рассматривать не хочу — мы будем рассматривать экономию в десятки раз, а не десятки процентов

Начальные условия

У нас будет две простых до безобразия таблички ( таблицы были сделаны просто для примера и заполнены случайными данными ):

Calls — 10 млн строк:

Users — 100 тыс строк:

Названия, я думаю, говорят сами за себя, индексы есть только по первичному ключу (id). Чисто в принципе, мы бы могли создать какие-нибудь индексы, которые бы нам помогли выполнять те запросы, которые мы будем исследовать, но у нас цель другая, а именно — исследовать, насколько быстро работает JOIN.

Типы таблиц

Для целей исследования, таблица Calls бралась двух видов — MyISAM и InnoDB, а таблица Users, к которой мы делаем JOIN, трёх видов — MyISAM, InnoDB и MEMORY

First blood

Все тесты проводились на моём ноутбуке, с MySQL версии 5.5.9 на Mac OS X с дефолтными настройками от MAMP. Все таблицы вмещались в память полностью, запросы прогонялись несколько раз, чтобы убедиться, что всё попадает в кеш.

Для начала, давайте просто посмотрим на скорость просмотра строк в MyISAM и InnoDB, выполнив такой запрос (напомню, что индексов ни по цене, ни по user_id нет — мы измеряем скорость FULL SCAN в MySQL):

Результаты (погрешность менее 5%):

Не хочу заниматься более подробным изучением, почему на аггрегирующие выборки в MyISAM так влияет кол-во попавших под WHERE строк, но факт остается фактом — при полном последовательном просмотре таблицы, MyISAM быстрее InnoDB в 4.5 раза. Отсюда и мнение о том, что InnoDB «тормоз», и о том, что сама MySQL (с MyISAM) очень шустра.

Мини-вывод: при полном последовательном просмотре MyISAM в 5 раз быстрее InnoDB

Давайте теперь подключим к делу таблицу Users — не зря же мы её создавали.
Мы будем исследовать запросы такого плана:

Параметр для cost подбирается таким образом, чтобы под выборку попал определенный процент записей в таблице Calls

Если мы сделаем запрос, приведенный выше, к мускулю, с параметром cost, который будет соответствовать N% строк, то MySQL сделает джойн всего-лишь в N% случаев, а для остальных (100-N)% строк он JOIN делать не будет. Вот такой MySQL умница.

Давайте же приступим к результатам тестирования с JOIN:

FULL SCAN + JOIN 0.1% строк

100)MEMORY5 350 (

100)

Пока что времена отличаются очень несущественно от FULL SCAN. Оно и понятно — ведь JOIN делается для мизерного количества строк.

FULL SCAN + JOIN 1% строк

Забавно, да? Всего-лишь 1% строк джойнится, а результаты для MyISAM + MyISAM больше в 2 раза, чем для MyISAM + InnoDB. Довольно забавно, что JOIN к InnoDB в данном случае оказывается быстрее, чем JOIN к MyISAM. И это мы ещё не начали тестировать :)!

FULL SCAN + JOIN 10% строк

За державу (MyISAM) обидно, а что поделать… Выходит, MyISAM не такой уж шустрый… Или нет? Давайте посмотрим на результаты финального тестирования

FULL SCAN + JOIN 100% строк

Обратите внимание на чудовищные (!) времена выборок при JOIN с MyISAM. А вот InnoDB приятно удивил — благодаря своей архитектуре, JOIN не является слишком дорогой операцией для InnoDB. Если говорить честно, то я был сильно удивлен, когда получил такой результат, что второй по скорости JOIN вариант — это когда к MyISAM джойнят InnoDB.

Ну а с MEMORY, я думаю, всё ясно — MEMORY дает оверхед в 525% (4 435 ms) на джойн по PK, InnoDB дает оверхед в 1 380% (11 655 ms), а про MyISAM стыдно говорить.

Замена JOIN на IN(. )

Зоркий глаз мог заметить, что для нашего сценария (когда мы делаем JOIN к users, чтобы отсеять всех женщин из таблицы звонков) есть способ без JOIN, а с простым перечислением всех user_id в IN():

Такой запрос к таблице типа MyISAM отработает за 3 730 мс, а к InnoDB — за 8 290 мс. Зоркий глаз может заметить, что этот способ быстрее, чем JOIN к MEMORY, хоть и не намного. Этот способ подходит в случаях, если у вас очень быстрое подключение к MySQL (например UNIX socket). В остальных случаях, ИМХО, при таких количествах записей очевидно, что гонять огромное количество трафика между MySQL сервером и сервером приложений — не самая лучшая идея.

Выводы можете сделать сами: если нужно много джойнить, и почему-то нужна высокая производительность, используйте комбинацию MyISAM + InnoDB, или просто InnoDB + InnoDB, если джойнов больше одного. Ну а если вы — экстремал, можете использовать MyISAM + MEMORY для получения максимальной производительности при любых сценариях. Есть ещё вариант с MEMORY+MEMORY, но для большого количества записей я бы не стал так делать ;).

UPD: Хочется поблагодарить хабраюзера homm за очень полезные комментарии, например за этот. В общем, очень рекомендую прочитать комментарии, в них разъясняется много вещей, которые почему-то для читающих не были очевидны:
— кэш запросов отключен
— JOIN делается по первичному ключу
— индексы на таблицу Calls не создаются, потому что мы не ставим задачу оптимизировать какой-то конкретный запрос

Источник

Tips & tricks for MySQL Developers. Работа с SQL

Эта статья задумана мной как сборник некоторых интересных моментов по использованию и оптимизации SQL запросов в БД MySQL, на мой взгляд, плохо освещенных в интернете. Так, из статьи вы узнаете о конструкции with rollup, и о том, как переписать подзапросы in и not in на join’ы, а так же обновление и удаление данных в нескольких таблицах — одним запросом, и многое другое. Начнем по порядку.

Переписываем подзапросы с in и not in на join’ы

Одни из самых распространённых подзапросов являются запросы с in и not in. Причём, мы знаем, что в MySQL join запросы чаще всего отрабатывают быстрее за счёт эффективного построения плана выполнения запроса оптимизатором (в определённых условиях, а также для других БД это утверждение может быть диаметрально противоположным), поэтому попробуем переписать классические подзапросы на join. Как мы это будем делать? Для начала уясним то, что in запросы выводят все значения, которые присутствуют в обоих таблицах, а значит такому запросу будет однозначно соответствовать внутренний inner join. Запросы с not in наоборот выводят все значения, которые не присутствуют в подзапросе, а значит им уже будет соответствовать внешний outer join. Итак, начало положено, попробуем с этим что-нибудь сделать.

Для примера буду использовать тестовую БД world, которую можно взять с официального сайта mysql здесь

В БД world есть таблицы Country (страны) и CountryLanguage (официальные языки). Поставим себе задачу найти все страны, в которых говорят хотя бы на одном языке. Говоря другими словами, мы ищем территории с населением, у которого есть официальные языки. Напишем привычный in подзапрос:

На заметку, этот запрос можно переписать ещё и так:

Теперь, исходя из предположения выше, перепишем подзапрос на inner join:

Почти получилось, но у нас произошло дублирование данных, которое убираем через опцию distinct. Конечный вариант для всех полей таблицы получится таким:

Отлично! Подзапрос in успешно переписан на join.

Теперь немного сложнее — перепишем not in на outer join. Цель запроса — все территории, на которых не проживают люди и нет официальных языков. Снова вначале привожу стандартный not in подзапрос:

И показываю его же для not exists:

Как и в первом случае, перепишем на left join:

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

Такими нехитрыми преобразованиями мы смогли немного помочь оптимизатору запросов.

Сравнение строк в подзапросах

Бывают редкие случаи, когда нам нужно написать подзапрос, в котором сравнение происходит не по одному, а нескольким столбцам, однако писать так было бы явно НЕправильно:

Для этих целей существует SQL запрос:

Такой запрос называется «конструктором строк» и может быть подчёркнут функцией ROW(). В этом случае мы бы написали:

Несмотря на свою привлекательность, конструктор строк имеет ряд ограничений:
1. Подзапрос должен возвращать одну строку, а не несколько
2. Вы не можете использовать операторы сравнения или <>, хотя это ограничение можно обойти специальными словами all, any, in или exists

Стоит обратить внимание, что такую конструкцию можно использовать не только для подзапросов, но и в скалярных выражениях:

Правда, на практике, конструктор запросов не очень эффективен для скалярных выражений, поэтому перепишем запрос к нормальному виду:

Обновление и удаление данных одновременно из нескольких таблиц.

Возможно, кого-то удивит такой заголовок, но почему бы и нет? Начнём с обновления данных. Официальная документация говорит про следующий синтаксис:

Скорее всего, вы сделаете запрос вида:

С другой стороны, никто не мешает сделать запрос, который обновит данные сразу в двух, трёх и более таблицах:

Правда, он вряд ли будет иметь смысл, но, тем не менее, такое возможно.

С операцией удаления ещё интереснее обстоят дела. Официальная документация декларирует такой синтаксис:

Что соответствует запросам вида:

В этих двух запросах удаление происходит из таблицы t1, а t2 используется для создания условия выборки данных.

И как вы уже догадались, для удаления данных одновременно из двух таблиц делаем так:

Немного про OLAP. Модификатор WITH ROLLUP

Возможно те, кто сталкивался с такими промышленными БД как Oracle или SQL Server при чтении заголовка вскрикнут: «Ну надо же!», — но, увы, я сразу остужу их пламенные возгласы. С версии MySQL 4.1.1, когда появился модификатор with rollup, эта тема не продвинулась ни на миллиметр, поэтому никаких кубов данных вы не сможете построить встроенными средствами данной БД.

Для тех, кто не в курсе, что означает модификатор with rollup кратко поясню, что он используется для создания отчетов, содержащих подытоги и окончательное итоговое значение. В примерах, буду снова использовать базу world.

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

Суммарное и среднее число проживающих людей на всех географических территориях (регионах):

Суммарное и среднее число проживающих людей на всех континентах:

Суммарное и среднее число проживающих людей во всём мире:

Вместо выполнения этих запросов и последующего сложного объединения результатов, можно выполнить всего один:

Обратите внимание, что в некоторых строках в не агрегирующих колонках стоит NULL, что указывает на то, что данная строка является подытогом. Например, строку

нужно читать как в Южной Америке суммарное население составляет 345780000 человек, а среднее значение 24698571.4286

Является окончательным итогом по отношению к численности населения на всём земном шаре.

Положительный эффект модификатора with rollup заключается в том, что проход по записям происходит один раз! Кстати, эта функциональность очень удобна при выводе какой-либо статистики на сайте (программе). Если вас заинтересовала данная функциональность или остались вопросы, то за подробностями прошу в официальную документацию

Для переопределения этих ограничений выполните следующий запрос со своими параметрами:

Для просмотра текущих настроек:

Также возможно параметризировать настройки при запуске клиента в шелле

Эстетичный комментарий

И на закуску. После БД, отличных от MySQL, меня всегда удивляло, почему MySQL в однострочном комментарии, выглядящем как двойное тире, обязательно после себя требует пробел, табуляцию или другой управляющий символ, хотя по стандарту обязательного управляющего символа не должно быть. Согласитесь, когда пишешь какой-то запрос и нужно быстро закомментировать часть кода, уж очень долго ставить такое количество символов.

Что я имею ввиду. В MySQL мы пишем так:

(с пробелом перед SELECT), а в других БД:

(без управляющего символа).

Разгадка оказалась очень простой. Дело в том, что если вы напишите такой небрежный запрос

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

UDP:
В комментариях есть сомнения в целесообразности переписывания запросов с in на join. Ниже, мой небольшой бенчмарк.

JOIN vs IN vs EXISTS

При работе с индексом JOIN (7.84 сек) сильно проигрывает по сравнению с IN (1.74 сек) и EXISTS (2.44 сек).
Ниже пример, когда колонки t11 и t22 без индекса:

Поставил limit, чтобы долго не ждать ответ. На результат он не влияет.

OUTER JOIN vs NOT IN vs NOT EXISTS

Без индекса эти запросы в MySQL 5.5 отрабатываются примерно за одинаковое время.
Ниже примеры с использованием индекса:

Как итог — результат зависит от версии БД и исходных данных!

Источник