Relační databáze (SQL): návrh a optimalizace dotazů

Co jsou SQL databáze a proč na nich stojí informační systémy

SQL databáze (relační databázové systémy) jsou softwarové platformy pro ukládání, správu a dotazování strukturovaných dat. Vycházejí z relačního modelu, kde jsou data organizována do tabulek (relací) s řádky (tuply) a sloupci (atributy). Klíčovou roli hraje jazyk SQL – Structured Query Language, který poskytuje jednotný způsob definice schématu, manipulace s daty i řízení přístupu. Relační databáze jsou základním stavebním kamenem finančních aplikací, ERP/CRM systémů, e-commerce, telekomunikací i webových služeb díky důrazu na integritu, konzistenci, transakce a standardizované rozhraní.

Relační model: entity, vztahy a integritní omezení

Entita reprezentuje typ objektu (např. Zákazník, Objednávka), v databázi mapovaná na tabulku.
Primární klíč (PK) jednoznačně identifikuje řádek (např. customer_id).
Cizí klíč (FK) odkazuje na PK v jiné tabulce a zajišťuje referenční integritu.
Integritní omezení (CHECK, NOT NULL, UNIQUE) zabraňují uložení neplatných hodnot.
Kardinální vztahy 1:1, 1:N, M:N (poslední se obvykle realizuje přes spojovací tabulku).

Normalizace: odstraňování redundance a anomálií

Normalizace je postup návrhu schématu s cílem minimalizovat redundanci a logické anomálie při vkládání, mazání a aktualizaci dat. Nejčastěji se uplatňují:

1NF: atomické hodnoty, žádná opakující se pole.
2NF: bez částečných závislostí na části složeného klíče.
3NF: odstranění tranzitivních závislostí (neklíčový atribut nesmí záviset na jiném neklíčovém atributu).
BCNF: zpřísněná 3NF – každý determinant je kandidátní klíč.

V praxi se někdy záměrně volí denormalizace pro zlepšení čtecího výkonu (např. agregace, materializované pohledy) – vždy s vědomím kompromisů.

SQL jazyk: DDL, DML, DCL a TCL

DDL (Data Definition Language): CREATE TABLE, ALTER TABLE, CREATE INDEX – definuje schéma a indexy.
DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE – čtení a úpravy dat.
DCL (Data Control Language): GRANT, REVOKE – oprávnění a role.
TCL (Transaction Control Language): BEGIN, COMMIT, ROLLBACK, SAVEPOINT – řízení transakcí.

Většina systémů podporuje rozšíření SQL (procedury, funkce, trigery) pro implementaci logiky blíže k datům.

Indexy a přístupové cesty

Indexy urychlují vyhledávání a spojování na úkor prostoru a režie při zápisu. Nejčastější typy jsou:

B-tree (výchozí, vhodné pro rozsahy a rovnostní dotazy).
Hash (pro rovnostní dotazy; dostupnost závisí na systému).
Bitmapové (nízká kardinálnost, často v OLAP prostředích).
Plnotextové (full-text), GIS (R-tree/GiST pro prostorová data), GIN pro pole a JSON.

Doporučená praxe: indexovat PK, FK, často filtrované sloupce a klíče spojující tabulky; využívat pokryvné indexy (INCLUDE) a parciální/filtrující indexy pro selektivní podmnožiny dat.

Transakce a ACID vlastnosti

Atomicita: buď jsou všechny změny potvrzeny, nebo žádná (operace „vše nebo nic“).
Konzistence: stav po transakci dodržuje definovaná pravidla (omezení, triggery).
Izolace: souběžné transakce jsou izolovány podle zvolené izolační úrovně.
Durabilita: potvrzené transakce přetrvají i po výpadku systému (použití write-ahead logu, žurnálování).

Izolační úrovně a jevy souběhu

READ UNCOMMITTED: umožňuje dirty reads (v praxi vzácně používané).
READ COMMITTED: zabraňuje dirty reads, ale umožňuje non-repeatable reads a phantoms.
REPEATABLE READ: zaručuje stabilní čtení řádků; fantomy jsou možné dle implementace.
SERIALIZABLE: chování jako by transakce probíhaly sekvenčně (nejvyšší izolace).

Moderní databáze často používají MVCC (Multi-Version Concurrency Control) – čtení neblokuje zápis a naopak, prostřednictvím verzování řádků.

Optimalizátor dotazů a plánovač

SQL dotaz je přeložen do plánu dotazu (scan, index scan/seek, hash/merge/nested loop join, třídění, agregace). Optimalizátor vybírá plán na základě statistik o distribuci dat. Klíčové činnosti jsou:

Aktualizovat statistiky a analyzovat výkon pomocí nástrojů jako EXPLAIN/EXPLAIN ANALYZE.
Psát selektivní podmínky, přenášet filtry co nejblíže ke zdrojovým datům (predicate pushdown).
Minimalizovat přenosy dat, preferovat set-based přístup před řádkovými smyčkami.

Návrh schématu: doménové modelování a klíče

Výběr klíčů: přirozený versus surrogátní (např. UUID, SERIAL/IDENTITY); přirozený klíč má sémantiku, surrogátní je stabilní a jednoznačný.
Datové typy: odpovídající doméně (např. NUMERIC pro částky, DATE/TIMESTAMP s časovou zónou, INET pro IP adresy u systémů podporujících tyto typy).
Omezení: definujte CHECK (např. amount >= 0), unikátní kombinace (e-mail, IČO + provozovna).
Auditing: sloupce created_at, updated_at, created_by, deleted_at pro podporu „měkkého“ mazání (soft delete).

OLTP vs. OLAP, datové sklady a model hvězdy

Produkční systémy (OLTP) vyžadují krátké transakce a vysoký souběh uživatelů. Analytické systémy (OLAP) pracují s agregacemi nad velkými objemy dat. Častým řešením je:

ETL/ELT pipeline přenášející data do datového skladu.
Model star schema: faktové tabulky (měřítka) a dimenze (kdo, co, kdy, kde).
Materializované pohledy a partitioning pro zlepšení výkonu nad rozsáhlými daty.

Horizontální a vertikální škálování

Vertikální škálování: navýšení CPU, paměti RAM, rychlejšího úložiště (NVMe/SSD), optimalizace konfigurace (buffery, work_mem).
Replikace: asynchronní (rychlejší, ale s rizikem ztráty několika transakcí), synchronní (bez ztráty dat, vyšší latence).
Sharding: rozdělení dat mezi uzly na základě klíče (tenant_id, hash, geografická oblast).
Partitioning: dělení tabulek podle rozsahu, seznamu nebo hash; zvyšuje efektivitu skenování a údržby.

Bezpečnost: identita, role, šifrování, audit

Autentizace: lokální účty, LDAP/OAuth, IAM (zejména u cloudových služeb).
Autorizace: role a GRANT práva na schémata, tabulky, pohledy i řádky (Row-Level Security).
Šifrování: TLS pro přenos dat, TDE (Transparent Data Encryption) a šifrování sloupců (například pro osobní identifikovatelná data – PII).
Audit: protokolování DDL/DML změn, detekce anomálií, nepopiratelnost (tamper-evident logy).
Zásady: zásada nejmenších oprávnění, rotace klíčů, segregace prostředí (DEV/TEST/PROD).

Zálohování a obnova: RPO/RTO v praxi

Plné zálohy doplněné o inkrementální zálohy/WAL umožňující obnovu do konkrétního časového bodu (point-in-time recovery).
Testování obnovy: pravidelně ověřujte možnost obnovit data do požadovaného bodu v čase.
Geografická redundance: off-site kopie, objektové úložiště, neměnné (immutable) zálohy.

Monitorování a observabilita

Klíčové metriky: latence dotazů, propustnost (throughput), poměr zásahů do cache, využití I/O, čekání na zámky.
Profilace: identifikace dlouhých dotazů (slow query log), analýza plánovače, rozpad dotazů na operátory.
Kapacitní plánování: trendy růstu dat, fragmentace indexů, statistiky tabulek.

Pokročilá data: JSON, geodata, časy a časová pásma

JSON/JSONB: flexibilní schéma pro polostrukturovaná data, podpora indexace klíčů a cest.
GIS: prostorové typy a indexy (Point, Polygon), operace contains/intersects.
Časová data: TIMESTAMP WITH TIME ZONE, kalendářní specifika, intervaly, časové řady (downsampling, časová okna).

Typické antipatterny a jak se jim vyhnout

Všechno jako text: ztráta kontroly nad datovými typy, pomalé porovnávání; používejte adekvátní datové typy.
Chybějící indexy na FK: zpomalují spojování a mazání/aktualizace v rodičovských tabulkách.
Předčasná denormalizace: obtížná zajištění konzistence; nejdříve správný model, až poté cílené optimalizace.
„OR“ přes mnoho sloupců: vede k nutnosti full table scan; zvažte normalizaci, unnest, union all, nebo vhodné indexy.
Řádkové cykly místo set-based operací: využívejte spojení, okna a agregace.

Ukázky idiomů SQL (bez konkrétního dialektu)

CREATE TABLE objednavka ( objednavka_id BIGINT PRIMARY KEY, zakaznik_id BIGINT NOT NULL, celkem NUMERIC(12,2) CHECK (celkem >= 0), vytvoreno_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (zakaznik_id) REFERENCES zakaznik(zakaznik_id) );

CREATE INDEX ix_objednavka_zakaznik ON objednavka(zakaznik_id);

BEGIN; UPDATE objednavka SET celkem = celkem + 100 WHERE objednavka_id = 42; COMMIT;

SELECT z.zakaznik_id, SUM(o.celkem) AS obrat FROM zakaznik z JOIN objednavka o USING (zakaznik_id) WHERE o.vytvoreno_at >= CURRENT_DATE - INTERVAL '90 days' GROUP BY z.zakaznik_id HAVING SUM(o.celkem) > 10000 ORDER BY obrat DESC;

ALTER TABLE objednavka ADD COLUMN stav TEXT NOT NULL DEFAULT 'nova';

Volba platformy: přehled běžných systémů a jejich silných stránek

PostgreSQL: bohatá typová soustava, rozsáhlá rozšíření (GIS, full-text), MVCC, silná podpora JSON a procedurálního kódu.
MySQL/MariaDB: široká dostupnost v hostingu, replikace, dobrý výkon pro OLTP, jednoduchá správa.
SQLite: embedded databáze pro mobilní a desktopové aplikace, testování, nulová správa, jediný datový soubor.
SQL Server: hluboká integrace s ekosystémem .NET/Windows, bohaté nástroje pro správu a business intelligence.
Oracle Database: enterprise funkce, pokročilá škálovatelnost, rozsáhlé možnosti partitioningu a zabezpečení.