Оптимизация подзапросов и производных таблиц

Подзапросы — мощный инструмент, но они часто становятся источником проблем производительности. MySQL 5.6+ значительно улучшил оптимизацию подзапросов, но есть нюансы.

#Коррелированные vs некоррелированные подзапросы

Некоррелированный подзапрос — не зависит от внешнего запроса, выполняется один раз:

-- Подзапрос выполняется ОДИН раз
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE total > 1000
);

MySQL может применить materialization: выполнить подзапрос один раз, сохранить результат во временной таблице и использовать для основного запроса.

Коррелированный подзапрос — зависит от внешнего запроса, выполняется для каждой строки:

-- Подзапрос выполняется для КАЖДОЙ строки orders
SELECT * FROM orders o
WHERE amount > (
    SELECT AVG(amount) FROM orders WHERE user_id = o.user_id
);

Для каждой строки orders MySQL выполняет подзапрос с o.user_id. Если orders содержит 1 млн строк — 1 млн выполнений подзапроса. Это дорого.

#Materialization

Materialization — ключевая оптимизация некоррелированных подзапросов:

EXPLAIN SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

id | select_type        | table  | type
 1 | PRIMARY            | users  | ALL
 2 | MATERIALIZED       | orders | range

MATERIALIZED означает: MySQL выполнил подзапрос один раз, сохранил результат во временной таблице и использует её как обычную таблицу. Это значительно эффективнее выполнения подзапроса для каждой строки.

#Semi-join оптимизация

MySQL 5.6+ преобразует многие IN-подзапросы в semi-join — аналог JOIN, но без дублирования строк:

-- IN-подзапрос может быть преобразован в semi-join
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'pending');

Semi-join стратегии:

Table Pullout — подзапрос вытаскивается в основной запрос
Duplicate Weedout — удаление дубликатов после JOIN
First Match — остановка после первого совпадения
LooseScan — сканирование только уникальных значений

В EXPLAIN semi-join отображается как часть основного плана, а не как отдельный подзапрос.

#EXISTS vs IN

EXISTS проверяет существование хотя бы одной строки и останавливается после первого совпадения:

-- EXISTS: остановка после первого совпадения
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000
);

IN строит весь набор результатов подзапроса:

-- IN: строит весь набор
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

Когда EXISTS предпочтительнее:

Подзапрос может вернуть много строк, а нужно только проверить наличие
Коррелированный подзапрос (EXISTS может быть эффективнее для каждой строки)
Есть риск NULL значений (IN с NULL ведёт себя неочевидно)

Когда IN предпочтительнее:

Подзапрос возвращает мало строк (materialization эффективна)
MySQL может применить semi-join оптимизацию

#Опасность NOT IN с NULL

NOT IN с подзапросом, содержащим NULL, всегда возвращает 0 строк:

-- Если подзапрос возвращает хотя бы один NULL — результат всегда пуст
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);
-- orders.user_id допускает NULL → запрос вернёт 0 строк!

Почему? value NOT IN (1, 2, NULL) = value != 1 AND value != 2 AND value != NULL. Сравнение с NULL даёт UNKNOWN, и весь результат — UNKNOWN.

Решения:

-- Вариант 1: NOT EXISTS (безопасен для NULL)
SELECT * FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Вариант 2: IS NOT NULL в подзапросе
SELECT * FROM users
WHERE id NOT IN (
    SELECT user_id FROM orders WHERE user_id IS NOT NULL
);

#Derived Tables

Derived table — подзапрос в FROM:

SELECT t.user_id, t.cnt
FROM (
    SELECT user_id, COUNT(*) AS cnt
    FROM orders GROUP BY user_id
) AS t
WHERE t.cnt > 5;

MySQL 8.0 оптимизирует derived tables двумя способами:

Derived Merge — подзапрос 'встраивается' в основной запрос (как view):

-- После merge эквивалентно:
SELECT user_id, COUNT(*) AS cnt
FROM orders GROUP BY user_id HAVING cnt > 5;

Materialization — создаётся временная таблица (если merge невозможен):

При наличии GROUP BY, LIMIT, агрегатов — merge невозможен
В EXPLAIN: <derivedN> в столбце table

Управление через хинты:

SELECT /*+ DERIVED_MERGE(t) */ * FROM (...) AS t;
SELECT /*+ NO_DERIVED_MERGE(t) */ * FROM (...) AS t;

#Практические рекомендации

Избегайте коррелированных подзапросов в больших таблицах. Замените на JOIN или оконные функции (MySQL 8.0+).

Предпочитайте EXISTS для проверки существования, IN — для получения конкретных значений.

Всегда добавляйте IS NOT NULL при использовании NOT IN с подзапросом.

Проверяйте EXPLAIN — подзапрос должен быть MATERIALIZED или DEPENDENT SUBQUERY с индексом. Если DEPENDENT SUBQUERY без индекса — это коррелированный подзапрос без оптимизации.