Что такое план запроса в SQL?

План запроса (Query Plan) в SQL: полное руководство

План запроса — это детальная инструкция для оптимизатора базы данных, описывающая как именно будет выполняться ваш SQL запрос. Это критичное понимание для Java разработчика, работающего с БД.

Что такое план запроса?

Когда вы пишете SQL запрос, база данных не просто выполняет его напрямую. Вместо этого:

1. Парсер проверяет синтаксис
2. Оптимизатор выбирает лучший способ выполнения
3. Компилятор преобразует в план
4. Исполнитель следует плану

План запроса — это результат шага 2 (оптимизатор).

Как посмотреть план запроса

PostgreSQL:

EXPLAIN SELECT * FROM users WHERE id = 1;

-- Результат:
-- Seq Scan on users (cost=0.00..35.50 rows=1 width=500)
--   Filter: (id = 1)

-- С деталями:
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

-- Результат:
-- Seq Scan on users (cost=0.00..35.50 rows=1 width=500) (actual time=0.020..0.025 rows=1)
--   Filter: (id = 1)

-- JSON формат (лучше для анализа):
EXPLAIN (FORMAT JSON) SELECT * FROM users WHERE id = 1;

MySQL:

EXPLAIN SELECT * FROM users WHERE id = 1;

-- Результат в таблице:
-- id | select_type | table | type  | possible_keys | key | rows | Extra
-- 1  | SIMPLE      | users | const | PRIMARY       | PRI | 1    | NULL

-- Детальный анализ:
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;

Java: как получить план в коде

public class QueryPlanAnalyzer {
    private DataSource dataSource;
    
    public void analyzeQueryPlan(String sql) {
        try (Connection conn = dataSource.getConnection();
             Statement stmt = conn.createStatement()) {
            
            // PostgreSQL
            ResultSet rs = stmt.executeQuery("EXPLAIN ANALYZE " + sql);
            while (rs.next()) {
                System.out.println(rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Основные компоненты плана запроса

1. Способ доступа к данным (Access Method)

-- Seq Scan (Sequential Scan) — полное сканирование таблицы
EXPLAIN SELECT * FROM users WHERE status = 'active';
-- Seq Scan on users (cost=0.00..35.50 rows=1000)
--   Filter: (status = 'active')

-- Index Scan — использование индекса
EXPLAIN SELECT * FROM users WHERE id = 1;
-- Index Scan using users_pkey on users (cost=0.29..8.30 rows=1)
--   Index Cond: (id = 1)

-- Bitmap Index Scan — эффективно для больших результатов
EXPLAIN SELECT * FROM users WHERE created_at > '2024-01-01';
-- Bitmap Index Scan on users_created_at_idx (cost=...)
-- Bitmap Heap Scan on users (cost=...)

2. Cost (стоимость операции)

Стоимость измеряется в "page reads":

EXPLAIN SELECT * FROM users WHERE id = 1;
-- Index Scan using users_pkey on users (cost=0.29..8.30 rows=1)
--  ^^^ Эти цифры: 0.29 = стоимость запуска
--      8.30 = общая стоимость

• Startup cost (0.29): время до первой строки
• Total cost (8.30): время для получения всех строк
• Меньше стоимость = лучше

3. Rows — прогноз количества строк

EXPLAIN SELECT * FROM users WHERE id = 1;
-- Index Scan using users_pkey on users (cost=0.29..8.30 rows=1)
--                                            ^^^^^^ Оптимизатор ожидает 1 строку

EXPLAIN SELECT * FROM users WHERE status = 'active';
-- Seq Scan on users (cost=0.00..35.50 rows=1000)
--                                        ^^^^ Может быть неточно!

Если actual rows >> estimated rows — нужна статистика.

4. Width — размер каждой строки в байтах

EXPLAIN SELECT * FROM users;
-- Seq Scan on users (cost=0.00..35.50 rows=1000 width=500)
--                                                   ^^^^^ 500 байт на строку

Типы операций в плане

-- Sequential Scan: полное сканирование
EXPLAIN SELECT * FROM users WHERE status = 'inactive';
-- Seq Scan on users (cost=0.00..35.50 rows=100)

-- Hash Join: соединение через хеш-таблицу (для больших наборов)
EXPLAIN SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id;
-- Hash Join (cost=...)
--   Hash Cond: (o.user_id = u.id)
--   -> Seq Scan on orders o
--   -> Hash
--      -> Seq Scan on users u

-- Nested Loop: вложенные циклы (для маленьких наборов)
EXPLAIN SELECT * FROM users u 
WHERE id = 1 AND status IN (SELECT status FROM statuses WHERE active);
-- Nested Loop (cost=...)
--   -> Index Scan on users u
--   -> Seq Scan on statuses (filtered)

-- Sort: сортировка
EXPLAIN SELECT * FROM users ORDER BY created_at DESC;
-- Sort (cost=100.00..115.00)
--   Sort Key: created_at DESC
--   -> Seq Scan on users

-- Group Aggregate: группировка
EXPLAIN SELECT status, COUNT(*) FROM users GROUP BY status;
-- HashAggregate (cost=...)
--   Group Key: status
--   -> Seq Scan on users

Практические примеры для Java разработчика

Пример 1: Плохой запрос (без индекса)

public class UserService {
    public List<User> findActiveUsers() {
        // SELECT * FROM users WHERE status = 'active';
        // ❌ Seq Scan on users (стоимость 35.50) — медленно!
        return jdbcTemplate.query(
            "SELECT * FROM users WHERE status = ?",
            (rs, rowNum) -> mapUser(rs),
            "active"
        );
    }
}

-- План запроса:
EXPLAIN SELECT * FROM users WHERE status = 'active';
-- Seq Scan on users (cost=0.00..35.50 rows=1000)
--   Filter: (status = 'active')
-- Полное сканирование всей таблицы! Если таблица большая, это медленно.

Пример 2: Хороший запрос (с индексом)

-- Создать индекс
CREATE INDEX idx_users_status ON users(status);

-- План запроса:
EXPLAIN SELECT * FROM users WHERE status = 'active';
-- Index Scan using idx_users_status on users (cost=0.29..8.30 rows=1000)
-- ✅ Намного быстрее! Использует индекс.

Пример 3: Сложный запрос с JOIN

public class OrderService {
    public List<OrderDTO> getOrdersWithUsers() {
        // SELECT u.id, u.name, o.id, o.total
        // FROM users u
        // JOIN orders o ON u.id = o.user_id
        // WHERE u.created_at > '2024-01-01'
        // ORDER BY o.created_at DESC;
        return jdbcTemplate.query(
            "SELECT u.id, u.name, o.id, o.total " +
            "FROM users u JOIN orders o ON u.id = o.user_id " +
            "WHERE u.created_at > ? " +
            "ORDER BY o.created_at DESC",
            (rs, rowNum) -> mapOrderDTO(rs),
            "2024-01-01"
        );
    }
}

-- План запроса:
EXPLAIN (FORMAT JSON) SELECT u.id, u.name, o.id, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
ORDER BY o.created_at DESC;

-- Результат (упрощённо):
-- Sort
--   -> Hash Join
--      Hash Cond: (u.id = o.user_id)
--      -> Index Scan using idx_users_created_at
--      -> Seq Scan on orders o

Как оптимизировать по плану запроса

1. Если видите Seq Scan на большой таблице — создать индекс

// ❌ Медленно
List<User> active = users.findByStatus("active"); // Seq Scan

// ✅ Быстро (нужен индекс)
CREATE INDEX idx_users_status ON users(status);
List<User> active = users.findByStatus("active"); // Index Scan

2. Если rows (прогноз) сильно отличается от actual rows — обновить статистику

-- PostgreSQL
ANALYZE users;

-- MySQL
ANALYZE TABLE users;

3. Если видите Sort на большом наборе — использовать индекс

-- ❌ Медленно: отсортирует весь результат
EXPLAIN SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
-- Sort (cost=100..115)
--   -> Seq Scan

-- ✅ Быстро: возьмёт первые 10 из индекса
CREATE INDEX idx_users_created_at ON users(created_at DESC);
EXPLAIN SELECT * FROM users ORDER BY created_at DESC LIMIT 10;
-- Limit (cost=0.29..1.50)
--   -> Index Scan Backward using idx_users_created_at

4. Если видите Nested Loop на больших таблицах — может помочь Hash Join

// Убедиться, что индексы хорошие
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_id ON users(id);

Метрики плана запроса

Cost      — CPU и I/O стоимость
Rows      — прогноз количества строк
Actual    — реальное количество строк
Loops     — сколько раз выполнено
Width     — размер строки в байтах
Buffers   — сколько блоков прочитано

Вывод

План запроса — это:

• Инструкция для БД как выполнять запрос
• Показывает, использует ли индексы
• Помогает выявить узкие места
• Критичен для оптимизации производительности

Для Java разработчика:

• Всегда смотрите EXPLAIN перед отправкой в production
• Проверяйте, используются ли индексы
• Если Seq Scan на большой таблице — это проблема
• Если rows прогноз очень отличается от actual — обновите статистику

Понимание планов запроса отличает хорошего разработчика от обычного.