Wskazówki dotyczące projektowania tabel rozproszonych przy użyciu dedykowanej puli SQL w usłudze Azure Synapse Analytics

Ten artykuł zawiera zalecenia dotyczące projektowania tabel rozproszonych przy użyciu skrótów i rozproszonych z działaniem okrężnym w dedykowanych pulach SQL.

W tym artykule założono, że znasz pojęcia dotyczące dystrybucji danych i przenoszenia danych w dedykowanej puli SQL. Aby uzyskać więcej informacji, zobacz Architektura usługi Azure Synapse Analytics.

Co to jest tabela rozproszona?

Tabela rozproszona jest wyświetlana jako pojedyncza tabela, ale wiersze są rzeczywiście przechowywane w 60 dystrybucjach. Wiersze są dystrybuowane za pomocą algorytmu skrótu lub działania okrężnego.

Dystrybucja skrótów zwiększa wydajność zapytań w dużych tabelach faktów i koncentruje się na tym artykule. Dystrybucja okrężna jest przydatna do poprawy szybkości ładowania. Te wybory projektowe mają znaczący wpływ na poprawę wydajności zapytań i ładowania.

Inną opcją magazynu tabel jest replikowanie małej tabeli we wszystkich węzłach obliczeniowych. Aby uzyskać więcej informacji, zobacz Wskazówki dotyczące projektowania replikowanych tabel. Aby szybko wybrać jedną z trzech opcji, zobacz Omówienie tabel rozproszonych.

W ramach projektowania tabel dowiedz się, jak najwięcej o danych i sposobie wykonywania zapytań dotyczących danych. Rozważmy na przykład następujące pytania:

• Jak duża jest tabela?
• Jak często jest odświeżona tabela?
• Czy mam tabele faktów i wymiarów w dedykowanej puli SQL?

Rozproszony skrót

Tabela rozproszona przy użyciu skrótu dystrybuuje wiersze tabeli w węzłach obliczeniowych przy użyciu funkcji skrótu deterministycznego w celu przypisania każdego wiersza do jednej dystrybucji.

Ponieważ identyczne wartości są zawsze skrótem do tej samej dystrybucji, usługa SQL Analytics ma wbudowaną wiedzę na temat lokalizacji wierszy. W dedykowanej puli SQL ta wiedza służy do minimalizowania przenoszenia danych podczas zapytań, co zwiększa wydajność zapytań.

Tabele rozproszone przy użyciu skrótów dobrze sprawdzają się w przypadku dużych tabel faktów w schemacie gwiazdy. Mogą mieć bardzo dużą liczbę wierszy i nadal osiągać wysoką wydajność. Istnieją pewne zagadnienia projektowe, które ułatwiają uzyskanie wydajności systemu rozproszonego, który ma na celu zapewnienie. Wybór dobrej kolumny dystrybucji lub kolumn jest jednym z takich zagadnień, które opisano w tym artykule.

Rozważ użycie tabeli rozproszonej przy użyciu skrótu, gdy:

• Rozmiar tabeli na dysku wynosi ponad 2 GB.
• Tabela zawiera częste operacje wstawiania, aktualizowania i usuwania.

Rozproszone działanie okrężne

Tabela rozproszona z działaniem okrężnym dystrybuuje wiersze tabeli równomiernie we wszystkich dystrybucjach. Przypisanie wierszy do dystrybucji jest losowe. W przeciwieństwie do tabel rozproszonych przy użyciu skrótów wiersze o równych wartościach nie mają gwarancji przypisania do tej samej dystrybucji.

W związku z tym system czasami musi wywołać operację przenoszenia danych, aby lepiej zorganizować dane przed rozwiązaniem zapytania. Ten dodatkowy krok może spowolnić zapytania. Na przykład łączenie tabeli z działaniem okrężnym zwykle wymaga przetasowania wierszy, co jest trafieniem wydajności.

Rozważ użycie dystrybucji działania okrężnego dla tabeli w następujących scenariuszach:

• Gdy rozpoczynasz pracę jako prosty punkt początkowy, ponieważ jest to domyślny punkt początkowy
• Jeśli nie ma oczywistego klucza dołączania
• Jeśli nie ma dobrej kolumny kandydata do rozdzielania tabeli przy użyciu skrótu
• Jeśli tabela nie ma wspólnego klucza sprzężenia z innymi tabelami
• Jeśli sprzężenia są mniej znaczące niż inne sprzężenia w zapytaniu
• Gdy tabela jest tymczasową tabelą przejściową

Samouczek Load New York taxicab data (Ładowanie danych podatkowych w Nowym Jorku) zawiera przykład ładowania danych do tabeli przejściowej z działaniem okrężnym.

Wybieranie kolumny dystrybucji

Tabela rozproszona przy użyciu skrótu zawiera kolumnę dystrybucji lub zestaw kolumn, które są kluczem skrótu. Na przykład poniższy kod tworzy tabelę rozproszoną ProductKey przy użyciu skrótu jako kolumnę dystrybucji.

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

Rozkład skrótów można stosować w wielu kolumnach w celu bardziej równomiernego rozkładu tabeli podstawowej. Dystrybucja wielokolumna umożliwia wybranie maksymalnie ośmiu kolumn do dystrybucji. Zmniejsza to nie tylko niesymetryczność danych w czasie, ale także poprawia wydajność zapytań. Na przykład:

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

Uwaga

Dystrybucję wielokolumnową w usłudze Azure Synapse Analytics można włączyć, zmieniając poziom zgodności bazy danych na 50 za pomocą tego polecenia. ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50; Aby uzyskać więcej informacji na temat ustawiania poziomu zgodności bazy danych, zobacz ALTER DATABASE SCOPED CONFIGURATION (ALTER DATABASE SCOPED CONFIGURATION). Aby uzyskać więcej informacji na temat dystrybucji wielokolumnach, zobacz CREATE MATERIALIZED VIEW, CREATE TABLE lub CREATE TABLE AS SELECT.

Dane przechowywane w kolumnach dystrybucji można aktualizować. Aktualizacje danych w kolumnach dystrybucji mogą spowodować operację mieszania danych.

Wybór kolumn dystrybucji jest ważną decyzją projektową, ponieważ wartości w kolumnach skrótu określają sposób dystrybucji wierszy. Najlepszy wybór zależy od kilku czynników i zwykle wiąże się z kompromisami. Po wybraniu kolumny dystrybucji lub zestawu kolumn nie można go zmienić. Jeśli nie wybrano najlepszych kolumn po raz pierwszy, możesz użyć polecenia CREATE TABLE AS SELECT (CTAS), aby ponownie utworzyć tabelę z żądanym kluczem skrótu dystrybucji.

Wybieranie kolumny dystrybucji z danymi równomiernymi

Aby uzyskać najlepszą wydajność, wszystkie dystrybucje powinny mieć mniej więcej taką samą liczbę wierszy. Jeśli co najmniej jedna dystrybucja ma nieproporcjonalną liczbę wierszy, niektóre dystrybucje kończą część zapytania równoległego przed innymi. Ponieważ zapytanie nie może zakończyć się, dopóki wszystkie dystrybucje nie zakończą przetwarzania, każde zapytanie jest tylko tak szybkie, jak najwolniejsza dystrybucja.

• Niesymetryczność danych oznacza, że dane nie są równomiernie dystrybuowane w różnych dystrybucjach
• Niesymetryczność przetwarzania oznacza, że niektóre dystrybucje trwa dłużej niż inne podczas uruchamiania zapytań równoległych. Może się tak zdarzyć, gdy dane są niesymetryczne.

Aby zrównoważyć przetwarzanie równoległe, wybierz kolumnę dystrybucji lub zestaw kolumn, które:

• Ma wiele unikatowych wartości. Co najmniej jedna kolumna dystrybucji może mieć zduplikowane wartości. Wszystkie wiersze o tej samej wartości są przypisywane do tej samej dystrybucji. Ponieważ istnieje 60 dystrybucji, niektóre dystrybucje mogą mieć > 1 unikatowe wartości, podczas gdy inne mogą kończyć się wartościami zerowymi.
• Nie ma list NUL lub ma tylko kilka NULLs. W skrajnym przykładzie, jeśli wszystkie wartości w kolumnach dystrybucji mają wartość NULL, wszystkie wiersze są przypisywane do tej samej dystrybucji. W związku z tym przetwarzanie zapytań jest niesymetryczne do jednej dystrybucji i nie korzysta z przetwarzania równoległego.
• Nie jest kolumną dat. Wszystkie dane dla tej samej daty lądują w tej samej dystrybucji lub będą rejestrowane według daty. Jeśli kilku użytkowników filtruje tę samą datę (na przykład bieżącą datę), tylko 1 z 60 dystrybucji wykonuje wszystkie operacje przetwarzania.

Wybieranie kolumny dystrybucji, która minimalizuje przenoszenie danych

Aby uzyskać poprawne zapytania, zapytania mogą przenosić dane z jednego węzła obliczeniowego do innego. Przenoszenie danych często występuje, gdy zapytania mają sprzężenia i agregacje w tabelach rozproszonych. Wybór zestawu kolumn lub kolumn dystrybucji, który pomaga zminimalizować przenoszenie danych, jest jedną z najważniejszych strategii optymalizacji wydajności dedykowanej puli SQL.

Aby zminimalizować przenoszenie danych, wybierz kolumnę dystrybucji lub zestaw kolumn, które:

• Jest używany w JOINklauzulach , , GROUP BYDISTINCT, OVERi HAVING . Gdy dwie duże tabele faktów mają częste sprzężenia, wydajność zapytań poprawia się podczas dystrybucji obu tabel w jednej z kolumn sprzężenia. Jeśli tabela nie jest używana w sprzężeniach, rozważ dystrybucję tabeli w kolumnie lub zestawie kolumn, który jest często w klauzuli GROUP BY .
• Nie jest używany w WHERE klauzulach. Gdy klauzula WHERE zapytania i kolumny dystrybucji tabeli znajdują się w tej samej kolumnie, zapytanie może napotkać duże niesymetryczność danych, co prowadzi do spadku obciążenia przetwarzania tylko na kilka dystrybucji. Ma to wpływ na wydajność zapytań, a w idealnym przypadku wiele dystrybucji współużytkuje obciążenie przetwarzania.
• Nie jest kolumną dat. WHERE klauzule często filtrować według daty. W takim przypadku wszystkie operacje przetwarzania mogą być uruchamiane tylko w kilku dystrybucjach wpływających na wydajność zapytań. W idealnym przypadku wiele dystrybucji współdzieli obciążenie przetwarzania.

Po zaprojektowaniu tabeli rozproszonej przy użyciu skrótu następnym krokiem jest załadowanie danych do tabeli. Aby uzyskać wskazówki dotyczące ładowania, zobacz Ładowanie przeglądu.

Jak sprawdzić, czy dystrybucja jest dobrym wyborem

Po załadowaniu danych do tabeli rozproszonej przy użyciu skrótów sprawdź, jak równomiernie wiersze są dystrybuowane w 60 dystrybucjach. Wiersze na dystrybucję mogą się różnić do 10% bez zauważalnego wpływu na wydajność.

Rozważ następujące sposoby oceny kolumn dystrybucji.

Ustal, czy tabela ma niesymetryczność danych

Szybkim sposobem sprawdzenia niesymetryczności danych jest użycie PDW_SHOWSPACEUSED DBCC. Poniższy kod SQL zwraca liczbę wierszy tabeli przechowywanych w każdej z 60 dystrybucji. W celu zapewnienia zrównoważonej wydajności wiersze w tabeli rozproszonej powinny być rozłożone równomiernie we wszystkich dystrybucjach.

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

Aby określić, które tabele mają ponad 10% niesymetryczności danych:

1. Utwórz widok dbo.vTableSizes wyświetlany w artykule Omówienie tabel.
2. Uruchom poniższe zapytanie:

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;

Sprawdzanie planów zapytań dotyczących przenoszenia danych

Dobry zestaw kolumn dystrybucji umożliwia sprzężenia i agregacje w celu minimalnego przenoszenia danych. Ma to wpływ na sposób pisania sprzężeń. Aby uzyskać minimalny ruch danych dla sprzężenia w dwóch tabelach rozproszonych przy użyciu skrótu, jedna z kolumn sprzężeń musi znajdować się w kolumnie lub kolumnach dystrybucji. Gdy dwie tabele rozproszone przy użyciu skrótu łączą się w kolumnie dystrybucji tego samego typu danych, sprzężenia nie wymagają przenoszenia danych. Sprzężenia mogą używać dodatkowych kolumn bez ponoszenia ruchu danych.

Aby uniknąć przenoszenia danych podczas sprzężenia:

• Tabele biorące udział w sprzężeniu muszą być rozdzielone skrótami w jednej z kolumn uczestniczących w sprzężeniu.
• Typy danych kolumn sprzężenia muszą być zgodne między obiem tabelą.
• Kolumny muszą być połączone z operatorem równości.
• Typ sprzężenia nie może być typem CROSS JOIN.

Aby sprawdzić, czy w zapytaniach występują przenoszenie danych, możesz przyjrzeć się planowi zapytania.

Rozwiązywanie problemu z kolumną dystrybucji

Nie jest konieczne rozwiązanie wszystkich przypadków niesymetryczności danych. Dystrybucja danych to kwestia znalezienia właściwej równowagi między minimalizacją niesymetryczności danych a przenoszeniem danych. Nie zawsze można zminimalizować niesymetryczność danych i przenoszenie danych. Czasami korzyści wynikające z minimalnego przenoszenia danych mogą przewyższać wpływ niesymetryczności danych.

Aby zdecydować, czy należy rozwiązać niesymetryczność danych w tabeli, należy zrozumieć jak najwięcej na temat woluminów danych i zapytań w obciążeniu. Kroki opisane w artykule Monitorowanie zapytań umożliwiają monitorowanie wpływu niesymetryczności zapytań na wydajność zapytań. W szczególności sprawdź, jak długo trwa wykonywanie dużych zapytań w poszczególnych dystrybucjach.

Ponieważ nie można zmienić kolumn dystrybucji w istniejącej tabeli, typowym sposobem rozwiązania niesymetryczności danych jest ponowne utworzenie tabeli z różnymi kolumnami dystrybucji.

Utwórz ponownie tabelę przy użyciu nowego zestawu kolumn dystrybucji

W tym przykładzie użyto polecenia CREATE TABLE AS SELECT , aby ponownie utworzyć tabelę z różnymi kolumnami dystrybucji skrótów.

Najpierw użyj CREATE TABLE AS SELECT nowej tabeli (CTAS) z nowym kluczem. Następnie ponownie utwórz statystyki i na koniec zamień tabele, zmieniając ich nazwy.

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

Powiązana zawartość

Aby utworzyć tabelę rozproszoną, użyj jednej z następujących instrukcji:

• CREATE TABLE (dedykowana pula SQL)
• CREATE TABLE AS SELECT (dedykowana pula SQL)