Що показує EXPLAIN у MySQL
Команда EXPLAIN змушує оптимізатор MySQL показати план виконання запиту замість того, щоб його виконувати: які таблиці читаються, у якому порядку, через який індекс і скільки рядків сервер планує переглянути. Без плану оптимізація запиту перетворюється на вгадування — EXPLAIN перетворює її на перевірку конкретних цифр.
План будується для SELECT, а також для UPDATE і DELETE — це зручно перед зміною великої таблиці, щоб заздалегідь побачити, чи не піде запит у повне сканування.
Ключові стовпці плану: type, key, rows, Extra
З десятка стовпців EXPLAIN на практиці важливі чотири:
| Стовпець | Що означає |
|---|---|
| type | Спосіб доступу до таблиці: const, eq_ref, ref, range, index, ALL |
| key | Індекс, який реально обрав оптимізатор |
| rows | Оцінка кількості рядків для перегляду на цьому кроці |
| Extra | Позначки на кшталт Using filesort або Using temporary |
| key_len | Скільки байтів індексу реально бере участь у пошуку |
Значення ALL у стовпці type означає повне сканування таблиці без індексу. Для таблиці на пару тисяч рядків це не страшно, а для таблиці на 10 мільйонів рядків — прямий шлях до повільного запиту. Детально про те, як індекси впливають на type і key, написано у статті про оптимізацію індексів MySQL.
Чим EXPLAIN ANALYZE відрізняється від звичайного EXPLAIN
Звичайний EXPLAIN — це лише прогноз оптимізатора, запит не виконується. EXPLAIN ANALYZE, доступний з MySQL 8.0.18, реально виконує запит і додає до плану фактичний час кожного кроку та реальну кількість рядків. Це дозволяє порівняти прогноз із дійсністю і знайти крок, де оцінка оптимізатора розійшлася з реальністю в рази.
Мінус EXPLAIN ANALYZE — він виконує запит цілком, включно з важкими UPDATE. На проді такі перевірки запускають на копії бази або в транзакції з наступним ROLLBACK.
Як читати план на прикладі запиту з JOIN
Візьмемо типовий запит замовлень із join до клієнтів і подивимося на вивід порядково.
EXPLAIN SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 20;Якщо у рядку для таблиці orders стовпець type дорівнює ref, а key вказує на індекс за status, оптимізатор знайшов потрібні рядки швидко. Якщо ж type показує ALL, а в Extra видно Using where; Using filesort, запит сканує всю таблицю і досортовує результат у пам'яті або на диску — вірна ознака, що бракує складеного індексу за (status, created_at).
Часті проблеми, які видно в плані
Кілька позначок в Extra майже завжди вказують на вузьке місце:
Using filesort— сервер сортує рядки окремо, індекс під ORDER BY не підійшов.Using temporary— для GROUP BY чи DISTINCT створюється тимчасова таблиця, часто на диску.Using join buffer— для JOIN не знайшлося індексу, і MySQL перебирає рядки в пам'яті.- Велике значення rows на верхньому рівні плану при маленькому LIMIT у запиті.
Як запустити EXPLAIN і EXPLAIN ANALYZE
Синтаксис однаково простий для обох команд:
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE customer_id = 42;
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;Формат JSON показує більше деталей, включно з вартістю кожного кроку в умовних одиницях оптимізатора, і зручний для автоматичних перевірок у CI. Звичайний табличний формат швидше читається очима під час разової діагностики. Знайдені за планом повільні запити зручно збирати через slow query log і розбирати пачкою, а не по одному.
Чек-лист оптимізації запиту за планом
Перш ніж закрити тикет на повільний запит, пройдіться пунктами:
- type для кожної таблиці в плані не гірше range, окрім свідомо малих довідників.
- key показує реальний індекс, а не порожньо.
- В Extra немає Using filesort і Using temporary на гарячому шляху.
- rows на верхньому рівні відповідає очікуваному результату, а не всій таблиці.
- EXPLAIN ANALYZE підтвердив, що оцінка rows близька до фактичної.