Что использовал при оптимизации сложных SQL запросов

Оптимизация сложных SQL запросов

Оптимизация SQL запросов — это критический навык для разработчика Java, так как неэффективные запросы могут стать узким местом приложения. Вот набор техник и инструментов, которые я использовал при оптимизации.

1. Индексирование (Indexing)

Индексы — это наиболее важный инструмент для оптимизации SQL запросов. Они ускоряют поиск данных на порядки, но требуют дополнительную память.

// SQL для создания индекса
// CREATE INDEX idx_user_email ON users(email);
// CREATE INDEX idx_order_date ON orders(created_at);

// В Hibernate можно использовать аннотации
@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_email", columnList = "email", unique = true),
    @Index(name = "idx_created_at", columnList = "created_at")
})
public class User {
    @Column(unique = true, nullable = false)
    private String email;
    
    @Column(name = "created_at")
    private LocalDateTime createdAt;
}

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

EXPLAIN показывает, как база данных выполняет запрос, и помогает найти проблемы.

// PostgreSQL пример
// EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123 AND status = completed;

// Результат показывает:
// - Seq Scan vs Index Scan
// - Количество строк (estimated vs actual)
// - Стоимость выполнения
// - Используемые индексы

public class QueryAnalyzer {
    @Autowired
    private EntityManager entityManager;
    
    public void analyzeQuery(String sql) {
        Session session = entityManager.unwrap(Session.class);
        SQLQuery query = session.createSQLQuery("EXPLAIN ANALYZE " + sql);
        List<Object[]> results = query.list();
        results.forEach(row -> System.out.println(row[0]));
    }
}

3. Избегание N+1 проблемы

N+1 проблема происходит, когда для каждой строки результата выполняется дополнительный запрос.

// ПЛОХО - N+1 проблема
@Service
public class OrderService {
    public List<OrderDto> getOrders() {
        List<Order> orders = orderRepository.findAll();
        // Для каждого заказа выполнится отдельный запрос к user
        return orders.stream()
            .map(o -> new OrderDto(o.getId(), o.getUser().getName()))
            .collect(Collectors.toList());
    }
}

// ХОРОШО - Eager loading с JOIN
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    @Query("SELECT DISTINCT o FROM Order o JOIN FETCH o.user u")
    List<Order> findAllWithUser();
}

// Или с помощью аннотации
@Entity
public class Order {
    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "user_id")
    private User user;
}

// Или с использованием @EntityGraph
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    @EntityGraph(attributePaths = {"user"})
    List<Order> findAll();
}

4. Aggregation и GROUP BY

// Эффективное агрегирование вместо обработки в Java
@Repository
public interface SalesRepository extends JpaRepository<Sale, Long> {
    @Query("SELECT new map(s.category as category, " +
           "COUNT(s) as count, SUM(s.amount) as total) " +
           "FROM Sale s GROUP BY s.category")
    List<Map<String, Object>> getSalesByCategory();
}

// Использование в сервисе
@Service
public class ReportService {
    public List<CategoryReport> generateReport() {
        List<Map<String, Object>> results = salesRepository.getSalesByCategory();
        // Данные уже агрегированы в БД, не в Java
        return results.stream()
            .map(m -> new CategoryReport(
                (String) m.get("category"),
                (Long) m.get("count"),
                (BigDecimal) m.get("total")
            ))
            .collect(Collectors.toList());
    }
}

5. Pagination

// Получение большого набора данных постепенно
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    // Используем Pageable для ограничения результатов
    Page<User> findAll(Pageable pageable);
}

@Service
public class UserService {
    public Page<UserDto> getUsers(int page, int size) {
        Pageable pageable = PageRequest.of(page, size, Sort.by("createdAt").descending());
        return userRepository.findAll(pageable)
            .map(UserMapper::toDto);
    }
}

6. Projection - выбор только нужных полей

// Вместо SELECT * используем SELECT конкретных полей
public interface UserProjection {
    Long getId();
    String getName();
    String getEmail();
}

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    // Загружаем только нужные поля
    List<UserProjection> findAllProjectedBy();
    
    // Или используем DTO
    @Query("SELECT new com.example.dto.UserDto(u.id, u.name, u.email) FROM User u")
    List<UserDto> findAllAsDto();
}

7. Caching

// Кэширование результатов сложных запросов
@Service
public class StatisticsService {
    @Cacheable("stats")
    public Statistics getStatistics() {
        // Этот запрос кэшируется
        return statisticsRepository.calculateStatistics();
    }
    
    @CacheEvict("stats")
    public void refreshStatistics() {
        // Очищаем кэш после обновления данных
    }
}

// Конфигурация кэширования
@Configuration
public class CacheConfig {
    @Bean
    public CacheManager cacheManager() {
        return new ConcurrentMapCacheManager("stats", "users");
    }
}

8. Query optimization в Hibernate

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    // Fetch strategy - загружаем зависимости с первым запросом
    @Query("SELECT DISTINCT o FROM Order o " +
           "LEFT JOIN FETCH o.items " +
           "LEFT JOIN FETCH o.user " +
           "WHERE o.createdAt > :date")
    List<Order> findRecentOrders(@Param("date") LocalDateTime date);
    
    // Используем batch size для оптимизации
    @Query(value = "SELECT * FROM orders WHERE status = :status LIMIT :limit",
           nativeQuery = true)
    List<Order> findByStatus(
        @Param("status") String status,
        @Param("limit") int limit
    );
}

9. Connection pooling

// application.properties
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.max-lifetime=1800000

// Правильный размер пула критичен для производительности

10. Query statistics и мониторинг

// Логирование медленных запросов
// application.properties
spring.jpa.properties.hibernate.generate_statistics=true
spring.jpa.properties.hibernate.use_sql_comments=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

// Отладка N+1 проблемы
spring.jpa.properties.hibernate.enable_lazy_load_no_trans=true

Инструменты для анализа

• EXPLAIN ANALYZE (PostgreSQL) — показывает план выполнения
• Slow Query Log (MySQL) — логирует медленные запросы
• JProfiler — профилирует Java приложение
• New Relic / DataDog — мониторит производительность
• pg_stat_statements (PostgreSQL) — статистика по запросам

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

1. Используйте EXPLAIN ANALYZE для анализа запросов
2. Создайте индексы на часто используемые колонки
3. Избегайте N+1 проблемы с JOIN FETCH или @EntityGraph
4. Используйте pagination для больших наборов данных
5. Выбирайте только нужные поля (projection)
6. Кэшируйте часто выполняемые запросы
7. Переносите логику в БД (GROUP BY, агрегацию)
8. Мониторьте выполнение запросов
9. Тестируйте на реальном размере данных
10. Периодически пересматривайте и оптимизируйте запросы

Правильная оптимизация SQL запросов может улучшить производительность приложения на порядки.