Indexy co je teba znt Miloslav Peterka Solitea
Indexy - co je třeba znát Miloslav Peterka, Solitea BI Experts, s. r. o. MCSE: Data Platform | Business Intelligence | Data Management and Analytics miloslav. peterka@biexperts. cz
Agenda § § § Heap vs. indexy Klastrované indexy Neklastrované indexy Volba indexů Optimalizace a údržba indexů
Co je HEAP § Tabulka bez uspořádání datových stránek a řádků na datových stránkách • Vkládané a měněné řádky mohou být umístěny kamkoliv v tabulce • Pojítkem mezi daty z tabulky je IAM § IAM = Index Allocation Map • bitová mapa určující bloky alokované jednotkou • pokrývá 4 GB prostor
Organizace Heapu sys. indexes index_id = 0 sys. partitions system_internals_allocation_units first_iam_page … IAM Extent 127 128 129 130 … Heap Bit. Map 1 1 0 1 Extent 128 Extent 129 Extent 127 01 Jan … 01 Dunn … 01 Fík … 01 Russo … 01 Akers … 02 01 Funk Smith … … 02 01 Máňa Smith … … 02 01 Paris Smith … … 02 Woods … 01 Con … 02 Funk … 01 Russo … 02 Owen … 03 Pepik … 03 Owen … 03 Tokyo 01 Akers … … 01 Akers … 03 Barr 01 Smith … … 02 01 Funk Smith … … 02 Funk … 02 Woods … 03 02 Smith Woods … … 03 Smith … 02 Funk … 04 03 Durrer 02 Jones Funk. . . … … 04 03 Sleppy 02 Jones Funk. . . … … 04 03 Atlanta Jones. . . … 02 Owen … …. . . Martin 02 Owen. . . … … … 03. . . Martin Woods. . . … 03 Woods 04 03 Barr … 05 … Lang 03 … Smith. . . … 05 … La. Mee 03 … Smith. . . … …. . . … … 03. . . Jones … … … …. . . 04 Martin. . . …. . . . … …. . . . . … … …. . . Extent 130 01 Graff … 01 Russo … 01 Akers … 02 01 Lojza Smith … … 02 Woods … 01 Con … 02 Funk … 01 Russo … 02 Owen … 03 Koch … 01 Akers … 03 Barr … 02 01 Funk Smith … … 03 02 Smith Woods … … … 03. . . 02 Jones Funk. . . … … …. . . Martin 02 Owen. . . … … 03 Woods 04 03 Barr … … …. . . 03 … Smith. . . … …. . . . … … 03. . . Jones … … …. . . 04 Martin … …. . . . . … …. . .
Operace na HEAPU § INSERT – řádek umístěn na první stránku s dostatkem místa § UPDATE – řádek zůstává na svém místě, pokud se vejde; jinak je umístěn na první stránku s dostatkem volného místa § DELETE – uvolní místo na dané stránce § SELECT – celá tabulka musí být přečtena, pokud neexistují indexy
Přístup SQL Serveru k datům § Table scan • SQL Server čte všechny stránky tabulky • Filtrace dat se provádí po jejich načtení • Vyžaduje velký počet IO operací § Využití indexu • Struktura indexu je použita pro načtení požadovaných dat • Méně IO operací, méně paměti, výrazně rychlejší
Indexy § Vytváří se nad jedním, případně více sloupci § Používají strukturu B-stromu § Umožňují rychlou navigaci k hledaným datům
Demo 1 § Heap a IAM § Přístup SQL Serveru k datům
Klastrované indexy § V každé tabulce možný pouze jeden § V listové úrovni stromu jsou vlastní datové stránky tabulky § Stránky i řádky na stránkách organizovány v logickém pořadí dle indexového klíče § Analogie k uspořádání informací ve slovníku nebo v telefonním seznamu
Práce s klastrovaným indexem SELECT Last. Name, First. Name FROM Customer WHERE Last. Name = 'Owen' root_page Page 141 Akers Ganio … Page 100 Akers 2334 Barr 5678 Con 2534 Funk 1334 Funk 1534. . . Page 110 Ganio 7678 Hall 8078 Jones 2434 Jones 5978 Jones 2634. . . Page 140 - Root Akers … Martin . . . . Page 120 Martin 1234 Martin 7778 Owen 5878 Pica 7878 Russo 6078. . . Page 145 Martin Smith … . . . . Page 130 Smith 1434 Smith 5778 Smith 7978 Woods 2234 Woods 1634. . .
Operace v klastrovaném indexu § INSERT • řádek musí být vložen na patřičnou pozici • pokud se nevejde, stránka se musí rozdělit § UPDATE • řádek zůstává na svém místě, pokud se po změně vejde a nemění se klastrovaný klíč • pokud se nevejde, stránka se musí rozdělit • Pokud se mění klastrovaný klíč, řádek se přesouvá
Operace v klastrovaném indexu § DELETE • uvolní místo jeho označením na volné § SELECT • Dotazy na klastrovaný klíč přistoupí k požadovaným řádkům prostřednictvím kl. Indexu (index seek) • Index podpoří také třídění dle klastrovaného klíče a hledání dle rozsahu klastrovaného klíče
Demo 2 § Klastrované indexy
Neklastrované indexy § § Používají stejný B-strom jako klastrované Analogie s rejstříkem v knize Jejich index_id je větší než 1, až 999 na tabulku Liší se v listové úrovni • Neobsahuje datové stránky tabulky s kompletními řádky, ale odkazuje do tabulky tzv. lookup hodnotou RID pro tabulku na heapu Klastrovaný klíč pro tabulku s klastrovaným indexem
Neklastrovaný index nad heapem SELECT Last. Name, First. Name FROM Customer WHERE Last. Name between 'Martin' and 'Russo' root_page Page 12 - Root Nelistové úrovně Akers … Martin Page 28 Martin Smith. . . Page 37 Akers Ganio. . . Page 41 Akers Barr Con Funk Page 51 4: 706: 01 4: 705: 03 4: 704: 01 4: 706: 02 4: 704: 02 Ganio Hall Jones Page 61 4: 709: 03 4: 709: 02 4: 708: 03 4: 707: 03 Martin Mather Owen Pica Russo Page 71 4: 708: 01 4: 706: 04 4: 707: 02 4: 708: 02 4: 705: 01 Smith Woods 4: 706: 03 4: 708: 04 4: 707: 01 4: 704: 03 4: 705: 02 Neklastr. index nad Last. Name Listová úroveň Heap Page 704 Page 705 Page 706 Page 707 Page 708 Page 709 01 02 03 . . . Con. . . Funk. . . Woods 01 02 03 . . . Russo. . . Woods. . . Barr 01 02 03 . . . Smith. . . Owen. . . Jones 01. . . Martin 02 02. . . Pica 03. . . Jones 01 02 03 . . . Ganio. . . Jones. . . Hall . . 01 02 03 04. . . Akers. . . Funk. . . Smith. . . Mather. . Smith. . .
Neklastrovaný index nad klastrovanou tabulkou SELECT Last. Name, Phone FROM Customer WHERE First. Name = ‘Mike' root_page Nelistové Nelistová úrovně úroveň Aaron. . . Jose Aaron Diane … Aaron Adam Amy … Nicholls Barr Strande … Jose Nina … Diane Glimp Don Hall Douglas Groncki … … Jose Judy Mike … … … Kim Kocak La. Mee … Shane Haluk Brian … Lugo Lew Nash … Listová Leafúroveň Level (klastrovaný (Clustered klíč) Key Value) Klastrovaný index nad Last. Name Barr Kim Narp O’Donnell Barr Adam Cox Brian Dawson Matt … … Neklastrovaný index nad First. Name … … Narp Sylvie Nash Mike Nicholls Aaron … … …
Included colums § Možné pouze pro neklastrované indexy § Do listové úrovně navíc přidány hodnoty z vybraných sloupců (k neklastrovanému a klastrovanému klíči) § Umožňují uspokojit dotaz pouze neklastrovaným indexem • Pokrývající index
Demo 3 § Neklastrované indexy
Volba indexů § Indexy s sebou nesou režii, více neznamená lépe § Indexovat obecně • • Primární a cizí klíče Selektivní často dotazované sloupce Sloupce, podle kterých se často třídí Sloupce, podle kterých se často seskupuje § Záleží na dotazech
Volba klastrovaného indexu § Volba klastrovaného indexu je klíčová • co nejmenší datový typ všechny neklastrované indexy obsahují klastrovaný klíč • unikátní jinak se přidává ke klíči číslo (uniqueifier) o velikosti 4 B omezení Primary Key a Unique vytváří automaticky unikátní indexy • s pokud možno neměnnou hodnotou • rostoucí – minimalizuje fragmentaci při INSERTu
Volba klastrovaného indexu § Dobrou volbou pro klastrovaný klíč může být • celočíselný Identity sloupec (4 B int, 8 B bigint) • klíč složený z rostoucího sloupce typu date a unikátního identifikátoru řádků, např. Identity sloupce • GUID sloupec, pokud je generován jako rostoucí řada (funkcí NEWSEQUENTIALID()) častý problém současných databází
Optimalizace indexů - FILLFACTOR § Definuje zaplnění listové úrovně indexu § Oddaluje fragmentaci Plné datové stránky Marti 47040 47060. . . 470801 Akers. . . n 1 1 Pica. . . 470802 47040 47060 Funk. . . Jone 2 2. . . 470803 s 47040 47060 West. . . Smith. . . 470804 3 3 Gani 47050 47060. . . 470901 Ruth. . . Martin. . . o 1 4 Fillfactor 47050 50 – listová úroveň Jone na 50% 47070 zaplněná. . . 470902 West. . . Smith. . . s 1 47060 47040 Ruth. . . 470502 Aker 47080 47060 Martin Marti. . . Con. . 1 4 47050 Okad 47070 n 1 1 1 Barr. . . s. . . 3 a 2 Fun 47040 Wes. . . 47050 47080 47060 Smith. . . 47070. . . Pica. . . Funk. . . t 2 1 k 2 2 2 47050 Okad 47070 We 47040 Barr. . . Jone 47080 Smit 47060. . . 3 a 2 st 3 s 3 h 3 Con . . . 47080 4 Gani 47090. . . o 1 Jone 47090. . . s 2 Smith. . .
Optimalizace indexů – PAD_INDEX § Ovlivňuje zaplnění nelistových úrovní indexu § Způsobí použití FILLFACTORu i na nelistové stránky CREATE NONCLUSTERED INDEX IX_Work. Order_Product. ID ON Production. Work. Order(Product. ID) WITH (FILLFACTOR = 80, PAD_INDEX = ON);
Filtrované indexy § Optimalizované neklastrované indexy § Indexují pouze část řádků tabulky dle filtrační podmínky • Typicky pro NOT NULL hodnoty, nearchivní data, . . . § Menší, rychlejší, efektivnější, . . .
Detekce chybějících indexů § Exekuční plány v Management Studiu § Katalogové pohledy ze skupiny sys. dm_db_missing_index_xxxxx § Database Engine Tuning Advisor
Demo 4 § Database Engine Tuning Advisor
Údržba indexů § Indexy je třeba defragmentovat § Míru fragmentace lze zjistit • v Management Studiu ve vlastnostech indexu ze standardních reportů • v pohledu sys. dm_db_index_physical_stats Detaily v BOL
Shrnutí § § § Heap vs. indexy Klastrované indexy Neklastrované indexy Volba indexů Optimalizace a údržba indexů
Děkuji vám za pozornost! Miloslav Peterka, Solitea BI Experts, s. r. o. MCSE: Data Platform | Business Intelligence | Data Management and Analytics miloslav. peterka@biexperts. cz
- Slides: 29