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

Azure Synapse'teki ayrılmış SQL havuzları için geçerli görünümler, karmaşık analitik sorguların herhangi bir sorgu değişikliği olmadan hızlı performans sağlamak için düşük bakım gerektiren bir yöntem sunar. Bu makalede malzemeleşmiş 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'teki 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 tablolar olarak sunulan sanal tablolardır. Görünümler ortak veri hesaplamasının karmaşıklığını kapsüller ve sorguları yeniden yazmaya gerek kalmaması için hesaplama değişikliklerine bir soyutlama katmanı ekler.

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 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 atıfta bulunmadan gerçekleşmiş bir görünüm kullanabilir, bu nedenle uygulama kodunu değiştirmenize gerek kalmaz.

Standart görünümdeki gereksinimlerin çoğu yine de materyalleş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 bakın CREATE MATERIALIZED VIEW AS SELECT

Karşılaştırma	Görüntüle	Gerçekleştirilmiş Görünüm
Tanımı görüntüle	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şlenmiş ve ayrılmış SQL havuzunda depolanmış. Altta yatan tablolara veri eklendikçe güncelleniyor.
Veri yenileme	Her zaman güncelleştirildi	Her zaman güncelleştirildi
Karmaşık sorgulardan görünüm verilerini alma hızı	Yavaş	Hızlı
Ek depolama alanı	Hayı	Evet
Sözdizimi	GÖRÜNÜM YARAT	SELECT İLE MALZEMELİ GÖRÜNÜM OLUŞTUR

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

Düzgün tasarlanmış bir maddileş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ığı 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şvuru yapmak için sorgu gerektirmez.
• Görünümler 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, şekillendirilmiş görünümlerin sorgulanmasıyla temel tabloların doğrudan sorgulanması gibi aynı verileri döndürmesini 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ı alır.

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. Bakınız: CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL).
• Sorguya özgü materyalleş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ı eylemi gerekmez.

Yaygın senaryolar

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

Büyük boyutlu verilerde 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 daha yoğun işlem gerektiren 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 derleme gerekmez ve böylece daha düşük işlem maliyeti ve daha hızlı sorgu yanıtı sağlanır.

Sorgu değişiklikleri yapmadan veya en az değişiklikle daha hızlı performans arıyorsanız

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. Eğer görünümler tarafından oluşturulan maliyet, sorgu performansındaki kazançla dengelenebilirse, insanlar sorgu performansını iyileştirmek için maddileş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 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 materyalize 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ılır) 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. Disk üzerindeki bir sanal tablo olan gerçekleştirilmiş görünüm, karma ve dönüşümlü 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 olan 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>'yu çalıştırabilirler. 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ını artıran 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ı düşüşünü önlemek için, her maddileştirilmiş görünüm SQL motoru tarafından ayrı ayrı tutulur.

Gerçekleştirilmiş görünüm sayısı ve temel tablo değişiklikleri arttığında bakım iş yükü artar. Kullanıcılar, tüm maddileştirilmiş görünümlerden kaynaklanan maliyetin sorgu performansındaki artışla dengelenip dengelenmediğini kontrol etmelidir.

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;

Malzemeleşmiş görünüm sayısını azaltma yöntemleri:

• İş 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 silin. 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 malzemeleşmiş 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 maliyeti azaltılmalıdır. Ö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 materialized view of this form

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

Tüm performans ayarlamaları için sorgu değişikliği gerekmez

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, görünümlere başvurmayan sorgulara ve gerçekleştirilmiş görünüm oluşturma sürecinde desteklenmeyen toplamları kullanan sorgulara saydam bir şekilde uygulanır. Sorgu değişikliği gerekmez. Bir sorgunun tahmini yürütme planını kontrol ederek gerçekleştirilmiş bir görünümün kullanılıp kullanılmadığını doğrulayabilirsiniz.

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

Gerçekleştirilmiş görünüm, kümelenmiş columnstore dizini (CCI) içeren 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ş bir görünümden sorguyu çözümlemek, temel tabloları doğrudan sorgulamaktan daha uzun sürebilir.

Sorgu performansının düşmesini önlemek için, görünümün overhead_ratio'sunu (total_rows / max(1, base_view_row)) izlemek amacıyla DBCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD çalıştırmak iyi bir uygulamadır. Kullanıcılar, overhead_ratio çok yüksekse materyalize görünümü YENİDEN 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ı ayarlaması için kullanılır. Sonuç kümesi önbelleğe alma, statik verilere karşı 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 isteyen 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'ından 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 fazla zaman alan 18 karıştırma ve 17 birleştirme işlemi vardır. Şimdi üç alt SELECT deyiminin her biri için bir maddileştirilmiş 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 katılım sayısı 17'den 5'e değişiyor ve karıştırma yok. Plandaki Filtre işlemi simgesini seçin; Çıkış Listesi, verilerin temel tablolar yerine gerçekleştirilmiş görünümlerden okunduğunu gösterir.

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

Sonraki adımlar

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