Урок 9.6 · Время чтения: ~9 мин

Материализованное представление (materialized view) — это представление, результат которого хранится на диске, как таблица: запрос выполняется один раз, а при чтении возвращаются сохраненные строки, без повторного расчета. Тяжелые отчеты становятся быстрыми, но данные в них актуальны только на момент последнего обновления. В этом уроке вы научитесь создавать, читать и обновлять материализованные представления в PostgreSQL, узнаете, какие СУБД их поддерживают, и соберете то же самое в MySQL с помощью сводной таблицы.

Материализованные представления в SQL

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

Материализованное представление решает эту проблему: оно сохраняет результат. Примеры построены на схеме Sakila в синтаксисе PostgreSQL; поведение проверено на PostgreSQL 17, а часть про MySQL — на MySQL 8.0.

Что такое материализованное представление

Материализованное представление — это именованный запрос и сохраненная копия его результата. Чтение идет из копии, поэтому оно такое же быстрое, как чтение небольшой таблицы. Копия не обновляется сама: когда исходные таблицы меняются, материализованное представление хранит старые данные, пока вы его не обновите.

Создание материализованного представления

Синтаксис такой же, как у представления, со словом MATERIALIZED:

CREATE MATERIALIZED VIEW category_revenue AS
SELECT c.name AS category, COUNT(*) AS rentals, SUM(p.amount) AS revenue
FROM payment AS p
JOIN rental AS r ON r.rental_id = p.rental_id
JOIN inventory AS i ON i.inventory_id = r.inventory_id
JOIN film_category AS fc ON fc.film_id = i.film_id
JOIN category AS c ON c.category_id = fc.category_id
GROUP BY c.name;

Результат: PostgreSQL выполняет запрос один раз и сохраняет 16 строк — по одной на категорию.

Теперь к нему обращаются как к таблице:

SELECT category, rentals, revenue
FROM category_revenue
ORDER BY revenue DESC
LIMIT 5;

Результат: Sports (1179 аренд, 5314.21), Sci-Fi (4756.98), Animation (4656.30), Drama (4587.39), Comedy (4383.58). Пять соединений повторно не выполняются: строки берутся из сохраненного результата.

Чтобы создать объект, не выполняя запрос, добавьте WITH NO DATA. Такое представление нельзя читать до первого обновления: SELECT завершится ошибкой «materialized view has not been populated».

Материализованное представление доступно только для чтения. INSERT INTO category_revenue … завершится ошибкой «cannot change materialized view». Его данные меняются только при обновлении.

Обновление данных

Когда приходят новые платежи, материализованное представление по-прежнему показывает старые итоги. Чтобы пересчитать его:

REFRESH MATERIALIZED VIEW category_revenue;

Результат: запрос выполняется заново и заменяет сохраненные строки. До этой команды новый платеж в представлении не появляется вовсе.

Обычный REFRESH блокирует материализованное представление: запросы, которые его читают, ждут окончания обновления. Для представления, которое читают постоянно, используйте CONCURRENTLY. Тогда новый результат строится рядом со старым и подменяются только изменившиеся строки, поэтому чтение не блокируется:

CREATE UNIQUE INDEX category_revenue_category_idx ON category_revenue (category);

REFRESH MATERIALIZED VIEW CONCURRENTLY category_revenue;

Для CONCURRENTLY нужен уникальный индекс на материализованном представлении; без него команда завершится ошибкой «cannot refresh materialized view concurrently». Кроме того, такое обновление медленнее обычного, поэтому применяйте его только там, где блокировка чтения — проблема.

Как часто обновлять, зависит от задачи: раз в сутки для дневного отчета, раз в несколько минут для дашборда. Обновление запускает планировщик, например cron или расширение pg_cron.

Индексы и удаление

Материализованное представление хранится как таблица, поэтому на нем можно создавать обычные индексы — у обычного представления это невозможно. Индексы подробно разбираются в модуле 10.

Удаление:

DROP MATERIALIZED VIEW category_revenue;

Список материализованных представлений в PostgreSQL — в pg_matviews; столбец ispopulated показывает, заполнено ли представление.

Поддержка в разных СУБД

В MySQL CREATE MATERIALIZED VIEW — синтаксическая ошибка.

Материализованное представление в MySQL: сводная таблица

В MySQL тот же результат хранят в обычной таблице, которую пересобирают сами:

CREATE TABLE category_revenue AS
SELECT c.name AS category, COUNT(*) AS rentals, SUM(p.amount) AS revenue
FROM payment AS p
JOIN rental AS r ON r.rental_id = p.rental_id
JOIN inventory AS i ON i.inventory_id = r.inventory_id
JOIN film_category AS fc ON fc.film_id = i.film_id
JOIN category AS c ON c.category_id = fc.category_id
GROUP BY c.name;

Чтобы обновить ее и не оставить читающих с пустой таблицей, заполните новую копию и поменяйте таблицы местами одной командой:

CREATE TABLE category_revenue_new LIKE category_revenue;

INSERT INTO category_revenue_new
SELECT c.name, COUNT(*), SUM(p.amount)
FROM payment AS p
JOIN rental AS r ON r.rental_id = p.rental_id
JOIN inventory AS i ON i.inventory_id = r.inventory_id
JOIN film_category AS fc ON fc.film_id = i.film_id
JOIN category AS c ON c.category_id = fc.category_id
GROUP BY c.name;

RENAME TABLE category_revenue TO category_revenue_old,
             category_revenue_new TO category_revenue;

DROP TABLE category_revenue_old;

Результат: RENAME TABLE меняет обе таблицы одновременно, поэтому запросы видят либо старые, либо новые данные, но никогда не пустую таблицу.

Для небольшой таблицы подойдет и DELETE вместе с INSERT … SELECT в одной транзакции. TRUNCATE здесь не используйте: в MySQL он выполняет неявный COMMIT, и читающие увидят пустую таблицу (см. урок 8.4).

Обновление запускают по расписанию планировщиком событий MySQL (CREATE EVENT … ON SCHEDULE EVERY 1 HOUR) или внешним заданием cron.

Когда использовать материализованное представление

Подходит:

• тяжелые отчеты и дашборды, которые соединяют много таблиц и агрегируют много строк;
• данные, которые могут отставать на минуты или часы: продажи за день, статистика за месяц, рейтинги;
• результаты, которые читают многие, а меняют редко.

Не подходит:

• данные, которые всегда должны быть актуальными, например остаток на счете или наличие товара;
• исходные таблицы, которые меняются постоянно, — представление пришлось бы обновлять без перерыва;
• простые быстрые запросы: хватит обычного представления или самого запроса.

Ключевые выводы этого урока:

• Материализованное представление хранит результат своего запроса; при чтении возвращаются сохраненные строки без выполнения запроса.
• Данные актуальны на момент последнего REFRESH MATERIALIZED VIEW; в PostgreSQL ничего не обновляется автоматически.
• REFRESH … CONCURRENTLY не блокирует чтение, но требует уникального индекса на материализованном представлении.
• Материализованное представление доступно только для чтения и может иметь собственные индексы.
• В MySQL и MariaDB материализованных представлений нет: используйте сводную таблицу и меняйте ее через RENAME TABLE.
• Применяйте их для тяжелых отчетов, которым допустимо небольшое отставание, а не для значений, которые должны быть актуальными.

Часто задаваемые вопросы

Чем представление отличается от материализованного представления?

Представление хранит только запрос и выполняет его при каждом чтении, поэтому данные всегда актуальны. Материализованное представление хранит результат запроса: чтение быстрое, но данные — на момент последнего обновления.

Обновляется ли материализованное представление автоматически?

В PostgreSQL нет: вы запускаете REFRESH MATERIALIZED VIEW сами или по расписанию. Oracle умеет обновлять при фиксации транзакции, а индексированные представления SQL Server поддерживаются автоматически при каждом изменении.

Поддерживает ли MySQL материализованные представления?

Нет. В MySQL и MariaDB есть только обычные представления. Обычная замена — сводная таблица, которую заполняют через INSERT … SELECT и пересобирают по расписанию.

Можно ли создать индекс на материализованном представлении?

Да. Материализованное представление хранится как таблица, поэтому его можно индексировать, а для REFRESH … CONCURRENTLY уникальный индекс обязателен.

Вопросы на собеседовании

Когда вы выберете материализованное представление вместо обычного?

Когда запрос дорогой, его результат читают часто, а данные могут немного отставать: отчеты, дашборды, агрегированная статистика. Обычное представление лучше, если данные всегда должны быть актуальными или запрос дешевый.

Что делает REFRESH MATERIALIZED VIEW CONCURRENTLY и что для него нужно?

Пересчитывает результат, не блокируя читающих: новый результат строится рядом со старым, и применяются только различия. Нужен уникальный индекс на материализованном представлении; такое обновление медленнее обычного.

Как реализовать материализованное представление в MySQL?

Создать сводную таблицу через CREATE TABLE … AS SELECT. Для обновления заполнить новую копию и поменять таблицы через RENAME TABLE, а обновление запускать планировщиком событий или cron.