В MySQL выборку с помощью JOIN можно производить разными способами. Мы постараемся рассмотреть все эти виды запросов. Вот список всех запросов с участием JOIN:
А вот иллюстрация к этим видам JOIN:
Я прикреплю к статье файлы нашего сайта, среди которых будет join.php в которых я буду выводить все записи с помощью разных операторов JOIN.
INNER JOINНачнем мы с этого оператора INNER JOIN, потому что этот оператор срабатывает по умолчанию, если вы пишите в запросе просто JOIN. Этот оператор выбирает все записи из двух таблиц, где выполняется условие идущее после оператора ON. У нас есть две таблицы Files и Messages:
Таблица Messages:
Запрос с JOIN будет следующий:
SELECT * FROM Messages INNER JOIN Files ON Messages.fid=Files.fid
В результате будут выведены такие записи
Таблица Files:
LEFT JOIN будет нужен когда выводим все записи сообщений, а есть или нет прикрепленный файл, мы проверим уже через PHP.
LEFT JOIN без пересечений с правой таблицейLEFT JOIN выводит все записи из левой таблицы, кроме тех в которых fid совпадают в правой таблице.
Таблица Messages:
Запрос с LEFT JOIN без пересечений будет следующий:
SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid WHERE Files.fid IS NULL
В результате мы получим вот такую вот выборку:
Таблица Files:
RIGHT JOIN будет нужен когда выводим все прикрепленные файлы без разницы используются они или нет, просто все файлы.
RIGHT JOIN без пересеченийRIGHT JOIN без пересечений выводит все записи из правой таблицы, кроме тех где есть пересечения с левой таблицей.
Таблица Messages:
Запрос с RIGHT JOIN без пересечений будет следующий:
SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid WHERE Messages.fid IS NULL
Таким образом мы получим следующие данные:
| mid | bodytext | fid | path |
| NULL | NULL | 1 | /files/1.png |
RIGHT JOIN будет нужен когда выводим все прикрепленные файлы, которые не прикреплены ни к каким сообщениям. Например, если мы хотим вывести файлы которые не используются.
FULL OUTER JOINНесмотря на то что в языке SQL есть FULL OUTER JOIN, в MySQL этого оператора нет . Дело в том что подобный оператор это огромная нагрузка на сервер. Сейчас у нас 3 файла и 3 сообщения, при этом образуется 4 строк в результате выполнения запроса. Я не уверен, что это хорошая идея писать запрос, который дает в совокупности два запроса LEFT JOIN и RIGHT JOIN. Но все же есть возможность эмулировать запрос FULL OUTER JOIN.
Таблица Messages:
Эмуляция запроса с FULL OUTER JOIN будет следующей:
SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid UNION SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid
В этом запросе мы используем оператор UNION, чтобы объединить два запроса LEFT JOIN и RIGHT JOIN.
В результате мы получим следующие записи:
| mid | bodytext | fid | path |
| 1 | Test | 2 | /files/2.png |
| 2 | Hi | NULL | NULL |
| 3 | Hello | 3 | /files/3.png |
| NULL | NULL | 1 | /files/1.png |
И здесь я уже затрудняюсь сказать зачем потребуется FULL OUTER JOIN. Но раз это есть в SQL, то видимо потребуется потом.
FULL OUTER JOIN без пересеченийЕще один вид JOIN еще более безумный, чем просто FULL OUTER JOIN, а именно FULL OUTER JOIN без пересечений. Я даже не могу предложить где можно использовать этот вид JOIN. Потому что в результате мы получаем файлы которые не используются и сообщения без файлов. И как вы наверно уже догадались этого оператора тоже нет в MySQL. Остается его только эмулировать с помощью двух операторов LEFT JOIN без перечений и RIGHT JOIN без пересечений.
Эмуляция запроса FULL OUTER JOIN без пересечений:
$sql = "SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid WHERE Files.fid IS NULL UNION SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid WHERE Messages.fid IS NULL";
В результате (исходные таблицы те же что и в примере с FULL OUTER JOIN) мы получим:
| mid | bodytext | fid | path |
| 2 | Hi | NULL | NULL |
| NULL | NULL | 1 | /files/1.png |
Вот наверно и все, в следующих уроках мы начнем писать еще более сложные запросы к нескольким таблицам сразу.
MySQL поддерживает следующий синтаксис оператора JOIN при использовании в командах SELECT:
Table_reference, table_reference table_reference JOIN table_reference table_reference INNER JOIN table_reference join_condition table_reference STRAIGHT_JOIN table_reference table_reference LEFT JOIN table_reference join_condition table_reference LEFT JOIN table_reference table_reference NATURAL ] JOIN table_reference { oj table_reference LEFT OUTER JOIN table_reference ON conditional_expr } table_reference RIGHT JOIN table_reference join_condition table_reference RIGHT JOIN table_reference table_reference NATURAL ] JOIN table_reference
где table_reference определено, как:
Table_name [ alias]
и join_condition определено, как:
ON conditional_expr | USING (column_list)
Никогда не следует указывать в части ON какие бы то ни было условия, накладывающие ограничения на строки в наборе результатов. Если необходимо указать, какие строки должны присутствовать в результате, следует сделать это в выражении WHERE .
Необходимо учитывать, что в версиях до 3.23.17 оператор INNER JOIN не принимает параметр join_condition !
Наличие последней из приведенных выше конструкций выражения LEFT OUTER JOIN обусловлено только требованиями совместимости с ODBC:
- Вместо ссылки на таблицу может использоваться псевдоним, который присваивается при помощи выражений tbl_name AS alias_name или tbl_name alias_name: mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name;
- Условный оператор ON представляет собой условие в любой форме из числа тех, которые можно использовать в выражении WHERE .
- Если запись для правой таблицы в частях ON или USING в LEFT JOIN не найдена, то для данной таблицы используется строка, в которой все столбцы установлены в NULL . Эту возможность можно применять для нахождения результатов в таблице, не имеющей эквивалента в другой таблице: mysql> SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; Этот пример находит все строки в таблице table1 с величиной id , которая не присутствует в таблице table2 (т.е. все строки в table1 , для которых нет соответствующих строк в table2). Конечно, это предполагает, что table2.id объявлен как NOT NULL . See section 5.2.6 Как MySQL оптимизирует LEFT JOIN и RIGHT JOIN .
- USING (column_list) служит для указания списка столбцов, которые должны существовать в обеих таблицах. Такое выражение USING , как: A LEFT JOIN B USING (C1,C2,C3,...) семантически идентично выражению ON , например: A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B.C3,...
- Выражение NATURAL JOIN для двух таблиц определяется так, чтобы оно являлось семантическим эквивалентом INNER JOIN или LEFT JOIN с выражением USING , в котором указаны все столбцы, имеющиеся в обеих таблицах.
- INNER JOIN и, (запятая) являются семантическими эквивалентами. Оба осуществляют полное объединение используемых таблиц. Способ связывания таблиц обычно задается в условии WHERE .
- RIGHT JOIN работает аналогично LEFT JOIN . Для сохранения переносимости кода между различными базами данных рекомендуется вместо RIGHT JOIN использовать LEFT JOIN .
- STRAIGHT_JOIN идентично JOIN , за исключением того, что левая таблица всегда читается раньше правой. Это выражение может использоваться для тех (немногих) случаев, когда оптимизатор объединения располагает таблицы в неправильном порядке.
- Начиная с версии MySQL 3.23.12, можно давать MySQL указания о том, какой индекс должен использоваться при извлечении информации из таблицы. Эта возможность полезна, если оператор EXPLAIN (выводящий информацию о структуре и порядке выполнения запроса SELECT), показывает, что MySQL использует ошибочный индекс. Задавая значение индекса в USE INDEX (key_list) , можно заставить MySQL применять для поиска записи только один из указанных индексов. Альтернативное выражение IGNORE INDEX (key_list) запрещает использование в MySQL данного конкретного индекса. Выражения USE/IGNORE KEY являются синонимами для USE/IGNORE INDEX .
Несколько примеров:
Mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id); mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id; mysql> SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3; mysql> SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
В этом учебном пособии вы узнаете, как использовать JOINS (INNER и OUTER)
в MySQL с синтаксисом, рисунками и примерами.
MySQL JOINS используются для извлечения данных из нескольких таблиц. JOIN выполняется всякий раз, когда две или более таблиц объединяются в SQL предложении.
Существуют различные типы соединений MySQL:
Рассмотрим синтаксис MySQL JOIN, а также изучим примеры MySQL JOIN.
INNER JOIN (простое соединение)Скорее всего, вы уже писали запросы в которых используются MySQL INNER JOIN. Это наиболее распространенный тип соединения. MySQL INNER JOINS возвращает все строки из нескольких таблиц, где выполняется условия соединения.
СинтаксисСинтаксис INNER JOIN в MySQL:
SELECT columns
FROM table1
INNER JOIN table2
В этом рисунке, MySQL INNER JOIN возвращает затененную область:
MySQL INNER JOIN будет возвращать записи, где table1 и table2 будут пересекаться.
ПримерНиже приведен пример MySQL INNER JOIN:
MySQL
У нас есть еще одна таблица orders с тремя полями (order_id , supplier_id и order_date ). Она содержит следующие данные:
| 500125 | 10000 | 05.05.2015 |
| 500126 | 10001 | 08.02.2016 |
| 500127 | 10004 | 06.01.2017 |
Если мы выполним MySQL оператор SELECT (который содержит INNER JOIN) ниже:
MySQL
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers INNER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Строки для Microsoft и NVIDIA из таблицы suppliers будут опущены, так как значения supplier_id 10002 и 10003 не существует в обеих таблицах. Строка order_id 500127 из таблицы orders будет опущена, так как supplier_id 10004 не существует в таблице suppliers .
Старый СинтаксисВ качестве последнего примечания, стоит отметить, что приведенный выше пример MySQL INNER JOIN можно переписать, используя старый неявный синтаксис следующим образом (но рекомендуется использовать синтаксис INNER JOIN):
Другой тип соединения называется MySQL LEFT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с левосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.
СинтаксисСинтаксис для LEFT OUTER JOIN в MySQL:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
В некоторых базах данных LEFT OUTER JOIN заменяется на LEFT JOIN.
На этом рисунке, MySQL LEFT OUTER JOIN возвращает затененную область:
MySQL LEFT OUTER JOIN возвратит все записи из table1 и только те записи из table2 , которые пересекаются с table1 .
ПримерMySQL
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT JOIN orders ON suppliers.supplier_id = orders.supplier_id;
SELECT suppliers.supplier_id,suppliers.supplier_name,orders.order_date FROM suppliers LEFT JOIN orders ON suppliers.supplier_id=orders.supplier_id; |
Этот пример LEFT OUTER JOIN возвратит все строки из таблицы suppliers , и только те строки из таблицы orders , где объединяемые поля равны.
Если значение supplier_id в таблице suppliers не существует в таблице orders , все поля таблицы orders будут отображаться в результирующем наборе как NULL.
Рассмотрим некоторые данные, чтобы понять, как работает LEFT OUTER JOIN:
У нас есть таблица suppliers с двумя полями (supplier_id и supplier_name ) которая содержит следующие данные:
Если мы выполним MySQL оператор SELECT (который содержит LEFT OUTER JOIN) ниже:
MySQL
SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date FROM suppliers LEFT OUTER JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Строки для Microsoft и NVIDIA будут включены, так как был использован LEFT OUTER JOIN. Тем не менее, вы заметите, что поле order_date для этих записей содержит значение NULL.
Другой тип соединения называется MySQL RIGHT OUTER JOIN. Этот тип соединения возвращает все строки из таблиц с правосторонним соединением, указанным в условии ON, и только те строки из другой таблицы, где объединяемые поля равны.
СинтаксисСинтаксиRIGHT OUTER JOIN в MySQL:
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
В некоторых базах данных, RIGHT OUTER JOIN заменяется на RIGHT JOIN.
На этом рисунке, MySQL RIGHT OUTER JOIN возвращает затененную область:
MySQL RIGHT OUTER JOIN возвратит все записи из table2 и только те записи из table1 , которые пересекаются с table2 .
ПримерНиже приведен пример MySQL RIGHT OUTER JOIN:
MySQL
SELECT orders.order_id, orders.order_date, suppliers.supplier_name FROM suppliers RIGHT JOIN orders ON suppliers.supplier_id = orders.supplier_id;
Поводом для написания данной статьи послужили некоторые дебаты в одной из групп linkedin, связанной с MySQL, а также общение с коллегами и хабролюдьми:-)
В данной статье хотел написать что такое вообще 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).
Где Р - условия склейки таблиц и фильтры в 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 .
Итак, как мы видим, JOINы это просто группа вложенных циклов. Так почему же в MySQL и UNION и SELECT и запросы с SUBQUERY тоже джоины?
MySQL оптимизатор старается приводить запросы к тому виду к которому ему удобней обрабатывать и выполнять запросы по стандартной схеме.
С SELECT все понятно - просто цикл без вложенных циклов. Все UNION выполняются как отдельные запросы и результаты складываются во временную таблицу, и потом MySQL работает уже с этой таблицей, т.е. проходясь циклом по записям в ней. С Subquery та же история.
Приводя все к одному шаблону, например, МySQL переписывает все RIGHT JOIN запросы на LEFT JOIN эквиваленты.
Но стратегия выполнения запросов через вложенные циклы накладывает некоторые ограничения, например, в связи с такой схемой MySQL не поддерживает выполнение FULL OUTER JOIN запросов.
Но результат такого запроса можно получить с помощью UNION двух запросов на LEFT JOIN и на RIGHT JOIN
Пример самого запроса можно посмотреть по ссылке на вики.
В отличии от других СУРБД MySQL не генерирует байткод для выполнения запроса, вместо этого MySQL генерирует список инструкций в древовидной форме, которых придерживается engine выполнения запроса выполняя запрос.
Это дерево имеет следующий вид и имеет название «left-deep tree»
В отличии от сбалансированных деревьев (Bushy plan), которые применяются в других СУБД (например Oracle)
Теперь перейдем к самому интересному - к оптимизации джоинов.
MySQL оптимизатор, а именно та его часть, которая отвечает за оптимизацию JOIN-ов выбирает порядок в котором будет производиться склейка имеющихся таблиц, т.к. можно получить один и тот же результат (датасет) при различном порядке таблиц в склейке. MySQL оптимизатор оценивает стоимость различных планов и выбирает с наименьшей стоимостью. Единицей оценки является операция единичного чтения страницы данных размером в 4 килобайта из произвольного места на диске.
Для выбранного плана можно узнать стоимость путем выполнения команды
SHOW SESSION STATUS LIKE "Last_query_cost";
Оценка основана на статистике: количество страниц памяти, занимаемое таблицей и/или индексами для этой таблицы, cardinality (число уникальных значений) индексов, длинна записей и индексов, их распределение и т.д. Во время своей оценки оптимизатор не рассчитывает на то, что какие-то части попадут в кеш, оптимизатор предполагает, что каждая операция чтения это обращение к диску.
Иногда анализатор-оптимизатор не может проанализировать все возможные планы выполнения и выбирает неправильный. Например, если у нас INNER JOIN по 3м таблицам, то возможных вариантов у анализатора - 3! = 6, а если у нас склейка по 10 таблицам, то тут возможных вариантов уже 10! = 3628800… MySQL не может проанализировать столько вариантов, поэтому в таком случае он использует алгоритм "жадного " поиска.
Но не стоит применять этот хак ко всем запросам, расчитывая произвести оптимизацию на спичках и сэкономить время на составление плана выполнения запроса оптимизатором и добавлять STRAIGH_JOIN ко всем запросам с джоинами , т.к. данные меняются и склейка, которая оптимальна сейчас может перестать быть оптимальной со временем, и тогда запросы начнуть очень сильно лагать.
Также, как уже говорилось выше, результаты джоинов помещаются во временные таблицы, поэтому зачастую уместно применять «derived table» в котором мы накладываем все необходимые нам условия на выборку, а также указываем LIMIT и порядок сортировки. В данном случае мы избавимся от избыточности данных во временной таблице, а также проведем сортировку на раннем этапе (по результату одной выборки, а не финальной склейки, что уменьшит размеры записей которые будут сортироваться).
Стандартный пример подхода описанного выше. Простая выборка для отношения много к многим: новости и теги к ним.
SELECTt.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.
Все нужно сделать одним запросом. Да, это, может, и не самый лучший способ, и вы вольны предложить другое решение:-)
Теги:
- MySQL
- mysql performance
- JOIN
На протяжении немалого времени, в начале своей карьеры веб-разработчика, я работал с базой данный как умел, а умел я не многое. Составлял простые примитивные запросы, а порою даже вставлял запросы в циклы. На тот момент мне к сожалению не попалась в руки правильная книжка по mySQL и учить приходилось методом проб и ошибок. Множество статей в интернете как-то не сразу донесли до меня замечательный mySQL запрос — JOIN.
В этой публикации я расскажу о всех возможных вариантах работы с JOIN и более того, представлю принцип работы каждой команды — визуально.
Отдельно стоит отметить пункты 5,6 и 7. На самом деле эти запросы не соединяют две таблицы, а наоборот исключают из одной таблицы столбцы присутствующие в другой. На деле это может оказать очень полезным.
Inner JOIN
Один из самых распространенных запросов, встречается предельно часто. Этот запрос вернет все записи из левой таблицы (таб. А) и записи из (таб. В), но при этом возвращены будут только совпадающие столбцы.
Пример запроса:
Просмотр кода SQL
| SELECT < select_list> FROM Table_A A INNER JOIN Table_B B ON A. Key = B. Key |
Left JOIN
Данный запрос вернет все столбцы из левой таблицы (таб. А), а также все столбцы из правой таблицы (таб. В) но только которые совпадают со столбцами из левой таблицы.
Пример запроса:
Просмотр кода SQL
| SELECT < select_list> FROM Table_A A LEFT JOIN Table_B B ON A. Key = B. Key |
Right JOIN
Аналогичен предыдущему запросу, но уже вернет все столбцы из правой таблицы (таб. В), а также все столбцы из левой таблицы (таб. А) которые совпадают со столбцами из правой таблицы.
Пример запроса:
Просмотр кода SQL
| SELECT < select_list> FROM Table_A A RIGHT JOIN Table_B B ON A. Key = B. Key |
Outer JOIN
Часто данный запрос записывают как FULL OUTER JOIN или FULL JOIN, все вариации выполняют одно действие, а именно возвращают все столбцы из обоих таблиц, при этом совпадающие столбцы будут перекрыты столбцами из левой таблицы.
Пример запроса:
Просмотр кода SQL
| SELECT < select_list> FROM Table_A A FULL OUTER JOIN Table_B B ON A. Key = B. Key |
Left Excluding JOIN
Этот запрос вернет все столбцы из левой таблицы (таб. А), которые не совпадают со столбцами из правой таблицы (таб. В).
Пример запроса:
Просмотр кода SQL