Настройка производительности с материализованными представлениями с помощью выделенного пула SQL в Azure Synapse Analytics

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

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

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

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

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

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

Сравнение	Просмотреть	Материализованное представление
Просмотр определения	Хранится в хранилище данных Azure.	Хранится в хранилище данных Azure.
Просмотр содержимого	Создается каждый раз при использовании представления.	Предварительно обработано и хранится в хранилище данных Azure во время создания представления. Обновлено при добавлении данных в базовые таблицы.
Обновление данных	Всегда обновляется	Всегда обновляется
Скорость получения данных из сложных запросов	Медленный	Быстрый
Дополнительное хранилище	нет	Да
Синтаксис	СОЗДАТЬ ПРЕДСТАВЛЕНИЕ	СОЗДАТЬ МАТЕРИАЛИЗОВАННОЕ ПРЕДСТАВЛЕНИЕ ЧЕРЕЗ SELECT

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Хранилище данных Azure — это распределенная и массовая параллельная система обработки (MPP).

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

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

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

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

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

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

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

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

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

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

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

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

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

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 Azure Synapse Analytics" с помощью динамических административных представлений.
Вы можете получить предполагаемый план выполнения с помощью SQL Server Management Studio (SSMS) или SET SHOWPLAN_XML.

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

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

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

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

Эти две функции представлены в выделенном пуле 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, и больше нет перетасовки. Щелкните значок операции фильтра в плане. Его список выходных данных показывает, что данные считываются из материализованных представлений вместо базовых таблиц.

План_Вывода_С_Материализованными_Представлениями

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

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

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

Обратная связь

Были ли сведения на этой странице полезными?

Last updated on 2023-03-11