Co jsou indexy v sql. SQL - Indexy. Proč se klastrované a neklastrované indexy na serveru SQL Server nazývají B-strom?
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žitá vlastnost Klastrovaný index znamená, ž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 svého 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 jde o seskupený nebo neshlukovaný index, může být dále konfigurován 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Í, Že celé jméno musí být 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 potřebná data – obvykle je to mnohem více nejlepší alternativa skenování stolu. 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. Zvažte stůl s velké množství vkládání, ale s občasnými aktualizacemi nebo mazáním dat. 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ánka. 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říkaz 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 v tomto příkladu vytvořili jsme na tabulce neshlukovaný index 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 ve 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é, pak alespoň zkuste použít sloupce s velmi vysokým obsahem jedinečných hodnot.
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 rychlejší dotazy (zkuste si představit telefonní číslo v adresáři, který není řazen podle žádného principu). 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í subsystém přechod hierarchická struktura dolů na příslušný listový uzel obsahující 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ů to 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. Toto poskytuje rychlé hledání A jednoduchý přístup k údajů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 nejlepší volba Bude existovat náhradní primární klíč, který je nejen jedinečný, ale také má malou velikost a jehož hodnoty se postupně zvyšují, díky čemuž jsou indexy bez seskupení založené na této hodnotě 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?
Prezentace je virtuální stůl, 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 do tabulek, ale precizně provedeným v tento moment, s indexy, které tomu 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. Je možné použít více klíčových sloupců, aby byl každý z nich jedinečný řádky tabulky, je také možné, že se primární klíč skládá z několika sloupců, aby byla zajištěna jeho jedinečnost, nebo se snažíte optimalizovat provádění často vyvolávaných dotazů na několik 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ší rozhodnutí 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. Dotazový 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 neshlukovaný 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 prováděcí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
Účel vzhledu tohoto překladu na stránkách Habrahabr to bylo říct nebo připomenout blog SimpleTalk od 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):
 |
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, dává 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í. | V zásadě můžete otevřít jednoduché indexy Přidat značky
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 sekvence o 18 znacích označující umístění dat v databázi, přičemž každý znak je reprezentován ve formátu base-64, který se skládá z znaky 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ě, že 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 stejné 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é ukládají 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.
Pro začátek vám doporučuji zjistit, co to je krycí index, dám úryvek z článku o Habrém:
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 to jen 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ů.
To je tam kde krycí index spěchá na záchranu. Když definujete index bez klastrů, můžete do klíčových sloupců zadat další sloupce.
Krycí index by tedy neměl obsahovat všechny volitelné sloupce dotazu ve stromové struktuře indexu, ale pouze ty, které budou použity k filtrování nebo seskupování dat v dotazu, zbývající sloupce ze sekce SELECT umístěte do INCLUDE část indexu.
Možná vám pomůže odpověď na jinou otázku.
Výše uvedený příklad používá složený index se 3 poli spíše než krycí index, kód pro vytvoření krycího indexu by vypadal takto:
VYTVOŘTE NEZAHRNUTÝ INDEX NA . ( ASC) INCLUDE (, ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOW__PAGE = ON, ON)
Odpověď na vaši otázku:
pro krycí index pořadí sloupců v sekci INCLUDE nedůležité, ale pro složený index je důležité pořadí sloupců, protože Sloupcová data jsou umístěna do stromu indexu v pořadí, v jakém jsou uvedeny sloupce, a optimalizátor dotazů nebude moci použít 2sloupcový index k vyhledání hodnot pouze 2 sloupců. Jasný příklad toho, jak bude vypadat indexová struktura 2 sloupců (EMPLOYEE_ID, SUBSIDIARY_ID) můžete vidět na obrázku.
1) Pojem index
Index je nástroj, který poskytuje rychlý přístup k řádkům tabulky na základě hodnot jednoho nebo více sloupců.
Tento operátor je velmi rozmanitý, protože není standardizován, protože normy neřeší problémy s výkonem.
2) Vytváření indexů
VYTVOŘIT INDEX
NA()
3) Změna a mazání indexů
K řízení aktivity indexu se používá operátor:
ALTER INDEX
Chcete-li odebrat index, použijte operátor:
DROP INDEX
a) Pravidla pro výběr tabulky
1. Je vhodné indexovat tabulky, ve kterých není vybráno více než 5 % řádků.
2. Tabulky, které nemají duplikáty v klauzuli WHERE příkazu SELECT, by měly být indexovány.
3. Není praktické indexovat často aktualizované tabulky.
4. Není vhodné indexovat tabulky, které nezabírají více než 2 stránky (pro Oracle je to méně než 300 řádků), protože jejich úplné skenování netrvá déle.
b) Pravidla výběru sloupců
1. Primární a cizí klíče – často se používají ke spojování tabulek, získávání dat a vyhledávání. Vždy se jedná o unikátní indexy s maximální využitelností
2. Při použití možností referenční integrity vždy potřebujete index na FK.
3. Sloupce, podle kterých jsou data často řazena a/nebo seskupována.
4. Sloupce, které se často prohledávají v klauzuli WHERE příkazu SELECT.
5. Neměli byste vytvářet indexy na dlouhých popisných sloupcích.
c) Zásady tvorby kompozitních indexů
1. Složené indexy jsou dobré, pokud jednotlivé sloupce mají málo jedinečných hodnot, ale složený index poskytuje větší jedinečnost.
2. Pokud všechny hodnoty vybrané příkazem SELECT patří do složeného indexu, pak jsou hodnoty vybrány z indexu.
3. Složený index by měl být vytvořen, pokud klauzule WHERE používá dvě nebo více hodnot v kombinaci s operátorem AND.
d) Nedoporučuje se tvořit
Nedoporučuje se vytvářet indexy na sloupcích, včetně složených, které:
1. Zřídka se používá pro vyhledávání, slučování a řazení výsledků dotazů.
2. Obsahují často se měnící hodnoty, což vyžaduje časté aktualizace index zpomaluje výkon databáze.
3. Obsahují malý počet jedinečných hodnot (méně než 10 % m/f) nebo převažující počet řádků s jednou nebo dvěma hodnotami (město bydliště dodavatele je Moskva).
4. V klauzuli WHERE jsou na ně použity funkce nebo výraz a index nefunguje.
e) Nesmíme zapomenout
Měli byste se snažit snížit počet indexů, protože velký počet z nich snižuje rychlost aktualizace dat. Proto MS SQL Server doporučuje vytvářet maximálně 16 indexů na tabulku.
Indexy se obvykle vytvářejí pro účely dotazů a pro zachování referenční integrity.
Pokud se index nepoužívá pro dotazy, měl by být odstraněn a referenční integrita by měla být zajištěna pomocí spouštěčů.
