👩‍💻 Материализованные представления в SQL — погружаемся глубже в теорию

Привет, ранее уже базово затрагивал темы обычных представлений и материализованных, однако, сегодня словил желание остановится на MView по подробнее.

🧊 Что такое материализованное представление?
Материализованное представление (Materialized View) — это реальный объект базы данных, содержащий предрасчитанные данные.

В отличие от обычного VIEW, здесь:
➖Результат запроса сохраняется как отдельная таблица;
➖При чтении SELECT возвращает готовые, быстро читаемые данные;
➖Данные не обновляются автоматически (кроме некоторых случаев, например Oracle или с триггерами);
➖В основном требует явного ручного обновления — REFRESH.

🧊 Архитектурно материализованное представление — это:
➖ Текст SQL-запроса,
➖ Физическая таблица,
➖ Метаданные для обновления

🧊 Ключевые особенности
➖ Сохраняет данные — Да
➖ Обновляется автоматически — Нет (нужно REFRESH), за исключением случаев, которые рассмотрим ниже.
➖ Быстрое выполнение SELECT — Да
➖ Требует места на диске — Да

🧊 Общий синтаксис для создания материализованного представления:

CREATE MATERIALIZED VIEW имя_представления AS
SELECT ...
FROM ...
[WHERE ...]
[GROUP BY ...]
[ORDER BY ...];

Дополнительно можно указать:
WITH NO DATA — представление создаётся, но не заполняется (нужно REFRESH).
WITH DATA (по умолчанию) — данные вычисляются сразу при создании.

🧊 Допустим, у нас есть таблица orders, в которой хранятся заказы клиентов. Она может содержать такие поля:

-----------------------------
id            SERIAL
order_date    TIMESTAMP
customer_id   INT
amount        NUMERIC

Таблица обновляется постоянно: пользователи делают заказы, и каждая строка — это один заказ.

Часто требуется строить отчёты:
➖ Сколько заказов было каждый день
➖ Какова средняя сумма заказа за день

Запрос к такой таблице может быть тяжёлым, особенно если в orders миллионы строк. Поэтому создаём материализованное представление — заранее рассчитанный агрегат, чтобы анализировать среднюю сумму заказов по дням:

CREATE MATERIALIZED VIEW daily_avg_orders AS
SELECT
  order_date::date AS day,
  COUNT(*) AS total_orders,
  AVG(amount) AS avg_order_value
FROM orders
GROUP BY day
ORDER BY day;

Что делает это материализованное представление:
После выполнения CREATE MATERIALIZED VIEW — СУБД физически сохраняет результат запроса. Теперь мы можем быстро выполнять:

SELECT * FROM daily_avg_orders;

И получать результат без перерасчёта данных каждый раз.

🛠 Обновление (refresh)
Чтобы MView оставалось актуальным, его нужно обновлять вручную или автоматически.
При ручном обновлении, чтобы обновить данные в представлении после изменений в таблице orders, выполняем:

REFRESH MATERIALIZED VIEW daily_avg_orders;

Если при создании добавили WITH NO DATA — первый REFRESH обязателен.

Можно настроить автообновление через:
➖ Планировщик задач (например, cron);
➖ Расписание в СУБД (например, PostgreSQL pg_cron, Oracle DBMS_SCHEDULER);
➖ Использование триггеров или CDC

⚙️ Индексы и уникальность
Во многих СУБД материализованные представления поддерживают индексы, особенно если нужно обеспечить уникальность:

CREATE UNIQUE INDEX ON имя_представления(ключ);

Для того чтобы представление можно было обновлять инкрементально (а не полностью), часто требуется уникальный ключ.

🖥 Поддержка материализованных представлений в разных СУБД:
➖ PostgreSQL — Полноценная, ручное обновление
➖ Oracle — Продвинутая (есть автообновление, query rewrite)
➖ SQL Server — Аналог: Indexed Views
➖ MySQL — поддержки нет ❌
➖ ClickHouse — Аналог через MATERIALIZED VIEW с INSERT
➖ BigQuery — Поддержка с автообновлением

👾 Где это особенно нужно?
➖ Сложные отчёты (много JOIN, WHERE, GROUP BY) — хранит результат и не пересчитывает каждый раз
➖ BI и дашборды — быстрые ответы
➖ Разгрузка сервера — меньше нагрузки на исходные таблицы
➖ Разделение хранилищ — можно экспортировать MView в другие слои данных
➖ Медленные источники данных — данные приходят редко, но запросы часты.

Так что, материализуемся 🥁

#Views #Materialized_views #SQL

📱 Подписаться на канал | Курс автора по SQL DDL