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

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

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

Выделенный пул SQL в Azure Synapse поддерживает стандартные и материализованные представления. Оба — это виртуальные таблицы, созданные с помощью выражений SELECT и представленные для запросов в виде логических таблиц. Представления инкапсулируют сложность общих вычислений данных и добавляют слой абстракции для вычислений изменений, поэтому не требуется переписать запросы.

Стандартное представление вычисляет свои данные каждый раз при использовании представления. Нет данных, хранящихся на диске. Обычно люди используют стандартные представления в качестве инструмента, помогающего упорядочивать логические объекты и запросы в выделенном пуле SQL. Чтобы использовать стандартное представление, запрос должен сделать прямую ссылку на него.

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

Большинство требований к стандартному представлению по-прежнему применяются к материализованному представлению. Дополнительные сведения о синтаксисе материализованного представления и других требованиях см. в статье CREATE MATERIALIZED VIEW AS SELECT

Преимущества использования материализованных представлений

Правильно спроектированное материализованное представление предоставляет следующие преимущества.

• Сокращение времени выполнения сложных запросов с операторами JOIN и агрегатными функциями. Чем сложнее запрос, тем потенциально больше времени удастся сэкономить. Наибольшее преимущество достигается, когда вычислительные затраты запроса высоки, а результирующий набор данных мал.
• Оптимизатор в выделенном пуле SQL может автоматически использовать развернутые материализованные представления для улучшения планов выполнения запросов. Этот процесс является прозрачным для пользователей, обеспечивает более высокую производительность запросов и не требует указания прямой ссылки на материализованные представления.
• Требуется низкое обслуживание представлений. Все добавочные изменения данных из базовых таблиц автоматически добавляются в материализованные представления синхронно, что означает, что базовые таблицы и материализованные представления обновляются в одной транзакции. Такая схема позволяет запросам из материализованных представлений возвращать те же данные, что и при непосредственном обращении к базовым таблицам.
• Данные в материализованном представлении могут распределяться иначе, чем в базовых таблицах.
• Данные в материализованных представлениях получают те же преимущества высокой доступности и устойчивости, что и данные в обычных таблицах.

Материализованные представления, реализованные в выделенном пуле SQL, также обеспечивают следующие преимущества:

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

• Широкая поддержка агрегатных функций. См. статью CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL).
• Поддержка рекомендаций по материализованным представлениям для конкретных запросов. См. статью EXPLAIN (Transact-SQL).
• Автоматическое и синхронное обновление данных при их изменении в базовых таблицах. Вмешательство пользователя не требуется.

Распространенные сценарии

Материализованные представления обычно используются в следующих сценариях:

Необходимо повысить производительность сложных аналитических запросов при больших объёмах данных

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

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

Требуется более быстрая производительность без изменений или минимальных изменений запросов

Изменения схемы и запросов в выделенных пулах SQL обычно сведены к минимуму для поддержки регулярных операций ETL и составления отчетов. Материализованные представления могут использоваться для настройки производительности запросов, если затраты, связанные с представлениями, могут быть компенсированы улучшением производительности запросов.

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

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

Требуется другая стратегия распределения данных для ускорения производительности запросов

Выделенный пул SQL — это система распределенной обработки запросов. Данные в таблице SQL распределены до 60 узлов с помощью одной из трех стратегий распределения (хэш, round_robin или репликация).

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

Руководство по проектированию

Ниже приведены общие рекомендации по использованию материализованных представлений для повышения производительности запросов:

Проектирование рабочей нагрузки

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

Пользователи могут запускать EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> для материализованных представлений, рекомендуемых оптимизатором запросов. Так как эти рекомендации зависят от запросов, материализованное представление, которое дает преимущество одному запросу, может не быть оптимальным для других запросов в той же рабочей нагрузке.

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

Помните о компромиссе между более быстрыми запросами и затратами

Для каждого материализованного представления есть затраты на хранение данных и затраты на обслуживание представления. По мере изменения данных в базовых таблицах размер материализованного представления увеличивается, а его физическая структура также изменяется. Чтобы избежать снижения производительности запросов, каждое материализованное представление сохраняется отдельно подсистемой SQL.

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

Этот запрос можно запустить для создания списка материализованных представлений в выделенном пуле SQL:

SELECT V.name as materialized_view, V.object_id
FROM sys.views V
JOIN sys.indexes I ON V.object_id= I.object_id AND I.index_id < 2;

Варианты для уменьшения количества материализованных представлений:

• Определите распространенные наборы данных, часто используемые сложными запросами в рабочей нагрузке. Создайте материализованные представления для хранения этих наборов данных, чтобы оптимизатор может использовать их в качестве стандартных блоков при создании планов выполнения.

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

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

-- Query 1 would benefit from having a materialized view created with this SELECT statement

SELECT A, SUM(B)
FROM T
GROUP BY A

-- Query 2 would benefit from having a materialized view created with this SELECT statement

SELECT C, SUM(D)
FROM T
GROUP BY C

-- You could create a single materialized view of this form

SELECT A, C, SUM(B), SUM(D)
FROM T
GROUP BY A, C

Не все настройки производительности требуют изменения запроса

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

Мониторинг материализованных представлений

Материализованное представление хранится в выделенном пуле SQL так же, как таблица с кластеризованным индексом columnstore (CCI). Чтение данных из материализованного представления включает сканирование сегментов индекса CCI и применение любых добавочных изменений из базовых таблиц. Если число добавочных изменений слишком велико, разрешение запроса из материализованного представления может занять больше времени, чем напрямую запрашивать базовые таблицы.

Чтобы избежать снижения производительности запросов, рекомендуется запускать PDW_SHOWMATERIALIZEDVIEWOVERHEAD DBCC для мониторинга overhead_ratio представления (total_rows /max(1, base_view_row)). Пользователям следует перестроить материализованное представление, если коэффициент накладных расходов слишком высок.

Материализованное представление и кэширование результирующих наборов

Эти две функции в выделенном пуле SQL используются для настройки производительности запросов. Кэширование результирующих наборов используется для получения высокой параллелизма и быстрого ответа от повторяющихся запросов к статическим данным.

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

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

Пример

В этом примере используется запрос TPCDS, который находит клиентов, которые тратят больше денег через каталог, чем в магазинах, определяют предпочитаемых клиентов и их страну или регион происхождения. Запрос содержит выборку TOP 100 записей из UNION трех подзапросов SELECT, включающих SUM() и GROUP BY.

WITH year_total AS (
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
       ,'s' sale_type
FROM customer
     ,store_sales
     ,date_dim
WHERE c_customer_sk = ss_customer_sk
   AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
UNION ALL
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
       ,'c' sale_type
FROM customer
     ,catalog_sales
     ,date_dim
WHERE c_customer_sk = cs_bill_customer_sk
   AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
UNION ALL
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
       ,'w' sale_type
FROM customer
     ,web_sales
     ,date_dim
WHERE c_customer_sk = ws_bill_customer_sk
   AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
         )
  SELECT TOP 100
                  t_s_secyear.customer_id
                 ,t_s_secyear.customer_first_name
                 ,t_s_secyear.customer_last_name
                 ,t_s_secyear.customer_birth_country
FROM year_total t_s_firstyear
     ,year_total t_s_secyear
     ,year_total t_c_firstyear
     ,year_total t_c_secyear
     ,year_total t_w_firstyear
     ,year_total t_w_secyear
WHERE t_s_secyear.customer_id = t_s_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_c_secyear.customer_id
   AND t_s_firstyear.customer_id = t_c_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_w_firstyear.customer_id
   AND t_s_firstyear.customer_id = t_w_secyear.customer_id
   AND t_s_firstyear.sale_type = 's'
   AND t_c_firstyear.sale_type = 'c'
   AND t_w_firstyear.sale_type = 'w'
   AND t_s_secyear.sale_type = 's'
   AND t_c_secyear.sale_type = 'c'
   AND t_w_secyear.sale_type = 'w'
   AND t_s_firstyear.dyear+0 =  1999
   AND t_s_secyear.dyear+0 = 1999+1
   AND t_c_firstyear.dyear+0 =  1999
   AND t_c_secyear.dyear+0 =  1999+1
   AND t_w_firstyear.dyear+0 = 1999
   AND t_w_secyear.dyear+0 = 1999+1
   AND t_s_firstyear.year_total > 0
   AND t_c_firstyear.year_total > 0
   AND t_w_firstyear.year_total > 0
   AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
           > CASE WHEN t_s_firstyear.year_total > 0 THEN t_s_secyear.year_total / t_s_firstyear.year_total ELSE NULL END
   AND CASE WHEN t_c_firstyear.year_total > 0 THEN t_c_secyear.year_total / t_c_firstyear.year_total ELSE NULL END
           > CASE WHEN t_w_firstyear.year_total > 0 THEN t_w_secyear.year_total / t_w_firstyear.year_total ELSE NULL END
ORDER BY t_s_secyear.customer_id
         ,t_s_secyear.customer_first_name
         ,t_s_secyear.customer_last_name
         ,t_s_secyear.customer_birth_country
OPTION ( LABEL = 'Query04-af359846-253-3');

Проверьте предполагаемый план выполнения запроса. Существует 18 перетасовок и 17 операций соединения, которые занимают больше времени для выполнения. Теперь создадим одно материализованное представление для каждого из трех вложенных инструкций SELECT.

CREATE materialized view nbViewSS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ss_ext_list_price-ss_ext_wholesale_cost-ss_ext_discount_amt+ss_ext_sales_price, 0)/2) year_total
          , count_big(*) AS cb
FROM dbo.customer
     ,dbo.store_sales
     ,dbo.date_dim
WHERE c_customer_sk = ss_customer_sk
   AND ss_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year
GO
CREATE materialized view nbViewCS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(cs_ext_list_price-cs_ext_wholesale_cost-cs_ext_discount_amt+cs_ext_sales_price, 0)/2) year_total
          , count_big(*) as cb
FROM dbo.customer
     ,dbo.catalog_sales
     ,dbo.date_dim
WHERE c_customer_sk = cs_bill_customer_sk
   AND cs_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year

GO
CREATE materialized view nbViewWS WITH (DISTRIBUTION=HASH(customer_id)) AS
SELECT c_customer_id customer_id
       ,c_first_name customer_first_name
       ,c_last_name customer_last_name
       ,c_preferred_cust_flag customer_preferred_cust_flag
       ,c_birth_country customer_birth_country
       ,c_login customer_login
       ,c_email_address customer_email_address
       ,d_year dyear
       ,sum(isnull(ws_ext_list_price-ws_ext_wholesale_cost-ws_ext_discount_amt+ws_ext_sales_price, 0)/2) year_total
          , count_big(*) AS cb
FROM dbo.customer
     ,dbo.web_sales
     ,dbo.date_dim
WHERE c_customer_sk = ws_bill_customer_sk
   AND ws_sold_date_sk = d_date_sk
GROUP BY c_customer_id
         ,c_first_name
         ,c_last_name
         ,c_preferred_cust_flag
         ,c_birth_country
         ,c_login
         ,c_email_address
         ,d_year

Снова проверьте план выполнения исходного запроса. Теперь количество объединений изменяется с 17 до 5, и нет пересортировки. Выберите значок операции фильтра в плане, его список выходных данных показывает, что данные считываются из материализованных представлений вместо базовых таблиц.

При использовании материализованных представлений тот же запрос выполняется быстрее без изменения кода.

Дальнейшие действия

Дополнительные советы по разработке см. в обзоре разработки выделенного пула SQL.