Příručka k architektuře a návrhu indexů SQL Serveru a Azure SQL

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

Špatně navržené indexy a nedostatek indexů jsou primárními zdroji kritických bodů databázové aplikace. Návrh efektivních indexů je pro dosažení dobrého výkonu databáze a aplikace nejdůležitější. Tento průvodce návrhem indexu obsahuje informace o architektuře indexů a osvědčené postupy, které vám pomůžou navrhnout efektivní indexy tak, aby vyhovovaly potřebám vaší aplikace.

V této příručce se předpokládá, že čtenář má obecný přehled o dostupných typech indexů. Obecný popis typů indexů najdete v tématu Indexy.

Tato příručka popisuje následující typy indexů:

Formát primárního úložiště	Typ indexu
Úložiště řádků založené na disku
	Seskupený
	Nepřidružený
	Jedinečný
	Filtrovaný
Columnstore
	Shlukové sloupcové úložiště
	Neklastrovaný columnstore
Optimalizováno pro paměť
	Haš
	Memory-Optimized neklastrované

Informace o indexech XML naleznete v tématu INDEXY XML (SQL Server) a selektivní indexy XML (SXI).

Informace o prostorových indexech naleznete v tématu Přehled prostorových indexů.

Pro informace o fulltextových indexech viz Naplnění indexů Full-Text.

Základy návrhu indexu

Zamyslete se nad běžnou knihou: na konci knihy je rejstřík, který pomáhá rychle najít informace v knize. Index je seřazený seznam klíčových slov a vedle každého klíčového slova je sada čísel stránek odkazující na stránky, kde lze najít jednotlivá klíčová slova.

Index rowstore se nijak neliší: jedná se o uspořádaný seznam hodnot a pro každou hodnotu jsou ukazatele na datové stránky , kde se tyto hodnoty nacházejí. Samotný index se ukládá na stránkách, které se označují jako indexové stránky. Pokud index v běžné knize zahrnuje více stránek a musíte najít ukazatele na všechny stránky, které obsahují slovo SQL , budete muset listovat, dokud nenajdete indexovou stránku, která obsahuje klíčové slovo SQL. Odtud budete postupovat podle ukazatelů na všechny stránky knihy. To by mohlo být optimalizováno dále, pokud na začátku indexu vytvoříte jednu stránku, která obsahuje abecední seznam, kde lze najít každé písmeno. Příklad: "A až D - strana 121", "E až G - strana 122" atd. Tato extra stránka by eliminovala krok procházení indexu, aby se zjistilo počáteční místo. Taková stránka v pravidelných knihách neexistuje, ale existuje v indexu rowstore. Tato jedna stránka se označuje jako kořenová stránka indexu. Kořenová stránka je počáteční stránkou struktury stromu, kterou používá index. Po analogii stromu se koncové stránky, které obsahují ukazatele na skutečná data, označují jako "listové stránky" stromu.

Index je struktura na disku nebo v paměti přidružená k tabulce nebo zobrazení, která urychluje načítání řádků z tabulky nebo zobrazení. Index rowstore obsahuje klíče vytvořené z jednoho nebo více sloupců v tabulce nebo zobrazení. U indexů rowstore jsou tyto klíče uložené ve stromové struktuře (strom B+), která databázovému stroji umožňuje rychle a efektivně najít řádek nebo řádky přidružené k klíčovým hodnotám.

Index rowstore ukládá data logicky uspořádaná jako tabulka s řádky a sloupci a fyzicky uložená v datovém formátu, který se nazývá rowstore¹, nebo uložená ve sloupcovém datovém formátu nazývaném columnstore.

Výběr správných indexů pro databázi a její úlohy představuje složitý postup spočívající ve vyrovnávání rychlostí dotazů a nákladů na aktualizaci. Úzké indexy úložiště řádků založené na disku nebo indexy s několika sloupci v klíči indexu vyžadují méně místa na disku a nižší režijní náklady na údržbu. Široké indexy zase pokrývají více dotazů. Před nalezením nejúčinnějšího indexu možná budete muset experimentovat s několika různými návrhy. Indexy je možné přidávat, upravovat a zahodit, aniž by to mělo vliv na návrh schématu databáze nebo aplikace. Proto byste neměli váhat experimentovat s různými indexy.

Optimalizátor dotazů v databázovém stroji spolehlivě zvolí nejúčinnější index ve většině případů. Vaše celková strategie návrhu indexu by měla poskytnout pro optimalizátor dotazů různé indexy, ze kterých si může vybrat a důvěřovat tomu, aby se rozhodl správně. To zkracuje čas analýzy a vede k dobrému výkonu v různých situacích. Pokud chcete zjistit, které indexy optimalizátor dotazů používá pro konkrétní dotaz, vyberte v aplikaci SQL Server Management Studio v nabídce Dotaz možnost Zahrnout skutečný plán provádění.

Nepoužívejte vždy rovnaní využití indexů s dobrým výkonem a dobrým výkonem s efektivním využitím indexu. Pokud by použití indexu vždy pomohlo dosáhnout nejlepšího výkonu, úloha optimalizátoru dotazů by byla jednoduchá. Ve skutečnosti může nesprávná volba indexu způsobit méně než optimální výkon. Proto je úkolem optimalizátoru dotazů vybrat index nebo kombinaci indexů, pouze pokud zlepšuje výkon, a vyhnout se indexovaným načtením, pokud brání výkonu.

^{Úložiště řádků 1} bylo tradičním způsobem ukládání dat relační tabulky. Úložiště řádků odkazuje na tabulku, ve které je podkladovým formátem úložiště dat halda, strom B+ (clusterovaný index) nebo tabulka optimalizovaná pro paměť. Úložiště řádků založené na disku vylučuje tabulky optimalizované pro paměť.

Úlohy návrhu indexu

Následující úlohy tvoří naši doporučenou strategii návrhu indexů:

1. Porozumíte charakteristikám samotné databáze.

   • Jedná se například o databázi online zpracování transakcí (OLTP) s častými úpravami dat, které musí udržovat vysokou propustnost? Tabulky a indexy optimalizované pro paměť jsou vhodné zejména pro tento scénář tím, že poskytují návrh bez západek. Další informace najdete v tématu Indexy v tabulkách Memory-Optimized nebo Memory-Optimized pokyny k návrhu neclusterovaného indexu a pokyny k návrhu hash indexu v této příručce.
   • Nebo je to příklad databáze DSS (Decision Support System) nebo datového skladu (OLAP), která musí rychle zpracovávat velmi velké datové sady? Sloupcové indexy jsou obzvláště vhodné pro typické datové sady v datových skladech. Indexy columnstore můžou uživatelům transformovat prostředí datových skladů tím, že umožňují rychlejší výkon pro běžné dotazy datového skladu, jako jsou filtrování, agregace, seskupování a dotazy s hvězdicovým spojením. Další informace najdete v tématu Indexy columnstore: přehled nebo pokyny k návrhu indexu Columnstore v tomto průvodci.

2. Seznamte se s charakteristikami nejčastěji používaných dotazů. Když například víte, že často používaný dotaz spojuje dvě nebo více tabulek, pomůže vám určit nejlepší typ indexů, které se mají použít.

3. Seznamte se s charakteristikami sloupců použitých v dotazech. Index je například ideální pro sloupce, které mají celočíselné datové typy a jsou také jedinečné nebo nenulové sloupce. Pro sloupce s dobře definovanými podmnožinami dat můžete použít filtrovaný index v SQL Serveru 2008 (10.0.x) a vyšších verzích. Další informace naleznete v tématu Filtrované pokyny k návrhu indexu v této příručce.

4. Určete, které možnosti indexu můžou zvýšit výkon při vytváření nebo údržbě indexu. Vytvoření clusterovaného indexu v existující velké tabulce by například bylo výhodné z možnosti indexu ONLINE . Tato ONLINE možnost umožňuje, aby souběžná aktivita na podkladových datech pokračovala, zatímco se index vytváří nebo znovu sestavuje. Další informace naleznete v tématu Nastavení možností indexu.

5. Určete optimální umístění úložiště pro index.

   Neclusterovaný index může být uložen ve stejné skupině souborů jako podkladová tabulka nebo v jiné skupině souborů. Umístění úložiště indexů může zvýšit výkon dotazů zvýšením výkonu vstupně-výstupních operací disku. Například uložení neclusterovaného indexu do skupiny souborů, která je na jiném disku než skupina souborů tabulky, může zvýšit výkon, protože více disků je možné číst současně. Seskupené a neseskupené indexy mohou také používat partiční schéma napříč více skupinami souborů. Při zvažování particionování určete, zda má být index zarovnaný, tj. particionovaný v podstatě stejným způsobem jako tabulka, nebo particionovaný nezávisle na tabulce. Více se dozvíte v sekci umístění indexu ve skupinách souborů nebo schématech oddílů v této části článku.

6. Když identifikujete chybějící indexy pomocí zobrazení dynamické správy (DMV), jako jsou sys.dm_db_missing_index_details a sys.dm_db_missing_index_columns, můžete mít podobné varianty indexů ve stejné tabulce a sloupcích. Prozkoumejte existující indexy v tabulce spolu s chybějícími návrhy indexů, abyste zabránili vytváření duplicitních indexů. Další informace najdete v optimalizaci neclusterovaných indexů s návrhy na chybějící indexy.

Obecné pokyny pro návrh indexu

Zkušení správci databází můžou navrhnout dobrou sadu indexů, ale tato úloha je složitá, časově náročná a náchylná k chybám i pro středně složité databáze a úlohy. Pochopení charakteristik databáze, dotazů a datových sloupců vám může pomoct při návrhu optimálních indexů.

Důležité informace o databázi

Při návrhu indexu zvažte následující pokyny k databázi:

• Velký počet indexů v tabulce ovlivňuje výkon INSERT, UPDATE, DELETEa MERGE příkazy, protože všechny indexy musí být odpovídajícím způsobem upraveny, protože data v tabulce se mění. Pokud se například sloupec používá v několika indexech a spustíte UPDATE příkaz, který upraví data daného sloupce, musí být každý index obsahující tento sloupec aktualizován a také sloupec v podkladové základní tabulce (haldy nebo clusterovaný index).

  • Vyhněte se příliš indexování silně aktualizovaných tabulek a udržujte indexy úzké, to znamená s co nejnižším množstvím sloupců.

  • Použití mnoha indexů ke zlepšení výkonu dotazů u tabulek s nízkými požadavky na aktualizaci, ale velké objemy dat. Velký počet indexů může pomoct s výkonem dotazů, které neupravují data, jako SELECT jsou příkazy, protože optimalizátor dotazů má více indexů, ze kterého si můžete vybrat, abyste zjistili nejrychlejší metodu přístupu.

• Indexování malých tabulek nemusí být optimální, protože může trvat déle, než optimalizátor dotazů projde index při hledání dat, než aby provedl základní prohledávání tabulky. Proto indexy u malých tabulek nemusí být nikdy využity, ale musí být i nadále udržovány, jak se mění data v tabulce.

• Indexy v zobrazeních můžou poskytovat významné zvýšení výkonu, pokud zobrazení obsahuje agregace, spojení tabulek nebo kombinaci agregací a spojení. Zobrazení nemusí být explicitně odkazováno v dotazu, aby ho optimalizátor dotazů používal.

• Databáze na primárních replikách ve službě Azure SQL Database automaticky generují doporučení výkonu od poradce pro indexy. Volitelně můžete povolit automatické ladění indexu.

• Úložiště dotazů pomáhá identifikovat dotazy s neoptimálním výkonem a poskytuje historii plánů provádění dotazů, které dokumentují indexy vybrané optimalizátorem.

Důležité informace o dotazech

Při návrhu indexu zvažte následující pokyny pro dotazy:

• Vytvořte neclusterované indexy ve sloupcích, které se často používají v predikátech a spojování podmínek v dotazech. Toto jsou vaše SARGable¹ sloupce. Měli byste se ale vyhnout přidávání nepotřebných sloupců. Přidání příliš velkého počtu sloupců indexu může nepříznivě ovlivnit výkon místa na disku a údržby indexů.

• Pokrytí indexů může zlepšit výkon dotazů, protože všechna data potřebná ke splnění požadavků dotazu existují v rámci samotného indexu. To znamená, že k načtení požadovaných dat jsou vyžadovány pouze indexové stránky, nikoli datové stránky tabulky nebo clusterovaného indexu; tím snižuje celkový počet vstupně-výstupních operací disku. Například dotaz na sloupce A a B na tabulku, která má složený index vytvořený ve sloupcích A, Ba C může načíst zadaná data z samotného indexu.

  Zakrývání indexů je označení neclusterovaného indexu , které řeší jeden nebo několik podobných výsledků dotazu přímo bez přístupu k jeho základní tabulce a bez použití vyhledávání.

  Tyto indexy mají na své úrovni listu všechny nezbytné sloupce, které nejsou SARGable. To znamená, že sloupce vrácené klauzulí SELECT i všemi argumenty WHERE a JOIN jsou zahrnuty do indexu.

  Pokud je index dostatečně úzký v porovnání s řádky a sloupci v samotné tabulce, může to znamenat, že se jedná o skutečnou podmnožinu celkových sloupců, což potenciálně výrazně snižuje množství vstupně-výstupních operací při provádění dotazu.

  Zvažte pokrytí indexů při výběru malé části velké tabulky a tam, kde je tato malá část definována pevným predikátem, například řídké sloupce , které obsahují pouze několik hodnot bez hodnoty NULL, například.

• Pište dotazy, které v jednom příkazu vkládají nebo upravují co nejvíce řádků, místo použití více dotazů k aktualizaci stejných řádků. Optimalizace údržby indexů se dá zneužít pouze jedním příkazem.

• Vyhodnoťte typ dotazu a způsob použití sloupců v dotazu. Například sloupec použitý v dotazu na přesnou shodu by byl vhodným kandidátem pro neklastrovaný nebo klastrovaný index.

¹ Termín SARGable v relačních databázích odkazuje na SearchARGumentable predikát, který může použít index k urychlení provádění dotazu.

Úvahy o sloupcích

Při návrhu indexu zvažte následující pokyny pro sloupce:

• Udržujte délku indexového klíče krátkou pro clusterované indexy. Clusterované indexy navíc využívají výhod vytváření v jedinečných nebo nenulových sloupcích.

• Sloupce, které jsou datovými typy ntext, text, image, varchar(max), nvarchar(max) a varbinary(max), nelze zadat jako sloupce klíče indexu. Varchar (max), nvarchar(max), varbinary(max) a datové typy XML se však můžou účastnit neclusterovaného indexu jako sloupce indexu bez klíče. Další informace najdete v části Index se zahrnutými sloupci v této příručce.

• Datový typ XML může být pouze klíčovým sloupcem v indexu XML. Další informace naleznete v tématu Indexy XML (SQL Server). SQL Server 2012 SP1 zavedl nový typ indexu XML známého jako selektivní index XML. Tento nový index může zlepšit výkon dotazování na data uložená jako XML, což umožňuje rychlejší indexování velkých datových úloh XML a zlepšit škálovatelnost snížením nákladů na úložiště samotného indexu. Další informace naleznete v tématu Selektivní indexy XML (SXI).

• Prozkoumejte jedinečnost sloupce. Jedinečný index místo neunique indexu ve stejné kombinaci sloupců poskytuje další informace pro optimalizátor dotazů, díky kterému je index užitečnější. Další informace naleznete v tématu Jedinečné pokyny k návrhu indexu v tomto průvodci.

• Prozkoumejte distribuci dat ve sloupci. Dlouhotrvající dotaz je často způsobený indexováním sloupce s několika jedinečnými hodnotami nebo provedením spojení s takovým sloupcem. Jedná se o základní problém s daty a dotazy a obecně se nedá vyřešit bez identifikace této situace. Například fyzický telefonní adresář seřazený abecedně podle jména rodiny nevyvolá vyhledání osoby, pokud se všichni lidé ve městě jmenují Smith nebo Jones. Další informace o distribuci dat naleznete v tématu Statistika.

• Zvažte použití filtrovaných indexů u sloupců, které mají dobře definované podmnožina, například řídké sloupce, sloupce s většinou NULL hodnotami, sloupce s kategoriemi hodnot a sloupce s odlišnými rozsahy hodnot. Dobře navržený filtrovaný index může zlepšit výkon dotazů, snížit náklady na údržbu indexů a snížit náklady na úložiště.

• Pokud index obsahuje více sloupců, zvažte pořadí sloupců. Nejprve by měl být umístěn sloupec, který se používá v WHERE klauzuli při podmínkách rovnosti (=), větší než (>), menší než (<) nebo BETWEEN nebo se účastní spojení. Další sloupce by se měly řadit na základě jejich úrovně jedinečnosti, tj. od těch nejvýraznějších po nejméně odlišné.

  Pokud je například index definován jako LastName, FirstNameje index užitečný, pokud je WHERE LastName = 'Smith' kritérium hledání nebo WHERE LastName = Smith AND FirstName LIKE 'J%'. Optimalizátor dotazů by ale index pro dotaz, který hledal pouze, FirstName (WHERE FirstName = 'Jane')nepoužil.

• Zvažte indexování počítaných sloupců. Další informace najdete v tématu Indexy ve vypočítaných sloupcích.

Charakteristiky indexu

Jakmile zjistíte, že je index vhodný pro dotaz, můžete vybrat typ indexu, který nejlépe vyhovuje vaší situaci. Mezi charakteristiky indexu patří následující seznam:

• Clusterované versus neclusterované
• Jedinečné versus neunique
• Jeden sloupec versus vícesloupcový graf
• Vzestupné nebo sestupné pořadí sloupců v indexu
• Celá tabulka oproti filtrování u neklastrových indexů
• Úložiště sloupců versus úložiště řádků
• Hash versus neklastrovaná pro tabulky optimalizované pro paměť

Můžete také přizpůsobit počáteční charakteristiky úložiště indexu tak, aby optimalizoval výkon nebo údržbu nastavením možnosti, například FILLFACTOR. Umístění úložiště indexu můžete také určit pomocí skupin souborů nebo schémat oddílů za účelem optimalizace výkonu.

Umístění indexu u skupin souborů nebo schémat oddílů

Při vývoji strategie návrhu indexu byste měli zvážit umístění indexů do skupin souborů přidružených k databázi. Pečlivý výběr schématu skupiny souborů nebo oddílů může zlepšit výkon dotazů.

Ve výchozím nastavení jsou indexy uloženy ve stejné skupině souborů jako základní tabulka, na které je index vytvořen. Nedílný clusterovaný index a základní tabulka se vždy nacházejí ve stejné skupině souborů. Můžete ale provést následující kroky:

• Vytvořte neclusterované indexy v jiné skupině souborů, než je skupina souborů základní tabulky nebo clusterovaného indexu.
• Rozdělte clusterované a neclusterované indexy tak, aby přesahovaly více skupin souborů.
• Přesuňte tabulku z jedné skupiny souborů do druhé přetažením clusterovaného indexu a zadáním nového schématu skupiny souborů nebo oddílu v MOVE TO klauzuli DROP INDEX příkazu nebo pomocí CREATE INDEX příkazu s DROP_EXISTING klauzulí.

Vytvořením neclusterovaného indexu v jiné skupině souborů můžete dosáhnout zvýšení výkonu, pokud skupiny souborů používají různé fyzické jednotky s vlastními řadiči. Informace o datech a indexech je pak možné číst paralelně několika diskovými hlavami. Pokud například Table_A na skupině souborů f1 a Index_A na skupině souborů f2 používá stejný dotaz, lze dosáhnout zlepšení výkonu, protože obě skupiny souborů jsou plně využívány bez střetu. Pokud Table_A je však dotaz prohledáván, ale Index_A není odkazován, použije se pouze skupina f1 souborů. Tím nedojde k žádnému zvýšení výkonu.

Vzhledem k tomu, že nemůžete předpovědět, k jakému typu přístupu dojde a kdy k němu dojde, může být lepším rozhodnutím rozšířit tabulky a indexy napříč všemi skupinami souborů. Tím by se zajistilo, že se ke všem diskům přistupuje, protože všechna data a indexy jsou rovnoměrně rozložené na všechny disky bez ohledu na to, jakým způsobem se k datům přistupuje. Jedná se také o jednodušší přístup pro správce systému.

Oddíly napříč několika skupinami souborů

Můžete také zvážit dělení clusterových a neclusterovaných indexů na disku napříč několika skupinami souborů. Rozdělené indexy jsou horizontálně nebo podle řádků rozdělené na základě funkce oddílu. Funkce oddílu definuje, jak se každý řádek mapuje na sadu oddílů na základě hodnot určitých sloupců, označovaných jako sloupce dělení. Schéma oddílů určuje mapování oddílů na sadu skupin souborů.

Rozdělení indexu může přinést následující výhody:

• Poskytuje škálovatelné systémy, které zpřístupňuje větší možnosti správy velkých indexů. Systémy OLTP mohou například implementovat oddílově vědomé aplikace, které pracují s velkými indexy.

• Zrychlete a zefektivněte spouštění dotazů. Když dotazy přistupují k několika oddílům indexu, může optimalizátor dotazů zpracovávat jednotlivé oddíly současně a vyloučit oddíly, na které dotaz nemá vliv.

Další informace najdete v tématu Dělené tabulky a indexy.

Pokyny pro návrh pořadí řazení indexu

Při definování indexů zvažte, zda mají být data pro sloupec klíče indexu uložena ve vzestupném nebo sestupném pořadí. Výchozí nastavení je vzestupně a zajišťuje kompatibilitu s předchozími verzemi Databázového stroje. Syntaxe CREATE INDEX, CREATE TABLEa ALTER TABLE příkazy podporuje klíčová slova ASC (vzestupně) a DESC (sestupně) u jednotlivých sloupců v indexech a omezeních.

Určení pořadí, ve kterém jsou hodnoty klíčů uloženy v indexu, je užitečné, když dotazy odkazující na tabulku obsahují ORDER BY klauzule, které určují různé směry pro klíčový sloupec nebo sloupce v daném indexu. V těchto případech může index odebrat potřebu operátora SORT v plánu dotazu. Proto je dotaz efektivnější. Například kupující v nákupním oddělení Adventure Works Cycles musí vyhodnotit kvalitu produktů, které nakupují od dodavatelů. Kupující se nejvíce zajímají o hledání produktů odeslaných těmito dodavateli s vysokou sazbou zamítnutí.

Jak je znázorněno v následujícím dotazu na ukázkovou databázi AdventureWorks, načtení dat tak, aby splňovala tato kritéria, vyžaduje RejectedQty řazení sloupce v Purchasing.PurchaseOrderDetail tabulce sestupně (velké až malé) a ProductID sloupec, který se má seřadit vzestupně (malé až velké).

SELECT RejectedQty,
       ((RejectedQty / OrderQty) * 100) AS RejectionRate,
       ProductID,
       DueDate
FROM Purchasing.PurchaseOrderDetail
ORDER BY RejectedQty DESC, ProductID ASC;
GO

Následující plán provádění pro tento dotaz ukazuje, že optimalizátor dotazu použil SORT operátor k vrácení sady výsledků v pořadí určeném ORDER BY klauzulí.

Pokud se vytvoří index rowstore založený na disku s klíčovými sloupci, které odpovídají sloupcům v ORDER BY klauzuli dotazu, může být operátor SORT odstraněn v plánu dotazu a plán dotazu je efektivnější.

CREATE NONCLUSTERED INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail
    (RejectedQty DESC, ProductID ASC, DueDate, OrderQty);
GO

Po opětovném spuštění dotazu následující plán spuštění ukazuje, že SORT operátor byl odstraněn a nově vytvořený neclusterovaný index se použije.

Databázový stroj se může rovnoměrně pohybovat v obou směrech. Index definovaný jako (RejectedQty DESC, ProductID ASC) lze stále použít pro dotaz, ve kterém je směr řazení sloupců v ORDER BY klauzuli obrácený. Například dotaz s ORDER BY klauzulí ORDER BY RejectedQty ASC, ProductID DESC může použít index.

Pořadí řazení lze zadat pouze pro klíčové sloupce v indexu. Katalogové zobrazení sys.index_columns a funkce INDEXKEY_PROPERTY uvádí, zda je indexový sloupec uložen ve vzestupném nebo sestupném pořadí.

Pokud pracujete s příklady kódu v ukázkové databázi AdventureWorks, můžete ji odstranit následujícím příkazem Transact-SQL:

DROP INDEX IX_PurchaseOrderDetail_RejectedQty
ON Purchasing.PurchaseOrderDetail;
GO

Metadatové informace

Pomocí těchto zobrazení metadat můžete zobrazit atributy indexů. V některých z těchto zobrazení jsou vloženy další informace o architektuře.

U indexů columnstore jsou všechny sloupce uloženy v metadatech jako zahrnuté sloupce. Index columnstore nemá klíčové sloupce.

• sys.sloupcové_úložiště_slovníky
• sys.column_store_row_groups
• sys.column_store_segments
• sys.dm_column_store_object_pool
• sys.dm_db_column_store_row_group_operational_stats (operační statistiky skupin řádků úložiště sloupců)
• sys.dm_db_column_store_row_group_physical_stats
• sys.dm_db_index_operational_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_xtp_hash_index_stats
• sys.dm_db_xtp_index_stats
• sys.dm_db_xtp_nonclustered_index_stats
• sys.dm_db_xtp_object_stats
• sys.dm_db_xtp_table_memory_stats
• sys.hash_indexes
• sys.index_columns
• sys.indexes
• sys.internal_partitions
• sys.memory_optimalizované_tabulky_interní_atributy
• sys.partitions

Pokyny pro návrh clusterovaného indexu

Clusterované indexy seřadí a uloží řádky dat v tabulce na základě jejich klíčových hodnot. Pro každou tabulku může existovat pouze jeden clusterovaný index, protože řádky dat samotné lze řadit pouze v jednom pořadí. S několika výjimkami by každá tabulka měla mít skupinový index definovaný ve sloupci nebo sloupcích, který nabízí následující:

• Lze použít pro často používané dotazy.

• Poskytuje vysoký stupeň jedinečnosti.

  Poznámka:

  Při vytváření PRIMARY KEY omezení se automaticky vytvoří jedinečný index sloupce nebo sloupců. Ve výchozím nastavení je tento index clusterovaný; Při vytváření omezení však můžete zadat neclusterovaný index.

• Lze použít v dotazech rozsahu.

Pokud clusterovaný index není vytvořen s vlastností UNIQUE, databázový stroj automaticky přidá 4-bajtový jedinečný sloupec do tabulky. Pokud je to nutné, databázový stroj automaticky přidá do řádku jedinečnou hodnotu, aby byl každý klíč jedinečný. Tento sloupec a jeho hodnoty se používají interně a uživatelé k němu nemají přístup.

Architektura clusterovaného indexu

Indexy řádkového úložiště jsou uspořádané jako stromy B+. Každá stránka ve stromu indexu B+ se nazývá uzel indexu. Horní uzel stromu B+ se nazývá kořenový uzel. Dolní uzly v indexu se nazývají uzly typu list. Všechny úrovně indexu mezi kořenovým a listovými uzly se souhrnně označují jako přechodné úrovně. V clusterovém indexu listové uzly obsahují datové stránky podkladové tabulky. Kořenové a přechodné uzly obsahují indexové stránky, které obsahují řádky indexu. Každý řádek indexu obsahuje hodnotu klíče a ukazatel na stránku střední úrovně ve stromu B+ nebo datový řádek na úrovni listu indexu. Stránky v každé úrovni indexu jsou propojeny v doubly propojeném seznamu.

Clusterované indexy mají v sys.partitions jeden řádek s index_id = 1 každým oddílem používaným indexem. Clusterovaný index má ve výchozím nastavení jeden oddíl. Pokud má clusterovaný index více oddílů, každý oddíl má strukturu stromu B+, která obsahuje data pro daný oddíl. Pokud například clusterovaný index obsahuje čtyři oddíly, existují čtyři struktury B+; každá v jednom oddílu.

V závislosti na datových typech v clusterovém indexu má každá struktura clusterovaného indexu jednu nebo více alokačních jednotek, ve kterých se mají ukládat a spravovat data pro určitý oddíl. Každý clusterovaný index má minimálně jednu IN_ROW_DATA alokační jednotku na oddíl. Clusterovaný index má také jednu LOB_DATA alokační jednotku na oddíl, pokud obsahuje velké sloupce objektu (LOB). Má také jednu ROW_OVERFLOW_DATA alokační jednotku na oddíl, pokud obsahuje sloupce s proměnlivou délkou, které překračují limit velikosti řádku o velikosti 8 060 bajtů.

Stránky v řetězu dat a řádky v nich jsou seřazeny podle hodnoty clusterovaného indexového klíče. Všechna vložení se vytvoří v místě, kde se hodnota klíče v vložené řadě vejde do pořadí mezi existujícími řádky.

Tento obrázek znázorňuje strukturu clusterovaného indexu v jednom oddílu.

Důležité informace o dotazech

Než vytvoříte clusterované indexy, porozumíte tomu, jak se k datům přistupuje. Zvažte použití clusterovaného indexu pro dotazy, které dělají následující:

• Vrátit rozsah hodnot pomocí operátorů, jako jsou BETWEEN, >, >=, < a <=.

  Po nalezení řádku s první hodnotou pomocí clusterovaného indexu je zaručeno, že řádky s následujícími indexovanými hodnotami budou fyzicky sousední. Pokud například dotaz načte záznamy mezi rozsahem čísel prodejních objednávek, clusterovaný index ve sloupci SalesOrderNumber může rychle najít řádek obsahující počáteční číslo prodejní objednávky a potom načíst všechny po sobě jdoucí řádky v tabulce, dokud nedosáhnete posledního čísla prodejní objednávky.

• Vrátí velké sady výsledků.

• Použijte klauzule JOIN; obvykle se jedná o sloupce cizího klíče.

• Použijte ORDER BY nebo GROUP BY klauzule.

  Index sloupců zadaných v ORDER BY klauzuli nebo GROUP BY klauzuli může odebrat potřebu databázového stroje seřadit data, protože řádky jsou už seřazené. Tím se zlepší výkon dotazů.

Úvahy o sloupcích

Obecně byste měli definovat clusterovaný indexový klíč s co nejnižším množstvím sloupců. Zvažte sloupce, které mají jeden nebo více následujících atributů:

• Jsou jedinečné nebo obsahují mnoho jedinečných hodnot.

  Například ID zaměstnance jednoznačně identifikuje zaměstnance. Omezení clusterovaného indexu nebo primárního klíče ve EmployeeID sloupci by zlepšilo výkon dotazů, které vyhledávají informace o zaměstnancích na základě čísla ID zaměstnance. Alternativně lze vytvořit clusterovaný index na LastName, FirstName, MiddleName, protože záznamy zaměstnanců jsou často seskupeny a dotazovány tímto způsobem, a kombinace těchto sloupců by stále poskytovala vysoký stupeň rozdílnosti.

  Návod

  Pokud není zadán jinak, při vytváření omezení PRIMÁRNÍHO KLÍČE vytvoří databázový stroj clusterovaný index pro podporu daného omezení.

  I když je možné použít uniqueidentifier k vynucení jedinečnosti jako PRIMARY KEY klíče, nejedná se o efektivní klíč pro seskupování.

  Pokud používáte uniqueidentifier jako PRIMARY KEY, doporučuje se vytvořit ho jako neklastrovaný index a použít jiný sloupec, například IDENTITY, pro vytvoření klastrovaného indexu.

• Je k nim přistupováno postupně.

  NAPŘÍKLAD ID produktu jednoznačně identifikuje produkty v Production.Product tabulce v AdventureWorks2022 databázi. Dotazy, ve kterých je zadané sekvenční vyhledávání, například WHERE ProductID BETWEEN 980 and 999, by mohly těžit z clusterovaného indexu .ProductID Důvodem je to, že řádky by byly uloženy v seřazených pořadí v daném klíčovém sloupci.

• Definováno jako IDENTITY.

• Často se používá k seřazení dat načtených z tabulky.

  Pokud chcete ušetřit náklady na operaci řazení při každém dotazování sloupce, může být vhodné seskupit (fyzicky seřadit) tabulku v daném sloupci.

Clusterované indexy nejsou dobrou volbou pro následující atributy:

• Sloupce, u kterých dochází k častým změnám

  To způsobí, že se přesune celý řádek, protože databázový stroj musí uchovávat datové hodnoty řádku ve fyzickém pořadí. To je důležitý faktor v systémech zpracování transakcí s velkým objemem, ve kterých jsou data obvykle nestálá.

• Široké klávesy

  Široké klávesy jsou složené z několika sloupců nebo několika velkých sloupců. Hodnoty klíčů z clusterovaného indexu používají všechny neclusterované indexy jako vyhledávací klíče. Všechny neclusterované indexy definované ve stejné tabulce jsou výrazně větší, protože neclusterované položky indexu obsahují clusteringový klíč a také klíčové sloupce definované pro tento neclusterovaný index.

Pokyny pro návrh neclusterovaného indexu

Neclusterovaný index úložiště řádků založený na disku obsahuje hodnoty klíče indexu a lokátory řádků, které odkazují na umístění úložiště dat tabulky. V tabulce nebo indexovém zobrazení můžete vytvořit více neclusterovaných indexů. Obecně platí, že neclusterované indexy by měly být navrženy tak, aby zlepšily výkon často používaných dotazů, které nejsou pokryty clusterovaným indexem.

Podobně jako při použití indexu v knize hledá optimalizátor dotazů hodnotu dat vyhledáním neclusterovaného indexu a vyhledá umístění hodnoty dat v tabulce a pak načte data přímo z tohoto umístění. Díky tomu jsou neclusterované indexy optimální volbou pro přesné shody dotazů, protože index obsahuje položky popisující přesné umístění v tabulce hodnot dat, které se v dotazech hledají. Pokud chcete například zadat dotaz na HumanResources.Employee tabulku pro všechny zaměstnance, kteří hlásí určitému manažeru, může optimalizátor dotazů použít neclusterovaný index IX_Employee_ManagerID, který má ManagerID jako klíčový sloupec. Optimalizátor dotazů dokáže rychle najít všechny položky v indexu, které odpovídají zadanému ManagerID. Každá položka indexu odkazuje na přesnou stránku a řádek v tabulce nebo clusterovaný index, ve kterém lze najít odpovídající data. Jakmile optimalizátor dotazů najde všechny položky v indexu, může přejít přímo na přesnou stránku a řádek a načíst data.

Architektura neclusterovaného indexu

Neclusterované indexy úložiště řádků založené na disku mají stejnou strukturu stromu B+ jako clusterované indexy s výjimkou následujících významných rozdílů:

• Řádky dat podkladové tabulky nejsou seřazené a uložené v pořadí podle jejich neclusterovaných klíčů.

• Úroveň listu neclusterovaného indexu se skládá z indexových stránek místo datových stránek. Indexové stránky na úrovni listu neclusterovaného indexu obsahují klíčové sloupce a zahrnuté sloupce.

Lokátory řádků v neclusterovaných řádcích indexu jsou ukazatel na řádek nebo jsou clusterovaným indexovým klíčem pro řádek, jak je popsáno v následujících příkladech:

• Pokud je tabulka haldou, což znamená, že nemá clusterovaný index, je lokátor řádku ukazatelem na řádek. Ukazatel je sestaven z identifikátoru souboru (ID), čísla stránky a čísla řádku na stránce. Celý ukazatel se označuje jako ID řádku (RID).

• Pokud má tabulka seskupený index nebo je index v indexovaném zobrazení, je ukazatel řádku klíč seskupeného indexu pro řádek.

Lokátory řádků také zajišťují jedinečnost pro neclusterované řádky indexu. Následující tabulka popisuje, jak databázový stroj přidává lokátory řádků do neclusterovaných indexů:

Typ tabulky	Neclusterovaný typ indexu	Lokátor řádků
hromada
	Nejedinečný	Identifikátor RID přidaný do klíčových sloupců
	Jedinečný	Identifikátor RID přidaný do zahrnutých sloupců
Jedinečný clusterovaný index
	Nejedinečný	Clusterované indexové klíče přidané do klíčových sloupců
	Jedinečný	Clusterované indexové klíče přidané do zahrnutých sloupců
Ne jedinečný clusterovaný index
	Nejedinečný	Klíče clusterovaných indexů a jedinečník (pokud je přítomen) přidány do klíčových sloupců.
	Jedinečný	Clusterované indexové klíče a uniqueifier (pokud jsou k dispozici) přidané do zahrnutých sloupců

Databázový stroj nikdy neuchovává daný sloupec dvakrát do neclusterovaného indexu. Pořadí klíčů indexu určené uživatelem při vytváření neclusterovaného indexu je vždy dodrženo: všechny sloupce lokátoru řádků, které je potřeba přidat do klíče neclusterovaného indexu, se přidají na konec klíče za sloupce zadané v definici indexu. Sloupce fungující jako lokátory řádků, které jsou založeny na klíčích clusterovaného indexu v neclusterovaném indexu, může optimalizátor dotazů použít nezávisle na tom, zda byly explicitně uvedeny v definici indexu.

Následující příklady ukazují, jak jsou lokátory řádků implementovány v neclusterovaných indexech:

Clusterovaný index	Definice neclusterovaného indexu	Definice neclusterovaného indexu s lokátory řádků	Vysvětlení
Jedinečný skupinový index s klíčovými sloupci (A, B, C)	Nevýznamný neclusterovaný index s klíčovými sloupci (B, A) a zahrnutými sloupci (E, G)	Klíčové sloupce (B, A, C) a zahrnuté sloupce (E, G)	Neclusterovaný index není jedinečný, takže lokátor řádku musí být v klíčích indexu. Sloupce B a A z lokátoru řádků jsou už k dispozici, takže se přidá jenom sloupec c . Sloupec c se přidá na konec seznamu klíčových sloupců.
Jedinečný skupinový index s klíčovým sloupcem (A)	Neunikátní neklastrový index s klíčovými sloupci (B, C) a zahrnutým sloupcem (A)	Klíčové sloupce (B, C, A)	Neklusterovaný index je neunikátní, takže se k klíči přidá lokátor řádku. Sloupec A ještě není zadaný jako klíčový sloupec, takže se přidá na konec seznamu klíčových sloupců. Sloupec A je teď v klíči, takže ho nemusíte ukládat jako zahrnutý sloupec.
Jedinečný skupinový index s klíčovým sloupcem (A, B)	Jedinečný neclusterovaný index s klíčovým sloupcem (C)	Klíčový sloupec (C) a zahrnuté sloupce (A, B)	Neclusterovaný index je jedinečný, takže lokátor řádku se přidá do zahrnutých sloupců.

Neclusterované indexy mají jeden řádek v sys.partitions pro každý oddíl index_id > 1 používaný indexem. Ve výchozím nastavení má neclusterovaný index jeden oddíl. Pokud má neclusterovaný index více oddílů, každý oddíl má strukturu stromu B+ obsahující řádky indexu pro daný oddíl. Pokud má například neklastrovaný index čtyři oddíly, existují čtyři struktury B+ stromu, přičemž jedna je v každém oddílu.

V závislosti na datových typech v neclusterovém indexu má každá neclusterovaná struktura indexu jednu nebo více jednotek přidělení, ve kterých se mají ukládat a spravovat data pro určitý oddíl. Každý neklastrovaný index má minimálně jednu IN_ROW_DATA alokační jednotku na oddíl pro ukládání stránek stromové struktury B+ indexu. Neclusterovaný index má také jeden LOB_DATA alokační jednotku na oddíl, pokud obsahuje velké sloupce objektu (LOB). Kromě toho má jednu ROW_OVERFLOW_DATA alokační jednotku na oddíl, pokud obsahuje sloupce s proměnlivou délkou, které překračují limit velikosti řádku o velikosti 8 060 bajtů.

Následující obrázek znázorňuje strukturu neclusterovaného indexu v jednom oddílu.

Důležité informace o databázi

Při návrhu neclusterovaných indexů zvažte charakteristiky databáze.

• Databáze nebo tabulky s nízkými požadavky na aktualizaci, ale velké objemy dat můžou těžit z mnoha neclusterovaných indexů, aby se zlepšil výkon dotazů. Zvažte vytvoření filtrovaných indexů pro dobře definované podmnožiny dat za účelem zlepšení výkonnosti dotazů, snížení nákladů na úložný prostor indexů a snížení nákladů na údržbu indexů v porovnání s neklastrovanými indexy.

  Aplikace a databáze systému podpory rozhodování, které obsahují primárně data určená jen pro čtení, můžou těžit z mnoha neclusterovaných indexů. Optimalizátor dotazů má více indexů, ze které si můžete vybrat, abyste zjistili nejrychlejší metodu přístupu a nízké charakteristiky aktualizace databáze znamenají, že údržba indexu neohrožuje výkon.

• Aplikace a databáze OLTP (Online Transaction Processing), které obsahují silně aktualizované tabulky, by se měly vyhnout nadměrnému indexování. Indexy by navíc měly být úzké, to znamená s co nejnižším množstvím sloupců.

  Velký počet indexů v tabulce ovlivňuje výkon INSERT, UPDATE, DELETEa MERGE příkazy, protože všechny indexy musí být odpovídajícím způsobem upraveny, protože data v tabulce se mění.

Důležité informace o dotazech

Než vytvoříte neclusterované indexy, měli byste pochopit, jak se k datům přistupuje. Zvažte použití neclusterovaného indexu pro dotazy, které mají následující atributy:

• Použijte JOIN nebo GROUP BY klauzule.

  Vytvořte několik neklastrovaných indexů pro sloupce zapojené do operací spojení a seskupování a klastrovaný index pro všechny sloupce cizích klíčů.

• Dotazy, které nevracely velké sady výsledků

  Vytvořte filtrované indexy pro pokrytí dotazů, které vrací dobře definovanou podmnožinu řádků z velké tabulky.

  Návod

  Klauzule WHERE obvykle odpovídá klauzuli CREATE INDEX dotazu pokrytého příkazem WHERE.

• Obsahují sloupce, které se často podílejí na vyhledávacích podmínkách dotazu, například WHERE klauzule, které zajišťují přesné shody.

  Návod

  Při přidávání nových indexů zvažte náklady a výhody. Možná bude vhodnější konsolidovat další požadavky na dotazy do existujícího indexu. Zvažte například přidání jednoho nebo dvou sloupců na úrovni listu do existujícího indexu, pokud umožňuje pokrytí několika kritických dotazů, místo toho, aby jeden přesně zakrýval index pro každý kritický dotaz.

Úvahy o sloupcích

Zvažte sloupce, které mají jeden nebo více těchto atributů:

• Zakryjte dotaz.

  Zvýšení výkonu se dosáhne, když index obsahuje všechny sloupce v dotazu. Optimalizátor dotazů může vyhledat všechny hodnoty sloupců v indexu; Data tabulek nebo clusterovaných indexů nejsou přístupná, což vede k menšímu počtu vstupně-výstupních operací disku. Použijte index se zahrnutými sloupci k přidání krycích sloupců místo vytváření širokého indexového klíče.

  Pokud tabulka obsahuje skupinový index, sloupec nebo sloupce definované v clusterovém indexu se automaticky přidají do každého neclusterovaného indexu v tabulce. To může vytvořit pokrytý dotaz bez zadání sloupců clusterovaného indexu v definici neclusterovaného indexu. Pokud například tabulka obsahuje skupinový index ve sloupci C, nevýznamný neclusterovaný index sloupců B a A má jako sloupce s klíčovými hodnotami , BAa C. Další informace najdete v neclusterované architektuře indexu.

• Spousta jedinečných hodnot, například kombinace názvu rodiny a křestního jména, pokud se pro jiné sloupce používá skupinový index.

  Pokud existuje velmi málo jedinečných hodnot, například pouze 1 a 0, většina dotazů nebude index používat, protože prohledávání tabulek je obecně efektivnější. U tohoto typu dat zvažte vytvoření filtrovaného indexu s jedinečnou hodnotou, která se vyskytuje pouze v několika řádcích. Pokud je 0například většina hodnot, optimalizátor dotazů může použít filtrovaný index pro řádky dat, které obsahují 1.

Použití zahrnutých sloupců k rozšíření neclusterovaných indexů

Funkčnost neclusterovaných indexů můžete rozšířit přidáním neklíčových sloupců na úroveň listu neclusterovaného indexu. Zahrnutím sloupců bez klíče můžete vytvořit neclusterované indexy, které pokrývají více dotazů. Je to proto, že sloupce bez klíče mají následující výhody:

• Mohou to být datové typy, které nejsou povoleny jako sloupce klíče indexu.

• Není brán v úvahu databázovým strojem při výpočtu počtu sloupců klíče indexu nebo velikosti klíče indexu.

Index se zahrnutými neklíčovými sloupci může výrazně zlepšit výkon dotazů, pokud jsou všechny sloupce v dotazu zahrnuty do indexu jako klíčové nebo neklíčové sloupce. Zvýšení výkonu se dosahuje, protože optimalizátor dotazů může vyhledat všechny hodnoty sloupců v indexu; Data tabulek nebo clusterovaných indexů nejsou přístupná, což vede k menšímu počtu vstupně-výstupních operací disku.

Poznámka:

Pokud index obsahuje všechny sloupce odkazované dotazem, obvykle se označuje jako pokrytí dotazu.

Zatímco klíčové sloupce jsou uloženy na všech úrovních indexu, neklíčové sloupce jsou uloženy pouze na úrovni listu.

Použití zahrnutých sloupců k zabránění limitům velikosti

Do neclusterovaného indexu můžete zahrnout neklíčové sloupce, abyste se vyhnuli překročení omezení aktuální velikosti indexu maximálně 16 klíčových sloupců a maximální velikosti klíče indexu 900 bajtů. Databázový stroj při výpočtu počtu sloupců klíče indexu nebo velikosti klíče indexu nebere v úvahu neklíčové sloupce.

Předpokládejme například, že chcete indexovat následující sloupce v Document tabulce:

Title NVARCHAR(50)
Revision NCHAR(5)
FileName NVARCHAR(400)

Vzhledem k tomu, že datové typy nchar a nvarchar vyžadují pro každý znak 2 bajty, index obsahující tyto tři sloupce by překročil omezení velikosti 900 bajtů o 10 bajtů (455 × 2). INCLUDE Pomocí klauzule CREATE INDEX příkazu lze klíč indexu definovat jako (Title, Revision) a FileName definovat jako sloupec bez klíče. Tímto způsobem by velikost klíče indexu byla 110 bajtů (55 × 2) a index by stále obsahoval všechny požadované sloupce. Následující příkaz vytvoří takový index.

CREATE INDEX IX_Document_Title
ON Production.Document(Title, Revision)
    INCLUDE(FileName);
GO

Pokud postupujete podle příkladů kódu, můžete tento index odstranit pomocí tohoto příkazu Transact-SQL.

DROP INDEX IX_Document_Title
ON Production.Document;
GO

Index s pokyny pro zahrnuté sloupce

Při návrhu neclusterovaných indexů s zahrnutými sloupci zvažte následující pokyny:

• Sloupce bez klíče jsou definovány v INCLUDE klauzuli CREATE INDEX příkazu.

• Neklíčové sloupce lze definovat pouze u neclusterovaných indexů v tabulkách nebo indexovaných zobrazeních.

• Všechny datové typy jsou povoleny s výjimkou textové, textu a obrázku.

• Počítané sloupce, které jsou deterministické a přesné nebo nepřesné, můžou být zahrnuté sloupce. Další informace najdete v tématu Indexy ve vypočítaných sloupcích.

• Stejně jako u klíčových sloupců mohou počítané sloupce odvozené z obrázku, ntextu a textových datových typů být bezklíčové (zahrnuté) sloupce, pokud je datový typ počítaného sloupce povolený jako neklíčový indexový sloupec.

• Názvy sloupců nelze zadat jak v INCLUDE seznamu, tak v seznamu klíčových sloupců.

• Názvy sloupců se v seznamu nedají opakovat INCLUDE .

Pokyny pro velikost sloupců

• Musí být definován nejméně jeden klíčový sloupec. Maximální počet sloupců bez klíče je 1 023 sloupců. Toto je maximální počet sloupců tabulky minus 1.

• Sloupce s klíči indexu, s výjimkou neklíčů, musí dodržovat omezení velikosti existujícího indexu maximálně 16 klíčových sloupců a celkovou velikost klíče indexu 900 bajtů.

• Celková velikost všech sloupců bez klíče je omezená pouze velikostí sloupců zadaných v INCLUDE klauzuli. Například sloupce varchar(max) jsou omezeny na 2 GB.

Pokyny pro úpravy sloupců

Když upravíte sloupec tabulky definovaný jako zahrnutý sloupec, platí následující omezení:

• Neklíčové sloupce nelze z tabulky vyhodit, pokud se index nezahodí jako první.

• Neklíčové sloupce nelze změnit, s výjimkou následujících kroků:

  • Změňte hodnotu null ve sloupci z NOT NULL na NULL.

  • Zvětšete délku sloupců varchar, nvarchar nebo varbinary .

    Poznámka:

    Tato omezení úprav sloupců platí také pro klíčové sloupce indexu.

Doporučení k návrhu

Přepracujte neclusterované indexy s velkou velikostí klíče tak, aby pro vyhledávání a dotazy byly sloupce pouze klíčové. Nastavte všechny ostatní sloupce, které pokrývají dotaz, včetně neklíčových sloupců. Tímto způsobem máte všechny sloupce potřebné k pokrytí dotazu, ale samotný klíč indexu je malý a efektivní.

Předpokládejme například, že chcete navrhnout index tak, aby pokrývala následující dotaz.

SELECT AddressLine1,
       AddressLine2,
       City,
       StateProvinceID,
       PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' AND N'99999';
GO

Aby bylo možné dotaz pokrýt, musí být každý sloupec definován v indexu. I když byste mohli definovat všechny sloupce jako klíčové sloupce, velikost klíče by byla 334 bajtů. Vzhledem k tomu, že jediný sloupec použitý jako kritéria hledání je PostalCode sloupec, který má délku 30 bajtů, bude lepší návrh indexu definovat PostalCode jako klíčový sloupec a zahrnout všechny ostatní sloupce jako neklíčové sloupce.

Následující příkaz vytvoří index se zahrnutými sloupci pro pokrytí dotazu.

CREATE INDEX IX_Address_PostalCode
ON Person.Address(PostalCode)
    INCLUDE(AddressLine1, AddressLine2, City, StateProvinceID);

Pokud chcete ověřit, že index pokrývá dotaz, vytvořte index a zobrazte odhadovaný plán provádění.

Pokud plán provádění zobrazuje pouze operátor SELECT a operátor Index Seek pro index IX_Address_PostalCode, pak je dotaz tímto indexem pokryt.

Index můžete vypustit pomocí následujícího příkazu:

DROP INDEX IX_Address_PostalCode
ON Person.Address;
GO

Důležité informace o výkonu

Vyhněte se přidávání nepotřebných sloupců. Přidání příliš velkého počtu sloupců indexu, klíčů nebo neklíčových prvků může mít následující dopady na výkon:

• Méně řádků indexu se vejde na stránku. To by mohlo způsobit zvýšení vstupně-výstupních operací a snížení efektivity mezipaměti.

• K uložení indexu je potřeba více místa na disku. Konkrétně přidání varchar(max), nvarchar(max), varbinary(max) nebo datových typů XML jako sloupce indexu bez klíče může výrazně zvýšit požadavky na místo na disku. Důvodem je to, že hodnoty sloupců se zkopírují do úrovně listu indexu. Proto se nacházejí v indexu i základní tabulce.

• Údržba indexu může zvýšit dobu potřebnou k provádění úprav, vkládání, aktualizací nebo odstraňování, do podkladové tabulky nebo indexovaného zobrazení.

Musíte určit, jestli zvýšení výkonu dotazů převáží vliv na výkon při úpravách dat a v dodatečných požadavcích na místo na disku.

Pokyny pro návrh jedinečného indexu

Jedinečný index zaručuje, že klíč indexu neobsahuje žádné duplicitní hodnoty, a proto je každý řádek v tabulce nějakým způsobem jedinečný. Určení jedinečného indexu dává smysl pouze v případech, kdy je jedinečnost charakteristická pro samotná data. Pokud například chcete zajistit, aby hodnoty ve NationalIDNumber sloupci v HumanResources.Employee tabulce byly jedinečné, když je EmployeeIDprimární klíč , vytvořte UNIQUE omezení NationalIDNumber sloupce. Pokud se uživatel pokusí zadat stejnou hodnotu v daném sloupci pro více než jednoho zaměstnance, zobrazí se chybová zpráva a duplicitní hodnota se nezadá.

U vícesloupcových jedinečných indexů index zaručuje, že každá kombinace hodnot v klíči indexu je jedinečná. Pokud je například vytvořen jedinečný index v kombinaci LastNameFirstName, a MiddleName sloupce, žádné dva řádky v tabulce by mohly mít stejnou kombinaci hodnot pro tyto sloupce.

Clusterované i neclusterované indexy můžou být jedinečné. Pokud jsou data ve sloupci jedinečná, můžete ve stejné tabulce vytvořit jedinečný clusterovaný index i několik jedinečných neclusterovaných indexů.

Mezi výhody jedinečných indexů patří:

• Je zajištěna integrita dat definovaných sloupců.
• K dispozici jsou další informace užitečné pro optimalizátor dotazů.

Vytvoření omezení PRIMARY KEY nebo UNIQUE automaticky vytvoří jedinečný index pro zadané sloupce. Neexistují žádné významné rozdíly mezi vytvořením UNIQUE omezení a vytvořením jedinečného indexu nezávisle na omezení. Ověření dat probíhá stejným způsobem a optimalizátor dotazů nerozlišuje mezi jedinečným indexem vytvořeným omezením nebo ručně vytvořeným. Pokud je však cílem integrita dat, měli byste ve sloupci vytvořit omezení UNIQUE nebo PRIMARY KEY. Tímto způsobem je cíl indexu jasný.

Úvahy

• Jedinečný index, UNIQUE omezení nebo PRIMARY KEY omezení nelze vytvořit, pokud v datech existují duplicitní hodnoty klíče.

• Pokud jsou data jedinečná a chcete vynutit jedinečnost, vytvoření jedinečného indexu místo neunique indexu ve stejné kombinaci sloupců poskytuje další informace pro optimalizátor dotazů, který může vytvářet efektivnější plány provádění. V tomto případě se doporučuje vytvoření jedinečného indexu (pokud možno vytvořením UNIQUE omezení).

• Jedinečný neclusterovaný index může obsahovat zahrnuté sloupce bez klíče. Další informace najdete v tématu Index s zahrnutými sloupci.

Pokyny k návrhu filtrovaného indexu

Filtrovaný index je optimalizovaný neclusterovaný index, který je zvláště vhodný pro pokrytí dotazů, které vybírají z dobře definované podmnožina dat. Používá predikát filtru k indexování části řádků v tabulce. Dobře navržený filtrovaný index může zlepšit výkon dotazů, snížit náklady na údržbu indexů a snížit náklady na úložiště indexů v porovnání s indexy full-table.

Filtrované indexy můžou poskytovat následující výhody oproti indexům full-table:

• Vylepšený výkon dotazů a kvalita plánu

  Dobře navržený filtrovaný index zlepšuje výkon dotazů a kvalitu plánu provádění, protože je menší než neclusterovaný index celé tabulky a má filtrované statistiky. Filtrované statistiky jsou přesnější než statistiky celé tabulky, protože pokrývají pouze řádky ve filtrovaném indexu.

• Snížení nákladů na údržbu indexů

  Index je zachován pouze v případech, kdy příkazy jazyka DML (Data Manipulat Language) ovlivňují data v indexu. Filtrovaný index snižuje náklady na údržbu indexů v porovnání s neclusterovaným indexem celé tabulky, protože je menší a udržuje se pouze v případech, kdy jsou ovlivněna data v indexu. Je možné mít velký počet filtrovaných indexů, zejména pokud obsahují data, která jsou ovlivněna zřídka. Podobně platí, že pokud filtrovaný index obsahuje pouze často ovlivněná data, menší velikost indexu snižuje náklady na aktualizaci statistiky.

• Snížení nákladů na úložiště indexů

  Vytvoření filtrovaného indexu může snížit diskové úložiště pro neclusterované indexy, pokud není nutný úplný index tabulky. Neclusterovaný index celé tabulky můžete nahradit několika filtrovanými indexy, aniž byste výrazně zvýšili požadavky na úložiště.

Filtrované indexy jsou užitečné, když sloupce obsahují dobře definované podmnožiny dat, na které se dotazy odkazují v SELECT příkazech. Příklady:

• Řídké sloupce, které obsahují pouze několik hodnot, jež nejsou NULL.
• Heterogenní sloupce, které obsahují kategorie dat.
• Sloupce obsahující rozsahy hodnot, jako jsou částky dolaru, čas a kalendářní data.
• Oddíly tabulky definované jednoduchou porovnávací logikou pro hodnoty sloupců

Nižší náklady na údržbu pro filtrované indexy jsou nejvýraznější, když je počet řádků v indexu malý v porovnání s indexem celé tabulky. Pokud filtrovaný index obsahuje většinu řádků v tabulce, může jeho údržba stát více než údržba celotabulkového indexu. V tomto případě byste měli místo filtrovaného indexu použít úplný index tabulky.

Filtrované indexy jsou definovány v jedné tabulce a podporují pouze jednoduché relační operátory. Pokud potřebujete výraz filtru, který odkazuje na více tabulek nebo má složitou logiku, měli byste vytvořit zobrazení.

Aspekty návrhu

Aby bylo možné navrhnout efektivní filtrované indexy, je důležité pochopit, jaké dotazy vaše aplikace používá a jak souvisí s podmnožinami dat. Mezi příklady dat s dobře definovanými podmnožinami patří sloupce s většinou NULL hodnotami, sloupce s heterogenními kategoriemi hodnot a sloupců s odlišnými rozsahy hodnot. Následující aspekty návrhu poskytují různé scénáře, kdy filtrovaný index může poskytovat výhody oproti indexům full-table.

Návod

Definice indexu columnstore bez clusteru podporuje použití filtrované podmínky. Pokud chcete minimalizovat dopad na výkon přidávání indexu columnstore v tabulce OLTP, použijte filtrovanou podmínku k vytvoření neclusterovaného indexu columnstore pouze na studená data provozní úlohy.

Filtrované indexy pro podmnožinu dat

Pokud sloupec obsahuje jenom několik relevantních hodnot pro dotazy, můžete pro podmnožinu hodnot vytvořit filtrovaný index. Pokud jsou například hodnoty ve sloupci většinou NULL a dotaz vybírá pouze z těch hodnot, které nejsou NULL, můžete vytvořit filtrovaný index pro řádky dat bez NULL. Výsledný index je menší a stojí méně na údržbu než neclusterovaný index celé tabulky definovaný na stejných klíčových sloupcích.

Ukázková databáze Production.BillOfMaterials má například tabulku s 2 679 řádky. Sloupec EndDate obsahuje pouze 199 řádků, které obsahují jinouNULL hodnotu a ostatní řádky 2480 obsahují NULL. Následující filtrovaný index by zahrnoval dotazy, které vracejí sloupce definované v indexu a které vyberou pouze řádky, které nemajíNULL hodnotu pro EndDate.

CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials(ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

Filtrovaný index FIBillOfMaterialsWithEndDate je platný pro následující dotaz. Zobrazte plán odhadovaného spuštění , abyste zjistili, jestli optimalizátor dotazů použil filtrovaný index.

SELECT ProductAssemblyID,
       ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL
      AND ComponentID = 5
      AND StartDate > '20080101';
GO

Další informace o tom, jak vytvořit filtrované indexy a jak definovat filtrovaný výraz predikátu indexu, naleznete v tématu Vytvoření filtrovaných indexů.

Filtrované indexy pro heterogenní data

Pokud tabulka obsahuje heterogenní datové řádky, můžete vytvořit filtrovaný index pro jednu nebo více kategorií dat.

Například produkty uvedené v Production.Product tabulce jsou každý přiřazen ke ProductSubcategoryID, které jsou zase přidružené k produktovým kategoriím Kola, Součásti, Oděvy nebo Příslušenství. Tyto kategorie jsou heterogenní, protože jejich hodnoty sloupců v Production.Product tabulce nejsou úzce korelovány. Například sloupce Color, , ReorderPoint, ListPriceWeight, Classa Style mají jedinečné vlastnosti pro každou kategorii produktu. Předpokládejme, že existují časté dotazy na příslušenství, které mají podkategorie mezi 27 a 36 včetně. Výkon dotazů na příslušenství můžete zlepšit vytvořením filtrovaného indexu v podkategoriích příslušenství, jak je znázorněno v následujícím příkladu.

CREATE NONCLUSTERED INDEX FIProductAccessories
ON Production.Product(ProductSubcategoryID, ListPrice)
INCLUDE(Name)
    WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID <= 36;
GO

Filtrovaný index FIProductAccessories pokrývá následující dotaz, protože výsledky dotazu jsou obsaženy v indexu a plán dotazu neobsahuje vyhledávání základní tabulky. Například, výraz predikátu dotazu ProductSubcategoryID = 33 je podmnožinou filtrovaného predikátu indexu ProductSubcategoryID >= 27 a ProductSubcategoryID <= 36; sloupce ProductSubcategoryID a ListPrice v predikátu dotazu jsou oba klíčovými sloupci v indexu a název je uložen na úrovni listu indexu jako zahrnutý sloupec.

SELECT Name,
       ProductSubcategoryID,
       ListPrice
FROM Production.Product
WHERE ProductSubcategoryID = 33
      AND ListPrice > 25.00;
GO

Klíčové sloupce

Osvědčeným postupem je zahrnout do definice filtrovaného indexu několik klíčových nebo zahrnutých sloupců a začlenit pouze sloupce, které jsou nezbytné pro optimalizaci dotazů k výběru filtrovaného indexu pro plán provádění dotazů. Optimalizátor dotazů může zvolit filtrovaný index pro dotaz bez ohledu na to, jestli index dotaz pokrývá nebo ne. Optimalizátor dotazů ale pravděpodobně zvolí filtrovaný index, pokud dotaz pokrývá.

V některých případech filtrovaný index pokrývá dotaz, aniž by zahrnoval sloupce ve výrazu filtrovaného indexu jako klíčové nebo zahrnuté sloupce v definici filtrovaného indexu. Následující pokyny vysvětlují, kdy sloupec ve filtrovaném výrazu indexu by měl být klíčem nebo zahrnutým sloupcem v definici filtrovaného indexu. Příklady odkazují na filtrovaný index, FIBillOfMaterialsWithEndDate který byl vytvořen dříve.

Sloupec ve výrazu filtrovaného indexu nemusí být klíčem ani zahrnutým sloupcem v definici filtrovaného indexu, pokud výraz filtrovaného indexu odpovídá predikátu dotazu a dotaz nevrací sloupec výrazu filtrovaného indexu ve výsledcích dotazu. Pokryje například následující dotaz, protože predikát dotazu je ekvivalentní výrazu filtru, a FIBillOfMaterialsWithEndDate není vrácen s výsledky dotazu. FIBillOfMaterialsWithEndDate nepotřebuje EndDate jako klíč ani zahrnutý sloupec ve filtrované definici indexu.

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

Sloupec ve filtrovaném výrazu indexu by měl být klíčem nebo zahrnutým sloupcem v definici filtrovaného indexu, pokud predikát dotazu používá sloupec v porovnávání, které není ekvivalentní filtrovanému výrazu indexu. Například platí pro následující dotaz, FIBillOfMaterialsWithEndDate protože vybere podmnožinu řádků z filtrovaného indexu. Nepokrývá ale následující dotaz, protože EndDate se používá ve srovnání EndDate > '20040101', což není ekvivalentem filtrovaného výrazu indexu. Procesor dotazů nemůže tento dotaz spustit, aniž by hledal hodnoty EndDate. EndDate Proto by měl být klíčem nebo zahrnutým sloupcem v definici filtrovaného indexu.

SELECT ComponentID,
       StartDate
FROM Production.BillOfMaterials
WHERE EndDate > '20040101';

Sloupec ve filtrovaném výrazu indexu by měl být klíčovým nebo zahrnutým sloupcem v definici filtrovaného indexu, pokud se sloupec nachází v sadě výsledků dotazu. Například FIBillOfMaterialsWithEndDate vynechává následující dotaz, protože obsahuje sloupec EndDate ve výsledcích dotazu. EndDate Proto by měl být klíčem nebo zahrnutým sloupcem v definici filtrovaného indexu.

SELECT ComponentID,
       StartDate,
       EndDate
FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;

Clusterovaný indexový klíč tabulky nemusí být klíčem ani zahrnutým sloupcem v definici filtrovaného indexu. Clusterovaný indexový klíč se automaticky zahrne do všech neclusterovaných indexů, včetně filtrovaných indexů.

Pro vynechání indexů FIBillOfMaterialsWithEndDate a FIProductAccessories spusťte následující příkazy:

DROP INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials;
GO

DROP INDEX FIProductAccessories
ON Production.Product;
GO

Operátory převodu dat v predikátu filtru

Pokud relační operátor zadaný ve filtrovaném indexovém výrazu filtrovaného indexu vede k implicitnímu nebo explicitnímu převodu dat, dojde k chybě, pokud dojde k převodu na levé straně relačního operátoru. Řešením je napsat filtrovaný indexový výraz s operátorem převodu dat (CAST nebo CONVERT) na pravé straně relačního operátoru.

Následující příklad vytvoří tabulku s různými datovými typy.

CREATE TABLE dbo.TestTable
(
    a INT,
    b VARBINARY (4)
);
GO

V následující filtrované definici indexu je sloupec b implicitně převeden na celočíselné datové typy pro účely porovnání s konstantou 1. Tím se vygeneruje chybová zpráva 10611, protože převod probíhá na levé straně operátoru ve filtrovaném predikátu.

CREATE NONCLUSTERED INDEX TestTabIndex
ON dbo.TestTable(a, b)
    WHERE b = 1;
GO

Řešením je převést konstantu na pravé straně tak, aby byla stejného typu jako sloupec b, jak je vidět v následujícím příkladu:

CREATE INDEX TestTabIndex
ON dbo.TestTable(a, b)
    WHERE b = CONVERT (VARBINARY (4), 1);
GO

Přesunutí převodu dat z levé strany na pravou stranu porovnávacího operátoru může změnit význam převodu. V předchozím příkladu se při přidání operátoru CONVERT na pravou stranu porovnání změnilo z celočíselného porovnání na varbinární porovnání.

Spuštěním následujícího příkazu odstraňte objekty vytvořené v tomto příkladu:

DROP TABLE TestTable;
GO

Architektura indexu Columnstore

Index sloupcového úložiště je technologie pro ukládání, načítání a správu dat pomocí sloupcového formátu dat, který se nazývá sloupcové úložiště. Další informace najdete v článku Indexy columnstore: přehled.

Informace o verzi a nové funkce najdete v sekci Co je nového v indexech columnstore.

Znalost těchto základů usnadňuje pochopení dalších článků columnstore, které vysvětlují, jak je efektivně používat.

Úložiště dat používá kompresi columnstore a rowstore.

Při diskuzi o indexech columnstore používáme termíny rowstore a columnstore ke zdůraznění formátu úložiště dat. Indexy columnstore používají oba typy úložiště.

• Columnstore je data, která jsou logicky uspořádaná jako tabulka s řádky a sloupci a fyzicky uložená ve sloupcovém datovém formátu.

  Index columnstore fyzicky ukládá většinu dat ve formátu columnstore. Ve formátu columnstore se data komprimují a dekomprimují po sloupcích. Není nutné nekomprimovat jiné hodnoty v každém řádku, které dotaz nevyžaduje. Díky tomu můžete rychle zkontrolovat celý sloupec velké tabulky.

• Úložiště řádků je data, která jsou logicky uspořádaná jako tabulka s řádky a sloupci, a poté fyzicky uložená ve formátu dat po řádcích. Jedná se o tradiční způsob ukládání dat relační tabulky, jako je halda nebo clusterovaný index stromu B+.

  Index columnstore také fyzicky ukládá některé řádky ve formátu rowstore nazývaném deltastore. Deltastore, také nazývaný delta rowgroups, je místo pro řádky, které jsou příliš málo na to, aby se kvalifikovaly k sloučení a kompresi do columnstore. Každá rozdílová skupina řádků je implementována jako klastrovaný index typu B+ stromu.

• Deltastore je úložiště pro řádky, které jsou v příliš malém počtu, aby je bylo možné zkomprimovat do columnstore. Deltastore ukládá řádky ve formátu rowstore.

Další informace o termínech a konceptech columnstore najdete v tématu Indexy Columnstore: přehled.

Operace se provádějí se skupinami řádků a segmenty sloupců.

Index columnstore seskupuje řádky do spravovatelných jednotek. Každá z těchto jednotek se nazývá skupina řádků. Pro zajištění nejlepšího výkonu je počet řádků ve skupině řádků dostatečně velký, aby se zlepšily míry komprese a dostatečně malé, aby mohly využívat operace v paměti.

Index columnstore například provádí tyto operace se skupinami řádků:

• Zkomprimuje skupiny řádků do sloupcového úložiště. Komprese se provádí u každého segmentu sloupce v rámci skupiny řádků.
• Sloučí skupiny řádků během ALTER INDEX ... REORGANIZE operace, včetně odebrání odstraněných dat.
• Vytvoří nové skupiny řádků během ALTER INDEX ... REBUILD operace.
• Sestavy o stavu skupiny řádků a fragmentaci ve zobrazeních dynamické správy (DMVs).

Deltastore se skládá z jedné nebo více skupin řádků, které se nazývají delta rowgroups. Každá delta skupina řádků je seskupený index B+ stromu, který ukládá malé hromadné zátěže a vklady, dokud skupina neobsahuje 1 048 576 řádků. V tomto okamžiku proces zvaný přesunovač n-tic automaticky komprimuje uzavřenou skupinu řádků do sloupcového úložiště.

Další informace o stavech skupiny řádků najdete v tématu sys.dm_db_column_store_row_group_physical_stats.

Návod

Příliš mnoho malých skupin řádků snižuje kvalitu indexu columnstore. Operace reorganizace slučuje menší skupiny řádků podle zásad interní prahové hodnoty, která určuje, jak odebrat odstraněné řádky a zkombinovat komprimované skupiny řádků. Po sloučení by se měla zlepšit kvalita indexu.

V SQL Serveru 2019 (15.x) a novějších verzích pomáhá přesouvání násobků úloha sloučení na pozadí, která automaticky komprimuje menší OPEN rozdílové skupiny řádků, které již nějaký čas existují a odpovídají interní prahové hodnotě, nebo slučuje COMPRESSED skupiny řádků, z nichž bylo odstraněno mnoho záznamů.

Každý sloupec má v každé skupině řádků několik hodnot. Tyto hodnoty se nazývají segmenty sloupců. Každá skupina řádků obsahuje jeden segment sloupce pro každý sloupec v tabulce. Každý sloupec má v každé skupině řádků jeden segment sloupce.

Když index columnstore komprimuje skupinu řádků, zkomprimuje každý segment sloupce samostatně. Pokud chcete dekomprimovat celý sloupec, index columnstore potřebuje odkomprimovat pouze jeden segment sloupce z každé skupiny řádků.

Další informace o termínech a konceptech columnstore najdete v tématu Indexy Columnstore: přehled.

Malé náklady a vložky se přesunou do deltastoru.

Index columnstore zlepšuje kompresi a výkon columnstore tím, že do indexu columnstore komprimuje alespoň 102 400 řádků najednou. Aby se řádky mohly hromadně zkomprimovat, index columnstore shromažďuje malé objemy a vkládá je do deltastore. Operace deltastore se zpracovávají na pozadí. Pokud chcete vrátit správné výsledky dotazu, clusterovaný index columnstore kombinuje výsledky dotazu z columnstore i deltastore.

Řádky přejdou do rozdílového úložiště, když jsou:

• Vloženo pomocí INSERT INTO ... VALUES příkazu.
• Na konci hromadného nahrávání a jejich počet je menší než 102 400.
• Aktualizovaný. Každá aktualizace se implementuje jako odstranění a vložení.

Deltastore také ukládá seznam ID odstraněných řádků, které byly označeny jako odstraněné, ale ještě nejsou fyzicky odstraněny ze columnstore.

Další informace o termínech a konceptech columnstore najdete v tématu Indexy Columnstore: přehled.

Když jsou skupiny rozdílových řádků plné, jsou zkomprimovány do sloupcového úložiště.

Clusterované columnstore indexy shromažďují až 1 048 576 řádků v každé řádkové skupině delta před její kompresí do columnstore. Tím se zlepší komprese indexu columnstore. Když rozdílová skupina řádků dosáhne maximálního počtu řádků, přejde ze stavu OPEN do stavu CLOSED. Proces běžící na pozadí s názvem tuple-mover kontroluje uzavřené skupiny řádků. Pokud proces najde uzavřenou skupinu řádků, zkomprimuje skupinu řádků a uloží ji do columnstore.

Když je delta skupina řádků zkomprimovaná, stávající delta skupina řádků přejde do stavu TOMBSTONE, který bude později odebrán nástrojem tuple-mover, jakmile na ni není žádný odkaz, a nová komprimovaná skupina řádků se označí jako COMPRESSED.

Další informace o stavech skupiny řádků najdete v tématu sys.dm_db_column_store_row_group_physical_stats.

Delta rowgroups můžete vynutit do columnstore pomocí alter INDEX k opětovnému sestavení nebo změně uspořádání indexu. Pokud při kompresi dojde k zatížení paměti, index columnstore může snížit počet řádků v komprimované skupině řádků.

Další informace o termínech a konceptech columnstore najdete v tématu Indexy Columnstore: přehled.

Každý oddíl tabulky má vlastní skupiny řádků a rozdílové skupiny řádků.

Koncept dělení je stejný pro clusterový index, haldu a index columnstore. Dělení tabulky rozdělí tabulku na menší skupiny řádků podle rozsahu hodnot sloupců. Často se používá ke správě dat. Můžete například vytvořit oddíl pro každý rok dat a pak pomocí přepínání oddílů archivovat data do levnějšího úložiště. Přepínání oddílů funguje na indexech columnstore a usnadňuje přesun oddílu dat do jiného umístění.

Skupiny řádků se vždy definují v rámci partitionu tabulky. Když je index columnstore rozdělen, má každý oddíl vlastní komprimované skupiny řádků a delta skupiny řádků.

Návod

Zvažte použití dělení tabulky, pokud je potřeba odebrat data z columnstore. Přepnutí a zkrácení oddílů, které už nejsou potřeba, je efektivní strategií odstraňování dat bez vytváření fragmentace způsobené menšími skupinami řádků.

Každý oddíl může mít více delta skupin řádků.

Každá partition může obsahovat více než jednu delta skupinu řádků. Když sloupcový index potřebuje přidat data do rozdílové skupiny řádků a tato skupina je uzamčená, pokusí se získat zámek na jiné rozdílové skupině řádků. Pokud nejsou k dispozici žádné rozdílové skupiny řádků, index columnstore vytvoří novou rozdílovou skupinu řádků. Například tabulka s 10 partice může mít snadno 20 nebo více delta skupin řádků.

Kombinování indexů columnstore a rowstore ve stejné tabulce

Neclusterovaný index obsahuje kopii části nebo všech řádků a sloupců v podkladové tabulce. Index je definován jako jeden nebo více sloupců tabulky a má volitelnou podmínku, která filtruje řádky.

V tabulce rowstore můžete vytvořit aktualizovatelný neclusterovaný index columnstore. Index columnstore ukládá kopii dat, takže potřebujete další úložiště. Data v indexu columnstore se ale komprimuje na menší velikost, než vyžaduje tabulka rowstore. Tímto způsobem můžete spustit analýzy indexu columnstore a transakcí na indexu rowstore současně. Columnstore se aktualizuje, když se data změní v tabulce rowstore, takže oba indexy pracují se stejnými daty.

U indexu columnstore můžete mít jeden nebo více neclusterovaných indexů rowstore. Tímto způsobem můžete provádět efektivní hledání tabulek v podkladovém columnstore. K dispozici jsou i další možnosti. Omezení primárního klíče můžete například vynutit pomocí UNIQUE omezení v tabulce rowstore. Jelikož se neunikátní hodnotu nepodaří vložit do tabulky typu rowstore, databázový engine nemůže vložit tuto hodnotu do columnstore.

Důležité informace o výkonu

• Definice indexu columnstore bez clusteru podporuje použití filtrované podmínky. Pokud chcete minimalizovat dopad na výkon přidávání indexu columnstore v tabulce OLTP, použijte filtrovanou podmínku k vytvoření neclusterovaného indexu columnstore pouze na studená data provozní úlohy.

• Tabulka v paměti může mít jeden sloupcový index. Můžete ji vytvořit při vytvoření tabulky nebo ji později přidat pomocí ALTER TABLE (Transact-SQL). Před SQL Serverem 2016 (13.x) může mít index columnstore pouze tabulka založená na disku.

Další informace najdete v tématu Indexy Columnstore – výkon dotazů.

Pokyny pro návrh

• Tabulka rowstore může mít jeden neklastrovaný aktualizovatelný index columnstore. Před SQL Serverem 2014 (12.x) byl neclusterovaný index columnstore jen pro čtení.

Další informace najdete v tématu Indexy Columnstore – pokyny k návrhu.

Pokyny k návrhu indexu hash

Všechny tabulky optimalizované pro paměť musí mít alespoň jeden index, protože se jedná o indexy, které spojují řádky. V tabulce optimalizované pro paměť je každý index také optimalizován pro paměť. Indexy hash jsou jedním z možných typů indexů v tabulce optimalizované pro paměť. Další informace najdete v tématu Indexy v tabulkách Memory-Optimized.

Platí pro: SQL Server, Azure SQL Database a Azure SQL Managed Instance

Architektura indexu hash

Index hash se skládá z pole ukazatelů a každý prvek pole se nazývá kontejner hash.

• Každý slot má 8 bajtů, které se používají k uložení adresy paměti spojového seznamu klíčových záznamů.
• Každá položka je hodnota pro klíč indexu a adresa odpovídajícího řádku v podkladové tabulce optimalizované pro paměť.
• Každá položka odkazuje na další položku ve spojovém seznamu položek, které jsou zřetězené do aktuálního kbelíku.

Počet kbelíků musí být zadán v době definice indexu:

• Čím nižší je poměr kontejnerů k řádkům tabulky nebo k jedinečným hodnotám, tím delší je průměrný seznam propojení kontejnerů.
• Seznamy krátkých odkazů fungují rychleji než dlouhé seznamy odkazů.
• Maximální počet kbelíků v hashových indexech je 1 073 741 824.

Návod

Pokud chcete určit to správné BUCKET_COUNT pro vaše data, přečtěte si téma Konfigurace počtu kontejnerů indexu hash.

Funkce hash se použije na sloupce klíče indexu a výsledek funkce určuje, do jakého kontejneru klíč spadá. Každý kontejner má ukazatel na řádky, jejichž hodnoty hashovaného klíče se mapují na tento kontejner.

Funkce hash použitá pro indexy hash má následující vlastnosti:

• Databázový stroj má jednu funkci hash, která se používá pro všechny indexy hash.
• Funkce hash je deterministická. Stejná hodnota vstupního klíče se vždy mapuje na stejný kontejner v indexu hash.
• Více indexových klíčů může být namapováno do stejného kontejneru hash.
• Funkce hash je vyvážená, což znamená, že rozdělení hodnot klíče indexu v kontejnerech hash se obvykle řídí rozdělením poissonovy nebo zvonové křivky, nikoli plochým lineárním rozdělením.
• Poissonovo rozdělení není rovnoměrné rozdělení. Hodnoty klíče indexu nejsou rovnoměrně distribuovány v kontejnerech hash.
• Pokud jsou dva indexové klíče namapované do stejného kontejneru hash, dojde ke kolizi hash. Velký počet kolizí hodnot hash může mít vliv na výkon operací čtení. Realistický cíl je pro 30 procent kontejnerů obsahovat dvě různé klíčové hodnoty.

Souhra hašovacího indexu a věder je shrnuta na následujícím obrázku.

Konfigurace počtu kontejnerů indexu hash

Počet kbelíků indexu hash se zadává při vytváření indexu ALTER TABLE...ALTER INDEX REBUILD a dá se změnit pomocí syntaxe.

Ve většině případů by počet kbelíků byl v ideálním případě mezi 1 a 2násobky počtu jedinečných hodnot v klíči indexu.
Nemusí být vždy možné předpovědět, kolik hodnot může určitý klíč indexu obsahovat nebo kolik bude mít. Výkon je obvykle stále dobrý, pokud hodnota BUCKET_COUNT je do 10násobku skutečného počtu klíčových hodnot, a přeceňování je obecně lepší než podcenění.

Příliš málo kbelíků může mít následující nevýhody:

• Více kolizí hash pro různé hodnoty klíčů.
• Každá jedinečná hodnota je nucena sdílet stejný kbelík s jinou odlišnou hodnotou.
• Průměrná délka řetězce na jeden kbelík roste.
• Čím delší je řetěz kbelíků, tím pomalejší bude hledání shody v indexu.

Příliš mnoho kontejnerů může mít následující nevýhody:

• Příliš vysoký počet kbelíků může vést k více prázdným kontejnerům.
• Prázdné kontejnery ovlivňují výkon kontrol úplného indexu. Pokud se kontroly provádějí pravidelně, zvažte výběr počtu kontejnerů blízko počtu jedinečných hodnot klíče indexu.
• Prázdné kontejnery používají paměť, i když každý kbelík používá pouze 8 bajtů.

Poznámka:

Přidání dalších kbelíků nijak nezmenšuje řetězení položek, které sdílejí duplicitní hodnotu. Míra duplikace hodnot se používá k rozhodování, jestli je hodnota hash vhodným typem indexu, nikoli k výpočtu počtu kbelíků.

Důležité informace o výkonu

Výkon indexu hash je následující:

• Vynikající, když predikát v WHERE klauzuli určuje přesnou hodnotu pro každý sloupec v klíči indexu hash. Hash index se vrátí ke skenování při použití podmínky nerovnosti.
• Špatné, když predikát v WHERE klauzuli hledá rozsah hodnot v klíči indexu.
• Špatné, když predikát v WHERE klauzuli stanoví jednu specifickou hodnotu pro první sloupec klíče indexu hash se dvěma sloupci, ale nezadá hodnotu pro ostatní sloupce klíče.

Návod

Predikát musí obsahovat všechny sloupce v klíči indexu hash. Index hash vyžaduje klíč (k zahashování), aby mohl přistupovat k indexu.

Pokud se indexový klíč skládá ze dvou sloupců a WHERE klauzule poskytuje pouze první sloupec, databázový stroj nemá úplný klíč k hodnotě hash. Výsledkem je dotazovací plán pro prohledávání indexu.

Pokud se použije hashový index a počet jedinečných klíčů indexu je 100krát (nebo více) větší než počet řádků, zvažte zvýšení počtu větších kbelíků, abyste se vyhnuli velkým řetězům řádků, nebo místo toho použijte neklastrový index.

Aspekty deklarace

Index hash může existovat pouze v tabulce optimalizované pro paměť. V tabulce na bázi disku nemůže existovat.

Index hash lze deklarovat takto:

• UNIQUEnebo může být výchozí hodnota nonunique.
• NONCLUSTERED, což je výchozí hodnota.

Následující ukázka syntaxe vytvoří index hash nezávisle na příkazu CREATE TABLE.

ALTER TABLE MyTable_memop ADD INDEX ix_hash_Column2
    UNIQUE NONCLUSTERED HASH (Column2) WITH (BUCKET_COUNT = 64);

Verze řádků a správa paměťového odpadu

V tabulce optimalizované pro paměť, když je řádek ovlivněný UPDATE, vytvoří tabulka aktualizovanou verzi řádku. Během transakce aktualizace mohou ostatní sezení být schopny přečíst starší verzi řádku a tak se vyhnout zpomalení výkonu způsobeného zámkem řádku.

Index hash může mít také různé verze svých položek, aby se přizpůsobily aktualizaci.

Pokud už starší verze nejsou potřeba, vlákno uvolňování paměti (GC) prochází kontejnery a jejich seznamy odkazů, aby se vyčistily staré položky. Vlákno GC funguje lépe, pokud jsou délky řetězu seznamu odkazů krátké. Pro více informací se podívejte na In-Memory OLTP Garbage Collection.

Pokyny k návrhu neklastrového indexu optimalizovaného pro paměť

Neclusterované indexy jsou jedním z možných typů indexů v tabulce optimalizované pro paměť. Další informace najdete v tématu Indexy v tabulkách Memory-Optimized.

Platí pro: SQL Server, Azure SQL Database a Azure SQL Managed Instance

Architektura neclusterovaného indexu v paměti

Neclusterované indexy v paměti se implementují pomocí datové struktury označované jako Bw-tree, původně vizualizované a popsané společností Microsoft Research v roce 2011. Bw-strom je varianta B-stromu bez zámků a západek. Další informace naleznete v tématu Bw-tree: A B-tree for New Hardware Platforms.

Na vysoké úrovni lze Bw-tree pochopit jako mapu stránek uspořádaných podle ID stránky (PidMap), zařízení pro přidělení a opakované použití ID stránek (PidAlloc) a sady stránek propojených v mapě stránky a k sobě navzájem. Tyto tři dílčí podkomponenty vysoké úrovně tvoří základní vnitřní strukturu Bw-tree.

Struktura se podobá normálnímu stromu B v tom smyslu, že každá stránka má sadu hodnot klíčů, které jsou seřazené a v indexu jsou úrovně, které odkazují na nižší úroveň a úrovně listu odkazují na řádek dat. Existuje však několik rozdílů.

Stejně jako indexy hash je možné propojit více datových řádků (verze). Ukazatele na stránku mezi úrovněmi jsou logická ID stránek, které se odkazují na tabulku mapování stránek, jež obsahuje fyzickou adresu pro každou stránku.

Nejsou k dispozici žádné místní aktualizace indexových stránek. Pro tento účel jsou zavedeny nové rozdílové stránky.

• Aktualizace stránek nevyžadují zajištění ani uzamykání.
• Indexové stránky nejsou pevnou velikostí.

Hodnota klíče na každé stránce úrovně bezlistí je nejvyšší hodnota, kterou obsahuje podřízená stránka, na kterou směřuje, a každý řádek také obsahuje logické ID této stránky. Na stránkách na úrovni listu obsahuje spolu s klíčovou hodnotou fyzickou adresu datového řádku.

Bodové vyhledávání se podobá stromům B, s tím rozdílem, že stránky jsou propojeny pouze jedním směrem, databázový engine SQL Serveru se řídí pravými ukazateli stránek, kde každá ne-listová stránka má nejvyšší hodnotu podřízené položky, ne nejnižší hodnotu jako v B-stromu.

Pokud se stránka na úrovni listu musí změnit, databázový stroj SQL Serveru nezmění samotnou stránku. Spíše databázový stroj SQL Serveru vytvoří rozdílový záznam, který popisuje změnu, a připojí ho k aktuální stránce. Pak také aktualizuje adresu tabulky stránkovací mapy pro předchozí stránku na adresu rozdílového záznamu, který se nyní stává fyzickou adresou této stránky.

Ke správě struktury stromu Bw mohou být požadovány tři různé operace: konsolidace, rozdělení a sloučení.

Rozdílová konsolidace

Dlouhý řetězec delta záznamů může nakonec snížit výkon hledání, protože to může znamenat, že při vyhledávání prostřednictvím indexu procházíme rozsáhlé řetězce. Pokud je nový rozdílový záznam přidán do řetězu, který již má 16 prvků, změny v rozdílových záznamech jsou sloučeny do odkazované indexové stránky a stránka se pak znovu sestaví, včetně změn označených novým rozdílovým záznamem, který aktivoval sloučení. Nově znovu sestavená stránka má stejné ID stránky, ale novou adresu paměti.

Rozdělená stránka

Indexová stránka ve stromu Bw roste podle potřeby, počínaje uložením jednoho řádku až po uložení maximálně 8 kB. Jakmile indexová stránka roste na 8 kB, nové vložení jednoho řádku způsobí rozdělení indexové stránky. Pokud jde o interní stránku, znamená to, že není k dispozici dostatek prostoru pro přidání další hodnoty klíče a ukazatele. U listové stránky to znamená, že řádek by byl příliš velký na to, aby se vešel na stránku, jakmile se začlení všechny rozdílové záznamy. Informace o statistikách v záhlaví listové stránky sledují, kolik místa je potřeba pro sloučení rozdílových záznamů. Tyto informace se upraví, jakmile je přidán každý nový rozdílový záznam.

Operace rozdělení se provádí ve dvou atomických krocích. V následujícím diagramu předpokládejme, že listová stránka vynutí rozdělení, protože se vkládá klíč s hodnotou 5, a existuje ne-listová stránka, která odkazuje na konec aktuální listové stránky (hodnota klíče 4).

Krok 1: Přidělte dvě nové stránky P1 a P2rozdělte řádky ze staré P1 stránky na tyto nové stránky, včetně nově vloženého řádku. Nový slot v tabulce mapování stránek se používá k uložení fyzické adresy stránky P2. Tyto stránky P1 a P2 zatím nejsou přístupné pro žádné souběžné operace. Kromě toho je nastavený logický ukazatel z P1 na P2 . Potom v jednom atomickém kroku aktualizujte tabulku mapování stránky tak, aby se ukazatel změnil ze starého P1 na nový P1.

Krok 2: Ne-listová stránka odkazuje na P1, ale neexistuje žádný přímý ukazatel z ne-listové stránky na P2. P2 je dosažitelná pouze prostřednictvím P1. Pokud chcete vytvořit ukazatel z neleaf stránky na P2, přidělte novou neleaf stránku (interní indexovou stránku), zkopírujte všechny řádky ze staré neleaf stránky a přidejte nový řádek, na který chcete odkazovat P2. Jakmile to uděláte, aktualizujte v jednom atomickém kroku tabulku mapování stránek tak, aby změnila ukazatel ze staré neleaf stránky na novou neleaf stránku.

Sloučit stránku

DELETE Pokud má operace za následek, že stránka má méně než 10 procent maximální velikosti stránky (aktuálně 8 kB) nebo s jedním řádkem na ní, tato stránka se sloučí s souvislou stránkou.

Při odstranění řádku ze stránky se přidá rozdílový záznam pro odstranění. Kromě toho se provede kontrola, která určí, jestli indexová stránka (neleaf page) splňuje podmínky pro sloučení. Tato kontrola ověří, jestli je zbývající místo po odstranění řádku menší než 10 procent maximální velikosti stránky. Pokud se kvalifikuje, sloučení se provádí ve třech atomických krocích.

Na následujícím obrázku předpokládejme, že DELETE operace odstraní hodnotu klíče 10.

Krok 1: Vytvoří se rozdílová stránka představující klíčovou hodnotu 10 (modrý trojúhelník) a její ukazatel v nelistové stránce Pp1 je nastaven na novou rozdílovou stránku. Vytvoří se také speciální stránka merge-delta (zelený trojúhelník), která je propojena tak, aby ukazovala na rozdílovou stránku. V této fázi nejsou obě stránky (delta page a merge-delta page) viditelné pro žádnou souběžnou transakci. V jednom atomickém kroku se ukazatel na stránku na úrovni listu P1 v tabulce mapování stránky aktualizuje tak, aby odkazoval na stránku merge-delta. Po tomto kroku nyní položka pro hodnotu klíče 10 v Pp1 odkazuje na stránku merge-delta.

Krok 2: Řádek představující hodnotu klíče 7 na neleafové stránce Pp1 je potřeba odebrat a položku pro klíčovou hodnotu 10 aktualizovat tak, aby odkazovala na P1. Chcete-li to provést, je přidělena nová ne-listová stránka Pp2 a všechny řádky z Pp1 jsou zkopírovány, s výjimkou řádku představujícího hodnotu 7 klíče; poté se řádek pro hodnotu 10 klíče aktualizuje tak, že bude odkazovat na stránku P1. V jednom atomovém kroku se po dokončení aktualizuje položka tabulky mapování stránky, která ukazuje na Pp1, aby nyní odkazovala na Pp2. Pp1 už není dostupný.

Krok 3: Stránky na úrovni listu P2 a P1 jsou sloučeny a rozdílové stránky se odeberou. Za tímto účelem se přidělí nová stránka P3 a řádky z P2 a P1 budou sloučeny a rozdílové změny stránky jsou zahrnuty v nové P3. Pak se v jednom atomovém kroku aktualizuje položka tabulky mapování stránky odkazující na stránku P1, aby odkazovala na stránku P3.

Důležité informace o výkonu

Výkon neclusterovaného indexu je lepší než neclusterované indexy hash při dotazování tabulky optimalizované pro paměť s predikáty nerovnosti.

Sloupec v tabulce optimalizované pro paměť může být součástí indexu hash i neclusterovaného indexu.

Pokud má klíčový sloupec v neklastrovaném indexu mnoho duplicitních hodnot, může se zhoršit výkon u aktualizací, vkládání a odstraňování. Jedním ze způsobů, jak v této situaci zlepšit výkon, je přidat sloupec, který má lepší selektivitu v klíči indexu.

Související obsah

• VYTVOŘTE INDEX (Transact-SQL)
• Optimalizace údržby indexů za účelem zlepšení výkonu dotazů a snížení spotřeby prostředků
• dělené tabulky a indexy
• Indexy v tabulkách Memory-Optimized
• Indexy columnstore: Přehled
• Indexy ve vypočítaných sloupcích
• Ladění neclusterovaných indexů s využitím návrhů týkajících se chybějících indexů