Что быстрее join или left join
Форум пользователей MySQL
Задавайте вопросы, мы ответим
Страниц: 1
#1 26.12.2016 16:03:42
Быстродейвие JOIN и LEFT JOIN
До недавнего времени был искренне убежден, что запросы вида
Отредактированно klow (26.12.2016 16:04:17)
#2 26.12.2016 16:30:05
Re: Быстродейвие JOIN и LEFT JOIN
у вас было правильное убеждение
опыт, указывает на то, что где-то грабли зарыты, например, в запросах отличается список выбираемых полей, в результате первый отдает сотни мегабайт, а второй десятки килобайт.
в общем нужно копать частный случай, чтобы понять на какие грабли наткнулись.
в целом так быть не должно
#3 26.12.2016 16:35:07
Re: Быстродейвие JOIN и LEFT JOIN
Меняю только LEFT и больше ничего не трогаю!
Запросы не привожу, так как они достаточно сложные и основаны на многих таблицах.
Отредактированно klow (26.12.2016 20:49:44)
#4 26.12.2016 17:42:52
Re: Быстродейвие JOIN и LEFT JOIN
скорее всего оптимизатор изменил порядок соединения таблиц и в результате выбрал гораздо худший план.
#5 26.12.2016 18:37:08
Re: Быстродейвие JOIN и LEFT JOIN
Да, глянуть бы на explain обоих запросов. В частности, если подсунуть в join-запрос STRAIGHT_JOIN.
#6 26.12.2016 20:47:46
Re: Быстродейвие JOIN и LEFT JOIN
STRAIGHT_JOIN выполняется за 9 секунд, еще одна странность.
Планы запросов во вложении
Отредактированно klow (26.12.2016 20:59:31)
#7 26.12.2016 20:57:41
Re: Быстродейвие JOIN и LEFT JOIN
#8 27.12.2016 02:38:22
Re: Быстродейвие JOIN и LEFT JOIN
klow написал:
STRAIGHT_JOIN выполняется за 9 секунд, еще одна странность.
STRAIGHT_JOIN похож на left, но все отличается, нужно больше подсказок оптимизатору (например, тех же STRAIGHT_JOIN)
я понимаю почему вы не стали выкладывать сами запросы 
это вы такое руками пишите или они всё-таки генерятся?
ещё можно посмотреть на профайлинг, может там время расходуется на составление самого плана.
#9 27.12.2016 09:59:50
Re: Быстродейвие JOIN и LEFT JOIN
Пишу руками, но нужно учитывать, что 2 самых сложных запроса оформлены к качестве View и используются несколько раз в самом запросе. Иначе запрос перерос бы в невообразимого монстра.
Одна вьюха нужна для таблицы, которая ссылается сама на себя и там использую UNION до 8 раз. К сожалению MySql нет инструментов для работы с рекурсивными запросами. 
Во второй вью идет предварительная выборка нужных ID и дат, а после на ней-же использую конструкцию
для выбора последней даты.
Поэтому такой сложный запрос.
Сейчас думаю, что неправильно сделал дав план всего запроса, нужно было бы только основные таблицы, вспомогательные не существенно влияют на запрос.
Поэтому наново привожу планы и профайлинг запроса удалив вспомогательные таблицы.
Странно, но разница времени выполнения между LEFT JOIN и JOIN уменьшилась, составила 5 и 15 сек. соответственно. Объяснить это я не могу.
Отредактированно klow (27.12.2016 10:29:53)
#10 27.12.2016 12:29:10
Re: Быстродейвие JOIN и LEFT JOIN
#11 27.12.2016 12:39:15
Re: Быстродейвие JOIN и LEFT JOIN
То есть утверждение, что JOIN быстрее LEFT JOIN в общем случае не корректно. Все зависит от оптимизатора. Ок.
Но почему если «детей» меньше или нет вовсе время выполнения тоже увеличивается? Или оптимизатор и тут тоже строит разные планы запроса и, возможно, ошибается?
Отредактированно klow (27.12.2016 12:39:46)
#12 27.12.2016 13:10:33
Re: Быстродейвие JOIN и LEFT JOIN
klow написал:
Но почему если «детей» меньше или нет вовсе время выполнения тоже увеличивается? Или оптимизатор и тут тоже строит разные планы запроса и, возможно, ошибается?
Это в общем случае звучит странно, а в частном случае может произойти по ряду причин. Может быть оптимизатор строит план исполнения в другом порядке, предпочитает полный скан или еще что-то.
#13 27.12.2016 13:17:04
Re: Быстродейвие JOIN и LEFT JOIN
klow написал:
К сожалению MySql нет инструментов для работы с рекурсивными запросами.
Будут в MySQL 8 и MariaDB 10.2, а пока есть возможность имитировать обобщенные табличные выражения с помощью хранимой процедуры.
Посмотрите, может окажется полезным:
WITH RECURSIVE и MySQL
#14 27.12.2016 13:49:29
Re: Быстродейвие JOIN и LEFT JOIN
Как мне не нравится Oracle, но его операторы CONNECT BY и PRIOR мне бы очень упростили жизнь.
Статью читал, но не смог ее применить для своего случая. Например как возвратить в SELECT результаты процедуры, когда возвращается много строк? Как в View сделать ссылку на саму себя?
Когда еще будут MySQL 8 и MariaDB 10.2, а работать нужно уже сегодня (вчера). Тем более, насколько я понял, PRIOR даже не планируется в MySql (MariaDB).
#15 27.12.2016 21:45:28
Re: Быстродейвие JOIN и LEFT JOIN
klow написал:
Поэтому наново привожу планы и профайлинг запроса удалив вспомогательные таблицы.
1. Вы проверяли, что временные таблицы создаются в памяти, а не на диске?
2. На таких сложных запросах оптимизатор может легко ошибиться. Экспериментально, вы знаете более выгодный план и в случае JOIN нужно с помощью подсказок добиться такого же плана.
#16 28.12.2016 01:37:44
Re: Быстродейвие JOIN и LEFT JOIN
Исправил сообщение #10, а то в нем была путаница. Все дело только в плане запроса, иначе JOIN должен быть быстрее, чем LEFT JOIN.
#17 28.12.2016 09:22:51
Re: Быстродейвие JOIN и LEFT JOIN
vasya написал:
1. Вы проверяли, что временные таблицы создаются в памяти, а не на диске?
Не проверял. Подскажите, пожалуйста, как это сделать?
#18 28.12.2016 10:42:56
Re: Быстродейвие JOIN и LEFT JOIN
значение Created_tmp_disk_tables указывает на число временных таблиц, созданных на диске. Это происходит, когда размер таблицы превышает минимальную из переменных (tmp_table_size, max_heap_table_size). Емнип, text/blob тоже приводят к такому результату.
#19 28.12.2016 16:23:13
Re: Быстродейвие JOIN и LEFT JOIN
Спасибо!
До
Created_tmp_disk_tables 0
Created_tmp_files 676
Created_tmp_tables 126
После
Created_tmp_disk_tables 0
Created_tmp_files 676
Created_tmp_tables 137
Я так понял, что на диск временные таблицы не скидываются.
#20 28.12.2016 17:21:46
Re: Быстродейвие JOIN и LEFT JOIN
#21 28.12.2016 18:00:06
Re: Быстродейвие JOIN и LEFT JOIN
Спасибо! Еще раз проведу анализ с учетом предложений.
#22 28.12.2016 18:21:29
Re: Быстродейвие JOIN и LEFT JOIN
#23 28.12.2016 20:51:25
Re: Быстродейвие JOIN и LEFT JOIN
Спасибо! Почитаю. Хотя уже много на эту тему читал статей.
Но рекурсия это смежная тема. В основном меня удивило, что left join выполняется быстрее обычного join.
8 уровней это сейчас, а вообще это переменная величина и зависит только от пользователей.
Для меня сейчас более важно получить смежные строки. Вот проблема. На это в основном (90-97%) тратятся ресурсы, но, к сожалению, более приемлемого метода, чем описал выше, не нашел.
Что быстрее join или left join
Но у вас и результат должен быть разный
Так проще понять и связи и остальное
Потому что первый вообще какой-то «левый»
INNER JOIN table2 AS t2 ON t1.object_id = t2.id AND t1.object_group = ‘com_group’
ЗАчем? если t1.object_group не в видимости t2
LEOnidUKG, Но это никак не объясняет разницы в быстродействии
Дайте угадаю, таблица table2 у вас маленькая?
INNER JOIN обычно быстрее LEFT JOIN, у вас либо не хватает индексов либо хз что. То что вам тут дали картинку, это хорошо, но это не объясняет такого поведения. По картинке, кстати, ясно, что INNER JOIN абсолютно не эквивалент LEFT JOIN.
P.S. Пока писал, появился пост выше, почти такой-же.
остальные мизер. На всех полях что участвуют в объединении есть индексы. Результат запроса одинаков абсолютли
Загрузите на SQL Fiddle схемы, можно будет подумать как оптимизировать.
Я привёл информацию, что это разные инструменты и результаты будут разные. Просто так заменить слова и радоваться не получится.
Я вообще стараюсь избегать таких запросов, лучше несколько простых и перебор, чем вот такие конструкции и потом ищи гадай, что и зачем, почему это сжирает память и ещё по 4-6 секунд выполняется.
тут можно экспериментировать сколько угодно
Например меняем порядок таблиц
LEOnidUKG:
Я привёл информацию, что это разные инструменты и результаты будут разные. Просто так заменить слова и радоваться не получится.
Я вообще стараюсь избегать таких запросов, лучше несколько простых и перебор, чем вот такие конструкции и потом ищи гадай, что и зачем, почему это сжирает память и ещё по 4-6 секунд выполняется.
Та нормальный запрос, То вы, наверное не видели джойнов по 10 таблиц, и вложенных запросов.
Понимание джойнов сломано. Это точно не пересечение кругов, честно
Так получилось, что я провожу довольно много собеседований на должность веб-программиста. Один из обязательных вопросов, который я задаю — это чем отличается INNER JOIN от LEFT JOIN.
Чаще всего ответ примерно такой: «inner join — это как бы пересечение множеств, т.е. остается только то, что есть в обеих таблицах, а left join — это когда левая таблица остается без изменений, а от правой добавляется пересечение множеств. Для всех остальных строк добавляется null». Еще, бывает, рисуют пересекающиеся круги.
Я так устал от этих ответов с пересечениями множеств и кругов, что даже перестал поправлять людей.
Дело в том, что этот ответ в общем случае неверен. Ну или, как минимум, не точен.
Давайте рассмотрим почему, и заодно затронем еще парочку тонкостей join-ов.
Во-первых, таблица — это вообще не множество. По математическому определению, во множестве все элементы уникальны, не повторяются, а в таблицах в общем случае это вообще-то не так. Вторая беда, что термин «пересечение» только путает.
(Update. В комментах идут жаркие споры о теории множеств и уникальности. Очень интересно, много нового узнал, спасибо)
INNER JOIN
Давайте сразу пример.
Итак, создадим две одинаковых таблицы с одной колонкой id, в каждой из этих таблиц пусть будет по две строки со значением 1 и еще что-нибудь.
Давайте, их, что ли, поджойним
Если бы это было «пересечение множеств», или хотя бы «пересечение таблиц», то мы бы увидели две строки с единицами.
На практике ответ будет такой:
Для начала рассмотрим, что такое CROSS JOIN. Вдруг кто-то не в курсе.
CROSS JOIN — это просто все возможные комбинации соединения строк двух таблиц. Например, есть две таблицы, в одной из них 3 строки, в другой — 2:
Тогда CROSS JOIN будет порождать 6 строк.
Так вот, вернемся к нашим баранам.
Конструкция
— это, можно сказать, всего лишь синтаксический сахар к
Небольшой disclaimer: хотя inner join логически эквивалентен cross join с фильтром, это не значит, что база будет делать именно так, в тупую: генерить все комбинации и фильтровать. На самом деле там более интересные алгоритмы.
LEFT JOIN
Если вы считаете, что левая таблица всегда остается неизменной, а к ней присоединяется или значение из правой таблицы или null, то это в общем случае не так, а именно в случае когда есть повторы данных.
Опять же, создадим две таблицы:
Теперь сделаем LEFT JOIN:
Результат будет содержать 5 строк, а не по количеству строк в левой таблице, как думают очень многие.
Так что, LEFT JOIN — это тоже самое что и INNER JOIN (т.е. все комбинации соединений строк, отфильтрованных по какому-то условию), и плюс еще записи из левой таблицы, для которых в правой по этому фильтру ничего не совпало.
LEFT JOIN можно переформулировать так:
Сложноватое объяснение, но что поделать, зато оно правдивее, чем круги с пересечениями и т.д.
Условие ON
Удивительно, но по моим ощущениям 99% разработчиков считают, что в условии ON должен быть id из одной таблицы и id из второй. На самом деле там любое булево выражение.
Например, есть таблица со статистикой юзеров users_stats, и таблица с ip адресами городов.
Тогда к статистике можно прибавить город
где && — оператор пересечения (см. расширение посгреса ip4r)
Если в условии ON поставить true, то это будет полный аналог CROSS JOIN
Производительность
Есть люди, которые боятся join-ов как огня. Потому что «они тормозят». Знаю таких, где есть полный запрет join-ов по проекту. Т.е. люди скачивают две-три таблицы себе в код и джойнят вручную в каком-нибудь php.
Это, прямо скажем, странно.
Если джойнов немного, и правильно сделаны индексы, то всё будет работать быстро. Проблемы будут возникать скорее всего лишь тогда, когда у вас таблиц будет с десяток в одном запросе. Дело в том, что планировщику нужно определить, в какой последовательности осуществлять джойны, как выгоднее это сделать.
Сложность этой задачи O(n!), где n — количество объединяемых таблиц. Поэтому для большого количества таблиц, потратив некоторое время на поиски оптимальной последовательности, планировщик прекращает эти поиски и делает такой план, какой успел придумать. В этом случае иногда бывает выгодно вынести часть запроса в подзапрос CTE; например, если вы точно знаете, что, поджойнив две таблицы, мы получим очень мало записей, и остальные джойны будут стоить копейки.
Кстати, Еще маленький совет по производительности. Если нужно просто найти элементы в таблице, которых нет в другой таблице, то лучше использовать не ‘LEFT JOIN… WHERE… IS NULL’, а конструкцию EXISTS. Это и читабельнее, и быстрее.
Выводы
Как мне кажется, не стоит использовать диаграммы Венна для объяснения джойнов. Также, похоже, нужно избегать термина «пересечение».
Как объяснить на картинке джойны корректно, я, честно говоря, не представляю. Если вы знаете — расскажите, плиз, и киньте в коменты. А мы обсудим это в одном из ближайших выпусков подкаста «Цинковый прод». Не забудьте подписаться.
Осмысляем работу джойнов в SQL: от реляционной алгебры до наглядных картинок
Выбираем, какие фильмы посмотреть, с помощью соединения данных в SQL.
скриншот из игры team fortress 2 / valve
Опять эта проблема — выбрать кино на вечер. Благодаря стриминговым сервисам доступны едва ли не все фильмы мира: это бесконечное полотно с постерами и фильтры, фильтры, фильтры…
МОЗГ: Поставлю-ка я фильтр по стране: пусть будет Дания, и добавлю ограничение по жанру — триллер… Ну вот — другое дело, относительно небольшой список.
— Мозг, а знаешь почему? Да потому что здесь только фильмы, которые сняты в Дании И помечены как триллеры.
— Да не знаю я, как задать такие критерии в этом сервисе. Вот если бы можно было писать на SQL — тут бы решение нашлось для любой комбинации признаков.
— Легко! Ещё и картинки будут. У меня и база фильмов уже спарсена — тренируйся не хочу.
Фулстек-разработчик. Любимый стек: Java + Angular, но в хорошей компании готова писать хоть на языке Ада.
Договоримся об обозначениях
Назовём множество датских фильмов — D, а множество триллеров — T. У каждого фильма будет уникальный номер, он же ключ. Раз ключ — пусть зовётся Key.
Заодно вспомним, как на SQL пишется простой запрос для связывания данных из двух таблиц:
INNER JOIN
Если не уточнить тип соединения ( JOIN), то по умолчанию применяется INNER JOIN — как раз тот вариант, который сработал в нашем кинофильтре. Это он выбирает и триллеры, и датские фильмы одновременно.
Почему много JOIN в запросе это плохо или не мешайте оптимизатору
Недавно столкнулся с одним приложением, которое генерировало запросы к БД. Я понимаю, что этим никого не удивишь, но когда приложение стало тормозить и мне пришло задание разобраться в чём причина, я был сильно удивлён, обнаружив эти запросы. Вот с чем иногда приходится иметь дело SQL Server:
Название объектов было изменено.
Больше всего бросается в глаза то, что одна и та же таблица используется множество раз, а количество скобок сводит с ума. Но не только мне не понравился такой код, SQL Server то же не в восторге и тратит много ресурсов на создание плана для него. Запрос может выполняться от 50 до 150 мс, а построение плана может занимать до 2,5 секунд. Сегодня я не буду рассматривать варианты исправления ситуации, скажу только что в моём случае исправить генерацию запроса в приложении было невозможно.
Я бы хотел рассмотреть почему же SQL Server так долго строит план запроса. В любой СУБД, SQL Server не исключение, главным вопросом оптимизации является способ соединения таблиц друг с другом. Кроме непосредственно самого способа соединения, очень важен порядок соединения таблиц.
Давайте поговорим о порядке соединения таблиц подробнее. В данном вопросе очень важно понять — возможное количество соединений таблиц растёт по экспоненте, а не линейно. Например, для 2-х таблиц число возможных вариантов соединения всего 2, для 3-х это число может доходить до 12-и. Разный порядок соединения, может иметь разную стоимость запроса и оптимизатор SQL Server должен выбрать «оптимальный» способ, но при большем количестве таблиц, это становится ресурсоёмкой задачей. В случае если SQL Server начнёт перебирать все возможные варианты, то такой запрос может никогда не выполнится, по этой причине SQL Server этого никогда не делает и всегда ищет «достаточно хороший план», а не «наилучший». SQL Server всегда пытается найти компромисс между временем выполнения и качеством плана.
Вот наглядный пример роста количества вариантов соединения по экспоненте. SQL Server может выбирать разные способы соединения (left-deep, right-deep, bushy trees).
Визуально это выглядит следующим образом:
Таблица показывает возможное количество вариантов соединения при увеличении количество таблиц:
Вы можете самостоятельно получить эти значения:
Для left-deep: 5! = 5 x 4 x 3 x 2 x 1 = 120
Вывод: Относитесь более аккуратно к количеству JOIN в запрос и не мешайте оптимизатору. Если у вас не получается добиться нужного результата в запросе, где много JOIN, разбейте его на несколько, более мелких запросов и вы удивитесь на сколько лучше может получиться результат.