Какие знаешь способы оптимизации базы данных при высокой нагрузке на CPU?

Оптимизация БД при высокой нагрузке на CPU

Когда CPU становится узким местом в БД, нужна комплексная стратегия оптимизации. CPU нагружается при парсинге запросов, выполнении функций, сортировке и агрегации данных.

1. Оптимизация запросов (Query Tuning)

Избегай дорогих операций

-- ❌ Неоптимально: обрабатывает все строки
SELECT * FROM orders WHERE YEAR(created_at) = 2024;

-- ✅ Оптимально: использует индекс на диапазон
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

Избегай N+1 queries

// ❌ Плохо: N+1 запросов
List<User> users = userRepository.findAll();
for (User user : users) {
    List<Order> orders = orderRepository.findByUserId(user.getId());
    // N+1 запроса!
}

// ✅ Хорошо: один запрос с JOIN
@Query("""
    SELECT u FROM User u 
    LEFT JOIN FETCH u.orders 
    WHERE u.active = true
""")
List<User> getAllWithOrders();

Используй SELECT только нужные столбцы

-- ❌ Ненужные данные занимают CPU и память
SELECT * FROM large_table WHERE id = 1;

-- ✅ Только нужные столбцы
SELECT id, name, email FROM users WHERE id = 1;

2. Кэширование на разных уровнях

L1: In-Memory кэш (application level)

private static final Map<Long, User> cache = new ConcurrentHashMap<>();

public User getUserCached(Long id) {
    return cache.computeIfAbsent(id, k -> loadFromDatabase(k));
}

L2: Redis (distributed cache)

@Cacheable(value = "users", key = "#id")
public User getUser(Long id) {
    return userRepository.findById(id).orElseThrow();
}

// Кэш на 1 час
@Cacheable(value = "products", key = "#id", unless = "#result == null")
@CacheEvict(value = "products", allEntries = true, cacheNames = "products", 
            condition = "T(java.time.LocalDateTime).now().getHour() == 3")
public Product getProduct(Long id) {
    return productRepository.findById(id).orElseThrow();
}

L3: Query Result Cache (PostgreSQL, MySQL)

// Spring Data с @Cacheable
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    @Cacheable(value = "topOrders", unless = "#result == null")
    @Query("SELECT o FROM Order o ORDER BY o.amount DESC LIMIT :limit")
    List<Order> findTopOrders(@Param("limit") int limit);
}

3. Connection Pooling и асинхронность

HikariCP оптимизация

# application.properties
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

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

@Service
public class ReportService {
    @Async
    public CompletableFuture<List<Report>> generateReports() {
        // Выполняется в отдельном потоке, не блокирует
        List<Report> reports = reportRepository.findAll();
        return CompletableFuture.completedFuture(reports);
    }
}

// Использование
@GetMapping("/reports")
public CompletableFuture<List<Report>> getReports() {
    return reportService.generateReports();
}

4. Batch Processing вместо одиночных операций

❌ Неоптимально

for (User user : users) {
    userRepository.save(user); // 1000 INSERT запросов!
}

✅ Оптимально

// Batch insert
userRepository.saveAll(users); // 1 большой INSERT

// Или через JDBC Batch
String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
PreparedStatement stmt = conn.prepareStatement(sql);
for (User user : users) {
    stmt.setString(1, user.getName());
    stmt.setString(2, user.getEmail());
    stmt.addBatch();
}
stmt.executeBatch(); // Все сразу

5. Индексирование (правильное)

-- ✅ Индекс на часто используемые WHERE условия
CREATE INDEX idx_user_status ON users(status) WHERE status = 'active';

-- ✅ Составной индекс для часто используемых фильтров
CREATE INDEX idx_order_user_date ON orders(user_id, created_at DESC);

-- ❌ Избегай избыточных индексов
-- PostgreSQL должна анализировать индексы = CPU нагрузка

6. Денормализация и материализованные представления

-- ❌ Каждый запрос пересчитывает агрегацию
SELECT 
    u.id,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- ✅ Материализованное представление (обновляется раз в час)
CREATE MATERIALIZED VIEW user_stats AS
SELECT 
    u.id,
    COUNT(o.id) as order_count,
    SUM(o.amount) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

REFRESH MATERIALIZED VIEW user_stats;

SELECT * FROM user_stats WHERE id = 1; -- Быстро!

7. Партиционирование таблиц (Partitioning)

При работе с большими таблицами разбей на части:

-- Партиционирование по диапазону дат
CREATE TABLE orders (
    id SERIAL,
    user_id INT,
    created_at TIMESTAMP,
    amount DECIMAL
) PARTITION BY RANGE (YEAR(created_at));

CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM (2023) TO (2024);

CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM (2024) TO (2025);

-- PostgreSQL автоматически выбирает нужный partition
SELECT * FROM orders WHERE created_at >= '2024-01-01';
-- Сканирует только orders_2024!

8. Использование EXPLAIN для анализа

-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM orders 
WHERE user_id = 1 AND created_at >= '2024-01-01';

-- Выведет план выполнения с информацией о CPU затратах
-- Плоды: Seq Scan (плохо), Index Scan (хорошо)

9. Агрегирование на уровне приложения вместо БД

// ❌ БД вычисляет все агрегации (CPU нагрузка)
@Query("""
    SELECT new map(
        o.status as status,
        COUNT(o.id) as count,
        AVG(o.amount) as avgAmount,
        SUM(o.amount) as totalAmount
    )
    FROM Order o
    GROUP BY o.status
""")
List<Map<String, Object>> getOrderStats();

// ✅ БД возвращает данные, приложение агрегирует
@Query("SELECT o FROM Order o")
List<Order> getAllOrders();

// В приложении
public Map<String, OrderStats> getOrderStats() {
    return getAllOrders().stream()
        .collect(Collectors.groupingBy(
            Order::getStatus,
            Collectors.collectingAndThen(
                Collectors.toList(),
                orders -> new OrderStats(
                    orders.size(),
                    orders.stream().mapToBigDecimal(Order::getAmount).average()
                )
            )
        ));
}

10. Реплики для чтения (Read Replicas)

// Маршрутизация чтения на реплику
@Service
public class UserService {
    
    @Autowired
    private UserRepositoryMaster masterRepo; // PRIMARY
    
    @Autowired
    private UserRepositorySlave slaveRepo;  // REPLICA
    
    public User getUserById(Long id) {
        // Чтение с реплики (не нагружаем PRIMARY)
        return slaveRepo.findById(id);
    }
    
    public void createUser(User user) {
        // Запись на PRIMARY
        masterRepo.save(user);
    }
}

11. Перемещение тяжёлых операций в фоновые задачи

// ❌ Блокирует HTTP запрос
@PostMapping("/orders")
public Order createOrder(@RequestBody CreateOrderRequest req) {
    Order order = orderService.create(req);
    sendAnalyticsData(order); // Медленная операция
    return order;
}

// ✅ Асинхронно
@PostMapping("/orders")
public Order createOrder(@RequestBody CreateOrderRequest req) {
    Order order = orderService.create(req);
    asyncService.sendAnalyticsDataAsync(order);
    return order;
}

@Service
public class AsyncService {
    @Async
    public void sendAnalyticsDataAsync(Order order) {
        sendAnalyticsData(order);
    }
}

12. Tune БД параметры для CPU

-- PostgreSQL (postgresql.conf)
shared_buffers = 256MB        # Увеличь буфер кэша
work_mem = 4MB                # Памяь для сортировки
effective_cache_size = 1GB    # Оптимизатор учитывает это
random_page_cost = 1.1        # Если SSD (не HDD)

-- MySQL (my.cnf)
innodb_buffer_pool_size = 1G
innodb_io_capacity = 2000

Чеклист оптимизации при CPU нагрузке

1. Анализируй запросы — EXPLAIN ANALYZE
2. Кэшируй результаты — Redis, в-памяти кэш
3. Оптимизируй индексы — удали неиспользуемые
4. Избегай функций в WHERE — используй диапазоны
5. Batch операции — saveAll вместо сохранения по одному
6. Используй replicas — чти с реплик
7. Денормализуй данные — материализованные представления
8. Партиционируй таблицы — big data
9. Async обработка — фоновые задачи
10. Monitor CPU — постоянно смотри на метрики

Выводы

1. CPU нагрузка в БД часто от неоптимальных запросов
2. Кэширование — первый шаг к оптимизации
3. EXPLAIN ANALYZE — твой друг для анализа
4. Асинхронность отправляет тяжёлые операции из критического пути
5. Replicas распределяют нагрузку на чтение