Оптимизация запросов в BI: как агрегаты и инкременты ускоряют аналитику

Когда аналитик ждёт ответа на простой вопрос - «Сколько продаж было в январе?» - и получает его только через 5 минут, это не просто раздражает. Это стоит компании деньги. В системах бизнес-аналитики (BI) медленные запросы - не проблема интерфейса, это проблема архитектуры. И чаще всего причина в том, что данные обрабатываются как будто они всё ещё лежат в операционной базе, а не в аналитическом хранилище. Правильная оптимизация запросов - это не про «нажать кнопку и всё станет быстрее». Это про понимание двух ключевых механизмов: агрегатов и инкрементов.

Агрегаты - это не просто SUM и COUNT

Когда вы пишете запрос с GROUP BY по месяцу, региону и продукту, и при этом считаете сумму выручки, среднее количество товаров и количество уникальных клиентов - вы используете агрегатные функции. Они мощные, но дорогие. Каждый GROUP BY требует сортировки, хеширования, хранения промежуточных результатов. Если в вашей таблице 50 миллионов строк, а вы группируете по 7 полям - система может потратить на это 10 минут. А если такой запрос запускается 10 раз в день - вы тратите 100 минут вычислительного времени ежедневно.

Решение? Материализованные агрегаты. Это не просто предварительно посчитанные значения. Это отдельные таблицы, которые обновляются в фоновом режиме. Например, вы создаёте таблицу sales_daily_aggr, где хранятся:

• дата
• регион
• категория продукта
• сумма выручки
• количество транзакций
• количество уникальных клиентов

Теперь вместо того, чтобы пересчитывать миллионы строк, BI-инструмент просто читает 50 тысяч записей. Это ускоряет запрос в 100-500 раз. В системах вроде Apache Impala или ClickHouse такие агрегаты можно строить автоматически через материализованные представления. Главное - не перегружать их. Не создавайте агрегаты для всех возможных комбинаций. Только для тех, которые реально запрашивают пользователи. Статистика показывает: 80% запросов приходится на 20% агрегатов. Найдите их - и вы освободите ресурсы для остальных.

Инкременты - не загружать всё заново

Представьте, что у вас есть таблица с продажами за последние 3 года. Каждый день в неё добавляется 200 тысяч новых строк. Если вы каждый день перезагружаете всю таблицу - вы тратите 600 миллионов операций ввода-вывода в день. А если вы загружаете только новые данные? Это называется инкрементная загрузка.

Как это работает? Просто:

1. Вы определяете маркер изменения - например, поле updated_at или batch_id.
2. В ETL-процессе вы берёте только те строки, где updated_at > last_load_time.
3. Вы обновляете не всю таблицу, а только партицию за сегодняшний день.

Партицирование - ключ. Если таблица разбита по дате (например, partition_by = sale_date), то система может обновить только одну партицию, а не сканировать всю базу. Это снижает нагрузку на хранилище и ускоряет ETL в разы. В Impala, PostgreSQL или Snowflake партицирование по дате - стандартная практика. Не используете его? Вы работаете в 2015 году.

Инкременты работают и с агрегатами. Если вы обновляете агрегатную таблицу только на основе новых данных - вы не пересчитываете всё с нуля. Вы просто добавляете новые значения к существующим. Это экономит не только время, но и память, и дисковое пространство.

Почему JOIN-ы убивают производительность

Вы когда-нибудь видели запрос, в котором участвуют 8 таблиц? Он выглядит как сложная схема метро. И работает как трамвай на льду. Каждый JOIN требует сопоставления строк между таблицами. Если вы JOIN-ите таблицу с 10 миллионами строк с таблицей в 5 миллионов - это 50 миллиардов сравнений. Даже если у вас есть индексы, это всё равно тяжело.

Правило простое: чем меньше JOIN-ов - тем быстрее запрос. Лучший способ избежать этого - предварительно объединить данные. Создайте одну широкую таблицу с необходимыми полями, а не храните их разрозненно. Это называется денормализация. В аналитике это не зло - это норма. В операционных системах мы избегаем дублирования данных. В аналитике - мы его поощряем. Если вы часто запрашиваете продажи + клиенты + регионы + продукты - объедините их в одну таблицу. Сделайте её партиционированной. И обновляйте инкрементально. Вы сократите JOIN-ы с 7 до 0.

Индексы - не панацея, но они важны

Многие думают: «Поставлю индекс - и всё будет быстро». Это не так. Индекс - это как книга с оглавлением. Он ускоряет поиск, если вы ищете по первому столбцу. Но если вы ищете по второму - оглавление бесполезно. Например, у вас составной индекс (region, product, date). Запросы по region или region + product будут быстрыми. А запрос по product - нет. Вы не сможете найти все продажи «iPhone» без указания региона - потому что индекс не устроен так, чтобы искать в середине.

Ещё одна ловушка - фильтрация по LIKE '%abc'. Это не использует индекс. А LIKE 'abc%' - использует. Почему? Потому что индекс упорядочен по началу строки. Это важно знать, когда пишете запросы в BI-инструментах, где фильтры часто генерируются автоматически.

Не создавайте индексы на каждом столбце. Они замедляют запись. Каждый индекс нужно обновлять при INSERT/UPDATE. Их нужно поддерживать. Лучше использовать фильтрованные индексы - то есть индексы, которые покрывают только нужные строки. Например, индекс на sale_date, но только для продаж с status = 'completed'. Такой индекс меньше, быстрее и дешевле в поддержке.

Почему N+1 - это катастрофа

Это не только проблема разработчиков. Это проблема аналитиков. Представьте, что вы запрашиваете список клиентов, у которых были покупки за последний месяц. Потом для каждого клиента вы запрашиваете его последние 3 покупки. Если у вас 10 000 клиентов - вы делаете 10 001 запрос. Один - чтобы получить клиентов. И ещё 10 000 - чтобы получить их покупки. Это называется N+1 проблема.

В BI-системах это проявляется, когда вы используете инструменты вроде Power BI или Tableau, которые подгружают связанные данные по мере необходимости. Если вы не настроили предварительную загрузку (eager loading), система будет делать кучу мелких запросов к базе. Это создаёт лавину сетевого трафика и перегружает сервер.

Решение? Используйте предварительную загрузку. В SQL это делается через JOIN. В BI-инструментах - через настройку связей между таблицами. Всегда загружайте все необходимые данные одним запросом. Не полагайтесь на «ленивую» загрузку. Она удобна для разработчиков, но убивает производительность BI-системы.

Что ещё замедляет запросы

• ORDER BY без индекса - сортировка больших таблиц требует полного сканирования и сортировки в памяти. Если вы сортируете по дате - убедитесь, что есть индекс по этой колонке.
• DISTINCT на большом количестве полей - это почти то же самое, что GROUP BY. Он требует хеширования всех комбинаций. Если вы используете DISTINCT для удаления дубликатов - подумайте: а нельзя ли сделать это на уровне загрузки данных?
• Фильтры в конце - если вы сначала JOIN-ите 5 таблиц, а потом фильтруете по дате - вы обработали 100 миллионов строк, чтобы потом отбросить 99 миллионов. Фильтруйте как можно раньше. Всегда.

Как проверить, что работает

Вы не можете оптимизировать то, что не измеряете. Используйте план выполнения запроса. В Impala - это EXPLAIN. В PostgreSQL - EXPLAIN ANALYZE. В SQL Server - план выполнения в SSMS. Смотрите на:

• Сканирование таблиц (Table Scan) - это плохо. Индексный поиск (Index Seek) - хорошо.
• Сколько строк обработано? Если 100 миллионов, а вы ожидаете 10 тысяч - вы что-то делаете не так.
• Сколько времени занимает каждый этап? Если JOIN занимает 80% времени - значит, вам нужно его упростить.

Мониторьте статистику выполнения запросов в BI-системе. Как часто запускаются самые медленные запросы? Какие из них используют больше всего ресурсов? Сделайте топ-10. Оптимизируйте их первыми. Итеративный процесс: измерить - оптимизировать - измерить снова.

Простое правило для старта

Если вы только начинаете оптимизировать BI-систему - сделайте это:

1. Определите 5 самых часто запускаемых запросов.
2. Создайте для них материализованные агрегаты.
3. Разбейте исходные таблицы по дате (партицирование).
4. Настройте инкрементную загрузку данных.
5. Уберите все JOIN-ы, которые можно заменить денормализацией.

Эти пять шагов дадут вам 70-90% прироста производительности. Без сложных настроек. Без переписывания всей системы. Просто - понимание того, что аналитика - это не про «всё сразу», а про «только нужное, в нужное время».