Sdílet prostřednictvím

Optimalizace výkonu pomocí seřazených columnstore indexů

  • Článek

platí pro: SQL Server 2022 (16.x) Azure SQL DatabaseAzure SQL Managed InstanceSQL Database v Microsoft Fabric

Díky povolení efektivního odstranění segmentů poskytují uspořádané indexy columnstore rychlejší výkon tím, že přeskočí velké objemy seřazených dat, které neodpovídají predikátu dotazu. Načtení dat do seřazeného columnstore indexu a jejich udržení seřazené pomocí přestavby indexu může trvat déle než u neřazených indexů kvůli operaci řazení dat; avšak dotazy na seřazené columnstore indexy mohou běžet rychleji.

Když se uživatelé dotazují na tabulku columnstore, optimalizátor zkontroluje minimální a maximální hodnoty uložené v každém segmentu. Segmenty, které jsou mimo hranice predikátu dotazu, se nečtou z disku do paměti. Dotaz se může dokončit rychleji, pokud je počet segmentů, které se mají přečíst, a jejich celková velikost menší.

Informace o dostupnosti seřazeného indexu columnstore najdete pod dostupnost seřazeného indexu columnstore.

Další informace o nedávno přidaných funkcích pro indexy columnstore najdete v tématu Novinky v indexech columnstore.

Uspořádaný vs. neuspořádaný sloupcový index

V indexu columnstore se data v každém sloupci každé skupiny řádků komprimují do samostatného segmentu. Každý segment obsahuje metadata popisující minimální a maximální hodnoty, takže segmenty, které jsou mimo hranice predikátu dotazu, se během provádění dotazu nečtou z disku.

Pokud index columnstore není seřazený, tvůrce indexů data před komprimací do segmentů neřadí. To znamená, že k segmentům s překrývajícími se rozsahy hodnot může dojít, což způsobí, že dotazy budou číst další segmenty z disku a jejich dokončení trvá déle.

Když vytvoříte uspořádaný index columnstore, databázový stroj seřadí existující data podle klíčů řazení, které zadáte, než je nástroj pro tvorbu indexu zkomprimuje do segmentů. Díky seřazeným datům je překrývání segmentů omezené nebo eliminované, což umožňuje, aby dotazy měly efektivnější odstranění segmentů, a tím i rychlejší výkon, protože z disku je méně segmentů pro čtení.

V závislosti na dostupné paměti, velikosti dat, stupni paralelismu, typu indexu (clusterovaný vs. neclusterovaný) a typu sestavení indexu (offline vs. online) může být řazení pro uspořádané indexy columnstore buď plné (bez překrývání segmentů) nebo částečné (s určitou mírou překrývání segmentů). Částečné řazení například nastane v případě, že dostupná paměť není dostatečná pro úplné řazení. Dotazy využívající uspořádaný index columnstore se často spouštějí rychleji než s neřazeným indexem, i když byl seřazený index sestaven pomocí částečného řazení.

Úplné řazení je poskytováno pro uspořádané clusterované indexy sloupcového úložiště vytvořené nebo znovu vytvořené s volbami ONLINE = ON a MAXDOP = 1. V takovém případě není řazení omezeno dostupnou pamětí, protože používá databázi tempdb k přelití dat, která se nevejdou do paměti. To může zpomalit proces sestavení indexu kvůli dalším tempdb vstupně-výstupním operacím. Při opětovném sestavení online indexu však dotazy můžou pokračovat v používání existujícího indexu, zatímco se znovu sestavuje nový seřazený index.

Úplné řazení může být také k dispozici pro uspořádané clusterované a neclusterované columnstore indexy vytvořené nebo znovu sestavené s možnostmi jak ONLINE = OFF, tak i MAXDOP = 1, pokud je množství dat k seřazení dostatečně malé, aby se plně vešlo do dostupné paměti.

Ve všech ostatních případech je řazení v uspořádaných indexech columnstore částečné.

Poznámka

V současné době je možné uspořádané indexy columnstore vytvořit nebo znovu vytvořit online pouze ve službě Azure SQL Database a ve službě Azure SQL Managed Instance se zásadami Always-up-to-date update.

Pokud chcete zkontrolovat rozsahy segmentů pro sloupec a zjistit, jestli se nějaký segment nepřekrývá, použijte následující dotaz a nahraďte zástupné symboly názvy schématu, tabulky a sloupců:

SELECT OBJECT_SCHEMA_NAME(o.object_id) AS schema_name,
       o.name AS table_name,
       cols.name AS column_name,
       pnp.index_id,
       cls.row_count,
       pnp.data_compression_desc,
       cls.segment_id,
       cls.column_id,
       cls.min_data_id,
       cls.max_data_id
FROM sys.partitions AS pnp
INNER JOIN sys.tables AS t
ON pnp.object_id = t.object_id
INNER JOIN sys.objects AS o
ON t.object_id = o.object_id
INNER JOIN sys.column_store_segments AS cls
ON pnp.partition_id = cls.partition_id
INNER JOIN sys.columns AS cols
ON o.object_id = cols.object_id
   AND
   cls.column_id = cols.column_id
WHERE OBJECT_SCHEMA_NAME(o.object_id) = '<Schema Name>'
      AND
      o.name = '<Table Name>'
      AND
      cols.name = '<Column Name>'
ORDER BY o.name, pnp.index_id, cls.min_data_id;

Například výstup z tohoto dotazu pro plně seřazený index columnstore může vypadat následovně. Všimněte si, že sloupce min_data_id a max_data_id se pro různé segmenty vůbec nepřekrývají.

schema_name table_name column_name index_id row_count data_compression_desc segment_id column_id min_data_id max_data_id
----------- ---------- ----------- -------- --------- --------------------- ---------- --------- ----------- -----------
dbo         Table1     Column1     1        479779    COLUMNSTORE           0          1         -17         1469515
dbo         Table1     Column1     1        887658    COLUMNSTORE           1          1         1469516     2188146
dbo         Table1     Column1     1        930144    COLUMNSTORE           2          1         2188147     11072928

Poznámka

V uspořádaném indexu columnstore se nová data vzniklá ze stejné dávky DML nebo operací načítání dat seřadí pouze v rámci této dávky. Neexistuje globální řazení, které zahrnuje existující data v tabulce.

Pokud chcete data v indexu seřadit po vložení nových dat nebo aktualizaci existujících dat, znovu vytvořte index.

V případě offline opětovného sestavení indexu columnstore s oddíly je opětovné sestavení provedeno po jednom oddílu. Data v oddílu, který se znovu sestavuje, nejsou k dispozici, dokud není opětovné sestavení tohoto oddílu dokončeno.

Data zůstávají dostupná během online opětovného sestavení. Další informace naleznete v tématu Provádění operací indexu online.

Výkon dotazů

Zvýšení výkonu z seřazeného indexu columnstore závisí na vzorech dotazu, velikosti dat, způsobu řazení dat, fyzické struktuře segmentů a výpočetních prostředcích dostupných pro provádění dotazů.

Dotazy s následujícími vzorci běží obvykle rychleji se seřazenými indexy columnstore.

  • Dotazy, které mají predikáty rovnosti, nerovnosti nebo rozsahu
  • Dotazy, kde jsou sloupce predikátu a seřazené sloupce CCI stejné.

V tomto příkladu má tabulka T1 clusterovaný index columnstore seřazený v posloupnosti Col_C, Col_Ba Col_A.

CREATE CLUSTERED COLUMNSTORE INDEX MyOrderedCCI ON T1
ORDER (Col_C, Col_B, Col_A);

Výkon dotazů 1 a 2 může více těžit ze seřazeného columnstore indexu než výkon dotazů 3 a 4, protože odkazují na všechny seřazené sloupce.

-- query 1
SELECT *
FROM T1
WHERE Col_C = 'c' AND Col_B = 'b' AND Col_A = 'a';

-- query 2
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_C = 'c' AND Col_A = 'a';

-- query 3
SELECT *
FROM T1
WHERE Col_B = 'b' AND Col_A = 'a';

-- query 4
SELECT *
FROM T1
WHERE Col_A = 'a' AND Col_C = 'c';

Výkon načítání dat

** Výkon načítání dat do tabulky s uspořádaným indexem columnstore je podobný jako u partitionované tabulky. Načítání dat může trvat déle než u neřazeného indexu columnstore kvůli operaci řazení dat, ale dotazy můžou běžet rychleji.

Zmenšení překrývajících se segmentů

Počet překrývajících se segmentů závisí na velikosti dat k seřazení, dostupné paměti a maximálním stupni paralelismu (MAXDOP) během seřazeného sestavení indexu columnstore. Následující strategie snižují překrývající se segmenty, ale proces sestavení indexu může trvat déle.

  • Pokud je k dispozici online sestavování indexu, při vytváření uspořádaného clusterovaného columnstore indexu použijte možnosti ONLINE = ON i MAXDOP = 1. Tím se vytvoří plně seřazený index.
  • Pokud online sestavení indexu není dostupné, použijte možnost MAXDOP = 1.
  • Před načtením předem seřaďte data podle klíčů řazení.

Pokud je MAXDOP větší než 1, každé vlákno použité při sestavování seřazeného indexu columnstore pracuje na podmnožině dat a místně je seřadí. Neexistuje globální řazení mezi daty seřazenými podle různých vláken. Použití paralelních vláken může zkrátit dobu vytvoření indexu, ale generuje překrývající se segmenty než při použití jednoho vlákna. Použití jedné operace s vlákny poskytuje nejvyšší kvalitu komprese. Pomocí příkazu CREATE INDEX můžete zadat MAXDOP.

Příklady

Kontrola seřazených sloupců a řadových řad

SELECT object_name(c.object_id) AS table_name,
       c.name AS column_name,
       i.column_store_order_ordinal
FROM sys.index_columns AS i
INNER JOIN sys.columns AS c
ON i.object_id = c.object_id
   AND
   c.column_id = i.column_id
WHERE column_store_order_ordinal <> 0;

Vytvoření uspořádaného indexu columnstore

Index columnstore seřazený podle clusterů:

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2);

Neclusterovaný uspořádaný index columnstore:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2);

Přidání nebo odebrání sloupců objednávek a opětovné sestavení existujícího uspořádaného indexu columnstore

Clusterovaný seřazený sloupcový index

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Neuspořádaný neclusterovaný skladovací index sloupců:

CREATE NONCLUSTERED COLUMNSTORE INDEX ONCCI
ON dbo.Table1
(
Column1, Column2, Column3
)
ORDER (Column1, Column2)
WITH (DROP_EXISTING = ON);

Vytvoření online uspořádaného clusterovaného indexu columnstore s úplným řazením na heapové tabulce

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (ONLINE = ON, MAXDOP = 1);

Opětovné online sestavení uspořádaného clusterovaného indexu pro úložiště sloupců s úplným řazením

CREATE CLUSTERED COLUMNSTORE INDEX OCCI
ON dbo.Table1
ORDER (Column1)
WITH (DROP_EXISTING = ON, ONLINE = ON, MAXDOP = 1);

Související obsah