10.2.2.2 Optimizing Subqueries with Materialization — MySQL 8.4

10.2.2.2 Оптимизация подзапросов с помощью материализации

Оптимизатор использует материализацию для повышения эффективности обработки подзапросов. Материализация ускоряет выполнение запроса, генерируя результат подзапроса как временную таблицу, обычно в памяти. В первый раз MySQL нуждается в результате подзапроса, он материализует этот результат в временную таблицу. В любой последующий раз, когда результат необходим, MySQL снова ссылается на временную таблицу. Оптимизатор может индексировать таблицу с помощью хэш-индекса, чтобы сделать поиск быстрым и недорогим. Индекс содержит уникальные значения для удаления дубликатов и уменьшения размера таблицы.

Материализация подзапроса использует временную таблицу в памяти, если это возможно, возвращаясь к хранению на диске, если таблица становится слишком большой. См. Раздел 10.4.4, «Использование внутренних временных таблиц в MySQL».

Если материализация не используется, оптимизатор иногда переписывает некоррелированный подзапрос как коррелированный подзапрос. Например, следующий IN подзапрос некоррелированный (where_condition включает только столбцы из t2 и не t1):

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

Оптимизатор может переписать это как EXISTS коррелированный подзапрос:

SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

Материализация подзапроса с использованием временной таблицы избегает таких переписываний и позволяет выполнить подзапрос только один раз вместо одного раза на каждую строку внешнего запроса.

Для использования материализации подзапросов в MySQL переменная системы optimizer_switch materialization должна быть включена. (См. Раздел 10.9.2, «Переключаемые оптимизации».) С включенной флагом materialization, материализация применяется к предикатным подзапросам, которые появляются где угодно (в списке выбора, WHERE, ON, GROUP BY, HAVING или ORDER BY), для предикатов, которые попадают в любой из этих случаев использования:

Предикат имеет этот вид, когда ни внешнее выражение oe_i, ни внутреннее выражение ie_i не могут быть NULL. N равно 1 или больше.
```
(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)
```
Предикат имеет этот вид, когда есть одно внешнее выражение oe и внутреннее выражение ie. Выражения могут быть NULL.
```
oe [NOT] IN (SELECT ie ...)
```
Предикат равен IN или NOT IN и результат UNKNOWN (NULL) имеет то же значение, что и результат FALSE.

Следующие примеры иллюстрируют, как требование эквивалентности оценки предикатов UNKNOWN и FALSE влияет на то, может ли использоваться материализация подзапроса. Предположим, что where_condition включает только столбцы из t2, а не t1, так что подзапрос некоррелированный.

Этот запрос подлежит материализации:

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

Здесь не имеет значения, возвращает ли предикат IN UNKNOWN или FALSE. В любом случае строка из t1 не включается в результат запроса.

Пример, где материализация подзапроса не используется, — это следующий запрос, где t2.b — столбец с возможностью значения NULL:

SELECT * FROM t1
WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2
                          WHERE where_condition);

Следующие ограничения применяются к использованию материализации подзапросов:

Типы внутренних и внешних выражений должны совпадать. Например, оптимизатор может использовать материализацию, если оба выражения являются целочисленными или оба являются десятичными, но не может, если одно выражение целочисленное, а другое — десятичное.
Внутреннее выражение не может быть BLOB.

Использование EXPLAIN с запросом дает некоторое представление о том, использует ли оптимизатор материализацию подзапроса:

По сравнению с выполнением запроса без использования материализации, select_type может измениться с DEPENDENT SUBQUERY на SUBQUERY. Это указывает на то, что для подзапроса, который будет выполняться один раз на каждую внешнюю строку, материализация позволяет выполнить подзапрос только один раз.
Для расширенного вывода EXPLAIN, текст, отображаемый последующим SHOW WARNINGS, включает materialize и materialized-subquery.

MySQL также может применить материализацию подзапроса к оператору UPDATE или DELETE с одной таблицей, который использует предикат подзапроса [NOT] IN или [NOT] EXISTS, при условии, что оператор не использует ORDER BY или LIMIT, и что материализация подзапроса разрешена подсказкой оптимизатора или параметром optimizer_switch.