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

Azure Synapse adanmış SQL havuzları için gerçekleştirilmiş görünümler, herhangi bir sorgu değişikliği yapmadan hızlı performans sağlamak üzere karmaşık analitik sorgular için düşük bakım yöntemi sağlar. Bu makalede gerçekleştirilmiş görünümleri kullanma hakkında genel yönergeler ele alınmaktadır.

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

Azure Synapse 'de adanmış SQL havuzu standart ve gerçekleştirilmiş görünümleri destekler. Her ikisi de SELECT ifadelerle oluşturulmuş ve sorguları Mantıksal tablolar olarak sunulan sanal tablolardır. Görünümler ortak veri hesaplamasının karmaşıklığını yalıtır ve değişiklikleri hesaplama için bir soyutlama katmanı ekler. böylece sorguları yeniden yazmaya gerek kalmaz.

Standart Görünüm, görünümün her seferinde verilerini hesaplar. Diskte depolanan veri yok. insanlar genellikle standart görünümleri, mantıksal nesneleri ve sorguları ayrılmış bir SQL havuzunda düzenlemenize yardımcı olan bir araç olarak kullanır. Standart bir görünüm kullanmak için bir sorgunun kendisine doğrudan başvuru yapması gerekir.

gerçekleştirilmiş bir görünüm, verileri bir tablo gibi ayrılmış bir SQL havuzunda önceden hesaplar, depolar ve saklar. Gerçekleştirilmiş bir görünüm kullanıldığında her zaman bir yeniden hesaplama gerekmez. Gerçekleştirilmiş görünümlerde verilerin tümünü veya bir alt kümesini kullanan sorguların bazıları daha hızlı performans alabilir. Daha da iyisi, sorgular kendisine 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ümde gereksinimlerin çoğu, gerçekleştirilmiş bir görünüm için de geçerlidir. Gerçekleştirilmiş görünüm sözdizimi ve diğer gereksinimler hakkında daha fazla bilgi için bkz. Select olarak GERÇEKLEŞTIRILMIŞ görünüm oluşturma

Karşılaştırma Görünüm Gerçekleştirilmiş Görünüm
Tanımı görüntüleme adanmış SQL havuzunda depolanır. adanmış 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 adanmış SQL havuzunda depolanır. Temel tablolara veri eklendikçe güncelleştirildi.
Veri yenileme Her zaman güncelleştiriliyor Her zaman güncelleştiriliyor
Karmaşık sorgulardan Görünüm verilerini alma hızı Yavaş Hızlı
Ek depolama Hayır Yes
Syntax CREATE VIEW GERÇEKLEŞTIRILMIŞ GÖRÜNÜMÜ SEÇ

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

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

  • Birleştirmelere ve toplama işlevlerine sahip karmaşık sorgular için yürütme süresini azaltın. Sorgu ne kadar karmaşık olursa, yürütme zamanı kaydetme olasılığı o kadar yüksektir. En avantaja, bir sorgunun hesaplama maliyeti yüksekse ve elde edilen veri kümesi küçük olduğunda kazanılabilir.
  • adanmış 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 tarafından saydamdır ve gerçekleştirilmiş görünümlere doğrudan başvuru yapmak için sorgular gerektirmez.
  • Görünümlerde düşük bakım gerektir. Taban tablolardaki tüm artımlı veri değişiklikleri, zaman uyumlu olarak gerçekleştirilmiş görünümlere otomatik olarak 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 doğrudan temel tabloları sorgulamak için aynı verileri döndürmesini sağlar.
  • Gerçekleştirilmiş bir görünümdeki veriler, temel tablolardan farklı şekilde dağıtılabilir.
  • Gerçekleştirilmiş görünümlerde veri, normal tablolardaki verilerle aynı yüksek kullanılabilirlik ve dayanıklılık avantajlarını alır.

adanmış 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, adanmış SQL havuzunda uygulanan gerçekleştirilmiş görünümler de aşağıdaki avantajları sağlar:

Not

CASE ifadeleriyle oluşturulan gerçekleştirilmiş bir görünüm, yalnızca görünüm oluşturma sırasında durum ölçütlerine uyan değerleri depolar. Gerçekleştirilmiş görünüm, görünüm oluşturulduktan sonra durum ifadelerinden kaynaklanan artımlı veri değişikliklerini yansıtmaz.

Genel senaryolar

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

Büyük verilere karşı karmaşık analitik sorguların performansını artırmanın gerekli olması gerekir

Karmaşık analitik sorgular genellikle daha fazla toplama işlevlerini ve tablo katılımları kullanır, bu da karışık ve sorgu yürütmesindeki birleşimler gibi daha fazla işlem ağır işleme olur. Bu nedenle, özellikle büyük tablolarda karmaşık analitik sorguların tamamlanması daha uzun sürer.

Kullanıcılar, ortak sorgu hesaplamalarından döndürülen veriler için gerçekleştirilmiş görünümler oluşturabilir, bu nedenle sorgular tarafından bu verilere ihtiyaç duyduğunda, daha düşük işlem maliyeti ve daha hızlı sorgu yanıtına izin veren bir yeniden hesaplama gerekmez.

Hiçbir veya en düşük sorgu değişikliği ile daha hızlı performans gerekiyor

adanmış SQL havuzlardaki şema ve sorgu değişiklikleri genellikle normal ETL işlemlerini ve raporlamayı desteklemek için en düşük düzeyde tutulur. Görünümler, görünümler tarafından tahakkuk eden maliyet sorgu performansından kazanımdan kayılarak, sorgu performansı ayarlama 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 sürdürmek için daha az bir üretim değişikliği ve potansiyel performans kazancı da daha yüksektir.

  • Gerçekleştirilmiş görünümlerin oluşturulması veya sürdürülmesi, temel tablolara karşı çalışan sorguları etkilemez.
  • Sorgu iyileştiricisi, bir sorgudaki doğrudan görünüm başvurusu olmadan dağıtılmış gerçekleştirilmiş görünümleri otomatik olarak kullanabilir. Bu yetenek, performans ayarlamasındaki sorgu değişikliği gereksinimini azaltır.

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

adanmış SQL havuzu, dağıtılmış bir sorgu işleme sistemidir. SQL tablodaki veriler, üç dağıtım stratejisinden (karma, round_robin veya çoğaltılan) biri kullanılarak 60 düğüm arasında 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. Gerçekleştirilmiş görünüm, disk üzerinde sanal bir tablo olmak üzere 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 olan veri dağıtımını seçebilirler.

Tasarım Kılavuzu

Sorgu performansını artırmak için gerçekleştirilmiş görünümleri kullanmaya yönelik genel rehberlik 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, iş yükünüzü sorgu desenleri, önem derecesi, sıklık ve elde edilen verilerin boyutu açısından derinlemesine bir şekilde anlamak önemlidir.

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

İş yükünüz gereksinimlerinize göre bu önerileri değerlendirin. İdeal gerçekleştirilmiş görünümler iş yükünün performansına faydalanabilir.

Daha hızlı sorgular ve maliyet arasındaki zorunluluğunu getirir farkında olun

Gerçekleştirilmiş her bir görünüm için bir veri depolama maliyeti ve görünümün sürdürülmesi için bir maliyet vardır. Temel tablolarda veri değişiklikleri yapıldığında, 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, gerçekleştirilmiş her görünüm SQL altyapısından ayrı olarak korunur.

Gerçekleştirilmiş görünümler ve temel tablo değişikliklerinin sayısı arttıkça bakım iş yükü artar. Kullanıcılar, gerçekleştirilmiş tüm görünümlerden tahakkuk eden maliyetin sorgu performans kazancı tarafından kaydırılarak yer olup olmadığını denetlemelidir.

ayrılmış bir SQL havuzunda gerçekleştirilmiş görünümlerin bir listesini oluşturmak 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üzün karmaşık sorguların sıklıkla kullandığı ortak veri kümelerini belirler. İyileştiricinin bunları yürütme planları oluştururken yapı taşları olarak kullanabilmesi için, bu veri kümelerini depolamak üzere gerçekleştirilmiş görünümler oluşturun.

  • Kullanımı düşük olan veya artık gerekli olan doğrulaştırılmış görünümleri bırakın. Devre dışı bırakılmış bir görünüm korunmasa da depolama maliyetine neden olur.

  • Aynı veya benzer temel tablolarda oluşturulan, verileri çakışmasa bile, oluşturulmuş olan görünümleri birleştirin. Elde edilen görünümlerin bir araya olması, ayrı görünümlerin toplamından daha büyük bir görünümle sonuçlansa da, görünüm bakım maliyetinin azalması 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 ayarlaması sorgu değişikliğini gerekli değildir

Sorgu SQL iyileştiricisi, sorgu performansını geliştirmek için dağıtılmış olan görünümleri otomatik olarak kullanabilir. Bu destek, edatlı görünümler oluşturmada desteklenmeyen toplamları kullanan görünümlere ve sorgulara başvuruda olmayan sorgulara saydam bir şekilde uygulanır. Sorgu değişikliği gerekmez. Bir sorgunun tahmini yürütme planını kontrol etmek için bir tahmini görünümün kullanılıp kullanılmay olduğunu onaylayın.

Uzmlaştırılmış görünümleri izleme

Bir doğrulanmış görünüm, kümelenmiş columnstore SQL (CCI) olan bir tablo gibi ayrılmış depolama havuzunda depolanır. CCI dizin segmentlerini taramayı ve temel tablolardan artımlı değişiklikleri uygulamayı içeren bir görünümden veri okuma. Artımlı değişikliklerin sayısı çok yüksek olduğunda, bir sorgunun bir gerçektenleştirilmiş görünümden çözülmesi, doğrudan temel tabloları sorgulamaya göre daha uzun sürebilir.

Sorgu performansında düşüşleri önlemek için dbCC PDW_SHOWMATERIALIZEDVIEWOVERHEAD overhead_ratio'ı çalıştırarak görünümün performansını (total_rows / max(1, base_view_row)) izlemek iyi bir uygulamadır. Kullanıcılar, çok yüksekse, uygulamanın overhead_ratio yeniden oluşturması gerekir.

Uzlaştırılan görünüm ve sonuç kümesi önbelleği

Ayrılmış havuz havuzu SQL bu iki özellik sorgu performansı ayarlama için kullanılır. Statik verilere karşı yinelenen sorgulardan yüksek eşzamanlılık ve hızlı yanıt almak için sonuç kümesi önbelleği kullanılır.

Önbelleğe alınan sonucu kullanmak için, önbellek isteği sorgusunun biçimi önbelleği üreten sorguyla eşleşmeli. Buna ek olarak, önbelleğe alınan sonuç sorgunun tamamına uygulanabilecektir.

Uzmlaştırılmış görünümler, temel tablolarda veri değişikliklerine olanak sağlar. Uygulanan görünümlerde veriler sorgunun bir parçasına uygulanabilir. Bu destek, aynı doğrulanan görünümlerin daha hızlı performans için bazı hesaplamaları paylaşan farklı sorgular tarafından da kullanılamalarına olanak sağlar.

Örnek

Bu örnekte katalog aracılığıyla mağazalara göre daha fazla para harcayan, tercih edilen müşterileri ve çıkış noktası ülkelerini/bölgelerini belirleyen müşterileri bulan TPCDS benzer bir sorgu edilmektedir. Sorgu, SUM() ve GROUP BY içeren üç alt SELECT deyiminin UNION'ından TOP 100 kayıtlarının seçerek 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ı kontrol edin. Daha fazla zaman alan 18 karıştırma ve 17 birleştirme işlemleri vardır. Şimdi üç alt SELECT deyiminin her biri için bir tane uzlaştırılan görünüm oluşturabilirsiniz.

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 kontrol edin. Artık birleştirme sayısı 17'den 5'e değişir ve karıştırma yoktur. Plandaki Filtre işlemi simgesini seçin. Çıkış Listesi, temel tablolar yerine verilerin uzlaştırılan görünümlerden okundu olarak gösterir.

Plan_Output_List_with_Materialized_Views

Aynı sorgu, uzlaştırılan görünümlerle 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ış.