Indexování a optimalizace

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 zrychluje čtení díky uspořádané struktuře (typicky B-tree), ale zpomaluje zápisy (INSERT/UPDATE/DELETE), zvětšuje úložné nároky a vyžaduje údržbu. Optimalizace dotazů proto vždy balancuje 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 rovnosti (=), rozsahy (>, >=, <, <=), ORDER BY a prefixové LIKE 'abc%'.
• Hash – extrémně rychlé rovnosti, bez podpory rozsahů/řazení (v PostgreSQL jen pro rovnosti).
• GiST/GiN/BRIN (PostgreSQL) – speciální indexy pro fulltext (GIN), podobnostní vyhledávání, geografii (GiST), velmi velké tabely s „lokalitou“ dat (BRIN).
• Clustered vs. non-clustered – v InnoDB je primární klíč clusterovaný (organizuje fyzickou tabulku). SQL Server umožňuje zvolit, který index bude clusterovaný.
• Pokrývající (covering) – index, který obsahuje všechna pole potřebná pro dotaz, takže není nutné číst samotnou tabulku (SQL Server: INCLUDE; PostgreSQL: INCLUDE od v11; InnoDB nepodporuje INCLUDE, ale sekundární index nese PK, což může pomoci).

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

V kompozitním indexu rozhoduje pořadí sloupců. Platí „left-prefix“ pravidlo: INDEX(a, b, c) obslouží dotazy filtrující a, (a, b) nebo (a, b, c). Pořadí volte podle selektivity (kolik řádků filtr vyřadí) a typických predikátů/řazení:

• Pro WHERE country = ? AND created_at >= ? je často vhodné (country, created_at), pokud se nejprve filtruje země a poté časový rozsah.
• Pro stránkování ORDER BY created_at DESC uvažte (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 šel použít index

Sargabilní predikát (Search ARGument ABle) dovolí plánovači použít index. Vyhněte se transformacím na sloupci v WHERE, které znemožní použití indexu:

• Špatně: WHERE LOWER(email) = 'a@b.com' → 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 joinu (FK sloupce v child tabulce; PK/UK v parent). Zlepší se hash/merge/nested-loop joiny.
• ORDER BY – index v odpovídajícím pořadí umožní „index-only sort“; jinak dojde k externímu třídění (náklad na CPU a I/O).
• GROUP BY – v některých enginetech lze využít uspořádání indexu (např. GROUP BY a, b s indexem (a, b)) pro levnější agregaci.

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

Omezte index na relevantní podmnožinu dat – ušetříte místo i zápisové náklady.

• 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%') – použije B-tree (za předpokladu kompatibilní kolace).
• Obsahuje (%abc%) – vyžaduje speciální index (PostgreSQL pg_trgm + GIN/GiST) nebo fulltext (TSVector/TSQuery, InnoDB FULLTEXT).
• Kolace a case-insensitive – zvolte konzistentní kolaci/typ (PostgreSQL CITEXT nebo funkční index s LOWER()).

Statistiky, kardinalita a plánovač

Plánovač (optimizer) odhaduje náklady na základě statistik: histogramy, hustotu, korelace sloupců. Nepřesné odhady → špatný plán (např. nested-loop místo hash joinu).

• Aktualizujte statistiky (PostgreSQL ANALYZE, autovacuum; SQL Server UPDATE STATISTICS; MySQL je aktualizuje automaticky podle heuristik).
• Zvyšte granularitu (PostgreSQL ALTER TABLE ... ALTER COLUMN ... SET STATISTICS n;).
• V PostgreSQL využijte extended statistics (CREATE STATISTICS ... (dependencies, mcv, ndistinct) ON (a, b);) pro korelované sloupce.

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

Začněte EXPLAIN pro strukturu plánu a EXPLAIN ANALYZE pro skutečné časy/počty řádků. Sledujte:

• Rozdíl mezi Rows (odhady) a Actual Rows (skutečnost) – velké odchylky => problém statistik.
• Operace způsobující nejvyšší Cost/Time – často Seq Scan na velké tabulce, Sort bez indexu nebo Hash Aggregate na mnoha řádcích.
• Filter vs. Index Cond – ideálně vidět predikáty v Index Cond (sargabilní), nikoli až jako filtr po načtení.

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

• Omezte SELECT * – menší šířka řádku zmenší I/O, zvyšuje šanci na index-only scan.
• CTE – v PostgreSQL jsou od v12 inline (predikáty mohou „propadnout“ dovnitř). Starší verze CTE materializovaly – někdy zbytečné.
• Eliminujte zbytečné DISTINCT – přidává sort/agg. Pokud je unikátnost zajištěna schématem, je DISTINCT redundantní.
• Window funkce – používejte s vhodným PARTITION BY/ORDER BY a indexy dle pořadí pro snížení sortů.
• Predicate pushdown – filtrujte co nejdříve; u pohledů nebo sub-dotazů zajistěte, aby se filtry propagovaly.

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

OFFSET je lineárně drahý (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).
• Stabilní pořadí zajistěte sekundárním klíčem (např. (created_at DESC, id DESC)).

Particionování a indexy

Horizontální partitioning (range/list/hash) zmenší pracovní sadu pro dotaz. Každá partition má vlastní indexy; pruning odstraní nerelevantní části.

• Zvolte klíč, který odpovídá přístupovým vzorům (např. časové range).
• Vytvářejte identické indexy na všech partition (nebo deklarujte „na nad-tabulce“, aby se propagovaly).

Údržba indexů a tabulek

• Fragmentace – udržujte fillfactor, periodicky REINDEX (PostgreSQL) nebo defragmentujte (SQL Server ALTER INDEX REORGANIZE/REBUILD).
• VACUUM/Autovacuum (PostgreSQL) – odstraňuje mrtvé tuple; bez něj se indexy/tabulky „nafukují“.
• Droppujte nepoužívané indexy – sledujte 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, bohatá paleta indexů (GIN/GiST/BRIN), INCLUDE, parciální a funkční indexy, pokročilé statistiky.
• MySQL InnoDB – clusterovaný primární klíč, sekundární indexy obsahují PK; vybírejte krátké a stabilní PK (int/bigint), jinak každá sekundární lookup je dražší.
• SQL Server – bohaté možnosti pokrývajících indexů (INCLUDE), filtered indexes, columnstore pro analytické workloady, paralelismus a „parameter sniffing“ (viz níže).

Parameter sniffing, plánová cache a bind parametry

Optimalizátor může zvolit plán podle prvních parametrů, které uložil do cache. Pro různé distribuce dat to může být nevhodné.

• SQL Server – uvažte OPTION (RECOMPILE), OPTIMIZE FOR nebo plan guides pro problematické dotazy.
• PostgreSQL – „generic vs. custom“ plány u připravených dotazů; někdy pomůže nepoužívat prepared statements pro extrémně nerovnoměrná data.
• MySQL – menší vliv; i tak pište dotazy s bind parametry (bez string-konkatenace) pro bezpečnost a lepší plánování.

Agregace a analytika: B-tree vs. Columnstore

Pro reporting/OLAP zvažte sloupcové indexy/úložiště (SQL Server columnstore, PostgreSQL cstore_fdw nebo externí analytické systémy). U čistě OLTP je však B-tree kralující volbou.

Integrita a indexy: PK, FK, unique

• PK – implicitně indexovaný; v InnoDB určuje fyzické uspořádání.
• UNIQUE – zajišťuje integritu a často zrychlí lookup podle jedinečných klíčů.
• FK – indexujte sloupce cizích klíčů v child tabulkách, jinak mazání/aktualizace v parent může být drahé.

Indexování JSON a polostrukturovaných dat

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

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

• Minimalizujte počet indexů na tabulkách s vysokým write-throughputem.
• Logicky rozdělte workload (hot vs. cold data), archivační strategie.
• Batch operace (bulk load) s dočasným vypnutím/odložením indexů (kde engine dovolí) a následným rebuildem.

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

Row-level security, pohledy a politické filtry mohou bránit predicate pushdown. U multitenant aplikací indexujte tenant_id jako první klíč v kombinaci s jinými filtry pro efektivní prunování.

Praktické návrhové vzory

• „Horký čas“: posledních X dní – kompozitní index (created_at DESC, id DESC) pro rychlé feedy.
• Filtrování podle stavu a času: (status, updated_at) + parciální index na nejčastější stavy.
• Auditní log: BRIN na čas pro masivní tabulky + periodické vacuum a archivační partitioning.
• Adresář/uživatelé: case-insensitive vyhledávání přes funkční index (LOWER(email)) nebo CITEXT.

Příklady syntaxe (různé systémy)

PostgreSQL

CREATE INDEX idx_orders_country_created_at ON public.orders (country, created_at DESC) INCLUDE (total_amount);
CREATE INDEX idx_users_email_nocase
ON public.users ((LOWER(email)));

CREATE INDEX idx_orders_paid_partial
ON public.orders (created_at) WHERE status = 'PAID';

EXPLAIN ANALYZE
SELECT *
FROM public.orders
WHERE country = 'CZ' AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

MySQL (InnoDB)

CREATE INDEX idx_orders_country_created_at ON orders (country, created_at);
EXPLAIN FORMAT=TREE
SELECT *
FROM orders
WHERE country = 'CZ' AND created_at >= NOW() - INTERVAL 7 DAY
ORDER BY created_at DESC
LIMIT 50;

SQL Server

CREATE NONCLUSTERED INDEX IX_Orders_Country_CreatedAt ON dbo.Orders(country, created_at) INCLUDE(total_amount);
CREATE NONCLUSTERED INDEX IX_Orders_Paid_Filtered
ON dbo.Orders(created_at)
WHERE status = 'PAID';

SET STATISTICS IO, TIME ON;
EXPLAIN -- (v SSMS: zobrazení skutečného plánu)
SELECT TOP (50) *
FROM dbo.Orders
WHERE country = 'CZ' AND created_at >= DATEADD(DAY, -7, SYSUTCDATETIME())
ORDER BY created_at DESC;

Monitorování a metriky

• Latence klíčových dotazů (p50/p95/p99), počet čtených stránek/tuplů, počet rechecků u GIN/GiST.
• Podíl index-only scanů vs. heap accessů.
• Autovacuum/autoanalyze běhy, bloat indexů/tabulek, fragmentace.
• Počet a velikost indexů na tabulku, využití (hit-rate), hotspoty na zápis.

Checklist pro produkční kvalitu

• Každý cizí klíč má odpovídající index.
• Hlavní dotazy jsou sargabilní a mají pokrývající/kompozitní indexy dle přístupových vzorů.
• Statistiky jsou aktuální a nastavená granularita odpovídá variabilitě dat.
• Pravidelná údržba (VACUUM/REINDEX, rebuild/reorganize) je automatizovaná.
• Stránkování řešeno keysetem, nikoli velkým OFFSET.
• U JSON/fulltextu použity vhodné specializované indexy.
• V InnoDB je PK krátký a stabilní (surrogátní bigint), aby sekundární lookupy byly levné.

Závěr: indexy jako dlouhodobá smlouva mezi schématem a workloadem

Indexování a optimalizace dotazů není jednorázová akce, ale neustálé slaďování schématu, plánovače a reálného workloadu. Správně zvolený typ indexu, pořadí sloupců, sargabilní dotazy a disciplinovaná údržba přinášejí řádové zlepšení výkonnosti, stability i nákladů. Začněte měřením (EXPLAIN/ANALYZE), navrhněte minimální sadu indexů pokrývajících hlavní cesty, průběžně sledujte metriky a iterujte. Výsledkem je databáze, která roste s byznysem, nikoli proti němu.