Azure Synapse Analytics'te ayrılmış SQL havuzu kullanarak dağıtılmış tablolar tasarlama kılavuzu

Makale
03/20/2023

Bu makale, ayrılmış SQL havuzlarında karma dağıtılmış ve hepsini bir kez deneme dağıtılmış tabloları tasarlamaya yönelik öneriler içerir.

Bu makalede, ayrılmış SQL havuzundaki veri dağıtımı ve veri taşıma kavramları hakkında bilgi sahibi olduğunuz varsayılır. Daha fazla bilgi için bkz. Azure Synapse Analytics mimarisi.

Dağıtılmış tablo nedir?

Dağıtılmış tablo tek bir tablo olarak görünür, ancak satırlar aslında 60 dağıtımda depolanır. Satırlar karma veya hepsini bir kez deneme algoritmasıyla dağıtılır.

Karma dağıtım , büyük olgu tablolarında sorgu performansını artırır ve bu makalenin odak noktasıdır. Hepsini bir kez deneme dağıtımı , yükleme hızını artırmak için kullanışlıdır. Bu tasarım seçimleri, sorguyu geliştirme ve performansı yükleme konusunda önemli bir etkiye sahiptir.

Başka bir tablo depolama seçeneği de küçük bir tabloyu tüm İşlem düğümleri arasında çoğaltmaktır. Daha fazla bilgi için bkz . Çoğaltılan tablolar için tasarım kılavuzu. Üç seçenek arasından hızla seçim yapmak için bkz. Tablolara genel bakış bölümündeki Dağıtılmış tablolar.

Tablo tasarımının bir parçası olarak, verileriniz ve verilerin nasıl sorgulandığı hakkında olabildiğince fazla bilgi edinin. Örneğin, şu soruları göz önünde bulundurun:

Tablo ne kadar büyük?
Tablo ne sıklıkta yenilenir?
Ayrılmış bir SQL havuzunda olgu ve boyut tablolarım var mı?

Karma dağıtılmış

Karma dağıtılmış tablo, her satırı tek bir dağıtıma atamak için belirlenimci bir karma işlevi kullanarak tablo satırlarını İşlem düğümleri arasında dağıtır.

Aynı değerler her zaman aynı dağıtımla karma olduğundan, SQL Analytics satır konumları hakkında yerleşik bilgiye sahiptir. Ayrılmış SQL havuzunda bu bilgi, sorgular sırasında veri taşımayı en aza indirmek için kullanılır ve bu da sorgu performansını artırır.

Karma dağıtılmış tablolar, yıldız şemasındaki büyük olgu tablolarında iyi çalışır. Çok fazla sayıda satıra sahip olabilirler ve yine de yüksek performans elde edebilir. Dağıtılmış sistemin sağlamak üzere tasarlandığı performansı almanıza yardımcı olacak bazı tasarım konuları vardır. İyi bir dağıtım sütunu veya sütunu seçmek, bu makalede açıklanan önemli noktalardan biridir.

Aşağıdaki durumlarda karma dağıtılmış tablo kullanmayı göz önünde bulundurun:

Disk üzerindeki tablo boyutu 2 GB'tan fazladır.
Tabloda sık sık ekleme, güncelleştirme ve silme işlemleri vardır.

Hepsini bir kez deneme dağıtılmış

Hepsini bir kez deneme dağıtılmış tablosu, tablo satırlarını tüm dağıtımlar arasında eşit olarak dağıtır. Satırların dağıtımlara ataması rastgeledir. Karma dağıtılmış tablolardan farklı olarak, eşit değerlere sahip satırların aynı dağıtıma atanması garanti değildir.

Sonuç olarak, sistemin bazen sorguyu çözümlemeden önce verilerinizi daha iyi düzenlemek için bir veri taşıma işlemi çağırması gerekir. Bu ek adım sorgularınızı yavaşlatabilir. Örneğin, hepsini bir kez deneme tablosunu birleştirmek için genellikle performans isabeti olan satırların yeniden karıştırılması gerekir.

Aşağıdaki senaryolarda tablonuz için hepsini bir kez deneme dağıtımını kullanmayı göz önünde bulundurun:

Varsayılan olduğundan basit bir başlangıç noktası olarak başlarken
Belirgin bir birleştirme anahtarı yoksa
Tabloyu dağıtmak için karma için iyi bir aday sütun yoksa
Tablo ortak bir birleştirme anahtarını diğer tablolarla paylaşmıyorsa
Birleştirme, sorgudaki diğer birleşimlerden daha az önemliyse
Tablo geçici bir hazırlama tablosu olduğunda

New York taksi verilerini yükleme öğreticisi, verileri hepsini bir kez deneme hazırlama tablosuna yükleme örneği verir.

Dağıtım sütunu seçme

Karma dağıtılmış tablo, karma anahtar olan bir dağıtım sütununa veya sütun kümesine sahiptir. Örneğin, aşağıdaki kod, dağıtım sütunu olarak ile ProductKey karma dağıtılmış bir tablo oluşturur.

CREATE TABLE [dbo].[FactInternetSales]
(   [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,  DISTRIBUTION = HASH([ProductKey])
);

Karma dağıtım, temel tablonun daha eşit bir dağılımı için birden çok sütuna uygulanabilir. Çok sütunlu dağıtım, dağıtım için en fazla sekiz sütun seçmenize olanak sağlar. Bu yalnızca zaman içinde veri dengesizliğini azaltmakla kalmaz, aynı zamanda sorgu performansını da geliştirir. Örneğin:

CREATE TABLE [dbo].[FactInternetSales]
(   [ProductKey]            int          NOT NULL
,   [OrderDateKey]          int          NOT NULL
,   [CustomerKey]           int          NOT NULL
,   [PromotionKey]          int          NOT NULL
,   [SalesOrderNumber]      nvarchar(20) NOT NULL
,   [OrderQuantity]         smallint     NOT NULL
,   [UnitPrice]             money        NOT NULL
,   [SalesAmount]           money        NOT NULL
)
WITH
(   CLUSTERED COLUMNSTORE INDEX
,  DISTRIBUTION = HASH([ProductKey],   [OrderDateKey],   [CustomerKey] ,   [PromotionKey])
);

Not

Azure Synapse Analytics'te çok sütunlu dağıtım, veritabanının uyumluluk düzeyi 50 bu komutla değiştirilerek etkinleştirilebilir. ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50; Veritabanı uyumluluk düzeyini ayarlama hakkında daha fazla bilgi için bkz. ALTER DATABASE SCOPED CONFIGURATION. Çok sütunlu dağıtımlar hakkında daha fazla bilgi için bkz . CREATE MATERIALIZED VIEW, CREATE TABLE veya CREATE TABLE AS SELECT.

Dağıtım sütunlarında depolanan veriler güncelleştirilebilir. Dağıtım sütunlarındaki verilere Güncelleştirmeler veri karıştırma işlemine neden olabilir.

Karma sütunlardaki değerler satırların nasıl dağıtıldığını belirlediğinden dağıtım sütunlarının seçilmesi önemli bir tasarım kararıdır. En iyi seçim çeşitli faktörlere bağlıdır ve genellikle dengeleri içerir. Bir dağıtım sütunu veya sütun kümesi seçildikten sonra, bunu değiştiremezsiniz. İlk kez en iyi sütunları seçmediyseniz, tabloyu istenen dağıtım karma anahtarıyla yeniden oluşturmak için CREATE TABLE AS SELECT (CTAS) kullanabilirsiniz.

Eşit olarak dağıtılan verileri içeren bir dağıtım sütunu seçin

En iyi performans için tüm dağıtımlar yaklaşık olarak aynı sayıda satıra sahip olmalıdır. Bir veya daha fazla dağıtımda orantısız sayıda satır olduğunda, bazı dağıtımlar paralel sorgunun kendi bölümünü diğerlerinden önce tamamlar. Tüm dağıtımların işlenmesi tamamlanana kadar sorgu tamamlanamadığından, her sorgu yalnızca en yavaş dağıtım kadar hızlıdır.

Veri dengesizliği, verilerin dağıtımlar arasında eşit dağıtılmaması anlamına gelir
Dengesizlik işleme, paralel sorgular çalıştırılırken bazı dağıtımların diğerlerinden daha uzun sürmesi anlamına gelir. Veriler çarpıtıldığında bu durum oluşabilir.

Paralel işlemeyi dengelemek için bir dağıtım sütunu veya sütun kümesi seçin:

Birçok benzersiz değere sahiptir. Dağıtım sütunlarının yinelenen değerleri olabilir. Aynı değere sahip tüm satırlar aynı dağıtıma atanır. 60 dağıtım olduğundan, bazı dağıtımlar 1 benzersiz değere sahip > olabilirken, diğerleri sıfır değerle bitebilir.
NUL'leri yoktur veya yalnızca birkaç DLL'i vardır. Aşırı bir örnek için, dağıtım sütunlarındaki tüm değerler NULL ise, tüm satırlar aynı dağıtıma atanır. Sonuç olarak, sorgu işleme tek bir dağıtıma çarpıtılır ve paralel işlemeden yararlanmaz.
Tarih sütunu değildir. Aynı tarihe ilişkin tüm veriler aynı dağıtımda yer alır veya kayıtları tarihe göre kümeler. Birden çok kullanıcının tümü aynı tarihte (bugünün tarihi gibi) filtreleniyorsa, tüm işleme işlerini 60 dağıtımdan yalnızca 1'i yapar.

Veri taşımayı en aza indiren bir dağıtım sütunu seçin

Doğru sorgu sonucunu almak için sorgular verileri bir İşlem düğümünden diğerine taşıyabilir. Veri taşıma genellikle sorguların dağıtılmış tablolarda birleştirmeleri ve toplamaları olduğunda gerçekleşir. Veri taşımayı en aza indirmeye yardımcı olan bir dağıtım sütunu veya sütun kümesi seçmek, ayrılmış SQL havuzunuzun performansını iyileştirmeye yönelik en önemli stratejilerden biridir.

Veri taşımayı en aza indirmek için bir dağıtım sütunu veya sütun kümesi seçin:

, , GROUP BY, DISTINCT, OVERve HAVING yan tümcelerinde kullanılırJOIN. İki büyük olgu tablosunun sık birleştirmeleri olduğunda, her iki tabloyu birleştirme sütunlarından birine dağıttığınızda sorgu performansı artar. Birleştirmelerde tablo kullanılmadığında, tabloyu yan tümcesinde sıklıkla bulunan bir sütuna veya sütun kümesine dağıtmayı GROUP BY göz önünde bulundurun.
Yan tümcelerde WHEREkullanılmaz. Sorgunun WHERE yan tümcesi ve tablonun dağıtım sütunları aynı sütunda olduğunda, sorgu yüksek veri dengesizliğiyle karşılaşabilir ve bu da işlem yükünün yalnızca birkaç dağıtıma düşmesine neden olur. Bu sorgu performansını etkiler; ideal olarak birçok dağıtım işlem yükünü paylaşır.
Tarih sütunu değildir . WHERE yan tümceleri genellikle tarihe göre filtreler. Bu durumda, tüm işlemler sorgu performansını etkileyen yalnızca birkaç dağıtımda çalıştırılabilir. İdeal olarak, birçok dağıtım işleme yükünü paylaşır.

Karma dağıtılmış bir tablo tasarladıktan sonra, bir sonraki adım tabloya veri yüklemektir. Yükleme yönergeleri için bkz . Yüklemeye genel bakış.

Dağıtımınızın iyi bir seçim olup olmadığını nasıl anlarız?

Veriler karma dağıtılmış bir tabloya yüklendikten sonra, satırların 60 dağıtım arasında ne kadar eşit dağıtıldığına bakın. Dağıtım başına satır sayısı performans üzerinde belirgin bir etki yaratmadan %10'a kadar değişebilir. Dağıtım sütunlarınızı değerlendirmek için aşağıdaki konuları göz önünde bulundurun.

Tabloda veri dengesizliği olup olmadığını belirleme

Veri dengesizliği denetlemenin hızlı bir yolu DBCC PDW_SHOWSPACEUSED kullanmaktır. Aşağıdaki SQL kodu, 60 dağıtımın her birinde depolanan tablo satırlarının sayısını döndürür. Dengeli performans için, dağıtılmış tablonuzdaki satırlar tüm dağıtımlara eşit olarak dağıtılmalıdır.

-- Find data skew for a distributed table
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');

Hangi tabloların %10'dan fazla veri dengesizliği olduğunu belirlemek için:

Tablolara genel bakış makalesinde gösterilen görünümü dbo.vTableSizes oluşturun.
Aşağıdaki sorguyu çalıştırın:

select *
from dbo.vTableSizes
where two_part_name in
    (
    select two_part_name
    from dbo.vTableSizes
    where row_count > 0
    group by two_part_name
    having (max(row_count * 1.000) - min(row_count * 1.000))/max(row_count * 1.000) >= .10
    )
order by two_part_name, row_count;

Veri taşıma için sorgu planlarını denetleme

İyi bir dağıtım sütun kümesi, birleştirmelerin ve toplamaların minimum veri taşımasına olanak tanır. Bu, birleştirmelerin yazılma şeklini etkiler. Karma dağıtılmış iki tabloda birleştirme için minimum veri hareketi elde etmek için birleştirme sütunlarından birinin dağıtım sütununda veya sütunlarında olması gerekir. Karma dağıtılmış iki tablo aynı veri türündeki bir dağıtım sütununda birleştirildiğinde, birleştirme için veri taşıma gerekmez. Birleştirmeler, veri taşımaya neden olmadan ek sütunlar kullanabilir.

Birleştirme sırasında veri hareketini önlemek için:

Birleştirmede yer alan tabloların karma birleştirmeye katılan sütunlardan birinde dağıtılması gerekir.
Birleştirme sütunlarının veri türleri her iki tablo arasında eşleşmelidir.
Sütunlar eşittir işleciyle birleştirilmelidir.
Birleştirme türü bir CROSS JOINolmayabilir.

Sorgularda veri taşıma sorunu olup olmadığını görmek için sorgu planına bakabilirsiniz.

Dağıtım sütunu sorununu çözme

Tüm veri dengesizliği durumlarını çözmek gerekli değildir. Verileri dağıtmak, veri dengesizliği ve veri taşımayı en aza indirmek arasında doğru dengeyi bulmakla ilgili bir konudur. Hem veri dengesizliklerini hem de veri hareketlerini en aza indirmek her zaman mümkün değildir. Bazen minimum veri taşımanın avantajı, veri dengesizliği yaşamanın etkisinden daha ağır basabilir.

Bir tablodaki veri dengesizliği sorununu çözmeniz gerekip gerekmediğini belirlemek için, iş yükünüzdeki veri hacimleri ve sorgular hakkında olabildiğince fazla bilgi edinmelisiniz. Dengesizlik durumunun sorgu performansı üzerindeki etkisini izlemek için Sorgu izleme makalesindeki adımları kullanabilirsiniz. Özellikle, tek tek dağıtımlarda büyük sorguların tamamlanmasının ne kadar sürdüğünü arayın.

Mevcut bir tablodaki dağıtım sütunlarını değiştiremediğiniz için, veri dengesizliklerini çözmenin tipik yolu tabloyu farklı bir dağıtım sütunuyla yeniden oluşturmaktır.

Tabloyu yeni bir dağıtım sütunu kümesiyle yeniden oluşturma

Bu örnekte, farklı karma dağıtım sütunlarına veya sütunlarına sahip bir tabloyu yeniden oluşturmak için CREATE TABLE AS SELECT kullanılır.

Yeni tabloyu yeni anahtarla ilk kez kullanın CREATE TABLE AS SELECT (CTAS). Ardından istatistikleri yeniden oluşturun ve son olarak tabloları yeniden adlandırarak değiştirin.

CREATE TABLE [dbo].[FactInternetSales_CustomerKey]
WITH (  CLUSTERED COLUMNSTORE INDEX
     ,  DISTRIBUTION =  HASH([CustomerKey])
     ,  PARTITION       ( [OrderDateKey] RANGE RIGHT FOR VALUES (   20000101, 20010101, 20020101, 20030101
                                                                ,   20040101, 20050101, 20060101, 20070101
                                                                ,   20080101, 20090101, 20100101, 20110101
                                                                ,   20120101, 20130101, 20140101, 20150101
                                                                ,   20160101, 20170101, 20180101, 20190101
                                                                ,   20200101, 20210101, 20220101, 20230101
                                                                ,   20240101, 20250101, 20260101, 20270101
                                                                ,   20280101, 20290101
                                                                )
                        )
    )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
OPTION  (LABEL  = 'CTAS : FactInternetSales_CustomerKey')
;

--Create statistics on new table
CREATE STATISTICS [ProductKey] ON [FactInternetSales_CustomerKey] ([ProductKey]);
CREATE STATISTICS [OrderDateKey] ON [FactInternetSales_CustomerKey] ([OrderDateKey]);
CREATE STATISTICS [CustomerKey] ON [FactInternetSales_CustomerKey] ([CustomerKey]);
CREATE STATISTICS [PromotionKey] ON [FactInternetSales_CustomerKey] ([PromotionKey]);
CREATE STATISTICS [SalesOrderNumber] ON [FactInternetSales_CustomerKey] ([SalesOrderNumber]);
CREATE STATISTICS [OrderQuantity] ON [FactInternetSales_CustomerKey] ([OrderQuantity]);
CREATE STATISTICS [UnitPrice] ON [FactInternetSales_CustomerKey] ([UnitPrice]);
CREATE STATISTICS [SalesAmount] ON [FactInternetSales_CustomerKey] ([SalesAmount]);

--Rename the tables
RENAME OBJECT [dbo].[FactInternetSales] TO [FactInternetSales_ProductKey];
RENAME OBJECT [dbo].[FactInternetSales_CustomerKey] TO [FactInternetSales];

Sonraki adımlar

Dağıtılmış tablo oluşturmak için şu deyimlerden birini kullanın: