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

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

Улучшение производительности БД — это критически важный навык для разработчика. Существует множество техник на разных уровнях: от оптимизации запросов до архитектурных решений.

1. Оптимизация SQL запросов

Проблема: N+1 Query Problem

// ПЛОХО: N+1 запросы
List<User> users = userRepository.findAll(); // 1 запрос

for (User user : users) {
    List<Post> posts = postRepository.findByUserId(user.getId()); // N запросов
    user.setPosts(posts);
}
// Всего: 1 + N запросов

// ХОРОШО: JOIN FETCH (одна сессия)
@Query("SELECT u FROM User u LEFT JOIN FETCH u.posts WHERE u.active = true")
List<User> findAllWithPosts();

// или используя Spring Data
@Query("SELECT DISTINCT u FROM User u LEFT JOIN FETCH u.posts")
List<User> findAllWithPostsDistinct();

Проблема: SELECT * (неоптимальные столбцы)

-- ПЛОХО: Загружаются ВСЕ столбцы
SELECT * FROM users WHERE id = 1;

-- ХОРОШО: Загружаются только нужные столбцы
SELECT id, name, email FROM users WHERE id = 1;

// В Hibernate/JPA:
@Query("SELECT new dto.UserDTO(u.id, u.name, u.email) FROM User u WHERE u.id = :id")
UserDTO findUserDTO(@Param("id") Long id);

2. Индексы

Создание индексов

-- БЕЗ индекса — полное сканирование таблицы O(n)
SELECT * FROM users WHERE email = 'john@example.com';

-- С индексом — быстрый поиск O(log n)
CREATE INDEX idx_email ON users(email);
SELECT * FROM users WHERE email = 'john@example.com';

Типы индексов

-- 1. Простой индекс
CREATE INDEX idx_name ON users(name);

-- 2. Составной индекс (для WHERE условий)
CREATE INDEX idx_user_email_active ON users(email, active);
-- Используется для: WHERE email = ? AND active = ?

-- 3. Уникальный индекс
CREATE UNIQUE INDEX idx_email_unique ON users(email);

-- 4. Индекс на TEXT (Full-Text Search)
CREATE FULLTEXT INDEX idx_content ON articles(content);
SELECT * FROM articles WHERE MATCH(content) AGAINST('keyword');

Правила использования индексов

@Entity
public class User {
    @Id
    @GeneratedValue
    private Long id;
    
    @Column(unique = true) // Создает индекс
    private String email;
    
    @Column(name = "created_at")
    @Index(name = "idx_created_at") // Явный индекс
    private LocalDateTime createdAt;
    
    @Column(name = "is_active")
    @Index(name = "idx_active")
    private boolean active;
}

3. Пакетная обработка (Batch Operations)

Проблема: Множество INSERT запросов

// ПЛОХО: Один INSERT за раз
List<User> users = getUsersFromFile(1000000);
for (User user : users) {
    userRepository.save(user); // 1000000 INSERT запросов!
}

Решение: Batch Insert

@Transactional
public void bulkSave(List<User> users) {
    int batchSize = 1000;
    
    for (int i = 0; i < users.size(); i++) {
        entityManager.persist(users.get(i));
        
        if ((i + 1) % batchSize == 0) {
            entityManager.flush(); // Отправить batch
            entityManager.clear(); // Очистить memory
        }
    }
    entityManager.flush();
}

Batch Update

@Transactional
public void bulkUpdate(List<User> users) {
    int batchSize = 1000;
    
    for (int i = 0; i < users.size(); i++) {
        User user = users.get(i);
        user.setStatus("PROCESSED");
        entityManager.merge(user);
        
        if ((i + 1) % batchSize == 0) {
            entityManager.flush();
            entityManager.clear();
        }
    }
}

4. Кэширование

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

import org.springframework.cache.annotation.*;

@Service
public class UserService {
    
    // ПЛОХО: Каждый запрос идет в БД
    public User getUserById(Long id) {
        return userRepository.findById(id).orElse(null);
    }
    
    // ХОРОШО: Кэшируем результат
    @Cacheable(value = "users", key = "#id")
    public User getUserByIdCached(Long id) {
        return userRepository.findById(id).orElse(null);
    }
    
    // Инвалидировать кэш при изменении
    @CacheEvict(value = "users", key = "#id")
    public void updateUser(Long id, User user) {
        userRepository.save(user);
    }
}

Второ-уровневый кэш Hibernate

@Entity
@Cacheable // Кэшируем в Hibernate second-level cache
public class User {
    @Id
    private Long id;
    private String name;
    private String email;
}

// application.properties
spring.jpa.properties.hibernate.cache.use_second_level_cache=true
spring.jpa.properties.hibernate.cache.region.factory_class=org.hibernate.cache.jcache.JCacheRegionFactory

5. Оптимизация Lazy/Eager Loading

Проблема: Lazy Loading вне сессии

// ПЛОХО: LazyInitializationException
@Transactional
public User getUser(Long id) {
    return userRepository.findById(id).get();
} // Сессия закрыта

// user.getPosts() → LazyInitializationException!

Решение: Загрузить в одной сессии

// ХОРОШО: Загружаем в одной сессии
@Transactional
public User getUserWithPosts(Long id) {
    User user = userRepository.findById(id).get();
    user.getPosts().size(); // Инициализировать в сессии
    return user;
}

// или используя JOIN FETCH
@Query("SELECT u FROM User u LEFT JOIN FETCH u.posts WHERE u.id = :id")
User findUserWithPosts(@Param("id") Long id);

6. Connection Pooling

# application.properties
# HikariCP конфигурация (по умолчанию в Spring Boot)
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000

7. Database Tuning

Query Execution Plan

-- Посмотреть как БД выполняет запрос
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com' AND active = true;

-- Вывод покажет:
-- - Используется ли индекс
-- - Количество сканируемых строк
-- - Время выполнения

Таблица Партиционирование (для больших таблиц)

-- Разделить большую таблицу на части
CREATE TABLE users_2024 PARTITION OF users
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- Запрос только по нужному диапазону
SELECT * FROM users WHERE created_at >= '2024-01-01';

8. Асинхронность и Параллелизм

@Service
public class AsyncUserService {
    
    @Async
    public CompletableFuture<List<User>> getAllUsersAsync() {
        List<User> users = userRepository.findAll();
        return CompletableFuture.completedFuture(users);
    }
    
    @Async
    public CompletableFuture<User> getUserAsync(Long id) {
        User user = userRepository.findById(id).orElse(null);
        return CompletableFuture.completedFuture(user);
    }
    
    public void process() throws Exception {
        CompletableFuture<List<User>> allUsers = getAllUsersAsync();
        CompletableFuture<User> user1 = getUserAsync(1L);
        CompletableFuture<User> user2 = getUserAsync(2L);
        
        CompletableFuture.allOf(allUsers, user1, user2).join();
    }
}

9. Мониторинг и Профилирование

// Логирование медленных запросов
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

// P6Spy для перехвата запросов
<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
</dependency>

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

• Использовать JOIN FETCH для связанных сущностей
• Создать индексы на часто используемых колонках
• Минимизировать SELECT * запросы
• Реализовать кэширование результатов
• Использовать batch операции для массовых изменений
• Настроить connection pool размер
• Избегать N+1 problem
• Профилировать медленные запросы
• Использовать database-specific оптимизации
• Регулярно обновлять статистику таблиц