Sdílet prostřednictvím

Indexy columnstore – výkon dotazů

Platí na:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytický platformový systém (PDW)SQL databáze v Microsoft Fabric

Tento článek obsahuje doporučení pro dosažení rychlého výkonu dotazů s indexy columnstore.

Indexy columnstore můžou dosáhnout až 100krát lepšího výkonu úloh analýzy a datových skladů a až 10krát lepší komprese dat než tradiční indexy rowstore. Tato doporučení pomáhají vašim dotazům dosáhnout rychlého výkonu dotazů, který indexy columnstore poskytují.

Doporučení pro zlepšení výkonu dotazů

Tady je několik doporučení pro dosažení vysokého výkonu, který indexy columnstore byly navrženy poskytnout.

1. Uspořádejte data, abyste eliminovali více skupin řádků z úplného prohledávání tabulky.

  • Pečlivě zvolte pořadí vložení. V případě tradičního datového skladu se data skutečně vloží do časového pořadí a analýza se provádí v časové dimenzi. Například analýza prodeje po čtvrtletích. U tohoto typu úlohy se odstranění skupiny řádků provádí automaticky. V SQL Serveru 2016 (13.x) můžete zjistit, kolik skupin řádků bylo při zpracování dotazu přeskočeno.

  • Použijte clusterovaný index rowstore. Pokud je běžný predikát dotazu ve sloupci (například C1) nesouvisející s pořadím vložení, vytvořte clusterovaný index rowstore ve sloupci C1. Potom zahoďte clusterovaný index rowstore a vytvořte clusterovaný index columnstore. Pokud vytváříte clusterovaný index columnstore explicitně pomocí MAXDOP = 1, výsledný clusterovaný index columnstore je dokonale seřazený podle sloupce C1. Pokud zadáte MAXDOP = 8, zobrazí se překrývající se hodnoty mezi osmi skupinami řádků. U neclusterovaného indexu columnstore (NCCI), pokud tabulka obsahuje clusterovaný index rowstore, řádky jsou už seřazené podle clusterovaného indexového klíče. V tomto případě je neklastrovaný index columnstore také automaticky seřazený. Index columnstore ze své podstaty neudržuje pořadí řádků. S tím, jak se vkládají nové řádky nebo se aktualizují starší řádky, může být potřeba tento proces opakovat, protože výkon analytického dotazu se může zhoršit.

  • Implementujte dělení tabulek. Index columnstore můžete rozdělit na oddíly a potom pomocí eliminace oddílu snížit počet skupin řádků k prohledání. Faktová tabulka například ukládá nákupy zákazníků. Běžným vzorem dotazu je vyhledání čtvrtletních nákupů podle customer. V tomto případě zkombinujte sloupec pro vložení objednávky s dělením na customer sloupec. Každý oddíl obsahuje řádky pro každý customer, seřazené podle pořadí vkládání. Zvažte také použití dělení tabulky, pokud potřebujete odebrat starší data ze sloupcového úložiště. Výměna a zmenšení oddílů, které nejsou potřeba, představuje efektivní strategii odstranění dat bez generování fragmentace.

  • Vyhněte se odstraňování velkých objemů dat. Odebrání komprimovaných řádků ze skupiny řádků není synchronní operace. Dekomprese skupiny řádků by bylo nákladné, odstranit řádek a pak ho znovu zkomprimovat. Proto když odstraníte data z komprimovaných skupin řádků, budou tyto skupiny řádků stále prohledány, i když vrátí méně řádků. Pokud je počet odstraněných řádků pro několik skupin řádků dostatečně velký, aby se sloučil do menšího počtu skupin řádků, změna uspořádání columnstore zvýší kvalitu indexu a výkon dotazů se zlepší. Pokud proces odstranění dat obvykle vyprázdní celé skupiny řádků, zvažte použití dělení tabulky. Vypněte oddíly, které už nepotřebujete, a místo odstranění řádků je zkrátíte.

    Note

    Od SQL Serveru 2019 (15.x) je přesun kolekce tuple podporován úkolem pro sloučení na pozadí. Tato úloha automaticky zkomprimuje menší skupiny řádků OPEN Delta, které existovaly po určitou dobu podle interní prahové hodnoty, nebo sloučí KOMPRIMOVANÉ skupiny řádků, odkud byl odstraněn velký počet řádků. To v průběhu času zlepšuje kvalitu indexu columnstore. Pokud je potřeba odstranit velké objemy dat z indexu columnstore, zvažte rozdělení této operace do menších dávek odstranění v průběhu času. Dávkování umožňuje úloze sloučení na pozadí zpracovat úkol sloučení menších skupin řádků a zlepšit kvalitu indexu. Po odstranění dat pak není nutné plánovat časová období údržby reorganizace indexu. Další informace o termínech a konceptech columnstore najdete v tématu Indexy Columnstore: přehled.

2. Naplánujte dostatek paměti pro paralelní vytváření indexů columnstore.

Vytváření indexu columnstore je ve výchozím nastavení paralelní operace, pokud není omezená paměť. Paralelní vytváření indexu vyžaduje více paměti než sériové vytváření indexu. Pokud je k dispozici dostatek paměti, vytvoření indexu columnstore trvá řádově 1,5krát delší dobu než vytvoření B-stromu pro stejné sloupce.

Paměť potřebná k vytvoření indexu columnstore závisí na počtu sloupců, počtu řetězcových sloupců, stupni paralelismu (DOP) a charakteristikách dat. Pokud má například tabulka méně než jeden milion řádků, databázový stroj používá k vytvoření indexu columnstore pouze jedno vlákno.

Pokud tabulka obsahuje více než milion řádků, ale databázovému stroji se nepodaří získat dostatečné množství paměti k vytvoření indexu s použitím MAXDOP, automaticky sníží přidělení paměti podle potřeby. V některých případech musí být DOP snížen na jeden, aby bylo možné vytvořit index v rámci omezené paměti v rámci přidělené dostupné paměti.

Vzhledem k tomu, že SQL Server 2016 (13.x), dotaz vždy funguje v dávkovém režimu. V předchozích verzích se dávkové spouštění používá jenom v případě, že je DOP větší než jeden.

Vysvětlení výkonu columnstore

Indexy columnstore dosahují vysokého výkonu dotazů kombinací vysokorychlostního dávkového zpracování v paměti s technikami, které výrazně snižují vstupně-výstupní požadavky. Vzhledem k tomu, že analytické dotazy prohledávají velký počet řádků, obvykle jsou vázané na vstupně-výstupní operace, a proto je pro návrh indexů columnstore zásadní omezení vstupně-výstupních operací během provádění dotazů. Jakmile se data načtou do paměti, je důležité snížit počet operací v paměti.

Indexy columnstore snižují nároky na I/O a optimalizují operace v paměti díky vysoké kompresi dat, eliminaci nepotřebných dat ve sloupcích, eliminaci řádkových skupin a dávkovému zpracování.

Komprese dat

Indexy columnstore dosahují až 10krát větší komprese dat než indexy rowstore. Tím se výrazně sníží vstupně-výstupní operace potřebné ke spouštění analytických dotazů, a tím se zlepší výkon dotazů.

  • Indexy columnstore čtou komprimovaná data z disku, což znamená, že do paměti musí být načteno méně bajtů dat.

  • Indexy columnstore ukládají data v komprimované podobě v paměti, což snižuje vstupně-výstupní operace tím, že se vyhne čtení stejných dat do paměti. Například při kompresi 10krát můžou indexy columnstore uchovávat 10krát více dat v paměti v porovnání s ukládáním dat v nekomprimované podobě. S více daty v paměti je pravděpodobnější, že index columnstore najde data, která potřebuje v paměti, aniž by došlo k zbytečnému čtení z disku.

  • Indexy Columnstore komprimují data podle sloupců místo řádků, dosahuje vysoké míry komprese a snižuje velikost dat uložených na disku. Každý sloupec se komprimuje a ukládá nezávisle. Data ve sloupci mají vždy stejný datový typ a obvykle mají podobné hodnoty. Techniky komprese dat sloupcového úložiště jsou skvělé pro dosažení vyšší komprese, když jsou hodnoty podobné.

Například tabulka faktů ukládá adresy zákazníků a má sloupec pro country-region. Celkový počet možných hodnot je menší než 200. Některé z těchto hodnot se často opakují. Pokud tabulka faktů obsahuje 100 milionů řádků, country-region sloupec se snadno zkomprimuje a vyžaduje malé úložiště. Komprese řádků po řádcích nemůže tímto způsobem využít podobnosti hodnot sloupců a musí použít více bajtů ke kompresi hodnot ve sloupci country-region.

Odstranění sloupce

Indexy columnstore přeskočí čtení ve sloupcích, na které dotaz neodkazuje. Eliminování sloupců dále snižuje vstupně-výstupní operace pro provádění dotazů, a tím zvyšuje výkon dotazů.

  • Odstranění sloupce je možné, protože data jsou uspořádaná a komprimovaná podle sloupce. Naproti tomu když jsou data uložená po řádce, hodnoty sloupců v každém řádku jsou fyzicky uložené společně a nelze je snadno oddělit. Procesor dotazů musí číst celý řádek, aby načetl konkrétní hodnoty sloupců, což zvyšuje vstupně-výstupní operace, protože se do paměti zbytečně načítají nadbytečná data.

Pokud má například tabulka 50 sloupců a dotaz používá pouze pět z těchto sloupců, index columnstore načte z disku pouze pět sloupců. Přeskočí čtení v ostatních 45 sloupcích, což snižuje počet vstupně-výstupních operací o dalších 90%za předpokladu, že všechny sloupce mají podobnou velikost. Pokud jsou stejná data uložená v úložišti řádků, procesor dotazů musí přečíst zbývající 45 sloupců.

Odstranění skupiny řádků

Pro úplné prohledávání tabulek obvykle velké procento dat neodpovídá kritériím predikátu dotazu. Pomocí metadat je index columnstore schopen přeskočit čtení ve skupinách řádků, které neobsahují data požadovaná pro výsledek dotazu, a to vše bez skutečné vstupně-výstupní operace. Tato schopnost, označovaná jako odstranění skupiny řádků, snižuje vstupně-výstupní operace pro úplné prohledávání tabulek a tím zlepšuje výkon dotazů.

Kdy index columnstore potřebuje provést úplnou kontrolu tabulky?

Počínaje SQL Serverem 2016 (13.x) můžete vytvořit jeden nebo více běžných neclusterovaných indexů rowstore nebo B-tree v clusterovém indexu columnstore. Neclusterované indexy stromu B mohou urychlit dotaz, který má predikát rovnosti nebo predikát s malým rozsahem hodnot. V případě složitějších predikátů může optimalizátor dotazů zvolit úplnou kontrolu tabulky. Bez možnosti přeskočit skupiny řádků může být úplná kontrola tabulky časově náročná, zejména u velkých tabulek.

Kdy má analytický dotaz výhodu odstranění skupiny řádků pro úplnou kontrolu tabulky?

Například maloobchodní firma modeluje svá prodejní data pomocí tabulky faktů s klastrovým columnstore indexem. Každý nový prodej ukládá různé atributy transakce, včetně data, kdy se produkt prodává. Zajímavé je, že i když indexy columnstore nezaručují seřazené pořadí, řádky v této tabulce se načtou v pořadí seřazeném podle data. V průběhu času se tato tabulka zvětšuje. I když maloobchodní firma může uchovávat prodejní data za posledních 10 let, analytický dotaz může potřebovat vypočítat agregaci za poslední čtvrtletí. Indexy columnstore můžou eliminovat přístup k datům za předchozích 39 čtvrtletí pouhým pohledem na metadata sloupce kalendářního data. Dochází k 97%% snížení množství dat, která se načítají do paměti a zpracovávají.

Které skupiny řádků se přeskočí při prohledávání celé tabulky?

K určení skupin řádků, které se mají odstranit, index columnstore používá metadata k uložení minimální a maximální hodnoty každého segmentu sloupce pro každou skupinu řádků. Pokud žádná z oblastí segmentů sloupců nesplňuje kritéria predikátu dotazu, celá skupina řádků se přeskočí, aniž by se prováděly skutečné vstupně-výstupní operace. To funguje, protože data se obvykle načítají v seřazeném pořadí. I když řazení řádků není zaručené, podobné datové hodnoty se často nacházejí ve stejné skupině řádků nebo sousední skupině řádků.

Další informace o skupinách řádků najdete v pokynech k návrhu indexu Columnstore.

Spouštění v dávkovém režimu

Spouštění dávkového režimu zpracovává řádky po skupinách, obvykle až 900 současně, aby se zlepšila efektivita. Například dotaz SELECT SUM(Sales) FROM SalesData vypočítá celkový prodej z SalesData tabulky. V dávkovém režimu dotazovací modul zpracovává data ve skupinách 900 řádků. Tento přístup snižuje náklady na přístup k metadatům a další typy režijních nákladů tím, že je rozprostírá do všech řádků v dávce, namísto aby se režijní náklady vztahovaly na každý jednotlivý řádek. Dávkový režim navíc pracuje s komprimovanými daty, pokud je to možné, a odstraňuje některé operátory výměny používané v režimu řádků, čímž výrazně urychluje analytické dotazy.

Ne všechny operátory spouštění dotazů se dají spustit v dávkovém režimu. Například operace jazyka pro manipulaci s daty (DML), jako jsou vložení, odstranění nebo aktualizace, se provádějí po jednom řádku. Operátor dávkového režimu, jako je Scan, Join, Aggregate, Sort a další, může zlepšit výkon dotazů. Vzhledem k tomu, že index columnstore byl zaveden v SQL Serveru 2012 (11.x), existuje trvalé úsilí o rozšíření operátorů, které lze spustit v dávkovém režimu. Následující tabulka uvádí operátory, které běží v dávkovém režimu podle verze produktu.

Operátory dávkového režimu Při použití SQL Server 2012 (11.x) SQL Server 2014 (12.x) SQL Server 2016 (13.x) a SQL databáze1 Comments
Operace DML (vložení, odstranění, aktualizace, sloučení) no no no Zvýšení výkonu při použití dávkového režimu s DML není významné.
Prohledávání sloupcového indexu SCAN Není k dispozici yes yes U indexů columnstore můžeme predikát odeslat do uzlu SCAN.
index columnstore prohledání (neklastrovaný) SCAN yes yes yes yes
vyhledávání pomocí indexu Není k dispozici Není k dispozici no Operaci hledání provádíme prostřednictvím neclusterovaného indexu B-tree v režimu řádků.
výpočet skaláru Výraz, který se vyhodnotí jako skalární hodnota yes yes yes Stejně jako všechny operátory dávkového režimu existují určitá omezení datového typu.
zřetězení UNION a UNION ALL no yes yes
filtr Použití predikátů yes yes yes
shoda hodnot hash Agregační funkce založené na hodnotě hash, vnější spojení hash, spojení s pravou hodnotou hash, levé spojení hash, pravé vnitřní spojení, levé vnitřní spojení yes yes yes Omezení agregace: pro řetězce není minimum ani maximum. Dostupné agregační funkce jsou sum/count/avg/min/max.
Omezení spojení: Žádná spojení s neodpovídajícími typy u typů jiných než celočíselných.
sloučení spojení no no no
Dotazy s více vlákny yes yes yes
vnořené smyčky no no no
Dotazy s jedním vláknem spuštěné v rámci MAXDOP 1 no no yes
Dotazy s jedním vláknem s plánem sériového dotazu no no yes
řadit Klauzule ORDER BY při skenování s indexem columnstore. no no yes
nejvyšší řazení no no yes
agregace oken Není k dispozici Není k dispozici yes Nový operátor v SQL Serveru 2016 (13.x).

1 platí pro SQL Server 2016 (13.x), úrovně SQL Database Premium, úrovně Standard – S3 a vyšší a všechny úrovně virtuálních jader a systém PDW (Analytics Platform System)

Další informace najdete v průvodci architekturou zpracování dotazů.

Agregace odsdílení změn

Normální postup provádění pro agregaci výpočtů k načtení vyhovujících řádků z uzlu SCAN a agregaci hodnot v režimu Batch. I když to přináší dobrý výkon, počínaje SQL Serverem 2016 (13.x), je možné agregační operaci odeslat do uzlu SCAN. Agregační zpracování zlepšuje výkon agregovaných výpočtů o několik řádů při využití režimu dávkového zpracování, za předpokladu, že jsou splněny následující podmínky:

  • Agregace jsou MIN, MAX, SUMCOUNT a COUNT(*).
  • Operátor agregace musí být nad uzlem SCAN nebo nad uzlem SCAN s GROUP BY.
  • Tato agregace není jedinečná agregace.
  • Agregační sloupec není řetězcový sloupec.
  • Agregační sloupec není virtuální sloupec.
  • Vstupní a výstupní datový typ musí být jeden z následujících typů a musí se vejít do 64 bitů:
    • tinyint, int, bigint, smallint, bit
    • smallmoney, money, decimal a numeric s přesností <= 18
    • smalldate, date, datetime, datetime2, time

Například optimalizace pomocí agregace se provádí v obou následujících dotazech.

SELECT  productkey, SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI
GROUP BY productkey;
    
SELECT  SUM(TotalProductCost)
FROM FactResellerSalesXL_CCI;

Propagace predikátu na úrovni řetězce

Při návrhu schématu datového skladu je doporučeným modelováním schématu použití hvězdicového schématu nebo sněhového schématu skládajícího se z jedné nebo více tabulek faktů a mnoha tabulek dimenzí.

Tip

Tabulka faktů ukládá obchodní měření nebo transakce a tabulky dimenzí ukládají dimenze, ve kterých je potřeba analyzovat fakta. Další informace o dimenzionálním modelování najdete v tématu Dimenzionální modelování v Microsoft Fabric.

Skutečnost může být například záznam představující prodej konkrétního produktu v konkrétní oblasti, zatímco dimenze představuje sadu oblastí, produktů atd. Faktové a dimenzionální tabulky jsou propojeny prostřednictvím relace mezi primárním a cizím klíčem. Nejčastěji používané analytické dotazy spojují jednu nebo více tabulek dimenzí s tabulkou faktů.

Představme si tabulku Productsdimenzí . Typický primární klíč je ProductCodeběžně reprezentovaný jako řetězec. Pro zajištění výkonu dotazů je osvědčeným postupem vytvořit náhradní klíč, obvykle celočíselnou hodnotu, který odkazuje na řádek tabulky dimenzí z tabulky faktů.

Index columnstore spouští analytické dotazy s spojeními a predikáty zahrnující číselné nebo celočíselné klíče efektivně. SQL Server 2016 (13.x) výrazně zlepšil výkon analytických dotazů se sloupci založenými na řetězcích tím, že do uzlu SCAN nasdílel predikáty s řetězcovými sloupci.

Nabízení predikátu řetězců využívá primární/sekundární slovník vytvořený pro sloupce ke zlepšení výkonu dotazů. Představte si například segment řetězcového sloupce v rámci skupiny řádků, skládající se ze 100 řetězcových jedinečných hodnot. Každá jedinečná řetězcová hodnota je v průměru odkazována 10 000krát za předpokladu, že existuje jeden milion řádků. Při nabízení predikátu řetězců vypočítá provádění dotazu predikát proti hodnotám ve slovníku. Pokud se predikát kvalifikuje, všechny řádky odkazující na hodnotu slovníku jsou automaticky kvalifikované. Tím se výkon zlepší dvěma způsoby:

  • Vrátí se pouze kvalifikovaný řádek, který snižuje počet řádků, které potřebují tok z uzlu skenování.
  • Počet porovnání řetězců je snížen. V tomto příkladu se vyžaduje pouze 100 porovnání řetězců oproti 1 milionům porovnání. Existují určitá omezení:
    • Pro rozdílové skupiny řádků není žádný predikát řetězce. Pro sloupce ve skupinách rozdílových řádků neexistuje žádný slovník.
    • Žádné přesunutí řetězcových predikátů, pokud slovník přesáhne 64 kB záznamů.
    • Není podporováno vyhodnocování výrazů s nulovými hodnotami.

Odstranění segmentů

Volby datových typů můžou mít významný vliv na výkon dotazů založených na běžných predikátech filtru pro dotazy na index columnstore.

V datech columnstore se skupiny řádků skládají ze segmentů sloupců. U každého segmentu existují metadata, která umožňují rychlé odstranění segmentů bez jejich čtení. Odstranění tohoto segmentu platí pro číselné datové typy, datum a čas a datový typ datetimeoffset s měřítkem menší nebo rovno dvěma. Počínaje SQL Serverem 2022 (16.x) se možnosti odstranění segmentů rozšiřují na řetězec, binární datové typy, datové typy GUID a datový typ datetimeoffset pro škálování větší než dvě.

Po upgradu na verzi SQL Serveru, která podporuje vyloučení minimálních/maximálních segmentů řetězce (SQL Server 2022 (16.x) a novější), index columnstore tuto funkci nevyužívá, dokud není znovu vytvořen pomocí ALTER INDEX REBUILD nebo CREATE INDEX WITH (DROP_EXISTING = ON).

Odstranění segmentů se nevztahuje na datové typy LOB, jako jsou (maximální) délky datových typů.

V současné době pouze SQL Server 2022 (16.x) a novější podporuje odstranění skupin řádků clusterovaného columnstore pro předponu LIKE predikátů, například column LIKE 'string%'. Odstranění segmentů není podporováno pro ne-prefixové použití LIKE, například column LIKE '%string'.

Seřazené indexy columnstore také využívají eliminaci segmentů, zvláště u řetězcových sloupců. V seřazených indexech columnstore je odstranění segmentů u prvního sloupce v klíči indexu nejúčinnější, protože je tento sloupec seřazený. Zvýšení výkonu způsobené odstraněním segmentů u jiných sloupců v tabulce je méně předvídatelné. Další informace o uspořádaných indexech columnstore najdete v tématu Použití uspořádaného indexu columnstore pro velké tabulky datového skladu. Pro dostupnost seřazeného columnstore indexu, viz Dostupnost seřazeného indexu sloupcového úložiště.

Pomocí možnosti připojení dotazu NASTAVIT STATISTIKU IO můžete vidět, jak dochází k eliminaci segmentů. Vyhledejte výstup, například následující, který označuje, že došlo k odstranění segmentu. Řádkové skupiny jsou složeny ze segmentů sloupců, což může naznačovat eliminaci segmentů. Následující SET STATISTICS IO příklad výstupu dotazu, zhruba 83% data se přeskočila dotazem:

...
Table 'FactResellerSalesPartCategoryFull'. Segment reads 16, segment skipped 83.
...

Související obsah

  • Last updated on