Marek Olšavský: blog

Nejsem velkým vyznavačem MySQL, už jsem se tu pokoušel porovnat MySQL a PostgreSQL z mého pohledu, ale používám ji, protože řadě zákazníků nemohu změnit webhosting. Mnohé hostingy ale nabízejí i MySQL 5.x, jež vypadá jako dospělá databáze a umí i pohledy, které mohou významně ušetřit zátěž databáze, ale přesto jsou ve velkém opomíjené.

Pro jeden z mých projektů připravuji data a protože mám ceníky od několika výrobců a dodavatelů (ti nemusejí mít v nabídce jen jednoho výrobce) a kolem databází se pohybuji již netriviálně krátkou dobu, vím, že je potřeba data pro uložení do tabulek optimalizovat, normalizovat. Normalizace ale znamená, že máte místo jedné tabulky s více sloupci několik tabulek, z nichž většina funguje jako číselníky. Je to příjemné z hlediska velikosti obsazeného diskového prostoru, trochu méně příjemné pro vkládání, protože při importu ceníku musíte každý řádek rozebrat do několika částí a ty podle potřeby uložit do databáze.

Pro výběr v trochu přehledné formě je sice možné využít v dotazech spojování tabulek (JOIN), ale to nemusí být zrovna nejrychlejší řešení, režie konverzí datových typů mezi aplikací a databází bude velmi pravděpodobně to ještě poměrně malé zlo, horší bude, že databáze musí (tak trochu) analyzovat, podle jakých sloupců se vybírají data a jaké potřebuje využít indexy. Mnohem rychlejší, a výhodnější i z hlediska zatížení databázového serveru, je využití pohledů.

Pohledy vcelku běžně znají uživatelé pokročilejších databázových řešení. Nejsnadnějším vysvětlením ,toho co jsou, je použít pro ně název virtuální tabulka, která je obvykle určena jen pro čtení dat. MySQL je až do verze 5.0 neuměla, ač to byla dost požadovaná funkčnost a celá řada vývojářů by je zajisté ocenila. Verze 5.0 byla přelomová ve více oblastech, ale v tomto článečku chci psát jen o pohledech. Dá se napsat, že verze 5.0 udělala z MySQL takřka seriózní databázi a přestala být pouze rychlým filesystémem, s indexací a jakýmsi fulltextovým vyhledáváním, vhodným leda jako webový backend.

V mém nasazení, respektive v jedné z mnoha databází, které mi na serveru běží jsou data v následující struktuře:

CREATE TABLE zbozi (
kod varchar(200) collate utf8_czech_ci NOT NULL,
nazev text collate utf8_czech_ci NOT NULL,
vyrobci_id int(10) unsigned NOT NULL,
dodavatele_id int(10) unsigned NOT NULL,
mnozstvi int(11) NOT NULL,
nakup double NOT NULL default '0',
imported tinyint(4) NOT NULL default '0',
PRIMARY KEY  (kod)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;

CREATE TABLE kategorie (
kategorie_id int(11) NOT NULL auto_increment,
vltava varchar(25) collate utf8_czech_ci default NULL,
vltava2 varchar(25) collate utf8_czech_ci default NULL,
zencart varchar(255) collate utf8_czech_ci NOT NULL,
PRIMARY KEY (kategorie_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 
COLLATE=utf8_czech_ci AUTO_INCREMENT=57 ;

CREATE TABLE dodavatele (
dodavatele_id int(10) unsigned NOT NULL auto_increment,
nazev varchar(50) collate utf8_czech_ci NOT NULL,
PRIMARY KEY (dodavatele_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=3 ;

CREATE TABLE vyrobci (
vyrobci_id int(10) unsigned NOT NULL auto_increment,
vyrobce varchar(50) collate utf8_czech_ci default NULL,
vltava int(10) unsigned default '0',
PRIMARY KEY  (vyrobci_id)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=24 ;

Je to struktura dat po normalizaci, kdy jsou duplicitní data vyřazena do zvláštních tabulek, fungujících, jako regulerní číselníky. Velmi často potřebuji pro přípravu importních seznamů název výrobku, kolik jej je skladem, jaká je nákupní cena (koeficienty se převádí na prodejní), název výrobce a jméno dodavatele. Samozřejmě to lze realizovat JOINovaným SELECTEM, například:

SELECT t1.kod AS kod,t1.nazev AS nazev,t2.vyrobce AS vyrobce,
t2.vltava AS nomenklatura, t3.nazev AS dodavatel, t1.mnozstvi AS mnozstvi,
t1.nakup AS nakup, t1.imported AS importovano from
((import.zbozi AS t1 left join import.vyrobci AS t2 
on((t1.vyrobci_id = t2.vyrobci_id))) left join import.dodavatele AS t3
on((t1.dodavatele_id = t3.dodavatele_id))

Dotaz sice vypadá trochu komplikovaně, nicméně je jednoduchý, ale díky délce mírně klame tělem, je to obyčejný JOIN přes 3 tabulky.

Použití pohledu je o něco čistší a nepoměrně rychlejší. Pohled si připravíte podobně jako předchozí dotaz, jen před ten dotaz uvedete sekvenci CREATE VIEW nazev_pohledu AS …:

CREATE VIEW zbozi2 AS SELECT t1.kod AS kod, t1.nazev AS nazev,
t2.vyrobce AS vyrobce, t2.vltava AS nomenklatura, t3.nazev AS dodavatel,
t1.mnozstvi AS mnozstvi, t1.nakup AS nakup, t1.imported AS importovano from
((import.zbozi AS t1 left join import.vyrobci AS t2
on((t1.vyrobci_id = t2.vyrobci_id))) left join import.dodavatele AS t3
on((t1.dodavatele_id = t3.dodavatele_id))

Výše uvedený příkaz vytvoří jakousi virtuální tabulku, ale protože je dotaz tohoto příkazu uložený na databázi a plně zanalyzovaný, je výběr z pohledu nepoměrně rychlejší, než kdybyste data vybírali ze 3 tabulek spojovaných pokaždé JOINem, pochopitelně pohled můžete vytvořit z kolika tabulek chcete a nemusí v něm být jen takto triviální vyjmenování sloupců, ale třeba i hodnoty vypočítané ze sloupců.

Pokud budete chtít získat data z pohledu, funguje naprosto normální příkaz SELECT * FROM jmeno_pohledu, pochopitelně že včetně omezení a výběru sloupců, které aktuálně potřebujete. Co jsem nezkoušel a zkoušet nebudu je INSERT, UPDATE, nebo DELETE dat v pohledu předpokládám, že by se to nemuselo chovat rozumně, ani nevím, jestli tyto operace MySQL dovoluje, vím že některé konkurenční servery ano.

PhpMyAdmin se na pohledy tváří malinko podivně, v přehledu databáze ani neuvidíte, kolik má řádků. Pohled zabírá na disku téměř nulové místo i v porovnání s tabulkami, ze kterých je sestaven, pokud tyto obsahují jen jednotky řádků, samozřejmě při velkých tabulkách obsazuje pohled defakto nulový prostor. Pohledy používám v databázích zcela běžně, protože je levnější a potenciálně výhodnější využívat takovéto optimalizace, než řešit extrémně rychle bobtnající databázi posilováním hardware.