indexy na vyhrazených tabulkách fondu SQL ve službě Azure Synapse Analytics

Recommendations a příklady indexování tabulek ve vyhrazeném fondu SQL ve službě Azure Synapse Analytics.

Typy indexů

vyhrazený fond SQL nabízí několik možností indexování včetně clusterovaných indexů columnstore, clusterovaných indexů a neclusterovaných indexůa možnost bez indexu, která se označuje také jako halda.

chcete-li vytvořit tabulku s indexem, přečtěte si dokumentaci CREATE TABLE (vyhrazený SQL fond) .

Clusterované indexy columnstore

ve výchozím nastavení vytvoří vyhrazený fond SQL clusterovaný index columnstore, pokud v tabulce nejsou zadány žádné možnosti indexu. Clusterované tabulky columnstore nabízejí nejvyšší úroveň komprese dat a nejlepší celkový výkon dotazů. Clusterované tabulky columnstore budou všeobecně překoná clusterovaných indexů nebo tabulek haldy a jsou obvykle nejlepší volbou pro velké tabulky. Z těchto důvodů je clusterový columnstore nejlepší místo, kde si nejste jisti, jak indexovat tabulku.

Chcete-li vytvořit clusterovanou tabulku columnstore, stačí zadat CLUSTERED COLUMNSTORE INDEX v klauzuli WITH nebo ponechat klauzuli WITH off:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED COLUMNSTORE INDEX );

Existuje několik scénářů, kdy clusterový columnstore nemůže být dobrou možností:

• Tabulky columnstore nepodporují varchar (max), nvarchar (max) a varbinary (max). Místo toho zvažte haldu nebo clusterovaný index.
• Tabulky columnstore můžou být pro přechodná data méně efektivní. Zvažte haldu a případně i dočasné tabulky.
• Malé tabulky s méně než 60 000 000 řádky. Zvažte tabulky haldy.

Tabulky haldy

když dočasně vydáte data ve vyhrazeném fondu SQL, může se stát, že při použití tabulky haldy bude celý proces rychlejší. Důvodem je to, že zatížení haldami je rychlejší než indexování tabulek a v některých případech je možné provést následné čtení z mezipaměti. Pokud načítáte data pouze do fáze před spuštěním více transformací, načítání tabulky do tabulky haldy je mnohem rychlejší než načítání dat do clusterované tabulky columnstore. Kromě toho se načítání dat do dočasné tabulky načítá rychleji než načítání tabulky do trvalého úložiště. Po načtení dat můžete v tabulce vytvořit indexy pro rychlejší výkon dotazů.

Pokud je více než 60 000 000 řádků, začne docházet k optimální kompresi v tabulkách clusteru columnstore. V případě malých vyhledávacích tabulek, méně než 60 000 000 řádků, zvažte použití HALDy nebo clusterovaného indexu pro rychlejší dotazování na výkon.

Chcete-li vytvořit tabulku haldy, stačí zadat HALDu v klauzuli WITH:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( HEAP );

Clustery a neseskupené indexy

Clusterované indexy můžou překoná clusterovaných tabulek columnstore, když je potřeba rychle načíst jeden řádek. Pro dotazy, u kterých je nutné, aby se při extrémní rychlosti využívalo jedno nebo příliš málo vyhledávání řádků, vezměte v úvahu clusterovaný index nebo neclusterovaný sekundární index. Nevýhodou použití clusterovaného indexu je to, že pouze dotazy, které mají výhodu, jsou ty, které využívají vysoce selektivní filtr u sloupce clusterovaného indexu. Pokud chcete vylepšit filtr na jiných sloupcích, můžete do jiných sloupců přidat neclusterovaný index. Každý index, který je přidán do tabulky, však přidává prostor a dobu zpracování pro načtení.

Chcete-li vytvořit tabulku clusterovaných indexů, stačí zadat CLUSTEROVANÝ INDEX v klauzuli WITH:

CREATE TABLE myTable
  (  
    id int NOT NULL,  
    lastName varchar(20),  
    zipCode varchar(6)  
  )  
WITH ( CLUSTERED INDEX (id) );

Chcete-li do tabulky přidat neclusterovaný index, použijte následující syntaxi:

CREATE INDEX zipCodeIndex ON myTable (zipCode);

Optimalizace clusterovaných indexů columnstore

Clusterované tabulky columnstore jsou v datech uspořádány do segmentů. Pro zajištění optimálního výkonu dotazů v tabulce columnstore je klíčové vysoké kvality segmentů. Kvalita segmentů se dá měřit počtem řádků v komprimované skupině řádků. Kvalita segmentu je optimální, pokud pro jednu zkomprimovanou skupinu řádků existuje minimálně 100 000 řádků a získáte výkon jako počet řádků na skupinu řádků s přístupem 1 048 576 řádků, což je většina řádků, které může skupina řádků obsahovat.

Níže uvedené zobrazení je možné vytvořit a použít ve vašem systému k výpočtu průměrných řádků na skupinu řádků a určení všech neoptimálních indexů columnstore clusteru. poslední sloupec v tomto zobrazení vygeneruje příkaz SQL, který se dá použít k opětovnému sestavení indexů.

CREATE VIEW dbo.vColumnstoreDensity
AS
SELECT
        GETDATE()                                                               AS [execution_date]
,       DB_Name()                                                               AS [database_name]
,       s.name                                                                  AS [schema_name]
,       t.name                                                                  AS [table_name]
,    COUNT(DISTINCT rg.[partition_number])                    AS [table_partition_count]
,       SUM(rg.[total_rows])                                                    AS [row_count_total]
,       SUM(rg.[total_rows])/COUNT(DISTINCT rg.[distribution_id])               AS [row_count_per_distribution_MAX]
,    CEILING    ((SUM(rg.[total_rows])*1.0/COUNT(DISTINCT rg.[distribution_id]))/1048576) AS [rowgroup_per_distribution_MAX]
,       SUM(CASE WHEN rg.[State] = 0 THEN 1                   ELSE 0    END)    AS [INVISIBLE_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE 0    END)    AS [INVISIBLE_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 0 THEN rg.[total_rows]     ELSE NULL END)    AS [INVISIBLE_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 1 THEN 1                   ELSE 0    END)    AS [OPEN_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE 0    END)    AS [OPEN_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 1 THEN rg.[total_rows]     ELSE NULL END)    AS [OPEN_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 2 THEN 1                   ELSE 0    END)    AS [CLOSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE 0    END)    AS [CLOSED_rowgroup_rows]
,       MIN(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 2 THEN rg.[total_rows]     ELSE NULL END)    AS [CLOSED_rowgroup_rows_AVG]
,       SUM(CASE WHEN rg.[State] = 3 THEN 1                   ELSE 0    END)    AS [COMPRESSED_rowgroup_count]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE 0    END)    AS [COMPRESSED_rowgroup_rows]
,       SUM(CASE WHEN rg.[State] = 3 THEN rg.[deleted_rows]   ELSE 0    END)    AS [COMPRESSED_rowgroup_rows_DELETED]
,       MIN(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MIN]
,       MAX(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_MAX]
,       AVG(CASE WHEN rg.[State] = 3 THEN rg.[total_rows]     ELSE NULL END)    AS [COMPRESSED_rowgroup_rows_AVG]
,       'ALTER INDEX ALL ON ' + s.name + '.' + t.NAME + ' REBUILD;'             AS [Rebuild_Index_SQL]
FROM    sys.[pdw_nodes_column_store_row_groups] rg
JOIN    sys.[pdw_nodes_tables] nt                   ON  rg.[object_id]          = nt.[object_id]
                                                    AND rg.[pdw_node_id]        = nt.[pdw_node_id]
                                                    AND rg.[distribution_id]    = nt.[distribution_id]
JOIN    sys.[pdw_table_mappings] mp                 ON  nt.[name]               = mp.[physical_name]
JOIN    sys.[tables] t                              ON  mp.[object_id]          = t.[object_id]
JOIN    sys.[schemas] s                             ON t.[schema_id]            = s.[schema_id]
GROUP BY
        s.[name]
,       t.[name];

Teď, když jste vytvořili zobrazení, spusťte tento dotaz, abyste identifikovali tabulky se skupinami řádků s méně než 100 KB řádků. Pokud hledáte lepší kvalitu segmentů, můžete zvýšit prahovou hodnotu 100 K.

SELECT    *
FROM    [dbo].[vColumnstoreDensity]
WHERE    COMPRESSED_rowgroup_rows_AVG < 100000
        OR INVISIBLE_rowgroup_rows_AVG < 100000;

Jakmile spustíte dotaz, můžete začít se podívat na data a analyzovat výsledky. V této tabulce je vysvětleno, co se má v analýze skupiny řádků Hledat.

Dopad údržby indexu

Sloupec Rebuild_Index_SQL v vColumnstoreDensity zobrazení obsahuje ALTER INDEX REBUILD příkaz, který lze použít k opětovnému sestavení indexů. Při opětovném sestavování indexů se ujistěte, že přidělíte dostatek paměti relaci, která znovu sestaví index. Provedete to tak, že zvýšíte třídu prostředků uživatele, který má oprávnění k opětovnému sestavení indexu v této tabulce, do doporučeného minima. Příklad najdete v tématu sestavení indexů pro zlepšení kvality segmentů dále v tomto článku.

Pro tabulku s uspořádaným clusterovaným indexem columnstore ALTER INDEX REBUILD budou data znovu řazena v databázi tempdb. Monitoruje databázi tempdb během operací opětovného sestavení. Pokud potřebujete více místa v databázi tempdb, naplánujte horizontální navýšení kapacity fondu databází. Po dokončení opětovného sestavení indexu se horizontální navýšení kapacity znovu zvětší.

Pro tabulku s seřazeným clusterovaným indexem columnstore nedojde k ALTER INDEX REORGANIZE opakovanému seřazení dat. Pokud chcete data znovu seřadit, použijte ALTER INDEX REBUILD .

Další informace o seřazených clusterovaných indexech columnstore najdete v tématu ladění výkonu pomocí seřazeného clusterovaného indexu columnstore.

Příčiny nekvalitních indexů columnstore

Pokud jste identifikovali tabulky s nízkou kvalitou segmentů, chcete identifikovat hlavní příčinu. Níže jsou uvedeny některé další běžné příčiny špatné kvality segmentu:

1. Tlak paměti při sestavení indexu
2. Velký objem operací DML
3. Malé nebo trickle operace načítání
4. Příliš mnoho oddílů

Tyto faktory můžou způsobit, že index columnstore bude mnohem menší než optimální 1 000 000 řádků na skupinu řádků. Mohou také způsobit, že řádky budou přejít do skupiny řádků Delta místo do komprimované skupiny řádků.

Tlak paměti při sestavení indexu

Počet řádků na komprimovanou skupinu řádků přímo souvisí se šířkou řádku a velikostí paměti, která je k dispozici pro zpracování skupiny řádků. Když se řádky zapisují do tabulek columnstore při zatížení paměti, může tím utrpět kvalita segmentů columnstore. Proto je osvědčeným postupem poskytnout relaci, která zapisuje do tabulek indexu columnstore, přístup k co nejvíce paměti. Vzhledem k tomu, že existuje kompromis mezi pamětí a souběžnou, jsou pokyny týkající se správného přidělení paměti závislé na datech v jednotlivých řádcích tabulky, jednotkách datového skladu, které jsou přiděleny vašemu systému, a počtu slotů souběžnosti, které můžete přiřadit do relace, která zapisuje data do tabulky.

Velký objem operací DML

Velký objem operací DML, které aktualizují a odstraňují řádky, mohou způsobit neúčinnost do columnstore. To platí hlavně v případě, že je většina řádků ve skupině řádků upravena.

• Odstranění řádku ze zkomprimované skupiny řádků pouze logicky označí řádek jako odstraněný. Řádek zůstane ve zkomprimované skupině řádků, dokud není oddíl nebo tabulka znovu sestavena.
• Vložení řádku přidá řádek do interní tabulky rowstore s názvem rozdílové skupiny řádků. Vložená řádka není převedena do columnstore, dokud není skupina rozdílových řádků zaplněna a je označena jako uzavřená. Skupiny řádků se zavřou, jakmile dosáhnou maximální kapacity 1 048 576 řádků.
• Aktualizace řádku ve formátu columnstore je zpracována jako logické odstranění a potom vložení. Vložený řádek může být uložený v rozdílovém úložišti.

Dávkové operace aktualizace a vložení, které přesahují hromadnou prahovou hodnotu 102 400 řádků na distribuci zarovnaným na oddíl, přejdou přímo do formátu columnstore. Za předpokladu rovnoměrného rozdělení byste ale museli upravovat více než 6 144 000 řádků v rámci jedné operace, aby k tomu mohlo dojít. Pokud je počet řádků pro danou distribuci zarovnaných oddílů menší než 102 400, řádky přejde do rozdílového úložiště a zůstanou tam, dokud nebudou vloženy nebo upraveny dostatečné řádky, aby se zavřela skupina řádků nebo byl index znovu sestaven.

Malé nebo trickle operace načítání

malá zatížení, která se přenášejí do vyhrazeného fondu SQL, se také někdy označují jako trickle zatížení. Typicky představují skoro konstantní datový proud dat zpracovávaných systémem. Vzhledem k tomu, že je tento datový proud blízko nepřetržitě, není objem řádků obzvláště velký. Častěji než data jsou výrazně pod prahovou hodnotou nutnou pro přímé zatížení ve formátu columnstore.

V těchto situacích je často lepší nakládat data jako první v úložišti objektů BLOB v Azure a nechat si je shromáždit před načtením. Tato technika se často označuje jako mikrodávkování.

Příliš mnoho oddílů

Další věcí, kterou je potřeba vzít v úvahu, je dopad dělení na oddíly v clusterovaných tabulkách columnstore. vyhrazený fond SQL už před vytvořením oddílů vydělí data do databáze 60. Dělení dále rozděluje vaše data. Při vytváření oddílů dat je potřeba vzít v úvahu, že každý oddíl potřebuje aspoň 1 000 000 řádků, aby bylo možné využít clusterový index columnstore. Pokud vytváříte oddíly tabulky na oddíly 100, je potřeba, aby tabulka měla aspoň 6 000 000 000 řádků, abyste využili výhod clusterovaného indexu columnstore (60 distribuce 100 oddíly 1 000 000 řádků). Pokud tabulka 100-partition neobsahuje 6 000 000 000 řádků, snižte počet oddílů nebo zvažte použití tabulky haldy.

Po načtení tabulek s některými daty pomocí následujících kroků identifikujte a znovu sestavíte tabulky s dílčími optimálními clusterovanými indexy columnstore.

Opětovné sestavení indexů pro zlepšení kvality segmentu

Krok 1: identifikace nebo vytvoření uživatele, který používá správnou třídu prostředků

Jedním z rychlých způsobů, jak okamžitě vylepšit kvalitu segmentů, je znovu sestavit index. SQL vrácený výše uvedeným zobrazením obsahuje příkaz ALTER INDEX rebuild, který lze použít k opětovnému sestavení indexů. Při opětovném sestavování indexů se ujistěte, že přidělíte dostatek paměti relaci, která znovu sestaví index. Provedete to tak, že zvýšíte třídu prostředků uživatele, který má oprávnění k opětovnému sestavení indexu v této tabulce, do doporučeného minima.

Níže je uveden příklad, jak přidělit uživateli více paměti tím, že zvýšíte jejich třídu prostředků. Chcete-li pracovat s třídami prostředků, přečtěte si téma třídy prostředků pro správu úloh.

EXEC sp_addrolemember 'xlargerc', 'LoadUser';

Krok 2: opětovné sestavení clusterovaných indexů columnstore s vyšším uživatelským třídou prostředků

Přihlaste se jako uživatel z kroku 1 ( LoadUser ), který nyní používá vyšší třídu prostředků, a spusťte příkaz ALTER index. Ujistěte se, že tento uživatel má oprávnění ALTER pro tabulky, ve kterých je index znovu sestaven. Tyto příklady ukazují, jak znovu sestavit celý index columnstore nebo jak znovu sestavit jeden oddíl. Ve velkých tabulkách je praktické znovu sestavovat indexy v jednom oddílu.

Případně místo opakovaného sestavování indexu můžete tabulku zkopírovat do nové tabulky pomocí CTAS. Jaký je nejlepší způsob? Pro velké objemy dat je CTAS obvykle rychlejší než Změna indexu. Pro menší objemy dat je snazší použít příkaz ALTER INDEX a nevyžaduje, abyste tabulku vyměnili.

-- Rebuild the entire clustered index
ALTER INDEX ALL ON [dbo].[DimProduct] REBUILD;

-- Rebuild a single partition
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5;

-- Rebuild a single partition with archival compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);

-- Rebuild a single partition with columnstore compression
ALTER INDEX ALL ON [dbo].[FactInternetSales] REBUILD Partition = 5 WITH (DATA_COMPRESSION = COLUMNSTORE);

nové sestavení indexu ve vyhrazeném fondu SQL je operace offline. Další informace o opětovném sestavení indexů naleznete v části ALTER INDEX Rebuild v tématu Defragmentaceindexů columnstore a v článku ALTER index.

Krok 3: ověření kvality segmentu clusterovaného v clusteru

Znovu spusťte dotaz, který identifikoval tabulku s nízkou kvalitou segmentů, a ověřte, že kvalita segmentu je vylepšená. Pokud se kvalita segmentu nezlepšila, může to být, že řádky v tabulce jsou velmi velké. Zvažte použití vyšší třídy prostředků nebo DWU při opakovaném sestavování indexů.

Opětovné sestavení indexů pomocí CTAS a přepínání oddílů

V tomto příkladu se používá příkaz CREATE TABLE AS Select (CTAS) a přepínání oddílů pro opětovné sestavení oddílu tabulky.

-- Step 1: Select the partition of data and write it out to a new table using CTAS
CREATE TABLE [dbo].[FactInternetSales_20000101_20010101]
    WITH    (   DISTRIBUTION = HASH([ProductKey])
            ,   CLUSTERED COLUMNSTORE INDEX
            ,   PARTITION   (   [OrderDateKey] RANGE RIGHT FOR VALUES
                                (20000101,20010101
                                )
                            )
            )
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
WHERE   [OrderDateKey] >= 20000101
AND     [OrderDateKey] <  20010101
;

-- Step 2: Switch IN the rebuilt data with TRUNCATE_TARGET option
ALTER TABLE [dbo].[FactInternetSales_20000101_20010101] SWITCH PARTITION 2 TO  [dbo].[FactInternetSales] PARTITION 2 WITH (TRUNCATE_TARGET = ON);

další informace o opětovném vytvoření oddílů pomocí CTAS najdete v tématu použití oddílů ve vyhrazeném fondu SQL.

Další kroky

Další informace o vývoji tabulek najdete v tématu vývoj tabulek.