10.2.1.9 Outer Join Optimization — MySQL 8.4

10.2.1.9 Оптимизация внешних соединений

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

MySQL реализует A LEFT JOIN B join_specification следующим образом:

Таблица 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 с перевёрнутыми ролями таблиц. Правые соединения преобразуются в эквивалентные левые соединения, как описано в Разделе 10.2.1.10, «Оптимизация внешних соединений».

Для 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;

Тривиальные WHERE условия, возникающие из константных литеральных выражений, удаляются на стадии подготовки, а не на более поздних этапах оптимизации, когда соединения уже были упрощены. Раннее удаление тривиальных условий позволяет оптимизатору преобразовывать внешние соединения во внутренние; это может привести к улучшенным планам для запросов с внешними соединениями, содержащими тривиальные условия в фрагменте WHERE, например, таком:

SELECT * FROM t1 LEFT JOIN t2 ON condition_1 WHERE condition_2 OR 0 = 1

Оптимизатор теперь на стадии подготовки видит, что 0 = 1 всегда ложно, делая OR 0 = 1 избыточным и удаляя его, оставляя это:

SELECT * FROM t1 LEFT JOIN t2 ON condition_1 where condition_2

Теперь оптимизатор может переписать запрос как внутреннее соединение, так:

SELECT * FROM t1 JOIN t2 WHERE condition_1 AND condition_2

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