Как начнешь разбираться в сложном и медленном запросе в базу данных

# Диагностика и оптимизация медленных запросов к БД

Медленные запросы — частая причина деградации производительности приложения. Процесс диагностики требует систематического подхода.

1. Идентификация медленного запроса

Использование логов базы данных

-- PostgreSQL: включить логирование медленных запросов
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- 1 сек
ALTER SYSTEM SET log_statement = 'all';
SELECT pg_reload_conf();

-- MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Проверить лог
TAIL -f /var/log/postgresql/postgresql.log

Java: перехват медленных запросов

@Configuration
public class HibernateStatisticsConfig {
    @Bean
    public SessionFactoryBuilderCustomizer sessionFactoryBuilderCustomizer() {
        return builder -> {
            // Включить статистику Hibernate
            builder.applyInterceptor(new StatementInspector() {
                @Override
                public String inspect(String sql) {
                    long start = System.currentTimeMillis();
                    // Выполнить и измерить
                    long duration = System.currentTimeMillis() - start;
                    
                    if (duration > 1000) {  // 1 сек
                        logger.warn("Slow query ({} ms): {}", duration, sql);
                    }
                    return sql;
                }
            });
        };
    }
}

// Или использовать Spring Data JPA Auditing
@Configuration
public class SlowQueryAuditor {
    @Bean
    public StatementInspector slowQueryInspector() {
        return sql -> {
            long start = System.nanoTime();
            // ... выполнение
            long duration = (System.nanoTime() - start) / 1_000_000;
            if (duration > 1000) {
                logger.warn("SLOW QUERY: {} ms\n{}", duration, sql);
            }
            return sql;
        };
    }
}

2. Анализ плана выполнения

PostgreSQL: EXPLAIN ANALYZE

-- Базовый план
EXPLAIN
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;

-- Детальный анализ
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 on users u (cost=0.00..50000.00 rows=1000000)
--   Filter: (created_at > '2024-01-01')
-- Hash Left Join (cost=100.00..200.00)
--   Hash Cond: (u.id = o.user_id)

Интерпретация

• Seq Scan — полное сканирование таблицы (медленно)
• Index Scan — использование индекса (быстро)
• Hash Join — эффективная стратегия для JOIN
• Nested Loop — медленная для больших таблиц
• cost — относительная стоимость операции
• rows — ожидаемое количество строк

3. Типичные проблемы и решения

Проблема 1: Отсутствие индекса

-- Медленный запрос
SELECT * FROM users WHERE status = 'ACTIVE';
-- Seq Scan: 5 сек

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

-- Теперь быстро: 10 мс
EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'ACTIVE';
-- Index Scan using idx_users_status

Проблема 2: N+1 запросы (в ORM)

// ПЛОХО — N+1 проблема
@Entity
public class User {
    @OneToMany
    private List<Order> orders;  // Lazy load по умолчанию
}

public List<UserDTO> getUsers() {
    List<User> users = userRepository.findAll();  // 1 запрос
    
    return users.stream()
        .map(u -> new UserDTO(
            u.getId(),
            u.getName(),
            u.getOrders().size()  // N запросов!
        ))
        .collect(toList());
}

// Решение 1: Eager Loading
@Query("""
    SELECT u FROM User u
    LEFT JOIN FETCH u.orders
""")
List<User> findAllWithOrders();

// Решение 2: DTO проекция
@Query("""
    SELECT new com.example.UserDTO(
        u.id, u.name, COUNT(o.id)
    )
    FROM User u
    LEFT JOIN u.orders o
    GROUP BY u.id, u.name
""")
List<UserDTO> getUsersWithOrderCount();

// Решение 3: Projection
public interface UserOrderCountProjection {
    Long getId();
    String getName();
    Long getOrderCount();
}

@Query("""
    SELECT u.id, u.name, COUNT(o.id) as orderCount
    FROM User u
    LEFT JOIN u.orders o
    GROUP BY u.id, u.name
""")
List<UserOrderCountProjection> getUsers();

Проблема 3: Полное сканирование большой таблицы

-- Медленно
SELECT * FROM orders
WHERE LOWER(customer_name) LIKE '%john%';

-- Быстро: использовать функциональный индекс
CREATE INDEX idx_orders_customer_lower
ON orders(LOWER(customer_name));

SELECT * FROM orders
WHERE LOWER(customer_name) LIKE '%john%';  -- Теперь использует индекс

Проблема 4: JOIN без индекса на внешнем ключе

-- Медленно
SELECT u.id, u.name, o.id, o.total
FROM users u
JOIN orders o ON u.id = o.user_id  -- user_id без индекса
WHERE o.created_at > '2024-01-01';

-- Решение: создать индекс на внешнем ключе
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Теперь быстро
EXPLAIN ANALYZE SELECT ...
-- Index Scan using idx_orders_user_id

Проблема 5: Сложный GROUP BY / HAVING

-- Медленно
SELECT user_id, COUNT(*) as order_count, AVG(total) as avg_total
FROM orders
GROUP BY user_id
HAVING COUNT(*) > 10;  -- Фильтрация после GROUP BY

-- Быстрее: использовать материализованное представление
CREATE MATERIALIZED VIEW user_order_stats AS
SELECT 
    user_id,
    COUNT(*) as order_count,
    AVG(total) as avg_total
FROM orders
GROUP BY user_id;

REFRESH MATERIALIZED VIEW user_order_stats;

SELECT * FROM user_order_stats WHERE order_count > 10;

4. Инструменты профилирования в Java

Spring Boot Actuator

spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true
  endpoint:
    health:
      show-details: always

management:
  endpoints:
    web:
      exposure:
        include: health,metrics,httptrace

@RestController
public class MetricsController {
    @Autowired
    private SessionFactory sessionFactory;
    
    @GetMapping("/metrics/database")
    public Map<String, Object> databaseMetrics() {
        Statistics statistics = sessionFactory.getStatistics();
        
        return Map.of(
            "prepareStatementCount", statistics.getPrepareStatementCount(),
            "entityLoadCount", statistics.getEntityLoadCount(),
            "entityInsertCount", statistics.getEntityInsertCount(),
            "collectionLoadCount", statistics.getCollectionLoadCount(),
            "successfulTransactionCount", statistics.getSuccessfulTransactionCount()
        );
    }
}

Профилирование с помощью Micrometer

@Component
public class DatabaseQueryMetrics {
    private final MeterRegistry meterRegistry;
    
    public void recordQueryTime(String queryName, long durationMs) {
        Timer.builder("db.query.duration")
            .tag("query", queryName)
            .publishPercentiles(0.5, 0.95, 0.99)
            .register(meterRegistry)
            .record(durationMs, TimeUnit.MILLISECONDS);
    }
}

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

[x] Включил логирование медленных запросов
[x] Запустил EXPLAIN ANALYZE
[x] Проверил, используются ли индексы
[x] Проверил на N+1 проблемы
[x] Оптимизировал JOIN условия
[x] Проверил статистику базы (ANALYZE)
[x] Рассмотрел кеширование результатов
[x] Проверил размер результата (LIMIT)
[x] Рассмотрел денормализацию данных
[x] Проверил параметры БД (work_mem, shared_buffers)

6. Продвинутые техники

Партиционирование таблиц

CREATE TABLE orders (
    id BIGINT,
    user_id BIGINT,
    created_at TIMESTAMP,
    total DECIMAL
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p_2023 VALUES LESS THAN (2024),
    PARTITION p_2024 VALUES LESS THAN (2025),
    PARTITION p_2025 VALUES LESS THAN (2026)
);

Query результаты кеширование

@Service
public class CachedUserService {
    @Cacheable(value = "users", key = "#id")
    public User getUserById(Long id) {
        return userRepository.findById(id);
    }
    
    @CacheEvict(value = "users", key = "#user.id")
    public void updateUser(User user) {
        userRepository.save(user);
    }
}

Лучшие практики

1. Логируй медленные запросы — это первый шаг диагностики
2. Используй EXPLAIN ANALYZE — понимай план выполнения
3. Создавай индексы для WHERE и JOIN условий — но не перенаполняй
4. Избегай N+1 проблем — используй fetch joins или DTO проекции
5. Мониторь метрики БД — размер результатов, количество запросов
6. Пересчитывай статистику БД — ANALYZE для PostgreSQL
7. Кешируй результаты — когда имеет смысл
8. Используй LIMIT — не везти все данные, если не нужны