Как решишь проблему с SELECT запросом к БД, который сильно нагружает процессор

Оптимизация SELECT запроса, нагружающего процессор

Проблема медленных SELECT запросов, которые нагружают процессор, — классический performance issue. Это может быть вызвано несколькими причинами, и решение зависит от диагностики.

Шаг 1: Диагностика проблемы

Анализ плана выполнения запроса

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

Видим:

• Seq Scan (полное сканирование таблицы) вместо Index Scan
• High planning time или execution time
• Большое количество обработанных строк
• Missing indexes

Использование pgAdmin или pg_stat_statements

SELECT query, calls, mean_time, max_time
FROM pg_stat_statements
WHERE query LIKE '%users%'
ORDER BY mean_time DESC;

Шаг 2: Основные причины и решения

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

-- Плохо: полное сканирование
SELECT * FROM orders
WHERE customer_id = 123 AND status = 'PENDING';

-- Решение: добавить индекс
CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

Проблема 2: Неправильная статистика БД

-- Переанализировать таблицу
ANALYZE orders;
ANALYZE users;

-- Или для конкретной таблицы и колонок
ANALYZE orders (customer_id, status);

Проблема 3: N+1 queries в коде

// Плохо: N+1 query problem
List<User> users = userRepository.findAll();
for (User user : users) {
    List<Order> orders = user.getOrders();  // N отдельных запросов!
}

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

// Решение 2: @EntityGraph
@EntityGraph(attributePaths = "orders")
List<User> findAll();

// Решение 3: Batch fetching
@BatchSize(size = 20)
private Collection<Order> orders;

Проблема 4: Неэффективные JOINы

-- Плохо: избыточные JOIN'ы
SELECT u.*, o.*, p.*, c.*
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
JOIN categories c ON p.category_id = c.id;

-- Решение: SELECT только нужные столбцы
SELECT u.id, u.name, o.id, o.total, p.name, c.name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id
JOIN categories c ON p.category_id = c.id;

Шаг 3: Практические решения

Решение 1: Добавление нужных индексов

-- Primary key индекс (автоматический)
CREATE TABLE orders (
    id UUID PRIMARY KEY
);

-- Индекс на внешний ключ
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Составной индекс для WHERE clause
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Индекс на JOIN колонку
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- Partial индекс (для часто используемых фильтров)
CREATE INDEX idx_orders_active ON orders(user_id)
WHERE status != 'CANCELLED';

Решение 2: Переписание запроса

// Было: N+1 problem с Hibernate
@Repository
public class OrderRepository {
    @Query("SELECT o FROM Order o WHERE o.createdAt > :date")
    List<Order> findRecentOrders(@Param("date") LocalDateTime date);
}

// Стало: с JOIN FETCH и оптимизацией
@Repository
public class OrderRepository extends JpaRepository<Order, UUID> {
    @Query("""
        SELECT DISTINCT o FROM Order o
        LEFT JOIN FETCH o.customer c
        LEFT JOIN FETCH o.items i
        LEFT JOIN FETCH i.product p
        WHERE o.createdAt > :date
        ORDER BY o.createdAt DESC
    """)
    List<Order> findRecentOrders(@Param("date") LocalDateTime date);
}

Решение 3: Кэширование результатов

@Service
public class OrderService {
    private final orderRepository;
    private final cacheManager;
    
    @Cacheable(value = "popular-orders", key = "'last-30-days'")
    public List<Order> getPopularOrders() {
        return orderRepository.findPopularOrders();
    }
    
    @CacheEvict(value = "popular-orders", key = "'last-30-days'")
    @Transactional
    public void cachePopularOrders() {
        // Пересчитать кэш когда нужно
    }
}

@Configuration
public class CacheConfig {
    @Bean
    public RedisCacheManager cacheManager(RedisConnectionFactory connectionFactory) {
        RedisCacheConfiguration config = RedisCacheConfiguration.defaultCacheConfig()
            .entryTtl(Duration.ofHours(1));
        return RedisCacheManager.create(connectionFactory);
    }
}

Решение 4: Pagination для больших результатов

// Плохо: загружать миллион записей
List<Order> allOrders = orderRepository.findAll();

// Хорошо: с пагинацией
@Query("SELECT o FROM Order o WHERE o.status = 'PENDING'")
Page<Order> findPendingOrders(Pageable pageable);

// Использование
Pageable pageRequest = PageRequest.of(0, 50, Sort.by("createdAt").descending());
Page<Order> page = orderRepository.findPendingOrders(pageRequest);
List<Order> orders = page.getContent();

Шаг 4: Мониторинг и профилирование

Использование Spring Boot Actuator

# application.yml
spring:
  jpa:
    show-sql: false
    properties:
      hibernate:
        generate_statistics: true
        use_sql_comments: true
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5

Логирование медленных запросов

@Configuration
public class HibernateConfig {
    @Bean
    public SessionFactoryBuilder hibernateMetrics() {
        // StatisticsService помогает отследить медленные запросы
        return new SessionFactoryBuilder();
    }
}

// Проверка медленных запросов
SessionFactory sessionFactory = entityManager.getEntityManagerFactory()
    .unwrap(SessionFactory.class);
SessionStatistics stats = sessionFactory.getStatistics();
for (String query : stats.getQueries()) {
    System.out.println("Query: " + query + " Time: " + 
        stats.getQueryStatistics(query).getExecutionAvgTime());
}

Использование Query Advisor

-- Найти самые медленные запросы
SELECT 
    query,
    calls,
    total_time,
    mean_time,
    max_time
FROM pg_stat_statements
WHERE mean_time > 100  -- больше 100ms
ORDER BY mean_time DESC
LIMIT 10;

Шаг 5: Комплексное решение

@Service
public class OrderService {
    private final orderRepository;
    private final cacheManager;
    private final logger;
    
    @Transactional(readOnly = true)
    @Cacheable(value = "orders-by-customer", key = "#customerId")
    public List<OrderDTO> getOrdersByCustomer(UUID customerId) {
        long startTime = System.currentTimeMillis();
        
        // Используем оптимизированный запрос с JOIN FETCH
        List<Order> orders = orderRepository.findByCustomerIdWithDetails(customerId);
        
        long duration = System.currentTimeMillis() - startTime;
        logger.info("Query executed in {}ms", duration);
        
        // Кэше результат
        return orders.stream()
            .map(OrderDTO::from)
            .collect(Collectors.toList());
    }
}

@Repository
public interface OrderRepository extends JpaRepository<Order, UUID> {
    @Query("""
        SELECT DISTINCT o FROM Order o
        LEFT JOIN FETCH o.customer
        LEFT JOIN FETCH o.items i
        LEFT JOIN FETCH i.product
        WHERE o.customer.id = :customerId
        AND o.createdAt > :dateThreshold
        ORDER BY o.createdAt DESC
    """)
    List<Order> findByCustomerIdWithDetails(
        @Param("customerId") UUID customerId,
        @Param("dateThreshold") LocalDateTime dateThreshold
    );
}

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

1. Анализ плана — EXPLAIN для каждого медленного запроса
2. Индексы — проверить наличие индексов на колонках в WHERE
3. Join Fetch — избежать N+1 в ORM
4. Pagination — для больших результатов
5. Caching — Redis для часто используемых данных
6. Статистика — ANALYZE таблицу если планы неправильные
7. Connection Pool — оптимизировать HikariCP параметры
8. Мониторинг — pg_stat_statements для отслеживания

Оптимизация SELECT запросов — это постоянный процесс мониторинга, анализа и улучшения.