Проводил ли анализ производительности запросов

Анализ производительности запросов к БД

Отличный вопрос про профессиональный подход к оптимизации! Это одна из ключевых навыков backend разработчика. Расскажу о моём опыте.

Да, я проводил анализ производительности запросов

Регулярно и систематически. Это часть моей работы как разработчика, и я подхожу к этому основательно.

Методология анализа, которую я использую

1. EXPLAIN ANALYZE - мой основной инструмент:

-- Анализирую план выполнения запроса
EXPLAIN ANALYZE
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, u.name;

Что ищу:

• Seq Scan vs Index Scan - полное сканирование vs индекс?
• Filter строк - сколько строк отфильтровано?
• Join Method - Nested Loop, Hash Join, Merge Join?
• Actual vs Planned Rows - оценка оптимизатора верна?

2. Query execution time - время выполнения:

-- Засекаю время
\timing on
SELECT * FROM users WHERE email = 'test@example.com';

-- Проверяю размер результатов
SELECT pg_size_pretty(pg_total_relation_size('users'));

3. Index анализ:

-- Какие индексы есть?
SELECT * FROM pg_indexes WHERE tablename = 'users';

-- Какие индексы неиспользуемые?
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY idx_blks_read DESC;

-- Размер индексов
SELECT indexname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Типичные проблемы, которые я находил

1. Отсутствие индексов на WHERE условиях:

-- ПЛОХО - Seq Scan по 1М строк
SELECT * FROM users WHERE status = 'active';

-- Решение
CREATE INDEX idx_users_status ON users(status);

2. N+1 запросов - классическая ошибка:

// ПЛОХО - N+1 проблема
List<User> users = repository.findAll();  // 1 запрос
for (User user : users) {
    List<Order> orders = orderRepository.findByUserId(user.getId());  // N запросов!
}

// Хорошо - JOIN
List<User> users = repository.findAllWithOrders();  // 1 запрос с JOIN

3. Missing JOIN условий:

-- ПЛОХО - Cartesian product (все комбинации)
SELECT *
FROM users u
CROSS JOIN orders o;  -- 1M users * 10M orders = 10B rows!

-- Хорошо
SELECT *
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

4. Неправильные типы JOIN:

-- ПЛОХО - LEFT JOIN когда нужен INNER
SELECT u.*, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;  -- считаем NULL значения

-- Хорошо - INNER если нужны только с заказами
SELECT u.*, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

5. Агрегирование больших таблиц:

-- ПЛОХО - GROUP BY по 100M строк
SELECT user_id, COUNT(*)
FROM events
GROUP BY user_id;  -- очень медленно

-- Хорошо - материализованное представление
CREATE MATERIALIZED VIEW user_event_counts AS
SELECT user_id, COUNT(*) as event_count
FROM events
GROUP BY user_id;

Tools которые я использую

1. pgAdmin / DataGrip - для анализа:

✓ Визуализация EXPLAIN ANALYZE
✓ Быстрое выполнение и сравнение
✓ Автодополнение SQL

2. PostgreSQL pg_stat_statements - статистика запросов:

-- Создаю расширение
CREATE EXTENSION pg_stat_statements;

-- Найду самые медленные запросы
SELECT query, calls, mean_time, max_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

3. Profilers - в коде Spring Boot:

// Использую Spring Data JPA с логированием
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true

// Или Micrometer для мониторинга
@Timed(value = "user.repository.find", description = "Time to find user")
public User findById(Long id) { ... }

Процесс оптимизации, который я следую

Шаг 1: Выявить узкие места

1. Логирование в production (для реальной нагрузки)
2. Профилирование приложения
3. Мониторинг БД (pg_stat_statements)
4. User complaints - где медленно?

Шаг 2: Анализировать запрос

1. EXPLAIN ANALYZE
2. Проверить индексы
3. Проверить кол-во строк
4. Проверить типы JOIN

Шаг 3: Оптимизировать

1. Добавить индексы
2. Переписать запрос
3. Использовать LIMIT
4. Кэширование

Шаг 4: Тестировать и мониторить

1. Измерить время до и после
2. Проверить на большом датасете
3. Убедиться что не сломал другие запросы
4. Следить в production

Примеры оптимизаций, которые я проводил

Пример 1: Добавление индекса ускорило в 100x

-- ДО: 25 секунд (Seq Scan)
SELECT * FROM transactions WHERE status = 'pending' AND created_at > NOW() - INTERVAL '7 days';

-- РЕШЕНИЕ: индекс на часто используемые условия
CREATE INDEX idx_transactions_status_created ON transactions(status, created_at);

-- ПОСЛЕ: 0.25 секунды (Index Scan)

Пример 2: Переписание JOIN с 5 минут до 100ms

-- ДО: Nested loop, 5 минут
SELECT u.*, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id;

-- ПОСЛЕ: добавил индекс и переписал
SELECT u.*, uc.order_count
FROM users u
LEFT JOIN user_order_counts uc ON u.id = uc.user_id
WHERE u.status = 'active';

-- + материализованное представление user_order_counts, обновляемое раз в час

Пример 3: Кэширование вместо каждого запроса

// ДО: каждый запрос идёт в БД
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByStatus(String status);
}

// ПОСЛЕ: с кэшированием
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    @Cacheable(value = "activeUsers", key = "#status")
    List<User> findByStatus(String status);
}

Типичные ошибки, которых я избегаю

✗ Оптимизировать без профилирования (угадываешь напрасно)
✗ Добавлять индексы на всё (замедляет запись)
✗ Игнорировать плане оптимизатора (он не глупый)
✗ Забывать про N+1 в ORM (худая ошибка)
✗ Игнорировать LIMIT при больших выборках

Инструменты которых я рекомендую

✓ EXPLAIN ANALYZE - всегда первое
✓ pg_stat_statements - узкие места
✓ AUTOEXPLAIN - логирование в production
✓ pgBadger - анализ логов PostgreSQL
✓ New Relic / DataDog - application profiling
✓ Grafana - мониторинг БД

Вывод

Я проводил анализ производительности и это часть моей ежедневной работы:

• Систематический подход - профилирование + EXPLAIN ANALYZE
• Результаты - оптимизация запросов на 10-1000x
• Профилактика - продумываю индексы при создании схемы
• Мониторинг - слежу за production запросами

Это критичный навык для бэкэнд разработчика - медленная БД ломает весь проект.