Эталонная архитектура: хранилище данных / аналитика
Аналитические запросы (отчётность, агрегация, широкие сканирования) принципиально отличаются от OLTP-транзакций: их мало, но они очень широкие и долгие. Начиная с решения отделить аналитику от OLTP, это дерево пошагово проходит по таким компонентам, как метод загрузки (ETL vs ELT), колоночное хранение/columnstore, моделирование по схеме «звезда», секционирование + сжатие и предварительный расчёт с помощью материализованных представлений, взвешивая компромисс между параллелизмом и пропускной способностью.
Работает ли ваша аналитическая нагрузка в той же базе данных, что и OLTP?
Самая частая ошибка — запускать тяжёлые отчёты на рабочей транзакционной БД и ухудшать задержку OLTP. Первое решение — разделение: аналитика всё ещё работает на экземпляре OLTP, вы строите отдельную аналитическую среду или решаете, как перенести туда данные?
- Отчёты всё ещё работают на рабочей OLTP; они замедляют OLTP
- Отдельное хранилище есть; как загружать данные?
- Хранилище есть; запросы всё ещё медленные, как моделировать?
Где выполнять преобразование: до или после загрузки?
При переносе данных из источников в хранилище будете ли вы выполнять очистку/преобразование в отдельном уровне обработки до загрузки (ETL) или сначала загрузите сырые данные в хранилище и преобразуете их с помощью SQL внутри мощного движка хранилища (ELT)? Решение зависит от вычислительной мощности хранилища и потребности в гибкости схемы.
- Преобразование сложное/внешнее; выполнять до загрузки
- Хранилище мощное; загружать сырые данные, преобразовывать внутри с помощью SQL
Узкое место запросов — в структуре хранения или в модели данных?
Если аналитика медленная, есть два основных рычага. Широкие сканирования при построчном хранении читают всю строку; колоночное хранение читает только нужные столбцы и хорошо сжимается. Со стороны модели избыточная нормализация означает множество соединений, замедляющих запросы; схема «звезда» (факты + измерения) упрощает аналитику.
- Широкие агрегирующие сканирования медленные; читается мало столбцов
- Слишком много соединений, сложная нормализованная схема
- Один и тот же тяжёлый отчёт многократно даёт одинаковый результат
Рекомендация: отделите аналитику от OLTP
Транзакционная и аналитическая нагрузки душат друг друга на одной СУБД: долгие отчёты удерживают блокировки/IO и задерживают короткие транзакции. Вынос аналитики в отдельную цель (реплика для чтения или отдельное аналитическое хранилище) разгружает обе стороны.
Рекомендация: ETL (преобразование до загрузки)
В ETL данные извлекаются из источников (Extract), очищаются и преобразуются в отдельном уровне обработки (Transform) и только затем загружаются в хранилище (Load). В хранилище попадают только чистые данные, соответствующие модели.
Рекомендация: ELT (преобразование внутри хранилища)
В ELT сырые данные сначала загружаются в хранилище, а преобразование выполняется внутри собственного мощного SQL-движка хранилища. Поскольку у современных колоночных хранилищ высокая вычислительная мощность, вы перемещаете данные один раз и обрабатываете их внутри, а не несёте преобразование к данным.
Рекомендация: колоночное хранение / columnstore + сжатие
Аналитические запросы обычно сканируют несколько столбцов по всем строкам. Колоночное хранение держит данные по столбцам; оно читает только нужные столбцы, очень хорошо сжимает, так как похожие значения расположены рядом, и IO резко снижается.
Рекомендация: схема «звезда» (факты + измерения)
В аналитической модели в центре находится таблица фактов с мерами, окружённая описательными таблицами измерений. По сравнению с чрезмерно нормализованной схемой число соединений снижается, запросы упрощаются, а СУБД строит лучшие планы.
Рекомендация: предварительный расчёт с помощью материализованных представлений
Если одна и та же тяжёлая агрегация (дневная выручка, месячная сводка) выполняется многократно и даёт одинаковый результат, физически сохраните результат в материализованном представлении, а отчёты пусть читают заранее рассчитанный результат. Тяжёлое вычисление выполняется один раз, а запросы возвращаются мгновенно.