Какие знаешь способы оптимизировать длительный запрос, если индексы настроены корректно и код работает без ошибок?

Оптимизация длительных запросов (при корректных индексах)

Если индексы уже настроены корректно, но запрос всё ещё медленный, есть много других способов оптимизации. Вот систематический подход:

1. Анализ EXPLAIN плана

Первый шаг — понять, что именно медленно:

EXPLAIN ANALYZE
SELECT u.id, u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.created_at > 2024-01-01
GROUP BY u.id, u.name
ORDER BY post_count DESC
LIMIT 100;

Что искать:

Planning Time: 0.085 ms
Execution Time: 2500.123 ms  ← ДОЛГО!

→ Seq Scan on users u (cost=0.00..5000.00) ← Полная таблица!
→ Hash Join (cost=1000.00..10000.00) ← Дорогая операция
→ GroupAggregate (cost=2000.00..15000.00) ← Группировка медленная

2. Избегай N+1 запросов

Проблема:

List<User> users = userRepository.findAll();  // 1 запрос
for (User user : users) {
    System.out.println(user.getPosts());  // N запросов
}
// Итого: 1 + N запросов

Решение: Eager Loading (JOIN FETCH)

@Query("""
    SELECT DISTINCT u 
    FROM User u 
    LEFT JOIN FETCH u.posts p
    WHERE u.createdAt > :date
""")
List<User> findAllWithPostsAfterDate(@Param("date") LocalDateTime date);

Или через @EntityGraph:

@EntityGraph(attributePaths = {"posts", "comments"})
List<User> findAll();

3. Используй DTO вместо Entity

Проблема: загружаешь все столбцы, хотя нужны только несколько

// ПЛОХО — загружает всё
List<User> users = userRepository.findAll();
users.stream().map(u -> u.getName()).collect(toList());

Решение: DTO projection

public record UserNameDto(Long id, String name) {}

@Query("SELECT new com.example.UserNameDto(u.id, u.name) FROM User u")
List<UserNameDto> findAllNames();

Или interface projection:

public interface UserNameProjection {
    Long getId();
    String getName();
}

List<UserNameProjection> findAllProjectedNames();

4. Пагинация

Проблема: загружаешь 1 млн записей в память

// ПЛОХО
List<Order> allOrders = orderRepository.findAll();  // OutOfMemory!

Решение: Page или Slice

public Page<OrderDto> getOrders(
    @RequestParam(defaultValue = "0") int page,
    @RequestParam(defaultValue = "50") int size
) {
    Pageable pageable = PageRequest.of(page, size, 
        Sort.by("createdAt").descending());
    return orderRepository.findAll(pageable);
}

Slice (не считает total):

Slice<Order> slice = orderRepository.findAll(
    PageRequest.of(0, 100)
);  // Быстрее, не нужен COUNT(*)

5. Запросы со статистикой вместо SUBQUERY

Проблема: subquery на каждой строке

-- ПЛОХО
SELECT u.*, 
       (SELECT COUNT(*) FROM posts WHERE user_id = u.id) as post_count
FROM users u;

Решение: JOIN с GROUP BY

-- ХОРОШО
SELECT u.id, u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name;

6. Кэширование (Query Cache, Redis)

Проблема: один и тот же запрос выполняется 100 раз в сек

Решение: Redis кэш

@Service
public class UserService {
    @Autowired
    private RedisTemplate<String, User> redisTemplate;
    
    @Cacheable(value = "users", key = "#id")
    public User getUserById(Long id) {
        // Первый вызов → БД
        // Следующие 5 минут → Redis
        return userRepository.findById(id).orElseThrow();
    }
    
    @CacheEvict(value = "users", key = "#id")
    public void updateUser(Long id, User user) {
        userRepository.save(user);
    }
}

Redis конфиг:

spring:
  cache:
    type: redis
    redis:
      time-to-live: 300000  # 5 минут
  redis:
    host: localhost
    port: 6379

7. Query Batching

Проблема: много маленьких запросов

// ПЛОХО
for (Long userId : userIds) {
    User user = userRepository.findById(userId);  // 1000 запросов!
}

Решение: загрузи все сразу

// ХОРОШО
List<User> users = userRepository.findAllById(userIds);  // 1 запрос

8. Асинхронные запросы (если долгие операции)

Проблема: пользователь ждёт 10 секунд

// ПЛОХО
@GetMapping("/report")
public ReportDto generateReport() {
    // 10 секунд обработки
    return expensiveOperation();
}

Решение: асинхронная обработка

@GetMapping("/report")
public ResponseEntity<Map<String, String>> generateReportAsync() {
    CompletableFuture<ReportDto> future = reportService.generateAsync();
    return ResponseEntity.accepted()
        .body(Map.of("jobId", UUID.randomUUID().toString()));
}

@Service
public class ReportService {
    @Async
    public CompletableFuture<ReportDto> generateAsync() {
        // Выполняется в отдельном потоке
        return CompletableFuture.completedFuture(expensiveOperation());
    }
}

9. Материализованные представления (PostgreSQL)

Проблема: сложная агрегация выполняется каждый раз

-- Создай материализованное представление (снимок)
CREATE MATERIALIZED VIEW user_stats AS
SELECT u.id, u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.name;

-- Запрос instant, не нужны JOIN/GROUP BY
SELECT * FROM user_stats WHERE post_count > 10;

-- Освежи когда нужно
REFRESH MATERIALIZED VIEW user_stats;

10. Batch Insert/Update

Проблема: INSERT 1000 записей за 1000 запросов

// ПЛОХО
for (Order order : orders) {
    orderRepository.save(order);  // 1000 INSERT
}

Решение: batch insert

spring:
  jpa:
    properties:
      hibernate:
        jdbc:
          batch_size: 50
        order_inserts: true
        order_updates: true

// ХОРОШО
@Transactional
public void saveBatch(List<Order> orders) {
    for (int i = 0; i < orders.size(); i++) {
        orderRepository.save(orders.get(i));
        if (i % 50 == 0) {
            // Flush каждые 50 записей
            entityManager.flush();
            entityManager.clear();
        }
    }
}

11. DISTINCT в JOIN

Проблема: DISTINCT замедляет запрос

-- ПЛОХО (много дублей)
SELECT DISTINCT u.* FROM users u
JOIN posts p ON u.id = p.user_id
JOIN comments c ON p.id = c.post_id;

Решение: subquery или FETCH

@Query("""
    SELECT DISTINCT u FROM User u
    LEFT JOIN FETCH u.posts p
    LEFT JOIN FETCH p.comments
""")
List<User> findUsersWithPostsAndComments();

12. Window Functions (вместо GROUP BY)

Проблема: GROUP BY на 1 млн строк

-- PostgreSQL: быстрее с window functions
SELECT id, name, post_count,
       ROW_NUMBER() OVER (ORDER BY post_count DESC) as rank
FROM (
    SELECT u.id, u.name, COUNT(p.id) OVER (PARTITION BY u.id) as post_count
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
);

13. Database Statistics & ANALYZE

-- MySQL
ANALYZE TABLE users;
ANALYZE TABLE posts;

-- PostgreSQL
ANALYZE users;
ANALYZE posts;

14. Connection Pooling

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000

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

• ✓ EXPLAIN ANALYZE показывает узкие места
• ✓ Нет N+1 запросов (используй JOIN FETCH)
• ✓ Используешь DTO вместо полных Entity
• ✓ Применена пагинация где нужна
• ✓ Статистика в GROUP BY, не subquery
• ✓ Кэш для часто запрашиваемых данных
• ✓ Batch операции для INSERT/UPDATE
• ✓ Статистика БД свежие (ANALYZE)
• ✓ Connection pool настроен
• ✓ Асинхронные операции если >5 сек

Итог

Порядок оптимизации:

1. EXPLAIN ANALYZE — определи проблему
2. Добавь JOIN FETCH / DTO projection
3. Пагинируй если много строк
4. Кэшируй повторяющиеся запросы
5. Используй батчирование
6. Профилируй результаты
7. Переходи на асинхронность если очень долго