--Index je štruktúra na disku, ktorá je spojená s tabuľkou alebo zobrazením a urýchľuje získavanie riadkov z tabuľky alebo zobrazenia. Index obsahuje kľúče vytvorené z jedného alebo viacerých stĺpcov v tabuľke alebo zobrazení. Tieto kľúče sú uložené vo vyváženej stromovej štruktúre, ktorá podporuje rýchle vyhľadávanie riadkov podľa ich hodnôt kľúča na serveri SQL Server.

– Klastrované indexy triedia a ukladajú riadky údajov v tabuľkách alebo zobrazeniach na základe ich kľúčových hodnôt. Tieto hodnoty sú stĺpce zahrnuté v definícii indexu. Existuje iba jeden klastrovaný index na tabuľku, pretože riadky údajov možno triediť iba v jednom poradí.
--Riadky údajov v tabuľke sú uložené v poradí zoradenia iba vtedy, ak tabuľka obsahuje klastrovaný index. Ak má tabuľka klastrovaný index, potom sa tabuľka nazýva klastrovaná. Ak tabuľka nemá klastrovaný index, dátové riadky sú uložené v neusporiadanej štruktúre nazývanej halda.

--Neklastrovaný index má presne rovnakú štruktúru ako klastrovaný index, ale s dvoma dôležitými rozdielmi:
-- nezhlukovaný index nemení fyzické poradie riadkov v tabuľke a listové stránky v nezhlukovanom indexe pozostávajú z indexových kľúčov a záložiek.

--Zhlukované indexy poskytujú rýchlejšie získavanie údajov ako nezhlukované indexy. Zvyčajne sa ukáže, že sú rýchlejšie aj pri aktualizácii, ale nie vtedy, keď sa veľa aktualizácií deje na rovnakom mieste uprostred vzťahu.

--Z nejakého dôvodu má klastrovaný index tendenciu bežať rýchlejšie ako nezhlukovaný index. Keď systém skenuje klastrovaný index, nie je potrebné opustiť štruktúru B-stromu na skenovanie dátových stránok, pretože takéto stránky sú už prítomné na úrovni listu stromu.

--Neklastrovaný index tiež vyžaduje viac I/O operácií ako zodpovedajúci klastrovaný index.

--Neklastrovaný index potrebuje čítať dátové stránky po naskenovaní B-stromu, alebo ak je klastrovaný index v inom stĺpci (stĺpcoch) tabuľky, nezhlukovaný index potrebuje čítať štruktúru B-stromu klastrovaného indexu .

-- Takže klastrovaný index bude výrazne rýchlejší ako skenovanie tabuľky, aj keď je jeho selektivita dosť nízka (dotaz vracia veľa riadkov)

VYTVORIŤ TABUĽKU tsql.dbo.NI
ID int NOT NULL,
T char(8) NULL
);

VYTVORIŤ TABUĽKU tsql.dbo.NCI
ID int NOT NULL,
T char(8) NULL
);

--Vytvorte klastrovaný index

VYTVORIŤ KLUSTEROVÝ INDEX IX_1
ON tsql.dbo.NCI(ID);

--Vytvorte v tabuľke nezhlukovaný index

VYTVORIŤ NEZAHRNUTÝ INDEX IX_2
ON tsql.dbo.NCI(T);

--Pridajte testovacie údaje
DECLARE @i INT = 100000;
DECLARE @t CHAR(1) = "T";

KEĎ @i > 0
ZAČAŤ
vložiť do tsql.dbo.NI hodnoty (@i, @t + CAST(@i AS char(6)));
vložiť do tsql.dbo.NCI hodnoty (@i, @t + CAST(@i AS char(6)));
SET @i -= 1;
KONIEC

--Dotazy na tabuľku s indexmi
VYBERTE ID, T Z tsql.dbo.NCI
OBJEDNAŤ PODĽA ID, T

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

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

--Dopyt pomocou oboch indexov
USE tsql;
SELECT CAST(dbo.NCI.ID AS VARCHAR)
OD dbo.NCI
GROUP PODĽA dbo.NCI.ID
UNION VŠETKO
SELECT dbo.NCI.T
OD dbo.NCI
SKUPINA PODĽA dbo.NCI.T

--Informácie o indexoch
SELECT index_type_desc, index_depth, index_level,
počet_strán, počet_záznamov
Z sys.dm_db_index_physical_stats
(DB_ID(N"tsql"), OBJECT_ID(N"dbo.NCI"), NULL, NULL, "DETAILNÉ");

--Vymazanie indexov
AK EXISTUJE (VYBERTE názov FROM sys.indexes
WHERE name = N"IX_1")
DROP INDEX IX_1 ON tsql.dbo.NCI;

AK EXISTUJE (VYBERTE názov FROM sys.indexes
WHERE name = N"IX_2")
DROP INDEX IX_2 ON tsql.dbo.NCI;

V predchádzajúcom článku sme predstavili spôsoby optimalizácie relačných databáz a diskutovali o tom, ako fungujú klastrované a neklastrované indexy v kontexte optimalizácie času vykonávania databázových dotazov. Teraz je čas uviesť tieto poznatky do praxe tým, že sa naučíte vytvárať optimalizačné indexy pre databázu MS SQL.

Dovoľte mi pripomenúť vám definíciu tabuľky Staffs, s ktorou budeme pracovať:

Stôl pre zamestnancov

Povedzme, že potrebujeme vytvoriť neklastrovaný index pre tabuľku Staffs, ktorý bude optimalizovať nasledujúci dotaz:

VYBERTE IDENTIFIKÁTOR, Meno, Pracovnú pozíciu Z Veci, KDE PLAT > 1000 A fotka NIE JE NULL

Indexovým kľúčom budú stĺpce PLAT a Foto, keďže výber je filtrovaný podľa týchto polí. A stĺpce Id, Name a Job budú stĺpce zahrnuté v indexe.

Všeobecná syntax príkazu je nasledovná:

POUŽÍVAŤ Ísť

VYTVORIŤ NEZAHRNUTÝ INDEX ON (ASC – indexové kľúčové stĺpce)

ZAHRNUŤ ( -- zahrnuté stĺpce) GO

V našom prípade bude žiadosť vyzerať takto:

(Plat, Foto) VRÁTAJTE (Id, Meno, Práca) ÍSŤ

Vytvorili sme nezhlukovaný index. Alebo skôr nezhlukovaný index pokrytia. To znamená, že index obsahuje všetky polia potrebné na vykonanie dotazu a SQL Server nebude pri vykonávaní dotazu pristupovať k základnej tabuľke.

Ak by bol náš kód takýto:

VYTVORIŤ NEZAHRNUTÝ INDEX IDX_StaffsSearch ON Stuffs

(Plat, Foto) VRÁTANE (Id) ÍSŤ

V tomto prípade index prestáva byť krycím indexom, pretože nezahŕňa všetky stĺpce použité v dotaze. Optimalizátor bude stále používať tento index pri vykonávaní dotazu, ale jeho účinnosť sa zníži o rádovo, pretože bude vyžadovať prístup k základnej tabuľke.

Klastrovaný index sa vytvorí pomocou nasledujúceho príkazu:

VYTVORIŤ KLUSTEROVÝ INDEX IDX_Stsffsid ON Stuffs (Id)

Tu bol vytvorený jedinečný klastrovaný index založený na primárnom kľúči tabuľky (stĺpec Id).

Reálny príklad

Poďme si teraz vypracovať scenár, v ktorom môžeme reálne zhodnotiť mieru nárastu výkonu v prípade použitia indexov.

Vytvorme novú databázu:

VYTVORIŤ DATABÁZU TestDB;

A jedna tabuľka Zákazníci, ktorá bude pozostávať zo štyroch stĺpcov:

VYTVORIŤ TABUĽKU.(

NOT NULL, NULL, NULL, NULL) GO

Teraz vyplňte našu tabuľku náhodnými údajmi. Stĺpec Id sa bude zvyšovať v slučke a zvyšné tri stĺpce tabuľky budú vyplnené náhodnými číslami pomocou špeciálnej verzie náhodnej funkcie:

DECLARE @i int = 0;

Kým< 500000) BEGIN INSERT INTO Customers(Id, Num1, Num2, Num3) VALUES(

@i, abs(kontrolný súčet(newid())), abs(kontrolný súčet(newid())), abs(kontrolný súčet(newid())) SET @i = @i + 1; KONIEC

Tento skript pridá do tabuľky pol milióna záznamov, takže buďte trpezliví, skript bude bežať minimálne 3 minúty.

Všetko je pripravené na test. Vyhodnotíme výkonnostné charakteristiky dotazu. Keďže čas vykonania dotazu môže závisieť od konkrétneho počítača, budeme analyzovať nezávislejší ukazovateľ - počet logických čítaní.

Ak chcete povoliť režim zhromažďovania štatistík, musíte spustiť nasledujúci príkaz:

Teraz, po vykonaní každej požiadavky, na karte Správy budeme mať prístup k štatistikám o vykonaní tejto požiadavky, ako je uvedené nižšie:

Nás zaujíma iba hodnota parametra logického čítania.

Takže v našej tabuľke zatiaľ nie sú žiadne indexy. Spustite nasledujúce tri dotazy a zaznamenajte počet logických čítaní pre každý dotaz do tabuľky výsledkov nižšie:

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

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

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

Tieto dopyty vrátia 1 riadok, 1 000 riadkov a 5 000 riadkov. Bez indexov je ukazovateľ výkonu (počet logických čítaní) pre všetky dotazy rovnaký a rovný 1621. Údaje zadáme do tabuľky výsledkov:

Vidíme, že pri druhom a treťom dotaze, keď sa vráti pomerne veľký počet riadkov, index, ktorý sme vytvorili, nezlepšil výkon. V prípade dotazu, ktorý vracia jeden riadok, však bolo zrýchlenie obrovské. Môžeme teda dospieť k záveru, že pri optimalizácii dopytov, ktoré vracajú jeden výsledok, má zmysel vytvárať nepokrývajúce indexy.

Teraz vytvorte krycí index, čím dosiahneme maximálny výkon.

Najprv vymažeme predchádzajúci index:

POUŽÍVAJTE TestDB GO DROP INDEX Customers.TestIndex1

A vytvoríme nový index:

VYTVORIŤ NEZAHRNUTÝ INDEX TestIndex2 ON dbo.Customers(Id) INCLUDE (Num1, Num2);

Teraz spustite naše dotazy tretíkrát a zapíšme výsledky do tabuľky:

Žiadne indexy
Nekrycí index
Krycí index

Je ľahké vidieť, že nárast výkonu bol enormný. Takto sme desaťnásobne zvýšili rýchlosť vykonávania dotazov. Pri prevádzke databázy, v ktorej sú uložené milióny riadkov, bude tento nárast výkonu celkom viditeľný.

V tomto článku sme sa pozreli na príklad optimalizácie databázy vytváraním indexov. Stojí za zmienku, že vytváranie indexov je čisto individuálny proces pre každú požiadavku. Ak chcete vytvoriť index, ktorý skutočne optimalizuje výkon dotazu, musíte dôkladne analyzovať samotný dotaz a plán jeho vykonávania.

Efektívne vytváranie indexov je jedným z najlepších spôsobov, ako zlepšiť výkon databázovej aplikácie. Bez použitia indexov je SQL Server ako čitateľ, ktorý sa snaží nájsť slovo v knihe tak, že sa pozrie na každú stránku. Ak má kniha vecný register (index), čitateľ si vyhľadá potrebné informácie oveľa rýchlejšie.

Ak neexistuje index, server SQL pri získavaní údajov z tabuľky prehľadá celú tabuľku a skontroluje každý riadok, či sú splnené kritériá dotazu. Takéto úplné skenovanie môže byť katastrofálne pre výkon celého systému, najmä ak je v tabuľkách veľa údajov.

Jednou z najdôležitejších úloh pri práci s databázou je vytvorenie optimálneho indexu na zlepšenie výkonu systému. Väčšina veľkých databáz poskytuje nástroje na zobrazenie plánu vykonávania dotazov a pomáha vám vyladiť a optimalizovať indexy. Tento článok zdôrazňuje niekoľko dobrých pravidiel, ktoré platia pri vytváraní alebo úprave indexov v databáze. Najprv sa pozrime na situácie, kedy indexovanie zlepšuje výkon a kde indexovanie môže ublížiť.

Užitočné indexy

Takže indexovanie tabuľky bude užitočné pri hľadaní konkrétneho záznamu v tabuľke pomocou príkazu Where. Medzi takéto dopyty patria napríklad dopyty, ktoré hľadajú rozsah hodnôt, dopyty, ktoré zodpovedajú presnej hodnote ku konkrétnej hodnote, a dopyty, ktoré spájajú dve tabuľky.

Napríklad nasledujúce dotazy na databázu Northwind budú bežať efektívnejšie pri vytváraní indexu v stĺpci UnitPrice.

Odstrániť z produktov, kde jednotková cena=1
Vyberte * z produktov, kde je jednotková cena medzi 14 A 16

Pretože položky indexu sú uložené zoradené, indexovanie je užitočné aj pri vytváraní dotazu pomocou klauzuly Order by. Bez indexu sa záznamy načítajú a triedia počas spustenia dotazu. Index založený na jednotkovej cene vám umožní jednoducho skenovať index a získať riadky podľa odkazu pri spracovaní ďalšej požiadavky. Ak chcete zoradiť riadky v zostupnom poradí, môžete index jednoducho naskenovať v opačnom poradí.

Vyberte * Z poradia produktov podľa jednotkovej ceny ASC

Zoskupenie záznamu pomocou príkazu Group by tiež často vyžaduje triedenie, takže vytvorenie indexu v stĺpci UnitPrice bude užitočné aj pre ďalší dotaz, ktorý počíta počet jednotiek produktu pri každej konkrétnej cene.

Vyberte počet (*), Jednotková cena zo skupiny produktov podľa jednotkovej ceny

Indexy sú užitočné na udržiavanie jedinečnej hodnoty pre stĺpec, pretože DBMS sa môže jednoducho pozrieť na index, aby zistil, či hodnota už existuje. Z tohto dôvodu sú primárne kľúče vždy indexované.

Nevýhody indexovania

Indexy znižujú výkon systému počas zmien záznamov. Vždy, keď sa vykoná dotaz na zmenu údajov v tabuľke, musí sa zmeniť aj index. Ak chcete vybrať optimálny počet indexov, musíte otestovať databázu a sledovať jej výkon. Statické systémy, kde sa databázy používajú predovšetkým na získavanie údajov, ako je vytváranie prehľadov, môžu obsahovať viac indexov na podporu dotazov iba na čítanie. Databázy s veľkým počtom transakcií na zmenu údajov budú potrebovať malý počet indexov, aby poskytli vyššiu priepustnosť.

Indexy zaberajú ďalšie miesto na disku a v RAM. Presná veľkosť bude závisieť od počtu záznamov v tabuľke, ako aj od počtu a veľkosti stĺpcov v indexe. Vo väčšine prípadov to nie je hlavný problém, pretože miesto na disku je teraz ľahké obetovať pre lepší výkon.

Budovanie optimálneho indexu

Jednoduchý index

Jednoduchý index je index, ktorý používa hodnoty jedného poľa v tabuľke. Používanie jednoduchého indexu je výhodné z dvoch dôvodov. Po prvé, prevádzka databázy veľmi zaťažuje váš pevný disk. Veľké indexové kľúče prinútia databázu vykonávať viac I/O operácií, čo obmedzuje výkon.

Po druhé, pretože prvky indexu sú často zapojené do porovnávania, menšie indexy sa ľahšie porovnávajú. Z týchto dvoch dôvodov je jeden celý stĺpec lepším indexom, pretože je malý a ľahko sa porovnáva. Reťazce znakov si na druhej strane vyžadujú porovnávanie znakov po znakoch a pozornosť pri manipulácii s parametrami.

Selektívny index

Najúčinnejšie indexy sú tie s nízkym percentom duplicitných hodnôt. Napríklad telefónny zoznam pre mesto, v ktorom má takmer každý priezvisko Smith, nebude taký užitočný, ak budú položky v ňom zoradené podľa priezviska.

Index s vysokým percentom jedinečných hodnôt sa nazýva aj selektívny index. Je zrejmé, že jedinečný index má najväčšiu selektivitu, pretože neobsahuje duplicitné hodnoty. Mnoho DBMS dokáže sledovať štatistiky o každom indexe a dokáže rozpoznať, koľko neduplikovaných hodnôt obsahuje každý index. Táto štatistika sa používa pri generovaní plánu vykonávania dotazu.

Krycie indexy

Indexy pozostávajú zo stĺpca údajov, na ktorom je zostavený samotný index, a ukazovateľa na príslušný riadok. Je to ako register knihy: obsahuje len kľúčové slová a odkaz na stránku, na ktorú môžete prejsť, kde nájdete ďalšie informácie. Typicky bude DBMS sledovať ukazovatele na riadok z indexu, aby zhromaždil všetky informácie potrebné pre dotaz. Ak však index obsahuje všetky stĺpce potrebné v dotaze, informácie možno získať bez prístupu k samotnej tabuľke.

Zoberme si index v stĺpci UnitPrice, ktorý už bol spomenutý vyššie. DBMS môže použiť iba položky indexu na vykonanie nasledujúceho dotazu.

Vyberte Počet (*), Jednotková cena zo skupiny produktov podľa jednotkovej ceny

Tento typ dotazu sa nazýva krycí dotaz, pretože všetky dopytované stĺpce možno získať z jedného indexu. V prípade najdôležitejších dotazov možno budete chcieť zvážiť vytvorenie indexu pokrytia pre najlepší možný výkon. Takéto indexy budú pravdepodobne zložené (používajú viac ako jeden stĺpec), čo je opak prvého princípu: vytvorte jednoduché indexy. Je zrejmé, že výber optimálneho počtu stĺpcov v indexe možno posúdiť iba testovaním a monitorovaním výkonu databázy v rôznych situáciách.

Index klastra

Mnoho databáz má jeden špeciálny index v tabuľke, kde sú všetky údaje z riadka obsiahnuté v indexe. V SQL Server sa takýto index nazýva klastrovaný index. Klastrovaný index možno porovnať s telefónnym zoznamom, pretože každý prvok indexu obsahuje všetky potrebné informácie a neobsahuje odkazy na získanie ďalších údajov.

Existuje všeobecné pravidlo – každá netriviálna tabuľka musí mať klastrovaný index. Ak je možné vytvoriť iba jeden index v tabuľke, urobte ho klastrovaný. Na serveri SQL Server sa po vytvorení primárneho kľúča automaticky vytvorí klastrovaný index (ak ho ešte neobsahuje), pričom sa ako indexovací kľúč použije stĺpec primárneho kľúča. Klastrovaný index je najefektívnejší index (ak sa používa, pokrýva celý dotaz) a v mnohých DBMS takýto index pomáha efektívne spravovať priestor požadovaný na ukladanie tabuliek, pretože inak (bez vytvárania zoskupeného indexu) sú riadky tabuliek uložené v neusporiadaná štruktúra, ktorá sa nazýva halda.

Buďte opatrní pri výbere stĺpcov pre klastrovaný index. Ak zmeníte záznam a zmeníte hodnotu stĺpca v klastrovanom indexe, databáza bude nútená prebudovať položky indexu (aby boli zoradené). Pamätajte, že položky indexu pre klastrovaný index obsahujú všetky hodnoty stĺpcov, takže zmena hodnoty stĺpca je porovnateľná s vykonaním príkazu Delete nasledovaného príkazom Insert, ktorý pri častom vykonávaní zjavne spôsobí problémy s výkonom. Z tohto dôvodu klastrované indexy často pozostávajú zo stĺpca primárneho kľúča a cudzieho kľúča. Ak sa kľúčové hodnoty zmenia, zmenia sa veľmi zriedka.

Záver

Určenie správnych indexov na použitie v databáze vyžaduje starostlivú analýzu a testovanie systému. Postupy uvedené v tomto článku sú dobrými pravidlami na vytváranie indexov. Po použití týchto metód budete musieť znova otestovať vašu konkrétnu aplikáciu za špecifických podmienok hardvéru, pamäte a prevádzky.

Jeden z najdôležitejších spôsobov, ako dosiahnuť vysokú produktivitu SQL Server je použitie indexov. Index urýchľuje proces dotazovania tým, že poskytuje rýchly prístup k riadkom údajov v tabuľke, podobne ako index v knihe vám pomáha rýchlo nájsť informácie, ktoré potrebujete. V tomto článku uvediem stručný prehľad indexov v SQL Server a vysvetliť, ako sú usporiadané v databáze a ako pomáhajú urýchliť databázové dotazy.

Indexy sa vytvárajú v stĺpcoch tabuľky a zobrazenia. Indexy poskytujú spôsob, ako rýchlo vyhľadávať údaje na základe hodnôt v týchto stĺpcoch. Ak napríklad vytvoríte index na primárnom kľúči a potom vyhľadáte riadok údajov pomocou hodnôt primárneho kľúča, potom SQL Server najprv nájde hodnotu indexu a potom pomocou indexu rýchlo nájde celý riadok údajov. Bez indexu sa vykoná úplná kontrola všetkých riadkov v tabuľke, čo môže mať významný vplyv na výkon.
Index môžete vytvoriť pre väčšinu stĺpcov v tabuľke alebo zobrazení. Výnimku tvoria najmä stĺpce s dátovými typmi na ukladanie veľkých objektov ( LOB), ako napr obrázok, text alebo varchar(max). Môžete tiež vytvoriť indexy v stĺpcoch určených na ukladanie údajov vo formáte XML, ale tieto indexy sú štruktúrované trochu inak ako štandardné a ich zohľadnenie je nad rámec tohto článku. Tiež článok nerozoberá columnstore indexy. Namiesto toho sa sústredím na tie indexy, ktoré sa najčastejšie používajú v databázach SQL Server.
Index pozostáva zo sady stránok, indexových uzlov, ktoré sú usporiadané do stromovej štruktúry - vyvážený strom. Táto štruktúra je svojou povahou hierarchická a začína koreňovým uzlom v hornej časti hierarchie a listovými uzlami, listami, v spodnej časti, ako je znázornené na obrázku:


Keď dotazujete indexovaný stĺpec, dotazovací mechanizmus sa spustí v hornej časti koreňového uzla a postupuje smerom nadol cez medziľahlé uzly, pričom každá medzivrstva obsahuje podrobnejšie informácie o údajoch. Dotazovací mechanizmus pokračuje v pohybe cez indexové uzly, kým nedosiahne spodnú úroveň s listami indexu. Ak napríklad hľadáte hodnotu 123 v indexovanom stĺpci, vyhľadávací mechanizmus najprv určí stránku na prvej strednej úrovni na koreňovej úrovni. V tomto prípade prvá stránka ukazuje na hodnotu od 1 do 100 a druhá od 101 do 200, takže dopytovací nástroj pristúpi na druhú stránku tejto strednej úrovne. Ďalej uvidíte, že by ste sa mali obrátiť na tretiu stránku ďalšej strednej úrovne. Odtiaľto bude dotazovací subsystém čítať hodnotu samotného indexu na nižšej úrovni. Listy indexu môžu obsahovať buď samotné údaje tabuľky, alebo jednoducho ukazovateľ na riadky s údajmi v tabuľke, v závislosti od typu indexu: klastrovaný index alebo nezhlukovaný index.

Klastrovaný index

Klastrovaný index ukladá skutočné riadky údajov do listov indexu. Ak sa vrátime k predchádzajúcemu príkladu, znamená to, že riadok údajov priradený k hodnote kľúča 123 bude uložený v samotnom indexe. Dôležitou charakteristikou zoskupeného indexu je, že všetky hodnoty sú zoradené v určitom poradí, buď vzostupne alebo zostupne. Preto tabuľka alebo zobrazenie môže mať iba jeden klastrovaný index. Okrem toho je potrebné poznamenať, že údaje v tabuľke sú uložené v triedenej forme iba vtedy, ak bol na tejto tabuľke vytvorený klastrovaný index.
Tabuľka, ktorá nemá klastrovaný index, sa nazýva halda.

Nezhlukovaný index

Na rozdiel od zoskupeného indexu obsahujú listy nezhlukovaného indexu iba tieto stĺpce ( kľúč), ktorým je tento index určený, a obsahuje aj ukazovateľ na riadky s reálnymi údajmi v tabuľke. To znamená, že systém poddotazov vyžaduje ďalšiu operáciu na nájdenie a získanie požadovaných údajov. Obsah ukazovateľa údajov závisí od toho, ako sú údaje uložené: klastrovaná tabuľka alebo halda. Ak ukazovateľ ukazuje na klastrovanú tabuľku, ukazuje na klastrovaný index, ktorý možno použiť na nájdenie skutočných údajov. Ak ukazovateľ odkazuje na haldu, potom ukazuje na konkrétny identifikátor riadka údajov. Neklastrované indexy nie je možné triediť ako klastrované indexy, ale v tabuľke alebo zobrazení môžete vytvoriť viac ako jeden neklastrovaný index, až do 999. To neznamená, že by ste mali vytvoriť čo najviac indexov. Indexy môžu zlepšiť alebo znížiť výkon systému. Okrem možnosti vytvárať viaceré indexy bez klastrov môžete zahrnúť aj ďalšie stĺpce ( zahrnutý stĺpec) do svojho indexu: listy indexu uložia nielen hodnotu samotných indexovaných stĺpcov, ale aj hodnoty týchto neindexovaných dodatočných stĺpcov. Tento prístup vám umožní obísť niektoré obmedzenia kladené na index. Môžete napríklad zahrnúť neindexovateľný stĺpec alebo obísť limit dĺžky indexu (vo väčšine prípadov 900 bajtov).

Typy indexov

Okrem toho, že je index klastrovaný alebo neklastrovaný, môže byť ďalej nakonfigurovaný ako zložený index, jedinečný index alebo krycí index.

Zložený index

Takýto index môže obsahovať viac ako jeden stĺpec. Do indexu môžete zahrnúť až 16 stĺpcov, ale ich celková dĺžka je obmedzená na 900 bajtov. Klastrované aj nezhlukované indexy môžu byť zložené.

Jedinečný index

Tento index zaisťuje, že každá hodnota v indexovanom stĺpci je jedinečná. Ak je index zložený, potom jedinečnosť platí pre všetky stĺpce v indexe, ale nie pre každý jednotlivý stĺpec. Napríklad, ak vytvoríte jedinečný index na stĺpcoch NÁZOV A PRIEZVISKO, potom musí byť celé meno jedinečné, ale duplikáty mena alebo priezviska sú možné.
Jedinečný index sa automaticky vytvorí, keď definujete obmedzenie stĺpca: obmedzenie primárneho kľúča alebo jedinej hodnoty:

• Primárny kľúč
  Keď definujete obmedzenie primárneho kľúča v jednom alebo viacerých stĺpcoch, potom SQL Server automaticky vytvorí jedinečný klastrovaný index, ak klastrovaný index ešte nebol vytvorený (v tomto prípade sa na primárnom kľúči vytvorí jedinečný nezhlukovaný index)
• Jedinečnosť hodnôt
  Keď definujete obmedzenie jedinečnosti hodnôt SQL Server automaticky vytvorí jedinečný index bez klastrov. Môžete určiť, že sa vytvorí jedinečný klastrovaný index, ak v tabuľke ešte nebol vytvorený žiadny klastrovaný index

Krycí index

Takýto index umožňuje špecifickému dotazu okamžite získať všetky potrebné údaje z listov indexu bez dodatočného prístupu k záznamom samotnej tabuľky.

Navrhovanie indexov

Akokoľvek užitočné môžu byť indexy, musia byť navrhnuté starostlivo. Pretože indexy môžu zaberať značné miesto na disku, nechcete vytvárať viac indexov, ako je potrebné. Okrem toho sa indexy automaticky aktualizujú, keď sa aktualizuje samotný riadok údajov, čo môže viesť k dodatočnej réžii prostriedkov a zníženiu výkonu. Pri navrhovaní indexov je potrebné vziať do úvahy niekoľko aspektov týkajúcich sa databázy a dotazov proti nej.

Databáza

Ako už bolo uvedené, indexy môžu zlepšiť výkon systému, pretože poskytujú vyhľadávaciemu nástroju rýchly spôsob vyhľadávania údajov. Mali by ste však vziať do úvahy aj to, ako často máte v úmysle vkladať, aktualizovať alebo mazať údaje. Keď zmeníte údaje, musia sa zmeniť aj indexy, aby odrážali zodpovedajúce akcie s údajmi, čo môže výrazne znížiť výkon systému. Pri plánovaní stratégie indexovania zvážte nasledujúce pokyny:

• Pre tabuľky, ktoré sa často aktualizujú, použite čo najmenej indexov.
• Ak tabuľka obsahuje veľké množstvo údajov, ale zmeny sú menšie, použite toľko indexov, koľko je potrebné na zlepšenie výkonu vašich dotazov. Pred použitím indexov na malých tabuľkách si však dobre premyslite, pretože... Je možné, že používanie indexového vyhľadávania môže trvať dlhšie ako jednoduché skenovanie všetkých riadkov.
• Pri klastrovaných indexoch sa snažte udržiavať polia čo najkratšie. Najlepším prístupom je použitie zoskupeného indexu v stĺpcoch, ktoré majú jedinečné hodnoty a neumožňujú NULL. To je dôvod, prečo sa primárny kľúč často používa ako klastrovaný index.
• Jedinečnosť hodnôt v stĺpci ovplyvňuje výkon indexu. Vo všeobecnosti platí, že čím viac duplikátov máte v stĺpci, tým horšiu výkonnosť indexu. Na druhej strane, čím viac jedinečných hodnôt je, tým lepší je výkon indexu. Vždy, keď je to možné, použite jedinečný index.
• Pri zloženom indexe vezmite do úvahy poradie stĺpcov v indexe. Stĺpce, ktoré sa používajú vo výrazoch KDE(Napríklad, WHERE Meno = "Charlie") musí byť na prvom mieste v indexe. Nasledujúce stĺpce by mali byť uvedené na základe jedinečnosti ich hodnôt (stĺpce s najvyšším počtom jedinečných hodnôt sú na prvom mieste).
• Môžete tiež zadať index pre vypočítané stĺpce, ak spĺňajú určité požiadavky. Napríklad výrazy použité na získanie hodnoty stĺpca musia byť deterministické (vždy vrátia rovnaký výsledok pre danú množinu vstupných parametrov).

Databázové dotazy

Ďalšou úvahou pri navrhovaní indexov je to, aké dotazy sa spúšťajú proti databáze. Ako už bolo uvedené, musíte zvážiť, ako často sa údaje menia. Okrem toho by sa mali dodržiavať tieto zásady:

• Skúste vložiť alebo upraviť čo najviac riadkov do jedného dotazu, namiesto toho, aby ste to robili v niekoľkých samostatných dotazoch.
• Vytvorte index bez klastrov v stĺpcoch, ktoré sa často používajú ako hľadané výrazy vo vašich dopytoch. KDE a spojenia v PRIPOJTE SA.
• Zvážte indexovanie stĺpcov používaných vo vyhľadávacích dopytoch riadkov na presné zhody hodnôt.

A teraz vlastne:

14 otázok o indexoch v SQL Server, ktoré ste sa hanbili položiť

Prečo nemôže mať tabuľka dva zoskupené indexy?

Chcete krátku odpoveď? Klastrovaný index je tabuľka. Keď vytvoríte klastrovaný index v tabuľke, úložný mechanizmus zoradí všetky riadky v tabuľke vo vzostupnom alebo zostupnom poradí podľa definície indexu. Klastrovaný index nie je samostatná entita ako iné indexy, ale mechanizmus na triedenie údajov v tabuľke a uľahčenie rýchleho prístupu k riadkom údajov.
Predstavme si, že máte tabuľku obsahujúcu históriu predajných transakcií. Tabuľka Predaj obsahuje informácie ako ID objednávky, pozícia produktu v objednávke, číslo produktu, množstvo produktu, číslo a dátum objednávky atď. Vytvoríte klastrovaný index na stĺpcoch Číslo objednávky A LineID, zoradené vo vzostupnom poradí, ako je uvedené nižšie T-SQL kód:
VYTVORIŤ JEDINEČNÝ KLUSTEROVÝ INDEX ix_oriderid_lineid ON dbo.Sales(ID objednávky, ID riadku);
Keď spustíte tento skript, všetky riadky v tabuľke budú fyzicky zoradené najprv podľa stĺpca OrderID a potom podľa LineID, ale samotné údaje zostanú v jedinom logickom bloku, tabuľke. Z tohto dôvodu nemôžete vytvoriť dva klastrované indexy. Môže existovať iba jedna tabuľka s jedným údajom a táto tabuľka môže byť zoradená iba raz v určitom poradí.

Ak klastrovaná tabuľka poskytuje veľa výhod, tak prečo používať haldu?

Máš pravdu. Klastrované tabuľky sú skvelé a väčšina vašich dotazov bude fungovať lepšie v tabuľkách, ktoré majú klastrovaný index. Ale v niektorých prípadoch možno budete chcieť ponechať stoly v ich prirodzenom, pôvodnom stave, t.j. vo forme haldy a vytvorte iba indexy bez klastrov, aby vaše dotazy zostali v chode.
Ako si pamätáte, halda ukladá údaje v náhodnom poradí. Úložný subsystém zvyčajne pridáva údaje do tabuľky v poradí, v akom sú vložené, no úložný subsystém tiež rád presúva riadky, aby bolo ukladanie efektívnejšie. Tým pádom nemáte šancu predpovedať, v akom poradí budú dáta uložené.
Ak dotazovací nástroj potrebuje nájsť údaje bez výhody nezhlukovaného indexu, vykoná úplné skenovanie tabuľky, aby našiel riadky, ktoré potrebuje. Na veľmi malých stoloch to zvyčajne nie je problém, ale ako sa halda zväčšuje, výkon rýchlo klesá. Samozrejme, neklastrovaný index môže pomôcť pomocou ukazovateľa na súbor, stránku a riadok, kde sú uložené požadované údaje – to je zvyčajne oveľa lepšia alternatíva k skenovaniu tabuľky. Napriek tomu je ťažké porovnávať výhody klastrovaného indexu pri zvažovaní výkonu dotazov.
Halda však môže pomôcť zlepšiť výkon v určitých situáciách. Uvažujme o tabuľke s množstvom vložiek, ale málo aktualizácií alebo vymazaní. Napríklad tabuľka s protokolom sa primárne používa na vkladanie hodnôt, kým nie je archivovaná. Na halde neuvidíte stránkovanie a fragmentáciu údajov ako pri klastrovanom indexe, pretože riadky sa jednoducho pridajú na koniec haldy. Prílišné rozdelenie stránok môže mať významný vplyv na výkon, a to nie práve v dobrom zmysle. Vo všeobecnosti vám halda umožňuje vkladať údaje relatívne bezbolestne a nebudete musieť riešiť režijné náklady na ukladanie a údržbu, ktoré by ste museli riešiť pri klastrovanom indexe.
Nedostatok aktualizácie a odstraňovania údajov by sa však nemal považovať za jediný dôvod. Dôležitým faktorom je aj spôsob vzorkovania údajov. Napríklad by ste nemali používať haldu, ak často dopytujete rozsahy údajov alebo údaje, ktoré dopytujete, je často potrebné triediť alebo zoskupovať.
To všetko znamená, že o použití haldy by ste mali uvažovať iba vtedy, keď pracujete s veľmi malými tabuľkami alebo ak je všetka vaša interakcia s tabuľkou obmedzená na vkladanie údajov a vaše dotazy sú mimoriadne jednoduché (a používate indexy bez klastrov). tak či tak). V opačnom prípade sa držte dobre navrhnutého zoskupeného indexu, napríklad indexu definovaného v jednoduchom vzostupnom kľúčovom poli, ako je široko používaný stĺpec s IDENTITA.

Ako zmením predvolený faktor plnenia indexu?

Zmena predvoleného faktora plnenia indexu je jedna vec. Pochopenie toho, ako funguje predvolený pomer, je druhá vec. Najprv však urobte pár krokov späť. Faktor plnenia indexu určuje množstvo priestoru na stránke na uloženie indexu na spodnej úrovni (úroveň listu) pred začatím vypĺňania novej strany. Napríklad, ak je koeficient nastavený na 90, potom keď index narastie, zaberie 90 % stránky a potom sa presunie na ďalšiu stránku.
V predvolenom nastavení je hodnota faktora plnenia indexu in SQL Server je 0, čo je rovnaké ako 100. Výsledkom je, že všetky nové indexy automaticky zdedia toto nastavenie, pokiaľ vo svojom kóde špecificky nezadáte hodnotu, ktorá sa líši od systémovej štandardnej hodnoty alebo nezmeníte predvolené správanie. Môžeš použiť SQL Server Management Studio upraviť predvolenú hodnotu alebo spustiť systémovú uloženú procedúru sp_configure. Napríklad nasledujúca sada T-SQL príkazy nastaví hodnotu koeficientu na 90 (najprv sa musíte prepnúť do režimu pokročilých nastavení):
EXEC sp_configure "zobraziť rozšírené možnosti", 1; PREJSŤ PREKONFIGURÁCIU; GO EXEC sp_configure "faktor plnenia", 90; PREJSŤ PREKONFIGURÁCIU; Ísť
Po zmene hodnoty faktora plnenia indexu musíte reštartovať službu SQL Server. Teraz môžete skontrolovať nastavenú hodnotu spustením sp_configure bez zadaného druhého argumentu:
EXEC sp_configure "faktor plnenia" GO
Tento príkaz by mal vrátiť hodnotu 90. V dôsledku toho budú všetky novovytvorené indexy používať túto hodnotu. Môžete to otestovať vytvorením indexu a dotazom na hodnotu faktora plnenia:
POUŽÍVAJTE AdventureWorks2012; -- vaša databáza 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_priezvisko";
V tomto príklade sme vytvorili neklastrovaný index na tabuľke Osoba v databáze AdventureWorks2012. Po vytvorení indexu môžeme získať hodnotu faktora plnenia zo systémových tabuliek sys.indexes. Dopyt by mal vrátiť 90.
Predstavme si však, že sme index vymazali a vytvorili znova, ale teraz sme zadali konkrétnu hodnotu faktora plnenia:
CREATE NENCLUSTERED INDEX ix_people_lastname ON Person.Person(Priezvisko) WITH (fillfactor=80); GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_priezvisko";
Tentokrát sme pridali návod S a možnosť fillfactor pre našu operáciu vytvorenia indexu VYTVORIŤ INDEX a špecifikoval hodnotu 80. Operátor VYBRAŤ teraz vráti zodpovedajúcu hodnotu.
Doteraz bolo všetko celkom jednoduché. V celom tomto procese sa môžete skutočne popáliť, keď vytvoríte index, ktorý používa predvolenú hodnotu koeficientu, za predpokladu, že túto hodnotu poznáte. Napríklad, niekto sa hrá s nastaveniami servera a je taký tvrdohlavý, že nastavil faktor plnenia indexu na 20. Medzitým pokračujete vo vytváraní indexov za predpokladu, že predvolená hodnota je 0. Bohužiaľ, nemáte spôsob, ako zistiť vyplnenie faktor, kým nevytvoríte index a potom skontrolujete hodnotu, ako sme to urobili v našich príkladoch. V opačnom prípade budete musieť počkať na okamih, keď výkon dotazu klesne natoľko, že začnete niečo tušiť.
Ďalším problémom, o ktorom by ste si mali byť vedomí, je prebudovanie indexov. Rovnako ako pri vytváraní indexu môžete pri jeho prestavbe zadať hodnotu faktora plnenia indexu. Na rozdiel od príkazu create index však rebuild nepoužíva predvolené nastavenia servera, napriek tomu, ako sa to môže zdať. Ešte viac, ak špecificky nešpecifikujete hodnotu faktora plnenia indexu, potom SQL Server použije hodnotu koeficientu, s ktorým tento index existoval pred jeho reštrukturalizáciou. Napríklad nasledujúca operácia ZMENIŤ INDEX prebuduje index, ktorý sme práve vytvorili:
ALTER INDEX ix_people_priezvisko ON Person.Person REBUILD; GO SELECT fill_factor FROM sys.indexes WHERE object_id = object_id("Person.Person") AND name="ix_people_priezvisko";
Keď skontrolujeme hodnotu faktora plnenia, dostaneme hodnotu 80, pretože to sme zadali pri poslednom vytváraní indexu. Predvolená hodnota sa ignoruje.
Ako vidíte, zmena hodnoty faktora plnenia indexu nie je až taká náročná. Je oveľa ťažšie poznať aktuálnu hodnotu a pochopiť, kedy sa aplikuje. Ak pri vytváraní a prestavbe indexov vždy konkrétne špecifikujete koeficient, tak vždy poznáte konkrétny výsledok. Pokiaľ sa nebudete musieť starať o to, aby niekto iný znova nepokazil nastavenia servera, čo by spôsobilo prebudovanie všetkých indexov so smiešne nízkym faktorom plnenia indexov.

Je možné vytvoriť klastrovaný index v stĺpci, ktorý obsahuje duplikáty?

Áno a nie. Áno, môžete vytvoriť klastrovaný index v stĺpci kľúča, ktorý obsahuje duplicitné hodnoty. Nie, hodnota kľúčového stĺpca nemôže zostať v nejedinečnom stave. Nechaj ma vysvetliť. Ak vytvoríte nejedinečný klastrovaný index v stĺpci, ukladací mechanizmus pridá k duplicitnej hodnote uniquifikátor, aby sa zabezpečila jedinečnosť, a preto bolo možné identifikovať každý riadok v klastrovanej tabuľke.
Môžete sa napríklad rozhodnúť vytvoriť klastrovaný index v stĺpci obsahujúcom údaje o zákazníkoch Priezvisko ponechanie si priezviska. Stĺpec obsahuje hodnoty Franklin, Hancock, Washington a Smith. Potom znova vložte hodnoty Adams, Hancock, Smith a Smith. Hodnota kľúčového stĺpca však musí byť jedinečná, takže ukladací mechanizmus zmení hodnotu duplikátov tak, aby vyzerali asi takto: Adams, Franklin, Hancock, Hancock1234, Washington, Smith, Smith4567 a Smith5678.
Na prvý pohľad sa tento prístup zdá byť v poriadku, ale celočíselná hodnota zväčšuje veľkosť kľúča, čo sa môže stať problémom, ak existuje veľký počet duplikátov a tieto hodnoty sa stanú základom nezhlukovaného indexu alebo cudzieho kľúčová referencia. Z týchto dôvodov by ste sa vždy, keď je to možné, mali snažiť vytvoriť jedinečné klastrované indexy. Ak to nie je možné, skúste aspoň použiť stĺpce s veľmi vysokým obsahom unikátnej hodnoty.

Ako sa uloží tabuľka, ak nebol vytvorený klastrovaný index?

SQL Server podporuje dva typy tabuliek: klastrované tabuľky, ktoré majú klastrovaný index a haldové tabuľky alebo len haldy. Na rozdiel od klastrovaných tabuliek nie sú údaje na halde nijako zoradené. V podstate ide o kopu (hromadu) dát. Ak do takejto tabuľky pridáte riadok, ukladací mechanizmus ho jednoducho pridá na koniec stránky. Keď sa stránka naplní údajmi, pridá sa na novú stránku. Vo väčšine prípadov budete chcieť vytvoriť klastrovaný index v tabuľke, aby ste využili výhody zoraditeľnosti a rýchlosti dopytov (skúste si predstaviť, že hľadáte telefónne číslo v nezoradenom adresári). Ak sa však rozhodnete nevytvoriť klastrovaný index, stále môžete na halde vytvoriť neklastrovaný index. V tomto prípade bude mať každý riadok indexu ukazovateľ na riadok haldy. Index obsahuje ID súboru, číslo stránky a číslo dátového riadku.

Aký je vzťah medzi obmedzeniami jedinečnosti hodnoty a primárnym kľúčom s indexmi tabuliek?

Primárny kľúč a jedinečné obmedzenie zabezpečujú, že hodnoty v stĺpci sú jedinečné. Pre tabuľku môžete vytvoriť iba jeden primárny kľúč a nemôže obsahovať hodnoty NULOVÝ. Pre tabuľku môžete vytvoriť niekoľko obmedzení jedinečnosti hodnoty a každá z nich môže mať jeden záznam NULOVÝ.
Keď vytvoríte primárny kľúč, ukladací mechanizmus tiež vytvorí jedinečný klastrovaný index, ak klastrovaný index ešte nebol vytvorený. Predvolené správanie však môžete prepísať a vytvorí sa index bez klastrov. Ak pri vytváraní primárneho kľúča existuje klastrovaný index, vytvorí sa jedinečný nezhlukovaný index.
Keď vytvoríte jedinečné obmedzenie, ukladací mechanizmus vytvorí jedinečný, nezhlukovaný index. Môžete však určiť vytvorenie jedinečného klastrovaného indexu, ak ešte nebol vytvorený.
Vo všeobecnosti sú jedinečné obmedzenie hodnoty a jedinečný index to isté.

Prečo sa klastrované a neklastrované indexy na SQL Serveri nazývajú B-strom?

Základné indexy na serveri SQL Server, klastrované alebo neklastrované, sú distribuované cez sady stránok nazývané indexové uzly. Tieto stránky sú usporiadané v špecifickej hierarchii so stromovou štruktúrou nazývanou vyvážený strom. Na najvyššej úrovni je koreňový uzol, na spodnej strane sú listové uzly, s medziľahlými uzlami medzi hornou a spodnou úrovňou, ako je znázornené na obrázku:


Koreňový uzol poskytuje hlavný vstupný bod pre dotazy, ktoré sa pokúšajú získať údaje cez index. Počnúc týmto uzlom spustí dopytovací mechanizmus navigáciu nadol v hierarchickej štruktúre k príslušnému koncovému uzlu obsahujúcemu údaje.
Predstavte si napríklad, že bola prijatá požiadavka na výber riadkov obsahujúcich kľúčovú hodnotu 82. Dopytový subsystém začne pracovať od koreňového uzla, ktorý odkazuje na vhodný medziľahlý uzol, v našom prípade 1-100. Z medziľahlého uzla 1-100 je prechod do uzla 51-100 a odtiaľ do konečného uzla 76-100. Ak ide o klastrovaný index, potom list uzla obsahuje údaje riadka spojeného s kľúčom rovným 82. Ak ide o index bez klastrov, potom list indexu obsahuje ukazovateľ na klastrovanú tabuľku alebo konkrétny riadok v hromada.

Ako môže index dokonca zlepšiť výkon dotazov, ak musíte prejsť cez všetky tieto indexové uzly?

Po prvé, indexy nie vždy zlepšujú výkon. Príliš veľa nesprávne vytvorených indexov mení systém na bahno a znižuje výkon dotazov. Je presnejšie povedať, že ak sú indexy aplikované opatrne, môžu poskytnúť významné zvýšenie výkonu.
Spomeňte si na obrovskú knihu venovanú ladeniu výkonu SQL Server(papierová verzia, nie elektronická verzia). Predstavte si, že chcete nájsť informácie o konfigurácii Resource Governor. Môžete posúvať prstom stránku po stránke cez celú knihu alebo otvoriť obsah a zistiť presné číslo strany s hľadanými informáciami (za predpokladu, že kniha je správne indexovaná a obsah má správne indexy). To vám určite ušetrí značný čas, aj keď najprv musíte získať prístup k úplne inej štruktúre (indexu), aby ste získali potrebné informácie z primárnej štruktúry (knihy).
Ako knižný index, index v SQL Server umožňuje spúšťať presné dotazy na údaje, ktoré potrebujete, namiesto úplného skenovania všetkých údajov obsiahnutých v tabuľke. Pri malých tabuľkách nie je úplné skenovanie zvyčajne problémom, ale veľké tabuľky zaberajú veľa strán údajov, čo môže mať za následok značný čas vykonania dotazu, pokiaľ neexistuje index, ktorý by dotazovaciemu mechanizmu umožnil okamžite získať správne umiestnenie údajov. Predstavte si, že sa stratíte na viacúrovňovej križovatke pred veľkou metropolou bez mapy a dostanete nápad.

Ak sú indexy také skvelé, prečo jednoducho nevytvoriť jeden v každom stĺpci?

Žiadny dobrý skutok by nemal zostať nepotrestaný. Aspoň to je prípad indexov. Indexy samozrejme fungujú skvele, pokiaľ spúšťate dotazy operátora na načítanie VYBRAŤ, ale hneď ako začnú časté hovory operátorom VLOŽIŤ, AKTUALIZOVAŤ A VYMAZAŤ, takže krajina sa veľmi rýchlo mení.
Keď spustíte žiadosť o údaje operátora VYBRAŤ vyhľadávací nástroj nájde index, prejde jeho stromovou štruktúrou a objaví údaje, ktoré hľadá. Čo môže byť jednoduchšie? Ale veci sa zmenia, ak iniciujete vyhlásenie o zmene ako AKTUALIZOVAŤ. Áno, pre prvú časť príkazu môže dotazovací nástroj opäť použiť index na nájdenie upravovaného riadku – to je dobrá správa. A ak dôjde k jednoduchej zmene údajov v riadku, ktorá neovplyvní zmeny v kľúčových stĺpcoch, proces zmeny bude úplne bezbolestný. Čo však v prípade, ak zmena spôsobí rozdelenie stránok obsahujúcich údaje alebo sa zmení hodnota kľúčového stĺpca, čo spôsobí jeho presunutie do iného indexového uzla – to bude mať za následok, že index bude pravdepodobne potrebovať reorganizáciu ovplyvňujúcu všetky súvisiace indexy a operácie čo má za následok rozsiahly pokles produktivity.
Podobné procesy sa vyskytujú pri volaní operátora VYMAZAŤ. Index môže pomôcť nájsť údaje, ktoré sa vymazávajú, ale odstránenie samotných údajov môže viesť k preusporiadaniu stránok. Ohľadom operátora VLOŽIŤ, hlavný nepriateľ všetkých indexov: začnete pridávať veľké množstvo dát, čo vedie k zmenám v indexoch a ich reorganizácii a všetci trpia.
Pri premýšľaní o tom, aký typ indexov a koľko ich vytvoriť, preto zvážte typy dopytov do databázy. Viac neznamená lepšie. Pred pridaním nového indexu do tabuľky zvážte nielen náklady na základné dopyty, ale aj množstvo spotrebovaného miesta na disku, náklady na údržbu funkčnosti a indexov, čo môže viesť k dominovému efektu na iné operácie. Vaša stratégia návrhu indexu je jedným z najdôležitejších aspektov vašej implementácie a mala by zahŕňať mnoho aspektov, od veľkosti indexu, počtu jedinečných hodnôt až po typ dopytov, ktoré bude index podporovať.

Je potrebné vytvoriť klastrovaný index na stĺpci s primárnym kľúčom?

Klastrovaný index môžete vytvoriť v ľubovoľnom stĺpci, ktorý spĺňa požadované podmienky. Je pravda, že klastrovaný index a obmedzenie primárneho kľúča sú vytvorené jeden pre druhého a sú zhodné v nebesiach, takže pochopte skutočnosť, že keď vytvoríte primárny kľúč, potom sa automaticky vytvorí klastrovaný index, ak ešte nebol vytvorený. vytvorené predtým. Môžete sa však rozhodnúť, že klastrovaný index by inde fungoval lepšie a vaše rozhodnutie bude často opodstatnené.
Hlavným účelom klastrovaného indexu je zoradiť všetky riadky vo vašej tabuľke na základe kľúčového stĺpca zadaného pri definovaní indexu. To poskytuje rýchle vyhľadávanie a jednoduchý prístup k údajom tabuľky.
Primárny kľúč tabuľky môže byť dobrou voľbou, pretože jedinečne identifikuje každý riadok v tabuľkách bez toho, aby ste museli pridávať ďalšie údaje. V niektorých prípadoch bude najlepšou voľbou náhradný primárny kľúč, ktorý je nielen jedinečný, ale má aj malú veľkosť a ktorého hodnoty sa postupne zvyšujú, vďaka čomu sú nezhlukované indexy založené na tejto hodnote efektívnejšie. Optimalizátor dotazov má tiež rád túto kombináciu klastrovaného indexu a primárneho kľúča, pretože spájanie tabuliek je rýchlejšie ako spájanie iným spôsobom, ktorý nepoužíva primárny kľúč a jeho priradený klastrovaný index. Ako som povedal, je to zápas vyrobený v nebi.
Nakoniec však stojí za zmienku, že pri vytváraní klastrovaného indexu je potrebné zvážiť niekoľko aspektov: koľko neklastrovaných indexov bude na ňom založených, ako často sa bude meniť hodnota stĺpca kľúčového indexu a aká veľká. Keď sa hodnoty v stĺpcoch klastrovaného indexu zmenia alebo index nefunguje podľa očakávania, môžu byť ovplyvnené všetky ostatné indexy v tabuľke. Klastrovaný index by mal byť založený na najtrvalejšom stĺpci, ktorého hodnoty sa zvyšujú v určitom poradí, ale nemenia sa náhodným spôsobom. Index musí podporovať dotazy na najčastejšie prístupné údaje tabuľky, takže dotazy plne využívajú skutočnosť, že údaje sú triedené a prístupné v koreňových uzloch, listoch indexu. Ak primárny kľúč vyhovuje tomuto scenáru, použite ho. Ak nie, vyberte inú sadu stĺpcov.

Čo ak indexujete zobrazenie, je to stále zobrazenie?

Pohľad je virtuálna tabuľka, ktorá generuje údaje z jednej alebo viacerých tabuliek. V podstate ide o pomenovaný dotaz, ktorý načíta údaje zo základných tabuliek, keď zadáte dotaz na toto zobrazenie. Výkon dotazov môžete zlepšiť vytvorením klastrovaného indexu a neklastrovaných indexov v tomto zobrazení, podobne ako pri vytváraní indexov v tabuľke, ale hlavnou výzvou je, že najprv vytvoríte klastrovaný index a potom môžete vytvoriť neklastrovaný.
Keď sa vytvorí indexované zobrazenie (materializované zobrazenie), samotná definícia pohľadu zostane samostatnou entitou. Toto je predsa len pevne zakódovaný operátor VYBRAŤ, uložený v databáze. Ale index je úplne iný príbeh. Keď vytvoríte klastrovaný alebo neklastrovaný index na poskytovateľovi, údaje sa fyzicky uložia na disk, rovnako ako bežný index. Okrem toho, keď sa zmenia údaje v podkladových tabuľkách, index zobrazenia sa automaticky zmení (to znamená, že sa možno budete chcieť vyhnúť indexovaniu zobrazení v tabuľkách, ktoré sa často menia). V každom prípade pohľad zostáva pohľadom - pohľadom na tabuľky, ale práve vykonaným, s indexmi, ktoré mu zodpovedajú.
Pred vytvorením indexu v zobrazení musí spĺňať niekoľko obmedzení. Pohľad môže napríklad odkazovať iba na základné tabuľky, ale nie na iné zobrazenia a tieto tabuľky musia byť v rovnakej databáze. V skutočnosti existuje mnoho ďalších obmedzení, takže si skontrolujte dokumentáciu SQL Server pre všetky špinavé detaily.

Prečo používať krycí index namiesto zloženého indexu?

Najprv sa uistite, že rozumieme rozdielu medzi týmito dvoma. Zložený index je jednoducho bežný index, ktorý obsahuje viac ako jeden stĺpec. Je možné použiť viacero stĺpcov kľúča, aby ste sa uistili, že každý riadok v tabuľke je jedinečný, alebo môžete mať viacero stĺpcov, aby ste sa uistili, že primárny kľúč je jedinečný, alebo sa možno pokúšate optimalizovať vykonávanie často vyvolávaných dotazov vo viacerých stĺpcoch. Vo všeobecnosti však platí, že čím viac kľúčových stĺpcov index obsahuje, tým menej efektívny bude index, čo znamená, že zložené indexy by sa mali používať uvážlivo.
Ako už bolo uvedené, dotazu môže veľmi prospieť, ak sú všetky požadované údaje okamžite umiestnené na listoch indexu, rovnako ako samotný index. Toto nie je problém pre klastrovaný index, pretože všetky údaje tam už sú (preto je také dôležité starostlivo premýšľať, keď vytvárate klastrovaný index). Ale nezhlukovaný index na listoch obsahuje iba kľúčové stĺpce. Ak chcete získať prístup ku všetkým ostatným údajom, optimalizátor dotazov vyžaduje ďalšie kroky, ktoré môžu výrazne zvýšiť réžiu pri vykonávaní vašich dotazov.
Tu prichádza na pomoc krycí index. Keď definujete index bez klastrov, môžete do kľúčových stĺpcov zadať ďalšie stĺpce. Povedzme napríklad, že vaša aplikácia často dopytuje údaje stĺpcov Číslo objednávky A Dátum objednávky v tabulke Predaj:
SELECT OrderID, OrderDate FROM Sales WHERE OrderID = 12345;
V oboch stĺpcoch môžete vytvoriť zložený index bez klastrov, ale stĺpec Dátum objednávky pridá iba réžiu údržby indexu bez toho, aby slúžil ako obzvlášť užitočný kľúčový stĺpec. Najlepším riešením by bolo vytvoriť krycí index na kľúčovom stĺpci Číslo objednávky a dodatočne zahrnutý stĺpec Dátum objednávky:
CREATE NENCLUSTERED INDEX ix_orderid ON dbo.Sales(OrderID) INCLUDE (OrderDate);
Tým sa vyhnete nevýhodám indexovania redundantných stĺpcov a zároveň sa zachovajú výhody ukladania údajov do listov pri spúšťaní dotazov. Zahrnutý stĺpec nie je súčasťou kľúča, ale údaje sú uložené na listovom uzle, indexovom liste. To môže zlepšiť výkon dotazov bez akejkoľvek ďalšej réžie. Okrem toho, stĺpce zahrnuté v krycom indexe podliehajú menším obmedzeniam ako kľúčové stĺpce indexu.

Záleží na počte duplikátov v kľúčovom stĺpci?

Pri vytváraní indexu sa musíte pokúsiť znížiť počet duplikátov v kľúčových stĺpcoch. Alebo presnejšie: snažte sa, aby frekvencia opakovania bola čo najnižšia.
Ak pracujete so zloženým indexom, potom sa duplikácia vzťahuje na všetky kľúčové stĺpce ako celok. Jeden stĺpec môže obsahovať veľa duplicitných hodnôt, ale medzi všetkými stĺpcami indexu by malo byť minimálne opakovanie. Napríklad vytvoríte zložený nezhlukovaný index na stĺpcoch Krstné meno A Priezvisko, môžete mať veľa hodnôt John Doe a veľa hodnôt Doe, ale chcete mať čo najmenej hodnôt John Doe, alebo najlepšie len jednu hodnotu John Doe.
Pomer jedinečnosti hodnôt kľúčového stĺpca sa nazýva indexová selektivita. Čím viac jedinečných hodnôt je, tým vyššia je selektivita: jedinečný index má najväčšiu možnú selektivitu. Dotazovací nástroj má naozaj rád stĺpce s vysokými hodnotami selektivity, najmä ak sú tieto stĺpce zahrnuté v klauzulách WHERE vašich najčastejšie vykonávaných dotazov. Čím selektívnejší je index, tým rýchlejšie dokáže dotazovací nástroj zmenšiť veľkosť výslednej množiny údajov. Nevýhodou samozrejme je, že stĺpce s relatívne malým počtom jedinečných hodnôt budú len zriedka dobrými kandidátmi na indexovanie.

Je možné vytvoriť index bez klastrov iba na špecifickej podmnožine údajov kľúčového stĺpca?

V predvolenom nastavení obsahuje nezhlukovaný index jeden riadok pre každý riadok v tabuľke. Samozrejme, to isté môžete povedať o klastrovanom indexe za predpokladu, že takýto index je tabuľka. Ale pokiaľ ide o nezhlukovaný index, vzťah jedna k jednej je dôležitým konceptom, pretože počnúc verziou SQL Server 2008, máte možnosť vytvoriť filtrovateľný index, ktorý obmedzí počet riadkov v ňom zahrnutých. Filtrovaný index môže zlepšiť výkon dotazu, pretože... má menšiu veľkosť a obsahuje filtrované presnejšie štatistiky ako všetky tabuľkové - to vedie k vytvoreniu vylepšených plánov vykonávania. Filtrovaný index tiež vyžaduje menej úložného priestoru a nižšie náklady na údržbu. Index sa aktualizuje iba vtedy, keď sa zmenia údaje, ktoré zodpovedajú filtru.
Okrem toho sa ľahko vytvorí filtrovateľný index. V operátorovi VYTVORIŤ INDEX stačí len uviesť KDE stav filtra. Môžete napríklad odfiltrovať všetky riadky obsahujúce NULL z indexu, ako je uvedené v kóde:
CREATE NENCLUSTERED INDEX ix_trackingnumber ON Sales.SalesOrderDetail(CarrierTrackingNumber) WHERE CarrierTrackingNumber NIE JE NULL;
V skutočnosti môžeme odfiltrovať všetky údaje, ktoré nie sú dôležité v kritických dopytoch. Buďte však opatrní, pretože... SQL Server ukladá niekoľko obmedzení na filtrovateľné indexy, ako napríklad nemožnosť vytvoriť filtrovateľný index v zobrazení, preto si pozorne prečítajte dokumentáciu.
Je tiež možné, že podobné výsledky môžete dosiahnuť vytvorením indexovaného zobrazenia. Filtrovaný index má však niekoľko výhod, ako napríklad schopnosť znížiť náklady na údržbu a zlepšiť kvalitu vašich realizačných plánov. Filtrované indexy je možné prebudovať aj online. Skúste to s indexovaným zobrazením.

A opäť niečo málo od prekladateľa

Účelom objavenia sa tohto prekladu na stránkach Habrahabr bolo povedať alebo pripomenúť blog SimpleTalk z r. RedGate.
Uverejňuje veľa zábavných a zaujímavých príspevkov.
Nie som pridružený k žiadnym produktom spoločnosti RedGate, ani s ich predajom.

Ako som sľúbil, knihy pre tých, ktorí chcú vedieť viac
Od seba odporúčam tri veľmi dobré knihy (odkazy vedú na zapáliť verzie v predajni Amazon):

V zásade môžete otvárať jednoduché indexy

pre začiatočníkov

index

Pridať značky

Microsoft SQL Server 2012 T-SQL Fundamentals (Reference Developer) Autor Itzik Ben-Gan Dátum vydania: 15. júl 2012 Autor, majster svojho remesla, poskytuje základné poznatky o práci s databázami. Ak ste na všetko zabudli alebo ste nikdy nevedeli, určite stojí za prečítanie.

ROWID indexy sú databázové objekty, ktoré poskytujú zobrazenie všetkých hodnôt v stĺpci tabuľky, ako aj ROWID všetkých riadkov v tabuľke, ktoré obsahujú hodnoty stĺpca.

ROWID je pseudostĺpec, ktorý je jedinečným identifikátorom pre riadok v tabuľke a v skutočnosti popisuje presné fyzické umiestnenie tohto konkrétneho riadka. Na základe týchto informácií Oracle môže následne nájsť údaje spojené s riadkom tabuľky. Zakaždým, keď sa riadok presunie, exportuje, importuje alebo pri akejkoľvek inej operácii, ktorá zmení jeho umiestnenie, ROWID línii, pretože zaujíma inú fyzickú polohu. Na ukladanie dát ROWID Vyžaduje sa 80 bitov (10 bajtov). Identifikátory ROWID pozostáva zo štyroch komponentov: číslo objektu (32 bitov), ​​relatívne číslo súboru (10 bitov), ​​číslo bloku (22 bitov) a číslo riadku (16 bitov). Tieto identifikátory sú zobrazené ako 18-znakové sekvencie označujúce umiestnenie údajov v databáze, pričom každý znak je reprezentovaný vo formáte base-64 pozostávajúci zo znakov A-Z, a-z, 0-9, + a /. Prvých šesť znakov je číslo dátového objektu, ďalšie tri sú relatívne číslo súboru, ďalších šesť je číslo bloku a posledné tri sú číslo riadku.

Príklad:

SELECT fam, ROWID OD študenta;

FAM ROWID

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

IVANOV AAAA3kAAGAAAAGsAAA

PETROV AAAA3kAAGAAAAGsAAB

V databáze Oracle indexy sa používajú na rôzne účely: na zabezpečenie jedinečnosti hodnôt v databáze, na zlepšenie výkonu vyhľadávania záznamov v tabuľke atď. Výkon sa zlepšuje zahrnutím odkazu na indexovaný stĺpec alebo stĺpce do kritérií vyhľadávania pre údaje v tabuľke. IN Oracle indexy je možné vytvoriť v ľubovoľnom stĺpci tabuľky okrem stĺpcov LONG. Indexy rozlišujú medzi aplikáciami necitlivými na rýchlosť a vysokovýkonnými aplikáciami, najmä pri práci s veľkými tabuľkami. Pred rozhodnutím o vytvorení indexu však musíte zvážiť klady a zápory týkajúce sa výkonu systému. Výkon sa nezlepší, ak jednoducho zadáte index a zabudnete naň.

Aj keď najväčšie zlepšenie výkonu pochádza z vytvorenia indexu v stĺpci, kde sú všetky hodnoty jedinečné, podobné výsledky môžete získať pre stĺpce, ktoré obsahujú duplicitné hodnoty alebo hodnoty NULL. Na vytvorenie indexu nie je potrebné, aby hodnoty stĺpca boli jedinečné. Tu je niekoľko odporúčaní, ktoré vám pomôžu dosiahnuť požadované zvýšenie výkonu pri používaní štandardného indexu a pri vytváraní indexu sa pozrieme aj na problémy súvisiace s rovnováhou medzi výkonom a spotrebou miesta na disku.

Používanie indexov na vyhľadávanie informácií v tabuľkách môže poskytnúť výrazné zlepšenie výkonu oproti skenovaniu tabuliek, ktorých stĺpce nie sú indexované. Výber správneho indexu však nie je vôbec jednoduchý. Samozrejme, stĺpec, ktorého hodnoty sú všetky jedinečné, je vhodnejší na indexovanie pomocou indexu B-stromu, ale stĺpec, ktorý nespĺňa tieto požiadavky, je dobrým kandidátom, pokiaľ približne 10 % jeho riadkov obsahuje rovnaké hodnoty. a nič viac. Stĺpce „Switch“ alebo „flag“, napríklad tie, ktoré uchovávajú informácie o pohlaví osoby, nie sú vhodné pre indexy B-stromu. Stĺpce, ktoré sa používajú na uloženie malého počtu „spoľahlivých hodnôt“, ako aj tie, ktoré ukladajú niektoré hodnoty tiež nie sú vhodné, potom znaky, napríklad „spoľahlivosť“ alebo „nespoľahlivosť“, „aktivita“ alebo „nečinnosť“, „áno“ alebo „nie“ atď., atď. používa sa spravidla tam, kde je inštalovaný a prevádzkovaný Oracle Parallel Server a musíte zvýšiť úroveň paralelizmu v databáze na maximum.