Indexy columnstore – pokyny k návrhu

platí pro:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)databáze SQL v Microsoft Fabric

Obecná doporučení pro návrh sloupcových indexů. Několik dobrých rozhodnutí o návrhu vám pomůže dosáhnout vysoké komprese dat a výkonu dotazů, které indexy columnstore poskytují.

Požadavky

Tento článek předpokládá, že znáte architekturu a odbornou terminologii sloupcového úložiště. Další informace najdete v tématu Přehled indexu Columnstore a Architektura indexu Columnstore.

Znalost požadavků na data

Než začnete navrhovat index columnstore, se co nejvíce seznamte s vašimi požadavky na data. Promyslete si například odpovědi na tyto otázky:

• Jak velký je můj stůl?
• Provádějí moje dotazy většinou analýzy, které prohledávají velké rozsahy hodnot? Indexy columnstore jsou navržené tak, aby fungovaly dobře pro kontroly velkých rozsahů, a ne vyhledávání konkrétních hodnot.
• Provádí moje pracovní zátěž spoustu aktualizací a mazání? Indexy columnstore fungují dobře, když jsou data stabilní. Dotazy by měly aktualizovat a mazat méně než 10% řádků.
• Mám tabulky faktů a dimenzí pro datový sklad?
• Musím provádět analýzy transakční úlohy? Pokud ano, přečtěte si pokyny k návrhu úložiště sloupců pro analýzy provozu v reálném čase.

Možná nebudete potřebovat sloupcový index. Tabulky rowstore (nebo B-tree) s haldami nebo clusterovanými indexy fungují nejlépe u dotazů, které hledají data, vyhledávání konkrétní hodnoty nebo dotazů na malý rozsah hodnot. Používejte indexy rowstore s transakčními úlohami, protože obvykle vyžadují převážně hledání tabulek místo prohledávání tabulek s velkým rozsahem.

Výběr nejlepšího indexu columnstore pro vaše potřeby

Index columnstore je buď clusterovaný, nebo neclusterovaný. Clusterovaný columnstore index může mít jeden nebo více neklastrovaných indexů B-stromu. Indexy columnstore se dají snadno vyzkoušet. Pokud vytvoříte tabulku jako index columnstore, můžete tabulku snadno převést zpět na tabulku rowstore odstraněním indexu columnstore.

Tady je souhrn možností a doporučení.

Možnost úložiště sloupců	Doporučení pro použití	Komprese
clusterovaného indexu columnstore	Používá se pro: 1) Tradiční úlohy datového skladu s hvězdicovým nebo sněhovým schématem 2) Úlohy Internetu věcí (IOT), které vkládají velké objemy dat s minimálními aktualizacemi a odstraněním.	Průměr 10x
uspořádaného clusterovaného sloupcového indexu	Používá se při dotazování clusterovaného indexu columnstore prostřednictvím jednoho seřazeného predikátu sloupce nebo sady sloupců. Tyto pokyny jsou podobné výběru klíčových sloupců pro clusterovaný index rowstore, i když se komprimované podkladové skupiny řádků chovají jinak. Další informace najdete v tématu CREATE COLUMNSTORE INDEX a Ladění výkonu s clusterovanými columnstore indexy.	Průměr 10x
neklastrované B-tree indexy v klastrovaném columnstore indexu	Použití pro: 1. Vynucujte omezení primárního klíče a cizího klíče u clusterovaného indexu columnstore. 2. Urychlíte dotazy, které hledají konkrétní hodnoty nebo malé rozsahy hodnot. 3. Urychlíte aktualizace a odstraníte konkrétní řádky.	10x průměrně plus další úložiště pro ncI.
Neklastrovaný columnstore index na hromadu založenou na disku nebo B-tree index	Používá se pro: 1) Úloha OLTP, která obsahuje některé analytické dotazy. Můžete odstranit B-stromové indexy vytvořené pro analýzu a nahradit je jedním neklastrovaným columnstore indexem. 2) Mnoho tradičních úloh OLTP, které provádějí operace extrakce transformace a načítání (ETL) pro přesun dat do samostatného datového skladu. ETL a samostatný datový sklad můžete eliminovat vytvořením neklastrovaného columnstore indexu na některých tabulkách OLTP.	NCCI je další index, který v průměru vyžaduje 10% více úložiště.
Columnstore index v tabulce v paměti	Stejná doporučení jako pro neklastrovaný sloupcový index v tabulce založené na disku, avšak základní tabulka je tabulka v paměti.	Index Columnstore je další index.

Použití clusterovaného indexu columnstore pro velké tabulky datového skladu

Clusterovaný index columnstore není jen index, ale slouží také jako primární úložiště tabulky. Dosahuje vysoké míry komprese dat a výrazného zlepšení výkonu dotazů na velkých datových skladových tabulkách faktů a dimenzí. Clusterované indexy columnstore jsou nejvhodnější pro analytické dotazy místo transakčních dotazů, protože analytické dotazy obvykle provádějí operace s velkými rozsahy hodnot, a ne vyhledáváním konkrétních hodnot.

Zvažte použití clusterovaného indexu columnstore v následujících případech:

• Každý oddíl má alespoň milion řádků. Indexy columnstore mají v rámci každého oddílu skupiny řádků. Pokud je tabulka příliš malá na to, aby zaplnila rowgroup v rámci každého oddílu, nebudete mít výhody komprese columnstore a lepšího výkonu dotazů.
• Dotazy primárně provádějí analýzu rozsahů hodnot. Pokud například chcete najít průměrnou hodnotu sloupce, musí dotaz zkontrolovat všechny hodnoty sloupce. Potom agreguje hodnoty tak, že je sečtou, aby se určily průměry.
• Většina vložených dat se týká velkých objemů dat s minimálními aktualizacemi a mazáním. Mnoho úloh, jako je Internet věcí (IOT), vkládá velké objemy dat s minimálními aktualizacemi a odstraňováním. Tyto úlohy mohou těžit z komprese a zvýšení výkonu dotazů, které jsou výsledkem používání clusterovaného indexu columnstore.

Nepoužívejte clusterovaný index columnstore, pokud:

• Tabulka vyžaduje datové typy varchar(max) , nvarchar(max) , nebo varbinary(max) . Nebo navrhni index columnstore tak, aby neobsadí tyto sloupce (platí pro: SQL Server 2016 (13.x) a předchozí verze).
• Data tabulky nejsou trvalá. Pokud potřebujete data rychle uložit a odstranit, zvažte použití haldy nebo dočasné tabulky.
• Tabulka obsahuje méně než jeden milion řádků na oddíl.
• Více než 10% operací v tabulce jsou aktualizace a odstranění. Velký počet aktualizací a odstranění způsobují fragmentaci. Fragmentace ovlivňuje míru komprese a výkon dotazů, dokud nespustíte operaci s názvem reorganizace, která vynutí všechna data do columnstore a odstraní fragmentaci. Další informace najdete v tématu Minimalizace fragmentace indexu v indexu columnstore.

Další informace najdete v tématu Columnstore indexy v datových skladech.

Použijte seřazený sloupcový clusterovaný index pro velké tabulky datového skladu

Informace o dostupnosti uspořádaného indexu columnstore najdete v části Indexy Columnstore: Přehled.

Zvažte použití uspořádaného clusterovaného indexu columnstore v následujících scénářích:

• Pokud jsou data relativně statická (bez častých zápisů a odstranění) a klíč uspořádaného clusterového indexu columnstore je statický, uspořádané clusterové indexy columnstore mohou poskytovat významné výhody výkonu oproti neuspořádaným clusterovým indexům columnstore nebo clusterovým indexům rowstore pro analytické úlohy.
• Čím více různorodých hodnot v prvním sloupci uspořádaného clusterovaného indexového klíče columnstore, tím větší může být zlepšení výkonu pro uspořádané clusterované indexy columnstore. Důvodem je vylepšené odstranění segmentů pro řetězcová data. Další informace viz eliminace segmentu.
• Zvolte uspořádaný clusterovaný indexový klíč columnstore, který se bude často dotazovat a může těžit z odstranění segmentů, zejména prvního sloupce klíče. Zvýšení výkonu způsobené odstraněním segmentů u jiných sloupců v tabulce bude méně předvídatelné.
• Případy použití, kdy se musí dotazovat pouze nejnovější analytická data, například za posledních 15 sekund, seřazené clusterované indexy columnstore můžou poskytovat odstranění segmentů pro starší data. První sloupec v klíči seřazeného clusterovaného úložiště sloupců musí obsahovat data a času, například vložené nebo vytvořené datum a čas. Odstranění segmentu by bylo efektivnější ve sloupcovém indexu s uspořádáním shluků než v neuspořádaném sloupcovém indexu s uspořádáním shluků.
• Zvažte uspořádané clusterované indexy columnstore u tabulek obsahujících klíče s daty GUID, kde se teď dá datový typ uniqueidentifier použít pro odstranění segmentů segmentů.

Setříděný clusterovaný index typu columnstore nemusí být v těchto scénářích tak efektivní:

• Podobně jako u jiných indexů columnstore, vysoká frekvence vkládání by mohla způsobit nadměrnou zátěž na vstupně-výstupní operace úložiště.
• U úloh s velkým množstvím operací zápisu se kvalita odstranění segmentů v průběhu času sníží kvůli údržbě skupiny řádků pomocí tuple moveru. To lze zmírnit pravidelnou údržbou indexu columnstore pomocí ALTER INDEX REORGANIZE.

Přidejte neklastrované indexy B-tree pro efektivní prohledávání tabulek

Počínaje SQL Serverem 2016 (13.x) můžete vytvořit neclusterované indexy B-tree nebo rowstore jako sekundární indexy v clusterovém indexu columnstore. Neclusterovaný index stromu B se aktualizuje, jakmile dojde ke změnám indexu columnstore. Jedná se o výkonnou funkci, kterou můžete využít ve své výhodě.

Pomocí sekundárního indexu B-tree můžete efektivně vyhledávat konkrétní řádky bez prohledávání všech řádků. K dispozici jsou i další možnosti. Můžete například vynutit omezení primárního nebo cizího klíče pomocí omezení UNIQUE v indexu B-tree. Vzhledem k tomu, že hodnota, která není jedinečná, se nepodaří vložit do indexu stromu B, sql Server nemůže vložit hodnotu do columnstore.

Zvažte použití indexu B-tree u indexu columnstore k:

• Spusťte dotazy, které vyhledávají konkrétní hodnoty nebo malé rozsahy hodnot.
• Vynucujte omezení, jako je například omezení primárního klíče nebo cizího klíče.
• Efektivně provádět operace aktualizace a odstraňování. Index B-tree dokáže rychle vyhledat konkrétní řádky pro aktualizace a odstranit bez prohledávání celé tabulky nebo oddílu tabulky.
• Máte k dispozici další úložiště pro uložení indexu B-tree.

Použití neclusterovaného indexu columnstore pro analýzy v reálném čase

Počínaje SQL Serverem 2016 (13.x) můžete mít neklastrovaný index columnstore na tabulce rowstore založené na disku nebo na tabulce OLTP v paměti. To umožňuje spouštět analýzy v reálném čase v transakční tabulce. Zatímco transakce probíhají v podkladové tabulce, můžete spustit analýzy indexu columnstore. Vzhledem k tomu, že jedna tabulka spravuje oba indexy, jsou změny dostupné v reálném čase jak pro úložiště řádků, tak pro indexy columnstore.

Vzhledem k tomu, že index columnstore dosahuje 10x lepší komprese dat než index rowstore, potřebuje pouze malé množství dodatečného úložiště. Pokud například komprimovaná tabulka rowstore trvá 20 GB, index columnstore může vyžadovat další 2 GB. Další potřebné místo také závisí na počtu sloupců v neklastrovaném columnstore indexu.

Zvažte použití neklastrovaného indexu columnstore k:

• Spouštění analýz v reálném čase v transakční tabulce rowstore Existující indexy stromu B, které jsou navržené pro analýzu, můžete nahradit neclusterovaným indexem columnstore.

• Eliminujte potřebu samostatného datového skladu. Tradičně společnosti provozují transakce v tabulce rowstore a pak načítají data do samostatného datového skladu k provádění analytiky. U mnoha úloh můžete eliminovat proces načítání a samostatný datový sklad vytvořením neclusterovaného indexu columnstore v transakčních tabulkách.

SQL Server 2016 (13.x) nabízí několik strategií, díky kterým bude tento scénář výkonnější. Je to velmi snadné, protože můžete povolit neclusterovaný index columnstore beze změn v aplikaci OLTP.

Pokud chcete přidat další prostředky zpracování, můžete analýzu spustit na čitelné sekundární. Použití čitelného sekundárního oddělení odděluje zpracování transakční úlohy a analytické úlohy.

Další informace najdete v tématu Začínáme se službou Columnstore pro provozní analýzy v reálném čase

Další informace o výběru nejlepšího indexu columnstore najdete na blogu Sunil Agarwal Který index columnstore je pro mou úlohu vhodný?.

Použití oddílů tabulky pro správu dat a výkon dotazů

Indexy Columnstore podporují dělení, což je dobrý způsob správy a archivace dat. Dělení také zlepšuje výkon dotazů omezením operací na jednu nebo více partice.

K usnadnění správy dat použijte rozdělení na oddíly.

U velkých tabulek je jediným praktickým způsobem správy rozsahů dat použití oddílů. Výhody partií pro tabulky rowstore platí také pro indexy columnstore.

Například tabulky rowstore i columnstore používají partice k:

• Řídí velikost přírůstkových záloh. Oddíly můžete zálohovat do samostatných skupin souborů a pak je označit jako jen pro čtení. Budoucí zálohy tímto krokem přeskočí souborové skupiny nastavené pouze pro čtení.
• Ušetřete náklady na úložiště přesunutím staršího oddílu do levnějšího úložiště. Například můžete použít přepínání oddílů k přesunutí oddílu do levnějšího umístění úložiště.
• Provádějte operace efektivně omezením jejich rozsahu na oddíl. Můžete například cílit pouze na fragmentované oddíly pro údržbu indexu.

Kromě toho s indexem columnstore použijete dělení na:

• Ušetřete dalších 30% v nákladech na úložiště. Starší oddíly můžete komprimovat pomocí možností komprese COLUMNSTORE_ARCHIVE. Výkon dotazování bude kvůli datům pomalejší, což je přijatelné, pokud se oddíl dotazuje jen zřídka.

Použijte oddíly ke zlepšení výkonu dotazů

Pomocí oddílů můžete dotazy omezit tak, aby kontrolují jenom konkrétní oddíly, což omezuje počet řádků, které chcete zkontrolovat. Pokud je například index rozdělený podle roku a dotaz analyzuje data z minulého roku, stačí zkontrolovat pouze data v jednom oddílu.

Použijte méně oddílů pro index columnstore

Pokud nemáte dostatečnou velikost dat, index columnstore funguje nejlépe s menším počtem oddílů, než jaký byste mohli použít pro index úložiště řádků. Pokud nemáte alespoň jeden milion záznamů na oddíl, většina záznamů může být uložena do deltastore, kde tím nedosáhnou výkonového přínosu z komprese columnstore. Pokud například načtete jeden milion řádků do tabulky s 10 oddíly a každý oddíl obdrží 100 000 řádků, všechny řádky se přesunou do rozdílových skupin řádků.

Příklad:

• Načtěte 1 000 000 řádků do jedné partice nebo nerozdělené tabulky. Získáte jednu komprimovanou skupinu řádků s 1 000 000 řádky. To je skvělé pro vysokou kompresi dat a rychlý výkon dotazů.
• Načtěte rovnoměrně 1 000 000 řádků do 10 částí. Každý oddíl získá 100 000 řádků, což je nižší než minimální prahová hodnota pro kompresi sloupcových úložišť. V důsledku toho může index columnstore obsahovat 10 deltových skupin řádků, přičemž každá z nich má 100 000 řádků. Existují způsoby, jak vynutit delta rowgroups do columnstore. Pokud se ale jedná o jediné řádky v indexu columnstore, komprimované skupiny řádků budou pro nejlepší kompresi a výkon dotazů příliš malé.

Další informace o dělení najdete v blogovém příspěvku Sunila Agarwala Mám rozdělit sloupcový index?.

Zvolte příslušnou metodu komprese dat.

Index sloupcového úložiště nabízí dvě možnosti komprese dat: kompresi sloupcového úložiště a archivní kompresi. Možnost komprese můžete zvolit při vytváření indexu nebo ji později změnit pomocí ALTER INDEX ... REBUILD.

Použití komprese columnstore k zajištění nejlepšího výkonu dotazů

Komprese columnstore obvykle dosahuje 10x lepší míry komprese oproti indexům rowstore. Jedná se o standardní metodu komprese pro indexy columnstore a umožňuje rychlý výkon dotazů.

Použití komprese archivu pro nejlepší kompresi dat

Komprese archivu je navržená pro maximální kompresi, pokud výkon dotazů není tak důležitý. Dosahuje vyšších úrovní komprese dat než komprese sloupcového úložiště, ale je to za cenu. Komprese a dekomprese dat trvá déle, takže se pro rychlý výkon dotazů nehodí.

Optimalizace při převodu tabulky rowstore na index columnstore

Pokud jsou data již v tabulce rowstore, můžete k převodu tabulky na clusterovaný index columnstore použít CREATE COLUMNSTORE INDEX. Existuje několik optimalizací, které po převodu tabulky zlepší výkon dotazů, jak je popsáno dále.

Zvýšení kvality skupiny řádků pomocí MAXDOP

Můžete nakonfigurovat maximální počet procesorů pro převod haldy nebo clusterovaného indexu B-tree na index columnstore. Ke konfiguraci procesorů použijte maximální stupeň paralelismu (MAXDOP).

Pokud máte velké objemy dat, MAXDOP 1 bude pravděpodobně příliš pomalé. Zvýšení maxdop na 4 funguje správně. Pokud výsledkem bude několik skupin řádků, které nemají optimální počet řádků, můžete spustit ALTER INDEX REORGANIZE je sloučit na pozadí.

Uchovávejte seřazené pořadí indexu B-tree

Vzhledem k tomu, že index stromu B již ukládá řádky v seřazeném pořadí, zachování této objednávky při komprimaci řádků do indexu columnstore může zlepšit výkon dotazů.

Index columnstore data neřadí, ale používá metadata ke sledování minimálních a maximálních hodnot jednotlivých segmentů sloupců v každé skupině řádků. Při vyhledávání rozsahu hodnot se může rychle vypočítat, kdy se má přeskakovat skupina řádků. Když jsou data seřazená, můžete přeskočit další skupiny řádků.

Zachování seřazeného pořadí během převodu:

• Použijte CREATE COLUMNSTORE INDEX s klauzulí DROP_EXISTING. Tím se zachová také název indexu. Pokud máte skripty, které už používají název indexu rowstore, nebudete je muset aktualizovat.

  Tento příklad převede clusterovaný index rowstore v tabulce s názvem MyFactTable na clusterovaný index columnstore. Název indexu, ClusteredIndex_d473567f7ea04d7aafcac5364c241e09, zůstane stejný.

  CREATE CLUSTERED COLUMNSTORE INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09
  ON MyFactTable
  WITH (DROP_EXISTING = ON);
  

Vysvětlení odstranění segmentů

Každá skupina řádků obsahuje jeden segment sloupce pro každý sloupec v tabulce. Každý segment sloupce se komprimuje a ukládá na fyzické médium.

U každého segmentu existují metadata, která umožňují rychlé odstranění segmentů bez jejich čtení. 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. Další informace naleznete v dokumentaci odstranění segmentu.

Související úkoly

Jedná se o úlohy vytváření a údržby indexů columnstore.

Úkol	Referenční články	Poznámky
Vytvořte tabulku jako sloupcový úložiště.	CREATE TABLE (Transact-SQL)	Počínaje SQL Serverem 2016 (13.x) můžete tabulku vytvořit jako clusterovaný index columnstore. Nemusíte nejprve vytvořit tabulku rowstore a pak ji převést na columnstore.
Vytvořte tabulku v paměti s indexem columnstore.	VYTVOŘIT TABULKU (Transact-SQL)	Počínaje SQL Serverem 2016 (13.x) můžete vytvořit tabulku optimalizovanou pro paměť s indexem columnstore. Index columnstore lze také přidat po vytvoření tabulky pomocí syntaxe ALTER TABLE ADD INDEX.
Převeďte tabulku rowstore na columnstore.	CREATE COLUMNSTORE INDEX (Transact-SQL)	Převeďte existující haldu nebo B-tree na columnstore. Příklady ukazují, jak zpracovat existující indexy a také název indexu při provádění tohoto převodu.
Převeďte tabulku sloupcového úložiště na řádkové úložiště.	CREATE CLUSTERED INDEX (Transact-SQL) nebo Převod tabulky columnstore zpět na řádkovou haldu	Tento převod obvykle není nutný, ale někdy může docházet k převodu. Příklady ukazují, jak převést zásobník sloupců na hromadu nebo clusterovaný index.
Vytvořte index columnstore v tabulce rowstore.	VYTVOŘIT COLUMNSTORE INDEX (Transact-SQL)	Tabulka rowstore může mít jeden index columnstore. Počínaje SQL Serverem 2016 (13.x) může mít index columnstore filtrovanou podmínku. Příklady ukazují základní syntaxi.
Vytváření výkonných indexů pro provozní analýzy	Začínáme se columnstorem pro provozní analýzy v reálném čase	Popisuje, jak vytvořit doplňkové indexy columnstore a B-tree tak, aby dotazy OLTP používaly indexy B-tree a analytické dotazy používají indexy columnstore.
Vytvořte výkonné sloupcové indexy pro datové sklady.	Indexy sloupcového úložiště v datových skladech	Popisuje, jak pomocí indexů B-tree v tabulkách columnstore vytvářet výkonné dotazy na datové sklady.
Pomocí indexu stromu B vynucujte omezení primárního klíče u indexu columnstore.	Sloupcové indexy v datových skladech	Ukazuje, jak kombinace indexů B-tree a columnstore může vynutit omezení primárního klíče na indexu columnstore.
Smazání indexu columnstore	DROP INDEX (Transact-SQL)	Vyřazení indexu columnstore používá standardní syntaxi DROP INDEX, kterou používají indexy stromu B. Odstranění clusterovaného indexu columnstore převede tabulku columnstore na haldu.
Odstranění řádku z indexu columnstore	DELETE (Transact-SQL)	K odstranění řádku použijte DELETE (Transact-SQL). columnstore řádek: SQL Server označí řádek jako logicky odstraněný, ale neuvolní fyzické úložiště řádku, dokud index znovu nevystaví. deltastore řádek: SQL Server logicky a fyzicky odstraní řádek.
Aktualizace řádku v indexu columnstore	AKTUALIZACE (Transact-SQL)	K aktualizaci řádku použijte UPDATE (Transact-SQL). columnstore řádek: SQL Server označí řádek jako logicky odstraněný a potom vloží aktualizovaný řádek do deltastore. deltastore řádek: SQL Server aktualizuje řádek v deltastore.
Vynutit, aby všechny řádky v deltastore byly přesunuty do columnstore.	ALTER INDEX (Transact-SQL) ... PŘESTAVĚT Optimalizace údržby indexů za účelem zlepšení výkonu dotazů a snížení spotřeby prostředků	ALTER INDEX s volbou REBUILD vynutí, aby všechny řádky byly uloženy v columnstore.
Defragmentace indexu columnstore	ALTER INDEX (Transact-SQL)	Příkaz ALTER INDEX ... REORGANIZE defragmentuje sloupcové indexy (columnstore indexes) online.
Sloučte tabulky s columnstore indexy.	MERGE (Transact-SQL)

Související obsah

Vytvoření prázdného indexu columnstore pro:

• SQL Server nebo SQL Database, viz CREATE TABLE (Transact-SQL).
• Azure Synapse Analytics najdete v tématu CREATE TABLE (Azure Synapse Analytics).

Další informace o tom, jak převést existující řádkovou haldu nebo B-stromový index na clusterovaný columnstore index nebo vytvořit neclusterovaný columnstore index, najdete v tématu CREATE COLUMNSTORE INDEX (Transact-SQL).

• Novinky v indexech columnstore
• sloupcové indexy v datových skladech
• indexy columnstore – výkon dotazů