Оптимизация JOIN запросов

JOIN — одна из самых частых операций в SQL, и одновременно — источник самых дорогих запросов. Понимание алгоритмов JOIN и стратегии индексирования критически важно для производительности.

#Nested Loop Join: алгоритм по умолчанию

MySQL использует Nested Loop Join (вложенные циклы):

SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'Russia';

Алгоритм:

for user in users WHERE country = 'Russia':    # Внешняя таблица (driver)
    for order in orders WHERE user_id = user.id:  # Внутренняя таблица (driven)
        output(user.name, order.total)

Для каждой строки из внешней таблицы MySQL ищет совпадения во внутренней. Ключевой вопрос: есть ли индекс на столбце JOIN внутренней таблицы?

С индексом на orders.user_id:

Для каждой строки из users — быстрый lookup по индексу (ref)
100 строк из users × 1 lookup = 100 операций

Без индекса на orders.user_id:

Для каждой строки из users — full table scan orders
100 строк из users × 1 000 000 строк orders = 100 000 000 операций

Разница — порядки величины.

#EXPLAIN для JOIN

EXPLAIN показывает порядок и тип доступа для каждой таблицы:

EXPLAIN SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.country = 'Russia';

+----+-------+-------+------+---------------+-----------+---------+-----------+------+-------+
| id | table | type  | key  | ref           | rows      | Extra   |
+----+-------+------+---------------+-----------+---------+-----------+------+-------+
|  1 | u     | ref  | idx_country | const     |  100 | Using where |
|  1 | o     | ref  | idx_user_id | u.id      |    5 | NULL        |
+----+-------+------+---------------+-----------+---------+-----------+------+-------+

Порядок таблиц в EXPLAIN — это порядок выполнения. MySQL читает users первой (driver), затем orders (driven). Для orders: type: ref — используется индекс idx_user_id, ref: u.id — lookup по значению из users.

Критическая проблема: если для orders вы видите type: ALL — нет индекса на user_id, и для каждой строки из users сканируется вся таблица orders.

#Block Nested Loop (BNL)

Когда нет индекса на столбце JOIN, MySQL использует Block Nested Loop:

-- Нет индекса на orders.user_id
SELECT u.name, o.total
FROM users u JOIN orders o ON u.id = o.user_id;

Алгоритм BNL:

Читает пакет строк из users в join_buffer (размер join_buffer_size)
За ОДИН проход по orders проверяет совпадения со ВСЕМИ строками из буфера
Повторяет, пока все строки users не обработаны

Это уменьшает количество сканирований orders, но всё ещё значительно медленнее JOIN с индексом. BNL отмечается в EXPLAIN как Extra: Using join buffer (Block Nested Loop).

-- Размер буфера на JOIN (по умолчанию 256 КБ)
SHOW VARIABLES LIKE 'join_buffer_size';
-- Увеличение помогает при BNL, но не заменяет индекс
SET SESSION join_buffer_size = 4 * 1024 * 1024; -- 4 МБ

Больший буфер позволяет обработать больше строк за один проход, но буфер выделяется на каждый JOIN в запросе — множественные JOIN с большим буфером потребляют много памяти.

#Batched Key Access (BKA)

BKA — оптимизация Nested Loop, использующая Multi-Range Read (MRR):

-- Включение BKA
SET optimizer_switch = 'mrr=on,mrr_cost_based=off,batched_key_access=on';

Алгоритм BKA:

Собирает ключи lookup из внешней таблицы в буфер
Сортирует ключи по порядку в индексе внутренней таблицы
Выполняет пакетный lookup — последовательный доступ к данным

Сортировка ключей обеспечивает последовательный (а не случайный) доступ к данным внутренней таблицы. Это значительно улучшает локальность чтения и снижает I/O. Особенно эффективно для больших JOIN.

#Порядок таблиц в JOIN

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

-- Запрос
SELECT * FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN products p ON o.product_id = p.id
WHERE c.country = 'Russia';

Оптимизатор может выбрать порядок: customers → orders → products или orders → customers → products. Он оценивает:

Селективность условий (country = 'Russia' фильтрует customers)
Размер таблиц
Доступность индексов

Если оценка неверна (устаревшая статистика), порядок может быть suboptimal. Изменить порядок:

-- STRAIGHT_JOIN — заставляет MySQL следовать указанному порядку
SELECT * FROM customers c
STRAIGHT_JOIN orders o ON c.id = o.customer_id
STRAIGHT_JOIN products p ON o.product_id = p.id
WHERE c.country = 'Russia';

-- Хинт в MySQL 8.0
SELECT /*+ JOIN_ORDER(c, o, p) */ * 
FROM customers c
JOIN orders o ON c.id = o.customer_id
JOIN products p ON o.product_id = p.id;

#Практические правила

Индекс на столбце JOIN внутренней таблицы — обязателен. Без него Nested Loop превращается в катастрофу производительности.

Фильтруйте внешнюю таблицу. WHERE c.country = 'Russia' сокращает driver-таблицу до нескольких строк — меньше lookup'ов.

Проверяйте EXPLAIN для каждого JOIN. type: ALL для любой таблицы в JOIN — красный флаг.

Рассмотрите BKA для больших JOIN без идеальных индексов. Он не заменяет индекс, но может значительно улучшить ситуацию.

+----+-------+-------+------+---------------+-----------+---------+-----------+------+-------+ | id | table | type | key | ref | rows | Extra | +----+-------+------+---------------+-----------+---------+-----------+------+-------+ | 1 | u | ref | idx_country | const | 100 | Using where | | 1 | o | ref | idx_user_id | u.id | 5 | NULL | +----+-------+------+---------------+-----------+---------+-----------+------+-------+