Gerçekleştirilmiş görünümlerle performans ayarı

Makale
06/01/2023

Azure Synapse'daki ayrılmış SQL havuzları için gerçekleştirilmiş görünümler, karmaşık analitik sorgular için sorgu değişikliği olmadan hızlı performans elde etmek için düşük bakım yöntemi sağlar. Bu makalede gerçekleştirilmiş görünümleri kullanmayla ilgili genel yönergeler ele alınmaktadır.

Gerçekleştirilmiş görünümler ile standart görünümler karşılaştırması

Azure Synapse'de ayrılmış SQL havuzu, standart ve gerçekleştirilmiş görünümleri destekler. Her ikisi de SELECT ifadeleriyle oluşturulan ve sorgulara mantıksal tablo olarak sunulan sanal tablolardır. Görünümler, ortak veri hesaplamasının karmaşıklığını kapsüller ve hesaplama değişikliklerine bir soyutlama katmanı ekleyerek sorguları yeniden yazmaya gerek kalmaz.

Standart görünüm, görünüm her kullanıldığında verilerini hesaplar. Diskte depolanan veri yok. Kişiler genellikle standart görünümleri, ayrılmış bir SQL havuzundaki mantıksal nesneleri ve sorguları düzenlemeye yardımcı olan bir araç olarak kullanır. Standart bir görünüm kullanmak için sorguya doğrudan başvuruda bulunılması gerekir.

Gerçekleştirilmiş görünüm, verilerini tıpkı bir tablo gibi ayrılmış bir SQL havuzunda önceden hesaplar, depolar ve tutar. Gerçekleştirilmiş görünüm her kullanıldığında yeniden hesaplama gerekmez. Bu nedenle gerçekleştirilmiş görünümlerde verilerin tümünü veya bir alt kümesini kullanan sorgular daha hızlı performans elde edebilir. Daha da iyisi, sorgular doğrudan başvuru yapmadan gerçekleştirilmiş bir görünüm kullanabilir, bu nedenle uygulama kodunu değiştirmeniz gerekmez.

Standart görünümdeki gereksinimlerin çoğu yine de gerçekleştirilmiş görünüm için geçerlidir. Gerçekleştirilmiş görünüm söz dizimi ve diğer gereksinimlerle ilgili ayrıntılar için BKZ . CREATE MATERIALIZED VIEW AS SELECT

Karşılaştırma	Görünüm	Gerçekleştirilmiş Görünüm
Tanımı görüntüleme	Ayrılmış SQL havuzunda depolanır.	Ayrılmış SQL havuzunda depolanır.
İçeriği görüntüleme	Görünüm her kullanıldığında oluşturulur.	Görünüm oluşturma sırasında önceden işlenir ve ayrılmış SQL havuzunda depolanır. Temel alınan tablolara veri eklendikçe güncelleştirildi.
Veri yenileme	Her zaman güncelleştirildi	Her zaman güncelleştirildi
Karmaşık sorgulardan görüntüleme verilerini alma hızı	Yavaş	Hızlı
Ek depolama alanı	Hayır	Yes
Syntax	CREATE VIEW	SELECT OLARAK GERÇEKLEŞTIRILMIŞ GÖRÜNÜM OLUŞTURMA

Gerçekleştirilmiş görünümleri kullanmanın avantajları

Düzgün tasarlanmış bir gerçekleştirilmiş görünüm aşağıdaki avantajları sağlar:

JOIN'ler ve toplama işlevleriyle karmaşık sorgular için yürütme süresini kısaltın. Sorgu ne kadar karmaşık olursa yürütme süresi tasarrufu olasılığı da o kadar yüksek olur. En avantaj, sorgunun hesaplama maliyeti yüksek olduğunda ve sonuçta elde edilen veri kümesi küçük olduğunda elde edilir.
Ayrılmış SQL havuzundaki iyileştirici, sorgu yürütme planlarını geliştirmek için dağıtılan gerçekleştirilmiş görünümleri otomatik olarak kullanabilir. Bu işlem, daha hızlı sorgu performansı sağlayan kullanıcılar için saydamdır ve gerçekleştirilmiş görünümlere doğrudan başvuruda bulunmak için sorgu gerektirmez.
Görünümlerde düşük bakım gerektirir. Temel tablolardan yapılan tüm artımlı veri değişiklikleri otomatik olarak gerçekleştirilmiş görünümlere zaman uyumlu bir şekilde eklenir, yani hem temel tablolar hem de gerçekleştirilmiş görünümler aynı işlemde güncelleştirilir. Bu tasarım, gerçekleştirilmiş görünümlerin sorgulanmasıyla temel tabloların doğrudan sorgulanmasıyla aynı verilerin döndürülmesini sağlar.
Gerçekleştirilmiş görünümdeki veriler temel tablolardan farklı şekilde dağıtılabilir.
Gerçekleştirilmiş görünümlerdeki veriler, normal tablolardaki veriyle aynı yüksek kullanılabilirlik ve dayanıklılık avantajlarını elde eder.

Ayrılmış SQL havuzunda uygulanan gerçekleştirilmiş görünümler de aşağıdaki avantajları sağlar:

Diğer veri ambarı sağlayıcılarıyla karşılaştırıldığında, ayrılmış SQL havuzunda uygulanan gerçekleştirilmiş görünümler de aşağıdaki avantajları sağlar:

Geniş toplama işlevi desteği. Bkz . CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL).
Sorguya özgü gerçekleştirilmiş görünüm önerisi desteği. Bkz . EXPLAIN (Transact-SQL).
Temel tablolardaki veri değişiklikleriyle otomatik ve zaman uyumlu veri yenileme. Kullanıcı müdahalesi gerekli değildir.

Genel senaryolar

Gerçekleştirilmiş görünümler genellikle aşağıdaki senaryolarda kullanılır:

Büyük boyutlu verilere karşı karmaşık analitik sorguların performansını geliştirme ihtiyacı

Karmaşık analitik sorgular genellikle daha fazla toplama işlevi ve tablo birleştirmesi kullanır ve bu da sorgu yürütmede karıştırmalar ve birleştirmeler gibi işlem açısından yoğun işlemlere neden olur. Bu nedenle karmaşık analitik sorguların tamamlanması özellikle büyük tablolarda daha uzun sürer.

Kullanıcılar, sorguların ortak hesaplamalarından döndürülen veriler için gerçekleştirilmiş görünümler oluşturabilir, bu nedenle sorgular için bu verilere ihtiyaç duyulduğunda yeniden hesaplama gerekmez ve böylece işlem maliyeti ve daha hızlı sorgu yanıtı elde edilir.

Sorgu değişikliği yok veya en düşük düzeyde olacak şekilde daha hızlı performans gerekiyor

Ayrılmış SQL havuzlarındaki şema ve sorgu değişiklikleri, normal ETL işlemlerini ve raporlamasını desteklemek için genellikle en düşük düzeyde tutulur. Kişiler, görünümler tarafından tahakkuk eden maliyet sorgu performansındaki kazançla dengelenebilirse sorgu performansı ayarlaması için gerçekleştirilmiş görünümleri kullanabilir.

Ölçeklendirme ve istatistik yönetimi gibi diğer ayarlama seçeneklerine kıyasla, gerçekleştirilmiş bir görünüm oluşturup korumak daha az etkili bir üretim değişikliğidir ve potansiyel performans kazancı da daha yüksektir.

Gerçekleştirilmiş görünümlerin oluşturulması veya bakımının gerçekleştirilmesi, temel tablolarda çalıştırılan sorguları etkilemez.
Sorgu iyileştiricisi, bir sorguda doğrudan görünüm başvurusu olmadan dağıtılan gerçekleştirilmiş görünümleri otomatik olarak kullanabilir. Bu özellik, performans ayarlamada sorgu değişikliği gereksinimini azaltır.

Daha hızlı sorgu performansı için farklı veri dağıtım stratejisi gerekiyor

Ayrılmış SQL havuzu, dağıtılmış bir sorgu işleme sistemidir. SQL tablosundaki veriler üç dağıtım stratejisinden (karma, round_robin veya çoğaltılmış) biri kullanılarak 60 düğüme kadar dağıtılır.

Veri dağıtımı tablo oluşturma zamanında belirtilir ve tablo bırakılana kadar değişmeden kalır. Diskte sanal tablo olan gerçekleştirilmiş görünüm, karma ve round_robin veri dağıtımlarını destekler. Kullanıcılar, temel tablolardan farklı ancak görünümleri kullanan sorguların performansı için en uygun veri dağıtımını seçebilir.

Tasarım kılavuzu

Sorgu performansını geliştirmek için gerçekleştirilmiş görünümleri kullanmayla ilgili genel yönergeler aşağıda verilmiştir:

İş yükünüz için tasarım

Gerçekleştirilmiş görünümler oluşturmaya başlamadan önce, sorgu desenleri, önem derecesi, sıklık ve sonuçta elde edilen verilerin boyutu açısından iş yükünüzü derinlemesine anlamanız önemlidir.

Kullanıcılar sorgu iyileştiricisi tarafından önerilen gerçekleştirilmiş görünümler için çalıştırılabilir EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> . Bu öneriler sorguya özgü olduğundan, tek bir sorgudan yararlanan gerçekleştirilmiş bir görünüm, aynı iş yükündeki diğer sorgular için en uygun olmayabilir.

İş yükü gereksinimlerinizi göz önünde bulundurarak bu önerileri değerlendirin. İdeal gerçekleştirilmiş görünümler, iş yükünün performansından yararlanan görünümlerdir.

Daha hızlı sorgular ile maliyet arasındaki dengeyi unutmayın

Gerçekleştirilmiş her görünüm için bir veri depolama maliyeti ve görünümün bakımının maliyeti vardır. Temel tablolardaki veriler değiştikçe, gerçekleştirilmiş görünümün boyutu artar ve fiziksel yapısı da değişir. Sorgu performansındaki düşüşü önlemek için, gerçekleştirilmiş her görünüm SQL altyapısı tarafından ayrı ayrı korunur.

Gerçekleştirilmiş görünümlerin ve temel tablo değişikliklerinin sayısı arttığında bakım iş yükü artar. Kullanıcılar, tüm gerçekleştirilmiş görünümlerden tahakkuk eden maliyetin sorgu performansı kazancıyla dengelenip dengelenmediğini denetlemelidir.

Bu sorguyu çalıştırarak ayrılmış bir SQL havuzunda gerçekleştirilmiş görünümlerin listesini oluşturabilirsiniz:

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;

Gerçekleştirilmiş görünümlerin sayısını azaltma seçenekleri:

İş yükünüzdeki karmaşık sorgular tarafından sık kullanılan yaygın veri kümelerini belirleyin. İyileştiricinin yürütme planları oluştururken bunları yapı taşları olarak kullanabilmesi için bu veri kümelerini depolamak için gerçekleştirilmiş görünümler oluşturun.
Kullanımı düşük veya artık gerekli olmayan gerçekleştirilmiş görünümleri bırakın. Devre dışı bırakılmış gerçekleştirilmiş görünüm korunmaz, ancak yine de depolama maliyetine neden olur.
Verileri çakışmasa bile aynı veya benzer temel tablolarda oluşturulan gerçekleştirilmiş görünümleri birleştirin. Gerçekleştirilmiş görünümlerin birleştirilmesi, ayrı görünümlerin toplamından daha büyük bir görünüme neden olabilir, ancak görünüm bakım maliyetinin düşmesi gerekir. Örnek:


-- 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 mateiralized view of this form

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

Tüm performans ayarlamaları sorgu değişikliği gerektirmez

SQL sorgu iyileştiricisi, sorgu performansını geliştirmek için dağıtılan gerçekleştirilmiş görünümleri otomatik olarak kullanabilir. Bu destek, gerçekleştirilmiş görünüm oluşturma işleminde desteklenmeyen toplamları kullanan görünümlere ve sorgulara başvurmayan sorgulara saydam olarak uygulanır. Sorgu değişikliği gerekmez. Gerçekleştirilmiş bir görünümün kullanılıp kullanılmadiğini onaylamak için sorgu tahmini yürütme planını kontrol edebilirsiniz.

Gerçekleştirilmiş görünümleri izleme

Gerçekleştirilmiş görünüm, kümelenmiş columnstore dizinine (CCI) sahip bir tablo gibi ayrılmış SQL havuzunda depolanır. Gerçekleştirilmiş görünümden veri okuma, CCI dizin kesimlerini taramayı ve temel tablolardan artımlı değişiklikleri uygulamayı içerir. Artımlı değişikliklerin sayısı çok yüksek olduğunda, gerçekleştirilmiş görünümden bir sorguyu çözümlemek, temel tabloları doğrudan sorgulamaktan daha uzun sürebilir.

Sorgu performansındaki düşüşü önlemek için , görünümün overhead_ratio (total_rows / max(1, base_view_row)) izlemek üzere DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD çalıştırmak iyi bir uygulamadır. Kullanıcılar, overhead_ratio çok yüksekse gerçekleştirilmiş görünümü YENIDEN OLUŞTURmalıdır.

Gerçekleştirilmiş görünüm ve sonuç kümesi önbelleğe alma

Ayrılmış SQL havuzundaki bu iki özellik sorgu performansını ayarlamak için kullanılır. Sonuç kümesi önbelleğe alma, statik verilere yönelik yinelenen sorgulardan yüksek eşzamanlılık ve hızlı yanıt almak için kullanılır.

Önbelleğe alınan sonucu kullanmak için önbellek isteğinde bulunan sorgunun biçimi, önbelleği oluşturan sorguyla eşleşmelidir. Ayrıca, önbelleğe alınan sonuç sorgunun tamamına uygulanmalıdır.

Gerçekleştirilmiş görünümler temel tablolarda veri değişikliklerine izin verir. Gerçekleştirilmiş görünümlerdeki veriler sorgunun bir parçasına uygulanabilir. Bu destek, aynı gerçekleştirilmiş görünümlerin daha hızlı performans için bazı hesaplamaları paylaşan farklı sorgular tarafından kullanılmasını sağlar.

Örnek

Bu örnekte, katalog aracılığıyla mağazalardan daha fazla para harcayan müşterileri bulan, tercih edilen müşterileri ve onların ülke/kaynak bölgesini tanımlayan TPCDS benzeri bir sorgu kullanılır. Sorgu, SUM() ve GROUP BY içeren üç alt SELECT deyiminin UNION'sinden TOP 100 kayıtlarının seçilmesini içerir.

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');

Sorgunun tahmini yürütme planını denetleyin. Yürütülmesi daha uzun süren 18 karıştırma ve 17 birleştirme işlemi vardır. Şimdi üç alt SELECT deyiminin her biri için gerçekleştirilmiş bir görünüm oluşturalım.

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

Özgün sorgunun yürütme planını yeniden denetleyin. Şimdi birleştirme sayısı 17'den 5'e değişiyor ve karıştırma yok. Plandaki Filtre işlemi simgesini seçtiğinizde Çıkış Listesi verilerin temel tablolar yerine gerçekleştirilmiş görünümlerden okunmuş olduğunu gösterir.

Plan_Output_List_with_Materialized_Views

Gerçekleştirilmiş görünümlerde, kod değişikliği olmadan aynı sorgu daha hızlı çalışır.

Sonraki adımlar

Daha fazla geliştirme ipucu için bkz . Ayrılmış SQL havuzu geliştirmeye genel bakış.