pokyny pro návrh distribuovaných tabulek pomocí vyhrazeného fondu SQL ve službě Azure Synapse Analytics

tento článek obsahuje doporučení pro návrh distribuovaných tabulek distribuovaných algoritmem hash a kruhové dotazování ve vyhrazených fondech SQL.

v tomto článku se předpokládá, že máte zkušenosti s koncepty distribuce dat a přesunu dat ve vyhrazeném fondu SQL. Další informace najdete v tématu Architektura Azure synapse Analytics.

Co je distribuovaná tabulka?

Distribuovaná tabulka se zobrazí jako jediná tabulka, ale tyto řádky jsou ve skutečnosti uloženy v rámci 60 distribucí. Řádky jsou distribuovány pomocí algoritmu hash nebo kruhového dotazování.

Při distribuci hodnot hash se zvyšuje výkon dotazů u rozsáhlých tabulek faktů a je tento článek soustředěný. Distribuce kruhového dotazování je užitečná pro zlepšení rychlosti načítání. Tyto možnosti návrhu mají výrazný dopad na zlepšení výkonu dotazů a načítání.

Další možností úložiště tabulek je replikace malé tabulky ve všech výpočetních uzlech. Další informace najdete v tématu pokyny k návrhu replikovaných tabulek. Pokud si chcete rychle vybrat ze tří možností, přečtěte si téma distribuované tabulky v tématu Přehled tabulek.

Jako součást návrhu tabulky Pochopte co nejvíce dat a způsob dotazování na data. Zvažte například tyto otázky:

• Jak velká je tabulka?
• Jak často je tabulka aktualizována?
• mám tabulky faktů a dimenzí ve vyhrazeném fondu SQL?

Hodnota hash distribuována

Tabulka distribuovaná algoritmem hash distribuuje řádky tabulky napříč výpočetními uzly pomocí deterministické funkce hash k přiřazení každého řádku k jedné distribuci.

vzhledem k tomu, že identické hodnoty vždycky vycházejí z hodnoty hash na stejnou distribuci, SQL Analytics má integrované znalosti o umístěních řádků. ve vyhrazeném fondu SQL se tato znalost používá k minimalizaci přesunu dat během dotazů, což zvyšuje výkon dotazů.

Tabulky distribuované pomocí algoritmu hash fungují dobře u velkých tabulek faktů ve schématu hvězdičky. Mohou mít velmi velký počet řádků a stále dosahovat vysokého výkonu. K dispozici jsou určitá doporučení pro návrh, která vám pomůžou získat výkon, který distribuovaný systém nabízí. Výběr dobrého distribučního sloupce je jedním z těchto aspektů, které jsou popsány v tomto článku.

Zvažte použití tabulky distribuované pomocí algoritmu hash v těchto případech:

• Velikost tabulky na disku je větší než 2 GB.
• Tabulka obsahuje časté operace vložení, aktualizace a odstranění.

Distribuované kruhové dotazování

Distribuovaná tabulka kruhového dotazování rozděluje řádky tabulky rovnoměrně napříč všemi distribucí. Přiřazení řádků k distribucím je náhodné. Na rozdíl od tabulek distribuovaných pomocí algoritmu hash nejsou řádky se stejnými hodnotami přiřazeny ke stejné distribuci.

V důsledku toho systém někdy potřebuje vyvolat operaci přesunu dat, aby lépe organizoval vaše data předtím, než dokáže dotaz vyřešit. Tento krok navíc může zpomalit vaše dotazy. Například spojení tabulky kruhového dotazování obvykle vyžaduje přepočet řádků, což je úspěšnost výkonu.

V následujících scénářích zvažte použití distribuce kruhového dotazování pro tabulku:

• Když začnete s jednoduchým výchozím bodem, protože se jedná o výchozí
• Pokud se žádný zjevně nepřipojuje klíč
• Pokud neexistuje dobrý kandidátný sloupec pro rozdělení hodnoty hash do tabulky
• Pokud tabulka nesdílí společný klíč JOIN s jinými tabulkami
• Pokud je spojení méně významné než jiné spojení v dotazu
• Když je tabulka dočasná pracovní tabulka

Kurz načtení dat New York taxislužby města poskytuje příklad načtení dat do pracovní tabulky kruhového dotazování.

Volba distribučního sloupce

Tabulka distribuovaná algoritmem hash má distribuční sloupec, který je klíčem hash. Například následující kód vytvoří tabulku distribuovanou algoritmem hash s označením ProductKey jako distribuční sloupec.

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

Data uložená v distribučním sloupci lze aktualizovat. Aktualizace dat ve sloupci distribuce by mohla vést k náhodnému fungování dat.

Výběr distribučního sloupce je důležité rozhodnutí o návrhu, protože hodnoty v tomto sloupci určují, jak jsou řádky distribuovány. Nejlepší volba závisí na několika faktorech a obvykle zahrnuje kompromisy. Po výběru distribučního sloupce jej nelze změnit.

Pokud jste nevybrali nejlepší sloupec poprvé, můžete pomocí Create Table jako Select (CTAS) znovu vytvořit tabulku s jiným distribučním sloupcem.

Volba distribučního sloupce s daty, která se rovnoměrně distribuuje

Nejlepšího výkonu dosáhnete, pokud všechny distribuce mají přibližně stejný počet řádků. V případě, že jedna nebo více distribucí mají neúměrný počet řádků, některá distribuce dokončí jejich část paralelního dotazu ještě před ostatními. Vzhledem k tomu, že dotaz nelze dokončit, dokud nebudou dokončeny všechny distribuce, je každý dotaz co nejpomalejšího rozdělení rychlejší.

• Zešikmení dat znamená, že data nejsou rovnoměrně rozložena napříč distribucí.
• Při zpracování je možné, že některé distribuce při spouštění paralelních dotazů pobírají déle než jiné. K tomu může dojít, když jsou data nakloněná.

Pro vyrovnávání paralelního zpracování vyberte distribuční sloupec, který:

• Má mnoho jedinečných hodnot. Sloupec může obsahovat duplicitní hodnoty. Všechny řádky se stejnou hodnotou jsou přiřazeny ke stejné distribuci. Vzhledem k tomu, že existují 60 distribuce, můžou mít některá distribuce > 1 jedinečných hodnot, zatímco jiné mohou končit nulami hodnotami.
• Nemá hodnoty NULL nebo obsahuje pouze několik hodnot NULL. Pro extrémní příklad, pokud jsou všechny hodnoty ve sloupci NULL, jsou všechny řádky přiřazeny ke stejné distribuci. Výsledkem je, že zpracování dotazů je rozdělené na jednu distribuci a nemá výhodu paralelního zpracování.
• Není sloupec data. Všechna data pro stejné datum na stejné distribuci. Pokud se ke stejnému datu filtruje několik uživatelů, pak jenom 1 distribuce 60 provádí veškerou práci na zpracování.

Výběr distribučního sloupce, který minimalizuje pohyb dat

Chcete-li získat správné dotazy na výsledky dotazu, může přesunout data z jednoho výpočetního uzlu do jiného. K přesunu dat často dochází, když dotazy mají spojení a agregace v distribuovaných tabulkách. výběr distribučního sloupce, který pomáhá minimalizovat pohyb dat, je jedním z nejdůležitějších strategií pro optimalizaci výkonu vyhrazeného fondu SQL.

Chcete-li snížit pohyb dat, vyberte distribuční sloupec:

• Se používá v JOIN GROUP BY DISTINCT klauzulích,,, a OVER HAVING . Pokud mají dvě velké tabulky faktů časté spojení, výkon dotazů se vylepšuje při distribuci obou tabulek v jednom ze sloupců spojení. V případě, že se tabulka v joins nepoužívá, zvažte možnost distribuovat tabulku do sloupce, který je v GROUP BY klauzuli často.
• Se nepoužívá v WHERE klauzulích. To může zúžit dotaz tak, aby se nespouštěl ve všech distribucích.
• Není sloupec data. WHERE klauzule se často filtrují podle data. V takovém případě může být veškeré zpracování spuštěno pouze v několika distribucích.

Co dělat, když žádný ze sloupců není dobrým distribučním sloupcem

Pokud žádný z vašich sloupců nemá dostatek jedinečných hodnot pro distribuční sloupec, můžete vytvořit nový sloupec jako složený z jedné nebo více hodnot. Chcete-li se vyhnout přesunu dat během provádění dotazu, použijte jako sloupec JOIN v dotazech složený sloupec distribuce.

Po navržení tabulky distribuované pomocí algoritmu hash je dalším krokem načtení dat do tabulky. Pokyny k načtení najdete v tématu načítání přehledu.

Jak zjistit, jestli je váš distribuční sloupec dobrou volbou

Po načtení dat do tabulky distribuované pomocí algoritmu hash zkontrolujte, jak rovnoměrně jsou řádky distribuovány v rámci 60 distribucí. Řádky na distribuci se mohou lišit až o 10%, aniž by to mělo znatelný dopad na výkon.

Určení, jestli má tabulka zešikmení dat

Rychlý způsob, jak zjistit, zda je možné data zkosit, je použití příkazu DBCC PDW_SHOWSPACEUSED. následující kód SQL vrátí počet řádků tabulky, které jsou uloženy v každé z distribucí 60. U vyváženého výkonu by se měly řádky v distribuované tabulce rovnoměrně rozložit napříč všemi distribucí.

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

Určení, které tabulky mají více než 10% zešikmení dat:

1. Vytvořte dbo.vTableSizes zobrazení, které je uvedené v článku Přehled tabulek.
2. Spusťte tento dotaz:

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
;

Kontrola plánů dotazů pro přesun dat

Dobrý distribuční sloupec umožňuje spojování a agregaci minimální přesun dat. To má vliv na způsob zápisu spojení. Aby bylo možné získat minimální přesun dat pro spojení ve dvou distribuovaných tabulkách s hodnotou hash, musí být jedním ze sloupců spojení distribuční sloupec. Pokud se dvě distribuované tabulky s hodnotou hash spojí na distribuční sloupec stejného datového typu, nevyžaduje spojení přesun dat. Spojení mohou používat další sloupce bez nutnosti přesunu dat.

Jak se vyhnout přesunu dat během spojení:

• Tabulky, které jsou součástí spojení, musí být rozdělené hodnoty hash na jeden ze sloupců zapojených do spojení.
• Datové typy sloupců spojení se musí mezi oběma tabulkami shodovat.
• Sloupce musí být spojené operátorem rovná se.
• Typ spojení nemusí být CROSS JOIN .

Pokud chcete zjistit, jestli u dotazů dochází k přesunu dat, můžete se podívat na plán dotazu.

Řešení problému s distribučním sloupcem

Není nutné řešit všechny případy nekosení dat. Distribuce dat je otázkou nalezení správné rovnováhy mezi minimalizací nerovnováze dat a přesunem dat. Ne vždy je možné minimalizovat neschůdné zobrazení dat i přesun dat. V některých případech může výhoda minimálního přesunu dat převážit dopad nekosení dat.

Pokud se chcete rozhodnout, jestli chcete vyřešit nekosení dat v tabulce, měli byste co nejvíce porozumět objemům dat a dotazům v úlohách. Pomocí kroků v článku Monitorování dotazů můžete monitorovat dopad nekosení na výkon dotazů. Konkrétně hledejte, jak dlouho trvá dokončení velkých dotazů u jednotlivých distribucí.

Vzhledem k tomu, že distribuční sloupec u existující tabulky nemůžete změnit, obvyklý způsob, jak vyřešit neschůdnou distribuci dat, je znovu vytvořit tabulku s jiným distribučním sloupcem.

Znovu vytvořte tabulku s novým distribučním sloupcem.

Tento příklad používá CREATE TABLE AS SELECT k opětovnému vytvoření tabulky s jiným distribučním sloupcem hash.

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

Další kroky

Pokud chcete vytvořit distribuovanou tabulku, použijte jeden z těchto příkazů:

• CREATE TABLE (vyhrazený SQL fond)
• CREATE TABLE AS SELECT (vyhrazený SQL fond)