8.2.2.1 Optimizing Subqueries, Derived Tables, and View References with Semijoin Transformations — MySQL 5.7

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

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

При внутреннем соединении между двумя таблицами соединение возвращает строку из одной таблицы столько раз, сколько совпадений есть в другой таблице. Но для некоторых вопросов единственной важной информацией является наличие совпадения, а не количество совпадений. Предположим, что есть таблицы с именами class и roster, которые содержат список курсов по учебному плану и списки присутствия на занятиях (студенты, записавшиеся на каждый курс), соответственно. Чтобы вывести список курсов, на которые записаны студенты, можно использовать это соединение:

SELECT class.class_num, class.class_name
FROM class INNER JOIN roster
WHERE class.class_num = roster.class_num;

Однако результат отображает каждый курс один раз для каждого студента, записавшегося на него. Для задаваемого вопроса эта избыточность информации не нужна.

Предполагая, что class_num является первичным ключом в таблице class, подавление дубликатов возможно с использованием SELECT DISTINCT, но неэффективно сначала генерировать все соответствующие строки, чтобы затем удалить дубликаты.

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

SELECT class_num, class_name
FROM class
WHERE class_num IN (SELECT class_num FROM roster);

Здесь оптимизатор распознает, что вставка IN требует от подзапроса возвращения только одного экземпляра каждого номера курса из таблицы roster. В этом случае запрос может использовать полусоединение; то есть операцию, которая возвращает только один экземпляр каждой строки в class, которая совпадает со строками в roster.

В спецификации внешнего запроса разрешены синтаксические конструкции внешнего и внутреннего соединения, а ссылки на таблицы могут быть базовыми таблицами, производными таблицами или ссылками на представления.

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

Он должен быть подзапросом IN (или =ANY), который появляется на верхнем уровне ввода WHERE или ON, возможно, как термин в выражении AND. Например:
```
SELECT ...
FROM ot1, ...
WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);
```
Здесь ot_i и it_i представляют таблицы во внешней и внутренней частях запроса, а oe_i и ie_i представляют выражения, которые ссылаются на столбцы во внешних и внутренних таблицах.
Он должен быть единственным SELECT без конструкций UNION.
Он не должен содержать вставки GROUP BY или HAVING.
Он не должен быть неявным образом сгруппирован (не должен содержать агрегатные функции).
Он не должен иметь ORDER BY с LIMIT.
В внешнем запросе не должно использоваться тип соединения STRAIGHT_JOIN.
Модификатор STRAIGHT_JOIN не должен присутствовать.
Количество внешних и внутренних таблиц вместе должно быть меньше максимального количества таблиц, разрешенных в соединении.

Подзапрос может быть коррелированным или некоррелированным. Разрешается использование DISTINCT, как и LIMIT, если также не используется ORDER BY.

Если подзапрос удовлетворяет перечисленным выше критериям, MySQL преобразует его в полусоединение и выбирает стратегию на основе стоимости из этих стратегий:

Преобразовать подзапрос в соединение или использовать извлечение таблицы и выполнить запрос как внутреннее соединение между таблицами подзапроса и внешними таблицами. Извлечение таблицы извлекает таблицу из подзапроса во внешний запрос.
Удаление дубликатов: выполнить полусоединение так, как если бы это было соединение, и удалить дубликаты с использованием временной таблицы.
Первый совпадение: при сканировании внутренних таблиц для сочетаний строк, если есть несколько экземпляров заданной группы значений, выбрать один, а не возвращать их все. Это «сокращает» сканирование и исключает создание ненужных строк.
Неполный поиск: поиск в таблице подзапроса с помощью индекса, который позволяет выбрать одно значение из каждой группы значений подзапроса.
Материализация подзапроса во временную таблицу с индексом, которая используется для выполнения соединения, где индекс используется для удаления дубликатов. Индекс может также использоваться позже для поиска при соединении временной таблицы с внешними таблицами; если нет, таблица сканируется. Дополнительную информацию о материализации см. в разделе 8.2.2.2 «Оптимизация подзапросов с помощью материализации».

Каждую из этих стратегий можно включить или отключить с помощью следующих флагов системных переменных optimizer_switch:

Флаг semijoin управляет использованием полусоединений.
Если semijoin включен, флаги firstmatch, loosescan, duplicateweedout и materialization обеспечивают более точный контроль над допустимыми стратегиями полусоединения.
Если отключена стратегия полусоединения duplicateweedout, она не используется, если не отключены все другие применимые стратегии.
Если duplicateweedout отключен, в некоторых случаях оптимизатор может сгенерировать план запроса, далекий от оптимального. Это происходит из-за эвристического обрезания во время жадного поиска, что можно избежать, установив optimizer_prune_level=0.

Эти флаги включены по умолчанию. См. раздел 8.9.2 «Переключаемые оптимизации».

Оптимизатор сводит к минимуму различия в обработке представлений и производных таблиц. Это влияет на запросы, которые используют модификатор STRAIGHT_JOIN и представление с подзапросом IN, который можно преобразовать в полусоединение. Следующий запрос демонстрирует это, потому что изменение обработки приводит к изменению преобразования и, следовательно, к другой стратегии выполнения:

CREATE VIEW v AS
SELECT *
FROM t1
WHERE a IN (SELECT b
           FROM t2);

SELECT STRAIGHT_JOIN *
FROM t3 JOIN v ON t3.x = v.a;

Оптимизатор сначала рассматривает представление и преобразует подзапрос IN в полусоединение, затем проверяет, можно ли объединить представление во внешний запрос. Поскольку модификатор STRAIGHT_JOIN во внешнем запросе запрещает полусоединение, оптимизатор отказывается от объединения, вызывая оценку производной таблицы с помощью материализованной таблицы.

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

Полусоединенные таблицы отображаются во внешнем выборе. Для расширенного вывода EXPLAIN текст, отображаемый последующим SHOW WARNINGS, показывает переписанный запрос, который отображает структуру полусоединения. (См. раздел 8.8.3 «Формат расширенного вывода EXPLAIN».) Из этого можно понять, какие таблицы были извлечены из полусоединения. Если подзапрос был преобразован в полусоединение, вы можете увидеть, что предикат подзапроса исчез, а его таблицы и вставка WHERE были объединены в список внешних соединений запроса и вставку WHERE.
Использование временной таблицы для удаления дубликатов обозначается Start temporary и End temporary в столбце Extra. Таблицы, которые не были извлечены и находятся в диапазоне строк вывода EXPLAIN, охватываемых Start temporary и End temporary, имеют свои rowid во временной таблице.
FirstMatch(tbl_name) в столбце Extra указывает на сокращение соединения.
LooseScan(m..n) в столбце Extra указывает на использование стратегии LooseScan. m и n — ключевые номера деталей.
Использование временной таблицы для материализации обозначается строками со значением select_type как MATERIALIZED и строками со значением table как <subqueryN>.