Может ли материализованное представление ускорить повторяющийся агрегатный запрос, если исходные таблицы часто изменяются?
Да. Материализованное представление хранит заранее вычисленный результат запроса, поэтому чтение агрегатов может обращаться к значительно меньшему объёму данных и использовать индексы уже по готовому результату. Цена ускорения — необходимость обновлять это представление и принимать возможную задержку между изменением исходных таблиц и обновлением результата.
Подход появился как ответ на проблему повторного выполнения дорогих операций над большими таблицами: соединений, группировок, сортировок и вычисления агрегатов. Обычное представление сохраняет только текст запроса, поэтому при каждом обращении оптимизатор снова строит план и читает исходные данные.
Материализованное представление сохраняет физический результат запроса. Это переносит часть вычислительной работы с момента чтения на момент создания или обновления результата и особенно полезно для отчётных и аналитических нагрузок.
Пусть отчёт каждый час считает продажи по клиентам за длительный период. При обычном запросе база снова читает большой объём заказов, выполняет фильтрацию, соединения и группировку, даже если с прошлого запуска изменилось немного строк.
Полное обновление материализованного представления может оказаться почти таким же дорогим, как исходный запрос. Инкрементальное обновление снижает эту цену, но требует поддержки изменений и обычно сложнее с точки зрения реализации и ограничений конкретной СУБД.
Материализованное представление хранит не определение, а набор строк результата. К нему можно создать обычные индексы, например по идентификатору клиента или периоду, поэтому запрос отчёта может читать уже агрегированные данные вместо исходных фактов.
Есть два основных режима актуализации:
Материализованное представление не гарантирует ускорение автоматически. Оптимизатор должен уметь выбрать его вместо исходных таблиц, либо запрос должен обращаться к нему явно; правила переписывания запросов различаются между СУБД.
Важны также транзакционная семантика и момент обновления. Если отчёт требует строго актуальных данных, отложенное обновление неприемлемо или должно быть явно учтено. Кроме того, представление занимает место, а его обновление конкурирует с обычными операциями записи и может требовать собственных индексов.
В интернет-магазине отчёт по выручке за день выполнял соединение заказов с товарами и группировку по магазину и категории. Запрос занимал минуты, хотя пользователи читали одни и те же агрегаты десятки раз в час.
Рассматривались три варианта. Увеличение числа индексов на исходных таблицах могло ускорить фильтрацию, но не устраняло повторные соединения и группировку. Кэширование результата на уровне приложения уменьшало нагрузку, однако усложняло инвалидацию и согласованность. Материализованное представление с обновлением после закрытия операционного периода перенесло вычисления в контролируемое окно.
Выбрали материализованное представление, потому что отчёт допускал задержку актуальности до завершения обновления. На представлении создали индекс по периоду и магазину; чтение отчёта стало обращаться к небольшому агрегированному набору, а основная OLTP-нагрузка перестала выполнять повторные группировки. Если бы требовались данные непосредственно после каждой записи, этот вариант пришлось бы заменить более частым или инкрементальным обновлением.
1. Чем материализованное представление отличается от обычного представления с индексом?
Обычное представление обычно не хранит результат: обращение к нему разворачивается в исходный запрос, после чего строится план чтения базовых таблиц. Материализованное представление хранит результат физически и потому требует отдельного жизненного цикла обновления. Наличие индексов на исходных таблицах не превращает обычное представление в материализованное.
2. Почему добавление индекса на материализованное представление не всегда ускоряет отчёт?
Индекс помогает только при подходящем предикате, порядке строк, соединении или покрытии нужных столбцов. Если запрос читает большую долю представления, оптимизатор может выбрать полное сканирование как более дешёвый вариант. Кроме того, каждый дополнительный индекс увеличивает объём хранения и стоимость обновления представления.
3. Как риск устаревших данных влияет на выбор этого подхода?
После изменения исходных таблиц материализованный результат может оставаться прежним до следующего обновления. Поэтому нужно определить допустимую задержку, способ обновления и поведение отчёта во время обновления. Если бизнес-требование — видеть каждую запись немедленно, материализованный результат без подходящего механизма синхронизации не должен использоваться как источник истины.