Wskazówki dotyczące projektowania tabel rozproszonych w puli SQL SynapseGuidance for designing distributed tables in Synapse SQL pool

Zalecenia dotyczące projektowania tabel rozproszonych rozproszonych i rozmieszczonych w trybie okrężnym w Synapse pulach SQL.Recommendations for designing hash-distributed and round-robin distributed tables in Synapse SQL pools.

W tym artykule założono, że znasz koncepcje dystrybucji i przenoszenia danych w puli SQL Synapse.This article assumes you are familiar with data distribution and data movement concepts in Synapse SQL pool.Aby uzyskać więcej informacji, zobacz architekturę usługi Azure Synapse Analytics (MPP).  For more information, see Azure Synapse Analytics massively parallel processing (MPP) architecture.

Co to jest tabela rozproszona?What is a distributed table?

Rozproszona tabela jest wyświetlana w postaci pojedynczej tabeli, ale wiersze są faktycznie przechowywane w dystrybucji 60.A distributed table appears as a single table, but the rows are actually stored across 60 distributions. Wiersze są dystrybuowane z algorytmem skrótu lub działania okrężnego.The rows are distributed with a hash or round-robin algorithm.

Tabele rozproszone przez funkcję mieszania zwiększają wydajność zapytań w przypadku dużych tabel faktów i są skoncentrowane na tym artykule.Hash-distributed tables improve query performance on large fact tables, and are the focus of this article. Tabele działające w trybie okrężnym są przydatne do poprawy szybkości ładowania.Round-robin tables are useful for improving loading speed. Te opcje projektowe mają znaczny wpływ na poprawę wydajności zapytań i ładowania.These design choices have a significant impact on improving query and loading performance.

Inną opcją magazynu tabel jest replikacja małej tabeli we wszystkich węzłach obliczeniowych.Another table storage option is to replicate a small table across all the Compute nodes. Aby uzyskać więcej informacji, zobacz wskazówki dotyczące projektowania zreplikowanych tabel.For more information, see Design guidance for replicated tables. Aby szybko wybierać spośród trzech opcji, Zobacz tabele rozproszone w tabelach przegląd.To quickly choose among the three options, see Distributed tables in the tables overview.

W ramach projektu tabeli należy zrozumieć możliwie jak najwięcej danych i jak są wykonywane zapytania dotyczące danych.As part of table design, understand as much as possible about your data and how the data is queried.Rozważmy na przykład następujące pytania:  For example, consider these questions:

  • Jak duży jest tabela?How large is the table?
  • Jak często jest odświeżana tabela?How often is the table refreshed?
  • Czy istnieją tabele faktów i wymiarów w puli Synapse SQL?Do I have fact and dimension tables in a Synapse SQL pool?

Wartość skrótu dystrybuowanaHash distributed

W tabeli rozproszonej przez funkcję mieszania wiersze tabeli są dystrybuowane w węzłach obliczeniowych przy użyciu funkcji deterministycznej mieszania, która umożliwia przypisanie każdego wiersza do jednej dystrybucji.A hash-distributed table distributes table rows across the Compute nodes by using a deterministic hash function to assign each row to one distribution.

Tabela rozproszonaDistributed table

Ponieważ identyczne wartości zawsze są skrótami do tej samej dystrybucji, magazyn danych ma wbudowaną wiedzę o lokalizacjach wierszy.Since identical values always hash to the same distribution, the data warehouse has built-in knowledge of the row locations. W puli SQL Synapse ta wiedza służy do minimalizowania przenoszenia danych podczas wykonywania zapytań, co zwiększa wydajność zapytań.In Synapse SQL pool this knowledge is used to minimize data movement during queries, which improves query performance.

Tabele rozproszone przez funkcję mieszania dobrze sprawdzają się w przypadku dużych tabel faktów w schemacie gwiazdy.Hash-distributed tables work well for large fact tables in a star schema. Mogą mieć bardzo dużą liczbę wierszy i nadal osiągać wysoką wydajność.They can have very large numbers of rows and still achieve high performance. Istnieją oczywiście zagadnienia dotyczące projektowania, które pomagają w uzyskaniu wydajności systemu rozproszonego do zapewnienia.There are, of course, some design considerations that help you to get the performance the distributed system is designed to provide. Wybór odpowiedniej kolumny dystrybucji jest taki, jak opisano w tym artykule.Choosing a good distribution column is one such consideration that is described in this article.

Rozważ użycie tabeli rozproszonej przez funkcję tworzenia skrótów, gdy:Consider using a hash-distributed table when:

  • Rozmiar tabeli na dysku jest większy niż 2 GB.The table size on disk is more than 2 GB.
  • W tabeli występują częste operacje wstawiania, aktualizowania i usuwania.The table has frequent insert, update, and delete operations.

Rozdystrybuowane działania okrężneRound-robin distributed

Rozproszona tabela w trybie okrężnym dystrybuuje wiersze tabeli równomiernie między wszystkimi dystrybucjami.A round-robin distributed table distributes table rows evenly across all distributions. Przypisanie wierszy do dystrybucji jest losowe.The assignment of rows to distributions is random. W przeciwieństwie do tabel rozproszonych za pomocą skrótów, wiersze z równymi wartościami nie są gwarantowane do przypisania do tej samej dystrybucji.Unlike hash-distributed tables, rows with equal values are not guaranteed to be assigned to the same distribution.

W związku z tym system czasami musi wywołać operację przenoszenia danych, aby lepiej organizować dane, zanim będzie możliwe rozpoznanie zapytania.As a result, the system sometimes needs to invoke a data movement operation to better organize your data before it can resolve a query. Ten dodatkowy krok może spowolnić zapytania.This extra step can slow down your queries. Na przykład dołączenie tabeli okrężnej wymaga zwykle reshuffling wierszy, które są trafień wydajności.For example, joining a round-robin table usually requires reshuffling the rows, which is a performance hit.

Należy rozważyć użycie rozkładu okrężnego dla tabeli w następujących scenariuszach:Consider using the round-robin distribution for your table in the following scenarios:

  • Gdy rozpoczynasz pracę jako prosty punkt początkowy, ponieważ jest to wartość domyślnaWhen getting started as a simple starting point since it is the default
  • Jeśli nie istnieje oczywisty sprzężenie kluczaIf there is no obvious joining key
  • Jeśli nie ma odpowiedniej kolumny kandydatów do dystrybucji dla tabeliIf there is no good candidate column for hash distributing the table
  • Jeśli tabela nie udostępnia wspólnego klucza sprzężenia z innymi tabelamiIf the table does not share a common join key with other tables
  • Jeśli sprzężenie jest mniej znaczące niż inne sprzężenia w zapytaniuIf the join is less significant than other joins in the query
  • Gdy tabela jest tymczasową tabelą tymczasowąWhen the table is a temporary staging table

Samouczek Załaduj Nowy Jork Taxicab danych zawiera przykład ładowania danych do tabeli przejściowej z działaniem okrężnym.The tutorial Load New York taxicab data gives an example of loading data into a round-robin staging table.

Wybieranie kolumny dystrybucjiChoosing a distribution column

Tabela dystrybuowana z mieszaniem ma kolumnę dystrybucji, która jest kluczem skrótu.A hash-distributed table has a distribution column that is the hash key. Na przykład poniższy kod tworzy tabelę rozproszoną z użyciem skrótów z ProductKey jako kolumna dystrybucji.For example, the following code creates a hash-distributed table with ProductKey as the distribution column.

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

Dane przechowywane w kolumnie dystrybucja można aktualizować.Data stored in the distribution column can be updated. Aktualizacje danych w kolumnie dystrybucja mogą skutkować operacją losową danych.Updates to data in the distribution column could result in data shuffle operation.

Wybór kolumny dystrybucji jest ważną decyzją projektową, ponieważ wartości w tej kolumnie określają sposób dystrybuowania wierszy.Choosing a distribution column is an important design decision since the values in this column determine how the rows are distributed. Najlepszy wybór zależy od kilku czynników i zazwyczaj wymaga kompromisów.The best choice depends on several factors, and usually involves tradeoffs. Po wybraniu kolumny dystrybucji nie można jej zmienić.Once a distribution column is chosen, you cannot change it.

Jeśli nie wybrano najlepszej kolumny po raz pierwszy, można użyć CREATE TABLE jako Select (CTAs) , aby ponownie utworzyć tabelę z inną kolumną dystrybucji.If you didn't choose the best column the first time, you can use CREATE TABLE AS SELECT (CTAS) to re-create the table with a different distribution column.

Wybierz kolumnę dystrybucji z danymi, które dystrybuują równomiernieChoose a distribution column with data that distributes evenly

W celu uzyskania najlepszej wydajności wszystkie dystrybucje powinny mieć mniej więcej tej samej liczby wierszy.For best performance, all of the distributions should have approximately the same number of rows. Gdy co najmniej jedna dystrybucja ma nieproporcjonalną liczbę wierszy, Niektóre dystrybucje kończą swoją część zapytania równoległego przed innymi.When one or more distributions have a disproportionate number of rows, some distributions finish their portion of a parallel query before others. Ponieważ nie można ukończyć zapytania, dopóki wszystkie dystrybucje nie zakończą przetwarzania, każde zapytanie będzie tak szybko, jak najwolniejsza dystrybucja.Since the query can't complete until all distributions have finished processing, each query is only as fast as the slowest distribution.

  • Pochylenie danych oznacza, że dane nie są rozkładane równomiernie między dystrybucjamiData skew means the data is not distributed evenly across the distributions
  • Skośność przetwarzania oznacza, że niektóre rozkłady są wykonywane dłużej niż inne podczas wykonywania zapytań równoległych.Processing skew means that some distributions take longer than others when running parallel queries. Może się tak zdarzyć, gdy dane są skośne.This can happen when the data is skewed.

Aby zrównoważyć przetwarzanie równoległe, wybierz kolumnę dystrybucji, która:To balance the parallel processing, select a distribution column that:

  • Ma wiele unikatowych wartości.Has many unique values. Kolumna może zawierać kilka zduplikowanych wartości.The column can have some duplicate values. Jednak wszystkie wiersze o tej samej wartości są przypisywane do tej samej dystrybucji.However, all rows with the same value are assigned to the same distribution. Ponieważ istnieją dystrybucje 60, kolumna powinna mieć co najmniej 60 unikatowych wartości.Since there are 60 distributions, the column should have at least 60 unique values. Zwykle liczba unikatowych wartości jest znacznie większa.Usually the number of unique values is much greater.
  • Nie ma wartości NULL lub ma tylko kilka wartości NULL.Does not have NULLs, or has only a few NULLs. W przypadku skrajnego przykładu, jeśli wszystkie wartości w kolumnie mają wartość NULL, wszystkie wiersze są przypisywane do tej samej dystrybucji.For an extreme example, if all values in the column are NULL, all the rows are assigned to the same distribution. W związku z tym przetwarzanie zapytań jest skośne w jednej dystrybucji i nie korzysta z przetwarzania równoległego.As a result, query processing is skewed to one distribution, and does not benefit from parallel processing.
  • Nie jest kolumną daty.Is not a date column. Wszystkie dane w tej samej dacie są gruntowe w tej samej dystrybucji.All data for the same date lands in the same distribution. Jeśli kilku użytkowników ma wszystkie filtrowanie dla tego samego dnia, tylko 1 z dystrybucji 60 wszystkie operacje przetwarzania.If several users are all filtering on the same date, then only 1 of the 60 distributions do all the processing work.

Wybierz kolumnę dystrybucji, która minimalizuje przenoszenie danychChoose a distribution column that minimizes data movement

W celu uzyskania poprawnych kwerend wyników zapytania mogą przenosić dane z jednego węzła obliczeniowego do innego.To get the correct query result queries might move data from one Compute node to another. Przenoszenie danych odbywa się często, gdy zapytania mają sprzężenia i agregacje w tabelach rozproszonych.Data movement commonly happens when queries have joins and aggregations on distributed tables. Wybór kolumny dystrybucji, która pomaga zminimalizować przenoszenie danych, jest jednym z najważniejszych strategii optymalizacji wydajności puli SQL Synapse.Choosing a distribution column that helps minimize data movement is one of the most important strategies for optimizing performance of your Synapse SQL pool.

Aby zminimalizować przenoszenie danych, wybierz kolumnę dystrybucji, która:To minimize data movement, select a distribution column that:

  • Jest używana w JOIN GROUP BY klauzulach,,, DISTINCT OVER i HAVING .Is used in JOIN, GROUP BY, DISTINCT, OVER, and HAVING clauses. Gdy dwa duże tabele faktów mają częste sprzężenia, wydajność zapytań zwiększa się, gdy obie tabele są dystrybuowane w jednej z kolumn sprzężenia.When two large fact tables have frequent joins, query performance improves when you distribute both tables on one of the join columns. Gdy tabela nie jest używana w sprzężeniach, rozważ rozłożenie tabeli w kolumnie, która jest często w GROUP BY klauzuli.When a table is not used in joins, consider distributing the table on a column that is frequently in the GROUP BY clause.
  • Nie jest używany w WHERE klauzulach.Is not used in WHERE clauses. Może to spowodować zawężenie zapytania, aby nie było uruchamiane na wszystkich dystrybucjach.This could narrow the query to not run on all the distributions.
  • Nie jest kolumną daty.Is not a date column. Klauzule WHERE często filtrują według daty.WHERE clauses often filter by date. W takim przypadku przetwarzanie może być wykonywane tylko w kilku dystrybucjach.When this happens, all the processing could run on only a few distributions.

Co zrobić, gdy żadna z kolumn nie jest dobrą kolumną dystrybucjiWhat to do when none of the columns are a good distribution column

Jeśli żadna z kolumn nie ma wystarczającej liczby odrębnych wartości dla kolumny dystrybucji, można utworzyć nową kolumnę jako element złożony jednej lub więcej wartości.If none of your columns have enough distinct values for a distribution column, you can create a new column as a composite of one or more values. Aby uniknąć przenoszenia danych podczas wykonywania zapytania, użyj kolumny dystrybucji złożonej jako kolumny sprzężenia w zapytaniach.To avoid data movement during query execution, use the composite distribution column as a join column in queries.

Po zaprojektowaniu tabeli rozproszonej przez funkcję mieszania następnym krokiem jest załadowanie danych do tabeli.Once you design a hash-distributed table, the next step is to load data into the table. Aby uzyskać wskazówki dotyczące ładowania, zobacz Omówienie ładowania.For loading guidance, see Loading overview.

Jak stwierdzić, czy Twoja kolumna dystrybucji jest dobrym wyboremHow to tell if your distribution column is a good choice

Po załadowaniu danych do tabeli rozproszonej przez funkcję tworzenia skrótów Sprawdź, czy wiersze są dystrybuowane w ramach dystrybucji 60.After data is loaded into a hash-distributed table, check to see how evenly the rows are distributed across the 60 distributions. Wiersze na dystrybucję mogą się różnić do 10% bez zauważalnego wpływu na wydajność.The rows per distribution can vary up to 10% without a noticeable impact on performance.

Ustal, czy tabela ma skośne daneDetermine if the table has data skew

Aby szybko sprawdzić pochylenie danych, należy użyć polecenia DBCC PDW_SHOWSPACEUSED.A quick way to check for data skew is to use DBCC PDW_SHOWSPACEUSED. Poniższy kod SQL zwraca liczbę wierszy tabeli, które są przechowywane w każdej z dystrybucji 60.The following SQL code returns the number of table rows that are stored in each of the 60 distributions. W celu zapewnienia zrównoważonej wydajności wiersze w tabeli rozproszonej powinny być równomiernie rozłożone na wszystkie dystrybucje.For balanced performance, the rows in your distributed table should be spread evenly across all the distributions.

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

Aby określić, które tabele mają więcej niż 10% pochylenia danych:To identify which tables have more than 10% data skew:

  1. Utwórz widok dbo. vTableSizes, który jest widoczny w artykule Przegląd tabel .Create the view dbo.vTableSizes that is shown in the Tables overview article.
  2. Uruchom zapytanie:Run the following query:
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
;

Sprawdź plany zapytania dotyczące przenoszenia danychCheck query plans for data movement

Dobra kolumna dystrybucji umożliwia łączenie i agregacje w celu uzyskania minimalnych ruchów danych.A good distribution column enables joins and aggregations to have minimal data movement. Ma to wpływ na sposób zapisywania sprzężeń.This affects the way joins should be written. Aby uzyskać minimalny ruch danych dla sprzężenia w dwóch tabelach rozproszonych z mieszaniem, jedną z kolumn sprzężeń musi być kolumna rozkład.To get minimal data movement for a join on two hash-distributed tables, one of the join columns needs to be the distribution column. Gdy dwie tabele rozproszone przez mieszanie są przyłączone do kolumny dystrybucji tego samego typu danych, sprzężenie nie wymaga przenoszenia danych.When two hash-distributed tables join on a distribution column of the same data type, the join does not require data movement. Sprzężenia mogą używać dodatkowych kolumn bez ponoszenia przenoszenia danych.Joins can use additional columns without incurring data movement.

Aby uniknąć przenoszenia danych podczas przyłączania:To avoid data movement during a join:

  • Tabele biorące udział w sprzężeniu muszą być dystrybuowane jako skrót na jednej z kolumn należących do sprzężenia.The tables involved in the join must be hash distributed on one of the columns participating in the join.
  • Typy danych kolumn sprzężenia muszą być zgodne między obiema tabelami.The data types of the join columns must match between both tables.
  • Kolumny muszą być sprzężone z operatorem Equals.The columns must be joined with an equals operator.
  • Typ sprzężenia nie może być CROSS JOIN .The join type may not be a CROSS JOIN.

Aby sprawdzić, czy w kwerendach występują przemieszczenie danych, możesz przyjrzeć się planie zapytania.To see if queries are experiencing data movement, you can look at the query plan.

Rozwiązywanie problemu z kolumną dystrybucjiResolve a distribution column problem

Nie jest konieczne rozwiązywanie wszystkich przypadków pochylenia danych.It is not necessary to resolve all cases of data skew. Dystrybucja danych polega na znalezieniu równowagi między zmniejszaniem i przenoszeniem danych.Distributing data is a matter of finding the right balance between minimizing data skew and data movement. Nie zawsze jest możliwe minimalizowanie pochylenia i przenoszenia danych.It is not always possible to minimize both data skew and data movement. Czasami korzyści wynikające z minimalnego przenoszenia danych mogą wznieść wpływ na pochylenie danych.Sometimes the benefit of having the minimal data movement might outweigh the impact of having data skew.

Aby zdecydować, czy należy rozwiązać pochylenie danych w tabeli, należy zrozumieć możliwie jak najwięcej ilości danych i zapytań w obciążeniu.To decide if you should resolve data skew in a table, you should understand as much as possible about the data volumes and queries in your workload. Aby monitorować wpływ pochylenia wydajności zapytań, można użyć kroków opisanych w artykule monitorowanie zapytań .You can use the steps in the Query monitoring article to monitor the impact of skew on query performance. Sprawdź, jak długo trwa wykonywanie dużych zapytań dotyczących poszczególnych dystrybucji.Specifically, look for how long it takes large queries to complete on individual distributions.

Ponieważ nie można zmienić kolumny dystrybucji w istniejącej tabeli, typowym sposobem rozwiązania pochylenia danych jest ponowne utworzenie tabeli z inną kolumną dystrybucji.Since you cannot change the distribution column on an existing table, the typical way to resolve data skew is to re-create the table with a different distribution column.

Utwórz ponownie tabelę z nową kolumną dystrybucjiRe-create the table with a new distribution column

W tym przykładzie używa się CREATE TABLE jako zaznaczone, aby ponownie utworzyć tabelę z inną kolumną dystrybucji skrótów.This example uses CREATE TABLE AS SELECT to re-create a table with a different hash distribution column.

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

Następne krokiNext steps

Aby utworzyć tabelę rozproszoną, należy użyć jednej z następujących instrukcji:To create a distributed table, use one of these statements: