Azure Synapse Analytics'te ayrılmış SQL havuzunu kullanarak gerçekleştirilmiş görünümlerle performans ayarlama

Makale
03/11/2023

Ayrılmış SQL havuzunda 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ı

SQL havuzu hem standart hem de 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ı ortaya çıkararak 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 veritabanındaki 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ış 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üm gereksinimlerinin ç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 gereksinimler hakkında ayrıntılı bilgi için CREATE MATERIALIZED VIEW AS SELECT bölümüne bakın.

Karşılaştırma	Görünüm	Gerçekleştirilmiş Görünüm
Tanımı görüntüleme	Azure veri ambarında depolanır.	Azure veri ambarında 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 azure veri ambarında önceden işlenir ve 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ümlerin 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 daha az yürütme süresi. 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 sorgu iyileştiricisi, 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. Gerçekleştirilmiş görünüm verileri iki yerde depolar; görünüm oluşturma zamanındaki ilk veriler için kümelenmiş columnstore dizini ve artımlı veri değişiklikleri için bir delta deposu. Temel tablolardaki tüm veri değişiklikleri zaman uyumlu bir şekilde otomatik olarak delta deposuna eklenir. Arka plan işlemi (tanımlama grubu taşıyıcısı), verileri delta deposundan görünümün columnstore dizinine düzenli aralıklarla taşır. 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.

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 ek avantajları sağlar:

Temel tablolardaki veri değişiklikleriyle otomatik ve zaman uyumlu veri yenileme. Kullanıcı müdahalesi gerekli değildir.
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).

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, özellikle büyük tablolarda bu sorguların tamamlanması 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 bu sayede 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

Normal ETL işlemlerini ve raporlamasını desteklemek için veri ambarlarındaki şema ve sorgu değişiklikleri 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şturmak ve korumak için çok 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

Azure veri ambarı dağıtılmış ve yüksek düzeyde paralel işleme (MPP) sistemidir.

Synapse SQL, kuruluşların veri mühendislerine tanıdık gelen standart T-SQL deneyimlerini kullanarak veri ambarı ve veri sanallaştırma senaryoları uygulamasına olanak tanıyan dağıtılmış bir sorgu sistemidir. Ayrıca akış ve makine öğrenmesi senaryolarını ele almak için SQL'in özelliklerini genişletir. Veri ambarı tablosundaki veriler üç dağıtım stratejisinden (karma, round_robin veya çoğaltılmış) biri kullanılarak 60 düğüme 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 sık 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 EXPLAIN WITH_RECOMMENDATIONS <SQL_statement> çalıştırabilir. 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, her gerçekleştirilmiş görünüm, delta deposundan columnstore dizin segmentlerine satır taşıma ve veri değişikliklerini birleştirme de dahil olmak üzere veri ambarı altyapısı tarafından ayrı ayrı tutulur.

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

Veritabanındaki gerçekleştirilmiş görünüm listesi için bu sorguyu çalıştırabilirsiniz:

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. Örneğin:

-- 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

Veri ambarı iyileştiricisi, sorgu performansını geliştirmek için dağıtılan gerçekleştirilmiş görünümleri otomatik olarak kullanabilir. Bu destek, görünümlere başvurmayan sorgulara ve gerçekleştirilmiş görünüm oluşturma işleminde desteklenmeyen toplamları kullanan 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çek yürütme planını alma hakkında daha fazla bilgi için bkz. DMV'leri kullanarak Azure Synapse Analytics ayrılmış SQL havuzu iş yükünüzü izleme.
tahmini yürütme planını SQL Server Management Studio (SSMS) veya SET SHOWPLAN_XML aracılığıyla alabilirsiniz.

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

Gerçekleştirilmiş görünüm, kümelenmiş columnstore dizinine (CCI) sahip bir tablo gibi veri ambarında depolanır. Gerçekleştirilmiş görünümden veri okuma, dizini taramayı ve değişiklik deposundan değişiklikleri uygulamayı içerir. Delta deposundaki satır 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 / base_view_row ) izlemek üzere DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD çalıştırmak iyi bir uygulamadır. overhead_ratio çok yüksekse, delta deposundaki tüm satırların columnstore dizinine taşınması için gerçekleştirilmiş görünümü yeniden oluşturmayı göz önünde bulundurun.

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

Bu iki özellik, sorgu performansı ayarlama için ayrılmış SQL havuzunda yaklaşık aynı zamanda kullanıma sunulmuştur. Sonuç kümesi önbelleği, statik verilere karşı tekrarlanan sorgulardan yüksek eşzamanlılık ve hızlı yanıt süreleri elde etmek 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 TPCDS benzeri bir sorgu kullanılır. Ayrıca tercih edilen müşterileri ve ülkelerini/menşe bölgelerini de tanımlar. 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 artık karıştırma yok. Plandaki Filtre işlemi simgesini seçin. Çı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ümlerle aynı sorgu, kod değişikliği olmadan çok daha hızlı çalışır.

Sonraki adımlar

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

Aracılığıyla paylaş