Что быстрее вложенный запрос или join
Присоединиться против подзапроса
Мне не хватает теоретических знаний, чтобы судить самому, есть ли разница. Подзапрос так же хорош, как и, JOIN и поэтому не о чем беспокоиться?
На практике, однако, ответ обычно сводится к производительности. Некоторые оптимизаторы сосут лимоны, когда им дают соединение против подзапроса, а некоторые оптимизаторы сосут лимоны другим способом, и это зависит от оптимизатора, от версии СУБД и от запроса.
Исторически явные объединения обычно выигрывают, поэтому установившаяся мудрость в том, что объединения лучше, но оптимизаторы все время улучшаются, и поэтому я предпочитаю сначала писать запросы логически последовательным образом, а затем реструктурировать, если этого требуют ограничения производительности.
В большинстве случаев JOIN s быстрее, чем подзапросы, и очень редко подзапрос будет быстрее.
Хорошая вещь в подзапросах состоит в том, что они более читабельны, чем JOIN s: именно поэтому большинство новых людей SQL предпочитают их; это простой способ; но когда дело доходит до производительности, JOINS лучше в большинстве случаев, хотя их тоже нетрудно прочитать.
PostgreSQL может переписать подзапрос в объединение или присоединение к подзапросу, когда он считает, что один быстрее другого. Все зависит от данных, индексов, корреляции, объема данных, запроса и т. Д.
Прежде всего, позвольте мне сказать самое важное: существуют различные формы подзапросов
И второе важное утверждение: размер имеет значение
Подзапросы в полях выбора
Подзапросы в выражении Where
Подзапросы в сообщении Join
Вложенные подзапросы
Вы можете вкладывать подзапросы на нескольких уровнях. Это может помочь с огромными наборами данных, если вам нужно сгруппировать или отсортировать результаты. Обычно DB-Server создает временную таблицу для этого, но иногда вам не нужно сортировать по всей таблице, только по набору результатов. Это может обеспечить гораздо лучшую производительность в зависимости от размера таблицы.
Вывод
MySQL Что правильнее/быстрее: подзапрос в основном запросе или связка всех таблиц с группировкой?
Создал 2 разных запроса к базе MySQL. Оба запроса выводят одинаковый результат, но работают по разному. Хочется понять, какой из запросов более правильный/быстрый. Буду рад любым комментариям.
Запрос 1:
Запрос 2:
Я не силен в синтаксисе оператора EXPLAIN, но второй запрос мне показался более медленным из-за создания временной таблицы для хранения результата (Using temporary).
1 ответ 1
Если судить по Explain, то первый вариант будет оптимальнее. Второй вариант приводит еще и к созданию промежуточных таблиц, что замедлит работу.
Но в общем плане оба запроса не оптимальны, т.к. таблица Shop_orders перебирается полностью без всяких индексов.
Плюс даже без индексов Вы можете оптимизировать запросы, т.к. сейчас они слишком усложнены.
Запрос 1: можно легко обойтись без having (его лучше вообще избегать):
Запрос 2: его надо перевести на нормальный join и все:
Во втором запросе стоит убрать GROUP BY и заменить его на distinct, т.к. индекс по ID, даже если он есть, в данном случае не будет никогда использоваться, и лишняя группировка может привести к доп. ненужной нагрузке, если Вам подсчитывать реально ничего не надо.
Теоретически второй вариант должен быть предпочтительнее, но, как я сказал ранее, для начала надо разобраться с индексами и подумать, как их можно улучшить.
Оптимизация 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) работает медленнее.
У каждой БД есть свои встроенные оптимизаторы, но они далеки от совершенства. Поэтому оптимизируй заранее.
JOIN запросы против нескольких запросов
Являются ли запросы JOIN быстрее, чем несколько запросов? (Вы запускаете свой основной запрос, а затем запускаете множество других SELECT на основе результатов вашего основного запроса)
Я спрашиваю, потому что присоединение к ним усложнит ОЧЕНЬ дизайн моего приложения
Если они быстрее, может ли кто-нибудь приблизительно приблизиться к тому, насколько? Если это в 1,5 раза, мне все равно, но если это в 10 раз, я думаю, что да.
Один запрос с 5 объединениями
запрос: 8,074508 секунд
размер результата: 2268000
5 запросов подряд
время комбинированного запроса: 0,00262 секунды
размер результата: 165 (6 + 50 + 7 + 12 + 90)
Обратите внимание, что мы получаем одинаковые результаты в обоих случаях (6 х 50 х 7 х 12 х 90 = 2268000)
левые соединения используют экспоненциально больше памяти с избыточными данными.
Ограничение памяти может быть не таким плохим, если вы объединяете только две таблицы, но, как правило, три или более, и это становится полезным для разных запросов.
Этот вопрос старый, но в нем отсутствуют некоторые критерии. Я сравнил JOIN с его 2 конкурентами:
Результат ясен: на MySQL JOIN все намного быстрее. N + 1 запросы могут резко снизить производительность приложения:
То есть, если вы не выберете много записей, которые указывают на очень небольшое количество отдельных, иностранных записей. Вот эталон для крайнего случая:
Это вряд ли произойдет в типичном приложении, если только вы не присоединяетесь к отношению-ко-многим, в этом случае внешний ключ находится в другой таблице, и вы дублируете данные основной таблицы много раз.
Смотрите мою статью на Medium для получения дополнительной информации.
Итог, как я лично это вижу: если он работает хорошо, выбирайте простое решение.
Провел быстрый тест, выбрав одну строку из таблицы строк 50000 и соединившись с одной строкой из таблицы строк 100000. В основном выглядело так:
Метод «два выбора» занял 3,7 секунды для 50 000 операций чтения, тогда как на моем медленном домашнем компьютере JOIN занял 2,0 секунды. INNER JOIN и LEFT JOIN ничего не изменили. Выборка нескольких строк (например, с использованием IN SET) дала аналогичные результаты.
Ответ TLDR:
Если один на один, используйте JOIN утверждение.
Если один ко многим, используйте один (или много) SELECT операторов с оптимизацией кода на стороне сервера.
Почему и как использовать SELECT для оптимизации
SELECT Использование (с несколькими запросами вместо объединений) для большой группы записей на основе отношения «один ко многим» обеспечивает оптимальную эффективность, поскольку в случае с JOIN проблемой экспоненциальной утечки памяти. Соберите все данные, а затем используйте язык сценариев на стороне сервера, чтобы разобраться в них:
Когда не использовать JOIN для оптимизации
JOIN большая группа записей, основанная на взаимно-однозначных отношениях с одной записью, обеспечивает оптимальную эффективность по сравнению с множеством SELECT операторов один за другим, которые просто получают следующий тип записи.
Но JOIN неэффективно при получении записей с отношением один ко многим.
Пример: Блоги базы данных имеют 3 таблицы интереса: Blogpost, Tag и Comment.
Если есть 1 запись блога, 2 тега и 2 комментария, вы получите следующие результаты:
Добавьте больше таблиц, больше записей и т. Д., И проблема быстро раздуется до сотен строк, которые заполнены в основном избыточными данными.
Сколько стоят эти дубликаты? Память (в SQL-сервере и коде, который пытается удалить дубликаты) и сетевые ресурсы (между SQL-сервером и вашим сервером кода).
В зависимости от сложности базы данных по сравнению со сложностью разработчика, может быть проще выполнять много вызовов SELECT.
Попробуйте запустить некоторую статистику базы данных как для JOIN, так и для нескольких SELECTS. Посмотрите, если в вашей среде JOIN быстрее / медленнее, чем SELECT.
Опять же, если изменение его на JOIN будет означать дополнительный день / неделю / месяц работы разработчика, я бы придерживался нескольких SELECT
По своему опыту я обнаружил, что обычно несколько запросов выполняется быстрее, особенно при получении больших наборов данных.
При взаимодействии с базой данных из другого приложения, такого как PHP, существует аргумент одной поездки на сервер из-за многих.
Я не новичок в том, что касается SQL, я думаю, что разработчики, особенно юниоры, склонны тратить много времени, пытаясь написать очень умные объединения, потому что они выглядят умными, тогда как на самом деле есть умные способы извлечения данных, которые выглядят просто.
Последний абзац был личным мнением, но я надеюсь, что это поможет. Я согласен с другими, хотя, кто говорит, что вы должны ориентироваться. Ни один из подходов не является серебряной пулей.
Различия в производительности во многом будут зависеть от того, насколько связана информация, к которой вы обращаетесь. Объединения работают и работают быстро, когда данные связаны, и вы правильно индексируете данные, но они часто приводят к некоторой избыточности, а иногда и к большему количеству результатов, чем необходимо. И если ваши наборы данных не связаны напрямую, их привязка к одному запросу приведет к тому, что называется декартовым произведением (в основном, всеми возможными комбинациями строк), что почти никогда не является тем, что вам нужно.
Давайте рассмотрим другой сценарий: вы хотите, чтобы комментарии, прикрепленные к сообщению, и контактная информация комментаторов.
Это где вы должны рассмотреть вопрос о присоединении. Помимо гораздо более естественного запроса, большинство систем баз данных (включая MySQL) имеют много умных людей, которые так же много работают над оптимизацией запросов. Для отдельных запросов, поскольку каждый запрос зависит от результатов предыдущего, запросы не могут выполняться параллельно, и общее время становится не только фактическим временем выполнения запросов, но и временем, потраченным на выборку результатов, просеивание через них для идентификаторов для следующего запроса, связывания строк и т. д.
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 отрабатываются примерно за одинаковое время.
Ниже примеры с использованием индекса:
Как итог — результат зависит от версии БД и исходных данных!