Что быстрее join или подзапрос
Присоединиться против подзапроса
Мне не хватает теоретических знаний, чтобы судить самому, есть ли разница. Подзапрос так же хорош, как и, JOIN и поэтому не о чем беспокоиться?
На практике, однако, ответ обычно сводится к производительности. Некоторые оптимизаторы сосут лимоны, когда им дают соединение против подзапроса, а некоторые оптимизаторы сосут лимоны другим способом, и это зависит от оптимизатора, от версии СУБД и от запроса.
Исторически явные объединения обычно выигрывают, поэтому установившаяся мудрость в том, что объединения лучше, но оптимизаторы все время улучшаются, и поэтому я предпочитаю сначала писать запросы логически последовательным образом, а затем реструктурировать, если этого требуют ограничения производительности.
В большинстве случаев JOIN s быстрее, чем подзапросы, и очень редко подзапрос будет быстрее.
Хорошая вещь в подзапросах состоит в том, что они более читабельны, чем JOIN s: именно поэтому большинство новых людей SQL предпочитают их; это простой способ; но когда дело доходит до производительности, JOINS лучше в большинстве случаев, хотя их тоже нетрудно прочитать.
PostgreSQL может переписать подзапрос в объединение или присоединение к подзапросу, когда он считает, что один быстрее другого. Все зависит от данных, индексов, корреляции, объема данных, запроса и т. Д.
Прежде всего, позвольте мне сказать самое важное: существуют различные формы подзапросов
И второе важное утверждение: размер имеет значение
Подзапросы в полях выбора
Подзапросы в выражении Where
Подзапросы в сообщении Join
Вложенные подзапросы
Вы можете вкладывать подзапросы на нескольких уровнях. Это может помочь с огромными наборами данных, если вам нужно сгруппировать или отсортировать результаты. Обычно DB-Server создает временную таблицу для этого, но иногда вам не нужно сортировать по всей таблице, только по набору результатов. Это может обеспечить гораздо лучшую производительность в зависимости от размера таблицы.
Вывод
Присоединиться против подзапроса
Мне не хватает теоретических знаний, чтобы самому судить, есть ли разница. Подзапрос так же хорош, как JOIN и, следовательно, не о чем беспокоиться?
19 ответов
Исторически сложилось так, что явные объединения обычно побеждают, отсюда устоявшееся мнение, что объединения лучше, но оптимизаторы постоянно совершенствуются, поэтому я предпочитаю сначала писать запросы логически согласованным образом, а затем реструктурировать, если этого требуют ограничения производительности.
В большинстве случаев запросы JOIN выполняются быстрее, чем подзапросы, и очень редко подзапросы выполняются быстрее.
В СУБД JOIN можно создать план выполнения, который лучше подходит для вашего запроса и может предсказать, какие данные должны быть загружены для обработки, и сэкономить время, в отличие от подзапроса, в котором будут выполняться все запросы и загружаться все их данные для обработки.
Преимущество подзапросов в том, что они более читабельны, чем JOIN : вот почему большинство новичков в SQL предпочитают их; это легкий путь; но когда дело доходит до производительности, JOINS в большинстве случаев лучше, хотя их тоже нетрудно читать.
PostgreSQL может переписать подзапрос в соединение или соединение с подзапросом, если считает, что один из них быстрее другого. Все зависит от данных, индексов, корреляции, количества данных, запроса и т. Д.
Прежде всего, позвольте мне сказать самое важное: Существуют разные формы подзапросов
Подзапросы в полях выбора
Подзапросы в выражении Where
Подзапросы в операторе соединения
Вложенные подзапросы
Вы можете вкладывать подзапросы на несколько уровней. Это может помочь с огромными наборами данных, если вам нужно сгруппировать или изменить результаты. Обычно DB-Server создает для этого временную таблицу, но иногда вам не нужны некоторые операции для всей таблицы, а только для набора результатов. Это может обеспечить гораздо лучшую производительность в зависимости от размера таблицы.
Вывод
Прежде всего, чтобы сравнить первые два, вы должны различать запросы с подзапросами на:
Для первого класса запросов хорошая РСУБД будет рассматривать соединения и подзапросы как эквивалентные и будут создавать те же планы запросов.
В наши дни это делает даже mysql.
Вывод состоит в том, что вам следует тестировать свои запросы как для вариантов соединения, так и для вариантов подзапроса, если вы хотите быть уверенным, какой из них будет работать лучше.
Для второго класса сравнение не имеет смысла, поскольку эти запросы нельзя переписать с помощью объединений, и в этих случаях подзапросы являются естественным способом выполнения требуемых задач, и вы не должны их дискриминировать.
Я думаю, что в приведенных ответах недооценивается проблема дубликатов и проблемных результатов, которые могут возникнуть в конкретных случаях (использования).
(хотя Марсело Кантос упоминает об этом)
Приведу пример из Стэнфордских курсов Lagunita по SQL.
Студенческий стол
Применить таблицу
(заявки подаются в определенные университеты и специальности)
Попробуем найти средний балл студентов, поступивших на специальность CS (независимо от университета).
Использование подзапроса:
Среднее значение для этого набора результатов:
Использование объединения:
Среднее значение для этого набора результатов:
Многие операторы Transact-SQL, содержащие подзапросы, могут быть альтернативно сформулированы как объединения. Остальные вопросы можно задавать только с помощью подзапросов. В Transact-SQL обычно нет разницы в производительности между оператором, который включает подзапрос, и семантически эквивалентной версией, в которой его нет. Однако в некоторых случаях, когда необходимо проверить наличие, соединение дает лучшую производительность. В противном случае вложенный запрос должен обрабатываться для каждого результата внешнего запроса, чтобы гарантировать устранение дубликатов. В таких случаях подход объединения даст лучшие результаты.
Так что если вам нужно что-то вроде
Попробуйте вместо этого использовать соединение. В остальных случаях это не имеет значения.
Я говорю: создание функций для подзапросов устраняет проблему беспорядка и позволяет реализовать дополнительную логику для подзапросов. Поэтому я рекомендую по возможности создавать функции для подзапросов.
Согласно моим наблюдениям, как в двух случаях, если в таблице меньше 100 000 записей, соединение будет работать быстро.
Но в случае, если в таблице более 100 000 записей, лучшим результатом будет подзапрос.
У меня есть одна таблица с 500000 записей, которую я создал ниже запроса, и время ее результата похоже на
Запустите очень большую базу данных из старой Mambo CMS:
EXPLAIN показывает, что они проверяют точно такое же количество строк, но одна занимает 3 секунды, а другая почти мгновенно. Мораль истории? Если производительность важна (а когда нет?), Попробуйте несколько способов и посмотрите, какой из них самый быстрый.
И снова те же результаты, такое же количество проверенных строк. Я предполагаю, что определение DISTINCT mos_content.catid занимает гораздо больше времени, чем DISTINCT mos_categories.id.
Подзапросы обычно используются для возврата одной строки как атомарного значения, хотя их можно использовать для сравнения значений с несколькими строками с ключевым словом IN. Они разрешены практически в любой значимой точке SQL-оператора, включая целевой список, предложение WHERE и так далее. В качестве условия поиска можно использовать простой подзапрос. Например, между парой таблиц:
Обратите внимание, что использование оператора обычного значения для результатов подзапроса требует, чтобы возвращалось только одно поле. Если вас интересует проверка существования одного значения в наборе других значений, используйте IN:
Это, очевидно, отличается от, скажем, LEFT-JOIN, когда вы просто хотите объединить данные из таблиц A и B, даже если условие соединения не находит подходящей записи в таблице B и т. Д.
Если вас просто беспокоит скорость, вам придется проверить свою базу данных и написать хороший запрос и посмотреть, есть ли существенная разница в производительности.
Версия MySQL: 5.5.28-0ubuntu0.12.04.2-log
Вот мой запрос с 3 подзапросами:
Тот же запрос с JOINs:
Конечно, когда я запускаю оба запроса, первый выполняется за 0,02 секунды, второй не завершается даже через 1 минуту, поэтому EXPLAIN правильно объяснил эти запросы.
Из первого запроса и соответственно:
Из второго запроса EXPLAIN возвращает одинаковое количество строк для обоих запросов, и оба эти запроса выполняются одинаково быстро.
Подзапросы имеют возможность вычислять агрегатные функции на лету. Например. Найдите минимальную цену книги и получите все книги, которые продаются по этой цене. 1) Использование подзапросов:
2) с использованием JOINs
У нас была таблица пользователей из ста тысяч записей и их данных о членстве (дружбе) около трехсот тысяч записей. Это было заявление о присоединении, чтобы забрать друзей и их данные, но с большой задержкой. Но он работал нормально там, где в таблице членства было лишь небольшое количество данных. Как только мы изменили его на использование подзапроса, он заработал нормально.
Но в то же время запросы на соединение работают с другими таблицами, в которых меньше записей, чем в первичной таблице.
Поэтому я думаю, что операторы соединения и подзапроса работают нормально, и это зависит от данных и ситуации.
В наши дни многие базы данных могут оптимизировать подзапросы и объединения. Таким образом, вы просто должны изучить свой запрос с помощью функции объяснения и посмотреть, какой из них быстрее. Если нет большой разницы в производительности, я предпочитаю использовать подзапросы, поскольку они просты и понятны.
Сначала я использую LEFT JOIN, потому что я думаю, что это правильный способ, mysql может оптимизировать и т.д. Второй раз просто для тестирования я переписываю, чтобы подвыбор против JOIN.
Время выполнения LEFT JOIN: 1,12 с. Время выполнения SUB-SELECT: 0,06 с.
Это зависит от нескольких факторов, включая конкретный запрос, который вы выполняете, количество данных в вашей базе данных. Подзапрос сначала выполняет внутренние запросы, а затем из набора результатов снова отфильтровывает фактические результаты. В то время как в join запускает и выдает результат за один раз.
Лучшая стратегия состоит в том, что вы должны протестировать как решение соединения, так и решение подзапроса, чтобы получить оптимизированное решение.
Если вы хотите ускорить свой запрос с помощью соединения:
Для «внутреннего соединения / соединения» не используйте условие where, вместо этого используйте его в условии «ON». Например:
Для «Левое / Правое соединение» не используйте в условии «ВКЛ», потому что, если вы используете левое / правое соединение, оно получит все строки для любой одной таблицы. Таким образом, нет смысла использовать его в «Вкл.». Итак, попробуйте использовать условие «Где»
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 отрабатываются примерно за одинаковое время.
Ниже примеры с использованием индекса:
Как итог — результат зависит от версии БД и исходных данных!
Оптимизация SQL-запросов
Все больше приложений используют базы данных. Все больше данных приходится хранить и обрабатывать. Если приложение медлительное, программисты, пользователи и администраторы в первую очередь ссылаются на низкую производительность сети, плохие аппаратные средства сервера и друг на друга :). И забывают про оптимизацию.
Общая оптимизация
Каждая SQL-операция имеет так называемый «коэффициент полезности» – уровень эффективности данной операции. Чем больше балл, тем «полезней» операция, а значит, SQL-запрос выполняется быстрее.
Практически любое условие состоит из двух операндов и знака операции между ними.
Примеры
Чтобы лучше понять таблицы, рассмотрим пример расчета рейтинга запроса.
… WHERE smallint_column = 12345
5 баллов за поле слева (smallint_column), 2 балла за точный цифровой операнд(smallint_column), 10 баллов за операцию сравнения (=) и 10 баллов за значение справа (12345). Итого получили 27 баллов. Теперь рассмотрим более сложный пример:
. WHERE char_column >= varchar_column || «x»
Естественно, такие расчеты не обязательно проводить для каждого запроса. Но когда встанет вопрос о скорости условий того или иного запроса, его можно будет выяснить с помощью этих двух таблиц. На скорость запроса также влияет количество выбираемых данных и дополнительные директивы, которые рассмотрим ниже. Также имей в виду, что расчет «коэффициента полезности» не является неким универсальным способом оптимизации. Все зависит от конкретной ситуации.
И эти приемы оптимизации работают практически всегда и везде.
Оптимизируем условия
Теперь настало время произвести оптимизацию самих условных операторов SQL. Большинство запросов используют директиву SQL WHERE, поэтому, оптимизируя условия, можно добиться значительной производительности запросов. При этом почему-то лишь небольшая часть приложений для БД используют оптимизацию условий.
AND
Очевидно, что в серии из нескольких операторов AND условия должны располагаться в порядке возрастания вероятности истинности данного условия. Это делается для того, чтобы при проверке условий БД не проверяла остальную часть условия. Эти рекомендации не относится к БД Oracle, где условия начинают проверяться с конца. Соответственно, их порядок должен быть обратным – по убыванию вероятности истинности.
OR
Ситуация с данным оператором прямо противоположна ситуации с AND. Условия должны располагаться в порядке убывания вероятности истинности. Фирма Microsoft настойчиво рекомендует использовать данный метод при построении запросов, хотя многие даже не знают об этом или, по крайней мере, не обращают на него внимание. Но опять-таки это не относится к БД Oracle, где условия должны располагаться по возрастанию вероятности истинности.
NOT
IN
LIKE
Эту операцию следует использовать только при крайней необходимости, потому что лучше и быстрее использовать поиск, основанный на full-text индексах. К сожалению, я вынужден направить тебя за информацией о поиске на просторы всемирной паутины.
CASE
. WHERE 1 = CASE slow_function(column1)
WHEN 3 THEN 1
WHEN 5 THEN 1
Сортировка
ORDER BY используется для сортировки, которая, как известно, занимает время. Чем больше объем данных, тем больше времени займет сортировка, поэтому нужно обязательно ее оптимизировать. На скорость сортировки в запросах влияет три фактора:
Самой ресурсоемкой сортировкой является сортировка строк. Несмотря на то, что текстовые поля имеют фиксированную длину, длина содержимого этих полей может быть различной (в пределах размера поля). Поэтому неудивительно, что сортировка колонки VARCHAR(100) будет медленней, чем сортировка колонки VARCHAR(10) (даже если данные будут одинаковые). А происходит это из-за того, что при сортировке сама база данных выделяет память для своих операций в соответствии с максимальным размером поля независимо от содержимого. Поэтому при объявлении полей всегда следует использовать размер, который нужен, и не выделять лишние байты про запас.
На компьютерах с ОС Windows поля типа INTEGER занимают 32 бита, а поля типа SMALLINT – 16 бит. Логично предположить, что сортировка полей типа SMALLINT должна происходить быстрее. На самом деле сортировка INTEGER происходит быстрее, чем SMALLINT. Также сортировка INTEGER происходит быстрее, чем CHAR.
Сортировка символов также имеет свои нюансы, описание которых займет не одну статью. Она может быть быстрой и неправильной или медленной, но с меньшим количеством ошибок. Оптимизации сортировки производится для конкретной ситуации, так что универсальных рекомендаций никто дать не может.
Группирование
Операция GROUP BY используется для определения подмножества в результате запроса, а также для применения к этому подмножеству агрегатных функций. Рассмотрим несколько наиболее эффективных методов оптимизации операции группирования.
В большинстве БД операции WHERE и HAVING не равноценны и выполняются не одинаково. Это значит, что следующие два запроса логически одинаковы, но выполняются с разной скоростью:
SELECT column1 FROM Table1 WHERE column2 = 5 GROUP BY column1 HAVING column1 > 6
SELECT column1 FROM Table1 WHERE column2 = 5 AND column1 > 6 GROUP BY column1
Второй запрос работает быстрее, чем первый. HAVING следует использовать в тех редких случаях, когда условие (в примере column1 > 6) сложно выразить без ущерба производительности.
При использовании MIN() и MAX() учитываем, что эти функции лучше работают по отдельности. Это значит, что их лучше использовать в раздельных запросах или в запросах с использованием UNION.
Соединения таблиц (JOINS)
Подзапросы (SUBQUERIES)
Ниже аргументы в пользу того или иного способа. Выбирай сам в зависимости от ситуации.
Основное преимущество JOIN’ов в том, что не надо указывать БД то, каким именно способом производить операцию. А основное преимущество подзапросов в том, что цикл подзапроса может иметь несколько итераций (повторений), что, в свою очередь, может существенно увеличить производительность.
Заключение
В этой статье показаны самые распространенные способы увеличения производительности SQL-запросов. Тем не менее, чтобы оптимизировать запросы, есть еще очень много разных уловок и трюков. Оптимизация запросов больше похожа на искусство, чем на науку. У каждой базы данных свои встроенные оптимизаторы, которые могут помочь в этом нелегком деле, но всю работу за тебя никто не сделает. Как говорил старенький преподаватель по физике: «Чтобы решать задачи, их нужно решать».
Не рекомендуется использовать ORDER BY в связке с такими операциями, как DISTINCT или GROUP B Y, потому что данные операторы могут создавать побочные эффекты для сортировки. Как следствие, ты можешь получить неправильно отсортированный набор данных, который может оказаться критическим в некоторых ситуациях. Такое следствие не относится к оптимизации, но забывать о нем не стоит.
Прежде чем повышать производительность сети и наращивать аппаратные средства сервера, попробуй сделать оптимизацию.
У любой SQL-операции есть «коэффициент полезности». Чем выше коэффициент, тем «полезней» операция: запрос выполняется быстрее.
В отличие от компиляторов, не все БД умеют упрощать выражения типа x=1+1-1-1 до x=0. Следовательно, они тратят драгоценное время на выполнение пустых операций. Оптимизируй их заранее.
Но если функции SUM() требуются для вычитания, используй противоположное: SUM(x) – SUM(y). SUM(x – y) работает медленнее.
У каждой БД есть свои встроенные оптимизаторы, но они далеки от совершенства. Поэтому оптимизируй заранее.