Jan Mikoláš
Slovník pojmů
Databázový index
Index není obecné tlačítko pro výkon. Pomáhá konkrétnímu filtru, řazení nebo spojení dat a současně zvyšuje cenu každého zápisu.
Stručná definice
Zkratka k vybraným řádkům, ne kopie celé tabulky.
Databázový index ukládá hodnoty vybraného sloupce nebo kombinace sloupců v uspořádané či jinak specializované struktuře spolu s odkazem na odpovídající řádky. Optimizer pak může u dotazu typu „všechny nové objednávky tohoto e-shopu seřazené podle času“ projít jen relevantní část indexu místo celé tabulky.
Index se vytváří podle skutečných přístupových vzorů, nikoli podle seznamu sloupců v tabulce. Každý INSERT, UPDATE nebo DELETE může také index upravit. Nadbytečné, špatně seřazené nebo nevyužité indexy proto nezrychlují aplikaci zdarma a mohou naopak zhoršit zápis i údržbu databáze.
K čemu se používá
Pro filtry, vztahy a řazení, které aplikace opravdu dělá
Dobře zvolený index odpovídá konkrétnímu WHERE, JOIN, ORDER BY nebo databázovému constraintu.
- vyhledání objednávky podle unikátního externího identifikátoru
- seznam objednávek jednoho obchodu filtrovaný stavem a řazený podle vytvoření
- spojení položek objednávky s objednávkou přes cizí klíč
- rychlé vyhledání neodeslaných outbox událostí pro workera
- dotazy do proměnlivého JSONB payloadu až poté, co se skutečně používají
Praktický příklad
Přehled nových objednávek jednoho obchodu
Administrace často ukazuje poslední nové objednávky pro konkrétní store_id. Dotaz filtruje obchod a stav, řadí podle created_at sestupně a vrací jen první stránku. Složený index ve stejném pořadí odpovídá tomuto čtecímu vzoru lépe než tři samostatné indexy bez návaznosti.
Než se index nasadí, plán se porovná na reprezentativní velikosti tabulky. Pokud se stav objednávky velmi často mění, ověří se také dopad zápisu. Index je pak samostatná migrační změna, nikoli neviditelný detail entity.
CREATE INDEX order_store_status_created_idx
ON customer_order (store_id, status, created_at DESC);
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, number, created_at
FROM customer_order
WHERE store_id = 42
AND status = 'new'
ORDER BY created_at DESC
LIMIT 50;Jak funguje
Od dotazu k plánu provedení
Databáze nemusí index použít vždy. Zvažuje podmínky, statistiky, velikost tabulky a cenu více možných plánů.
- Dotaz popíše potřebu WHERE, JOIN, ORDER BY a LIMIT určují, jaká data jsou potřebná a v jakém pořadí.
- Planner vyhodnotí možnosti Porovná sekvenční scan, index scan, bitmap scan a další plán podle statistik a odhadované ceny.
- Index zúží kandidáty Vhodný index přivede databázi k menšímu počtu řádků nebo ve správném pořadí; zbytek podmínky se může dopočítat nad nimi.
- Tabulka poskytne potřebná data Pokud index neobsahuje vše potřebné a není možné index-only čtení, databáze načte odpovídající řádky z tabulky.
- Zápis udržuje strukturu Při změně indexovaného údaje se aktualizuje tabulka i všechny relevantní indexy.
Hlavní části a pojmy
Typ indexu a pořadí sloupců mají konkrétní důsledek.
Nejprve se čte dotaz a plán, teprve potom se přidává index.
B-tree
B-tree je běžný výchozí index pro rovnost, rozsahy a často i řazení. Pro mnoho běžných relačních dotazů je správným začátkem, ne však automatickou odpovědí na každý typ dat.
Složený index
Pořadí sloupců rozhoduje. Index nad (store_id, status, created_at) přirozeně odpovídá filtru podle obchodu a stavu s řazením podle času; obrácená potřeba může chtít jiný návrh.
UNIQUE, primární a cizí klíč
Primární a unikátní constraint obvykle potřebují index jako součást vynucení pravidla. Index cizího klíče může být důležitý pro joiny a mazání rodičovského záznamu, ale jeho potřebu ověřuje konkrétní provoz.
Selektivita a částečný index
Sloupec se dvěma hodnotami nemusí sám o sobě zúžit dost řádků. Částečný index může držet jen často hledaný podmnožinový stav, například nezpracované události.
EXPLAIN a měření
EXPLAIN (ANALYZE) ukáže skutečný plán, počet řádků a čas. Je lepší podklad než odhad, že „sloupec ve WHERE si zaslouží index“.
Vztah k ostatním nástrojům
Index patří k modelu dat, ne k jedné PHP knihovně.
ORM ani migrační nástroj nerozhodnou samy, který index odpovídá reálnému provozu.
- PostgreSQL
- Nabízí B-tree i další indexové metody; planner vybírá z nich podle statistiky a dotazu.
- Databázová migrace
- Index se verzovaně vytváří a na velkých tabulkách plánuje s ohledem na zámky a dobu běhu.
- Doctrine ORM a DBAL
- Mapování nebo query builder mohou vygenerovat dotaz, ale výkon je potřeba ověřit nad výsledným SQL a plánem.
- Elasticsearch
- Vyhledávací index řeší jiný typ fulltextového hledání; nenahrazuje relační indexy, constrainty ani zdroj pravdy.
Výhody a omezení
Čtení rychlejší, zápis a údržba náročnější.
Přínosy
- rychlejší vyhledání malé části velké tabulky
- možnost vracet data v požadovaném pořadí bez samostatného třídění
- podpora unikátních pravidel a běžných joinů
- měřitelný způsob, jak řešit konkrétní pomalý dotaz
Omezení a časté chyby
- index na každý sloupec zpomaluje zápisy a zvyšuje nároky na úložiště
- špatné pořadí složeného indexu nepomůže zamýšlenému dotazu
- index nelze posoudit bez reálného plánu a objemu dat
- index neřeší N+1 dotazy, špatný filtr ani načítání zbytečných dat
- vytvoření indexu na velké produkční tabulce může mít provozní dopad
Kdy dává smysl
Až když známe otázku, na kterou má databáze odpovídat.
Index dává smysl pro častý nebo citlivý dotaz nad rostoucí tabulkou, zejména pokud filtr výrazně zúží data, spojení využívá vztah nebo aplikace potřebuje stabilní řazení s limitem. Na malé tabulce bývá sekvenční scan levnější a správnější než obcházení indexu.
Při návrhu se hodnotí i zápisový poměr. Tabulka s intenzivním importem a jen občasným reportem nemá dostat stejnou sadu indexů jako katalog často filtrováný uživateli. Nejdříve pomáhá odstranit N+1, zúžit SELECT a podívat se na EXPLAIN; teprve pak má index jasný účel.
Na co myslet
Index je návrhové rozhodnutí, které se ověřuje v datech.
Název a migrace mají říct, pro jaký dotaz index existuje.
- měřit konkrétní SQL přes EXPLAIN (ANALYZE) na reprezentativních datech
- volit pořadí složeného indexu podle filtrů, řazení a selektivity
- ověřit cenu indexu při insertu, update a dávkovém importu
- pro velké tabulky plánovat vytvoření indexu s ohledem na souběh a nasazení
- průběžně odstraňovat nebo přehodnocovat nevyužívané indexy podle provozních dat
Časté otázky
Indexy bez zbytečných zkratek
Zrychlí index každý dotaz?
Ne. Planner může vyhodnotit, že pro malou tabulku nebo velký podíl řádků je levnější sekvenční scan. Přínos se ověřuje na skutečném plánu.
Mám indexovat každý cizí klíč?
Často je to užitečné pro joiny a operace nad rodičovskou tabulkou, ale nejde o univerzální pravidlo. Rozhoduje způsob čtení a mazání konkrétních dat.
Proč nepomohou tři samostatné indexy vždy stejně jako jeden složený?
Složený index nese pořadí a může přímo odpovídat kombinaci filtru a řazení. Databáze někdy indexy zkombinuje, ale není to totéž pro každý dotaz.
Nahradí index opravu N+1 problému?
Ne. N+1 znamená příliš mnoho dotazů. Index může zrychlit jednotlivý z nich, ale nevhodný způsob načítání dat zůstane.
Jak pracuji s databázemi v praxi
Výkon databáze řeším od skutečného dotazu a jeho plánu.
U e-commerce a integračních aplikací navrhuji indexy, constrainty a dotazy podle reálných dat, provozu a bezpečného postupu změny schématu.