Databázové indexy

Databázové indexy Lubomír Andrle lubomir.andrle@unicorn.eu 3. přednáška 14.10.2013

Obsah • Co je index • Struktura indexu • Jak se používá • Časté chyby

Proč indexy • Slouží ke zrychlení vyhledávání dat • Efektivní vynucování uniqueconstraintů • Z povahy jejich struktury • Redukce I/O operací • Čtení dat bez přístupu do tabulky

Vztah indexů s ostatními objekty

Přehled schémat indexů • B-tree index • B-tree cluster index • Hash cluster index • Reverse key index • Bitmap index • Bitmap join index

Příklad přístupu do tabulky Bez indexu Index Index (range) scan Nejdřív do indexu a až pak do tabulky Nemusí být výhodné, pokud je Davidů hodně • Full table scan • Výhodné pokud je Davidů hodně Everybody repeat after me: „Full table scans are not bad” ;)

Struktura B-tree indexu • Rootblock • Branchblock • Klíč a pointer na první leafblock • Leaf • Klíč a rowid • Pointer na předchozí a následující leaf

Vlastnosti indexu • Balanced • Všechny leafblocky jsou stejně daleko od rootu • Setříděné záznamy • Zleva do prava • Single-blockready • Multi-blockready jen ve spec. případě

Statistiky indexu • B-level • Výška indexu mezi rootblock a leaf • Definuje minimální počet „getů“ potřebných k projití indexu • Hloubka indexu = B-level + 1 • Leafblocks • Averageleafblocks per key • Průměrný počet leafblocks potřebných projít v případě uložení hodnoty • Average data blocks per key • Průměrný počet table blocks, která obsahují řádky referencované z klíče indexu

Statistiky indexu II • Clusteringfactor (CF) • Velmi silná statistika hýbající s cenou přístupu • Představuje jak dobře jsou setříděny řádky v tabulce vůči indexu • Statistika používaná CBO (Costbasedoptimizer) pro určení „ceny“ indexu • Problematický výpočet • Ne vždy odpovídá realitě • Kde je hledat • user_indexes • v$index_stats

Index s perfektním CF

Index se slabým CF

Práce s indexy

Metody přístupu do indexu • Metody • Uniquescan • Rangescan • Fullscan • Fastfullscan • Skip scan • Min/max • Optimalizované joiny • Přístupy do indexu jsou vždy single-block • dbfilesequentialread • Výjimka Fastfullscan

Uniquescan • Jeden z nejefektivnějších přístupů k datům • Použit v případě jedinečných hodnot indexu • Primarykey, Uniqueconstraint • Vrací vždy maximálně jeden řádek

Range scan • Běžná operace pro přístup k výběru hodnot • Data vrácena setříděna • Defaultně vzestupně • Více řádků s identickými hodnotami seřazeny podle ROWID

Rangescan - příklad • col1 = :b1 • col1 < :b1; col1 > :b1 • col1 like 'ASD%' • where col1 between 'J' and 'T'

Fullscan • Vlastně rangescan bez omezení

Skip scan - příklad • Tabulka Employees • Sloupce id, pohlaví • SELECT * FROM employees WHERE employee_id = 101;

Min/max • Hledá minimum nebo maximum na základě branches

Fastfullscan • Alternativa k fullscan v případě, kdy všechny sloupce dotazu jsou součástí indexu • Není nutný přístup do tabulky • Multi-blockread • Využití paralelního přístupu • Ignoruje branch bloky a strukturu indexu vůbec • Data nejsou řazena

Náročnost čtení indexu • Jde vždy o LIO (logické I/O) • B-level + (leaf bloky * selektivita indexu) +(clusteringfactor * selektivita tabulky) • Pravidlo 95/5 • „Na jeden klíč max. čtení 5% indexu“ • CBO • Pokud jsou statistiky přesné, spočítá to za vás

Náročnost zápisu do indexu • Typicky největší overhead při použití indexu • Zápis vždy při • Insert • Delete • Update • ( = insert + delete ) • Vlastní redo • Konkurence

Split indexu • 50/50 split při zaplnění leaf-blocku

Split indexu II • 90/10 split při zaplnění leaf-blocku • Speciální případ, pouze směrem doprava

Split indexu - konkurence • Během splitu se čeká … • Vlastní Redo • Propaguje se vždy směrem nahoru • Alwaysbalanced • Rootblock – jediný se nikdy nemění

Speciální případy

Složené indexy • Index nad více sloupci tabulky • Zvýšení rychlosti v případech, kdy where podmínka obsahuje všechny sloupce indexu • Záleží na pořadí sloupců!

Function-based indexy • Poskytuje efektivní mechanismus pro vyhodnocení příkazů, které obsahují funkci ve where podmínce • Pozor na Null hodnotu • Není v indexu ukládána • Trik s decode • Create index ix1 on tab1 (decode(status,'X',null)) • Příklad • CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);

Reverse-key indexy • Navrhnut k randomizaci indexovaných hodnot napříč strukturou indexu • Typicky u front v RAC • Opatrně, může mít zásadní overhead • Randomizace v rámci celého indexu • Např. Při použití seq (1000) a „hotblocku“

Partitioning indexes • Lokální index • Globální index • Nad celou tabulkou • Typicky použité pro OLAP

Bitmap index • MARITAL_STATUS, GENDER mají nízkou kardinalitu • Struktura indexu pro sloupec REGION

Bitmap index • Ukázka použití bitmap indexu

Časté chyby

Příliš mnoho indexů • Velký overhead • AWR (AutomaticWorkloadRepository) • Prohledání plánů • Indexovat podle toho, co chceme hledat • Výjimkou jsou ForeignKeys

Špatné pořadí sloupců • Nekompatibilní predikáty • Optimizer pak musí použít špatnou metodu přístupu • Where podmínka neodpovídá ani jednomu indexu • Index (col1), index (col2) • Where col1 = x and col2 = y • Měl by být složený ;)

Použití OR kde není potřeba • Ukázka • Výchozí hodnota sloupce Sl1 (not null) • Občas nutný override Sl2 (null) • Select … where Sl1 = ‘value’or Sl2 = ‘value’

Rebuild indexu • Téměř nikdy není potřeba • Index se sám stále vyrovnává a čistí • Často velice drahý • Konkurence • Redo • CPU • Může zbytečně index rozmělnit • Je potřeba pokud • Velké nárazové akce • Masivní delete, které ovlivní celý index • Fronty – typicky je všechno zajímavé úplně vpravo a index je zbytečně velký

Špatné plány • Zbytečné fullscany indexů • CPU • Dbfilesequentialread • Zbytečné přístupy do tabulek • Špatné where podmínky

Q&A

Databázové indexy