Решал ли проблему медленных запросов к базе данных

Решение проблемы медленных запросов к базе данных

Да, в своей практике я неоднократно сталкивался с проблемой медленных запросов и успешно применял различные техники оптимизации. Расскажу о системном подходе, который я использую.

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

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

• EXPLAIN ANALYZE — посмотреть план выполнения запроса
• Логирование с временем выполнения
• Мониторинг БД (показывает медленные запросы)
• Профилирование приложения (которое вызывает запрос)

// Пример: включаем логирование SQL в Hibernate
// application.properties
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true

// Или более подробное логирование
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Частые причины медленных запросов

1. Отсутствие индексов:

-- Медленно: поиск по незиндексированному полю
SELECT * FROM users WHERE email = 'user@example.com';

-- Решение: добавить индекс
CREATE INDEX idx_users_email ON users(email);

// В Hibernate:
@Entity
@Table(name = "users", indexes = {
    @Index(name = "idx_users_email", columnList = "email", unique = true)
})
public class User {
    @Id
    private Long id;
    
    @Column(name = "email")
    private String email;
}

2. N+1 query problem:

// ПЛОХО: N+1 запросов
List<Order> orders = orderRepository.findAll(); // 1 запрос
for (Order order : orders) {
    Customer customer = order.getCustomer(); // N запросов (по одному на Order)
}

// РЕШЕНИЕ 1: Eager loading с @Query и JOIN FETCH
@Query("SELECT o FROM Order o JOIN FETCH o.customer")
List<Order> findAllWithCustomers();

// РЕШЕНИЕ 2: EntityGraph
@EntityGraph(attributePaths = {"customer"})
@Query("SELECT o FROM Order o")
List<Order> findAllWithGraph();

// РЕШЕНИЕ 3: Batch loading
@BatchSize(size = 20)
@ManyToOne
private Customer customer;

3. Сложный 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
WHERE u.id = ?;

-- Оптимизация: индексы на внешние ключи
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_orders_product_id ON orders(product_id);

4. Полный скан таблицы (table scan):

-- ПЛОХО: нет WHERE, выбирается вся таблица
SELECT * FROM products;

-- ХОРОШО: используем LIMIT, и есть индексы
SELECT * FROM products WHERE category_id = 5 LIMIT 10;

Пример из реальной практики

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

@Entity
@Table(name = "user_logs")
public class UserLog {
    @Id
    private Long id;
    
    @Column(name = "user_id")
    private Long userId; // БЕЗ индекса — БУД!
    
    @Column(name = "action")
    private String action;
    
    @Temporal(TemporalType.TIMESTAMP)
    @Column(name = "created_at")
    private Date createdAt; // БЕЗ индекса
}

// Запрос был медленным:
@Query("SELECT l FROM UserLog l WHERE l.userId = ?1 AND l.createdAt > ?2")
List<UserLog> findUserLogsAfter(Long userId, Date date);

Решение:

@Entity
@Table(name = "user_logs", indexes = {
    @Index(name = "idx_user_logs_user_id", columnList = "user_id"),
    @Index(name = "idx_user_logs_created_at", columnList = "created_at"),
    @Index(name = "idx_user_logs_composite", columnList = "user_id, created_at")
})
public class UserLog {
    // ...
}

После этого запрос выполнялся в 100 раз быстрее.

Техники оптимизации

1. Проекции (выбираем только нужные поля):

@Query("SELECT new com.example.UserDTO(u.id, u.name, u.email) FROM User u WHERE u.id = ?1")
UserDTO findUserDTO(Long id);

// Без new — SELECT * (всё равно)

2. Pagination для больших результатов:

// ПЛОХО: все 1 млн записей
List<Order> orders = orderRepository.findAll();

// ХОРОШО: постраничная выборка
Page<Order> page = orderRepository.findAll(PageRequest.of(0, 50));

3. Кэширование часто запрашиваемых данных:

@Service
public class CategoryService {
    @Cacheable("categories")
    public List<Category> getAllCategories() {
        return categoryRepository.findAll();
    }
}

4. Асинхронные запросы для heavy операций:

@Service
public class ReportService {
    @Async
    public CompletableFuture<Report> generateReport(Long userId) {
        // Долгий запрос в отдельном потоке
        List<Order> orders = orderRepository.findByUserId(userId);
        return CompletableFuture.completedFuture(buildReport(orders));
    }
}

5. Batch operations для множественных операций:

// ПЛОХО: 1000 INSERT'ов отдельно
for (Order order : orders) {
    orderRepository.save(order); // 1000 запросов
}

// ХОРОШО: batch insert
public void saveOrders(List<Order> orders) {
    for (int i = 0; i < orders.size(); i++) {
        sessionFactory.getCurrentSession().save(orders.get(i));
        if (i % 20 == 0) {
            sessionFactory.getCurrentSession().flush();
            sessionFactory.getCurrentSession().clear();
        }
    }
}

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

• MySQL: SHOW SLOW QUERY LOG, SHOW PROCESSLIST
• PostgreSQL: pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS)
• JPA/Hibernate: spring-boot-starter-data-jpa с логированием
• APM: New Relic, Datadog — показывают узкие места

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

✓ Используй EXPLAIN для анализа плана ✓ Добавляй индексы на часто фильтруемые поля ✓ Решай N+1 problem с JOIN FETCH или EntityGraph ✓ Выбирай только нужные колонки (проекции) ✓ Используй LIMIT и OFFSET для больших наборов ✓ Кэшируй static данные ✓ Батчь multiple операции ✓ Мониторь медленные запросы в продакшене