Когда вы создаёте обычное представление (разобрал ранее в посте) в SQL, вы, по сути, сохраняете тело запроса, но не его результат. Каждый раз при обращении к такому представлению СУБД выполняет запрос заново, извлекая данные из указанных таблиц. Это гарантирует актуальность, но процесс может быть медленным, особенно если запрос тяжёлый и работает с большим количеством данных.
Чтобы ускорить работу, особенно для выполнения аналитики и подготовки отчётности, придумали материализованные представления.
🔍 Что такое материализованное представление?
Это объект базы данных, который сохраняет результат выполнения SQL-запроса — то есть не только структуру, но и сами данные. Он существует как полноценная таблица с данными, которые можно быстро читать без перерасчёта.
Разумеется, это порождает главный компромисс: такие представления не обновляются автоматически. Если в исходных таблицах изменились данные, то материализованное представление останется устаревшим, пока вы его не обновите вручную или по расписанию.
🧠 Теория: зачем нужны материализованные представления?
Материализованные представления применяются, когда:
1. Данные редко меняются, но часто читаются.
2. Запросы сложные и требуют много ресурсов.
3. Нужно сократить время ответа в BI-системах, аналитике, отчётах.
4. Вы строите ETL/ELT пайплайн и хотите сохранить промежуточные агрегаты.
5. Нужен кэш сложного запроса, особенно с объединениями и агрегациями.
Их можно рассматривать как оптимизированные, частично "ручные" кеши внутри базы данных.
Пример: агрегаты по продажам
Исходная таблица:
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
product_id INT,
quantity INT,
total NUMERIC,
sale_date DATE
);
Создаём материализованное представление (CREATE MATERIALIZED VIEW имя_представления), в котором храним сумму продаж и количество товаров по каждому продукту и месяцу:
CREATE MATERIALIZED VIEW sales_summary AS
SELECT
product_id,
DATE_TRUNC('month', sale_date) AS month,
SUM(quantity) AS total_quantity,
SUM(total) AS total_revenue
FROM sales
GROUP BY product_id, DATE_TRUNC('month', sale_date);
Теперь можно обращаться к sales_summary, как к обычной таблице — всё уже посчитано и лежит в готовом виде.
🔄 Обновление данных
Так как данные в материализованном представлении со временем устаревают, их нужно обновлять вручную:
REFRESH MATERIALIZED VIEW sales_summary;
Если вы не хотите блокировать чтение из представления во время обновления (например, в боевой системе), используйте:
REFRESH MATERIALIZED VIEW CONCURRENTLY sales_summary;
Но этот способ требует уникального индекса на представлении. Также параллельные SELECT операции просто получают старую версию представления до завершения обновления, после чего подменяется новая копия.
⚙️ Технические особенности
1. Нельзя использовать функции, которые дают разные результаты при каждом вызове: NOW(), RANDOM(), CURRENT_USER и пр.
2. В представление нельзя передавать параметры (например, даты).
3. Представление ведёт себя как таблица: в нём есть строки и столбцы, и оно занимает место на диске.
4. Можно создавать индексы на материализованном представлении, как на обычной таблице — это даёт дополнительный прирост производительности.
5. Обновление может быть затратным по времени — особенно если исходные таблицы большие и часто меняются.
🔧 Поддержка в разных СУБД
1. В PostgreSQL материализованные представления поддерживаются нативно. Можно использовать REFRESH и CONCURRENTLY.
2. В Oracle поддержка есть с 8 версии, и она даже продвинутая — позволяет настраивать автоматическое обновление по расписанию.
3. В SQL Server их нет как отдельного объекта, но похожее поведение обеспечивают Indexed Views.
4. В MySQL и SQLite — отсутствуют, но можно эмулировать, создавая таблицу + триггеры или процедуры обновления.
#Views #Materialized_views #SQL
📱 Подписаться на канал | Курс автора по SQL DDL