0 dní v řadě · 0 bodů

SQL · 20 min

Indexy a transakce

Jak index urychlí hledání a čím za to platíš. A jak pomocí transakcí docílit, aby se více změn provedlo buď všechny, nebo žádná.

Lidsky řečeno

Telefonní seznam seřazený podle příjmení najdeš za pár vteřin. Kdyby byl seřazený podle čehokoli jiného, musel bys číst od začátku do konce. Index je takový seřazený rejstřík ke sloupci: databáze podle něj najde řádek bez čtení celé tabulky. Transakce je zase balíček změn, který se buď provede celý, nebo vůbec: jako převod peněz, kdy musí z jednoho účtu ubýt a na druhý přibýt.

Index

Bez indexu musí databáze při WHERE mesto = 'Brno' projít všechny řádky (full scan). Index na sloupci mesto drží seřazený seznam hodnot s odkazy na řádky, takže dotaz skočí rovnou na správné místo.

text
CREATE INDEX idx_zaci_mesto ON zaci(mesto);

Že databáze index opravdu používá, ukáže EXPLAIN QUERY PLAN. Zkus ho před vytvořením indexu a po něm:

Vyzkoušej

Plán dotazu bez indexu a s ním

Tabulky v databázi
Načítám…

V odpovědi je nejdřív SCAN zaci (prochází se celá tabulka). Přidej před dotaz CREATE INDEX idx_zaci_mesto ON zaci(mesto); a odpověď se změní na SEARCH zaci USING INDEX idx_zaci_mesto.

Kdy index vyplatí a kdy ne

  • Ano: sloupce, podle kterých často hledáš (WHERE), spojuješ (JOIN, cizí klíče) nebo řadíš.
  • Ne: malé tabulky (sken je stejně rychlý) a sloupce s pár hodnotami (ano / ne).
  • Cena: index zabírá místo a každý INSERT, UPDATE a DELETE ho musí udržovat, takže zápisy zpomaluje.
  • Primární klíč a UNIQUE mají index automaticky.
  • UNIQUE index (CREATE UNIQUE INDEX …) navíc zakáže duplicitní hodnoty.

Transakce

Představ si převod: odečíst 500 Kč z účtu A a přičíst je účtu B. Kdyby po prvním kroku spadl server, peníze by zmizely. Transakce zaručí, že se provedou všechny kroky, nebo žádný.

PříkazVýznam
BEGIN;začni transakci
COMMIT;potvrď: změny se uloží natrvalo
ROLLBACK;zruš: vše od BEGIN se vrátí
Vyzkoušej

Smazat a vrátit zpět

Tabulky v databázi
Načítám…

Spusť ho a v poslední tabulce uvidíš, že všech 32 známek je zpátky. (Zobrazí se výsledek posledního dotazu; zkus smazat poslední SELECT a spustit jen první tři řádky, ať vidíš „prázdno“ uvnitř transakce.)

ACID

Spolehlivost databází se popisuje čtyřmi vlastnostmi:

  • Atomicity (atomicita): vše, nebo nic.
  • Consistency (konzistence): po transakci platí všechna pravidla (klíče, CHECK).
  • Isolation (izolace): souběžné transakce se navzájem nevidí rozpracované.
  • Durability (trvanlivost): potvrzená změna přežije i výpadek proudu.
Kvíz

K čemu slouží index?

Kvíz

Co udělá ROLLBACK;?

Kvíz

Proč se při převodu peněz používá transakce?

Úkoly

Úkol

Vytvoř index

Vytvoř index idx_znamky_zak na sloupci zak_id v tabulce znamky.

Tabulky v databázi
Načítám…
Úkol

Zkus a zruš

V transakci smaž všechny žáky (DELETE FROM zaci;), pak změnu zruš a na konci vypiš počet žáků jako pocet (musí vyjít původních 12).

Tabulky v databázi
Načítám…
Pod kapotou · Jak index vypadá uvnitř a proč izolace není zadarmo

Většina indexů je B-strom: vyvážený strom s malou hloubkou (často 3 až 4 úrovně i pro miliony řádků), takže hledání trvá logaritmicky dlouho, ne lineárně. Tentýž strom umí i rozsahy (BETWEEN) a řazení. Index na textu ve tvaru LIKE '%abc' (hledání uprostřed) nepomůže, protože strom je seřazený od začátku. Izolaci databáze zajišťují zámky nebo verzemi řádků (MVCC): čtenáři nečekají na zapisovatele, ale každý vidí „snímek“ dat. Čím přísnější je úroveň izolace (read committed, repeatable read, serializable), tím méně zvláštních jevů (nepotvrzené čtení, fantomové řádky), ale tím víc čekání a opakování transakcí.