Эквисоединения с использованием where и join

Явные операции соединения стр. 1

Соединение может быть либо внутренним ( INNER ), либо одним из внешних ( OUTER ). Служебные слова INNER и OUTER можно опускать, поскольку внешнее соединение однозначно определяется его типом — LEFT (левое), RIGHT (правое) или FULL (полное), а просто JOIN будет означать внутреннее соединение.

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

computers

В данном примере в результирующем наборе будут соединяться только те строки из таблиц РС и Product, у которых совпадают номера моделей.

Для визуального контроля в результирующий набор включен как номер модели из таблицы PC, так и из таблицы Product:

Maker model_1 model_2 price
A 1232 1232 600
A 1232 1232 400
A 1232 1232 350
A 1232 1232 350
A 1233 1233 600
A 1233 1233 950
A 1233 1233 980
A 1233 1233 970
B 1121 1121 850
B 1121 1121 850
B 1121 1121 850
E 1260 1260 350

Источник

Явные операции соединения стр. 2

Привести все модели ПК, их производителей и цену:

computers

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

Maker model_1 model_2 price
A 1232 1232 600
A 1232 1232 400
A 1232 1232 350
A 1232 1232 350
A 1233 1233 600
A 1233 1233 950
A 1233 1233 980
B 1121 1121 850
B 1121 1121 850
B 1121 1121 850
E 2111 NULL NULL
E 2112 NULL NULL
E 1260 1260 350

Источник

MySQL и JOINы

Поводом для написания данной статьи послужили некоторые дебаты в одной из групп linkedin, связанной с MySQL, а также общение с коллегами и хабролюдьми 🙂

В данной статье хотел написать что такое вообще JOINы в MySQL и как можно оптимизировать запросы с ними.

Что такое JOINы в MySQL

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

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

Прмер обычного запроса с INNER JOIN

где Р — условия склейки таблиц и фильтры в WHERE условии.

Можно представить такой псевдокод выполнения такого запроса.

где конструкция t1||t2||t3 означает конкатенацию столбцов из разных таблиц.

Если в запросе встречаются OUTER JOINs, например, LEFT OUTER JOIN

то алгоритм выполнения этого запроса MySQL будет выглядеть как-то так

Итак, как мы видим, JOINы это просто группа вложенных циклов. Так почему же в MySQL и UNION и SELECT и запросы с SUBQUERY тоже джоины?

MySQL оптимизатор старается приводить запросы к тому виду к которому ему удобней обрабатывать и выполнять запросы по стандартной схеме.

С SELECT все понятно — просто цикл без вложенных циклов. Все UNION выполняются как отдельные запросы и результаты складываются во временную таблицу, и потом MySQL работает уже с этой таблицей, т.е. проходясь циклом по записям в ней. С Subquery та же история.

Приводя все к одному шаблону, например, МySQL переписывает все RIGHT JOIN запросы на LEFT JOIN эквиваленты.

Но стратегия выполнения запросов через вложенные циклы накладывает некоторые ограничения, например, в связи с такой схемой MySQL не поддерживает выполнение FULL OUTER JOIN запросов.

Но результат такого запроса можно получить с помощью UNION двух запросов на LEFT JOIN и на RIGHT JOIN
Пример самого запроса можно посмотреть по ссылке на вики.

План выполнения JOIN запросов

В отличии от других СУРБД MySQL не генерирует байткод для выполнения запроса, вместо этого MySQL генерирует список инструкций в древовидной форме, которых придерживается engine выполнения запроса выполняя запрос.
Это дерево имеет следующий вид и имеет название «left-deep tree»
2799c2e387dc87a15462aa2ffd2936a2

В отличии от сбалансированных деревьев (Bushy plan), которые применяются в других СУБД (например Oracle)

847d8e5dcbbbbfa4cf581ba817397f70

JOIN оптимизация

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

Для выбранного плана можно узнать стоимость путем выполнения команды

SHOW SESSION STATUS LIKE ‘Last_query_cost’;

после выполнения интересующего нас запроса. Переменная Last_query_cost является сессионной переменной. Описание переменной Last_query_cost в MySQL документации можно найти здесь — dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#option_mysqld_Last_query_cost

Оценка основана на статистике: количество страниц памяти, занимаемое таблицей и/или индексами для этой таблицы, cardinality (число уникальных значений) индексов, длинна записей и индексов, их распределение и т.д. Во время своей оценки оптимизатор не рассчитывает на то, что какие-то части попадут в кеш, оптимизатор предполагает, что каждая операция чтения это обращение к диску.

Иногда анализатор-оптимизатор не может проанализировать все возможные планы выполнения и выбирает неправильный. Например, если у нас INNER JOIN по 3м таблицам, то возможных вариантов у анализатора — 3! = 6, а если у нас склейка по 10 таблицам, то тут возможных вариантов уже 10! = 3628800… MySQL не может проанализировать столько вариантов, поэтому в таком случае он использует алгоритм «жадного» поиска.

И вот как раз для решения данной проблемы, нам может пригодиться конструкция STRAIGHT_JOIN. На самом деле я противник подобных хаков как FORCE INDEX и STRAIGH_JOIN, точней против их бездумного использования везде где только можно и нельзя. В данном случае — можно 🙂 Выяснив (либо экспериментальным путем делая запросы с STRAIGH_JOIN и оценивая Last_query_cost, либо эмпирическим путем) нужный порядок джоинов можно переписать запрос с таблицами в соответствующем порядке и добавить STRAIGH_JOIN к данному запросу, таким образом мы сразу убьем двух зайцев — определим правильный план выполнения запроса (это главный заяц) и сэкономим время на стадии «Statistic» (Все стадии выполнения запроса можно посмотреть установив профайлинг запросов командой SET PROFILING =1, я описывал это в своей предыдущей статье по теме профайлинга запросов в MySQL )

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

Также, как уже говорилось выше, результаты джоинов помещаются во временные таблицы, поэтому зачастую уместно применять «derived table» в котором мы накладываем все необходимые нам условия на выборку, а также указываем LIMIT и порядок сортировки. В данном случае мы избавимся от избыточности данных во временной таблице, а также проведем сортировку на раннем этапе (по результату одной выборки, а не финальной склейки, что уменьшит размеры записей которые будут сортироваться).

Стандартный пример подхода описанного выше. Простая выборка для отношения много к многим: новости и теги к ним.

Ну и на последок небольшая задачка, которую я иногда задаю на собеседованиях 🙂

Есть новостной блоггерный сайт. Есть такие сущности как новости и комментарии к ним.

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

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

Источник

Эквисоединения стр. 2

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

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

Сравните результаты следующих запросов (предикат в предложении WHERE использован лишь для сокращения размера выборки).

Соединение строк из таблиц Income и Outcome по равенству значений в столбце date (база данных Фирма вторсырья)

date code point inc code point out
2001-04-13 00:00:00 6 1 5000.00 7 1 4490.00
2001-04-13 00:00:00 10 1 5000.00 7 1 4490.00
2001-05-11 00:00:00 7 1 4500.00 9 1 2530.00
2001-09-13 00:00:00 12 3 1350.00 16 3 1200.00
2001-09-13 00:00:00 13 3 1750.00 16 3 1200.00
2001-09-13 00:00:00 12 3 1350.00 17 3 1500.00
2001-09-13 00:00:00 13 3 1750.00 17 3 1500.00

Соединение строк из таблиц Income и Outcome по равенству значений в столбцах date и point

point date code inc code out
1 2001-04-13 00:00:00 6 5000.00 7 4490.00
1 2001-04-13 00:00:00 10 5000.00 7 4490.00
1 2001-05-11 00:00:00 7 4500.00 9 2530.00
3 2001-09-13 00:00:00 12 1350.00 16 1200.00
3 2001-09-13 00:00:00 13 1750.00 16 1200.00
3 2001-09-13 00:00:00 12 1350.00 17 1500.00
3 2001-09-13 00:00:00 13 1750.00 17 1500.00

Это соединение по всем столбцам с совпадающими именами эквивалентно естественному соединению

Чтобы продемонстрировать вывод в последнем варианте, воспользуемся левым соединением

Источник

Поддерживаемые Oracle типы соединений в SQL: JOIN и другие

SQL Join Oracle smВ Oracle поддерживается несколько типов соединений, отличающихся способом, которым производится объединение строк из двух или более таблиц или представлений. В этой заметке моего блога будут описаны типы соединений, применяемые в Oracle наиболее часто.

Эквисоединение

При эквисоединении (equi-join) две или более таблиц соединяются на основании условия равенства между столбцами. Другими словами, один и тот же столбец имеет одинаковое значение во всех соединяемых таблицах. Ниже приведен пример применения эквисоединения:

Для показанного выше оператора соединения также можно использовать и следующий новый синтаксис:

Естественное соединение

Естественным соединением (natural join) называется эквисоединение, при котором столбцы, которые должны сопоставляться для выполнения соединения, специально не указываются. Oracle автоматически определяет подлежащие соединению столбцы на основании совпадающих столбцов в двух таблицах. Ниже приведен пример применения естественного соединения:

Рефлексивное соединение

Под рефлексивным соединением (self join) подразумевается соединение таблицы с самой собой за счет использования псевдонимов. В следующем примере осуществляется соединение таблицы employees с самой собой при помощи псевдонима с удалением всех дублированных строк.

SQL Join Oracle

Внутреннее соединение

Внешнее соединение

Внешнее соединение (outer join) применяется для возврата всех строк, которые удовлетворяют указанному условию соединения, плюс некоторых или всех строк из таблицы, в которой нет подходящих строк, удовлетворяющих указанному условию соединения. Существуют три вида внешнего соединения: левое внешнее соединение (left outer join), правое внешнее соединение (right outer join) и полное внешнее соединение (full outer join). В операторе полного внешнего соединения слово OUTER обычно опускается.

Oracle позволяет использовать операцию внешнего соединения, подразумевающую применение знака плюс (+) для обозначения недостающих значений в одной таблице, но рекомендует лучше использовать вместо нее более новый синтаксис соединения ISO/ANSI. Ниже приведен пример типичного запроса с оператором полного внешнего соединения:

Источник

Комфорт