Що показує 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 близька до фактичної.
Часті питання
В чому принципова різниця між EXPLAIN і EXPLAIN ANALYZE?
Звичайний EXPLAIN показує лише прогноз оптимізатора і не виконує запит, тому безпечний для будь-якої таблиці. EXPLAIN ANALYZE реально запускає запит і додає до плану фактичний час кожного кроку та справжню кількість рядків, що дає змогу побачити, де оцінка оптимізатора розійшлася з дійсністю, проте вимагає обережності з важкими операціями зміни даних.
Наскільки безпечно запускати EXPLAIN ANALYZE на робочій базі даних?
EXPLAIN ANALYZE виконує запит по-справжньому, а не просто будує прогноз, тому для SELECT це зазвичай безпечно, а ось для UPDATE чи DELETE запуск на проді ризикований — реальні дані зміняться. Такі перевірки прийнято робити на копії бази або всередині транзакції з подальшим відкатом, аби глянути план без наслідків для даних.
Чому оптимізатор іноді обирає повне сканування (ALL), навіть якщо на потрібному стовпці є індекс?
Індекс існує — це не означає, що він підійде саме цьому запиту: оптимізатор дивиться на статистику таблиці і може вирішити, що переглянути всі рядки дешевше, ніж застосувати індекс низької вибірковості, тобто такий, де значення повторюється майже в кожному рядку. Часто причина ще й у застарілій статистиці або відсутності потрібного складеного індексу під конкретні умови WHERE і ORDER BY.
Якщо план показує Using filesort, чи обов'язково одразу додавати індекс?
Не завжди: позначка Using filesort означає, що сервер досортовує рядки окремо, але для невеликої вибірки це недорога операція і непомітна за часом відповіді. Додавати складений індекс під ORDER BY варто, коли це поєднується з великим rows на верхньому рівні плану або зі зростанням часу відповіді запиту в міру зростання таблиці.