# EXPLAIN і EXPLAIN ANALYZE: як читати план запиту

> Розбираємо стовпці EXPLAIN у MySQL: type, key, rows, Extra. Відмінність від EXPLAIN ANALYZE і ознаки повільного запиту в плані виконання.

## Що показує 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](https://www.zevshost.net/uk/kb/mysql-indeksy-optimizatsiya/).

## Чим 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](https://www.zevshost.net/uk/kb/mysql-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 на верхньому рівні плану або зі зростанням часу відповіді запиту в міру зростання таблиці.

---

Джерело: https://www.zevshost.net/uk/kb/mysql-explain-plan-zaprosa/