Pokyny k návrhu distribuovaných tabulek s využitím vyhrazeného fondu SQL v Azure Synapse Analytics
Tento článek obsahuje doporučení pro návrh distribuovaných tabulek hash a distribuovaných tabulek s kruhovým dotazováním ve vyhrazených fondech SQL.
Tento článek předpokládá, že máte zkušenosti s koncepty distribuce 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 zobrazuje jako jedna tabulka, ale řádky jsou ve skutečnosti uložené v 60 distribucích. Řádky se distribuují pomocí algoritmu hash nebo kruhového dotazování.
Distribuce hodnot hash zlepšuje výkon dotazů u rozsáhlých tabulek faktů a je cílem tohoto článku. Distribuce kruhového dotazování je užitečná pro zvýšení rychlosti načítání. Tyto volby návrhu mají významný dopad na zlepšení výkonu dotazů a načítání.
Další možností úložiště tabulek je replikace malé tabulky napříč všemi výpočetními uzly. Další informace najdete v tématu Pokyny k návrhu replikovaných tabulek. Pokud si chcete rychle vybrat mezi třemi možnostmi, přečtěte si téma Distribuované tabulky v přehledu tabulek.
V rámci návrhu tabulek co nejvíce porozumíte datům a způsobu jejich dotazování. Zvažte například tyto otázky:
- Jak velká je tabulka?
- Jak často se tabulka aktualizuje?
- Mám ve vyhrazeném fondu SQL tabulky faktů a dimenzí?
Distribuovaná hodnota hash
Distribuovaná zatřiďovací tabulka distribuuje řádky tabulky napříč výpočetními uzly pomocí deterministické hashovací funkce pro přiřazení každého řádku k jedné distribuci.
Vzhledem k tomu, že identické hodnoty se vždy hashuje do stejné distribuce, SQL Analytics má integrovanou znalost umístění řádků. Ve vyhrazeném fondu SQL se tyto znalosti používají k minimalizaci přesunu dat během dotazů, což zvyšuje výkon dotazů.
Distribuované zatřiďovací tabulky fungují dobře pro velké tabulky faktů ve hvězdicovém schématu. Můžou mít velký počet řádků a přesto dosahovat vysokého výkonu. Existuje několik aspektů návrhu, které vám pomůžou dosáhnout výkonu, pro který je distribuovaný systém navržený. Volba vhodného distribučního sloupce nebo sloupců je jedním z takových aspektů, které jsou popsány v tomto článku.
Použití tabulky distribuované hodnotou hash zvažte v následujících 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 s kruhovým dotazem distribuuje řádky tabulky rovnoměrně napříč všemi distribucemi. Přiřazení řádků k distribucím je náhodné. Na rozdíl od hash distribuovaných tabulek není zaručeno, že řádky se stejnými hodnotami budou přiřazeny ke stejné distribuci.
V důsledku toho systém někdy potřebuje vyvolat operaci přesunu dat, aby data lépe uspořádal, než dokáže dotaz vyřešit. Tento dodatečný krok může zpomalit vaše dotazy. Například připojení k tabulce s kruhovým dotazem obvykle vyžaduje přemístit řádky, což je výkonový hit.
Použití distribuce kruhového dotazování pro tabulku zvažte v následujících scénářích:
- Když začínáte jako jednoduchý výchozí bod, protože je to výchozí
- Pokud neexistuje žádný zřejmý spojovací klíč
- Pokud neexistuje vhodný sloupec s kandidátem na distribuci tabulky
- Pokud tabulka nesdílí společný klíč spojení s jinými tabulkami
- Pokud je spojení méně významné než jiná spojení v dotazu
- Pokud je tabulka dočasnou pracovní tabulkou
Kurz Načtení dat taxislužby v New Yorku nabízí příklad načtení dat do pracovní tabulky s kruhovým dotazem.
Volba distribučního sloupce
Distribuovaná tabulka hodnot hash obsahuje distribuční sloupec nebo sadu sloupců, které jsou klíčem hash. Následující kód například vytvoří tabulku distribuovanou hodnotou hash s distribučním ProductKey sloupcem.
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])
);
Distribuci hodnoty hash je možné použít u více sloupců, aby bylo rozdělení základní tabulky rovnoměrnější. Rozdělení s více sloupci vám umožní vybrat pro distribuci až osm sloupců. To nejen snižuje nerovnoměrnou distribuci dat v průběhu času, ale také zlepšuje výkon dotazů. Příklad:
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])
);
Poznámka
Distribuci s více sloupci ve službě Azure Synapse Analytics je možné povolit změnou úrovně kompatibility databáze na 50 pomocí tohoto příkazu.
ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50; Další informace o nastavení úrovně kompatibility databáze najdete v tématu ALTER DATABASE SCOPED CONFIGURATION. Další informace o distribucích s více sloupci najdete v tématech CREATE MATERIALIZED VIEW, CREATE TABLE nebo CREATE TABLE AS SELECT.
Data uložená v distribučních sloupcích je možné aktualizovat. Aktualizace dat v distribučních sloupcích může vést k operaci náhodného prohazování dat.
Volba distribučních sloupců je důležitým rozhodnutím o návrhu, protože hodnoty ve sloupcích hash určují způsob distribuce řádků. Nejlepší volba závisí na několika faktorech a obvykle zahrnuje kompromisy. Jakmile vyberete distribuční sloupec nebo sadu sloupců, nemůžete je změnit. Pokud jste nevybrali nejlepší sloupce napoprvé, můžete pomocí příkazu CREATE TABLE AS SELECT (CTAS) znovu vytvořit tabulku s požadovaným distribučním hashovým klíčem.
Volba distribučního sloupce s daty, která se distribuují rovnoměrně
Pro zajištění nejlepšího výkonu by všechny distribuce měly mít přibližně stejný počet řádků. Pokud má jedna nebo více distribucí nepřiměřený počet řádků, některé distribuce dokončí svoji část paralelního dotazu před ostatními. Vzhledem k tomu, že dotaz nelze dokončit, dokud se nedokončí zpracování všech distribucí, je každý dotaz pouze stejně rychlý jako nejpomalejší distribuce.
- Nerovnoměrná distribuce dat znamená, že data nejsou distribuovaná rovnoměrně napříč distribucemi.
- Nerovnoměrná distribuce zpracování znamená, že některé distribuce při spouštění paralelních dotazů trvá déle než jiné. K tomu může dojít při nerovnoměrné distribuci dat.
Pokud chcete vyrovnávat paralelní zpracování, vyberte distribuční sloupec nebo sadu sloupců, které:
- Má mnoho jedinečných hodnot. Distribuční sloupce můžou mít duplicitní hodnoty. Všechny řádky se stejnou hodnotou jsou přiřazeny ke stejné distribuci. Vzhledem k tomu, že existuje 60 rozdělení, některá rozdělení můžou mít > 1 jedinečné hodnoty, zatímco jiná můžou končit nulovými hodnotami.
- Neobsahuje knihovny NUL nebo má pouze několik. V extrémním příkladu platí, že pokud mají všechny hodnoty v distribučních sloupcích hodnotu NULL, přiřadí se všechny řádky ke stejné distribuci. V důsledku toho je zpracování dotazů zkreslené na jednu distribuci a paralelní zpracování neprospívá.
- Není sloupec kalendářního data. Všechna data pro stejné datum přistane ve stejné distribuci nebo seskupí záznamy podle data. Pokud několik uživatelů filtruje ke stejnému datu (například dnešní datum), pak veškeré zpracování provede pouze 1 z 60 distribucí.
Volba distribučního sloupce, který minimalizuje přesun dat
Pokud chcete získat správný výsledek dotazu, mohou dotazy přesunout data z jednoho výpočetního uzlu do druhého. K přesunu dat často dochází, když dotazy mají spojení a agregace v distribuovaných tabulkách. Volba distribučního sloupce nebo sady sloupců, které pomáhají minimalizovat přesun dat, je jednou z nejdůležitějších strategií pro optimalizaci výkonu vyhrazeného fondu SQL.
Pokud chcete přesun dat minimalizovat, vyberte distribuční sloupec nebo sadu sloupců, které:
- Používá se v
JOINklauzulích ,GROUP BY,DISTINCT,OVERaHAVING. Když se často spojují dvě velké tabulky faktů, zvýší se výkon dotazů, když obě tabulky distribuujete do jednoho ze sloupců spojení. Pokud se tabulka nepoužívá ve spojeních, zvažte distribuci tabulky na sadu sloupců, která se často nachází v klauzuliGROUP BY. - V klauzulích se nepoužívá
WHERE. Pokud jsou klauzule dotazuWHEREa distribuční sloupce tabulky ve stejném sloupci, může dotaz narazit na vysokou nerovnoměrnou distribuci dat, což může vést k tomu, že zatížení zpracování bude klesat pouze na několik distribucí. To má vliv na výkon dotazů, v ideálním případě zatížení zpracování sdílí mnoho distribucí. - Není sloupec kalendářního data.
WHEREklauzule často filtrují podle data. Když k tomu dojde, může veškeré zpracování běžet jenom u několika distribucí, které mají vliv na výkon dotazů. V ideálním případě zatížení zpracování sdílí mnoho distribucí.
Jakmile navrhnete tabulku distribuovanou hodnotou hash, dalším krokem je načtení dat do tabulky. Pokyny k načítání najdete v tématu Přehled načítání.
Jak zjistit, jestli je vaše distribuce vhodnou volbou
Po načtení dat do tabulky s distribuovanou hodnotou hash zkontrolujte, jak rovnoměrně jsou řádky rozdělené mezi 60 distribucí. Počet řádků na distribuci se může lišit až o 10 %, aniž by to mělo znatelný dopad na výkon. Zvažte následující témata a vyhodnoťte distribuční sloupce.
Určení, jestli tabulka obsahuje nerovnoměrnou distribuci dat
Rychlý způsob, jak zkontrolovat nerovnoměrnou distribuci dat, je použít DBCC PDW_SHOWSPACEUSED. Následující kód SQL vrátí počet řádků tabulky, které jsou uloženy v každé z 60 distribucí. Kvůli vyváženému výkonu by řádky v distribuované tabulce měly být rovnoměrně rozložené napříč všemi distribucemi.
-- Find data skew for a distributed table
DBCC PDW_SHOWSPACEUSED('dbo.FactInternetSales');
Pokud chcete zjistit, které tabulky mají více než 10% nerovnoměrnou distribuci dat:
- Vytvořte zobrazení
dbo.vTableSizes, které je znázorněno v článku Přehled tabulek . - 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 přesunu dat v plánech dotazů
Dobrá sada distribučních sloupců umožňuje spojením a agregacím minimální přesun dat. To má vliv na způsob psaní spojení. Pokud chcete dosáhnout minimálního přesunu dat pro spojení ve dvou distribuovaných tabulkách hash, musí být jeden ze sloupců spojení v distribučních sloupcích nebo sloupcích. Pokud se dvě distribuované tabulky hash spojí na distribuční sloupec stejného datového typu, spojení nevyžaduje přesun dat. Spojení můžou používat další sloupce bez přesunu dat.
Pokud chcete zabránit přesunu dat během spojení:
- Tabulky zahrnuté do spojení musí být distribuované v jednom ze sloupců, které se spojení účastní.
- Datové typy sloupců spojení se musí shodovat mezi oběma tabulkami.
- Sloupce musí být spojeny pomocí operátoru 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 dotazů.
Řešení problému s distribučním sloupcem
Není nutné řešit všechny případy nerovnoměrné distribuce dat. Distribuce dat spočívá v nalezení správné rovnováhy mezi minimalizací nerovnoměrné distribuce dat a přesunem dat. Není vždy možné minimalizovat nerovnoměrnou distribuci dat i přesun dat. Někdy může výhoda minimálního přesunu dat převážit dopad nerovnoměrné distribuce dat.
Pokud se chcete rozhodnout, jestli byste měli vyřešit nerovnoměrnou distribuci dat v tabulce, měli byste co nejvíce porozumět objemům dat a dotazům ve vašich úlohách. Pomocí kroků v článku Monitorování dotazů můžete monitorovat dopad nerovnoměrné distribuce na výkon dotazů. Konkrétně zjistěte, jak dlouho trvá dokončení velkých dotazů u jednotlivých distribucí.
Vzhledem k tomu, že nemůžete změnit distribuční sloupce v existující tabulce, je typickým způsobem, jak vyřešit nerovnoměrnou distribuci dat, opětovné vytvoření tabulky s jinými distribučními sloupci.
Opětovné vytvoření tabulky s novou sadou distribučních sloupců
V tomto příkladu se funkce CREATE TABLE AS SELECT používá k opětovnému vytvoření tabulky s jiným distribučním sloupcem nebo sloupci nebo sloupci distribuce hodnoty hash.
Nejprve použijte CREATE TABLE AS SELECT (CTAS) novou tabulku s novým klíčem. Pak znovu vytvořte statistiku a nakonec prohoďte tabulky tím, že je znovu pojmnujte.
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ů: