Зачем нужен EXPLAIN в SQL?

Зачем нужен EXPLAIN в SQL

EXPLAIN — это инструмент SQL для анализа плана выполнения запроса. Он показывает, как база данных будет выполнять запрос, какие индексы использует, сколько времени это займет.

Основная задача EXPLAIN

EXPLAIN помогает:

1. Найти медленные запросы
2. Понять, почему запрос медленный
3. Оптимизировать использование индексов
4. Предсказать затраты на операцию

Синтаксис

-- Стандартный EXPLAIN
EXPLAIN SELECT * FROM users WHERE age > 30;

-- С анализом (фактическое выполнение)
EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

-- PostgreSQL с более подробной информацией
EXPLAIN (FORMAT JSON, ANALYZE) SELECT * FROM users WHERE age > 30;

-- MySQL
EXPLAIN SELECT * FROM users WHERE age > 30;
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age > 30;

Что показывает EXPLAIN

EXPLAIN SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.age > 30
GROUP BY u.id;

-- Результат (PostgreSQL):
-- Seq Scan on users u  (cost=0.00..35.00 rows=500)
--   Filter: (age > 30)
--   -> Hash
--       -> Seq Scan on orders o  (cost=0.00..100.00 rows=1000)

Как читать:

• Seq Scan — последовательное чтение всей таблицы (медленно)
• cost=0.00..35.00 — стоимость от начала до конца
• rows=500 — ожидаемое количество строк
• Filter — условие WHERE

Основные операции в EXPLAIN

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

EXPLAIN SELECT * FROM users WHERE name = 'Alice';
-- Seq Scan on users  (если нет индекса на name)

Index Scan — чтение через индекс (быстро):

EXPLAIN SELECT * FROM users WHERE id = 1;
-- Index Scan using users_pkey on users

Hash Join — объединение через хеш-таблицу:

EXPLAIN SELECT * FROM users u, orders o WHERE u.id = o.user_id;
-- Hash Join

Nested Loop — циклическое объединение (медленное для больших данных):

EXPLAIN SELECT * FROM users u, orders o WHERE u.id = o.user_id;
-- Nested Loop

Практические примеры

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

-- Создаем таблицу
CREATE TABLE products (id INT, name VARCHAR(100), price DECIMAL);
INSERT INTO products VALUES (1, 'Laptop', 999.99), (2, 'Phone', 499.99);

-- Плохой запрос (без индекса на name)
EXPLAIN SELECT * FROM products WHERE name = 'Laptop';
-- Seq Scan on products  (cost=0.00..35.00 rows=1)
--   Filter: (name = 'Laptop')  -- Медленно, проверяет все строки

-- Создаем индекс
CREATE INDEX idx_products_name ON products(name);

-- Теперь быстро
EXPLAIN SELECT * FROM products WHERE name = 'Laptop';
-- Index Scan using idx_products_name on products
--   Index Cond: (name = 'Laptop')  -- Быстро через индекс

Пример 2: EXPLAIN ANALYZE (с фактическим выполнением)

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

-- Результат:
-- Seq Scan on users  (cost=0.00..35.00 rows=500) (actual time=0.1..2.5 rows=450)
--   Filter: (age > 30)
--   Rows Removed by Filter: 50

-- Интерпретация:
-- cost=0.00..35.00 — оценка планировщика
-- actual time=0.1..2.5 — реальное время выполнения
-- rows=450 — оценка количества строк
-- Rows Removed by Filter: 50 — фактическое количество отфильтрованных строк

Пример 3: Анализ JOIN

EXPLAIN ANALYZE
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.age > 30;

-- Результат:
-- Hash Join  (cost=100..500 rows=400) (actual time=1.2..5.3 rows=380)
--   Hash Cond: (u.id = o.user_id)
--   -> Seq Scan on users u  (cost=0..35 rows=500) (actual time=0..0.1 rows=450)
--        Filter: (age > 30)
--   -> Hash  (cost=50..80 rows=1000) (actual time=0.5..1.0 rows=1000)
--       -> Seq Scan on orders o  (cost=0..40 rows=1000) (actual time=0..0.05 rows=1000)

Как оптимизировать запросы с EXPLAIN

Оптимизация 1: Добавить индекс

# До оптимизации (Seq Scan)
from sqlalchemy import text
session.execute(text("EXPLAIN SELECT * FROM users WHERE email = :email"), 
               {"email": "user@example.com"})
# Seq Scan on users (медленно)

# После оптимизации
session.execute(text("CREATE INDEX idx_users_email ON users(email)"))
session.execute(text("EXPLAIN SELECT * FROM users WHERE email = :email"), 
               {"email": "user@example.com"})
# Index Scan (быстро)

Оптимизация 2: Переписать запрос

-- Медленно: поиск в LIKE
EXPLAIN SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Seq Scan (проверяет все строки)

-- Быстро: разделить домен
EXPLAIN SELECT * FROM users WHERE email LIKE '%@gmail.com';
-- Index Scan (если есть индекс на email)

Оптимизация 3: Использовать нужные колонки

from sqlalchemy import text

# Плохо: SELECT * (лишние колонки)
session.execute(text("EXPLAIN SELECT * FROM users WHERE id = :id"),
               {"id": 1})
# Seq Scan (читает все колонки)

# Хорошо: выбрать только нужные колонки
session.execute(text("EXPLAIN SELECT id, name FROM users WHERE id = :id"),
               {"id": 1})
# Index Scan + Index Only Scan (быстрее)

Интерпретация стоимости

Стоимость в EXPLAIN:

• cost=0..1000 — очень дешево (индекс, малая таблица)
• cost=1000..10000 — дорого (Seq Scan большой таблицы)
• cost=10000+ — очень дорого (несколько больших сканов)

-- Дешево (индекс)
EXPLAIN SELECT * FROM users WHERE id = 1;
-- Index Scan (cost=0.0..8.27)

-- Дорого (полное сканирование)
EXPLAIN SELECT * FROM users WHERE name LIKE '%John%';
-- Seq Scan (cost=0.0..35000.0)  -- Очень дорого!

EXPLAIN в Python + SQLAlchemy

from sqlalchemy import text, select
from sqlalchemy.orm import Session

def analyze_query(session: Session, query_text: str):
    """Анализировать план выполнения запроса."""
    result = session.execute(text(f"EXPLAIN ANALYZE {query_text}"))
    for row in result:
        print(row[0])

# Использование
query = "SELECT * FROM users WHERE age > 30"
analyze_query(session, query)

# Или с параметрами
def analyze_complex_query(session: Session, user_id: int):
    query = """
    EXPLAIN ANALYZE
    SELECT u.name, COUNT(o.id) as order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id = :user_id
    GROUP BY u.id
    """
    result = session.execute(text(query), {"user_id": user_id})
    for row in result:
        print(row[0])

analyze_complex_query(session, user_id=123)

Чек-лист оптимизации

1. Запустить EXPLAIN ANALYZE
2. Найти операции с большой стоимостью (Seq Scan)
3. Проверить, есть ли индекс на колонке в WHERE
4. Добавить индекс, если его нет
5. Переписать запрос, если нужно
6. Снова запустить EXPLAIN для проверки

Заключение

EXPLAIN — это essential инструмент для:

• Диагностики медленных запросов
• Планирования индексов
• Оптимизации производительности
• Понимания плана выполнения

Регулярный анализ EXPLAIN помогает удерживать базу данных в хорошем состоянии и предотвращать проблемы с производительностью.