Indexování a optimalizace databází

Proč indexovat: principy, cíle a kompromisy

Indexování v relačních databázích (PostgreSQL, MySQL/InnoDB, SQL Server, Oracle aj.) je klíčové pro rychlé vyhledávání, spojování a řazení dat. Index výrazně zrychluje čtení díky uspořádané struktuře (typicky B-tree), ale zpomaluje zápisy (INSERT/UPDATE/DELETE), zvyšuje nároky na úložiště a vyžaduje pravidelnou údržbu. Optimalizace dotazů proto vždy vyvažuje mezi latencí čtení, propustností zápisu, velikostí dat a složitostí správy.

Základní typy indexů a kdy je použít

• B-tree – univerzální volba pro testy rovnosti (=), rozsahy (>, >=, <, <=), ORDER BY a prefixové LIKE 'abc%'.
• Hash – extrémně rychlé pro rovnost, bez podpory rozsahů a řazení (v PostgreSQL pouze pro rovnosti).
• GiST/GiN/BRIN (PostgreSQL) – speciální indexy pro fulltextové vyhledávání (GIN), podobnostní vyhledávání a geografické údaje (GiST), velmi velké tabulky s lokalitou dat (BRIN).
• Clustered vs. non-clustered – v InnoDB je primární klíč clusterovaný a fyzicky organizuje tabulku. SQL Server umožňuje definovat, který index je clusterovaný.
• Pokrývající (covering) – index, který obsahuje všechna pole potřebná pro dotaz, takže není nutné přistupovat k samotné tabulce (SQL Server: INCLUDE; PostgreSQL: INCLUDE od verze 11; InnoDB nepodporuje INCLUDE, ale sekundární indexy nesou primární klíč, což může pomoci).

Volba sloupců a pořadí v kompozitním indexu

V kompozitním indexu rozhoduje pořadí sloupců. Platí pravidlo „left-prefix“: INDEX(a, b, c) pokryje dotazy, které filtrují podle a, (a, b) nebo (a, b, c). Pořadí volíme podle selektivity, tedy kolik řádků daný filtr vyloučí, a podle typických predikátů a řazení:

• Pro dotaz WHERE country = ? AND created_at >= ? je často vhodný index (country, created_at), pokud se nejprve filtruje země a následně časový rozsah.
• Pro stránkování s ORDER BY created_at DESC zvažte index (created_at DESC) (PostgreSQL podporuje směr v indexu), případně klíčové stránkování (viz níže).

Sargabilita: napište dotaz tak, aby bylo možné použít index

Sargabilní predikát (Search ARGument ABle) umožní plánovači využít index. Vyhněte se transformacím sloupců v WHERE, které zabrání použití indexu:

• Špatně: WHERE LOWER(email) = 'a@b.com' – raději vytvořte funkční/index výrazu (expression index) nebo uložte normalizovanou hodnotu.
• Špatně: WHERE created_at + INTERVAL '1 day' > NOW() – přepište na WHERE created_at > NOW() - INTERVAL '1 day'.
• Špatně: WHERE price * 1.21 >= 100 – použijte WHERE price >= 100/1.21.

Indexy pro spojování, řazení a agregace

• JOIN – indexujte cizí klíče na obou stranách spojení (FK sloupce v child tabulce; PK/UK v parent). Zlepší se výkon hash, merge i nested-loop joinů.
• ORDER BY – index ve správném pořadí umožní „index-only sort“, jinak dochází k externímu třídění s náklady na CPU a I/O.
• GROUP BY – některé engine umí využít uspořádání indexu (např. GROUP BY a, b s indexem (a, b)) pro efektivnější agregaci.

Parciální/filtrující a funkční indexy

Omezte index na relevantní podmnožinu dat, čímž ušetříte místo i náklady na zápis.

• Parciální index (PostgreSQL): CREATE INDEX ... ON orders(created_at) WHERE status = 'PAID';
• Filtered index (SQL Server): CREATE INDEX ... ON dbo.Orders(created_at) WHERE status = 'PAID';
• Index výrazu (PostgreSQL): CREATE INDEX ... ON users ((LOWER(email)));

Fulltext, podobnost a „LIKE“

• Prefixové LIKE (name LIKE 'abc%') – využívá B-tree (za podmínky kompatibilní kolace).
• Obsahuje (%abc%) – vyžaduje speciální index (PostgreSQL pg_trgm + GIN/GiST) nebo fulltextové indexy (TSVector/TSQuery, InnoDB FULLTEXT).
• Kolace a case-insensitive – zvolte konzistentní kolaci a typ, např. PostgreSQL CITEXT nebo funkční index s LOWER().

Statistiky, kardinalita a plánovač

Plánovač (optimalizátor) odhaduje náklady na základě statistik – histogramů, hustoty a korelace sloupců. Nepřesné odhady vedou ke špatným plánům (např. nested-loop místo hash joinu).

• Pravidelně aktualizujte statistiky (PostgreSQL ANALYZE, autovacuum; SQL Server UPDATE STATISTICS; MySQL aktualizuje automaticky podle heuristik).
• Zvyšte granularitu statistik (PostgreSQL ALTER TABLE ... ALTER COLUMN ... SET STATISTICS n;).
• V PostgreSQL používejte extended statistics (CREATE STATISTICS ... (dependencies, mcv, ndistinct) ON (a, b);) pro korelované sloupce.

EXPLAIN/EXPLAIN ANALYZE: jak číst plán

Začněte příkazem EXPLAIN pro strukturu plánu a použijte EXPLAIN ANALYZE pro získání skutečných časů a počtu řádků. Sledujte:

• Rozdíl mezi Rows (odhady) a Actual Rows (skutečnost) – velké odchylky naznačují problémy se statistikami.
• Operace s nejvyššími náklady a časy – často jde o Seq Scan na velké tabulce, Sort bez indexu nebo Hash Aggregate na velkém množství řádků.
• Rozlišujte Filter a Index Cond – ideálně by měl být predikát v Index Cond (sargabilní), ne až jako filtr po načtení dat.

Optimalizace dotazů: přepisy, které mění plán

• Omezte SELECT * – menší šířka řádku snižuje I/O a zvyšuje pravděpodobnost index-only scan.
• CTE – v PostgreSQL od verze 12 jsou inline (predikáty mohou „propadnout“ dovnitř). Starší verze materializovaly CTE, což někdy bylo zbytečné.
• Eliminujte zbytečné DISTINCT – to přidává sort či agregaci. Pokud je unikátnost zajištěna schématem, je DISTINCT nadbytečný.
• Window funkce – používejte s vhodným PARTITION BY/ORDER BY a odpovídajícími indexy pro minimalizaci třídění.
• Predicate pushdown – filtrujte co nejdříve; u pohledů nebo sub-dotazů zajistěte propagaci filtrů.

Stránkování: od OFFSET k keyset paginaci

OFFSET je nákladné lineárně, protože server přeskočí N řádků. Preferujte keyset pagination:

• Místo ORDER BY created_at DESC LIMIT 50 OFFSET 10000 použijte WHERE created_at < poslední_hodnota s indexem (created_at DESC).
• Zajistěte stabilní pořadí sekundárním klíčem (např. (created_at DESC, id DESC)).

Particionování a indexy

Horizontální particionování (range/list/hash) zmenšuje pracovní sadu dat pro dotaz. Každá partition má vlastní indexy; díky pruningu se odstraní nerelevantní části dat.

• Zvolte klíč particionování podle přístupových vzorů (např. časové rozsahy).
• Vytvářejte identické indexy na všech partition nebo je deklarujte nad nadřazenou tabulkou, aby se propagovaly.

Údržba indexů a tabulek

• Fragmentace – udržujte vhodný fillfactor, periodicky provádějte REINDEX (PostgreSQL) nebo defragmentujte pomocí ALTER INDEX REORGANIZE/REBUILD (SQL Server).
• VACUUM/Autovacuum (PostgreSQL) – odstraňuje mrtvé tuple; bez něj se indexy a tabulky zvětšují a zpomalují.
• Odstraňujte nepoužívané indexy – sledujte dynamické pohledy a statistiky (DMV/pg_stat_*) – každý index zpomaluje zápisy.

Vliv enginu: PostgreSQL vs. InnoDB vs. SQL Server

• PostgreSQL – MVCC s viditelností v indexech, HOT aktualizace, široká nabídka indexů (GIN/GiST/BRIN), INCLUDE, parciální a funkční indexy, pokročilé statistiky.
• MySQL InnoDB – clusterovaný primární klíč, sekundární indexy nesou primární klíč; preferujte krátké a stabilní primární klíče (int/bigint), jinak jsou sekundární lookupy dražší.
• SQL Server – bohaté možnosti pokrývajících indexů (INCLUDE), filtered indexes, columnstore pro analytické workloady, paralelismus a problematika „parameter sniffing“ (viz níže).

Parameter sniffing, plánová cache a bind parametry

Optimalizátor může volit plán na základě prvních parametrů uložených v cache. Pro rozdílné distribuční vzory dat to nemusí být optimální.

• SQL Server – zvažte OPTION (RECOMPILE), OPTIMIZE FOR nebo plan guides u problematických dotazů.
• PostgreSQL – rozlišuje „generic vs. custom“ plány u připravených dotazů; někdy je vhodné nepoužívat prepared statements pro velmi nerovnoměrná data.
• MySQL – menší vliv; doporučuje se psát dotazy s bind parametry (bez stringové konkatenace) pro bezpečnost a lepší plánování.

Agregace a analytika: B-tree vs. Columnstore

Pro reporting a OLAP zvažte sloupcové indexy a úložiště (SQL Server columnstore, PostgreSQL cstore_fdw nebo externí analytické systémy). U čistě OLTP aplikací však zůstává B-tree dominantní volbou.

Integrita a indexy: PK, FK, unique

• PK – implicitně indexovaný; v InnoDB určuje fyzické uspořádání tabulky.
• UNIQUE – zajišťuje integritu a často zrychluje vyhledávání podle jedinečných klíčů.
• FK – doporučuje se indexovat sloupce cizích klíčů v child tabulkách, jinak jsou operace mazání a aktualizace v parent tabulce nákladné.

Indexování JSON a polostrukturovaných dat

• PostgreSQL – GIN na jsonb pro klíč/hodnotu, funkční indexy na extrahované cesty (((jsonb_col ->> 'key'))).
• MySQL – funkční indexy na JSON_EXTRACT(...) (verze 8.0 a novější).
• SQL Server – computed columns a indexy nad nimi.

Řízení zápisové zátěže a kompromisy

• Minimalizujte počet indexů u tabulek s vysokým zápisovým zatížením.
• Logicky rozdělte workload na „hot“ a „cold“ data, využijte archivační strategie.
• Batch operace (bulk load) provádějte s dočasným vypnutím nebo odložením indexů (pokud to engine umožňuje) a následným rebuildem.

Bezpečnost a multitenance dopady na plánování

Row-level security, pohledy a politické filtry mohou znemožnit predicate pushdown. U multitenantních aplikací indexujte tenant_id jako první klíč v kompozici s dalšími filtry pro efektivní prunování dat.

Praktické návrhové vzory

• „Horký čas“: posledních několik dní – kompozitní index (created_at DESC, id DESC) pro rychlé načítání feedů.
• Filtrování podle stavu a času: (status, updated_at) plus parciální indexy pro nejčastější stavy.
• Auditní log: BRIN index na čas pro masivní tabulky, doplněný o pravidelné vacuum a archivační partitioning.
• Adresář/uživatelé