Что такое EXPLAIN в SQL?

Что такое EXPLAIN в SQL?

EXPLAIN — это команда SQL, которая показывает план выполнения запроса. Вместо выполнения запроса, EXPLAIN выводит информацию о том, как база данных будет выполнять этот запрос: какие индексы использует, в каком порядке выполнять операции, сколько строк будет обработано.

Зачем нужен EXPLAIN

Это критический инструмент для оптимизации SQL запросов:

• Диагностика медленных запросов — понять, почему запрос работает долго
• Поиск узких мест — какая операция требует больше всего ресурсов
• Проверка эффективности индексов — использует ли БД нужный индекс
• Оптимизация — попробовать разные варианты и выбрать лучший

Синтаксис и примеры

-- Простой EXPLAIN
EXPLAIN SELECT * FROM users WHERE id = 5;

-- С дополнительной информацией (PostgreSQL)
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 5;

-- С форматом JSON (PostgreSQL)
EXPLAIN (FORMAT JSON, ANALYZE) 
    SELECT * FROM users WHERE id = 5;

Интерпретация EXPLAIN

Основные метрики

Seq Scan (Sequential Scan) — линейный поиск по всей таблице:

Seq Scan on users  (cost=0.00..35.50 rows=1000 width=100)
  Filter: (id = 5)

Это медленно для больших таблиц.

Index Scan — поиск через индекс:

Index Scan using users_pkey on users  (cost=0.29..8.31 rows=1 width=100)
  Index Cond: (id = 5)

Это быстро.

Nested Loop Join — объединение через вложенные циклы:

Nested Loop  (cost=0.29..16.48 rows=5 width=200)
  ->  Index Scan using users_pkey on users
  ->  Seq Scan on orders
        Filter: (user_id = users.id)

Hash Join — объединение через хеш-таблицу (обычно эффективнее):

Hash Join  (cost=100.00..200.00 rows=5000 width=200)
  Hash Cond: (users.id = orders.user_id)

Cost и Rows

• cost=X..Y —估计成本в единицах чтения диска (0..Y означает начальная стоимость..максимальная)
• rows=N — предполагаемое количество возвращаемых строк
• width=W — средняя ширина строки в байтах

Примеры оптимизации

Пример 1: Медленный запрос без индекса

EXPLAIN ANALYZE 
SELECT * FROM orders WHERE user_id = 100;

-- Результат:
Seq Scan on orders  (cost=0.00..1000.00 rows=50 width=100)
  Filter: (user_id = 100)
  Actual rows: 50
  Planning Time: 0.1 ms
  Execution Time: 50.2 ms

Проблема: Seq Scan (медленно). Решение: создать индекс

CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Теперь EXPLAIN показывает:
Index Scan using idx_orders_user_id on orders  (cost=0.29..8.31 rows=50 width=100)
  Index Cond: (user_id = 100)
  Actual rows: 50
  Execution Time: 0.8 ms

Пример 2: JOIN с Nested Loop

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) FROM users u 
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

Если видишь Nested Loop с Seq Scan на большой таблице — это плохо. Добавь индекс на user_id в orders.

EXPLAIN ANALYZE

Это самая полезная версия:

EXPLAIN ANALYZE SELECT ...

Отличие от обычного EXPLAIN:

• EXPLAIN — только план (не выполняет запрос)
• EXPLAIN ANALYZE — план + реальные данные выполнения

Это показывает, насколько точны оценки оптимизатора.

Что обращать внимание

1. Seq Scan на больших таблицах → нужен индекс
2. Index Scan с high cost → возможно, индекс не оптимален
3. Nested Loop на больших наборах → рассмотри Hash Join
4. Actual rows ≠ rows → статистика таблицы устарела (ANALYZE TABLE)
5. Sort, Aggregate с большим memory → может быть медленно

Инструменты визуализации

• explain.depesz.com — онлайн визуализация планов
• pgAdmin — встроенный EXPLAIN
• DataGrip — удобная визуализация

Различия между БД

Sинтаксис немного отличается:

• PostgreSQL: EXPLAIN ANALYZE, EXPLAIN (FORMAT JSON)
• MySQL: EXPLAIN, EXPLAIN FORMAT=JSON
• SQLite: EXPLAIN QUERY PLAN

EXPLAIN — это окно в то, как БД "думает" о твоём запросе. Это одно из самых мощных средств оптимизации.