Materialization, derived tables, оптимизация EXISTS vs IN, коррелированные подзапросы
Подзапросы — мощный инструмент, но они часто становятся источником проблем производительности. MySQL 5.6+ значительно улучшил оптимизацию подзапросов, но есть нюансы.
Некоррелированный подзапрос — не зависит от внешнего запроса, выполняется один раз:
-- Подзапрос выполняется ОДИН раз
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 — ключевая оптимизация некоррелированных подзапросов:
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 выполнил подзапрос один раз, сохранил результат во временной таблице и использует её как обычную таблицу. Это значительно эффективнее выполнения подзапроса для каждой строки.
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 стратегии:
В EXPLAIN semi-join отображается как часть основного плана, а не как отдельный подзапрос.
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 предпочтительнее:
Когда IN предпочтительнее:
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 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 невозможен):
<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 без индекса — это коррелированный подзапрос без оптимизации.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.