Mysql inner join примеры: Поговорим о JOIN

MySQL и JOINы / Хабр

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

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

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

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

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

SELECT
  *
FROM
  Table1
INNER JOIN
  Table2 ON P1(Table1,Table2)
INNER JOIN
  Table3 ON P2(Table2,Table3)
WHERE
   P(Table1,Table2,Table3).

* This source code was highlighted with Source Code Highlighter.

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

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

FOR each row t1 in Table1 {
 IF(P(t1)) {
  FOR each row t2 in Table2 {
   IF(P(t2)) {
    FOR each row t3 in Table3 {
     IF P(t3) {
       t:=t1||t2||t3; OUTPUT t;
     }
    }
   }
  }
 }
}

* This source code was highlighted with Source Code Highlighter.

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

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

SELECT
   *
FROM
   Table1
LEFT JOIN
(
   Table2 LEFT JOIN Table3 ON P2(Table2,Table3)
)
   ON P1(Table1,Table2)
WHERE
   P(Table1,Table2,Tabke3)

* This source code was highlighted with Source Code Highlighter.

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

FOR each row t1 in T1 {
 BOOL f1:=FALSE;
 FOR each row t2 in T2 such that P1(t1,t2) {
  BOOL f2:=FALSE;
  FOR each row t3 in T3 such that P2(t2,t3) {
   IF P(t1,t2,t3) {
    t:=t1||t2||t3; OUTPUT t;
   }
   f2=TRUE;
   f1=TRUE;
  }
  IF (!f2) {
   IF P(t1,t2,NULL) {
    t:=t1||t2||NULL; OUTPUT t;
   }
   f1=TRUE;
  }
 }
 IF (!f1) {
  IF P(t1,NULL,NULL) {
   t:=t1||NULL||NULL; OUTPUT t;
  }
 }
}

* This source code was highlighted with Source Code Highlighter.

Более подробно почитать об этом можно здесь — dev.mysql.com/doc/refman/5.1/en/nested-joins.html

Итак, как мы видим, 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»

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

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 и порядок сортировки. В данном случае мы избавимся от избыточности данных во временной таблице, а также проведем сортировку на раннем этапе (по результату одной выборки, а не финальной склейки, что уменьшит размеры записей которые будут сортироваться).

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

SELECT
    t.tid, t.description, n.nid, n.title, n.extract, n.modtime
FROM
(
  SELECT
    n.nid
  FROM
    news n
  WHERE
    n.type = 1321
    AND n. published = 1
    AND status = 1
  ORDER BY
    n.modtime DESC
  LIMIT
    200
) as news
INNER JOIN
    news n ON n.nid = news.nid
INNER JOIN
    news_tag nt ON n.nid = nt.nid
INNER JOIN
    tags t ON nt.tid = t.tid

* This source code was highlighted with Source Code Highlighter.

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

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

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

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

LEFT JOIN и INNER JOIN объединение таблиц.

MySQL поддерживает синтаксис [LEFT|RIGHT|INNER] JOIN для части определения таблиц в операторе SELECT и операторах DELETE и UPDATE для нескольких таблиц. Объединения таблиц позволяют извлекать данные из нескольких таблиц без создания временных таблиц и за один запрос.

Пример объединения нескольких таблиц при помощи LEFT JOIN:

SELECT * FROM t1 
    LEFT JOIN t2 ON (t2.a = t1.a)
    LEFT JOIN t3 ON (t2.b = t3.b)
-- эквивалентно
SELECT * FROM t1 
LEFT JOIN t2
    INNER JOIN t3
    ON t2.b = t3.b
ON t2.a = t1.a

Здесь все записи из таблицы t1 соединяются с соответствующими записями таблицы t2, в свою очередь все записи из таблицы t2 соединяются с соответствующими записями таблицы t3.

Эквивалентный вариант объединяет t1 с t3 через промежуточную таблицу t2. Так как соединение между t1 и t2 является LEFT JOIN, то получаем все записи таблицы t1.

Инструкция ON служит для условий, указывающих, как соединять таблицы. Другими словами, выражение ON (t2.a = t1.a) говорит MySQL по каким полям объединять таблицу t1 с таблицей t2. Оператор ON может содержать сложные условия объединения таблиц, например ON (t2.a = t1.a AND t2.b = t1.b) — здесь происходит соединение таблицы t1 с таблицей t2 по двум столбцам a и b.

При объединении таблиц, вместо ссылки на таблицу может использоваться псевдоним alias, который присваивается при помощи выражений tbl AS alias или tbl alias:

-- использование `tbl AS alias`
SELECT * FROM left_tbl AS a 
      LEFT JOIN right_tbl AS b ON (b.id = a.id)
-- эквивалентно, без `AS`
SELECT * FROM left_tbl a 
      LEFT JOIN right_tbl b ON (b.id = a.id)

• LEFT JOIN: возвращает все записи из левой таблицы и соответствующие записи из правой таблицы.
• RIGHT JOIN: возвращает все записи из правой таблицы и соответствующие записи из левой таблицы.
• INNER JOIN или просто JOIN: выбирает записи, которые имеют совпадающие значения в обеих таблицах (перекрёстное объединение).

В MySQL CROSS JOIN и INNER JOIN являются синтаксическими эквивалентами (они могут заменять друг друга). В стандартном SQL они не эквивалентны. INNER JOIN используется с предложением ON, в противном случае используется CROSS JOIN.

На практике чаще всего используется объединение таблиц при помощи инструкции LEFT JOIN.

SELECT * FROM left_tbl 
    LEFT JOIN right_tbl ON (right_tbl.id = left_tbl.id)

Запрос возвращает все записи из левой (первой) таблицы left_tbl и только совпавшие записи (согласно right_tbl.id = left_tbl.id) из правой (второй) таблицы right_tbl.

При использовании LEFT JOIN, если нет соответствующей строки/записи для правой таблицы в операторе ON, то для правой таблицы используется строка со всеми столбцами, установленными в NULL. Это поведение можно использовать, чтобы найти строки/записи в таблице, которые не имеют аналога в другой таблице:

SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

Объединение при помощи RIGHT JOIN работает аналогично LEFT JOIN. Для сохранения переносимости кода между различными базами данных рекомендуется вместо RIGHT JOIN использовать LEFT JOIN.

SELECT * FROM left_tbl 
    RIGHT JOIN right_tbl ON (right_tbl.id = left_tbl.id)

Запрос возвращает все записи из правой (второй) таблицы right_tbl и только совпавшие записи (согласно right_tbl.id = left_tbl. id) из левой (первой) таблицы left_tbl.

Объединения при помощи JOIN или INNER JOIN производят декартово произведение между указанными таблицами (то есть каждая строка в первой таблице соединяется с каждой совпадающей строкой во второй таблице).

Инструкции JOIN/INNER JOIN и запятая , между названиями таблиц в операторе FROM семантически эквивалентны. Способ связывания таблиц в этом случае задается в условии WHERE.

SELECT * FROM left_tbl 
    INNER JOIN right_tbl ON (left_tbl.id = right_tbl.id)
-- эквивалентно
SELECT * FROM left_tbl, right_tbl 
    WHERE left_tbl.id = right_tbl.id

При использовании инструкции INNER JOIN следует учитывать, что процесс соединения таблиц может быть ресурсоемким, поэтому следует соединять только те таблицы, данные из которых действительно необходимы. Чем больше таблиц соединяется, тем больше снижается производительность.

MySQL ВНУТРЕННЕЕ СОЕДИНЕНИЕ

INNER JOIN используется для возврата данных из нескольких таблиц. В частности, INNER JOIN предназначен для случаев, когда вы заинтересованы только в возврате записей, в которых есть хотя бы одна строка в обеих таблицах, соответствующих условию соединения.

Рассмотрим следующие таблицы:

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

ВЫБЕРИТЕ город, страну ИЗ города INNER JOIN страна ON city.country_id = страна.country_id;

И результат будет выглядеть так:

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

Вот схема этих двух таблиц (с выделенной связью внешнего ключа):

Квалификация полей

Вы заметите, что в приведенном выше примере мы уточнили имена некоторых полей с соответствующими именами таблиц. В частности, последняя строка гласит: город .country_id = страна .country_id; .

Мы делаем это, потому что имена полей совпадают ( country_id ). Если бы мы не уточнили его именами таблиц (т.е. идентификатор_страны = идентификатор_страны; ) MySQL не будет знать, на какой столбец мы ссылаемся — на столбец в таблице city или в таблице country .

Если мы не уточним столбцы, MySQL выдаст ошибку неоднозначного столбца. Это будет примерно так:

Использование псевдонимов

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

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

ВЫБЕРИТЕ город, страну ИЗ города а INNER JOIN страна b ON a.country_id = b.country_id;

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

В следующем примере мы меняем его местами и предоставляем список стран в одном столбце с количеством городов, которые содержит каждая страна, в другом столбце.

Для этого мы используем агрегатную функцию COUNT() для подсчета количества городов для каждой страны, затем GROUP BY Предложение для группировки результатов по странам.

ВЫБЕРИТЕ страну, COUNT(город) ИЗ страны а ВНУТРЕННЕЕ СОЕДИНЕНИЕ город b ON a.country_id = b.country_id СГРУППИРОВАТЬ ПО странам;

Результат:

Разница между типами соединения проста.

ВНУТРЕННЕЕ СОЕДИНЕНИЕ

Возвращает только строки, в которых есть совпадающая строка в обеих таблицах .

ЛЕВОЕ СОЕДИНЕНИЕ

Все строки из слева 9Таблица 0081 будет возвращена, даже если в правильной таблице нет соответствующей строки.

ПРАВОЕ СОЕДИНЕНИЕ

Будут возвращены все строки из правой таблицы, даже если в левой таблице нет соответствующей строки.

В примерах на этой странице используется образец базы данных Sakila.

INNER JOIN в MySQL с примерами. | Джошуа Отвелл

 mysql> ВЫБЕРИТЕ СЧЕТ(город), страна 
 -> ИЗ города 
 -> ВНУТРЕННЕЕ СОЕДИНЕНИЕ страна 
 -> ON city.country_id = country.country_id 
 -> ГРУППИРОВАТЬ ПО стране 
 -> ИМЕЕТ СЧЁТ (город) = 6; 
 + — — — — — — — + — — — — — + 
 | COUNT(город) | страна | 
 + — — — — — — —+ — — — — — + 
 | 6 | Колумбия | 
 | 6 | Египет | 
 | 6 | Украина | 
 | 6 | Вьетнам | 
 + — — — — — — — + — — — — — + 
 4 строки в наборе (0,00 с) 

 mysql> SELECT COUNT (город), страна, country_id 
 -> ИЗ города 
 -> ВНУТРЕННЕЕ СОЕДИНЕНИЕ страна 
 -> ON city.country_id = country.country_id 
 -> ГРУППИРОВАТЬ ПО стране 
 -> ИМЕЕТ СЧЁТ (город) = 6; 
 ОШИБКА 1052 (23000): Столбец «country_id» в списке полей неоднозначен 

 mysql> SELECT COUNT(ci.city), co.country, ci.country_id 
 -> FROM город AS ci 
 -> INNER JOIN страна AS co 
 -> ON ci.country_id = co.country_id 
 -> ГРУППИРОВАТЬ ПО co.country, ci.country_id 
 -> ИМЕЕТ СЧЁТ (ci.city) = 6; 
 + — — — — — — — — + — — — — — + — — — — — — + 
 | COUNT(город) | страна | идентификатор_страны | 
 + — — — — — — — — + — — — — — + — — — — — — + 
 | 6 | Колумбия | 24 | 
 | 6 | Египет | 29 | 
 | 6 | Украина | 100 | 
 | 6 | Вьетнам | 105 | 
 + — — — — — — — — + — — — — — + — — — — — — + 
 4 строки в наборе (0,00 с) 

 имя_таблицы AS требуемое_имя_ссылки имя_таблицы требуемое_имя_ссылки 

 ИЗ города AS ci 

 ИЗ города ci 

 mysql> SELECT COUNT(город), страна 
 -> ИЗ города AS ci 
 -> ВНУТРЕННЕЕ СОЕДИНЕНИЕ страны AS co 
 -> USING(country_id) 
 -> GROUP BY co.country 
 -> HAVING COUNT(ci.city) = 6; 
 + — — — — — — —+ — — — — — + 
 | COUNT(город) | страна | 
 + — — — — — — —+ — — — — — + 
 | 6 | Колумбия | 
 | 6 | Египет | 
 | 6 | Украина | 
 | 6 | Вьетнам | 
 + — — — — — — — + — — — — — + 
 4 ряда в наборе (0,00 с) 

 mysql> ВЫБЕРИТЕ ci.city, a.address, a.postal_code, co.country 
 -> ОТ города КАК ci 
 -> Адрес ВНУТРЕННЕГО СОЕДИНЕНИЯ КАК 
 -> ВКЛ ci. city_id = a.city_id 
 -> ВНУТРЕННЕЕ СОЕДИНЕНИЕ страна КАК co 
 -> ВКЛ ci.country_id = co.country_id 
 -> ГДЕ co .country = 'Египет';