В последней версии HQBird Server 5.0.4 появились так называемые материализованные представления. В данном вебинаре мы поговорим о том, что это такое, как с ними работать, как вы можете использовать их у себя для ускорения некоторых аналитических выборок. Будет представлен небольшой бенчмарк, который показывает, как материализованные представления могут ускорять некоторые аналитические выборки.

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

Материализованные представления представляют собой гибрид представлений и стандартной постоянной таблицы.
Материализованные представления вы можете использовать только для чтения, то есть делать из них только SELECT. В них нельзя напрямую делать INSERT, UPDATE и DELETE.
Для обновления материализованных представлений используются специальные команды: REFRESH MATERIALIZED VIEW и REFRESH MATERIALIZED VIEW CONCURRENTLY. Это не DML-команды, а DDL, поэтому напрямую использовать их внутри PSQL нельзя.
Для материализованных представлений можно создавать индексы, в том числе и уникальные.
Материализованные представления можно преобразовать в обычные представления, и наоборот, обычные представления можно преобразовать в материализованные представления.
Функции автоматического обновления материализованных представлений при изменении базовых таблиц, как это, например, есть в опции ON COMMIT, у нас нет. Но, с другой стороны, хочу сказать, что даже в Oracle эта функция очень сильно ограничивает сами запросы, в которых такие штуки могут работать. То есть она работает далеко не всегда.

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

Если сравнить материализованное представление с обычными, то при выполнении запросов к обычному представлению этот запрос может быть медленным, если внутри представления запрос довольно-таки сложный, с агрегатами, множеством join’ов. К материализованному представлению, наоборот, запросы будут быстрыми, поскольку они выполняются к обычной таблице.

Обычные представления автоматически отслеживают любые изменения, потому что вы просто заново выполняете полностью запрос. Материализованные представления этого не делают. Чтобы получить новые данные в материализованных представлениях, необходимо их обновить с помощью команды REFRESH MATERIALIZED VIEW. Если для обычного представления вы можете написать триггеры и сделать к ним через INSERT, UPDATE, DELETE соответствующее обновление базовых таблиц, то для материализованных представлений такой возможности нет. Но зато для материализованных представлений вы можете создавать разные индексы.

Как мы жили раньше?

Чем заменяли раньше материализованные представления? Это можно было делать только вручную, написав довольно-таки много кода. Обычно создавалась обычная таблица с полями, идентичными по составу полям запроса и писалась хранимая процедура, которая обновляла эту таблицу. В HQBird есть также вариант, когда можно создавать таблицу с полями, идентичными запросу, с данными из этого запроса - специальный вид команды CREATE TABLE AS SELECT. Минусы этого подхода состоят в том, что очищать такую таблицу, если нужно её полностью обновить, очень дорого, т.к. это реальное выполнение команды DELETE с соответствующим порождением версий и мусора. Трудно следить за типами и составом столбцов таблиц - если вы решили добавить какое-то новое поле в такое псевдоматериализованное представление, вам нужно добавить его в таблицу, в запросе его добавить, в процедуре учесть обновление этих материализованных таблиц. Были и определённые плюсы - можно написать триггер для базовых таблиц, которые обновляют это псевдоматериализованное представление.

CREATE TABLE AS SELECT

CREATE [GLOBAL TEMPORARY] TABLE <table_name>

    [(column_name [, ...])]

	AS (<select>)

	[WITH [NO] DATA]

	[ON COMMIT PRESERVE | DELETE ROWS]

Сделаем небольшое отступление про CREATE TABLE AS SELECT. Она тоже появилась в HQBird Server 5.0.4 и позволяет создать таблицу с полями, идентичными по составу и типу полям, которые указаны в запросе SELECT. Можно создать эту таблицу с такими же полями, либо сразу её наполнить данными. Если по коммиту из временной таблицы данные удаляются, то данные её никогда не переносят. Но здесь есть определенный плюс - это быстрее, чем просто сделать CREATE и последующий INSERT INTO ... SELECT, потому что в данном случае у нас нет конкуренции за страницами. Там используется специальный вид вставки, так называемый BULK INSERT, когда страница выделяется в эксклюзивном режиме. Поскольку во время создания с этой таблицей никто не может ничего делать, то данные в неё заливаются гораздо быстрее. Индексы при этом не копируются. Копируются только типы полей и NOT NULL.

Сценарии использования материальных представлений

Теперь перейдём к самим представлениям. Для чего они могут использоваться? Для расчёта хранимых агрегатов по каким-то закрытым периодам, предварительных расчётов по редко изменяемым данным, для создания витрины для дальнейшего анализа. Можно делать либо каждый раз давая команду для обновления, либо написать сценарий, который по cron’у будет обновлять материализованные представления.

Синтаксис создания представлений

CREATE [OR ALTER] MATERIALIZED VIEW <view_name> [(columns_names)] 

  AS <query> 

  [WITH [NO] DATA]

  

ALTER MATERIALIZED VIEW <view_name> [(columns_names)] 

  AS <query> 

  [WITH [NO] DATA]

RECREATE MATERIALIZED VIEW <view_name> [(columns_names)] 

  AS <query> 

  [WITH [NO] DATA]

DROP VIEW <view_name>

По умолчанию используется WITH NO DATA!

Здесь указан синтаксис создания представлений:

В зависимости от предложения WITH DATA и WITH NO DATA, материализованные представления сразу будут содержать данные, либо они будут созданы без данных, а данные будут наполняться позже командой REFRESH MATERIALIZED VIEW. Тут также указано, что имена полей из запроса можно переопределять, если вы их укажете в списке столбцов, как для обычного представления.

VIEW <-> MATERIALIZED VIEW

ALTER MATERIALIZED VIEW <view_name> [(columns_names)] 

  AS <query> 

  TO NOT MATERIALIZED

ALTER VIEW <view_name> [(columns_names)] 

  AS <query> 

TO MATERIALIZED

Есть полезная такая вещь, как преобразование материализованного представления в обычное представление и, наоборот, преобразование обычного представления в материализованное. Для чего это может быть полезно? У вас, допустим, есть какое-то представление, которое долго считается. Вы решили попробовать его сделать материализованным и посмотреть, как оно отработает. Преобразуете, смотрите, понравилось, не понравилось, вернули обратно. Кроме того, вернуть обратно может помочь вам, если вы решили какую-то миграцию сделать куда-то где этих материализованных представлений нет. То есть вы преобразуете материализованное представление в обычное представление, делаете backup и restore и работаете на какой-то предыдущей версии, например, HQBird или на ванильной версии Firebird.

Синтаксис обновления данных

REFRESH MATERIALIZED VIEW <view_name> 

[CONCURRENTLY | DROP DATA] [CASCADE]

Есть несколько опций: CONCURRENTLY, DROP DATA, CASCADE. Сейчас я подробно о них расскажу, что они означают.

REFRESH MATERIALIZED VIEW <view_name>

Обычно, когда никаких опций не указано, команда REFRESH MATERIALIZED VIEW требует эксклюзивного доступа к материализованному представлению. Это значит, что в это время не должно быть никаких активных запросов к материализованному представлению. Только в этом случае можно его обновить. В противном случае будет выдана ошибка, что представление материализованное используется. Как оно выполняется? Деактивируются все индексы, которые созданы для материализованного представления. Удаляются все данные из этого представления,делается это очень быстро, похоже на TRUNCATE TABLE, то есть там просто странички помечаются свободными, поэтому это очень быстрый процесс. Далее таблица заполняется новыми данными. Опять же, это делается очень быстро, потому что в данном режиме нет конкуренции за страницами данных и они немного по-другому выделяются, тут такой же принцип, как CREATE TABLE AS SELECT и BULK INSERT. И далее, если на этом материализованном представлении были какие-то индексы, то они перестраиваются. Надо сказать, что этот способ является самым быстрым для обновления большого количества данных материализованного представления. Но, с другой стороны, оно требует эксклюзивного режима доступа.

REFRESH MATERIALIZED VIEW <view_name> CONCURRENTLY

Для устранения этого недостатка есть обновление материализованного представления в конкурентном доступе, то есть здесь добавляется ключевое слово CONCURRENTLY. Оно позволяет обновлять материализованное представление и в это время какие-то запросы, если начали работать с материализованным представлением, они просто работают со старыми данными. Такой режим требует, чтобы для материализованного представления обязательно был создан хотя бы один уникальный индекс. Лучше вообще, если он один, чтобы не запутать оптимизатор. Кроме того, эта команда обязательно требует, чтобы вы не стирали исходный запрос этого материализованного представления. Поскольку в данном режиме исходный запрос, который сделан для построения материализованного представления, он преобразуется в другие запросы, чтобы обновлять само материализованное представление. То есть внутри этой команды делается что-то подобное MERGE от запроса внутри материализованного представления к таблице, которая скрыта за кадром этого материализованного представления. Такой режим хорош, если необходимо обновить небольшое количество записей в материализованном представлении. И при этом с самим представлением ведётся активная работа, т.е. какие-то к нему есть SELECT-запросы.

REFRESH MATERIALIZED VIEW <view_name> DROP DATA

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

REFRESH MATERIALIZED VIEW … CASCADE

Ещё один режим – это добавление ключевого слова CASCADE. Обновление материализованного представления с ключевым словом CASCADE позволяет обновить не только данные в конкретном материализованном представлении, но и обновить данные в материализованных представлениях, которые задействованы в запросе текущего материализованного представления. То есть когда запрос для создания одного материализованного представления использует какие-то другие, одно или несколько материализованных представлений. Данная команда может использоваться совместно с ключевыми словами CONCURRENTLY или DROP DATA. Соответственно, CONCURRENTLY CASCADE требует уникального индекса для всех материализованных представлений, от которых зависят текущие материализованные представления и производится конкурентно, то есть не требует эксклюзивного доступа. DROP DATA CASCADE удаляет данные не только из текущего материализованного представления, но и из всех представлений, от которых зависит текущее. Соответственно, делает это быстро, но требует эксклюзивного доступа.

MATERIALIZED VIEW в GBAK

Как материализованные представления поддерживаются в GBAK? Естественно, сами данные материализованных представлений не попадают в резервную копию, но метаданные копируются. При восстановлении материализованное представление сначала восстанавливается по его метаданным, и в конце процесса восстановления запускается команда REFRESH MATERIALIZED VIEW CASCADE для всех материализованных представлений. В gbak также есть переключатель -NO_M(ATVIEWS), который позволяет не обновлять представления при восстановлении. То есть у вас база данных восстанавливается, метаданные для материализованного представления есть, но данных в них нет, вы потом можете просто в основной базе данных сделать REFRESH MATERIALIZED VIEW. В сервисах есть соответствующий параметр isc_spb_res_no_matviews

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

Практика. Остатки товаров

Теперь посмотрим на некий бенчмарк, который показывает, как можно использовать материализованные представления и скорость выполнения. Есть некоторая база данных, которая хранит приход и расход товара. То есть товары продаются, лежат на складе, и она за 10 лет. У нас 10 тысяч продуктов, и инвойсов 11 миллионов.

База данных хранит продажу и приход товара за 10 лет
Пример искусственный
PRODUCT – 10000 записей
INVOICE – 1100000 записей
INVOICE_LINE – 11545586 записей
PRODUCT_STORE – 4810985 записей

Она не очень большая, но достаточно для того, чтобы показать разницу в производительности.И вот у нас есть некоторый SQL-запрос, который рассчитывает текущие остатки по каждой позиции товара:

WITH

  T AS (

    SELECT S.PRODUCT_ID, SUM(S.QUANTITY) AS QUANTITY

    FROM PRODUCT_STORE S

    GROUP BY 1

    UNION ALL

    SELECT L.PRODUCT_ID, -SUM(L.QUANTITY) AS QUANTITY

    FROM INVOICE_LINE L

    GROUP BY 1

  ),

  REMAIN_PRODUCTS AS (

    SELECT PRODUCT_ID, SUM(QUANTITY) AS QUANTITY

    FROM T

    GROUP BY 1

  )

SELECT

  REMAIN_PRODUCTS.PRODUCT_ID,

  PRODUCT.NAME AS PRODUCT_NAME,

  REMAIN_PRODUCTS.QUANTITY

FROM

  REMAIN_PRODUCTS

  JOIN PRODUCT ON PRODUCT.PRODUCT_ID = REMAIN_PRODUCTS.PRODUCT_ID

ORDER BY PRODUCT.NAME;

Соответственно, сначала мы считаем все приходы, потом считаем все расходы по каждому товару и делаем разницу между количеством приходов и количеством расходов. Потом объединяем с товаром и смотрим сколько получилось. Естественно, этот запрос сильно упрощён, на практике остатки считаются обычно гораздо сложнее, но по производительности вы увидите разницу. То есть этот запрос на моём компьютере выполнялся 29 с небольшим секунд.

Соответственно, здесь показано, сколько он там чтений сделал, то есть он прочитал три таблицы целиком, пусть даже с индексированным доступом.

Практика. Остатки товаров. Как улучшить?

Теперь подумаем, как можно это делать лучше с помощью материализованных представлений. Мы можем сделать так - будем считать, что в текущем квартале у нас остатки меняются довольно-таки часто, и мы их будем считать. А за предыдущие кварталы будем считать, что остатки уже не будут меняться, и поэтому мы можем их хранить в материализованном представлении. И поэтому мы можем остатки на начало текущего квартала брать из материализованного представления и суммировать их с остатками, которые получаются за текущий квартал, и выводить их пользователю.

SQL для вычисления остатков на конец пред. квартала

WITH

  T AS (

    SELECT S.PRODUCT_ID, SUM(QUANTITY) AS QUANTITY

    FROM

      PRODUCT_STORE S

    WHERE S.DATE_OF_RECEIPT < FIRST_DAY(OF QUARTER FROM CURRENT_DATE)

    GROUP BY 1

    UNION ALL

    SELECT L.PRODUCT_ID, -SUM(L.QUANTITY) AS QUANTITY

    FROM

      INVOICE_LINE L

      JOIN INVOICE ON INVOICE.INVOICE_ID = L.INVOICE_ID

    WHERE INVOICE.CREATE_DATE < FIRST_DAY(OF QUARTER FROM CURRENT_DATE)

    GROUP BY 1

  )

SELECT

  T.PRODUCT_ID, SUM(T.QUANTITY) AS QUANTITY

FROM T

GROUP BY 1;

Здесь вот показан запрос, который вычисляет остатки на конец предыдущего квартала. Показана статистика выполнения этого запроса. Он выполняется за 23 с небольшим секунды

MATERIALIZED VIEW для хранения остатков на конец пред. квартала

CREATE MATERIALIZED VIEW V_REMAIN_PRODUCTS

AS

WITH

  T AS (

    SELECT S.PRODUCT_ID, SUM(QUANTITY) AS QUANTITY

    FROM PRODUCT_STORE S

    WHERE S.DATE_OF_RECEIPT < FIRST_DAY(OF QUARTER FROM CURRENT_DATE)

    GROUP BY 1

    UNION ALL

    SELECT L.PRODUCT_ID, -SUM(L.QUANTITY) AS QUANTITY

    FROM

      INVOICE_LINE L

      JOIN INVOICE ON INVOICE.INVOICE_ID = L.INVOICE_ID

    WHERE INVOICE.CREATE_DATE < FIRST_DAY(OF QUARTER FROM CURRENT_DATE)

    GROUP BY 1

  )

SELECT

  T.PRODUCT_ID, SUM(T.QUANTITY) AS QUANTITY

FROM T

GROUP BY 1 

WITH DATA -- создание с данными, если WITH NO DATA (умолчание), то без данных

;

Теперь создаём материализованные представления на основе этого запроса. Просто записываем CREATE MATERIALIZED VIEW, и далее вот этот запрос. После запроса вы можете указать, создается он с данными, либо без данных, с помощью предложения WITH DATA, WITH NO DATA.

Создание MATERIALIZED VIEW. СТАТИСТИКА

И здесь вот показаны две статистики. Если он без данных, естественно, там одни метаданные создаются, материализованные представления создаются там за 0,009 секунды, это очень быстро. А если с данными, то за 23 секунды, тут погрешность. Фактически с той же скоростью, как непосредственный запрос, который внутри материализованного представления. Соответственно, в статистике вы видите, что весь внутренний запрос был выполнен и 10 тысяч записей, то есть по каждому товару, было вставлено в материализованное представление.

MATERIALIZED VIEW для хранения остатков на конец пред. квартала

СОЗДАНИЕ ИНДЕКСА

CREATE UNIQUE INDEX IDX_V_REMAIN_PRODUCTS_PRODUCT_ID 

ON V_REMAIN_PRODUCTS(PRODUCT_ID);

Теперь создадим для материализованного представления уникальный индекс, он нам очень потребуется для конкурентного обновления плюс может быть полезен для запроса. Здесь вот показана статистика. Естественно, мы в качестве ключа выбрали PRODUCT_ID.

SQL ЗАПРОС ДЛЯ ВЫЧИСЛЕНИЯ ТЕКУЩИХ ОСТАТКОВ

WITH

  T AS (

    SELECT S.PRODUCT_ID, SUM(S.QUANTITY) AS QUANTITY

    FROM PRODUCT_STORE S

    WHERE S.DATE_OF_RECEIPT >= FIRST_DAY(OF QUARTER FROM CURRENT_DATE)

    GROUP BY 1

    UNION ALL

    SELECT L.PRODUCT_ID, -SUM(L.QUANTITY) AS QUANTITY

    FROM

      INVOICE_LINE L

      JOIN INVOICE ON INVOICE.INVOICE_ID = L.INVOICE_ID

    WHERE INVOICE.CREATE_DATE >= FIRST_DAY(OF QUARTER FROM CURRENT_DATE)

    GROUP BY 1

  ),

  REMAIN_PRODUCTS AS (

    SELECT PRODUCT_ID, SUM(QUANTITY) AS QUANTITY

    FROM T 

    GROUP BY 1

    UNION ALL

    SELECT PRODUCT_ID, QUANTITY

    FROM V_REMAIN_PRODUCTS

  ),

  T2 AS (SELECT PRODUCT_ID, SUM(QUANTITY) AS QUANTITY FROM REMAIN_PRODUCTS GROUP BY 1)

SELECT

  T2.PRODUCT_ID, PRODUCT.NAME AS PRODUCT_NAME, T2.QUANTITY

FROM T2 JOIN PRODUCT ON PRODUCT.PRODUCT_ID = T2.PRODUCT_ID

ORDER BY PRODUCT.NAME;

Теперь переписываем запрос, так чтобы часть его считалась с помощью материализованного представления, а часть — текущий квартал. То есть вот здесь вот в первом табличном выражении вычисляются остатки товара за текущий квартал,а далее идёт объединение остатков из материализованного представления, получается, на конец предыдущего квартала, с остатками текущих кварталов. И далее просто мы их суммируем по каждому продукту, эти остатки и выводим на экран. С материализованным представлением скорость выполнения такого запроса, то есть полностью остатки по всем 10 тысячам позиций, выполнили за 0,3 секунды.

Сравним производительность 29.398 sec vs 0.308 sec. Это ~ 95 раз быстрее.

Показана статистика,по каждой таблице уже читаем не все данные, а только те, которые у нас в текущем квартале и плюс 10 тысяч записей читаем из материализованного представления. То есть если сравнить 29 секунд и 0,3 секунды, это примерно в 95 раз быстрее.

ЭКСКЛЮЗИВНОЕ ОБНОВЛЕНИЕ MATERIALIZED VIEW

Теперь попробуем как-то обновить данные в материализованном представлении. То есть, допустим, у нас в какой-то одной базовой таблице мы взяли и обновили 1168 записей. И теперь мы можем выполнить команду REFRESH MATERIALIZED VIEW, и она выполняется, опять же, со скоростью внутреннего запроса, который в материализованном представлении указан.

То есть за 23 секунды вся эта команда выполняется. Тут важно учесть, что сколько бы вы данных на самом деле ни поменяли, команда REFRESH MATERIALIZED VIEW всегда перезальёт все данные,но сделает это достаточно быстро. Но эта команда требует эксклюзивного режима, то есть в это время, когда происходит обновление самого материализованного представления, с ним работать нельзя.

КОНКУРЕНТНОЕ ОБНОВЛЕНИЕ MATERIALIZED VIEW

Если необходимо конкурентное обновление, вы можете использовать REFRESH MATERIALIZED VIEW CONCURRENTLY.

Тут тоже мы как бы вернули эти остатки назад или попробовали его обновить. Эта команда выполняется чуть дольше, потому что внутри хоть это DDL-запрос, внутри выполняются некоторые DML-запросы, которые, собственно говоря, обновляют данные материализованного представления с помощью уникального индекса. Вот здесь показано, что мы же для одного продукта обновляли, а реальное обновление произошло одно. Но это выполнение немного дольше, чем полное обновление материализованного представления. Соответственно, чем больше данных вы поменяете в материализованном представлении, такое обновление может быть дольше. Зато оно позволяет работать с материализованным представлением, не выгоняя всех.

Если нужно удалить данные материализованного представления, собственно, выполняем команду REFRESH MATERIALIZED VIEW DROP DATA. Она выполняется, ну, тоже очень быстро, видите, за пять тысячных секунды, потому что реального DELETE там не выполняется, мусора никакого не будет, просто страницы помечаются свободными.

Вопросы?

Вопросы можете задать в техническую поддержку HQbird support@ibase.ru

Присоединяйтесь к сообществу СУБД FIrebird в Telegram и Max

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