Какие параметры возвращает EXPLAIN

Какие параметры возвращает EXPLAIN

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

Что такое EXPLAIN

EXPLAIN выводит план выполнения запроса БЕЗ его реального выполнения. Это показывает пошаговые операции, которые база данных выполнит.

-- PostgreSQL
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- MySQL
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- SQL Server
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT * FROM users WHERE email = 'john@example.com';

Параметры EXPLAIN в PostgreSQL

Этот наиболее подробный вариант:

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;

Основные параметры в PostgreSQL:

1. ANALYZE (по умолчанию FALSE)

Включает реальное выполнение и сбор фактических статистик:

EXPLAIN ANALYZE  -- Выполняет запрос и показывает фактические числа
SELECT * FROM large_table WHERE id > 1000000;

Вывод:

Seq Scan on large_table  (cost=0.00..35000.00 rows=500000)
                         (actual time=0.1..500.2 rows=500000)

Здесь важно:

• cost — предполагаемые единицы стоимости (0.00..35000.00)
• rows — предполагаемое количество строк (500000)
• actual time — фактическое время выполнения (0.1..500.2 мс)
• actual rows — фактическое количество строк (500000)

2. BUFFERS

Показывает использование памяти (буфер-кеша):

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 123;

Вывод содержит:

Shared Hit Blocks: количество прочитанных блоков из памяти
Shared Read Blocks: количество блоков, прочитанных с диска
Local Hit: локальные блоки из памяти сессии
Local Read: локальные блоки с диска
Local Written: блоки, записанные на диск

Это помогает понять, использует ли запрос индексы или полностью сканирует таблицу.

3. VERBOSE

Показывает дополнительную информацию:

EXPLAIN (ANALYZE, VERBOSE)
SELECT * FROM users WHERE age > 25;

Дополнительные поля:

• Output: какие столбцы выводит этот узел
• Filter: какие условия применяются
• Rows Removed by Filter: сколько строк отфильтровано

4. FORMAT

Выбор формата вывода:

EXPLAIN (FORMAT JSON) SELECT * FROM users;
-- Выводит JSON

EXPLAIN (FORMAT XML) SELECT * FROM users;
-- Выводит XML

EXPLAIN (FORMAT YAML) SELECT * FROM users;
-- Выводит YAML

Параметры EXPLAIN в MySQL

MySQL имеет похожие, но несколько отличающиеся параметры:

EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;

Основные поля в MySQL:

// Пример парсинга EXPLAIN из Java приложения
@Service
public class QueryAnalyzer {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public void analyzeQuery(String sql) {
        String explainQuery = "EXPLAIN FORMAT=JSON " + sql;
        String result = jdbcTemplate.queryForObject(
            explainQuery,
            String.class
        );
        
        // Парсим JSON результат
        // Ищем: 
        // - "select_type": тип селекта (SIMPLE, UNION, etc)
        // - "type": тип доступа к таблице
        // - "possible_keys": какие индексы могли быть использованы
        // - "key": какой индекс фактически использован
        // - "key_len": длина используемого индекса
        // - "rows": примерное количество строк
        // - "filtered": % строк после WHERE
    }
}

Основные параметры в обоих БД

1. Type (тип доступа)

Самый важный параметр — показывает, как БД получает данные:

EXPLAIN SELECT * FROM orders WHERE id = 123;
-- type: const (быстро, идеальное использование индекса)

EXPLAIN SELECT * FROM orders WHERE user_id = 123;
-- type: ref (хороший индекс)

EXPLAIN SELECT * FROM orders WHERE status LIKE '%completed%';
-- type: index_merge (несколько индексов)

EXPLAIN SELECT * FROM orders;
-- type: ALL (полный скан таблицы, медленно!)

От лучшего к худшему:

• system: таблица имеет только 1 строку
• const: максимум 1 совпадающая строка (PRIMARY KEY или UNIQUE)
• eq_ref: одна строка на каждую строку таблицы
• ref: несколько строк совпадают с одним значением индекса
• fulltext: используется полнотекстовый индекс
• ref_or_null: как ref, но с NULL
• index_merge: объединение нескольких индексов
• unique_subquery: подзапрос, возвращающий уникальное значение
• index_subquery: подзапрос с индексом
• range: диапазон значений (>, <, BETWEEN)
• index: полный скан индекса (часто лучше, чем ALL)
• ALL: полный скан таблицы (медленно)

2. Key

Какой индекс фактически использован:

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- key: email_idx (используется индекс email)

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- key: NULL (индекс не использован, полный скан)

3. Rows

Примерное количество строк, которые будут обработаны:

EXPLAIN SELECT * FROM large_table WHERE age > 25;
-- rows: 500000 (БД думает, что будет 500k строк)

Если это число сильно отличается от реального (особенно ANALYZE), значит нужно обновить статистику:

ANALYZE large_table;  -- PostgreSQL
ANALYZE TABLE large_table;  -- MySQL

4. Extra

Дополнительная информация:

Using index: данные прочитаны только из индекса
Using where: WHERE условие применено
Using temporary: временная таблица была создана
Using filesort: внешняя сортировка
Using index for group-by: GROUP BY использует индекс

Практический пример анализа

@Service
public class PerformanceOptimization {
    
    public void optimizeQuery() {
        // Медленный запрос
        String slowQuery = 
            "SELECT u.id, u.name, COUNT(o.id) as orders " +
            "FROM users u " +
            "LEFT JOIN orders o ON u.id = o.user_id " +
            "WHERE u.status = 'active' " +
            "GROUP BY u.id";
        
        // EXPLAIN показывает:
        // - Seq Scan on users (полный скан таблицы users)
        // - Hash Aggregate (групповой расчёт в памяти)
        // - rows: 100000 (предполагаемое количество)
        
        // Оптимизация: добавляем индекс
        String createIndex = "CREATE INDEX idx_users_status ON users(status);";
        
        // После оптимизации EXPLAIN должен показать:
        // - Index Scan using idx_users_status
        // - rows: 50000 (меньше)
    }
}

Типичные проблемы

-- ❌ Проблема 1: Using filesort (внешняя сортировка, медленно)
EXPLAIN SELECT * FROM users WHERE status = 'active' ORDER BY created_at;
-- Extra: Using where; Using filesort

-- ✅ Решение: создать индекс
CREATE INDEX idx_status_created ON users(status, created_at);

-- ❌ Проблема 2: Using temporary (временная таблица, память)
EXPLAIN SELECT * FROM orders GROUP BY user_id, product_id;
-- Extra: Using temporary; Using filesort

-- ✅ Решение: оптимизировать GROUP BY

-- ❌ Проблема 3: ALL (полный скан, очень медленно)
EXPLAIN SELECT * FROM large_table WHERE created_at > DATE_SUB(NOW(), INTERVAL 1 DAY);
-- type: ALL

-- ✅ Решение: добавить индекс
CREATE INDEX idx_created_at ON large_table(created_at);

Использование в Java приложении

@Configuration
public class DatabaseConfig {
    
    @Bean
    public DataSourceProxy dataSourceProxy(DataSource dataSource) {
        return new DataSourceProxy(dataSource);
    }
}

@Repository
public class UserRepositoryImpl {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public void explainQuery() {
        String sql = "SELECT * FROM users WHERE status = 'active'";
        String explainSql = "EXPLAIN " + sql;
        
        List<Map<String, Object>> result = 
            jdbcTemplate.queryForList(explainSql);
        
        for (Map<String, Object> row : result) {
            System.out.println("EXPLAIN: " + row);
        }
    }
}

Заключение

EXPLAIN — это мощный инструмент для оптимизации SQL запросов. Главные параметры:

1. Type — как получаются данные (важнейший параметр)
2. Key — какой индекс использован
3. Rows — сколько строк обработано
4. Extra — дополнительные операции

Любой Java разработчик, работающий с БД, должен уметь читать EXPLAIN. Регулярная проверка EXPLAIN в development сохранит вам от production проблем.