Стратегии индексирования для 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 без идеальных индексов. Он не заменяет индекс, но может значительно улучшить ситуацию.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.