--Index je struktura na disku, která je spojena s tabulkou nebo pohledem a urychluje načítání řádků z tabulky nebo pohledu. Index obsahuje klíče sestavené z jednoho nebo více sloupců v tabulce nebo pohledu. Tyto klíče jsou uloženy ve vyvážené stromové struktuře, která podporuje rychlé vyhledávání řádků podle jejich hodnot klíčů v SQL Server.

--Clusterové indexy třídí a ukládají řádky dat v tabulkách nebo pohledech na základě jejich klíčových hodnot. Tyto hodnoty jsou sloupce zahrnuté v definici indexu. Existuje pouze jeden seskupený index na tabulku, protože datové řádky lze třídit pouze v jednom pořadí.
--Řádky dat v tabulce jsou uloženy v pořadí řazení pouze v případě, že tabulka obsahuje seskupený index. Pokud má tabulka seskupený index, pak se tabulka nazývá seskupená. Pokud tabulka nemá seskupený index, jsou datové řádky uloženy v neuspořádané struktuře nazývané halda.

--Neshlukovaný index má přesně stejnou strukturu jako seskupený index, ale se dvěma důležitými rozdíly:
--Neshlukovaný index nemění fyzické pořadí řádků v tabulce a listové stránky v neshlukovaném indexu se skládají z indexových klíčů a záložek.

--Shlukované indexy poskytují rychlejší načítání dat než neshlukované indexy. Obvykle se ukáže, že jsou rychlejší i při aktualizaci, ale ne, když se na stejném místě uprostřed vztahu odehrává mnoho aktualizací.

--Z nějakého důvodu má seskupený index tendenci běžet rychleji než neshlukovaný index. Když systém skenuje seskupený index, není nutné opouštět strukturu B-stromu pro skenování datových stránek, protože takové stránky jsou již přítomny na úrovni listu stromu.

--Neklastrovaný index také vyžaduje více I/O operací než odpovídající seskupený index.

--Neshlukovaný index potřebuje číst datové stránky po skenování B-stromu, nebo pokud je seskupený index na jiném sloupci tabulky, musí neclusterovaný index číst strukturu B-stromu seskupeného indexu. .

--Takže clusterovaný index bude výrazně rychlejší než prohledávání tabulky, i když je jeho selektivita docela špatná (dotaz vrací hodně řádků)

VYTVOŘIT TABULKU tsql.dbo.NI
ID int NOT NULL,
T char(8) NULL
);

VYTVOŘTE TABULKU tsql.dbo.NCI
ID int NOT NULL,
T char(8) NULL
);

--Vytvořte seskupený index

VYTVOŘIT KLUSTEROVÝ INDEX IX_1
ON tsql.dbo.NCI(ID);

--Vytvořte neklastrovaný index na tabulce

VYTVOŘTE NEZAHRNUTÝ INDEX IX_2
ON tsql.dbo.NCI(T);

--Přidat testovací data
DECLARE @i INT = 100000;
DECLARE @t CHAR(1) = "T";

KDYŽ @i > 0
ZAČÍT
vložit do tsql.dbo.NI hodnoty (@i, @t + CAST(@i AS char(6)));
vložit do tsql.dbo.NCI hodnoty (@i, @t + CAST(@i AS char(6)));
SET @i -= 1;
KONEC

--Dotazy na tabulku s indexy
VYBERTE ID, T Z tsql.dbo.NCI
OBJEDNEJTE PODLE ID, T

SELECT ID, COUNT(*) AS C FROM tsql.dbo.NCI
GROUP PODLE ID, T

VYBERTE ID, T Z tsql.dbo.NCI
WHERE ID > 4000 A ID< 55000 AND T LIKE "T%"

--Dotaz pomocí obou indexů
USE tsql;
SELECT CAST(dbo.NCI.ID AS VARCHAR)
OD dbo.NCI
GROUP BY dbo.NCI.ID
UNION VŠECHNY
VYBERTE dbo.NCI.T
OD dbo.NCI
GROUP BY dbo.NCI.T

--Informace o indexech
SELECT index_type_desc, index_depth, index_level,
počet_stránek, počet_záznamů
Z sys.dm_db_index_physical_stats
(DB_ID(N"tsql"), OBJECT_ID(N"dbo.NCI"), NULL, NULL, "DETAILNÍ");

-- Mazání indexů
POKUD EXISTS (VYBRAT název FROM sys.indexes
WHERE jméno = N"IX_1")
DROP INDEX IX_1 ON tsql.dbo.NCI;

POKUD EXISTS (VYBRAT název FROM sys.indexes
WHERE jméno = N"IX_2")
DROP INDEX IX_2 ON tsql.dbo.NCI;

V předchozím článku jsme představili způsoby optimalizace relačních databází a diskutovali o tom, jak fungují klastrované a neklastrované indexy v kontextu optimalizace doby provádění databázového dotazu. Nyní je čas uvést tyto znalosti do praxe tím, že se naučíte vytvářet optimalizační indexy pro databázi MS SQL.

Dovolte mi připomenout definici schématu tabulky Staffs, se kterou budeme pracovat:

Stůl pro zaměstnance

Řekněme, že potřebujeme vytvořit neklastrovaný index pro tabulku Staffs, který bude optimalizovat následující dotaz:

VYBERTE ID, jméno, práci Z Věcí, KDE MLA > 1000 A fotka NENÍ NULL

Indexovým klíčem budou sloupce SALARY a Photo, protože výběr je filtrován podle těchto polí. A sloupce Id, Name a Job budou sloupce zahrnuté v indexu.

Obecná syntaxe příkazu je následující:

POUŽITÍ JÍT

VYTVOŘTE NEZAHRNUTÝ INDEX NA (ASC – indexové klíčové sloupce)

ZAHRNOUT ( -- zahrnuté sloupce) GO

V našem případě bude žádost vypadat takto:

(Plat, Foto) ZAHRNUJTE (Id, Jméno, Práce) GO

Vytvořili jsme neshlukovaný index. Nebo spíše neseskupený krycí index. To znamená, že index obsahuje všechna pole nezbytná k provedení dotazu a SQL Server nebude při provádění dotazu přistupovat k základní tabulce.

Pokud by náš kód byl takto:

VYTVOŘIT NEZAHRNUTÝ INDEX IDX_StaffsSearch ON Stuffs

(Plat, Foto) VČETNĚ (Id) JÍT

V tomto případě index přestává být krycím indexem, protože nezahrnuje všechny sloupce použité v dotazu. Optimalizátor bude při provádění dotazu stále používat tento index, ale jeho účinnost se o řád sníží, protože bude vyžadovat přístup k základní tabulce.

Clusterový index se vytvoří pomocí následujícího příkazu:

VYTVOŘIT CLUSTERED INDEX IDX_Stsffsid ON Stuffs (ID)

Zde byl vytvořen jedinečný seskupený index založený na primárním klíči tabulky (sloupec Id).

Skutečný příklad

Pojďme si nyní vypracovat scénář, ve kterém můžeme reálně vyhodnotit míru nárůstu výkonu v případě použití indexů.

Vytvoříme novou databázi:

VYTVOŘIT DATABÁZI TestDB;

A jedna tabulka Zákazníci, která se bude skládat ze čtyř sloupců:

VYTVOŘIT TABULKU.(

NOT NULL, NULL, NULL, NULL) GO

Nyní naplníme naši tabulku náhodnými daty. Sloupec Id se bude ve smyčce zvětšovat a zbývající tři sloupce tabulky budou vyplněny náhodnými čísly pomocí zvláštní verze náhodné funkce:

DECLARE @i int = 0;

Zatímco já< 500000) BEGIN INSERT INTO Customers(Id, Num1, Num2, Num3) VALUES(

@i, abs(kontrolní součet(newid())), abs(kontrolní součet(newid())), abs(kontrolní součet(newid())) SET @i = @i + 1; KONEC

Tento skript přidá do tabulky půl milionu záznamů, takže buďte trpěliví, skript poběží minimálně 3 minuty.

Vše je připraveno ke zkoušce. Vyhodnotíme výkonnostní charakteristiky dotazu. Protože doba provádění dotazu může záviset na konkrétním počítači, budeme analyzovat nezávislejší ukazatel - počet logických čtení.

Chcete-li povolit režim shromažďování statistik, musíte spustit následující příkaz:

Nyní, po provedení každého požadavku, budeme mít na kartě Zprávy přístup ke statistikám o provedení tohoto požadavku, jak je uvedeno níže:

Zajímá nás pouze hodnota parametru logické čtení.

V naší tabulce tedy zatím nejsou žádné indexy. Spusťte následující tři dotazy a zaznamenejte počet logických čtení pro každý dotaz do tabulky výsledků níže:

1) VYBERTE Id, Num1, Num2 FROM Customers WHERE Id = 2000

2) VYBERTE Id, Num1, Num2 FROM Customers WHERE Id >= 0 AND Id< 1000

3) VYBERTE Id, Num1, Num2 FROM Customers WHERE Id >= 0 AND Id< 5000

Tyto dotazy vrátí 1 řádek, 1 000 řádků a 5 000 řádků. Bez indexů je ukazatel výkonu (počet logických čtení) pro všechny dotazy stejný a roven 1621. Zapišme data do tabulky výsledků:

Vidíme, že u druhého a třetího dotazu, kdy je vráceno poměrně velké množství řádků, index, který jsme vytvořili, nezlepšil výkon. U dotazu, který vrací jeden řádek, však bylo zrychlení obrovské. Můžeme tedy dojít k závěru, že při optimalizaci dotazů, které vracejí jediný výsledek, má smysl vytvářet nepokrývající indexy.

Nyní vytvoříme krycí index, čímž dosáhneme maximálního výkonu.

Nejprve smažeme předchozí index:

POUŽÍVEJTE TestDB GO DROP INDEX Customers.TestIndex1

A vytvoříme nový index:

VYTVOŘIT NENCLUSTEROVANÝ INDEX TestIndex2 ON dbo.Customers(Id) INCLUDE (Num1, Num2);

Nyní spusťte naše dotazy potřetí a zapište výsledky do tabulky:

Žádné indexy
Nekrycí index
Krycí index

Je snadné vidět, že nárůst výkonu byl enormní. Rychlost provádění dotazů jsme tak zvýšili desetinásobně. Při provozování databáze, která ukládá miliony řádků, bude tento nárůst výkonu docela patrný.

V tomto článku jsme se podívali na příklad optimalizace databáze vytvářením indexů. Stojí za zmínku, že vytváření indexů je čistě individuální proces pro každý požadavek. Chcete-li vytvořit index, který skutečně optimalizuje výkon dotazu, musíte pečlivě analyzovat samotný dotaz a plán jeho provádění.

Efektivní vytváření indexů je jedním z nejlepších způsobů, jak zlepšit výkon databázové aplikace. Bez použití indexů je SQL Server jako čtenář, který se snaží najít slovo v knize pohledem na každou stránku. Pokud má kniha věcný rejstřík (rejstřík), může čtenář vyhledat potřebné informace mnohem rychleji.

Pokud neexistuje index, SQL server při načítání dat z tabulky prohledá celou tabulku a zkontroluje každý řádek, zda jsou splněna kritéria dotazu. Takové úplné skenování může být katastrofální pro výkon celého systému, zvláště pokud je v tabulkách mnoho dat.

Jedním z nejdůležitějších úkolů při práci s databází je vytvoření optimálního indexu pro zlepšení výkonu systému. Většina velkých databází poskytuje nástroje pro zobrazení plánu provádění dotazů a pomáhá vám vyladit a optimalizovat indexy. Tento článek zdůrazňuje několik dobrých pravidel, která platí při vytváření nebo úpravách indexů v databázi. Nejprve se podívejme na situace, kdy indexování zlepšuje výkon a kde může indexování bolet.

Užitečné indexy

Indexování tabulek tedy bude užitečné při hledání konkrétního záznamu v tabulce pomocí příkazu Where. Mezi takové dotazy patří například dotazy, které hledají rozsah hodnot, dotazy, které odpovídají přesné hodnotě konkrétní hodnotě, a dotazy, které spojují dvě tabulky.

Například následující dotazy na databázi Northwind poběží efektivněji při vytváření indexu ve sloupci UnitPrice.

Odstranit z produktů, kde jednotková cena=1
Vyberte * z produktů, kde je jednotková cena mezi 14 A 16

Protože jsou položky indexu uloženy seřazené, je indexování také užitečné při vytváření dotazu pomocí klauzule Order by. Bez indexu se záznamy načítají a třídí, zatímco je dotaz spuštěn. Index založený na jednotkové ceně vám umožní jednoduše skenovat index a načíst řádky podle odkazu při zpracování dalšího požadavku. Pokud chcete řádky seřadit v sestupném pořadí, můžete jednoduše naskenovat index v opačném pořadí.

Vyberte * Z objednávky produktů podle jednotkové ceny ASC

Seskupení záznamu pomocí příkazu Group by také často vyžaduje řazení, takže vytvoření indexu ve sloupci UnitPrice bude také užitečné pro další dotaz, který počítá počet jednotek produktu za každou konkrétní cenu.

Vyberte počet (*), Jednotková cena ze skupiny produktů podle jednotkové ceny

Indexy jsou užitečné pro udržování jedinečné hodnoty pro sloupec, protože DBMS se může snadno podívat na index a zjistit, zda hodnota již existuje. Z tohoto důvodu jsou primární klíče vždy indexovány.

Nevýhody indexování

Indexy snižují výkon systému během změn záznamů. Kdykoli je proveden dotaz ke změně dat v tabulce, index se musí také změnit. Chcete-li vybrat optimální počet indexů, musíte otestovat databázi a sledovat její výkon. Statické systémy, kde se databáze používají především pro získávání dat, jako je vytváření sestav, mohou obsahovat více indexů pro podporu dotazů pouze pro čtení. Databáze s velkým počtem transakcí pro změnu dat budou potřebovat malý počet indexů, aby zajistily vyšší propustnost.

Indexy zabírají další místo na disku a v paměti RAM. Přesná velikost bude záviset na počtu záznamů v tabulce a také na počtu a velikosti sloupců v indexu. Ve většině případů to není hlavní problém, protože místo na disku je nyní snadné obětovat pro lepší výkon.

Vytvoření optimálního indexu

Jednoduchý index

Jednoduchý index je index, který používá hodnoty jednoho pole v tabulce. Použití jednoduchého indexu je výhodné ze dvou důvodů. Za prvé, provozování databáze velmi zatěžuje váš pevný disk. Velké indexové klíče přinutí databázi provádět více I/O operací, což omezuje výkon.

Zadruhé, protože prvky indexu jsou často zapojeny do porovnávání, lze snáze porovnávat menší indexy. Z těchto dvou důvodů je jeden celočíselný sloupec lepším indexem, protože je malý a snadno se srovnává. Znakové řetězce na druhé straně vyžadují porovnávání znaků po znaku a pozornost při manipulaci s parametry.

Selektivní index

Nejúčinnější indexy jsou ty s nízkým procentem duplicitních hodnot. Například telefonní seznam pro město, ve kterém má téměř každý příjmení Smith, nebude tak užitečný, pokud budou položky v něm seřazeny podle příjmení.

Index s vysokým procentem jedinečných hodnot se také nazývá selektivní index. Je zřejmé, že jedinečný index má největší selektivitu, protože neobsahuje duplicitní hodnoty. Mnoho DBMS může sledovat statistiky o každém indexu a dokáže rozpoznat, kolik neduplikovaných hodnot každý index obsahuje. Tato statistika se používá při generování plánu provádění dotazu.

Krycí indexy

Indexy se skládají ze sloupce dat, na kterém je postaven samotný index, a ukazatele na odpovídající řádek. Je to jako rejstřík knihy: obsahuje pouze klíčová slova a odkaz na stránku, na kterou můžete přejít pro další informace. DBMS obvykle sleduje ukazatele na řádek z indexu, aby shromáždil všechny informace potřebné pro dotaz. Pokud však index obsahuje všechny sloupce potřebné v dotazu, lze informace načíst bez přístupu k samotné tabulce.

Uvažujme index ve sloupci UnitPrice, který již byl zmíněn výše. DBMS může použít pouze položky indexu k provedení dalšího dotazu.

Vyberte Count(*), UnitPrice From Products Group by UnitPrice

Tento typ dotazu se nazývá krycí dotaz, protože všechny dotazované sloupce lze načíst z jednoho indexu. U nejdůležitějších dotazů můžete zvážit vytvoření krycího indexu pro nejlepší možný výkon. Takové indexy budou pravděpodobně složené (používají více než jeden sloupec), což je opak prvního principu: vytvářet jednoduché indexy. Je zřejmé, že výběr optimálního počtu sloupců v indexu lze posoudit pouze testováním a sledováním výkonu databáze v různých situacích.

Clusterový index

Mnoho databází má jeden speciální index v tabulce, kde jsou všechna data z řádku obsažena v indexu. Na serveru SQL Server se takový index nazývá seskupený index. Seskupený index lze přirovnat k telefonnímu seznamu, protože každý prvek indexu obsahuje všechny informace, které potřebujete, a neobsahuje odkazy na získání dalších dat.

Platí obecné pravidlo – každá netriviální tabulka musí mít seskupený index. Pokud je možné v tabulce vytvořit pouze jeden index, vytvořte jej shlukovaný. Na serveru SQL Server se při vytvoření primárního klíče automaticky vytvoří seskupený index (pokud jej již neobsahuje) pomocí sloupce primárního klíče jako indexovacího klíče. Clusterový index je nejúčinnějším indexem (pokud je použit, pokrývá celý dotaz) a v mnoha DBMS takový index pomáhá efektivně spravovat prostor požadovaný pro ukládání tabulek, protože jinak (bez vytváření seskupeného indexu) jsou řádky tabulek uloženy v neuspořádaná struktura, která se nazývá halda.

Při výběru sloupců pro seskupený index buďte opatrní. Pokud změníte záznam a změníte hodnotu sloupce v seskupeném indexu, databáze bude nucena znovu sestavit položky indexu (aby je zachovala v seřazeném pořadí). Pamatujte, že položky indexu pro seskupený index obsahují všechny hodnoty sloupců, takže změna hodnoty sloupce je srovnatelná s provedením příkazu Delete následovaného příkazem Insert, což při častém provádění zjevně způsobí problémy s výkonem. Z tohoto důvodu se seskupené indexy často skládají ze sloupce primárního klíče a cizího klíče. Pokud se klíčové hodnoty změní, změní se velmi zřídka.

Závěr

Určení správných indexů pro použití v databázi vyžaduje pečlivou analýzu a testování systému. Postupy uvedené v tomto článku jsou dobrými pravidly pro vytváření indexů. Po použití těchto metod budete muset znovu otestovat vaši konkrétní aplikaci za specifických podmínek hardwaru, paměti a operací.

Jeden z nejdůležitějších způsobů, jak dosáhnout vysoké produktivity SQL Server je použití indexů. Index urychluje proces dotazování tím, že poskytuje rychlý přístup k řádkům dat v tabulce, podobně jako rejstřík v knize vám pomáhá rychle najít informace, které potřebujete. V tomto článku podám stručný přehled indexů v SQL Server a vysvětlit, jak jsou organizovány v databázi a jak pomáhají urychlit databázové dotazy.

Indexy se vytvářejí ve sloupcích tabulky a zobrazení. Indexy poskytují způsob, jak rychle vyhledávat data na základě hodnot v těchto sloupcích. Pokud například vytvoříte index na primárním klíči a poté vyhledáte řádek dat pomocí hodnot primárního klíče, pak SQL Server nejprve najde hodnotu indexu a poté pomocí indexu rychle najde celý řádek dat. Bez indexu bude provedena úplná kontrola všech řádků v tabulce, což může mít významný dopad na výkon.
Můžete vytvořit index pro většinu sloupců v tabulce nebo pohledu. Výjimkou jsou především sloupce s datovými typy pro ukládání velkých objektů ( LOB), jako obraz, text nebo varchar(max). Můžete také vytvářet indexy pro sloupce určené k ukládání dat ve formátu XML, ale tyto indexy jsou strukturovány mírně odlišně než standardní a jejich zohlednění je nad rámec tohoto článku. Také článek nepojednává columnstore indexy. Místo toho se zaměřuji na ty indexy, které se v databázích používají nejčastěji SQL Server.
Index se skládá ze sady stránek, indexových uzlů, které jsou uspořádány do stromové struktury - vyrovnaný strom. Tato struktura je ve své podstatě hierarchická a začíná kořenovým uzlem na vrcholu hierarchie a listovými uzly, listy, dole, jak je znázorněno na obrázku:


Při dotazu na indexovaný sloupec se dotazovací stroj spustí v horní části kořenového uzlu a postupuje dolů přes mezilehlé uzly, přičemž každá mezivrstva obsahuje podrobnější informace o datech. Dotazovací stroj pokračuje v pohybu mezi uzly indexu, dokud nedosáhne spodní úrovně s listy indexu. Pokud například hledáte hodnotu 123 v indexovaném sloupci, dotazovací stroj nejprve určí stránku na první střední úrovni na kořenové úrovni. V tomto případě první stránka ukazuje na hodnotu od 1 do 100 a druhá od 101 do 200, takže dotazovací stroj přistoupí na druhou stránku této střední úrovně. Dále uvidíte, že byste se měli obrátit na třetí stránku další středně pokročilé úrovně. Odtud bude dotazovací subsystém číst hodnotu samotného indexu na nižší úrovni. Listy indexu mohou obsahovat buď samotná data tabulky, nebo jednoduše ukazatel na řádky s daty v tabulce, v závislosti na typu indexu: seskupený index nebo neshlukovaný index.

Seskupený index

Clusterový index ukládá skutečné řádky dat v listech indexu. Vrátíme-li se k předchozímu příkladu, znamená to, že řádek dat spojený s hodnotou klíče 123 bude uložen v samotném indexu. Důležitou charakteristikou seskupeného indexu je, že všechny hodnoty jsou seřazeny v určitém pořadí, buď vzestupně nebo sestupně. Tabulka nebo pohled tedy může mít pouze jeden seskupený index. Kromě toho je třeba poznamenat, že data v tabulce jsou uložena v seřazené podobě pouze v případě, že byl na této tabulce vytvořen seskupený index.
Tabulka, která nemá seskupený index, se nazývá halda.

Neshlukovaný index

Na rozdíl od seskupeného indexu obsahují listy neseskupeného indexu pouze tyto sloupce ( klíč), kterým je tento index určen, a obsahuje také ukazatel na řádky s reálnými daty v tabulce. To znamená, že systém poddotazů vyžaduje další operaci k vyhledání a načtení požadovaných dat. Obsah datového ukazatele závisí na tom, jak jsou data uložena: klastrovaná tabulka nebo halda. Pokud ukazatel ukazuje na seskupenou tabulku, ukazuje na seskupený index, který lze použít k nalezení skutečných dat. Pokud ukazatel odkazuje na haldu, pak ukazuje na konkrétní identifikátor řádku dat. Neklastrované indexy nelze třídit jako klastrované indexy, ale můžete vytvořit více než jeden neclusterovaný index v tabulce nebo pohledu až do 999. To neznamená, že byste měli vytvořit co nejvíce indexů. Indexy mohou zlepšit nebo snížit výkon systému. Kromě toho, že můžete vytvořit více indexů bez klastrů, můžete také zahrnout další sloupce ( zahrnutý sloupec) do vašeho indexu: listy indexu budou ukládat nejen hodnotu samotných indexovaných sloupců, ale také hodnoty těchto neindexovaných dalších sloupců. Tento přístup vám umožní obejít některá omezení kladená na index. Můžete například zahrnout neindexovatelný sloupec nebo obejít limit délky indexu (ve většině případů 900 bajtů).

Typy indexů

Kromě toho, že je buď klastrovaný, nebo neklastrovaný, lze index dále konfigurovat jako složený index, jedinečný index nebo krycí index.

Složený index

Takový index může obsahovat více než jeden sloupec. Do indexu můžete zahrnout až 16 sloupců, ale jejich celková délka je omezena na 900 bajtů. Seskupené i neshlukované indexy mohou být složené.

Unikátní index

Tento index zajišťuje, že každá hodnota v indexovaném sloupci je jedinečná. Pokud je index složený, pak se jedinečnost vztahuje na všechny sloupce v indexu, ale ne na každý jednotlivý sloupec. Pokud například vytvoříte jedinečný index na sloupcích NÁZEV A PŘÍJMENÍ, pak musí být celé jméno jedinečné, ale duplikáty jména nebo příjmení jsou možné.
Jedinečný index se automaticky vytvoří, když definujete omezení sloupce: primární klíč nebo omezení jedinečné hodnoty:

• Primární klíč
  Když definujete omezení primárního klíče na jeden nebo více sloupců, pak SQL Server automaticky vytvoří jedinečný seskupený index, pokud nebyl dříve vytvořen seskupený index (v tomto případě je na primárním klíči vytvořen jedinečný neshlukovaný index)
• Jedinečnost hodnot
  Když definujete omezení jedinečnosti hodnot, pak SQL Server automaticky vytvoří jedinečný index bez klastrů. Můžete určit, že se vytvoří jedinečný seskupený index, pokud v tabulce ještě nebyl vytvořen žádný seskupený index

Krycí index

Takový index umožňuje konkrétnímu dotazu okamžitě získat všechna potřebná data z listů indexu bez dalšího přístupu k záznamům samotné tabulky.

Navrhování indexů

Jakkoli mohou být indexy užitečné, musí být navrženy pečlivě. Protože indexy mohou zabírat značné místo na disku, nechcete vytvářet více indexů, než je nutné. Indexy se navíc automaticky aktualizují při aktualizaci samotného datového řádku, což může vést k další režii prostředků a snížení výkonu. Při navrhování indexů je třeba vzít v úvahu několik aspektů týkajících se databáze a dotazů proti ní.

Databáze

Jak bylo uvedeno dříve, indexy mohou zlepšit výkon systému, protože poskytují dotazovacímu stroji rychlý způsob, jak najít data. Měli byste však také vzít v úvahu, jak často máte v úmyslu vkládat, aktualizovat nebo mazat data. Když změníte data, musí se změnit také indexy, aby odrážely odpovídající akce s daty, což může výrazně snížit výkon systému. Při plánování strategie indexování zvažte následující pokyny:

• U tabulek, které jsou často aktualizovány, používejte co nejméně indexů.
• Pokud tabulka obsahuje velké množství dat, ale změny jsou malé, použijte tolik indexů, kolik je potřeba ke zlepšení výkonu vašich dotazů. Před použitím indexů na malých tabulkách si však dobře rozmyslete, protože... Je možné, že použití indexového vyhledávání může trvat déle než pouhé skenování všech řádků.
• U seskupených indexů se snažte udržovat pole co nejkratší. Nejlepší přístup je použít seskupený index pro sloupce, které mají jedinečné hodnoty a neumožňují NULL. To je důvod, proč se primární klíč často používá jako seskupený index.
• Jedinečnost hodnot ve sloupci ovlivňuje výkon indexu. Obecně platí, že čím více duplikátů ve sloupci máte, tím horší je výkon indexu. Na druhou stranu, čím více jedinečných hodnot existuje, tím lepší je výkon indexu. Kdykoli je to možné, používejte jedinečný index.
• U složeného indexu vezměte v úvahu pořadí sloupců v indexu. Sloupce, které se používají ve výrazech KDE(Například, KDE Křestní jméno = "Charlie") musí být první v indexu. Následující sloupce by měly být uvedeny na základě jedinečnosti jejich hodnot (sloupce s nejvyšším počtem jedinečných hodnot jsou na prvním místě).
• Můžete také zadat index pro počítané sloupce, pokud splňují určité požadavky. Například výrazy použité k získání hodnoty sloupce musí být deterministické (vždy vracejí stejný výsledek pro danou sadu vstupních parametrů).

Databázové dotazy

Dalším aspektem při navrhování indexů je to, jaké dotazy jsou spouštěny proti databázi. Jak bylo uvedeno dříve, musíte zvážit, jak často se data mění. Kromě toho by měly být použity následující zásady:

• Pokuste se vložit nebo upravit co nejvíce řádků v jednom dotazu, nikoli v několika jednotlivých dotazech.
• Vytvořte index bez klastrů pro sloupce, které se často používají jako vyhledávací termíny ve vašich dotazech. KDE a připojení v PŘIPOJIT.
• Zvažte indexování sloupců používaných v dotazech pro vyhledávání řádků pro přesné shody hodnot.

A teď vlastně:

14 otázek o indexech v SQL Server, na které jste se styděli zeptat

Proč nemůže mít tabulka dva seskupené indexy?

Chcete krátkou odpověď? Sdružený index je tabulka. Když vytvoříte seskupený index v tabulce, úložný modul seřadí všechny řádky v tabulce ve vzestupném nebo sestupném pořadí podle definice indexu. Klastrovaný index není samostatnou entitou jako jiné indexy, ale mechanismem pro řazení dat v tabulce a usnadňující rychlý přístup k datovým řádkům.
Představme si, že máte tabulku obsahující historii prodejních transakcí. Tabulka Prodej obsahuje informace, jako je ID objednávky, pozice produktu v objednávce, číslo produktu, množství produktu, číslo a datum objednávky atd. Vytvoříte seskupený index na sloupcích Číslo objednávky A LineID, seřazené vzestupně, jak je uvedeno níže T-SQL kód:
VYTVOŘIT JEDINEČNÝ KLUSTEROVÝ INDEX ix_oriderid_lineid ON dbo.Sales(ID objednávky, ID řádku);
Když spustíte tento skript, všechny řádky v tabulce budou fyzicky seřazeny nejprve podle sloupce OrderID a poté podle LineID, ale samotná data zůstanou v jediném logickém bloku, tabulce. Z tohoto důvodu nemůžete vytvořit dva seskupené indexy. Může existovat pouze jedna tabulka s jedním údajem a tuto tabulku lze seřadit pouze jednou v určitém pořadí.

Pokud seskupená tabulka poskytuje mnoho výhod, tak proč používat haldu?

Máš pravdu. Seskupené tabulky jsou skvělé a většina vašich dotazů bude fungovat lépe v tabulkách, které mají seskupený index. Ale v některých případech můžete chtít ponechat stoly v jejich přirozeném, nedotčeném stavu, tj. ve formě haldy a vytvořte pouze indexy bez klastrů, aby vaše dotazy zůstaly spuštěné.
Halda, jak si pamatujete, ukládá data v náhodném pořadí. Úložný subsystém obvykle přidává data do tabulky v pořadí, ve kterém jsou vložena, ale úložný subsystém také rád přesouvá řádky, aby bylo úložiště efektivnější. V důsledku toho nemáte šanci předvídat, v jakém pořadí budou data uložena.
Pokud dotazovací stroj potřebuje najít data bez výhody neshlukovaného indexu, provede úplné prohledání tabulky, aby našel řádky, které potřebuje. Na velmi malých stolech to obvykle není problém, ale jak se halda zvětšuje, výkon rychle klesá. Neklastrovaný index samozřejmě může pomoci pomocí ukazatele na soubor, stránku a řádek, kde jsou uložena požadovaná data – to je obvykle mnohem lepší alternativa ke skenování tabulky. I tak je obtížné porovnávat výhody seskupeného indexu při zvažování výkonu dotazů.
Halda však může pomoci zlepšit výkon v určitých situacích. Vezměme si tabulku s velkým množstvím vložení, ale málo aktualizací nebo odstranění. Například tabulka uchovávající protokol se primárně používá k vkládání hodnot, dokud není archivována. Na haldě neuvidíte stránkování a fragmentaci dat jako u seskupeného indexu, protože řádky jsou jednoduše přidány na konec haldy. Přílišné rozdělení stránek může mít významný dopad na výkon, a to ne v dobrém slova smyslu. Obecně platí, že halda umožňuje vkládat data relativně bezbolestně a nebudete muset řešit režii úložiště a údržby, kterou byste museli řešit s klastrovaným indexem.
Nedostatek aktualizace a mazání dat by však neměl být považován za jediný důvod. Důležitým faktorem je také způsob vzorkování dat. Například byste neměli používat haldu, pokud často dotazujete na rozsahy dat nebo data, na která se dotazujete, často potřebují seřadit nebo seskupit.
To vše znamená, že byste měli zvážit použití haldy pouze v případě, že pracujete s velmi malými tabulkami nebo když je veškerá vaše interakce s tabulkou omezena na vkládání dat a vaše dotazy jsou extrémně jednoduché (a používáte neshlukované indexy tak jako tak). Jinak se držte dobře navrženého seskupeného indexu, například indexu definovaného na jednoduchém vzestupném klíčovém poli, jako je široce používaný sloupec s IDENTITA.

Jak změním výchozí faktor plnění indexu?

Změna výchozího faktoru plnění indexu je jedna věc. Pochopení toho, jak výchozí poměr funguje, je druhá věc. Nejprve ale udělejte pár kroků zpět. Faktor naplnění indexu určuje množství místa na stránce pro uložení indexu na spodní úrovni (úroveň listu) před zahájením plnění nové stránky. Pokud je například koeficient nastaven na 90, pak při růstu indexu zabere 90 % stránky a poté se přesune na další stránku.
Ve výchozím nastavení je hodnota faktoru plnění indexu in SQL Server je 0, což je stejné jako 100. V důsledku toho všechny nové indexy automaticky zdědí toto nastavení, pokud v kódu konkrétně nezadáte hodnotu, která se liší od systémové standardní hodnoty nebo nezměníte výchozí chování. Můžeš použít SQL Server Management Studio upravit výchozí hodnotu nebo spustit systémovou uloženou proceduru sp_configure. Například následující sada T-SQL příkazy nastaví hodnotu koeficientu na 90 (nejprve se musíte přepnout do režimu pokročilého nastavení):
EXEC sp_configure "zobrazit pokročilé možnosti", 1; PŘEJÍT PŘEKONFIGUROVAT; GO EXEC sp_configure "faktor plnění", 90; PŘEJÍT PŘEKONFIGUROVAT; JÍT
Po změně hodnoty faktoru plnění indexu je třeba restartovat službu SQL Server. Nyní můžete zkontrolovat nastavenou hodnotu spuštěním sp_configure bez zadaného druhého argumentu:
EXEC sp_configure "faktor plnění" GO
Tento příkaz by měl vrátit hodnotu 90. V důsledku toho budou všechny nově vytvořené indexy používat tuto hodnotu. Můžete to otestovat vytvořením indexu a dotazem na hodnotu faktoru plnění:
POUŽÍVEJTE AdventureWorks2012; -- vaše databáze GO CREATE NENCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName); GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
V tomto příkladu jsme vytvořili neklastrovaný index na tabulce Osoba v databázi AdventureWorks 2012. Po vytvoření indexu můžeme získat hodnotu faktoru plnění ze systémových tabulek sys.indexes. Dotaz by měl vrátit 90.
Představme si však, že jsme index odstranili a vytvořili znovu, ale nyní jsme zadali konkrétní hodnotu faktoru plnění:
CREATE NENCLUSTERED INDEX ix_people_lastname ON Person.Person(LastName) WITH (fillfactor=80); GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
Tentokrát jsme přidali návod S a možnost fillfactor pro naši operaci vytváření indexu VYTVOŘIT INDEX a specifikoval hodnotu 80. Operátor VYBRAT nyní vrací odpovídající hodnotu.
Doposud bylo vše docela jednoduché. V celém tomto procesu se můžete opravdu spálit, když vytvoříte index, který používá výchozí hodnotu koeficientu, za předpokladu, že tuto hodnotu znáte. Někdo si například pohrává s nastavením serveru a je tak tvrdohlavý, že nastavil faktor plnění indexu na 20. Mezitím pokračujete ve vytváření indexů za předpokladu, že výchozí hodnota je 0. Bohužel nemáte způsob, jak zjistit naplnění faktor, dokud nevytvoříte index a pak nezkontrolujete hodnotu, jako jsme to udělali v našich příkladech. V opačném případě budete muset počkat na okamžik, kdy výkon dotazu klesne natolik, že začnete něco tušit.
Dalším problémem, o kterém byste měli vědět, je opětovné sestavení indexů. Stejně jako při vytváření indexu můžete určit hodnotu faktoru plnění indexu při jeho opětovném sestavení. Na rozdíl od příkazu create index však rebuild nepoužívá výchozí nastavení serveru, i když se to může zdát. Ještě více, pokud konkrétně neuvedete hodnotu faktoru plnění indexu, pak SQL Server použije hodnotu koeficientu, se kterým tento index existoval před svou restrukturalizací. Například následující operace ALTER INDEX znovu sestaví index, který jsme právě vytvořili:
ALTER INDEX ix_people_lastname ON Person.Person REBUILD; GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_lastname";
Když zkontrolujeme hodnotu faktoru plnění, dostaneme hodnotu 80, protože to je to, co jsme zadali při posledním vytváření indexu. Výchozí hodnota je ignorována.
Jak vidíte, změna hodnoty faktoru plnění indexu není tak obtížná. Je mnohem obtížnější znát aktuální hodnotu a pochopit, kdy je aplikována. Pokud při vytváření a přestavbě indexů vždy konkrétně specifikujete koeficient, pak vždy znáte konkrétní výsledek. Pokud se nebudete muset starat o to, aby někdo jiný znovu nepokazil nastavení serveru, což by způsobilo přestavbu všech indexů se směšně nízkým faktorem plnění indexu.

Je možné vytvořit seskupený index na sloupci, který obsahuje duplikáty?

Ano i ne. Ano, můžete vytvořit seskupený index na klíčovém sloupci, který obsahuje duplicitní hodnoty. Ne, hodnota klíčového sloupce nemůže zůstat v nejedinečném stavu. Nech mě to vysvětlit. Pokud vytvoříte nejedinečný seskupený index ve sloupci, modul úložiště přidá k duplicitní hodnotě uniquifier, aby byla zajištěna jedinečnost, a proto bylo možné identifikovat každý řádek v seskupené tabulce.
Můžete se například rozhodnout vytvořit seskupený index ve sloupci obsahujícím zákaznická data Příjmení ponechání příjmení. Sloupec obsahuje hodnoty Franklin, Hancock, Washington a Smith. Poté znovu vložíte hodnoty Adams, Hancock, Smith a Smith. Hodnota klíčového sloupce však musí být jedinečná, takže modul úložiště změní hodnotu duplikátů tak, aby vypadaly asi takto: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 a Smith5678.
Na první pohled se tento přístup zdá v pořádku, ale celočíselná hodnota zvětšuje velikost klíče, což může být problém, pokud existuje velký počet duplikátů, a tyto hodnoty se stanou základem neshlukovaného indexu nebo cizího klíčová reference. Z těchto důvodů byste se měli vždy snažit vytvořit jedinečné seskupené indexy, kdykoli je to možné. Pokud to není možné, zkuste alespoň použít sloupce s velmi vysokým obsahem unikátní hodnoty.

Jak je tabulka uložena, pokud nebyl vytvořen seskupený index?

SQL Server podporuje dva typy tabulek: seskupené tabulky, které mají seskupený index a tabulky haldy nebo jen haldy. Na rozdíl od seskupených tabulek nejsou data na haldě nijak řazena. V podstatě se jedná o hromadu (hromadu) dat. Pokud do takové tabulky přidáte řádek, úložiště jej jednoduše připojí na konec stránky. Když je stránka naplněna daty, bude přidána na novou stránku. Ve většině případů budete chtít vytvořit seskupený index na tabulce, abyste využili možnosti řazení a rychlosti dotazů (zkuste si představit, že hledáte telefonní číslo v netříděném adresáři). Pokud se však rozhodnete nevytvářet seskupený index, můžete na haldě vytvořit i neklastrovaný index. V tomto případě bude mít každý řádek indexu ukazatel na řádek haldy. Index obsahuje ID souboru, číslo stránky a číslo datového řádku.

Jaký je vztah mezi omezeními jedinečnosti hodnoty a primárním klíčem s indexy tabulek?

Primární klíč a jedinečné omezení zajišťují, že hodnoty ve sloupci jsou jedinečné. Pro tabulku můžete vytvořit pouze jeden primární klíč a nemůže obsahovat hodnoty NULA. Můžete vytvořit několik omezení jedinečnosti hodnoty pro tabulku a každé z nich může mít jeden záznam NULA.
Když vytvoříte primární klíč, modul úložiště také vytvoří jedinečný seskupený index, pokud ještě nebyl vytvořen seskupený index. Výchozí chování však můžete přepsat a vytvoří se index bez klastrů. Pokud při vytváření primárního klíče existuje seskupený index, bude vytvořen jedinečný neshlukovaný index.
Když vytvoříte jedinečné omezení, modul úložiště vytvoří jedinečný index bez klastrů. Můžete však určit vytvoření jedinečného seskupeného indexu, pokud nebyl vytvořen dříve.
Obecně platí, že omezení jedinečné hodnoty a jedinečný index jsou totéž.

Proč se klastrované a neklastrované indexy na serveru SQL Server nazývají B-strom?

Základní indexy v SQL Server, klastrované nebo neklastrované, jsou distribuovány mezi sady stránek nazývané indexové uzly. Tyto stránky jsou organizovány ve specifické hierarchii se stromovou strukturou nazývanou vyvážený strom. Na horní úrovni je kořenový uzel, dole jsou listové uzly, s mezilehlými uzly mezi horní a spodní úrovní, jak je znázorněno na obrázku:


Kořenový uzel poskytuje hlavní vstupní bod pro dotazy pokoušející se načíst data prostřednictvím indexu. Počínaje tímto uzlem zahájí dotazovací stroj navigaci dolů v hierarchické struktuře k příslušnému koncovému uzlu obsahujícímu data.
Představte si například, že byl přijat požadavek na výběr řádků obsahujících hodnotu klíče 82. Dotazový subsystém začne pracovat od kořenového uzlu, který odkazuje na vhodný mezilehlý uzel, v našem případě 1-100. Z mezilehlého uzlu 1-100 je přechod do uzlu 51-100 a odtud do koncového uzlu 76-100. Pokud se jedná o seskupený index, pak list uzlu obsahuje data řádku spojeného s klíčem rovným 82. Pokud se jedná o index bez seskupení, pak list indexu obsahuje ukazatel na seskupenou tabulku nebo konkrétní řádek v hromada.

Jak může index dokonce zlepšit výkon dotazů, když musíte procházet všechny tyto indexové uzly?

Za prvé, indexy ne vždy zlepšují výkon. Příliš mnoho nesprávně vytvořených indexů mění systém v bažinu a snižuje výkon dotazů. Je přesnější říci, že pokud jsou indexy pečlivě aplikovány, mohou poskytnout významné zvýšení výkonu.
Vzpomeňte si na obrovskou knihu věnovanou ladění výkonu SQL Server(papírová verze, nikoli elektronická verze). Představte si, že chcete najít informace o konfiguraci Resource Governor. Můžete táhnout prstem stránku po stránce přes celou knihu nebo otevřít obsah a zjistit přesné číslo stránky s hledanou informací (za předpokladu, že je kniha správně indexována a obsah má správné rejstříky). To vám jistě ušetří významný čas, i když musíte nejprve vstoupit do úplně jiné struktury (indexu), abyste získali potřebné informace z primární struktury (knihy).
Jako knižní rejstřík, rejstřík v SQL Server umožňuje spouštět přesné dotazy na data, která potřebujete, místo úplného skenování všech dat obsažených v tabulce. U malých tabulek není úplné prohledání obvykle problémem, ale velké tabulky zabírají mnoho stránek dat, což může mít za následek značnou dobu provádění dotazu, pokud neexistuje index, který by dotazovacímu stroji umožnil okamžitě získat správné umístění dat. Představte si, že se ztratíte na víceúrovňové křižovatce před velkou metropolí bez mapy a dostanete nápad.

Když jsou indexy tak skvělé, proč nevytvořit jeden pro každý sloupec?

Žádný dobrý skutek by neměl zůstat nepotrestán. Alespoň u indexů tomu tak je. Indexy samozřejmě fungují skvěle, pokud spouštíte dotazy operátora načítání VYBRAT, ale jakmile začnou časté hovory operátorům VLOŽIT, AKTUALIZACE A VYMAZAT, takže krajina se velmi rychle mění.
Když zahájíte požadavek na data ze strany operátora VYBRAT, dotazovací stroj najde index, projde jeho stromovou strukturou a objeví data, která hledá. Co by mohlo být jednodušší? Ale věci se změní, pokud iniciujete prohlášení o změně jako AKTUALIZACE. Ano, pro první část příkazu může dotazovací stroj opět použít index k vyhledání upravovaného řádku – to je dobrá zpráva. A pokud dojde k jednoduché změně dat v řádku, která neovlivní změny v klíčových sloupcích, pak bude proces změny zcela bezbolestný. Ale co když změna způsobí rozdělení stránek obsahujících data nebo se změní hodnota klíčového sloupce a způsobí jeho přesunutí do jiného indexového uzlu - to bude mít za následek, že index bude pravděpodobně potřebovat reorganizaci ovlivňující všechny přidružené indexy a operace což má za následek rozsáhlý pokles produktivity.
K podobným procesům dochází při volání operátora VYMAZAT. Index může pomoci najít odstraňovaná data, ale odstranění samotných dat může vést k přeskupení stránek. Ohledně operátora VLOŽIT, úhlavní nepřítel všech indexů: začnete přidávat velké množství dat, což vede ke změnám indexů a jejich reorganizaci a všichni trpí.
Při přemýšlení o tom, jaký typ indexů a kolik jich vytvořit, tedy zvažte typy dotazů do databáze. Více neznamená lépe. Před přidáním nového indexu do tabulky zvažte náklady nejen na základní dotazy, ale také na množství spotřebovaného místa na disku, náklady na údržbu funkčnosti a indexů, což může vést k dominovému efektu na další operace. Vaše strategie návrhu indexu je jedním z nejdůležitějších aspektů vaší implementace a měla by zahrnovat mnoho aspektů, od velikosti indexu, počtu jedinečných hodnot až po typ dotazů, které bude index podporovat.

Je nutné vytvořit seskupený index na sloupci s primárním klíčem?

Seskupený index můžete vytvořit pro libovolný sloupec, který splňuje požadované podmínky. Je pravda, že seskupený index a omezení primárního klíče jsou vytvořeny jedna pro druhou a jde o shodu vytvořenou v nebi, takže pochopte skutečnost, že když vytvoříte primární klíč, automaticky se vytvoří seskupený index, pokud nebyl vytvořen. vytvořené dříve. Můžete se však rozhodnout, že seskupený index bude fungovat lépe jinde a vaše rozhodnutí bude často oprávněné.
Hlavním účelem seskupeného indexu je seřadit všechny řádky v tabulce na základě klíčového sloupce zadaného při definování indexu. To poskytuje rychlé vyhledávání a snadný přístup k datům tabulky.
Primární klíč tabulky může být dobrou volbou, protože jedinečně identifikuje každý řádek v tabulkách bez nutnosti přidávat další data. V některých případech bude nejlepší volbou náhradní primární klíč, který je nejen jedinečný, ale také má malou velikost a jehož hodnoty se postupně zvyšují, takže neshlukované indexy založené na této hodnotě jsou efektivnější. Optimalizátor dotazů má také rád tuto kombinaci seskupeného indexu a primárního klíče, protože spojování tabulek je rychlejší než spojování jiným způsobem, který nepoužívá primární klíč a jeho přidružený seskupený index. Jak jsem řekl, je to zápas vyrobený v nebi.
Nakonec je však vhodné poznamenat, že při vytváření seskupeného indexu je třeba zvážit několik aspektů: kolik indexů bez klastrů na něm bude založeno, jak často se bude měnit hodnota sloupce indexu klíče a jak velká. Když se hodnoty ve sloupcích seskupeného indexu změní nebo index nefunguje podle očekávání, mohou být ovlivněny všechny ostatní indexy v tabulce. Seskupený index by měl být založen na nejtrvalejším sloupci, jehož hodnoty se zvyšují v určitém pořadí, ale nemění se náhodným způsobem. Index musí podporovat dotazy na nejčastěji používaná data tabulky, takže dotazy plně využívají toho, že data jsou tříděna a přístupná v kořenových uzlech, listech indexu. Pokud primární klíč vyhovuje tomuto scénáři, použijte jej. Pokud ne, vyberte jinou sadu sloupců.

Co když indexujete pohled, je to stále pohled?

Pohled je virtuální tabulka, která generuje data z jedné nebo více tabulek. V podstatě se jedná o pojmenovaný dotaz, který při dotazu na tento pohled načítá data z podkladových tabulek. Výkon dotazů můžete zlepšit vytvořením seskupeného indexu a neklastrovaných indexů v tomto zobrazení, podobně jako vytváření indexů v tabulce, ale hlavní upozornění spočívá v tom, že nejprve vytvoříte seskupený index a poté můžete vytvořit neklastrovaný.
Když je vytvořen indexovaný pohled (materializovaný pohled), pak samotná definice pohledu zůstává samostatnou entitou. Toto je koneckonců jen pevně zakódovaný operátor VYBRAT, uložený v databázi. Ale index je úplně jiný příběh. Když vytvoříte klastrovaný nebo neklastrovaný index na poskytovateli, data se fyzicky uloží na disk, stejně jako běžný index. Kromě toho, když se změní data v podkladových tabulkách, index zobrazení se automaticky změní (to znamená, že se možná budete chtít vyhnout indexování zobrazení v tabulkách, které se často mění). Pohled každopádně zůstává pohledem – pohledem na tabulky, ale aktuálně spuštěným, s indexy, které mu odpovídají.
Než budete moci vytvořit index v pohledu, musí splňovat několik podmínek. Pohled může například odkazovat pouze na základní tabulky, ale nikoli na jiné pohledy, a tyto tabulky musí být ve stejné databázi. Ve skutečnosti existuje mnoho dalších omezení, takže se nezapomeňte podívat na dokumentaci SQL Server pro všechny špinavé detaily.

Proč používat krycí index místo složeného indexu?

Nejprve se ujistěte, že rozumíme rozdílu mezi těmito dvěma. Složený index je jednoduše běžný index, který obsahuje více než jeden sloupec. Více sloupců klíče lze použít k zajištění toho, že každý řádek v tabulce je jedinečný, nebo můžete mít více sloupců, abyste zajistili, že primární klíč je jedinečný, nebo se můžete snažit optimalizovat provádění často vyvolávaných dotazů na více sloupců. Obecně však platí, že čím více klíčových sloupců index obsahuje, tím méně efektivní bude index, což znamená, že složené indexy by měly být používány uvážlivě.
Jak již bylo řečeno, dotazu může velmi prospět, pokud jsou všechna požadovaná data okamžitě umístěna na listech indexu, stejně jako index samotný. To není problém pro seskupený index, protože všechna data tam již jsou (proto je tak důležité pečlivě přemýšlet, když vytváříte seskupený index). Ale neklastrovaný index na listech obsahuje pouze klíčové sloupce. Chcete-li získat přístup ke všem ostatním datům, optimalizátor dotazů vyžaduje další kroky, které mohou zvýšit značnou režii při provádění vašich dotazů.
Zde přichází na pomoc krycí index. Když definujete index bez klastrů, můžete do klíčových sloupců zadat další sloupce. Řekněme například, že vaše aplikace často dotazuje data sloupců Číslo objednávky A Datum objednávky ve stole Odbyt:
SELECT OrderID, OrderDate FROM Sales WHERE OrderID = 12345;
V obou sloupcích můžete vytvořit složený index bez klastrů, ale sloupec DatumObjednávky pouze přidá režii údržby indexu, aniž by sloužil jako zvláště užitečný klíčový sloupec. Nejlepším řešením by bylo vytvořit krycí index na klíčovém sloupci Číslo objednávky a navíc zahrnutý sloupec Datum objednávky:
CREATE NENCLUSTERED INDEX ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate);
Tím se vyhnete nevýhodám indexování redundantních sloupců při zachování výhod ukládání dat do listů při spouštění dotazů. Zahrnutý sloupec není součástí klíče, ale data jsou uložena na listovém uzlu, indexovém listu. To může zlepšit výkon dotazů bez jakékoli další režie. Kromě toho se na sloupce zahrnuté v krycím indexu vztahuje méně omezení než na klíčové sloupce indexu.

Záleží na počtu duplikátů v klíčovém sloupci?

Při vytváření indexu se musíte pokusit snížit počet duplikátů ve vašich klíčových sloupcích. Nebo přesněji: snažte se držet opakovací frekvenci co nejnižší.
Pokud pracujete se složeným indexem, pak se duplikace vztahuje na všechny klíčové sloupce jako celek. Jeden sloupec může obsahovat mnoho duplicitních hodnot, ale mezi všemi sloupci indexu by mělo být minimální opakování. Například vytvoříte složený neshlukovaný index na sloupcích Jméno A Příjmení, můžete mít mnoho hodnot John Doe a mnoho hodnot Doe, ale chcete mít co nejméně hodnot John Doe, nebo nejlépe jen jednu hodnotu John Doe.
Poměr jedinečnosti hodnot klíčového sloupce se nazývá indexová selektivita. Čím více jedinečných hodnot existuje, tím vyšší je selektivita: jedinečný index má největší možnou selektivitu. Dotazovací stroj má opravdu rád sloupce s vysokými hodnotami selektivity, zvláště pokud jsou tyto sloupce zahrnuty v klauzulích WHERE vašich nejčastěji prováděných dotazů. Čím selektivnější je index, tím rychleji může dotazovací stroj zmenšit velikost výsledné datové sady. Nevýhodou samozřejmě je, že sloupce s relativně malým počtem jedinečných hodnot budou jen zřídka vhodnými kandidáty na indexování.

Je možné vytvořit index bez klastrů pouze pro konkrétní podmnožinu dat klíčového sloupce?

Ve výchozím nastavení obsahuje index bez klastrů jeden řádek pro každý řádek v tabulce. Samozřejmě můžete totéž říci o seskupeném indexu, za předpokladu, že takový index je tabulka. Ale pokud jde o neseskupený index, vztah jedna ku jedné je důležitým konceptem, protože počínaje verzí SQL Server 2008, máte možnost vytvořit filtrovatelný index, který omezí počet řádků v něm obsažených. Filtrovaný index může zlepšit výkon dotazů, protože... je menší velikosti a obsahuje filtrované, přesnější statistiky než všechny tabulkové - to vede k vytvoření vylepšených realizačních plánů. Filtrovaný index také vyžaduje méně úložného prostoru a nižší náklady na údržbu. Index se aktualizuje pouze tehdy, když se změní data odpovídající filtru.
Navíc lze snadno vytvořit filtrovatelný index. V operátorovi VYTVOŘIT INDEX stačí jen uvést KDE stav filtru. Můžete například odfiltrovat všechny řádky obsahující NULL z indexu, jak je znázorněno v kódu:
CREATE NENCLUSTERED INDEX ix_trackingnumber ON Sales.SalesOrderDetail(CarrierTrackingNumber) WHERE CarrierTrackingNumber NENÍ NULL;
Ve skutečnosti můžeme odfiltrovat všechna data, která nejsou důležitá v kritických dotazech. Ale buďte opatrní, protože... SQL Server ukládá několik omezení na filtrovatelné indexy, jako je například nemožnost vytvořit filtrovatelný index na pohledu, proto si pečlivě přečtěte dokumentaci.
Může se také stát, že podobných výsledků dosáhnete vytvořením indexovaného zobrazení. Filtrovaný index má však několik výhod, jako je schopnost snížit náklady na údržbu a zlepšit kvalitu vašich plánů provádění. Filtrované indexy lze také znovu vytvořit online. Zkuste to s indexovaným zobrazením.

A zase něco málo od překladatele

Účelem objevení se tohoto překladu na stránkách Habrahabru bylo sdělit nebo připomenout blog SimpleTalk z r. RedGate.
Publikuje mnoho zábavných a zajímavých příspěvků.
Nejsem spojen s produkty žádné společnosti RedGate ani s jejich prodejem.

Jak jsem slíbil, knihy pro ty, kteří chtějí vědět víc
Doporučuji za sebe tři velmi dobré knihy (odkazy vedou na roznítit verze v obchodě Amazonka):

V zásadě můžete otevřít jednoduché indexy

pro začátečníky

index

Přidat štítky

Microsoft SQL Server 2012 T-SQL Fundamentals (Reference pro vývojáře) Autor Itzik Ben-Gan Datum zveřejnění: 15. července 2012 Autor, mistr svého řemesla, poskytuje základní znalosti o práci s databázemi. Pokud jste vše zapomněli nebo jste nikdy nevěděli, rozhodně stojí za přečtení.

ROWID indexy jsou databázové objekty, které poskytují zobrazení všech hodnot ve sloupci tabulky a také ROWID všech řádků v tabulce, které obsahují hodnoty sloupce.

ROWID je pseudosloupec, který je jedinečným identifikátorem pro řádek v tabulce a ve skutečnosti popisuje přesné fyzické umístění tohoto konkrétního řádku. Na základě těchto informací Věštec může následně najít data spojená s řádkem tabulky. Při každém přesunutí, exportu, importu nebo jakékoli jiné operaci, která změní jeho umístění, se zobrazí ROWID linii, protože zaujímá jinou fyzickou polohu. Pro ukládání dat ROWID Je vyžadováno 80 bitů (10 bajtů). Identifikátory ROWID sestávají ze čtyř složek: číslo objektu (32 bitů), relativní číslo souboru (10 bitů), číslo bloku (22 bitů) a číslo řádku (16 bitů). Tyto identifikátory jsou zobrazeny jako 18znakové sekvence označující umístění dat v databázi, přičemž každý znak je reprezentován ve formátu base-64 sestávající ze znaků A-Z, a-z, 0-9, + a /. Prvních šest znaků je číslo datového objektu, další tři jsou relativní číslo souboru, dalších šest je číslo bloku a poslední tři jsou číslo řádku.

Příklad:

SELECT fam, ROWID OD studenta;

FAM ROWID

——————————————

IVANOV AAAA3kAAGAAAAGsAAA

PETROV AAAA3kAAGAAAAGsAAB

V databázi Věštec indexy se používají k různým účelům: k zajištění jedinečnosti hodnot v databázi, ke zlepšení výkonu vyhledávání záznamů v tabulce atd. Výkon je vylepšen zahrnutím odkazu na indexovaný sloupec nebo sloupce do vyhledávacích kritérií pro data v tabulce. V Věštec indexy lze vytvořit na libovolném sloupci tabulky kromě sloupců LONG. Indexy rozlišují mezi aplikacemi necitlivými na rychlost a vysoce výkonnými aplikacemi, zejména při práci s velkými tabulkami. Než se však rozhodnete vytvořit index, musíte zvážit pro a proti výkonu systému. Výkon se nezlepší, pokud jednoduše zadáte index a zapomenete na něj.

Přestože největší zlepšení výkonu pochází z vytvoření indexu ve sloupci, kde jsou všechny hodnoty jedinečné, můžete získat podobné výsledky pro sloupce, které obsahují duplicitní hodnoty nebo hodnoty NULL. K vytvoření indexu není nutné, aby hodnoty sloupců byly jedinečné. Zde je několik doporučení, která vám pomohou dosáhnout požadovaného zvýšení výkonu při použití standardního indexu, a také se podíváme na problémy související s rovnováhou mezi výkonem a spotřebou místa na disku při vytváření indexu.

Použití indexů k vyhledávání informací v tabulkách může poskytnout výrazné zlepšení výkonu oproti skenování tabulek, jejichž sloupce nejsou indexovány. Vybrat ten správný index však není vůbec jednoduché. Samozřejmě, sloupec, jehož hodnoty jsou všechny jedinečné, je vhodnější pro indexování pomocí indexu B-stromu, ale sloupec, který nesplňuje tyto požadavky, je dobrým kandidátem, pokud asi 10 % jeho řádků obsahuje identické hodnoty. a nic víc. Sloupce „Přepnout“ nebo „příznak“, například ty, které ukládají informace o pohlaví osoby, nejsou vhodné pro indexy B-stromu. Sloupce, které se používají k uložení malého počtu „spolehlivých hodnot“, stejně jako ty, které určité hodnoty, také nejsou vhodné. pak znaky např. „spolehlivost“ nebo „nespolehlivost“, „aktivita“ nebo „nečinnost“, „ano“ nebo „ne“ atd. atd. Konečně jsou indexy s reverzními klíči používá se zpravidla tam, kde je instalován a provozován Věštec Parallel Server a musíte zvýšit úroveň paralelismu v databázi na maximum.