Indexování a optimalizace dotazů v SQL: zvýšení rychlosti

Telekomunikace
Peter Kráľ
Indexování a optimalizace dotazů v SQL: Zvýšení rychlosti

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 nároky na úložiště a vyžaduje údržbu. Optimalizace dotazů proto vždy hledá rovnováhu 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é vyhledávání podle rovnosti, bez podpory rozsahů a řazení (v PostgreSQL pouze pro rovnost).
  • GiST/GiN/BRIN (PostgreSQL) – speciální indexy pro fulltextové vyhledávání (GIN), vyhledávání podobnosti, geografická data (GiST) a velmi velké tabulky s „lokalitou“ dat (BRIN).
  • Clustered vs. non-clustered – v InnoDB je primární klíč clusterovaný (určuje fyzické uspořádání tabulky). 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 verze 11; InnoDB INCLUDE nepodporuje, ale sekundární index obsahuje PK, 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) 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ů či řazení:

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

Sargabilita: pište dotazy tak, aby mohly využít index

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

  • Špatně: WHERE LOWER(email) = 'a@b.com' → vytvořte funkční index (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 podřízené tabulce; PK/UK v nadřazené tabulce). Zlepší se hash joiny, merge joiny i nested-loop joiny.
  • ORDER BY – index v odpovídajícím pořadí umožní „řazení pouze pomocí indexu“; jinak dojde k externímu třídění (náklady na CPU a I/O).
  • GROUP BY – některé databázové enginy mohou využít uspořádání indexu (např. GROUP BY a, b s indexem (a, b)) a zlevnit tak agregaci.

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

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

  • 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)));

Fulltextové vyhledávání, podobnost a „LIKE“

  • Prefixové LIKE (name LIKE 'abc%') – použije B-tree (za předpokladu kompatibilní kolace).
  • Vyhledávání podřetězce (%abc%) – vyžaduje speciální index (PostgreSQL pg_trgm + GIN/GiST) nebo fulltext (TSVector/TSQuery, InnoDB FULLTEXT).
  • Kolace a vyhledávání bez rozlišení velikosti písmen – zvolte konzistentní kolaci či typ (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ému plánu (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 příkazem EXPLAIN, který zobrazí strukturu plánu, a pokračujte příkazem EXPLAIN ANALYZE, který ukáže skutečné časy a počty řádků. Sledujte:

  • Rozdíl mezi Rows (odhady) a Actual Rows (skutečnost) – velké odchylky => problém se statistikami.
  • Operace s nejvyšší hodnotou Cost/Time – často Seq Scan na velké tabulce, Sort bez indexu nebo Hash Aggregate nad mnoha řádky.
  • Filter vs. Index Cond – predikáty by se měly ideálně objevit v Index Cond (jsou sargabilní), nikoli až jako filtr po načtení.

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

  • Omezte SELECT * – menší šířka řádku snižuje I/O a zvyšuje šanci na index-only scan.
  • CTE – v PostgreSQL se od verze 12 vkládají přímo do dotazu (predikáty mohou „propadnout“ dovnitř). Starší verze CTE materializovaly, což bylo někdy zbytečné.
  • Odstraňte zbytečné DISTINCT – přidává třídění či agregaci. Pokud jedinečnost zajišťuje schéma, je DISTINCT nadbytečné.
  • Window funkce – používejte je s vhodným PARTITION BY/ORDER BY a indexy odpovídajícími pořadí, abyste omezili třídění.
  • Predicate pushdown – filtrujte co nejdříve; u pohledů nebo poddotazů zajistěte, aby se filtry propagovaly.

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

OFFSET je lineárně nákladný (server přeskočí N řádků). Upřednostněte 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)).

Partitioning 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íč odpovídající přístupovým vzorům (např. časovému rozsahu).
  • Vytvářejte identické indexy ve všech partition (nebo je deklarujte „nad tabulkou“, aby se propagovaly).

Údržba indexů a tabulek

  • Fragmentace – nastavte vhodný fillfactor a pravidelně spouštějte REINDEX (PostgreSQL) nebo defragmentujte (SQL Server ALTER INDEX REORGANIZE/REBUILD).
  • VACUUM/Autovacuum (PostgreSQL) – odstraňuje mrtvé tuple; bez něj se indexy a tabulky „nafukují“.
  • Odstraňujte nepoužívané indexy – sledujte DMV/pg_stat_*; každý index zpomaluje zápisy.

Vliv databázového enginu: PostgreSQL vs. InnoDB vs. SQL Server

  • PostgreSQL – MVCC s viditelností v indexech, aktualizace HOT, bohatá 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 obsahují PK; volte krátký a stabilní PK (int/bigint), jinak je každé vyhledání přes sekundární index nákladnější.
  • SQL Server – široké možnosti tvorby pokrývajících indexů (INCLUDE), filtered indexes, columnstore pro analytické workloady, paralelismus a „parameter sniffing“ (viz níže).

Parameter sniffing, cache plánů a bind parametry

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

  • SQL Server – uvažte OPTION (RECOMPILE), OPTIMIZE FOR nebo plan guides pro problematické dotazy.
  • PostgreSQL – u připravených dotazů se používají „generic vs. custom“ plány; někdy pomůže nepoužívat prepared statements u dat s extrémně nerovnoměrnou distribucí.
  • MySQL – vliv je menší; i tak pište dotazy s bind parametry (bez konkatenace řetězců) kvůli bezpečnosti a lepšímu plánování.

Agregace a analytika: B-tree vs. Columnstore

Pro reporting/OLAP zvažte sloupcové indexy či úložiště (SQL Server columnstore, PostgreSQL cstore_fdw nebo externí analytické systémy). V čistě OLTP prostředí však zůstává B-tree jednoznačně nejčastější volbou.

Integrita a indexy: PK, FK, unique

  • PK – je implicitně indexovaný; v InnoDB určuje fyzické uspořádání.
  • UNIQUE – zajišťuje integritu a často zrychluje vyhledávání podle jedinečných klíčů.
  • FK – indexujte sloupce cizích klíčů v podřízených tabulkách, jinak může být mazání či aktualizace v nadřazené tabulce nákladné.

Indexování JSON a polostrukturovaných dat

  • PostgreSQL – GIN nad jsonb pro klíče a hodnoty, funkční indexy nad extrahovanými cestami ((jsonb_col ->> 'key')).
  • MySQL – funkční indexy nad JSON_EXTRACT(...) (8.0+).
  • SQL Server – vypočítané sloupce a indexy nad nimi.

Řízení zátěže při zápisu a kompromisy

  • Minimalizujte počet indexů na tabulkách s vysokou propustností zápisu.
  • Logicky rozdělte workload (hot vs. cold data) a zvažte archivační strategie.
  • Dávkové operace (bulk load) provádějte s dočasným vypnutím či odložením indexů (pokud to databázový engine umožňuje) a následným opětovným vytvořením.

Bezpečnost a dopad multitenancy na plánování

Zabezpečení na úrovni řádků, pohledy a filtry založené na zásadách mohou bránit predicate pushdown. V multitenantních aplikacích indexujte tenant_id jako první klíč v kombinaci s dalšími filtry, abyste dosáhli efektivního vyřazování nerelevantních částí.

Praktické návrhové vzory

  • „Horké období“: 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 pro nejčastější stavy.
  • Auditní log: BRIN nad časovým sloupcem pro rozsáhlé tabulky + pravidelné vacuum a archivace pomocí partitioningu.
  • Adresář/uživatelé: vyhledávání bez rozlišení velikosti písmen pomocí funkčního indexu (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 přečtených stránek/tuplů, počet opakovaných kontrol u GIN/GiST.
  • Podíl index-only scanů vůči přístupům k heapu.
  • Průběhy autovacuum/autoanalyze, bloat indexů a tabulek, fragmentace.
  • Počet a velikost indexů na tabulku, využití (hit-rate), úzká místa při zápisu.

Kontrolní seznam pro produkční kvalitu

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

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

Indexování a optimalizace dotazů nejsou jednorázovou akcí, ale průběžným slaďováním 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í přístupové cesty, průběžně sledujte metriky a postupně návrh upravujte. Výsledkem je databáze, která roste spolu s byznysem, nikoli proti němu.