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.
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:
Plán dotazu bez indexu a s ním
Tabulky v databázi
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,UPDATEaDELETEho musí udržovat, takže zápisy zpomaluje. - Primární klíč a
UNIQUEmají index automaticky. UNIQUEindex (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říkaz | Význam |
|---|---|
BEGIN; | začni transakci |
COMMIT; | potvrď: změny se uloží natrvalo |
ROLLBACK; | zruš: vše od BEGIN se vrátí |
Smazat a vrátit zpět
Tabulky v databázi
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.
K čemu slouží index?
Index je seřazený rejstřík. Čtení zrychlí, ale každá změna dat musí aktualizovat i index.
Co udělá ROLLBACK;?
COMMIT změny potvrdí, ROLLBACK je zahodí.
Proč se při převodu peněz používá transakce?
Tomu se říká atomicita. Částečně provedený převod by znamenal ztracené peníze.
Úkoly
Vytvoř index
Vytvoř index idx_znamky_zak na sloupci zak_id v tabulce znamky.
Tabulky v databázi
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
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í.