Чтение и интерпретация EXPLAIN, EXPLAIN ANALYZE, типы JOIN в плане выполнения
EXPLAIN — главный инструмент для понимания того, КАК MySQL выполняет запрос. Без умения читать планы выполнения оптимизация индексов — это гадание.
Команда EXPLAIN показывает план выполнения запроса БЕЗ его фактического выполнения:
EXPLAIN SELECT * FROM users WHERE email = 'test@test.com';Результат — таблица с колонками:
| Колонка | Что показывает |
|---|---|
id | Номер шага выполнения (для подзапросов > 1) |
select_type | Тип SELECT: SIMPLE, PRIMARY, SUBQUERY, DERIVED |
table | Таблица, к которой относится шаг |
type | Тип доступа (от худшего к лучшему) |
possible_keys | Индексы, которые МОГЛИ бы использоваться |
key | Индекс, который РЕАЛЬНО используется |
key_len | Длина используемой части индекса в байтах |
ref | Какие столбцы или константы сравниваются с индексом |
rows | Оценка количества прочитанных строк |
Extra | Дополнительная информация |
Иерархия типов доступа (type) — ключевой показатель эффективности:
ALL → index → range → ref → eq_ref → const → system
ALL (full table scan) — читает КАЖДУЮ строку таблицы:
EXPLAIN SELECT * FROM orders WHERE status = 'pending';
-- type: ALL, rows: 5000000 — читает 5 млн строкindex (full index scan) — читает весь индекс, но не таблицу. Быстрее ALL (индекс меньше), но всё ещё плохо для больших таблиц.
range — поиск по диапазону индекса:
EXPLAIN SELECT * FROM orders WHERE created_at > '2024-01-01';
-- type: range — использует индекс для диапазонного поискаref — поиск по некластерному индексу через равенство:
EXPLAIN SELECT * FROM users WHERE email = 'test@test.com';
-- type: ref — один или несколько lookup по индексуeq_ref — UNIQUE или PRIMARY KEY lookup в JOIN:
EXPLAIN SELECT * FROM orders JOIN users ON orders.user_id = users.id;
-- Для users: type: eq_ref — ровно одна строка на lookupconst — lookup по UNIQUE/PK с константой (максимум одна строка):
EXPLAIN SELECT * FROM users WHERE id = 42;
-- type: const — одна строка, максимально быстрыйUsing index — покрывающий индекс:
EXPLAIN SELECT user_id FROM orders WHERE user_id = 42;
-- Extra: Using index — все данные в индексеUsing where — фильтрация после чтения строк:
Extra: Using where — MySQL читает строки и фильтрует их на серверном уровне
Using filesort — сортировка без индекса (не обязательно на диске):
Extra: Using filesort — MySQL сортирует данные отдельно, без индекса
Using temporary — создана временная таблица:
Extra: Using temporary — для GROUP BY, DISTINCT или подзапросов
Using index condition — Index Condition Pushdown:
Extra: Using index condition — фильтр на уровне storage engine
EXPLAIN ANALYZE (MySQL 8.0.18+) фактически выполняет запрос и показывает реальные метрики:
EXPLAIN ANALYZE
SELECT o.id, o.total
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.email = 'test@test.com'
ORDER BY o.created_at DESC
LIMIT 10;Результат показывает:
-> Limit: 10 row(s) (actual time=12.543..12.550 rows=10 loops=1)
-> Sort: o.created_at DESC, take top 10 row(s) (actual time=12.540..12.545 rows=10 loops=1)
-> Nested loop inner join (cost=4.55 rows=5) (actual time=0.050..12.400 rows=120 loops=1)
-> Index lookup on u using idx_email (email='test@test.com') (cost=0.25 rows=1) (actual time=0.045..0.050 rows=1 loops=1)
-> Index lookup on o using idx_user_id (user_id=u.id) (cost=4.30 rows=5) (actual time=0.030..12.200 rows=120 loops=1)
Формат времени: actual time=startup..total
Это критически важно, когда оценки оптимизатора (rows) расходятся с реальностью.
Типичный процесс оптимизации:
-- Шаг 1: EXPLAIN для плана
EXPLAIN SELECT * FROM orders WHERE user_id = 5 AND status = 'pending';
-- Шаг 2: EXPLAIN ANALYZE для реальных метрик
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 5 AND status = 'pending';
-- Шаг 3: Анализ
-- rows слишком большой? → нужен индекс
-- Using filesort? → добавить столбец ORDER BY в индекс
-- Using temporary? → пересмотреть GROUP BY или подзапрос
-- Шаг 4: Создать индекс
CREATE INDEX idx_user_status ON orders(user_id, status);
-- Шаг 5: Проверить результат
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 5 AND status = 'pending';
-- Сравниваем время до/послеtype: ALL для большой таблицы — нет подходящего индекса или условие не использует индекс (функция на столбце, неявное приведение типов).
rows: миллионы для простого запроса — статистика устарела, запустите ANALYZE TABLE.
Using temporary; Using filesort — запрос делает слишком много работы. Рассмотрите covering index или переписывание запроса.
Разные планы для EXPLAIN и реального выполнения — оценки оптимизатора неверны из-за устаревшей статистики или skewed data distribution. EXPLAIN ANALYZE покажет реальность.
Далее: Селективность индексов и статистика