Стратегии индексирования для JOIN, Nested Loop, Block Nested Loop, Batched Key Access
JOIN — одна из самых частых операций в SQL, и одновременно — источник самых дорогих запросов. Понимание алгоритмов 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)Без индекса на orders.user_id:
users — full table scan ordersРазница — порядки величины.
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.
Когда нет индекса на столбце 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 с большим буфером потребляют много памяти.
BKA — оптимизация Nested Loop, использующая Multi-Range Read (MRR):
-- Включение BKA
SET optimizer_switch = 'mrr=on,mrr_cost_based=off,batched_key_access=on';Алгоритм BKA:
Сортировка ключей обеспечивает последовательный (а не случайный) доступ к данным внутренней таблицы. Это значительно улучшает локальность чтения и снижает I/O. Особенно эффективно для больших 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 без идеальных индексов. Он не заменяет индекс, но может значительно улучшить ситуацию.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.
Далее: Оптимизация подзапросов и производных таблиц