Как ускорить массивный аналитический запрос

Оптимизация массивных аналитических запросов

Массивные аналитические запросы — это частая боль в production системах. Они сканируют большие объёмы данных и могут блокировать всю базу. Вот комплексный подход к их ускорению.

1. Оптимизация на уровне SQL

Анализ плана выполнения:

EXPLAIN ANALYZE
SELECT u.id, u.name, COUNT(o.id) as orders_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL 1 YEAR
GROUP BY u.id, u.name;

Ищи Sequential Scan на больших таблицах — это часто узкое место.

Добавление индексов:

CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_users_created_status ON users(created_at, status);
CREATE INDEX idx_users_active ON users(id) WHERE status = 'active';

2. Партиционирование данных

Для таблиц > 10 ГБ раздели по диапазонам:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    created_at TIMESTAMP,
    amount DECIMAL
) PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p_2022 VALUES LESS THAN (2023),
    PARTITION p_2023 VALUES LESS THAN (2024),
    PARTITION p_2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

3. Агрегация и денормализация

Pre-aggregated таблицы:

CREATE TABLE daily_stats AS
SELECT 
    DATE(created_at) as date,
    user_id,
    COUNT(*) as orders_count,
    SUM(amount) as total_amount
FROM orders
GROUP BY DATE(created_at), user_id;

CREATE INDEX idx_daily_stats_date ON daily_stats(date);

4. Пейджинация в Java

Правильно используй ScrollableResults:

Query query = session.createQuery("SELECT u FROM User u");
ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);

while (results.next()) {
    User user = (User) results.get(0);
    processUser(user);
}
results.close();

Или обычная пейджинация:

public List<User> getUsers(int pageNumber, int pageSize) {
    return session.createQuery("SELECT u FROM User u", User.class)
        .setFirstResult(pageNumber * pageSize)
        .setMaxResults(pageSize)
        .list();
}

5. Кэширование результатов

Hibernate query cache:

public List<User> getActiveUsers() {
    return session.createQuery(
        "SELECT u FROM User u WHERE u.status = 'active'",
        User.class
    )
    .setCacheable(true)
    .setCacheRegion("users_cache")
    .setFetchSize(500)
    .list();
}

Spring кэширование:

@Cacheable(value = "users", key = "'active'") 
public List<User> getActiveUsers() {
    return userRepository.findByStatus("active");
}

6. Асинхронная обработка

Для отчётов, которые можно считать фоном:

@Async
public CompletableFuture<Report> generateReport() {
    List<AnalyticsData> data = analyticsRepository.findAll();
    Report report = process(data);
    return CompletableFuture.completedFuture(report);
}

7. Batch processing

Для обработки больших объёмов:

int batchSize = 1000;
int processed = 0;

ScrollableResults results = session.createQuery(
    "SELECT u FROM User u"
).scroll(ScrollMode.FORWARD_ONLY);

while (results.next()) {
    User user = (User) results.get(0);
    processUser(user);
    
    if (++processed % batchSize == 0) {
        session.flush();
        session.clear();
    }
}

8. Альтернативные решения

• ClickHouse — колончатая БД для аналитики
• Apache Druid — real-time аналитика
• Snowflake / Redshift — облачные warehouse

Итого

✅ Оптимизировать SQL (индексы, EXPLAIN ANALYZE) ✅ Использовать пейджинацию для больших результатов ✅ Кэшировать результаты часто запрашиваемых данных ✅ Асинхронная обработка для длительных операций ✅ Batch processing для обновления больших объёмов ✅ Рассмотреть выделенное хранилище для аналитики

Эффективная аналитика — это комбинация хороших индексов, правильной архитектуры и использования памяти и кэша.