Оптимизация запросов: EXPLAIN и EXPLAIN ANALYZE

Почему EXPLAIN — твой лучший друг в SQL? 🔍

Когда запрос работает медленно, EXPLAIN покажет, что на самом деле делает база данных под капотом. Это как рентген для SQL — без него мы работаем вслепую!

-- Простейший пример:
EXPLAIN SELECT * FROM users WHERE age > 30;

Вот что выдаст PostgreSQL (примерный вывод):

Seq Scan on users  (cost=0.00..15.50 rows=500 width=36)
  Filter: (age > 30)

Как читать вывод EXPLAIN? 📖

Ключевые элементы в выводе:

1. Тип операции (Seq Scan, Index Scan и т.д.)

2. Оценочная стоимость (cost=0.00..15.50)

• Первое число — стоимость получения первой строки
• Второе — стоимость получения всех строк

3. Ожидаемое количество строк (rows=500)

4. Размер строки в байтах (width=36)

EXPLAIN vs EXPLAIN ANALYZE: В чём разница? ⚔️

EXPLAIN показывает план, а EXPLAIN ANALYZE выполняет запрос и даёт реальную статистику:

-- Для анализа реального выполнения:
EXPLAIN ANALYZE SELECT * FROM orders WHERE total > 1000;

Пример вывода:

Seq Scan on orders  (cost=0.00..1200.50 rows=15000 width=48) (actual time=0.015..45.320 rows=14892 loops=1)
  Filter: (total > 1000)
  Rows Removed by Filter: 10708
Planning Time: 0.115 ms
Execution Time: 46.780 ms

Обрати внимание на новые метрики:

• actual time — реальное время выполнения
• rows — фактические строки
• Planning Time — время на построение плана
• Execution Time — общее время выполнения

Как находить узкие места? 🕵️

1. Полное сканирование таблицы (Seq Scan) — часто признак отсутствия индекса:

-- До оптимизации:
EXPLAIN ANALYZE SELECT * FROM products WHERE category_id = 5;

-- Добавляем индекс:
CREATE INDEX idx_products_category ON products(category_id);

2. Медленные сортировки (Sort):

-- Если видишь:
Sort  (cost=2500.50..2510.50 rows=4000 width=40)
  Sort Key: created_at DESC

-- Попробуй добавить индекс для сортировки:
CREATE INDEX idx_orders_created ON orders(created_at DESC);

3. Вложенные циклы (Nested Loop) могут быть медленными для больших таблиц

Продвинутые приёмы оптимизации 🚀

Анализ JOIN-ов:

EXPLAIN ANALYZE
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;

Ищем: - Какой тип соединения используется (Nested Loop, Hash Join, Merge Join) - Порядок соединения таблиц - Количество просматриваемых строк

Оптимизация подзапросов:

-- Часто лучше переписать так:
EXPLAIN ANALYZE SELECT * FROM products WHERE id IN (SELECT product_id FROM order_items);

-- В виде JOIN:
EXPLAIN ANALYZE 
SELECT p.* FROM products p
JOIN order_items oi ON p.id = oi.product_id;

Практическое задание 💻

1. Возьми свой медленный запрос (или используй пример выше)
2. Запусти EXPLAIN ANALYZE
3. Определи самую дорогую операцию
4. Попробуй улучшить (добавь индекс, измени запрос)
5. Сравни результаты

Пример для тренировки:

-- Исходный запрос:
EXPLAIN ANALYZE
SELECT u.* FROM users u
WHERE u.registration_date > '2023-01-01'
AND u.status = 'active'
ORDER BY u.last_activity DESC;

Важные нюансы ⚠️

1. Статистика имеет значение: ANALYZE table_name; обновляет статистику для планировщика
2. Параметры сервера влияют на план выполнения
3. EXPLAIN ANALYZE выполняет запрос — не используй на продакшене для тяжёлых запросов!
4. Разные СУБД — разные выводы: PostgreSQL, MySQL и Oracle показывают разную информацию