8.2.1.8 Outer Join Optimization — MySQL 5.7

8.2.1.8 Оптимизация внешних объединений

Внешние объединения включают LEFT JOIN и RIGHT JOIN.

MySQL реализует оптимизацию внешних объединений следующим образом:

Таблица B устанавливается как зависящая от таблицы A и всех таблиц, от которых зависит A.
Таблица A устанавливается как зависящая от всех таблиц (кроме B), используемых в условии LEFT JOIN.
Условие LEFT JOIN используется для определения того, как извлекать строки из таблицы B. (Другими словами, любые условия в предложении WHERE не используются.)
Выполняются все стандартные оптимизации объединений, за исключением того, что таблица всегда считывается после всех таблиц, от которых она зависит. При наличии циклической зависимости возникает ошибка.
Выполняются все стандартные оптимизации WHERE.
Если в таблице A есть строка, соответствующая условию WHERE, но в таблице B нет строки, соответствующей условию ON, генерируется дополнительная строка B со всеми столбцами, установленными в NULL.
Если вы используете LEFT JOIN для поиска строк, которые не существуют в какой-то таблице, и у вас есть следующее условие: col_name IS NULL в части WHERE, где col_name - столбец, объявленный как NOT NULL, MySQL прекращает поиск дополнительных строк (для определённой комбинации ключей) после того, как найдена одна строка, соответствующая условию LEFT JOIN.

Реализация RIGHT JOIN аналогична реализации LEFT JOIN с обратными ролями таблиц. Правые объединения преобразуются в эквивалентные левые объединения, как описано в разделе 8.2.1.9, «Оптимизация внешних объединений».

Для LEFT JOIN, если условие WHERE всегда ложно для сгенерированной строки NULL, то LEFT JOIN изменяется на внутреннее объединение. Например, предложение WHERE будет ложным в следующем запросе, если t2.column1 было NULL:

SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;

Поэтому запрос безопасно преобразовать во внутреннее объединение:

SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;

Теперь оптимизатор может использовать таблицу t2 до таблицы t1, если это приведёт к более эффективному плану запроса. Для указания порядка объединения таблиц используйте STRAIGHT_JOIN; см. раздел 13.2.9, «Выражение SELECT». Однако STRAIGHT_JOIN может препятствовать использованию индексов, так как отключает преобразования полуобъединений; см. раздел 8.2.2.1, «Оптимизация подзапросов, производных таблиц и ссылок на представления с преобразованиями полуобъединений».